目录
SQL简介
- SQL语句可以单行或多行书写,以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
- 注释:
- 单行注释:-- 注释内容 或 # 注释内容
- 多行注释:/* 注释内容 */
分类 | 全称 | 说明 |
DDL |
Data Definition
Language
|
数据定义语言,用来定义数据库对象
(
数据库,表, 字段)
|
DML |
Data Manipulation
Language
|
数据操作语言,用来对数据库表中的数据进行增删改
|
DQL |
Data Query Language
|
数据查询语言,用来查询数据库中表的记录
|
DCL |
Data Control Language
|
数据控制语言,用来创建数据库用户、控制数据库的访问权限
|
1.DDL
Data Definition Language ,数据定义语言,用来定义数据库对象 ( 数据库,表,字段 ) 。
1.1 数据库操作
1.1.1 查询数据库
show databases ;
1.1.2 查询当前数据库
select database() ;
1.1.3 创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;
1.1.4 使用/切换数据库
use 数据库名 ;
1.1.5 删除数据库
drop database [ if exists ] 数据库名 ;
1.2 表的操作
1.2.1 创建表
1.2.1.1语法
create table 表名(
字段1 字段1类型 [约束] [comment 字段1注释 ],
字段2 字段2类型 [约束] [comment 字段2注释 ],
......
字段n 字段n类型 [约束] [comment 字段n注释 ]
) [ comment 表注释 ] ;
1.2.1.2 约束
概念:所谓约束就是作用在表中字段上的规则,用于限制存储在表中的数据。
作用:就是来保证数据库当中数据的正确性、有效性和完整性。(后面的学习会验证这些)
在MySQL数据库当中,提供了以下5种约束:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段值不能为null | not null |
唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key |
默认约束 | 保存数据时,如果未指定该字段值,则采用默认值 | default |
检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | check |
外键约束 | 让两张表的数据建立连接,保证数据的一致性和完整性 | foreign key |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
案例:建立如下要求的表:
字段名 | 字段含义 | 字段类型 | 约束条件 | 约束关键字 |
id | ID唯一 标识 | int | 主键,并且自动增长 | PRIMARY KEY, AUTO_INCREMENT |
name | 姓名 | varchar(10) | 不为空,并且唯一 | NOT NULL , UNIQUE |
age | 年龄 | int | 大于0,并且小于等于120 | CHECK |
status | 状态 | char(1) | 如果没有指定该值,默认为1 | DEFAULT |
gender | 性别 | char(1) | 无 |
CREATE TABLE user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
status char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);
外键约束
外键:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
添加外键
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) );
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
行为 | 说明 |
NO ACTION | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
RESTRICT | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不 允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
CASCADE | 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则 也删除/更新外键在子表中的记录。 |
SET NULL | 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表 中该外键值为null(这就要求该外键允许取null)。 |
SET DEFAULT | 父表有变更时,子表将外键列设置成一个默认的值 (Innodb不支持) |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
1.2.1.3 数据类型
在上面建表语句中,我们在指定字段的数据类型时,用到了int 、varchar、char,那么在MySQL中除了以上的数据类型,还有哪些常见的数据类型呢? 接下来,我们就来详细介绍一下MySQL的数据类型。
MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型。
数值类型
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|
TINYINT(tinyint) | 1byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT(smallint) | 2bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT(mediumint) | 3bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT/INTEGER(int/integer) | 4bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT(bigint) | 8bytes | (-2^63,2^63-1) | (0,2^64-1) | 极大整数值 |
FLOAT(float) | 4bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE(double) | 8bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
DECIMAL(decimal) | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
示例: 年龄字段 ---不会出现负数, 而且人的年龄不会太大 age tinyint unsigned 分数 ---总分100分, 最多出现一位小数 score double(4,1)
字符串类型
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char 与 varchar 都可以描述字符串,char是定长字符串,指定长度多长,就占用多少个字符,和字段值的长度无关 。而varchar是变长字符串,指定的长度为最大占用长度 。相对来说,char的性能会更高些,但浪费空间。varchar的性能会低些,但节约空间。
示例: 用户名 username ---长度不定, 最长不会超过50 username varchar(50) 手机号 phone ---固定长度为11 phone char(11)
日期时间类型
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
示例: 生日字段 birthday ---生日只需要年月日 birthday date 创建时间 createtime --- 需要精确到时分秒 createtime datetime
1.2.2 查询表
1.2.2.1 查询当前数据库所有表
show tables;
1.2.2.2 查看指定表结构
desc 表名 ;
#可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息
1.2.2.3 查询指定表的建表语句
show create table 表名 ;
1.3.3 修改表
1.3.3.1 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
1.3.3.2 修改数据类型
修改字段的数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段的数据类型
alter table 表名 change 旧字段名 新字段名 数据类型(长度) [comment 注释] [约束];
1.3.3.3 删除字段
alter table 表名 drop 字段名;
1.3.3.4 修改表名
rename table 表名 to 新表名;
1.3.4 删除表
drop table [ if exists ] 表名;
if exists :只有表名存在时才会删除该表,表名不存在,则不执行删除操作(如果不加该参数项,删除一张不存在的表,执行将会报错)。
2.DML
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增、删、改操作。
-
添加数据(INSERT)
-
修改数据(UPDATE)
-
删除数据(DELETE)
2.1 增加(insert)
2.1.1向指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
2.1.2 全部字段添加数据
insert into 表名 values (值1, 值2, ...);
2.1.3批量添加数据(指定字段)
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
2.1.4批量添加数据(全部字段)
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
Insert操作的注意事项:
- 插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引号中。
- 插入的数据大小,应该在字段的规定范围内。
2.2 修改(update)
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
注意事项:
-
修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
-
在修改数据时,一般需要同时修改公共字段update_time,将其修改为当前操作时间。
2.3 删除(delete)
delete from 表名 [where 条件] ;
案例:删除tb_emp表中id为1的员工
delete from tb_emp where id = 1;
注意事项:
• DELETE 语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE,将该字段值置为NULL即可)。
• 当进行删除全部数据操作时,会提示询问是否确认删除所有数据,直接点击Execute即可。
3.DQL
语法结构
SELECT ④
字段列表
FROM ①
表名列表
WHERE ②
条件列表
GROUP BY ③
分组字段列表
HAVING
分组后条件列表
ORDER BY ⑤
排序字段列表
LIMIT ⑥
分页参数
-
基本查询(不带任何条件)
-
条件查询(where)
-
分组查询(group by)
-
排序查询(order by)
-
分页查询(limit)
①②③④⑤⑥为DQL的执行顺序
3.1 基本查询(不带任何条件)
3.1.1 查询多个字段
select 字段1, 字段2, 字段3 from 表名;
3.1.2 查询所有字段
select * from 表名;
*
号代表查询所有字段,在实际开发中尽量少用(不直观、影响效率)
3.1.3 设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
as 可以省略
3.1.4 去除重复记录
select distinct 字段列表 from 表名;
3.2 条件查询
select 字段列表 from 表名 where 条件列表 ;
-- 条件列表:意味着可以有多个条件
学习条件查询就是学习条件的构建方式,而在SQL语句当中构造条件的运算符分为两类:
-
比较运算符
-
逻辑运算符
常用的比较运算符
如下:
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ... and ... | 在某个范围之内(含最小、最大值) |
in(...) | 在in之后的列表中的值,多选一 |
like 占位符 | 模糊匹配( _ 匹配单个字符, % 匹配任意个字符) |
is null | 是null |
常用的逻辑运算符
如下:
逻辑运算符 | 功能 |
---|---|
and 或 && | 并且 (多个条件同时成立) |
or 或 || | 或者 (多个条件任意一个成立) |
not 或 ! | 非 , 不是 |
3.4聚合函数
之前我们做的查询都是横向查询,就是根据条件一行一行的进行判断,而使用聚合函数查询就是纵向查询,它是对一列的值进行计算,然后返回一个结果值。(将一列数据作为一个整体,进行纵向计算)
语法:
select 聚合函数(字段列表) from 表名 ;
注意 : 聚合函数会忽略空值,对NULL值不作为统计。
常用聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
count :按照列去统计有多少行数据。
在根据指定的列统计的时候,如果这一列中有null的行,该行不会被统计在其中。
sum :计算指定列的数值和,如果不是数值类型,那么计算结果为0
max :计算指定列的最大值
min :计算指定列的最小值
avg :计算指定列的平均值
3.4分组查询(group by)
分组: 按照某一列或者某几列,把相同的数据进行合并输出。
分组其实就是按列进行分类(指定列下相同的数据归为一类),然后可以对分类完的数据进行合并计算。
分组查询通常会使用聚合函数进行计算。
语法:
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where和having的区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
案例:查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job, count(*)
from tb_emp
where entrydate <= '2015-01-01' -- 分组前条件
group by job -- 按照job字段分组
having count(*) >= 2; -- 分组后条件
3.5排序查询(order by)
排序在日常开发中是非常常见的一个操作,有升序排序,也有降序排序。
语法:
select 字段列表
from 表名
[where 条件列表]
[group by 分组字段 ][having 分组后条件列表]
order by 字段1 排序方式1 , 字段2 排序方式2 … ;
排序方式:
- ASC :升序(默认值)
- DESC:降序
案例:根据性别分组 , 统计男性和女性员工的数量
select gender, count(*)
from tb_emp
group by gender; -- 按照gender字段进行分组(gender字段下相同的数据归为一组)
注意事项:
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
3.6 分页查询(limit)
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
注意事项:
- 起始索引从0开始,起始索引 = (查询页码 - 1)* 每页显示记录数。
- 分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT。
- 如果查询的是第一页数据,起始索引可以省略,直接简写为 limit 10。
4.DCL
4.1管理用户
4.1.1 查询用户
use mysql;
select * from user;
4.1.2 创建用户
create user '用户名'@'主机名' identified by '密码';
案例1:创建用户name1, 只能够在当前主机localhost访问, 密码123456;
create user 'name1'@'localhost' identified by '123456';
案例2:创建用户name2, 可以在任意主机访问该数据库, 密码123456;
create user 'name2'@'%' identified by '123456';
4.1.3修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
案例:修改用户name2的访问密码为1234;
alter user 'name2'@'%' identified with mysql_native_password by '1234';
4.1.4 删除用户
drop user '用户名'@'主机名';
注意事项:
- 在MySQL中需要通过用户名@主机名的方式,来唯一标识一个用户。
- 这类SQL开发人员操作的比较少,主要是DBA( Database Administrator 数据库 管理员)使用。
4.2权限控制
MySQL中定义了很多种权限,但是常用的就以下几种:
权限 | 说明 |
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询权限 |
INSERT | 插入权限 |
UPDATE | 修改权限 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
4.2.1 查询权限
show grants for '用户名'@'主机名';
案例:查询 'name2'@'%' 用户的权限
show grants for 'name2'@'%';
4.2.2授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
案例:授予 'name2'@'%' 用户db01数据库所有表的所有操作权限
grant all on db01.* to 'name2'@'%';
4.2.3撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
案例:撤销 'name2'@'%' 用户的db01数据库的所有权限
revoke all on db01.* from 'name2'@'%';
注意事项:
- 多个权限之间,使用逗号分隔
- 授权时, 数据库名和表名可以使用 * 进行通配,代表所有。
5.常见函数
5.1字符串函数
MySQL中内置了很多字符串函数,常用的几个如下:
函数 | 功能 |
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个长度的字符串 |
案例:将企业员工的工号(workId),统一为5位数,目前不足5位数的全部在前面补0。比如: 1号员 工的工号应该为00001。
update emp set workId= lpad(workId,5,'0');
5.2数值函数
常见的数值函数如下:
函数 | 功能 |
CEIL(x) | 向上取整 |
FLOOR(x) | 向下取整 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机数 |
ROUND(x,y) | 求参数x的四舍五入的值,保留y位小数 |
5.3日期函数
常见的日期函数如下:
函数 | 功能 |
CURDATE() | 返回当前日期(年-月-日 2024-09-10) |
CURTIME() | 返回当前时间 (时-分-秒 17:06:09) |
NOW() | 返回当前日期和时间(年-月-日 时-分-秒2024-09-10 17:07:45) |
YEAR(date) | 获取指定date的年份 |
MONTH(date) | 获取指定date的月份 |
DAY(date) | 获取指定date的日期 |
DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
DATEDIFF(date1,date2) | 返回起始时间date1 和 结束时间date2之间的天数 |
案例:计算当前日期之后的30天的日期
SELECT DATE_ADD(NOW(),INTERVAL 30 DAY);
5.4流程控制函数
常见的流程控制函数如下:
函数 | 功能 |
IF(value , t , f) | 如果value为true,则返回t,否则返回 f |
IFNULL(value1 , value2) | 如果value1不为null(注意是不为null,不是 '' ),返回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默认值 |
案例1:查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
SELECT
name,
(CASE workaddress
WHEN '北京' THEN '一线城市'
WHEN '上海' THEN '一线城市'
ELSE '二线城市'
END) as '工作地址'
FROM emp;
案例2:查询学生的成绩,(成绩 >= 85 --------> 优秀,成绩 >= 60 --------> 及格 ,其他 --------> 不及格 )
select
name,
(CASE WHEN math >= '85' THEN '优秀'
WHEN math >= '60' THEN '及格'
ELSE '不及格'
END) as '数学',
(CASE WHEN english >= '85' THEN '优秀'
WHEN math >= '60' THEN '及格'
ELSE '不及格'
END) as '英语',
(CASE WHEN chinese >= '85' THEN '优秀'
WHEN math >= '60' THEN '及格'
ELSE '不及格'
END) as '语文'
FROM score;
6.多表查询
6.1多表关系
项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结 构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:
- 一对多(多对一)
- 多对多
- 一对一
6.1.1一对多关系
- 案例: 部门 与 员工的关系
- 关系: 一个部门对应多个员工,一个员工对应一个部门
- 实现: 在多的一方建立外键,指向一的一方的主键
6.1.2多对多关系
- 案例: 学生 与 课程的关系
- 关系: 一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现: 建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
6.1.3一对一关系
- 案例: 用户与用户详情的关系
- 关系: 一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另 一张表中,以提升操作效率
- 实现: 在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
6.2多表查询分类
6.2.1连接查询
6.2.1.1内连接
相当于查询两张交集部分数据
隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
6.2.1.2外连接
左外连接
查询左表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接
查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
注意事项:
左外连接和右外连接是可以相互替换的,只需要调整在连接查询时SQL中,表结构的先后顺序就可以了。而我们在日常开发使用时,更偏向于左外连接。
6.2.1.3自连接
当前表与自身的连接查询,自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
对于自连接查询,可以是内连接查询,也可以是外连接查询。
注意事项:
在自连接查询中,必须要为表起别名,要不然我们不清楚所指定的条件、返回的字段,到底 是哪一张表的字段。
6.2.2 联合查询
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
注意事项:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起,union 会对合并之后的数据去重。
6.2.3 子查询
6.2.3.1概述
概念
SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
分类
根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置,分为:
- WHERE之后
- FROM之后
- SELECT之后
6.2.3.2 标量子查询(子查询结果为单个值)
子查询返回的结果是单个值(数字、字符串、日期等),最简单的形式,这种子查询称为标量子查询。
常用的操作符:= <> > >= < <=
案例:查询 "销售部" 的所有员工信息
完成这个需求时,我们可以将需求分解为两步:
- 查询 "销售部" 部门ID
select id from dept where name = '销售部';
- 根据 "销售部" 部门ID, 查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
6.2.3.3 列子查询(子查询结果为一列)
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN 、NOT IN 、 ANY 、SOME 、 ALL
操作符 | 描述 |
IN | 在指定的集合范围之内,多选一 |
NOT IN | 不在指定的集合范围之内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
案例1:查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
- 查询所有 财务部 人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
- 比 财务部 所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') );
6.2.3.4 行子查询(子查询结果为一行)
子查询返回的结果是一行(可以是多列),这种子查询称为行子查询。
常用的操作符:= 、<> 、IN 、NOT IN
案例:查询与 "张无忌" 的薪资相同而且直属领导相同的员工信息
这个需求可以拆解为两步进行:
- 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
- 查询与 "张无忌" 的薪资相同而且直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
6.2.3.5 表子查询(子查询结果为多行多列)
子查询返回的结果是多行多列,这种子查询称为表子查询。
常用的操作符:IN
案例:查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
分解为两步执行:
- 查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
7.事务
7.1简介
事务 是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系 统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
注意:
默认MySQL的事务是自动提交的,也就是说,当执行完一条DML语句时,MySQL会立即隐 式的提交事务。
7.2事务操作
7.2.1事务控制
方法一
- 查看/设置事务提交方式
SELECT @@autocommit ;
SET @@autocommit = 0 ;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
注意:
上述的这种方式,我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提 交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
方法二
- 开启事务
START TRANSACTION 或 BEGIN ;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
7.3事务的四大特性(ACID)
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立 环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
7.4并发事务问题
- 赃读:一个事务读到另外一个事务还没有提交的数据
- 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据 已经存在,好像出现了 "幻影"。
7.5事务的隔离级别
为了解决并发事务所引发的问题,在数据库中引入了事务隔离级别。主要有以下几种:
隔离级别 | 脏读 | 不可重复读 | 幻读 |
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable Read(MySQL默认) | × | × | √ |
Serializable | × | × | × |
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
注意:
事务隔离级别越高,数据越安全,但是性能越低。