MySQL 笔记4

Myth丶恋晨 2023-06-17 12:00 141阅读 0赞

### SHOW 命令 ###

SHOW GRANTS FOR user; -- 显示指定用户拥有的权限
    SHOW INDEX FROM tbl_name; -- 显示表索引信息
    SHOW PRIVILEGES; -- 显示服务器所支持的不同权限
    SHOW CREATE DATABASE db_name; -- 显示已经创建的库,创建时的语句
    SHOW CREATE TABLE tbl_name; -- 显示已经创建的表,创建时的语句
    SHOW LOGS; -- 显示BDB存储引擎的日志
    SHOW WARNINGS; -- 显示最后一个执行的语句所产生的错误、警告和通知
    SHOW ERRORS; -- 只显示最后一个执行语句所产生的错误

*参考：show命令总结 [https://www.cnblogs.com/Rohn/archive/2019/06/23/11072228.html][https_www.cnblogs.com_Rohn_archive_2019_06_23_11072228.html]*

### MySQL 复制表 ###

第一步 CREATE TABLE *target\_tbl* LIKE *source\_tbl;*  
也可以使用 SHOW CREATE TABLE 获取创建数据表(CREATE TABLE) 语句，复制SQL语句并修改数据表名，执行SQL语句

第二步 INSERT INTO *target\_tbl* SELECT \* FROM *source\_tbl;*

*参考：菜鸟教程 https://www.runoob.com/mysql/mysql-clone-tables.html  
create table select from 和 insert into table select from区别  [https://blog.csdn.net/moshowgame/article/details/82952992][https_blog.csdn.net_moshowgame_article_details_82952992]*

\[例\] role为原表，role1和role2为复制的表

### MySQL 处理重复数据 ###

可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

INSERT IGNORE INTO 如果插入重复数据，将不返回错误，只以警告形式返回  
REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉，再插入新记录

**统计重复数据**  
\[例\]统计表中 first\_name 和 last\_name的重复记录数

mysql> SELECT COUNT(\*) as repetitions, last\_name, first\_name  \-- 确定哪一列包含的值可能会重复，使用COUNT(\*)统计次数  
    -> FROM person\_tbl  
    -> GROUP BY last\_name, first\_name  \-- 在GROUP BY子句中列出那些列  
    -> HAVING repetitions > 1;  \-- HAVING子句设置重复数大于

**过滤重复数据**  
方法一：在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last\_name, first\_name  
    -> FROM person\_tbl;  
      
方法二：使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last\_name, first\_name  
    -> FROM person\_tbl  
    -> GROUP BY (last\_name, first\_name);

**删除重复数据**  
方法一：过滤重复数据插入到一张新表、删旧表、重命名新表  
mysql> CREATE TABLE tmp SELECT last\_name, first\_name, sex FROM person\_tbl  GROUP BY (last\_name, first\_name, sex);  
mysql> DROP TABLE person\_tbl;  
mysql> ALTER TABLE tmp RENAME TO person\_tbl;

方法二：在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）来删除表中的重复记录  
mysql> ALTER IGNORE TABLE person\_tbl  
    -> ADD PRIMARY KEY (last\_name, first\_name);

### MySQL 导出数据 ###

**1.使用SELECT...INTO OUTFILE语句导出数据到文本文件**  
\[例\] 将数据表 test\_tbl 数据导出到 /tmp/test.txt 文件中:  
mysql> SELECT \* FROM test\_tbl   
    -> INTO OUTFILE '/tmp/test.txt';

\[例\] 导出 CSV 格式：  
mysql> SELECT \* FROM passwd INTO OUTFILE '/tmp/test.txt'  
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
    -> LINES TERMINATED BY '\\r\\n';

**2.导出表作为原始数据**（使用- -tab=dir\_name选项，mysqldump对于每一个需备份的数据表产生两个输出文件：一个是带分隔符的文本文件，备份的数据表中的每行存储为文本中的一行，以“表名.txt”保存；另一个输出文件为数据表的CREATE TABLE语句，以“表名.sql”保存。）  
mysqldump 产生一个 SQL 脚本，其中包含创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

\[例\]将数据表 test\_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p --no-create-info \\  
            --tab=/tmp testdb test\_tbl  
password \*\*\*\*\*\*

**3.导出 SQL 格式的数据到指定文件**（不使用- -tab=dir\_name选项，mysqldump产生的数据文件是纯文本的SQL文件，有CREATE语句和INSERT语句组成。）  
$ mysqldump -u root -p testdb test\_tbl > dump.txt  
password \*\*\*\*\*\*

导出整个数据库的数据使用以下命令  
$ mysqldump -u root -p testdb > database\_dump.txt  
password \*\*\*\*\*\*

备份所有数据库，使用以下命令  
$ mysqldump -u root -p --all-databases > database\_dump.txt  
password \*\*\*\*\*\*

将备份的数据库导入到MySQL服务器中，可以使用以下命令，注意：需要确认数据库已经创建$ mysql -u root -p database\_name < dump.txt  
password \*\*\*\*\*

关于2和3的区别

*参考：  
菜鸟教程   https://www.runoob.com/mysql/mysql-database-export.html  
mysqldump的用法  https://www.cnblogs.com/ct20150811/p/9430018.html*

### MySQL 导入数据 ###

**1.mysql 命令导入**  
$ ./mysql -udbuser -pdbpasswd < test.sql  
password \*\*\*\*\*\*

**2.source 命令导入**  
mysql> create database abc;      \# 创建数据库  
mysql> use abc;                  \# 使用已创建的数据库   
mysql> set names utf8;           \# 设置编码  
mysql> source /home/abc/abc.sql  \# 导入备份数据库

**3.使用 LOAD DATA 导入数据**  
\[例\]从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;  
如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

可以在LOAD DATA语句中指出列值的分隔符和行尾标记，默认标记是定位符和换行符。

FIELDS 和 LINES 子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、\[OPTIONALLY\] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。  
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl  
  -> FIELDS TERMINATED BY ':'  
  -> LINES TERMINATED BY '\\r\\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

\[例\]在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，数据导入语法如下  
mysql> LOAD DATA LOCAL INFILE 'dump.txt'   
    -> INTO TABLE mytbl (b, c, a);  
      
**4.使用 mysqlimport 导入数据**  
mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口，大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：  
$ mysqlimport -u root -p --local mytbl dump.txt  
password \*\*\*\*\*

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：  
$ mysqlimport -u root -p --local --fields-terminated-by=":" \\  
   --lines-terminated-by="\\r\\n"  mytbl dump.txt  
password \*\*\*\*\*

mysqlimport 语句中使用 --columns 选项来设置列的顺序：  
$ mysqlimport -u root -p --local --columns=b,c,a \\  
    mytbl dump.txt  
password \*\*\*\*\*

*参考：  
菜鸟教程  [https://www.runoob.com/mysql/mysql-database-import.html][https_www.runoob.com_mysql_mysql-database-import.html]*

[https_www.cnblogs.com_Rohn_archive_2019_06_23_11072228.html]: https://www.cnblogs.com/Rohn/archive/2019/06/23/11072228.html
[https_blog.csdn.net_moshowgame_article_details_82952992]: https://blog.csdn.net/moshowgame/article/details/82952992
[https_www.runoob.com_mysql_mysql-database-import.html]: https://www.runoob.com/mysql/mysql-database-import.html