MySQL数据库基础——学以致用

一、SQL的分类

DDL数据定义语言：create，alter，drop，rename，truncate
DML数据操作语言：insert，delete，update
DQL数据查询语言：select
DCL数据控制语言：commit，rollback，savepoint，grant，revoke

二、DDL数据库操作

2.1 数据库的创建、删除和使用

1. 查询所有数据库

show databases;

2. 查询当前数据库

select database();

3. 创建数据库

create database [if not exists] 数据库名 default charset 字符集;

4. 删除数据库

drop database [if exist] 数据库名;

5. 使用数据库

use database;

2.2 查询、创建表操作

1. 查询当前数据库

show tables;

2. 查询表结构

desc 表名;

3. 查询指定表的建表语句

show create table 表名;

4. 创建表语句

create table 表名(
	字段1 类型 [comment 注释],
	字段2 类型 [comment 注释],
	字段3 类型 [comment 注释],
	...
)[comment 表注释];

# 例子：
create table tb_user(
	id int comment '姓名',
	name varchar(50) comment '名字',
	age int comment '年龄',
	gender varchar(1) comment '性别';
);

2.3 MySQL数据类型

2.4 修改表操作

1. 添加表字段

alter table 表名 add 字段名 类型 [comment 注释];

2. 修改字段的数据类型

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

3. 修改表字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型 [comment注释][约束];

4. 删除表中的字段

alter table 表名 drop 字段名;

5. 修改表名

alter table 表名 rename to 新表名;

6. 删除表

drop table [if exists] 表名;

# 删除指定表，并重新创建该表
truncate table 表名;

三、DML数据库操作

3.1 插入数据

# 给指定字段插入数据
insert into 表名(字段1, 字段2, ...) values (值1, 值2, ...);

# 给全部字段添加数据
insert into 表名 values (值1, 值2, ...);

# 批量添加数据
insert into 表名(字段1, 字段2, ...) values (值1, 值2, ...),(值1, 值2, ...)...;
insert into 表名 values(值1, 值2, ...), (值1, 值2, ...)...

注意：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

3.2 修改数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

注意：如果没有条件，则会修改整张表的所有数据。

3.3 删除数据

delete from 表名 [where 条件];

注意：

1. delete的条件可以有可以没有，如果没有则删除整张表的所有数据。
2. delete语句不能删除某一个字段的值（可以使用update）。

四、DQL数据查询语言

4.1 DQL语法

select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

执行顺序：

4.2 基本查询

# 查询多个字段
select 字段1, 字段2, ... from 表名;
select * from 表名;

# 设置别名
select 字段1 [as 别名1], 字段2 [as 别名2], ... from 表名;

# 去除重复记录
select distinct 字段列表 from 表名;

4.3 条件查询

# 语法
select 字段列表 from 表名 where 条件列表;

# 查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工
select * from emp where gender = '男' and age between 20 and 30 and name like '_ _ _';

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between…and…	在某个范围内（含最小、最大值）
in(…)	在in之后的列表中的值，多选一
like	模糊匹配（_匹配单个字符，%匹配多个字符）
is null 或 <=>	是空

逻辑运算符	功能
and 或 &&	且
or 或 ||	或
not 或 !	非

4.4 聚合函数

1. 什么是聚合函数？

   聚合函数就是将一列数据作为一个整体，进行纵向计算。

2. 常见的聚合函数

   函数	功能
   count	统计数量
   max	最大值
   min	最小值
   avg	平均值
   sum	求和

3. 语法

   select 聚合函数(字段列表) from 表名 [where 条件];
   

   注意：所有的null值不参与聚合函数运算。

4.5 分组查询

# 语法
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

# 根据性别分组，并统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;
# 根据性别分组，并统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;
# 查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) >= 3;

1. 执行顺序：where -> 聚合函数 -> having。
2. 分组之后，查询的字段一般为聚合函数和分组字段，其他字段无任何意义。

where和having的区别:

1. where是分组之前进行过滤，不满足where条件的不参与分组。
2. having是分组之后对结果进行过滤。
3. where不能对聚合函数进行判断，而having可以。

4.6 排序查询

# 语法
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2 ...;

# 根据年龄对公司员工进行升序排序
select * from emp order by age asc;
# 根据年龄对公司员工进行升序排序，如果年龄相同再按照入职时间降序排序
select * from emp order by age asc, entrydate desc;

1. 使用order by进行对查询到的数据排序操作。
2. 排序方式：ASC（升序）【默认】、DESC（降序）。
3. 如果是多字段排序，当第一个字段相同时，再按照第二个字段排序。
4. where需要声明在from后，order by之前。

4.7 分页查询

# 语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;

# 查询第一页的员工数据，每页展示10条
select * from emp limit 10;
# 查询第二页的员工数据，每页展示10条
select * from emp limit 10,10;

1. 起始索引从0开始，起始索引 = （查询页码 - 1）*每页记录数。
2. 分页查询在不同数据库有不同的实现方式，MySQL是limit。
3. 如果查询的是第一页数据，起始索引可省略，直接简写为limit 10。

4.8 多表查询

4.8.1 多表关系

• 一对多（多对一）
• 多对多
• 一对一

4.8.2 多表查询

1. 概述：指从多张表中查询数据。

2. 笛卡尔积：两个集合A，B的所有组合情况。（多表查询时，需要消除无效的笛卡尔积）

3. 连接查询：

   内连接：相当于查询A和B交集的数据。

   左外连接：查询左表所有数据，以及两表交集部分。
   右外连接：查询右表所有数据，以及两表交集部分。

   自连接：当前表与自身的连接查询，自连接必须使用表别名。

4.8.3 内连接

内连接查询语法：内连接查询的是两表交集部分

# 隐式内连接
select 字段列表 from 表1, 表2 where 条件...;

# 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

4.8.4 外连接

左外连接：相当于查询表1的所有数据，包含表1和表2交集部分的数据。

select 字段列表 from 表1 left [outer] join 表2 on 条件...;

右外连接：相当于查询表2的所有数据，包含表1和表2交集部分的数据。

select 字段列表 from 表1 right [outer] join 表2 on 条件...;

4.8.5 自连接

自连接：可以是内连接，也可以是外连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

4.8.6 联合查询

对于unoin查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表A...
union [all]
select 字段列表 from 表B...;

注意：

1. 删去all关键字之后，就对查询的结果集合并后去重。
2. 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

4.8.7 子查询

SQL语句中嵌套select语句，成为嵌套查询，也叫子查询。

# 子查询外部的语句可以是增删改查中的任意一个
select * from t1 where column1 = (select column1 from t2);

根据子查询结果不同，分为

1. 标量子查询（子查询结果是单个值）

子查询返回的结果是单个值（数字、字符串、日期等），常用的操作符：=、<>、>、>=、<、<=.

# 查询销售部的所有员工信息
# a. 查询销售部的部门ID
# b. 根据销售部的部门ID查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

2. 列子查询（子查询结果为一列）

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

# 1. 查询销售部和市场部的所有员工信息
# a. 查询销售部和市场部的部门ID
# b. 根据部门ID查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or '市场部');

# 2. 查询比财务部所有人工资都高的员工信息
# a. 查询所有财务部人员工资
# b. 比财务部所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

3. 行子查询（子查询结果为一行）

子查询返回的结果是一行，常用操作符为=，<>，IN，NOT IN

# 查询与“张无忌”的薪资和直属领导相同的员工信息
# a. 查询张无忌的薪资和直属领导
# b. 查询与张无忌的薪资和直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

4. 表子查询（子查询结果为多行多列）

子查询返回的结果是多行多列，常用操作符：IN

# 查询入职日期是“2006-01-01”之后的员工信息，及其部门信息
# a. 查询入职日期是“2006-01-01”之后的员工信息
# b. 查询这部分员工对应的部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

子查询的位置分为

• where之后
• from之后
• select之后

五、DCL数据库控制语言

5.1 管理用户

# 查询用户
use mysql;
select * from user;

# 创建用户
create user '用户名'@'主机名' identified by '密码';
# 创建用户可以在任意主机访问
create user '用户名'@'%' identified by '密码';

# 修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

# 删除用户
drop user '用户名'@'主机名';

5.2 权限控制

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

# 查询权限
show grants for '用户名'@'主机名';

# 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

# 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

1. 多个权限之间，使用逗号分隔。
2. 授权时，数据库名和表名都可以使用通配符*代表所有。

六、函数

6.1 字符串函数

函数	功能
concat(s1, s2, …sn)	字符串拼接，将s1，s2…，sn拼接成一个字符串
lower(str)	将字符串str转为小写
upper(str)	将字符串str转为大写
lpad(str, n, pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str, n, pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str, start, len)	返回从字符串str从start位置起的len个长度的字符串

# 案例：将员工工号，统一为5位数，不足五位数的在前面补0
update emp set workno = lpad(workno, 5, '0');

6.2 数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回x/y的值
rand()	返回0~1之间的随机数
round(x, y)	求参数x的四舍五入的值，保留y位小数

# 通过数据库函数，生成一个随机验证码
select lpad(round(rand()*1000000, 0), 0, '0');

6.3 日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
day_add(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1, date2)	返回起始时间date1和结束时间date2之间的天数

# 查询所有员工的入职天数，并根据入职天数倒序排序（entrydate是入职时间）
select name, datediff(curdate(), entrydate) as 'entrydates' from emp order by entrydates desc;

6.4 流程控制函数

函数	功能
if(value, t, f)	如果value为true，则返回t，否则返回f
ifnull(value1, value2)	如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end	如果val1为true，返回res1，…否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end	如果expr的值为val1，返回res1，…否则返回default默认值

# IF()
select name, IF(score > 50, 'PASS', 'FAIL') as result from student;
# case when
select name,
	case
		when score > 90 then 'A'
		when score > 80 then 'B'
		when score > 70 then 'C'
	end as grade
from student;

七、约束

7.1 约束的概念和分类

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
• 目的：保证数据库中数据的正确、有效性和完整性。
• 分类：

约束	描述	关键字
非空约束	限制该字段数据不为空	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.16版本之后）	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据一致性和完整性	FOREIGN KEY

7.2 外键约束

1. 添加外键的语法

# 在创建表时添加外键
create table 表名 (字段名 数据类型, ...,[constraint][外键名称] foreign key(外键字段名) references 主表(主表列名));

# 修改时添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

2. 删除外键的语法

# 删除外键
alter table 表名 drop foreign key 外键名称;

八、事务

8.1 事务的概念

事务是一组操作的集合，是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作的请求。即这些操作要么同时成功，要么同时失败。

8.2 事务的操作

方式一：手动提交事务

# 查看事务提交方式
select @@ autocommit; # 如果为1则是自动提交，0则是手动提交

# 设置事务提交方式
set @@ autocommit = 0; # 设置为手动提交

# 提交事务
commit;

# 回滚事务
rollback;

方式二：开启事务

# 开启事务
start transaction; 或 begin;

# 提交事务
commit;

# 回滚事务
rollback;

8.3 事务的四大特性ACID

1. 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
2. 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
3. 隔离性（lsolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
4. 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

8.4 并发事务引发的问题

多个并发事务在执行过程中出现的问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在

8.5 事务的隔离级别

数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。

1. 读未提交：READ UNCOMMITTED
   允许A读取B未提交的修改
2. 读已提交：READ COMMITTED
   要求A只能读取B已提交的修改
3. 可重复读：REPEATABLE READ【MySQL默认】
   确保A可以多次从一个字段中读取到相同的值，即A执行期间禁止其它事务对这个字段进行更新
4. 串行化：SERIALIZABLE
   确保A可以多次从一个表中读取到相同的行，在A执行期间，禁止其它事务对这个表进行添加、更新、删除操作。可以避免任何并发问题，但性能十分低下

能力	脏读	不可重复读	幻读
READ UNCOMMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

# 查看事务的隔离级别
select @@ transaction——isolation;

# 设置事务的隔离级别，session是指会话级别当前窗口有效，global是指全局级别针对所有窗口有效
set [session|global] transaction isolation level { 隔离级别 };