MySQL常用命令详解(MySQL总结)

一、SQL

1. 什么是SQL？

SQL（Structured Query Language）：结构化查询语言。定义了操作所有关系型数据库（如：MySQL、Oracle）的规则，不同的数据库之间会存在不一样的地方。

2. SQL通用语法

1. SQL语句可单行或单行书写，分号结尾
2. 可使用空格与缩进来增强语句的可读性
3. MySQL数据库中的SQL语句不区分大小写
4. 注释：
   * 单行注释：-- 注释内容**（ -- 后必须加一个空格）**
   * 多行注释：/* 注释内容 */

3. SQL分类

1. DDL（Data Definition Language）：数据定义语言。用来操作数据库、表。

2. DML（Data Manipulation Language）：数据操作语言。用来增删改表中的数据。

3. DQL（Data Query Language）：数据查询语言。用来查询表中的数据。

4. DCL（Data Control Language）：数据控制语句。用来管理用户及相关授权。

二、DDL：操作数据库、表

1. 操作数据库（CRUD）

1. C（Create）：创建

1. 创建数据库

create database 数据库名称;

2. 创建数据库并判断是否存在，不存在则创建（防止因数据库已存在，创建时报错影响后续操作）

create database if not exists 数据库名称;

3. 创建数据库并指定字符集

create database 数据库名称 character set 字符集名;

示例：创建 db 数据库，判断是否存在，并指定字符集为 gbk

create database if not exists db character set gbk;

2. R（Retrieve）：查询

1. 查询所有数据库的名称

show databases;

2. 查询某个数据库的创建语句（主要用来查询该数据库的字符集）

show create database 数据库名称;

3. U（Update）：修改

1. 修改数据库的字符集

alter database 数据库名称 character set 字符集名称;

4. D（Delete）：删除

1. 删除数据库

drop database 数据库名称;

2. 判断数据库存在，再删除数据库

drop database if exists 数据库名称;

5. 使用数据库

1. 使用数据库

use 数据库名称;

2. 查询当前正在使用的数据库

select database();

2. 操作表（CRUD）

1. MySQL常用数据类型

• int：整数类型

• double：小数类型

  例：score double(5,2) 表示该数一共 5 位，小数点后有 2 位，如 999.99

• date：日期类型（只包含年月日 YYYY-MM-dd）

• datetime：日期类型（包含年月日时分秒 YYYY-MM-dd HH:mm:ss）

• timestamp：时间戳类型（包含年月日时分秒，如果不给该类型字段赋值，或赋值为 null，则默认使用当前系统时间）

• varchar：字符串类型

  例：name varchar(20) 表示最多 20 个字符的字符串

2. C（Create）：创建

1. 创建表

create table 表名(
	列名1 数据类型1,
    列名2 数据类型2,
    .... ........
    列名n 数据类型n
);

2. 复制表

create table 表名 like 被复制的表名;

3. R（Retrieve）：查询

1. 查询某个数据库中所有的表名

show tables;

2. 查询表结构

desc 表名;

4. U（Update）：修改

1. 修改表名

alter table 表名 rename to 新的表名;

2. 修改表的字符集

alter table 表名 character set 字符集名称;

3. 添加一列（字段）

alter table 表名 add 列名 数据类型;

4. 修改列名、数据类型

alter table 表名 change 列名 新列名 新数据类型;

5. 仅修改数据类型

alter table 表名 modify 列名 新数据类型;

6. 删除列

alter table 表名 drop 列名;

5. D（Delete）：删除

1. 删除表

drop table 表名;

2. 判断表存在，再删除表

drop table if exists 表名;

三、DML：增删改表中的数据

1. 添加数据

insert into 表名(列名1,列名2,...,列名n) values (值1,值2,...,值n);

注意：

1. 列名与值要一一对应
2. 如果表名后不加列名，默认会给所有列添加值

insert into 表名 values (值1,值2,...,值n);

3. 除了数字类型，其他数据类型数据都要用引号（单双皆可）引起来

2. 删除数据

delete from 表名 [where 条件]; -- []表示可选操作

注意：

1. 如果不加 where 条件，则会删除表中所有记录（有多少条记录执行多少次删除操作，效率较低）

delete from 表名;

2. 删除表中所有记录（先删除当前表，再创建一张一模一样的空表，效率较高）

truncate table 表名;

3. 修改数据

update 表名 set  列名1=值1,列名2=值2,...,列名n=值n [where 条件]; -- []表示可选操作

四、DQL：查询表中的数据

1.语法

select 
	字段列表
from
	表名列表
where
	条件列表
group by
	分组条件
having
	分组之后的条件
order by
	排序
limit
	分页限定

2. 基础查询

1. 多个字段的查询

select 字段名1,字段名2,...,字段名n from 表名;

2. 查询所有字段

select * from 表名;

3. 去除重复（两条记录所有字段的值都一样才会去除）

select distinct 字段名1,字段名2,...字段名n from 表名;

4. 可以使用四则运算计算一些列的值

   示例：查询学生的姓名、数学成绩、英语成绩及数学成绩与英语成绩的和

select name,math,english,math+english from student;

5. ifnull(表达式1,表达式2)

   表达式1：需要被判断是否为 null 的字段名

   表达式2：如果字段为 null 则替换为该值

   示例：查询学生的姓名、数学成绩，如果数学成绩为 null 则替换为 0（成绩为 null 不合常理）

select name,ifnull(math,0) from student;

6. 起别名（as）

   示例：查询学生姓名与数学成绩（别名可不用打引号）

select name [as] 姓名,math [as] 数学 from student; -- as可省略

3. 条件查询

1. 常用运算符

• >，<，=，>=，<=，!=，<>
• between…and…
• in (集合)
• like（模糊查询）
• is null，is not null
• and，&&
• or，||
• not，!

2. 使用示例

1. 查询学生表中年龄不等于 20 的记录

select * from student where age!=20; -- 方式一
select * from student where age<>20; -- 方式二

2. 查询学生表中年龄大于等于 20，小于等于 30 的记录

select * from student where age>=20 and age<=30; -- 方式一
select * from student where age between 20 and 30; -- 方式二

3. 查询学生表中年龄为 18，22，25 的记录

select * from student where age=18 or age=22 or age=25; -- 方式一
select * from student where age in (18,22,25); -- 方式二

4. 查询学生表中英语成绩为 null 的记录

select * from student where english=null; -- (×) null值不能用 = 与 != 判断
select * from student where english is null; -- (√)

4. 模糊查询（like）

1. 常用占位符

• _：表示单个任意字符
• %：表示多个任意字符

2. 使用示例

1. 查询学生表中姓氏为马的记录

select * from student where name like '马%';

2. 查询学生表中姓名第二个字为云的记录

select * from student where name like '_云%';

3. 查询学生表中姓名为三个字的记录

select * from student where name like '___';

4. 查询学生表中姓名中包含云的记录

select * from student where name like '%云%';

5. 聚合函数

1. 常用聚合函数

• count：计算个数
• max：计算最大值
• min：计算最小值
• sum：计算和
• avg：计算平均值

2. 使用示例

1. 查询学生表中的纪录总数

-- 聚合函数的计算，一般会排除 null 值，所以一般计算主键
select count(id) from student; -- 方式一
select count(*) from student; -- 方式二
-- 也可使用 ifnull() 函数转换为非 null 值再参与计算
select count(ifnull(math,0)); -- 方式三

2. 查询学生表中数学成绩的平均分

select avg(math) from student;

6. 排序查询

1. 语法

-- 多个排序条件，靠前优先级高
order by 排序字段1 排序方式1,排序字段2 排序方式2,...

2. 排序方式

• asc：升序（不指定排序方式，默认为升序）
• desc：降序

3. 使用示例

1. 查询学生信息，按照数学成绩升序排名，如果数学成绩一样，则按照英语成绩降序排名

select * from student order by math [asc],english desc; -- asc可省略

7.分组查询

1. 语法

group by 分组字段

2. 使用示例

1. 对学生表按性别分组，分别查询男、女生的数学平均分、人数，要求分数低于 70 分的不参与分组，分组后组内人数要大于 2 人

select 
	sex,avg(math),count(id) 人数 -- 给count起别名为人数
from 
	student 
where 
	math>70 
group by 
	sex 
having 
	人数>2; -- 用别名代替count(id)参与条件判断

3. 重点：where 与 having 的区别

1. where 在分组之前进行限定，如果条件不满足，则不参与分组
   having 在分组之后进行限定，如果条件不满足，则结果不会被查询出来
2. where 后不可跟聚合函数
   having 后可以进行聚合函数的判断
总的来说：where 是限定条件，having 是过滤结果

8. 分页查询

1. 语法

-- 开始的索引 = (当前的页码 - 1) * 每页显示的记录条数
limit 开始的索引,每页显示的记录条数

2. 使用示例

1. 每页显示 3 条记录

select * from student limit 0,3; -- 第一页
select * from student limit 3,3; -- 第二页

9. 内连接查询

1. 隐式内连接

select 字段列表 from 表名列表 where 条件列表;

2. 显式内连接

select 字段列表 from 表名1 [inner] join 表名2 on 条件; -- inner可省略

3. 使用示例

1. 查询所有员工信息与对应的部门信息

select * from emp,dept where emp.dept_id=dept.id; -- 隐式内连接
select * from emp join dept on emp.dept_id=dept.id; -- 显示内连接

2. 查询员工的姓名、性别与部门的名称

select 
	t1.name,t1.gender,t2.name -- 别名在SQL语句中的任意位置都可以使用
from 
	emp t1, -- 起别名
	dept t2
where
	t1.dept_id=t2.id;

10. 外连接查询（多表查询）

1. 左外连接

-- 查询左表所有数据以及两张表的交集
select 字段列表 from 表1 left [outer] join 表2 on 条件; -- outer可省略

2. 右外连接

-- 查询右表所有数据以及两张表的交集
select 字段列表 from 表1 right [outer] join 表2 on 条件; -- outer可省略

3. 使用示例

1. 查询所有员工信息，如果员工有所属部门，则查询部门名称，如果没有，则不显示

select 
	t1.*,t2.name
from
	emp t1
left join
	dept t2
on
	t1.dept_id=t2.id;

11. 子查询（多表查询）

1. 子查询的结果为单行单列

• 子查询可以作为条件，使用运算符 >，<，=，>=，<= 判断

2. 子查询的结果为多行单列

• 子查询可以作为条件，使用运算符 in 判断

3. 子查询的结果为多行多列

• 子查询可以作为一张虚拟表

4. 使用示例

1. 查询员工工资小于平均工资的记录

-- 子查询结果为单行单列
select
	* 
from 
	emp 
where 
	emp.salary<(select avg(salary) from emp);

2. 查询财务部与市场部所有员工信息

-- 子查询结果为多行单列
select 
	* 
from 
	emp 
where 
	dept_id in (select id from dept where name in ('财务部','市场部'));

3. 查询员工入职日期为 2020-09-05 之后的员工信息与部门信息

-- 子查询结果为多行多列
select
	*
from 
	dept t1,
	(select * from emp where emp.join_date>'2020-09-05') t2
where
	t1.id=t2.dept_id;
	
-- 隐式内连接方式查询
select 
	* 
from 
	emp t1,
	dept t2 
where 
	t1.dept_id=t2.id 
and 
	t1.join_date>'2020-09-05';

五、DCL：管理用户及相关授权

1. 添加用户

-- 用户存储在 mysql 数据库的 user 表中
create user '用户名'@'主机名' identified by '密码';

2. 删除用户

drop user '用户名'@'主机名'; -- 可用通配符 % 表示所有主机

3. 查询用户

select * from user;

4. 修改用户密码

update user set password=password('新密码') where user='用户名'; -- 方式一
set password for '用户名'@'主机名'=password('新密码'); -- 方式二

5. 查询权限

show grants for '用户名'@'主机名';

6. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on *.* to 'admin'@'%'; -- 给 admin 用户授予所有权限

7. 撤销权限

revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名';

重点：MySQL 中忘记了 root 用户的密码？

1. cmd（管理员方式启动）--> 键入 net stop mysql 回车（停止 MySQL 服务，8.x 版本的为 net stop mysql80）
2. 键入 mysqld --skip-grand-tables 回车（使用无验证方式启动 MySQL 服务）
3. 打开新的 cmd 窗口，键入 mysql 回车登录
4. 键入 use mysql 回车（使用 mysql 数据库）
5. 键入 update user set password=password('新密码') where user='root'; 回车（修改 root 用户密码）
6. 关闭所有 cmd 窗口
7. 打开任务管理器，手动结束 mysql.exe 的进程
8. 启动 mysql 服务
9. 使用新密码登录

六、约束

1. 非空约束（not null）

1. 创建表时添加非空约束

-- 添加了非空约束的字段值不能为 null
create table 表名(
	字段名 数据类型 not null;
);

2. 给已存在的表中字段添加非空约束

alter table 表名 modify 字段名 数据类型 not null;

3. 删除非空约束

alter table 表名 modify 字段名 数据类型;

2. 唯一约束（unique）

1. 创建表时添加唯一约束

-- 添加了非空约束的字段值不能重复（可以为 null，但最多只能有一条记录该字段为 null）
create table 表名(
	字段名 数据类型 unique;
);

2. 给已存在的表中字段添加唯一约束

alter table 表名 modify 字段名 数据类型 unique;

3. 删除唯一约束

alter table 表名 drop index 字段名;

3. 主键约束（primary key）

1. 创建表时添加主键约束

-- 添加了主键约束的字段值非空（not null）且唯一（unique）
create table 表名(
	字段名 数据类型 primary key [auto_increment]; --[]设置主键自增长（相对于上一个数据自增长）
);

2. 给已存在的表中字段添加主键约束

--[]设置主键自增长（相对于上一个数据自增长）
alter table 表名 modify 字段名 数据类型 primary key [auto_increment];

3. 删除主键约束

alter table 表名 drop primary key;

4. 外键约束（foreign key）

1. 创建表时添加外键约束

-- 通过添加外键约束，让表与表之间产生联系，从而保证数据的正确性
create table 表名(
	字段名1 数据类型1,
    字段名2 数据类型2,
    ...... ........
    字段名n 数据类型n,
    [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名)
); -- []为可选操作，如未给出自定义外键名，MySQL会给出唯一外键名

2. 给已存在的表中字段添加主键约束

-- []为可选操作，如未给出自定义外键名，MySQL会给出唯一外键名
alter table 表名 add [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名);

3. 删除外键约束

alter table 表名 drop foreign key 外键名;

4. 级联操作

-- 当添加级联操作后，主表字段值发生变化时，外键关联字段会发生对应变化
-- 在添加外键约束的语句后添加级联操作
on delete cascade -- 级联删除
on update cascade -- 级联更新
-- 示例：
alter table 表名 add [constraint 自定义外键名] foreign key (外键字段名) references 主表名(主表字段名) [on delete cascade] [on update cascade]; -- []为可选操作

七、表设计

1. 多表之间的关系实现

1. 一对一

• 关系示例：人与身份证（一个人对应一张身份证，一张身份证对应一个人）

• 实现方式：在任意一方添加唯一（unique）外键指向另一方主键

2. 一对多/多对一

• 关系示例：员工与部门（一个员工属于一个部门，一个部门有多个员工）

• 实现方式：在多的一方建立外键，指向一的一方的主键

3. 多对多

• 关系示例：学生与老师（一个学生有多名老师，一名老师教多名学生）

• 实现方式：借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键（这两个字段不能同时重复，需要作为联合主键 primary key(字段1,字段2) ）

2. 数据库设计的范式

1. 几个概念

• 函数依赖：A -> B，如果通过 A 属性（属性组）的值，可以唯一确定 B 的值，则称 B 依赖于 A（如：学号 -> 姓名；(学号，课程名) -> 分数）
• 完全函数依赖：A -> B，如果 A 是一个属性组，B 属性值的确定需要依赖于 A 属性组中所有的属性值，则称 B 完全依赖于 A（如：(学号，课程名) -> 分数）
• 部分函数依赖：A -> B，如果 A 是一个属性组，B 属性值的确定只需要依赖 A 属性组中某一些值，则称 B 部分依赖于 A（如：(学号，课程名) -> 姓名）
• 传递函数依赖：A -> B，B -> C，如果通过 A 属性（属性组）的值，可以唯一确定 B 属性（属性组）的值，再通过 B 属性（属性组）的值，可以唯一确定 C 属性的值，则称 C 传递依赖于 A （如：学号 -> 系名，系名 -> 系主任）
• 码（候选码）：如果在一张表中，一个属性（属性组），被其他所有属性完全依赖，则称这个属性（属性组）为该表的码
• 主属性（主码）：码属性组中的所有属性
• 非主属性：出了码属性组之外的属性

2. 第一范式（1NF）

每一列都是不可分割的原子数据项

3. 第二范式（2NF）

在 1NF 的基础上，非码属性必须完全依赖于码（在 1NF 的基础上消除非主属性对主码的部分函数依赖）

4. 第三范式（3NF）

在 2NF 的基础上，任何非主属性不依赖于其他非主属性（在 2NF 的基础上消除传递依赖）

八、事务

1. 概念

如果一个包含多个业务步骤的业务操作，被事务管理，则这些操作，要么同时成功，要么同时失败

2. 操作

-- 在开启事务后，回滚或提交之前的操作所导致的数据变化都是临时变化，不影响真实数据（有误，则回滚；无误，则提交）
-- 开启事务：
start transaction;
-- 回滚事务：
rollback;
-- 提交事务：
commit;

3. 事务提交的两种方式

1. 自动提交（MySQL 默认）：每条 DML 语句后都会自动提交一次事务
2. 手动提交（Oracle 默认）：需要先开启事务，再提交
3. 查看事务默认提交方式

select @@autocommit; -- 1，自动提交；0，手动提交

4. 修改事务默认提交方式

set @@autocommit=0; -- 1，自动提交；0，手动提交

4. 事务的四大特征

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
• 持久性：当事务提交或回滚后，数据库会持久化地保存数据
• 隔离性：多个事务之间相互独立
• 一致性：事务操作前后，数据总量不变

5. 事务的隔离级别

1. 概念

多个事务之间有隔离性，相互独立。但多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这些问题

2. 存在问题

   • 脏读：一个事务，读取到另一个事务中还没有提交的数据
   • 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
   • 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，前一个事务无法查询到自己的修改

3. 隔离级别（从前往后，安全性越来越高，效率越来越低）

   • read uncommitted：读未提交

     产生的问题：脏读、不可重复读、幻读

   • read committed：读已提交（Oracle默认隔离级别）

     产生的问题：不可重复读、幻读

   • repeatable read：可重复读（MySQL默认隔离级别）

     产生的问题：幻读

   • serializable：串行化

     可以解决所有问题

4. 查询数据库隔离级别

select @@tx_isolation;

5. 修改数据库隔离级别

set global transaction isolation level 级别字符串;

九、数据库的备份与还原

1. 备份

mysqldump -u用户名 -p密码 数据库名 > 保存的路径

2. 还原

1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件
	source 文件路径