MySQL命令行速查手册（持续更新ing...）

原创已于 2025-03-11 16:59:47 修改 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #SQL #SQL语言 #关系数据库

于 2023-06-05 10:24:00 首次发布

编程学习笔记专栏收录该内容

190 篇文章

订阅专栏

本文是一篇关于MySQL基础的博文目录，涵盖了从基本的语法常识到用户管理和数据库、表的操作，包括创建、修改和查询。还涉及SQL函数的使用和常见的错误信息解析，为学习和使用MySQL提供了一站式的参考。

诸神缄默不语-个人优快云博文目录

文章目录

1. MySQL语法常识
2. 用户管理
3. 数据库管理
4. 表管理
5. SQL函数
6. 导入和导出数据
- 6.1 MySQL导出数据
- 6.2 MariaDB执行MySQL导出SQL报字符集排序规则错误问题
7. 配置参数
8. 常见报错信息
9. 本文撰写过程中使用的其他参考资料

1. MySQL语法常识

大多命令需以;作为结尾
（以下'localhost'都可以替换成实际IP地址）
``和’'的区别应该不大
执行SQL命令文件，如果在MySQL环境中可以直接用source sql文件名;
需要注意Windows文件路径中的\需要替换成/（替换成\\也能运行但会报警告信息）

2. 用户管理

修改密码：ALTER USER 'user_name'@'localhost' IDENTIFIED BY 'New-Password';
创建新用户：create user 'New-Username'@'localhost' identified by 'New-Password';
@后面的参数是指可以登录的IP地址：如果是"%"，则表示可以在任意IP登录。（注意MySQL很狗的一点是接不同的@就算是不同的用户，所以需要不同的授权才行，所以别用*了没用的）
将某一数据库的所有权力赋给某一用户：grant all privileges on database_name.* to 'user_name'@'localhost';
flush privileges;
查看指定用户的权限：SHOW GRANTS FOR 'user_name'@'localhost';

3. 数据库管理

查看数据库：show databases;
创建新数据库：create DATABASE New-DatabaseName;
转换数据库环境：use database_name（也可以在进入MySQL命令行环境时用-p指定）
查看数据库中所有表：show tables;

4. 表管理

1. 创建表

（我后来发现如果用Flask的话直接用Python语言创建也挺好的，这个可以查看我写的另一篇博文：在云服务器上安装MySQL (MariaDB) 数据库并与Python连接和互动）

示例

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `phone_number` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    `email` varchar(255) COLLATE utf8_bin,
    `memo` varchar(255) COLLATE utf8_bin,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
AUTO_INCREMENT=1 ;

约束
1. not null：不能为空
2. auto_increment：会在插入对应样本时自动递增对应的值，1,2,3,…这样下去
3. default：对每个样本，给该列一个默认值
  1. （timestamp）default current_timestamp
属性
1. int()
2. varchar()
3. text
4. timestamp
5. 布尔类型：MySQL没有官方的布尔类型，用TINYINT(1)就行。举例：
  alter table Papersadd columnis_legalAI TINYINT(1) default 0;¹
6. 字符串编码：不要用“utf8”，用“utf8mb4”（这个是真正的UTF-8编码，“utf8”是MySQL自己的编码）²

2. 修改表

增加列

alter table `users` add column `nickname` varchar(20) ;

3. 查询表

查询全部内容：select * from 表名
limit 限制返回行数：SELECT * FROM table_name LIMIT 10;（返回表中前10行：基于表中数据的物理存储顺序的）
查询最后10条数据：SELECT * FROM table_name ORDER BY id DESC LIMIT 10;（id是一个自增的主键，也可以替换成其他顺序递增的列）
union 组合查询语句的结果集（每个SELECT语句必须具有相同的列数，UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名）默认不允许重复
```
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```

union all允许重复

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

where 条件查询
SELECT * FROM Papers WHERE LOWER(title) = LOWER('这是一个示例标题')
查询一个表中的记录数：SELECT COUNT(*) FROM table_name;
请注意，COUNT(*)函数会计算表中的所有记录，包括那些值为NULL的记录。如果你只想要计算某个特定列的非NULL值的数量，你可以使用COUNT(column_name)，其中column_name是你想要计算的列的名称。
distinct 去重
SELECT DISTINCT 列名 FROM your_table;
将查询内容创建新表：create table new_table as 查询语句

4. 删除表

delete（不可逆）
删除表中的所有数据（但不删除表本身）：DELETE FROM table_name;
truncate（不可逆）：你想要删除表中的所有数据，并且重置所有的自增字段

TRUNCATE TABLE table_name;

5. SQL函数

lower()
REGEXP
replace(列名,被替换的字符串,替换后的字符串)

6. 导入和导出数据

6.1 MySQL导出数据

在terminal中运行代码：mysqldump -u root -p database_name > sql_file_name.sql
需要输入密码

如果你希望导出所有数据库，可以使用 --all-databases 参数：
mysqldump -u root -p --all-databases > sql_file_name.sql

如果导入数据的数据库也是MySQL的话，直接用source命令执行SQL代码就行，也可以用mysql -u cloud_user -p -h cloud_server_ip your_cloud_database < /path/to/backup.sql
在这里插入图片描述
如果是MariaDB的话，直接执行导入可能会报字符集的错，报错原因及其解决方案如下一小节所示。

此外还需要注意的是如果是从Windows系统的MySQL直接导出，可能会出现表名全部转换为小写的问题（因为Windows系统MySQL默认大小写不敏感），导入Linux系统的MySQL可能就会出问题，因为Linux系统MySQL默认大小写敏感。解决方案是要么把两台机子上的lower_case_table_names参数设置为一样的（见本文第7节），要么跟我一样直接手动把所有表名修改得跟原来一样……

6.2 MariaDB执行MySQL导出SQL报字符集排序规则错误问题

具体的报错信息是：ERROR 1273 (HY000) at line 25 in file: sql_file_name.sql': Unknown collation: 'utf8mb4_0900_ai_ci'
解决方案：将SQL文件中所有utf8mb4_0900_ai_ci都改成utf8mb4_general_ci，然后退出MySQL环境重新执行source命令（如果不退出MySQL环境好像不会刷新SQL命令）

原因：MariaDB 默认不支持 utf8mb4_0900_ai_ci，这个排序规则是 MySQL 8.0 引入的。
MariaDB 支持的字符集排序规则有 utf8mb4_general_ci 或 utf8mb4_unicode_ci，改成这几个都可以