MySQL | 常用命令示例
- 一、启停MySQL数据库服务
- 二、连接MySQL数据库
- 三、创建和管理数据库
- 四、创建和管理数据表
- 五、数据备份和恢复
- 六、查询与优化
MySQL是一款常用的关系型数据库管理系统,广泛应用于各个领域。在使用MySQL时,我们经常需要编写一些常用脚本来进行数据操作和管理。本文将介绍一些常用的MySQL脚本,帮助更好地使用MySQL。
一、启停MySQL数据库服务
- 启动MySQL数据库服务
systemctl start mysqld
- 停止MySQL数据库服务
systemctl stop mysqld
- MySQL数据库服务状态查看
systemctl status mysqld
- MySQL数据库服务开机自启
systemctl enable mysqld
- MySQL数据库服务禁止开机自启
systemctl disable mysqld
二、连接MySQL数据库
在使用MySQL之前,我们需要先连接到数据库。可以使用以下脚本进行连接:
- 使用用户名和密码连接MySQL数据库
mysql -u username -p password
或者
mysql -u username -p
其中,username为数据库用户名,password为数据库密码。
- 使用主机名、用户名和密码连接MySQL数据库
mysql -h 主机名 -P 端口号 -u 用户名 -p 密码
或者
mysql -h 主机名 -P 端口号 -u 用户名 -p
- 使用数据库
连接到 MySQL 数据库后,可能有多个可以操作的数据库,可使用use 命令选择你要操作的数据库。
use 数据库名;
- 关闭MySQL数据库连接的SQL语句
exit;
或者
quit;
三、创建和管理数据库
- 创建数据库:使用CREATE DATABASE语句可以创建新的数据库(mydatabase),例如:
mysql> CREATE DATABASE mydatabase;
Query OK, 1 row affected (0.00 sec)
mysql>
- 删除数据库(慎用):使用DROP DATABASE语句可以删除指定的数据库(mydatabase),例如:
mysql> DROP DATABASE mydatabase;
Query OK, 0 rows affected (0.01 sec)
mysql>
- 查看数据库:使用SHOW DATABASES语句可以列出当前所有的数据库,例如:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydatabase |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
- 查看当前使用的数据库:
mysql> select database();
+------------+
| database() |
+------------+
| mydatabase |
+------------+
1 row in set (0.00 sec)
mysql>
- 查看当前数据库包含的表信息
mysql> show tables;
+----------------------+
| Tables_in_mydatabase |
+----------------------+
| users |
+----------------------+
1 row in set (0.00 sec)
mysql>
- 获取表结构
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>
- 查看数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
mysql>
四、创建和管理数据表
- 创建数据表
数据表是数据库中存储数据的主要方式。可以使用以下脚本创建一个名为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>
- 插入数据
插入数据是将数据添加到数据库中的关键操作。可以使用以下脚本向users
表中插入一条数据:
mysql> INSERT INTO users (name, age, email) VALUES ('John', 25, 'john@example.com');
Query OK, 1 row affected (0.01 sec)
mysql>
- 查询数据
查询数据是使用MySQL的常见操作之一。可以使用以下脚本查询users
表中的所有数据:
mysql> SELECT * FROM users;
+----+------+------+------------------+
| id | name | age | email |
+----+------+------+------------------+
| 1 | John | 25 | john@example.com |
+----+------+------+------------------+
1 row in set (0.00 sec)
mysql>
- 更新数据
更新数据是在数据库中修改现有数据的操作。可以使用以下脚本将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: 0
mysql> SELECT * FROM users;
+----+------+------+------------------+
| id | name | age | email |
+----+------+------+------------------+
| 1 | John | 30 | john@example.com |
+----+------+------+------------------+
1 row in set (0.00 sec)
mysql>
- 删除数据
删除数据是从数据库中删除不再需要的数据的操作。可以使用以下脚本删除users
表中id
为1的记录:
DELETE FROM users WHERE id = 1;
- 排序数据:使用ORDER BY子句可以对查询结果进行排序。以下是按照年龄从小到大对"users"表中的记录进行排序的示例:
SELECT * FROM users ORDER BY age ASC;
- 连接表:使用JOIN语句可以将多个表连接起来进行查询。以下是连接"users"表和"orders"表,并检索用户和订单信息的示例:
SELECT users.name, orders.order_number FROM users JOIN orders ON users.id = orders.user_id;
- 创建索引:使用CREATE INDEX语句可以为表中的列创建索引,以提高查询效率。以下是为"users"表中的name列创建索引的示例:
CREATE INDEX idx_name ON users (name);
- 删除表和数据库(慎用):使用DROP TABLE语句可以删除表,使用DROP DATABASE语句可以删除数据库。以下是删除"users"表和"mydatabase"数据库的示例:
DROP TABLE users;
DROP DATABASE mydatabase;
- 更改表名
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 | john.com
|2 | Tom | 25 | tom.com
|3 | Zyl | 18 | zyl.com
+----+------+------+-------------------+
3 rows in set (0.00 sec)
mysql>
- mysql的表中增加字段
###表t_users 中添加了一个字段address ,类型为 varchar(255)。
mysql> alter table t_users add column address varchar(255);
Query OK, 0 rows affected (0.01 sec)
- 授权
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-server ~]# mysqldump -u root -p mydatabase > backup.sql
Enter password:
[root-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-server ~]# mysql -u root -p mydatabase < backup.sql
Enter password:
[root-server ~]#
###验证
mysql> select * from users;
+----+------+------+-------------------+
| id | name | age | email |
+----+------+------+-------------------+
| 1 | John | 30 | john.com
|2 | Tom | 25 | tom.com
|3 | Zyl | 18 | zyl.com
+----+------+------+-------------------+
3 rows in set (0.00 sec)
mysql>
导出数据到csv文件:
###创建目录、赋权
[root-server ~]# mkdir -p /path/to
[root-server ~]# chmod -R 777 /path/to/
[root-server ~]# cd /path/
[root-server path]# ll
total 0
drwxrwxrwx. 2 root root 6 Jul 29 17:20 to
[root-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: 0
mysql>
导出数据到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-server ~]# mysqldump -u root -p mydatabase users > mydatabases_users.sql
Enter password:
[root-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-server ~]# mysqldump -u root -p -d --add-drop-table mydatabase > mydatabase-tab.sql
Enter password:
[root-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: 0
Query 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 | john.com
|2 | Tom | 25 | tom.com
|3 | Zyl | 18 | zyl.com
+----+------+------+-------------------+
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-server ~]# mysql -u root -p mydatabase < backup.sql
Enter password:
[root-server ~]#
###验证
mysql> select * from users;
+----+------+------+-------------------+
| id | name | age | email |
+----+------+------+-------------------+
| 1 | John | 30 | john.com
|2 | Tom | 25 | tom.com
|3 | Zyl | 18 | zyl.com
+----+------+------+-------------------+
3 rows in set (0.00 sec)
mysql>
六、查询与优化
查询数据:使用SELECT语句可以从表中查询数据,例如:
mysql> SELECT * FROM users WHERE id = 1;
+----+------+------+-------------------+
| id | name | age | email |
+----+------+------+-------------------+
| 1 | John | 30 | john@example.com
+----+------+------+-------------------+
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>