MySQL | 常用命令示例

MySQL是一款常用的关系型数据库管理系统，广泛应用于各个领域。在使用MySQL时，我们经常需要编写一些常用脚本来进行数据操作和管理。本文将介绍一些常用的MySQL脚本，帮助更好地使用MySQL。

一、启停MySQL数据库服务

1. 启动MySQL数据库服务

systemctl start mysqld

2. 停止MySQL数据库服务

systemctl stop mysqld

3. MySQL数据库服务状态查看

systemctl status mysqld

4. MySQL数据库服务开机自启

systemctl enable mysqld

5. MySQL数据库服务禁止开机自启

systemctl disable mysqld

二、连接MySQL数据库

在使用MySQL之前，我们需要先连接到数据库。可以使用以下脚本进行连接：

1. 使用用户名和密码连接MySQL数据库

mysql -u username -p password
或者
mysql -u username -p

其中，username为数据库用户名，password为数据库密码。

2. 使用主机名、用户名和密码连接MySQL数据库

mysql -h 主机名 -P 端口号 -u 用户名 -p 密码
或者
mysql -h 主机名 -P 端口号 -u 用户名 -p 

3. 使用数据库

连接到 MySQL 数据库后，可能有多个可以操作的数据库，可使用use 命令选择你要操作的数据库。

use 数据库名;

4. 关闭MySQL数据库连接的SQL语句

exit;
或者
quit;

三、创建和管理数据库

1. 创建数据库：使用CREATE DATABASE语句可以创建新的数据库（mydatabase），例如：

mysql> CREATE DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)mysql>

2. 删除数据库（慎用）：使用DROP DATABASE语句可以删除指定的数据库（mydatabase），例如：

mysql> DROP DATABASE mydatabase;
Query OK, 0 rows affected (0.01 sec)mysql> 

3. 查看数据库：使用SHOW DATABASES语句可以列出当前所有的数据库，例如：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql> 

4. 查看当前使用的数据库：

mysql> select database();
+------------+
| database() |
+------------+
| mydatabase |
+------------+
1 row in set (0.00 sec)mysql> 

5. 查看当前数据库包含的表信息

mysql> show tables; 
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| users                |
+----------------------+
1 row in set (0.00 sec)mysql> 

6. 获取表结构

mysql> desc users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    |                |
| email | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)mysql> 

7. 查看数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)mysql> 

四、创建和管理数据表

1. 创建数据表

数据表是数据库中存储数据的主要方式。可以使用以下脚本创建一个名为users的数据表：

mysql> use mydatabase
Database changed
mysql> mysql> CREATE TABLE users (->   id INT AUTO_INCREMENT PRIMARY KEY,->   name VARCHAR(50),->   age INT,->   email VARCHAR(50)-> );
Query OK, 0 rows affected (0.01 sec)mysql> 

2. 插入数据

插入数据是将数据添加到数据库中的关键操作。可以使用以下脚本向users表中插入一条数据：

mysql> INSERT INTO users (name, age, email) VALUES ('John', 25, '[e-mail]');
Query OK, 1 row affected (0.01 sec)mysql>

3. 查询数据

查询数据是使用MySQL的常见操作之一。可以使用以下脚本查询users表中的所有数据：

mysql> SELECT * FROM users;
+----+------+------+------------------+
| id | name | age  | email            |
+----+------+------+------------------+
|  1 | John |   25 | [e-mail] |
+----+------+------+------------------+
1 row in set (0.00 sec)mysql> 

4. 更新数据

更新数据是在数据库中修改现有数据的操作。可以使用以下脚本将users表中id为1的记录的age字段更新为30：

mysql> UPDATE users SET age = 30 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM users;
+----+------+------+------------------+
| id | name | age  | email            |
+----+------+------+------------------+
|  1 | John |   30 | [e-mail] |
+----+------+------+------------------+
1 row in set (0.00 sec)mysql> 

5. 删除数据

删除数据是从数据库中删除不再需要的数据的操作。可以使用以下脚本删除users表中id为1的记录：

DELETE FROM users WHERE id = 1;

6. 排序数据：使用ORDER BY子句可以对查询结果进行排序。以下是按照年龄从小到大对"users"表中的记录进行排序的示例：

SELECT * FROM users ORDER BY age ASC;

7. 连接表：使用JOIN语句可以将多个表连接起来进行查询。以下是连接"users"表和"orders"表，并检索用户和订单信息的示例：

SELECT users.name, orders.order_number FROM users JOIN orders ON users.id = orders.user_id;

8. 创建索引：使用CREATE INDEX语句可以为表中的列创建索引，以提高查询效率。以下是为"users"表中的name列创建索引的示例：

CREATE INDEX idx_name ON users (name);

9. 删除表和数据库（慎用）：使用DROP TABLE语句可以删除表，使用DROP DATABASE语句可以删除数据库。以下是删除"users"表和"mydatabase"数据库的示例：

DROP TABLE users;
DROP DATABASE mydatabase;

10. 更改表名

rename table 原表名 to 新表名;mysql>  rename table users to t_users;
Query OK, 0 rows affected (0.01 sec)mysql> select *  from t_users;
+----+------+------+-------------------+
| id | name | age  | email             |
+----+------+------+-------------------+| 1 | John |   30 | [e-mail]|2 | Tom  |   25 | [e-mail]|3 | Zyl  |   18 | [e-mail]
+----+------+------+-------------------+
3 rows in set (0.00 sec)mysql> 

11. mysql的表中增加字段

###表t_users 中添加了一个字段address ，类型为 varchar(255)。mysql> alter table t_users add column address varchar(255);
Query OK, 0 rows affected (0.01 sec)

12. 授权
    MySQL8.0 以上将创建账户和赋予权限分开了，需要用两个语句创建账号，赋予远程权限；

###1、创建test用户
create user 'test'@'%' identified by 'Test##2023';###授权所有权限
grant all privileges on *.* to 'test'@'%' with grant option;###部分权限(select, insert)，这个用户只能连接到数据库，并拥有select, insert权限。
mysql>  grant select, insert  on  mydatabase.* to 'test'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> ###验证test用户权限
mysql> show grants for 'test'@'%';mysql> delete from t_users;
ERROR 1142 (42000): DELETE command denied to user 'test'@'localhost' for table 't_users'
mysql> ###---->该用户目前没有DELETE 的权限

五、数据备份和恢复

备份数据库：使用mysqldump命令可以备份整个数据库，例如：

mysqldump -u username -p mydatabase > backup.sql

[root@db-server ~]# mysqldump -u root -p mydatabase > backup.sql
Enter password: 
[root@db-server ~]# ll
total 1964180
-rw-r--r--. 1 root root        2036 Jul 29 17:35 backup.sql

恢复数据库：使用mysql命令可以从备份文件中恢复数据库，例如：

mysql -u username -p mydatabase < backup.sql

###删除表数据
mysql> delete from users;
Query OK, 3 rows affected (0.02 sec)###恢复
[root@db-server ~]# mysql -u root -p mydatabase < backup.sql
Enter password: 
[root@db-server ~]# ###验证
mysql> select * from users;
+----+------+------+-------------------+
| id | name | age  | email             |
+----+------+------+-------------------+| 1 | John |   30 | [e-mail]|2 | Tom  |   25 | [e-mail]|3 | Zyl  |   18 | [e-mail]
+----+------+------+-------------------+
3 rows in set (0.00 sec)mysql> 

导出数据到csv文件：

###创建目录、赋权
[root@db-server ~]# mkdir -p /path/to
[root@db-server ~]# chmod -R 777 /path/to/
[root@db-server ~]# cd /path/
[root@db-server path]# ll
total 0
drwxrwxrwx. 2 root root 6 Jul 29 17:20 to
[root@db-server path]# 

配置 secure_file_priv 变量，在/etc/my.cnf文件中加入

secure_file_priv=/path/to

重启数据库服务：

systemctl restart mysqld

导出数据是将数据库中的数据导出到文件的操作。可以使用以下脚本将users表中的数据导出到users.csv文件中：

mysql> SELECT * INTO OUTFILE '/path/to/users.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM users;
Query OK, 1 row affected (0.00 sec)mysql> 

从csv文件导入数据：

导入数据是将文件中的数据导入到数据库中的操作。可以使用以下脚本将users.csv文件中的数据导入到users表中：

mysql> LOAD DATA INFILE '/path/to/users.csv' INTO TABLE users FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0mysql> 

导出数据到txt文件：

使用SELECT INTO OUTFILE语句可以将查询结果导出为文件，例如：

mysql> SELECT * INTO OUTFILE '/path/to/result.txt' FROM users;
Query OK, 3 rows affected (0.01 sec)mysql> 

从txt文件导入导入数据：

使用LOAD DATA INFILE语句可以将数据从文件导入到表中，例如：

LOAD DATA INFILE '/path/to/result.txt' INTO TABLE users;

导出一个表：

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名[root@db-server ~]# mysqldump -u root -p mydatabase users > mydatabases_users.sql
Enter password: 
[root@db-server ~]# ll
total 1964184
-rw-r--r--. 1 root root        2036 Jul 29 18:07 mydatabases_users.sql

导出一个数据库结构：

###参数： -d 没有数据  --add-drop-table 在每个create语句之前增加一个drop table[root@db-server ~]#  mysqldump -u root -p -d --add-drop-table mydatabase  > mydatabase-tab.sql
Enter password: 
[root@db-server ~]# ll
total 1964196
-rw-r--r--. 1 root root        2036 Jul 29 17:35 backup.sql
-rw-r--r--. 1 root root        2036 Jul 29 18:07 mydatabases_users.sql
-rw-r--r--. 1 root root        1732 Jul 29 18:12 mydatabase-tab.sql

source 命令导入数据：

mysql> delete from users;
Query OK, 3 rows affected (0.00 sec)####备份文件位置
mysql> source /root/backup.sql
Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected, 1 warning (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0Query OK, 0 rows affected (0.04 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> ###验证：
mysql> select * from users;
+----+------+------+-------------------+
| id | name | age  | email             |
+----+------+------+-------------------+| 1 | John |   30 | [e-mail]|2 | Tom  |   25 | [e-mail]|3 | Zyl  |   18 | [e-mail]
+----+------+------+-------------------+
3 rows in set (0.00 sec)mysql> 

注意： mysqldump导出mysql数据库中某个数据库的数据，用mysqldump只要把输出符号换为输入符号。那样导入后，虽说不报错，但是数据库中是没有任何数据的！需要直接通过mysql命令来实现。

恢复数据库：使用mysql命令可以从备份文件中恢复数据库，例如：

mysql -u username -p mydatabase < backup.sql

###删除表数据
mysql> delete from users;
Query OK, 3 rows affected (0.02 sec)###恢复
[root@db-server ~]# mysql -u root -p mydatabase < backup.sql
Enter password: 
[root@db-server ~]# ###验证
mysql> select * from users;
+----+------+------+-------------------+
| id | name | age  | email             |
+----+------+------+-------------------+| 1 | John |   30 | [e-mail]|2 | Tom  |   25 | [e-mail]|3 | Zyl  |   18 | [e-mail]
+----+------+------+-------------------+
3 rows in set (0.00 sec)mysql> 

六、查询与优化

查询数据：使用SELECT语句可以从表中查询数据，例如：

mysql> SELECT * FROM users WHERE id = 1;
+----+------+------+-------------------+
| id | name | age  | email             |
+----+------+------+-------------------+| 1 | John |   30 | [e-mail]
+----+------+------+-------------------+
1 row in set (0.00 sec)mysql> 

优化查询：使用EXPLAIN语句可以分析查询语句的执行计划，例如：

mysql> EXPLAIN SELECT * FROM users WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)mysql>