1、数据库简介与安装（Windows）
MySQL数据库下载和安装
MySQL启动
MySQL客户端连接
MySQL数据模型
2、SQL语言分类
| 分类 | 全程 | 说明 |
| DDL | Data Defination Language | 数据定义语言，用来定义数据库对象（数据库、表格、字段） |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表中的数据 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库访问权限 |
DDL
1、查询数据库 show Databases; 2、查询当前数据库 select DataBase(); 3、创建数据库 create database [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [Collate 排序规则]; create database if not exists itTest default charset utf8mb4 collate 4、删除数据库 drop Database [IF NOT EXISTS] 数据库名; 5、使用数据库 use 数据库名 6、修改数据库 ALTER DATABASE 数据库名 CHARACTER SET 字符集;
1、查看当前数据库所有表格 show tables; 2、查询表结构； desc 表格名称； 3、查询指定表格的创建表格语句； show create table tableName; 4、创建表 Create table 表名 ( 字段名 字段类型 [default 默认值] [comment 字段注释], 字段名 字段类型 [default 默认值] [comment 字段注释]， 字段名 字段类型 [default 默认值] [comment 字段注释], 字段名 字段类型 [default 默认值] [comment 字段注释], 字段名 字段类型 [default 默认值] [comment 字段注释] )[Comment 表格注释] [Engine=InnoDB] [Default charset=字符集] [collate=排序规则]; 5、表结构的修改-添加字段 Alter table 表名 add 字段名 类型（长度） [comment 注释] [约束]; 6、表结构的修改-修改字段名 和 字段类型； Alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]； 7、表结构的修改-修改数据类型 alter table 表名 modify 需要修改的字段名 新的数据类型(长度); 8、表结构的修改-删除字段 alter table 表名 drop 字段名; 9、修改表名 alter table 表名 rename to 新表名; 10、删除表-完全删除 drop table [if exists] 表名; 11、删除表-删除并重新创建该表(即仅删除数据但保留表结构) truncate table 表名;
字段类型：
(1) 数值类型
| 分类 | 类型 | 大小 | 有符号范围 | 无符号范围 | 描述 |
| 数 值 类 型 | tinyint | 1byte | (-128,127) | (0,2^8-1) | 小整数 |
| smallint | 2byte | (-32768,32767) | (0,2^16-1) | 大整数 | |
| mediumint | 3byte | (-8388608,8388607) | (0,2^24-1) | 大整数 | |
| int/integer | 4byte | (-2147483648,2147483647) | (0,2^32-1) | 大整数 | |
| bigint | 8byte | (-2^63,2^63-1) | (0,2^64-1) | 极大整数 | |
| float | 4byte | (-3.402823466 E+38,3.402823466 E+38) | 单精度 | ||
| double | 8byte | 双精度 | |||
| decimal | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度) 和D(标度)的值 | 小数值（精确定点数） |
(2) 字符串类型
| 分类 | 类型 | 大小 | 描述 |
| 字 符 串 类 型 | char | 0-255bytes | 定长字符串 |
| varchar | 0-65535bytes | 变长字符串 | |
| tinyblob | 0-255bytes | 不超过255字符的二进制数据 | |
| tinytext | 0-255bytes | 短文本字符串 | |
| blob | 0-65535bytes | 二进制形式的长文本数据 | |
| text | 0-65535bytes | 长文本数据 | |
| mediumblob | 0-16777215bytes | 二进制形式的中等长度文本数据 | |
| mediumtext | 0-16777215bytes | 中等长度文本数据 | |
| longblob | 0-4294967295bytes | 二进制形式的极大文本数据 | |
| longtext | 0-4294967295bytes | 极大文本数据 |
二进制数据：图片视频等，但多数情况下不会使用数据库来存储视频图片等数据，而是建立文件服务器进行存储。
char&varchar：
- 使用方式：char(32),varchar(32). 即数据类型（数据最大长度）。
- 区别：char性能高，varchar性能低（在使用时会根据内容来自动分配长度）。
总结下来：char费空间，varchar费性能。
(3) 日期时间格式
| 分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
| 日 期 类 型 | 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 | 日期+时间值，时间戳形式 |
1、设计员工表，要求如下 （1）编号（纯数字） （2）员工工号（字符串类型，长度不超过10） （3）员工姓名（字符串类型，长度不超过10） （4）性别（男/女 ，一个汉字） （5）年龄（正常人年龄，不可能为负数） （6）身份证号（二代身份证18位，身份证中有X这样的字符） （7）入职时间（年月日即可） create table emp( id int primary key not null comment '编号', work_no varchar(10) not null comment '工号', name varchar(10) not null comment '姓名', gender char(1) comment '性别', age tinyint unsigned comment '年龄', id_card char(18) not null comment '身份证号', in_time date comment '入职时间' ) engine=innodb comment '员工表' 2、添加字段：nickName 不超过20字符长度 alter table emp add nick_name varchar(20) comment '昵称'; 3、修改字段age 的类型为无符号的tinyInt类型 alter table emp modify age tinyint unsigned ; 3、修改字段id_card 的为"idcard"，备注为"员工身份证号"； alter table emp change id_card idcard char(18) comment '员工身份证号' ; 4、删除nick_name字段 alter table emp drop nick_name; 5、修改表名为employee alter table emp rename to employee; 6、truncate删除表数据 truncate table employee; 7、删除表 drop table employee;
DML
1、给指定字段添加数据 insert into 表名 (字段1,字段2,字段3, ....) values (值1,值2,值3, ....) (value和values均可以) 2、给全部字段添加数据 insert into 表名 values (值1,值2,值3, ....) 3、批量添加数据-指定字段 insert into 表名 (字段1,字段2,字段3,...) values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...); 4、批量添加数据-全字段 insert into 表名 values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...); 5、修改字段值-单字段 update 表名 SET 列名=新值 WHERE 限定条件; 6、修改字段值-多个字段 update 表名 SET 列名1=新值1,列名2=新值2,列名3=新值3 , ..... WHERE 限定条件; 7、删除表数据 delete from 表名 [where 条件];
DQL
select 字段1,字段2,字段3,..... from 表名 where 条件列表 group by 分组字段列表 having 分组后的条件列表 order by 排序字段列表 asc/desc limit 分页参数; (where 不可以使用聚合条件,在分组之前过滤。 having 可以使用聚合条件，在分组之后过滤) (where > 聚合函数 > having) (分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段是没有意义的) (执行顺序:from -> where -> group by -> select -> having -> order by -> limit) (其中：having是在where条件查询和分组以及聚合函数后，order by 和limit也是) 1、基本查询-指定字段 select 字段1 [as 别名1],字段2 [as 别名2],字段3 [as 别名3], .... from 表名; 2、基本查询-全查询（不推荐使用,影响效率，会进行全表扫描，不走索引） select * from 表名; 3、基本查询-去重 select distinct 字段列表 from 表名; 4、条件查询 select 字段列表 from 表名 where 条件列表;
条件查询
| 比较运算符 | 功能 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between .... and .... | 在某个范围内（包含最大和最小），时间日期也可用此运算符 |
| in (......) | 表示字段在in后的列表中的值，多选1 |
| like 占位符 | 模糊查询（_匹配单个字符，%匹配任意字符） |
| is null | 是空 |
| 逻辑运算符 | 功能 |
| and 或者 && | 并且 （多条件同时成立） |
| or 或者 || | 或者（多条件任意一个成立） |
| not 或者 ！ | 非，不是 |
CREATE TABLE `emp` ( `id` int(11) NOT NULL COMMENT '主键ID', `workno` varchar(10) NOT NULL COMMENT '工号', `name` varchar(10) NOT NULL COMMENT '姓名', `gender` char(1) DEFAULT NULL COMMENT '性别', `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄', `idcard` char(18) NOT NULL COMMENT '身份证号码', `workaddress` varchar(64) DEFAULT NULL COMMENT '工作地址', `entrydate` date DEFAULT NULL COMMENT '入职时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表' insert into emp (id,workno,name,gender,age,idcard,workaddress,entrydate) values (1,'0000000001','a1','男',99,'123456789987654321','北京','2022-08-16'), (2,'0000000002','a2','男',45,'123456789987654321','北京','2022-08-16'), (3,'0000000003','a3','男',23,'123456789987654321','北京','2022-08-16'), (4,'0000000004','a4','男',23,'123456789987654321','北京','2022-08-16'), (5,'0000000005','a5','男',30,'123456789987654321','北京','2022-08-16'), (6,'0000000006','a6','男',23,'123456789987654321','北京','2022-08-16'), (7,'0000000007','a7','男',35,'123456789987654321','北京','2022-08-16'), (8,'0000000008','a8','男',23,'123456789987654321','北京','2011-01-30'), (9,'0000000009','a9','男',28,'123456789987654321','北京','2011-01-30'), (10,'0000000010','a10','男',23,'123456789987654321','西安','2022-08-16'), (11,'0000000011','a11','男',23,null,'西安','2022-08-16'), (12,'0000000012','a12','女',23,'123456789987654321','西安','2022-08-03'), (13,'0000000013','a13','女',28,'123456789987654321','西安','2022-08-03'), (14,'0000000014','a14','女',23,'123456789987654321','西安','2022-08-03'), (15,'0000000015','a15','女',20,null,'西安','2011-01-30'), (16,'0000000016','a16','女',28,'123456789987654321','西安','2022-08-03'), (17,'0000000017','a17','女',20,'123456789987654321','上海','2022-08-03'), (18,'0000000018','a18','女',19,'123456789987654321','上海','2011-01-30'), (19,'0000000019','a19','女',21,'123456789987654321','上海','2022-08-03'), (20,'0000000020','a20','女',23,'123456789987654321','上海','2022-08-03'), (21,'0000000021','a21','女',19,'123456789987654321','上海','2022-08-03'), (22,'0000000022','a22','女',18,'123456789987654321','上海','2022-08-03'), (23,'0000000023','a23','女',23,'123456789987654321','上海','2022-08-03'), (24,'0000000024','a24','女',23,'123456789987654321','上海','2022-08-03'), (25,'0000000025','a25','女',23,'123456789987654321','上海','2022-08-03'), (26,'0000000026','a26','女',23,'123456789987654321','上海','2022-08-03'), (27,'0000000027','a27','女',23,'123456789987654321',null,'2022-08-03'); 1、查询年龄小于20的员工 select * from emp where age <20 ; 2、查询年龄等与28的员工信息； select * from emp where age = 28; 3、查询年龄小于等于20的员工 select * from emp where age <= 20; 4、查询没有身份证号的员工信息 select * from emp where idcard is null; 5、查询有身份证号的员工信息 select * from emp where idcard is not null; 6、查询年龄不等于28的员工信息 select * from emp where age != 28; 7、查询年龄在15岁（包含） 到20岁（包含）之间的员工信息 select * from emp where age >= 15 and age <=20; select * from emp where age between 15 and 20; 8、查询性别为女且年龄小于25的员工信息 select * from emp where gender = '女' and age <25; 9、查询年龄等与18或者20或者40 select * from emp where age in (18,20,40); select * from emp where age =18 or age=20 or age=40; 10、查询姓名为两个字符的员工 select * from emp where name like '__'; 11、查询身份证号最后一位是"X"的员工； select * from emp where idcard like '%X';
聚合查询
- 聚合函数定义：
将一列数据作为一个整体，进行纵向计算；
- 常见聚合函数：
| 函数 | 功能 |
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
| count_big | 统计数量，且返回的数据为bigint类型 |
| grouping | grouping产生一个附加的列，当用cube或rollup运算符添加行时，输出值为1.当所添加的行不是由cube或rollup产生时，输出值为0. |
| binary_checksum | 返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改 |
| checksum_agg | 返回指定数据的校验值，null值将被忽略 |
| var | 方差 |
| varp | 填充的方差 |
| stdev | 标准差 |
| stdevp | 填充的标准差 |
| checksum | 返回校验值，用于生成hash索引 |
- 练习：
1、count统计总数量 select count(*) from emp; select count(id) from emp; 2、avg统计平均值 select avg(age) from emp; 3、求最大和最小年龄； select max(age),min(age) from emp; 4、sum求和； select sum(age) as '年龄和' from emp where workaddress = '西安'; 5、统计数量返回bigint select count_bigint(*) from emp ;
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段 [having 分组后的过滤条件] 1、根据性别分组，统计员工数量 select gender , count(*) from emp group by gender; 2、根据性别分组，统计平均年龄 select gender,avg(age) from emp group by gender; 3、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址 select workaddress,count(*) from emp where age <45 group by workaddress having count(*) >3;
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2； 1、根据年龄对员工升序排序 select * from emp order by age asc; 2、根据入职时间，对员工进行降序排序； select * from emp order by entryDate desc; 3、根据年龄对员工进行升序排序，年龄相同的再根据入职时间降序排序 select * from emp order by age asc , entrydate desc;
分页查询
select 字段列表 from 表名 limit 起始索引，查询记录数; （起始索引 = （查询页数-1）*每页数量） 1、查询第一页数据，每一页十条 select * from emp limit 0,10; select * from emp limit 10; 2、查询第二页，每页十条记录 select * from emp limit 10,10;
案例练习
1、查询年龄为20，21，22，23岁的女性员工； select * from emp where gender = '女' and age in (20,21,22,23); 2、查询性别为男，并且年龄在20-40（包含）以内的姓名为三个字的员工； select * from emp where gender = '男' and age between 20 and 40 and name like '___'; 3、统计员工表中，年龄小于60岁的，男性员工和女性员工的人数 select gender,count(*) from emp where age < 60 group by gender; 4、查询所有年龄小于等于35岁的员工的姓名和年龄，并对查询结果按照年龄升序排序，如果年龄相同按照入职时间降序排序 select name '姓名' ,age '年龄' from emp where age <= 35 order by age ,entrydate desc; 5、查询性别为男，且年龄在20-40岁（包含）以内的前五个员工的信息，并对结果进行年龄升序排序，年龄相同按照入职时间升序排序 select * from emp where gender = '男' and age between 20 and 40 order by age,entrydate limit 5;;
DCL
用户管理
1、查询用户 user mysql; select * from user; 2、创建用户 create user '用户名'@'主机名' identified by '密码'; 3、修改密码 alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; 4、删除用户 drop user '用户名'@'主机名';
权限控制
常用权限说明：
| 权限 | 说明 |
| all, all privileges | 所有权限 |
| select | 查询数据 |
| insert | 插入数据 |
| update | 更新数据 |
| delete | 删除数据 |
| alter | 修改表 |
| drop | 删除表或数据库或视图 |
| create | 创建数据库或者表或者视图 |
1、查询权限 show grants for '用户名'@'主机名'; (usage权限表示仅仅能登录) 2、授予权限 grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; 3、撤销权限 revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; (多个权限之间使用逗号进行分隔) (授权时，数据库名和表名可以使用 * 进行统配，代表所有)
3、函数
字符串函数
| 函数 | 说明 |
| length(str) | 返回字符串str的字节数（utf8mb4编码下一个汉字3个字节） |
| octet_length(str) | 返回字符串str的字节数（utf8mb4编码下一个汉字3个字节） |
| char_length(str) || character_length(str) | 返回str的字符数 |
| bit_length(str) | 返回str的比特长度 |
| concat(s1,s2,s2,....sn) | 将s1,s2,...sn按顺序拼接成一个字符串。任意一个字符为null，返回Null |
| concat_ws(sep,str1,str2,...strn) | 将str1,str2,....strn用分隔符sep拼接并返回。当sep为null时，返回null |
| lower(str) || lcase(str) | 将str全部转化为小写 |
| upper(str) || ucase(str) | 将str全部转换为大写 |
| lpad(str,n,pad) | 左填充，用pad字符串对str字符串左侧填充，达到n个字符串长度 |
| rpad(str,n,pad) | 右填充，用pad字符串对str字符串右侧填充，达到n个字符串长度 |
| trim(str) | 去掉字符串头部和尾部的空格 |
| substring(str,start,len) | 返回 字符串str从strat位置开始的len长度的字符串, start可以为负数，表示从右开始数start的位置开始 len可以省略，表示到字符串尾部结束 |
| substring_index(str,delim,count) | str按照delim字符分隔成多分，返回从左数count个数量的字符 count为负数表示从右侧开始返回count数量的字符 |
| left(str,len) | 返回Str左侧的len个字符串 |
| right(str,len) | 返回str右侧的len个字符串 |
| instr(str,s1) | 返回s1在str中第一个字符的索引位置，没有则返回0 |
| field(str,s1,s2,s3,s4,....) | 返回str在后面s1,s2,s3,s4,...出现的次数，没有出现过则返回0 |
| locate(s1,str) locate(s1,str,pos) | 返回s1在str中的第一次出现的位置 返回s1在str中pos位置往后的字符串中的第一次出现的位置 |
| find_in_set(str,strlist) | 返回str在后面列表字符串中第一次出现的位置，其中strlist是多个字符串用逗号拼接而成 |
| reverse(str) | 将str的字符顺序颠倒后返回。 |
| replace(str,str_from,str_to) | 将str中的str_from字符替换为str_to字符串并返回； |
| insert(str,pos,len,newstr) | 将str中pos索引位置开始的len个长度的字符串替换为newstr字符串并返回 |
(1) length()
select length("Hello MySql攻克指南课") lendth,
octet_length("Hello MySql攻克指南课") octetlength ,
char_length("Hello MySql攻克指南课") charlength,
character_length("Hello MySql攻克指南课") characterLength,
bit_length("Hello MySql攻克指南课") bit_length ;
+--------+-------------+------------+-----------------+------------+
| lendth | octetlength | charlength | characterLength | bit_length |
+--------+-------------+------------+-----------------+------------+
| 21 | 21 | 16 | 16 | 168 |
+--------+-------------+------------+-----------------+------------+
(2) concat()
select concat('Hello',' ','World') str1 ,concat("Hellow",null,"World") str2;
+-------------+------+
| str1 | str2 |
+-------------+------+
| Hello World | NULL |
+-------------+------+
(3) concat_ws()
select concat_ws("_","Hellow","MySql","攻克指南课") str1 ,concat_ws(null,"Hellow","MySql","攻克指南课") str2;
+-------------------------+------+
| str1 | str2 |
+-------------------------+------+
| Hellow_MySql_攻克指南课 | NULL |
+-------------------------+------+
(4) lower() | lcase()
select lower ("Hello MySql") str1,lcase("Hello MySql") str2 ;
+-------------+-------------+
| str1 | str2 |
+-------------+-------------+
| hello mysql | hello mysql |
+-------------+-------------+
(5) upper()|ucase()
select upper ("Hello MySql") str1,ucase("Hello MySql") str2 ;
+-------------+-------------+
| str1 | str2 |
+-------------+-------------+
| HELLO MYSQL | HELLO MYSQL |
+-------------+-------------+
(6) lpad()
select lpad("MySql",11,"Hello") str1 , lpad("MySql",12,"Hello") str2 ,lpad("MySql",10,"Hello") str3 ;
+-------------+--------------+------------+
| str1 | str2 | str3 |
+-------------+--------------+------------+
| HelloHMySql | HelloHeMySql | HelloMySql |
+-------------+--------------+------------+
(7) rpad()
select rpad("Hello",11,"MySql") str1 , rpad("Hello",12,"MySql") str2 ,rpad("Hello",10,"MySql") str3 ;
+-------------+--------------+------------+
| str1 | str2 | str3 |
+-------------+--------------+------------+
| HelloMySqlM | HelloMySqlMy | HelloMySql |
+-------------+--------------+------------+
(8) trim()
select " Hello MySql攻克指南课 " str1 ,trim(" Hello MySql攻克指南课 ") str2;
+---------------------------+-----------------------+
| str1 | str2 |
+---------------------------+-----------------------+
| Hello MySql攻克指南课 | Hello MySql攻克指南课 |
+---------------------------+-----------------------+
(9) substring()
select substring("Hello MySql攻克指南课",1,5) str1;
+-------+
| str1 |
+-------+
| Hello |
+-------+
(10) substring_index()
select substring_index("Hello,MySql,Hello,World,Hellow MySql攻克指南课",",",2) str1,
substring_index("Hello,MySql,Hello,World,Hellow MySql攻克指南课",",",-2) str2;
+-------------+------------------------------+
| str1 | str2 |
+-------------+------------------------------+
| Hello,MySql | World,Hellow MySql攻克指南课 |
+-------------+------------------------------+
(11) left()
select left("Hello MySql 攻坚课",5) str;
+-------+
| str |
+-------+
| Hello |
+-------+
(12) right()
select right("Hello MySql 攻坚课",3) str;
+--------+
| str |
+--------+
| 攻坚课 |
+--------+
(13) instr()
select instr("Hi,hello Darling,hello MySql","Hello") pos1,
instr("Hi.HellooWorld","Hello") pos2;
+------+------+
| pos1 | pos2 |
+------+------+
| 4 | 4 |
+------+------+
(14) field()
select field("Hello","Helloo","MySql","Hello","World","Hellow MySql攻克指南课") pos;
+-------+
| pos |
+-------+
| 3 |
+-------+
(15) locate()
select locate('mysql','Hello mysql learing') loc1 , locate('mysql','Hello mysql,mysqlLearning',10) loc2;
+------+------+
| loc1 | loc2 |
+------+------+
| 7 | 13 |
+------+------+
(16) find_in_set()
select find_in_set("Hello","Hello,MySql,Hello,World,Hellow MySql攻克指南课") pos;
+-----+
| pos |
+-----+
| 1 |
+-----+
(17) reverse()
select reverse("Hello MySql攻克指南课") str ;
+-----------------------+
| str |
+-----------------------+
| 课南指克攻lqSyM olleH |
+-----------------------+
(18) replace()
select replace("Hello MySql攻克指南课","Hello MySql","MySql") str;
+-----------------+
| str |
+-----------------+
| MySql攻克指南课 |
+-----------------+
(19) insert()
select insert("Hello MySql 攻坚课",13,3,"学习进步课") str;
+------------------------+
| str |
+------------------------+
| Hello MySql 学习进步课 |
+------------------------+
一、（转多列）
select substring_index(substring_index('张三,李四,王五',',',help_topic_id+1),',',-1) as name
from mysql.help_topic
where help_topic_id < length('张三,李四,王五')-length(replace('张三,李四,王五',',',''))+1;
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王五 |
+------+
解析：
涉及字符串函数：substring_index(),length(),replace()
其中（length('张三,李四,王五')-length(replace('张三,李四,王五',',',''))+1）
此处利用 mysql 库的 help_topic 表的 help_topic_id 来作为变量，因为 help_topic_id 是自增的，
help_topic_id最大为658，如果需要的id长度过长也可以利用现有表id，也可以自己造自增id。
步骤1: help_topic_id < length('张三,李四,王五')-length(replace('张三,李四,王五',',',''))+1
获取逗号可以分割成多少份（先获取分隔符的数量，再加1表示人名的数量）
help_topic_id是从0开始的，案例中help_topic_id <4；
步骤2：根据“，”逗号来拆分字符串，此处利用 substring_index（str, delim, count） 函数，最后把结果赋值给 num 字段。
涉及的代码片段：substring_index(substring_index('张三,李四,王五',',',help_topic_id+1),',',-1) AS num
二、（转多行）
select
-- 截取第一个逗号前边的数据，即为第一个字符串
substring_index( ids, ',', 1 ) id1,
-- 在52,15字符串中，从字符串中逗号的位置+1开始截取即为第二个字符串
substring(
substring_index( ids, ',', 2 ),
locate( ',', substring_index( ids, ',', 2 ), 1 )+ 1
) id2,
-- 将第一个字符串和第二个字符串的长度+2(两个逗号)，从这个位置开始截取即为第三个字符串
substring(ids,
length(
concat(
substring_index( ids, ',', 1 ),
substring(
substring_index( ids, ',', 2 ),
locate( ',',
substring_index( ids, ',', 2 ),
1
)
)
)
)+ 2
) id3
from
( select replace ( replace ( '[52,15,894]', '[', '' ), ']', '' ) as ids ) t;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 52 | 15 | 894 |
+------+------+------+
详细解析可以参考：https://blog.csdn.net/weixin_43847283/article/details/124029614
数值函数
| 函数 | 说明 |
| abs(x) | 求x的绝对值 |
| floor(x) | 向下取整 |
| ceil(x) | 向上取整 |
| mod(x,y) | 返回x/y的模 |
| rand() | 返回0到1内的随机数 |
| round(x,y) | 求参数x的四舍五入的值，保留y位小数 |
| truncate(x,y) | 求参数x截断为y位小数的 |
(1) abs()
select abs(-10) num1,abs(-1.1) num2; +------+------+ | num1 | num2 | +------+------+ | 10 | 1.1 | +------+------+
(2) ceil()
select ceil(1.5) num1 , ceil(-2.5) num2; +------+------+ | num1 | num2 | +------+------+ | 2 | -2 | +------+------+
(3) floor()
select floor(1.5) num1 , floor(-2.5) num2; +------+------+ | num1 | num2 | +------+------+ | 1 | -3 | +------+------+
(4) mod(x,y)
select mod(9,2) mod1 , mod(17,-4) mod2; +------+------+ | mod1 | mod2 | +------+------+ | 1 | 1 | +------+------+
(5) rand()
select rand() rand1 , rand()*1000 rand2; +---------------------+-------------------+ | rand1 | rand2 | +---------------------+-------------------+ | 0.06207907019376668 | 939.5468010935438 | +---------------------+-------------------+ select lpad(round(rand()*1000000,0),6,'0') '六位随机数'; +------------+ | 六位随机数 | +------------+ | 511497 | +------------+
(6) round(x,y)
select round(0.06207907019376668,4) num; +--------+ | num | +--------+ | 0.0621 | +--------+
(7) truncate(x,y)
select truncate(0.06207907019376668,4) num; +--------+ | num | +--------+ | 0.0620 | +--------+
日期函数
| 函数 | 说明 |
| curdate() | 当前日期 |
| curtime() | 当前时间 |
| now() | 当前日期和时间 |
| year(date) | 指定dete的年份 |
| month(date) | 指定dete的月份 |
| day(date) | 指定dete的天份 |
| date_add(date,INTERVAL expr type) | 日期/时间值加上一个时间间隔expr后的时间值(INTERVAL固定写法) |
| datediff(date1,date2) | 返回开始时间date1至结束时间date2的天数 |
(1) curdate()
select curdate() curdate; +------------+ | curdate | +------------+ | 2022-08-20 | +------------+
(2) curtime()
select curtime() curtime; +------------+ | curtime | +------------+ | 21:07:34 | +------------+
(3) now()
select now() now; +---------------------+ | now | +---------------------+ | 2022-08-20 21:08:34 | +---------------------+
(4) year(date)
select year(now()) curYear; +---------+ | curYear | +---------+ | 2022 | +---------+
(5) month(date)
select month(now()) curMonth; +----------+ | curMonth | +----------+ | 8 | +----------+
(6) day(date)
select day(now()) curDay; +--------+ | curDay | +--------+ | 20 | +--------+
(7) date_add()
select date_add(now(),INTERVAL 70 day) afterDate; +---------------------+ | afterDate | +---------------------+ | 2022-10-29 21:12:31 | +---------------------+ select date_add(curdate(),INTERVAL 70 day) afterDate; +------------+ | afterDate | +------------+ | 2022-10-29 | +------------+
(8) datediff()
select datediff(now(),date_add(now(),INTERVAL 70 day)) 差值；
+------+
| 差值 |
+------+
| -70 |
+------+
select name 'user_name',entrydate 'inTime', datediff(now(),entrydate) 'days'
from emp
order by days;
+-----------+------------+------+
| user_name | inTime | days |
+-----------+------------+------+
| a1 | 2022-08-16 | 4 |
| a2 | 2022-08-16 | 4 |
| a3 | 2022-08-16 | 4 |
| a4 | 2022-08-16 | 4 |
| a5 | 2022-08-16 | 4 |
| a7 | 2022-08-16 | 4 |
| a10 | 2022-08-16 | 4 |
| a11 | 2022-08-16 | 4 |
| a12 | 2022-08-03 | 17 |
| a13 | 2022-08-03 | 17 |
| a14 | 2022-08-03 | 17 |
| a16 | 2022-08-03 | 17 |
| a17 | 2022-08-03 | 17 |
| a19 | 2022-08-03 | 17 |
| a20 | 2022-08-03 | 17 |
| a21 | 2022-08-03 | 17 |
| a22 | 2022-08-03 | 17 |
| a23 | 2022-08-03 | 17 |
| a24 | 2022-08-03 | 17 |
| a25 | 2022-08-03 | 17 |
| a26 | 2022-08-03 | 17 |
| a27 | 2022-08-03 | 17 |
| a6 | 2021-05-30 | 447 |
| a8 | 2011-01-30 | 4220 |
| a9 | 2011-01-30 | 4220 |
| a15 | 2011-01-30 | 4220 |
| a18 | 2011-01-30 | 4220 |
+-----------+------------+------+
流程控制函数
目的：可以在sql语句中实现条件筛选，从而提高语句效率
| 函数 | 说明 |
| IF(value,t,f) | 如果value为true，则返回t，否则返回f |
| IFNULL(value1,value2) | 如果value1不为null，则返回value1，否则返回value2 |
| CASE WHEN [val1] THEN [res1] ..... ELSE [default] END | 如果val1 为true则返回res1，val1不为true判断val2是否为true，val2为true返回res2 .....否则返回default默认值 |
| CASE [expr] WHEN [val1] THEN [res1] ..... ELSE [default] END | 如果expr的值等于val1 则返回res1，如果expr的值等于val2 则返回res2，.....否则返回default默认值 |
(1) IF()
select if(true,'OK','NOTOK') if1,if(false,'OK','NOTOK') if2; +-----+-------+ | if1 | if2 | +-----+-------+ | OK | NOTOK | +-----+-------+
(2) IFNULL()
select ifnull('notnull','null') if1,ifnull('','null') if2,ifnull(null,'null') if3;
+---------+-----+------+
| if1 | if2 | if3 |
+---------+-----+------+
| notnull | | null |
+---------+-----+------+
(3) CASE WHEN
select name,case
when workaddress ='北京' then '一线城市'
when workaddress ='上海' then '一线城市'
else '二线城市及以下'
end citySize
from emp ;
+------+----------------+
| name | citySize |
+------+----------------+
| a1 | 一线城市 |
| a2 | 一线城市 |
| a3 | 一线城市 |
| a4 | 一线城市 |
| a5 | 一线城市 |
| a6 | 一线城市 |
| a7 | 一线城市 |
| a8 | 一线城市 |
| a9 | 一线城市 |
| a10 | 二线城市及以下 |
| a11 | 二线城市及以下 |
| a12 | 二线城市及以下 |
| a13 | 二线城市及以下 |
| a14 | 二线城市及以下 |
| a15 | 二线城市及以下 |
| a16 | 二线城市及以下 |
| a17 | 一线城市 |
| a18 | 一线城市 |
| a19 | 一线城市 |
| a20 | 一线城市 |
| a21 | 一线城市 |
| a22 | 一线城市 |
| a23 | 一线城市 |
| a24 | 一线城市 |
| a25 | 一线城市 |
| a26 | 一线城市 |
| a27 | 二线城市及以下 |
+------+----------------+
(4) CASE(expr) WHEN
select name,age,case age >=40
when false then '年轻打工人'
when true then '中年打工人'
end ageSize
from emp ;
（else语句根据业务来设置，如不需要可省略）
+------+------+------------+
| name | age | ageSize |
+------+------+------------+
| a1 | 99 | 中年打工人 |
| a2 | 45 | 中年打工人 |
| a3 | 23 | 年轻打工人 |
| a4 | 23 | 年轻打工人 |
| a5 | 30 | 年轻打工人 |
| a6 | 23 | 年轻打工人 |
| a7 | 35 | 年轻打工人 |
| a8 | 23 | 年轻打工人 |
| a9 | 28 | 年轻打工人 |
| a10 | 23 | 年轻打工人 |
| a11 | 23 | 年轻打工人 |
| a12 | 23 | 年轻打工人 |
| a13 | 28 | 年轻打工人 |
| a14 | 23 | 年轻打工人 |
| a15 | 20 | 年轻打工人 |
| a16 | 28 | 年轻打工人 |
| a17 | 20 | 年轻打工人 |
| a18 | 19 | 年轻打工人 |
| a19 | 21 | 年轻打工人 |
| a20 | 23 | 年轻打工人 |
| a21 | 19 | 年轻打工人 |
| a22 | 18 | 年轻打工人 |
| a23 | 23 | 年轻打工人 |
| a24 | 23 | 年轻打工人 |
| a25 | 23 | 年轻打工人 |
| a26 | 23 | 年轻打工人 |
| a27 | 23 | 年轻打工人 |
+------+------+------------+
select name, workaddress ,case workaddress in ('北京',"上海","广州","深圳","成都")
when true then "一线城市"
when false then "二线城市及以下"
else NULL
end citySize
from emp;
+------+-------------+----------------+
| name | workaddress | citySize |
+------+-------------+----------------+
| a1 | 北京 | 一线城市 |
| a2 | 北京 | 一线城市 |
| a3 | 北京 | 一线城市 |
| a4 | 北京 | 一线城市 |
| a5 | 北京 | 一线城市 |
| a6 | 北京 | 一线城市 |
| a7 | 北京 | 一线城市 |
| a8 | 北京 | 一线城市 |
| a9 | 北京 | 一线城市 |
| a10 | 西安 | 二线城市及以下 |
| a11 | 西安 | 二线城市及以下 |
| a12 | 西安 | 二线城市及以下 |
| a13 | 西安 | 二线城市及以下 |
| a14 | 西安 | 二线城市及以下 |
| a15 | 西安 | 二线城市及以下 |
| a16 | 西安 | 二线城市及以下 |
| a17 | 上海 | 一线城市 |
| a18 | 上海 | 一线城市 |
| a19 | 上海 | 一线城市 |
| a20 | 上海 | 一线城市 |
| a21 | 上海 | 一线城市 |
| a22 | 上海 | 一线城市 |
| a23 | 上海 | 一线城市 |
| a24 | 上海 | 一线城市 |
| a25 | 上海 | 一线城市 |
| a26 | 上海 | 一线城市 |
| a27 | NULL | NULL |
+------+-------------+----------------+
案例练习
数据准备
create table score(
id int primary key auto_increment not null comment 'ID',
name varchar(10) not null comment '姓名',
math tinyint unsigned comment '数学成绩',
chinese tinyint unsigned comment '语文成绩',
english tinyint unsigned comment '英语成绩'
) engine = innodb comment '学生成绩表';
insert into score(name,math,chinese,english)
values ('熊大',55,59,73),
('熊二',75,84,86),
('张三',85,55,90),
('李四',95,97,99),
('王五',59,75,90);
要求：
学生成绩展示
>=85 优秀
>=60 良好
其他 不及格
select id ,
name,
case when math>=85 then '优秀' when math >=60 then '良好' else '不及格' end '数学',
case when chinese>=85 then '优秀' when math >=60 then '良好' else '不及格' end '数学',
case when english>=85 then '优秀' when math >=60 then '良好' else '不及格' end '数学'
from score;
+----+------+--------+--------+--------+
| id | name | 数学 | 数学 | 数学 |
+----+------+--------+--------+--------+
| 1 | 熊大 | 不及格 | 不及格 | 不及格 |
| 2 | 熊二 | 良好 | 良好 | 优秀 |
| 3 | 张三 | 优秀 | 良好 | 优秀 |
| 4 | 李四 | 优秀 | 优秀 | 优秀 |
| 5 | 王五 | 不及格 | 不及格 | 优秀 |
+----+------+--------+--------+--------+
4、约束
概念：约束是作用在表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确性、有效性、完整性
分类：
| 约束 | 描述 | 关键字 |
| 非空约束 | 保证字段数据不可以为null（尽量放在其他约束后面） | not null |
| 唯一约束 | 保证字段的数据都是唯一不可重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定字段的值，则采用默认值 | default |
| 自增约束 | 自动增长，列的类型只能是整数类型，并且一个表只能有一个自增约束 | auto_increment |
| 无符号约束 | int unsigned 只有正数 | unsigned |
| 零填充约束 | 在类型后添加长度，输入数据未达到长度自动在左侧填充0 | zerofilll |
| 检查约束（8.0.16版本以后才有） | 保证字段值满足某个条件 | check（expr） |
| 外键约束（不使用，影响性能） | 多表之间建立连接，保证数据一致和完整。 无法删除或更新主表中数据，从而保证数据一致和完整 | foreign key reference |
| 行为 | 说明 |
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否存在外键，若有则不允许删除/更新（与RESTRICT一致） |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否存在外键，若有则不允许删除/更新（与NO ACTION一致） |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否存在外键，若有则也删除/更新子表中的记录 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否存在外键，若有则设置子表中该外键为null（子表该字段必须允许为null） |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值（INNODB不支持） |
设计表格“学生表”
id，唯一，int类型，主键自增
name，姓名，varchar（10），不可为空且唯一
student_no,学号，为六位数字
age，年龄，int ,大于0且小于120
status，状态，char(1)，如果未指定该值则默认为1
gender,性别，char(1),无
--id:主键约束、自增
--name:非空约束、唯一约束
--student_no:零填充约束
--age:无符号约束,检查约束
--statue:默认约束
create table student(
id int primary key auto_increment comment 'ID',
name varchar(10) unique not null comment '姓名',
staudent_no int(6) zerofill not null comment '学号',
age tinyint unsigned check(age>0 && age <=120) comment '年龄',
status char(1) default '1' comment '状态',
gender char(1) comment '性别'
) engine= innodb comment '学生表';
insert into student (name,student_no,age,status,gender)
values
('Tom1','001',16,'1','男'),
('Tom2','002',17,'0','男'),
('Tom3','004',17,'1','男')
insert into student (name,student_no,age,gender)
values
('Tom5','001',16,'男')
外键约束
用来让两张表建立连接，从而保证数据的一致性和完整性
1、添加外键 create table tablename( 字段名 属性， 字段名 属性， 字段名 属性， ...... [constraint] [外键名称] foreign key (外键字段名) reference 主表（字段名） ) alter table tablename add constraint 外键名（起个主键的名称即可） foreign key (外键字段名：本表需要添加外键的字段) reference 主表（主表主键字段）[on update 更新/删除行为 on delete 更新/删除行为]; 2、删除外键 alter table tablename drop foreign key 外键名称 [on update 更新/删除行为 on delete 更新/删除行为];
数据准备
create table dep(
id int primary key auto_increment comment 'ID',
name varchar(10) not null comment '部门名称'
)comment '部门表';
insert into dep (name) values("研发部"),("市场部"),("策划部"),("销售部"),("总经办")
create table user (
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年龄',
job varchar(20) comment '职位' ,
salary int comment '薪资',
enreydate date comment '入职时间',
manager_id int comment '直属领导ID',
dept_id int comment '部门ID'
)
insert into user (name,age,job,salary,entrydate,manager_id,dept_id)
values
('张无极',28,'教主',500000,"2021-05-03",'0001',5),
('张三丰',28,'教主',5000000,"2021-05-03",'0001',1),
('韦一笑',28,'教主',5000000,"2021-05-03",'0001',1),
('阿勇',25,'教主',5000000,"2021-05-03",'0001',2),
('杨逍',25,'教主',510000,"2021-05-03",'0001',2),
('杨不悔',23,'教主',500000,"2021-05-03",'0001',2),
('李四',21,'教主',500000,"2021-05-03",'0001',3),
('王五',21,'教主',800000,"2021-05-03",'0001',3),
('苍空',21,'教主',100000,"2021-05-03",'0001',3),
('波多衣',21,'教主',200000,"2021-05-03",'0001',3),
('小老弟',21,'教主',500000,"2021-05-03",'0001',3),
('狗蛋',31,'教主',500000,"2021-05-03",'0001',4),
('金刚',31,'教主',500000,"2021-05-03",'0001',4),
('小泽亚',21,'教主',500000,"2021-05-03",'0001',4),
('修女',24,'教主',500000,"2021-05-03",'0001',4),
('阿狸',29,'教主',500000,"2021-05-03",'0001',4);
(1) 当删除部门表一号部门时，user表数据不会改变
(2) 给user表添加外键
alter table user add constraint fk_emp_demp_id foreign key (dept_id) references dept(id);
此时删除部门，会同时删除user表数据
(3) 删除外键
alter table user drop foreign key fk_demp_id;
5、多表查询
多表关系
- 一对一
部门与员工之间：一个部门多个员工，一个员工只能有一个部门
- 一对多（多对一）
部门与员工之间：一个部门多个员工，一个员工只能有一个部门
可以建立外键并加上唯一约束。
- 多对多
学生和课程：学生可以选多个课程，一个课程也可以被多个学生选择；
需要建立中间表来维护两表之间的关系。
-- --------------------------------- 一对一 ---------------------------
create table tb_user(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '1: 男 , 2: 女',
phone char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu(
id int auto_increment primary key comment '主键ID',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
insert into tb_user(id, name, age, gender, phone) values
(null,'黄渤',45,'1','18800001111'),
(null,'冰冰',35,'2','18800002222'),
(null,'码云',55,'1','18800008888'),
(null,'李彦宏',50,'1','18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);
--学生表
create table student(
id int primary key auto_increment comment 'ID',
name varchar(10) comment '姓名'
) comment '学生表';
insert into student (name) values ('Tom'),('Rose'),('Jack'),('Shy'),('Black'),('Jie');
--课程表
create table course(
id int auto_increment primary key comment 'ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course (name) values ('英语'),('语文'),('数学'),('地理'),('高分子'),('量子力学');
--中间表
create table student_course(
id int auto_increment comment 'ID' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_student_id foreign key (student_id) references student(id) on update cascade on delete cascade,
constraint fk_course_id foreign key (course_id) references course(id) on update cascade on delete cascade
) comment '学生课程中间表';
insert into student_course (student_id,course_id) values
(1,1),(1,2),(1,3),(2,4),(2,6),(2,5),(3,1),(3,4),(3,5),
(4,2),(4,4),(4,6),(5,1),(5,5),(5,2),(6,3),(6,4),(6,6);
多表查询概述
从多张表中查询所需要的数据
select * from user , dept ; 一共查出来36条数据，user表12*dept表3条。---笛卡尔积现象，需要消除无效的笛卡尔积 +----+------------+------+------+--------+------------+------------+---------+----+--------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | id | name | +----+------------+------+------+--------+------------+------------+---------+----+--------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | 3 | 策划部 | | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | 4 | 销售部 | | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | 5 | 总经办 | | 32 | 张无极 | 28 | 教主 | 500000 | 2021-05-03 | 1 | 5 | 3 | 策划部 | | 32 | 张无极 | 28 | 教主 | 500000 | 2021-05-03 | 1 | 5 | 4 | 销售部 | | 32 | 张无极 | 28 | 教主 | 500000 | 2021-05-03 | 1 | 5 | 5 | 总经办 | | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 4 | 销售部 | | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 5 | 总经办 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 4 | 销售部 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 5 | 总经办 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 4 | 销售部 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 5 | 总经办 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | 4 | 销售部 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | 5 | 总经办 | | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 4 | 销售部 | | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 5 | 总经办 | | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 3 | 策划部 | | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 5 | 总经办 | | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 3 | 策划部 | | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 5 | 总经办 | | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 3 | 策划部 | | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 5 | 总经办 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 3 | 策划部 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 5 | 总经办 | | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 3 | 策划部 | | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 5 | 总经办 | +----+------------+------+------+--------+------------+------------+---------+----+--------+ select * from user u,dept d where u.dept_id = d.id; +----+------------+------+------+--------+------------+------------+---------+----+--------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | id | name | +----+------------+------+------+--------+------------+------------+---------+----+--------+ | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 3 | 策划部 | | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 4 | 销售部 | | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | 5 | 总经办 | | 32 | 张无极 | 28 | 教主 | 500000 | 2021-05-03 | 1 | 5 | 5 | 总经办 | +----+------------+------+------+--------+------------+------------+---------+----+--------+
多表查询分类
(1) 内连接
查询两表交集部分的数据
1、隐式内连接 select 字段列表 from 表1 ，表2 where 连接条件 .....; select user.name , dept.name from user , dept where user.dept_id = dept.id ; +------------+--------+ | name | name | +------------+--------+ | 李四 | 策划部 | | 王五 | 策划部 | | 苍空 | 策划部 | | 波多衣 | 策划部 | | 小老弟 | 策划部 | | 狗蛋 | 销售部 | | 金刚 | 销售部 | | 小泽亚 | 销售部 | | 修女 | 销售部 | | 阿狸 | 销售部 | | 张无极 | 总经办 | | 张无极 | 总经办 | +------------+--------+ 2、显式内连接 select 字段列表 from 表1 [inner] join 表2 on 连接条件; select e.name, d.name from user e inner join dept d on e.dept_id = d.id; +------------+--------+ | name | name | +------------+--------+ | 李四 | 策划部 | | 王五 | 策划部 | | 苍空 | 策划部 | | 波多衣 | 策划部 | | 小老弟 | 策划部 | | 狗蛋 | 销售部 | | 金刚 | 销售部 | | 小泽亚 | 销售部 | | 修女 | 销售部 | | 阿狸 | 销售部 | | 张无极 | 总经办 | | 张无极 | 总经办 | +------------+--------+
(2) 外连接
左外连接
查询左表所有数据，以及两表交集部分数据
select u.*, d.name from user u left outer join dept d on u.dept_id = d.id; select u.*, d.name from user u left join dept d on u.dept_id = d.id; +----+------------+------+------+--------+------------+------------+---------+--------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | name | +----+------------+------+------+--------+------------+------------+---------+--------+ | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 策划部 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 策划部 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 策划部 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | 策划部 | | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | 策划部 | | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 销售部 | | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 销售部 | | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 销售部 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 销售部 | | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | 总经办 | | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | NULL | | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | NULL | NULL | +----+------------+------+------+--------+------------+------------+---------+--------+
右外连接
查询右表所有数据，以及两表交集部分数据
select d.*, u.* from user u right outer join dept d on u.dept_id = d.id; 右外也可以改为左外 select d.*, u.* from dept d left outer join user u on u.dept_id = d.id; +----+--------+------+------------+------+------+--------+------------+------------+---------+ | id | name | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+--------+------+------------+------+------+--------+------------+------------+---------+ | 3 | 策划部 | 38 | 李四 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | | 3 | 策划部 | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | | 3 | 策划部 | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | | 3 | 策划部 | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | | 3 | 策划部 | 42 | 小老弟 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 3 | | 4 | 销售部 | 43 | 狗蛋 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | | 4 | 销售部 | 44 | 金刚 | 31 | 教主 | 500000 | 2021-05-03 | 1 | 4 | | 4 | 销售部 | 45 | 小泽亚 | 21 | 教主 | 500000 | 2021-05-03 | 1 | 4 | | 4 | 销售部 | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | | 4 | 销售部 | 47 | 阿狸 | 29 | 教主 | 500000 | 2021-05-03 | 1 | 4 | | 5 | 总经办 | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 1 | 5 | | 5 | 总经办 | 32 | 张无极 | 28 | 教主 | 500000 | 2021-05-03 | 1 | 5 | +----+--------+------+------------+------+------+--------+------------+------------+---------+
(3) 自连接
当前表与自身连接查询，子连接必须使用表别名
select 字段列表 from 表1 别名1 join 表1 别名2 on 连接条件; 1、查询员工及其所属领导的名字 select a.name '员工姓名', b.name '领导姓名' from user a , user b where a.managerid = b.id; +------------+----------+ | 员工姓名 | 领导姓名 | +------------+----------+ | 张无极2 | 张无极 | | 李四 | 张无极 | | 王五 | 张无极 | | 苍空 | 张无极 | | 波多衣 | 张无极 | | 小老弟 | 张无极 | | 狗蛋 | 张无极 | | 金刚 | 张无极 | | 小泽亚 | 张无极 | | 修女 | 张无极 | | 阿狸 | 张无极 | +------------+----------+ 2. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 select a.name '员工', b.name '领导' from user a left join user b on a.managerid = b.id; +------------+--------+ | 员工 | 领导 | +------------+--------+ | 张无极 | NULL | | 张无极2 | 张无极 | | 李四 | 张无极 | | 王五 | 张无极 | | 苍空 | 张无极 | | 波多衣 | 张无极 | | 小老弟 | 张无极 | | 狗蛋 | 张无极 | | 金刚 | 张无极 | | 小泽 亚 | 张无极 | | 修女 | 张无极 | | 阿狸 | 张无极 |
(4) 子连接
select * from 表格1 where 表格1.字段名称1 in （select 表格2.字段名称1 from 表格2）；
| 类别 | 说明 | 常用操作符 |
| 标量子查询 | 子查询结果为单个值 | =, <>, >, >=, <, <= |
| 列子查询 | 子查询结果为一列 | in, notin, any, some, all, |
| 行子查询 | 子查询结果为一列 | =, <> , ,in . not in |
| 表子查询 | 子查询结果为多行多列 | in |
1、标量子查询 -- a. 查询 "销售部" 的所有员工信息 -- b. 查询 "销售部" 部门ID select id from dept where name = '销售部'; select * from user where dept_id = id; +----+ | id | +----+ | 4 | +----+ ----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 4 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+ -- 根据销售部部门ID, 查询员工信息 select * from user where dept_id = (select id from dept where name = '销售部'); ----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 4 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+ 2、查询王五之后入职的信息（不包含和王五同一天入职） --分部查询： select entrydate from user where name = '王五'; +------------+ | entrydate | +------------+ | 2021-05-03 | +------------+ select * from user where entrydate > '2021-05-03'; +----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+ --子查询 select * from user u where u.entrydate > (select entrydate from user where name = '王五'); +----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+
1、查询销售部和市场部所有员工信息
--分步查询：
select id from dept where name = '销售部' or name = '策划部';
+----+
| id |
+----+
| 3 |
| 4 |
+----+
select * from user where dept_id in(3,4);
+----+------------+------+------+--------+------------+------------+---------+
| id | name | age | job | salary | entrydate | manager_id | dept_id |
+----+------------+------+------+--------+------------+------------+---------+
| 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 |
| 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 |
| 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 |
| 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 |
| 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 |
| 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 |
| 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 4 |
| 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 4 |
| 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 |
+----+------------+------+------+--------+------------+------------+---------+
--列子查询：
select * from user where dept_id in (select id from dept where name in ('销售部','策划部'));
+----+------------+------+------+--------+------------+------------+---------+
| id | name | age | job | salary | entrydate | manager_id | dept_id |
+----+------------+------+------+--------+------------+------------+---------+
| 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 |
| 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 |
| 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 |
| 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 |
| 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 |
| 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 |
| 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 4 |
| 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 4 |
| 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 |
+----+------------+------+------+--------+------------+------------+---------+
2、查询比 财务部 所有人工资都高的员工信息
--分步查询：
select id from dept where name = '财务部';
+----+
| id |
+----+
| 7 |
+----+
select salary from user where dept_id = '7';
+--------+
| salary |
+--------+
| 200000 |
| 300000 |
+--------+
select * from user where salary > 200000 and salary > 300000;
+----+---------+------+------+--------+------------+------------+---------+
| id | name | age | job | salary | entrydate | manager_id | dept_id |
+----+---------+------+------+--------+------------+------------+---------+
| 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 |
| 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL |
| 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 |
| 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 |
| 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 |
| 47 | 阿狸 | 29 | 教主 | 350000 | 2021-05-03 | 1 | NULL |
+----+---------+------+------+--------+------------+------------+---------+
--列子查询
select * from user where salary > all(select salary from user where dept_id in (select id from dept where name = '财务部'));
+----+---------+------+------+--------+------------+------------+---------+
| id | name | age | job | salary | entrydate | manager_id | dept_id |
+----+---------+------+------+--------+------------+------------+---------+
| 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 |
| 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL |
| 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 |
| 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 |
| 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 |
| 47 | 阿狸 | 29 | 教主 | 350000 | 2021-05-03 | 1 | NULL |
+----+---------+------+------+--------+------------+------------+---------+
3、查询比财务部其中任意一人工资高的员工信息
select * from user where salary > any (select salary from user where dept_id = (select id from dept where name = '财务部'));
+----+------------+------+------+--------+------------+------------+---------+
| id | name | age | job | salary | entrydate | manager_id | dept_id |
+----+------------+------+------+--------+------------+------------+---------+
| 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 |
| 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL |
| 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 |
| 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 |
| 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 |
| 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 7 |
| 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 |
| 47 | 阿狸 | 29 | 教主 | 350000 | 2021-05-03 | 1 | NULL |
+----+------------+------+------+--------+------------+------------+---------+
1. 查询与 "张无忌" 的薪资及直属领导相同的员工信息; --行子查询 select * from user where (salary,manager_id) = (select salary, manager_id from user where name = '张无极2'); +----+---------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+---------+------+------+--------+------------+------------+---------+ | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | +----+---------+------+------+--------+------------+------------+---------+
1. 查询与 "张无极2" , "张三" 的职位和薪资相同的员工信息 --表子查询 select * from user where (job,salary) in ( select job, salary from user where name = '张无极2' or name = '张三' ); +----+---------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+---------+------+------+--------+------------+------------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | +----+---------+------+------+--------+------------+------------+---------+ 2. 查询入职日期是 "2021-05-03" 之后的员工信息 , 及其部门信息 --表子查询 select u.*,d.* from (select * from user where entrydate >'2021-05-03') u left join dept d on u.dept_id = d.id; +----+------------+------+------+--------+------------+------------+---------+------+--------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | id | name | +----+------------+------+------+--------+------------+------------+---------+------+--------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | 5 | 总经办 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | 3 | 策划部 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | 3 | 策划部 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | 4 | 销售部 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | 7 | 财务部 | +----+------------+------+------+--------+------------+------------+---------+------+--------+
(5) 联合查询
union：合并查询结果并去重
union all ：合并查询结果不去重
另外：合并的查询结果必须是相同的字段
1. 将薪资低于 250000 的员工 , 和 年龄大于 50 岁的员工全部查询出来. select * from user where salary < 250000 union all select * from user where age > 30; +----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+ select * from user where salary < 250000 union select * from user where age > 30; +----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-03 | 1 | 3 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2021-05-03 | 1 | 4 | +----+------------+------+------+--------+------------+------------+---------+
多表案例练习
数据准备 CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL, `losal` int(11) DEFAULT NULL, `hisal` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='薪资等级表'; INSERT INTO ittest.salgrade (grade, losal, hisal) VALUES (1, 0, 30000), (2, 30001, 50000), (3, 50001, 80000), (4, 80001, 100000), (5, 100001, 150000), (6, 150001, 200000), (7, 200001, 300000), (8, 300001, 500000), (9, 500001, 150000); 1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接） -- 表: user , dept -- 连接条件: user.dept_id = dept.id select e.name , e.age , e.job , d.name from user e , dept d where e.dept_id = d.id; +------------+------+------+--------+ | name | age | job | name | +------------+------+------+--------+ | 李四 | 21 | 教主 | 策划部 | | 王五 | 21 | 教主 | 策划部 | | 苍空 | 21 | 教主 | 策划部 | | 波多衣 | 21 | 教主 | 策划部 | | 小老弟 | 21 | 教主 | 策划部 | | 狗蛋 | 31 | 教主 | 销售部 | | 修女 | 24 | 教主 | 销售部 | | 张无极 | 28 | 教主 | 总经办 | | 金刚 | 31 | 教主 | 财务部 | | 小泽亚 | 21 | 教主 | 财务部 | +------------+------+------+--------+ 2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接） -- 表: user , dept -- 连接条件: user.dept_id = dept.id select e.name , e.age , e.job , d.name from user e inner join dept d on e.dept_id = d.id where e.age < 30; +------------+------+------+--------+ | name | age | job | name | +------------+------+------+--------+ | 张无极 | 28 | 教主 | 总经办 | | 李四 | 21 | 教主 | 策划部 | | 王五 | 21 | 教主 | 策划部 | | 苍空 | 21 | 教主 | 策划部 | | 波多衣 | 21 | 教主 | 策划部 | | 小老弟 | 21 | 教主 | 策划部 | | 小泽亚 | 21 | 教主 | 财务部 | | 修女 | 24 | 教主 | 销售部 | +------------+------+------+--------+ 3. 查询拥有员工的部门ID、部门名称（隐示内连接） -- 表: user , dept -- 连接条件: user.dept_id = dept.id select distinct d.id , d.name from user e , dept d where e.dept_id = d.id; +----+--------+ | id | name | +----+--------+ | 3 | 策划部 | | 4 | 销售部 | | 5 | 总经办 | | 7 | 财务部 | +----+--------+ 4. 查询所有年龄大于30岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来 -- 表: user , dept -- 连接条件: user.dept_id = dept.id -- 外连接 select e.*, d.name from user e left join dept d on e.dept_id = d.id where e.age > 30 ; +----+------+------+------+--------+------------+------------+---------+--------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | name | +----+------+------+------+--------+------------+------------+---------+--------+ | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | 销售部 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | 财务部 | | 47 | 阿狸 | 32 | 教主 | 350000 | 2021-05-03 | 1 | NULL | NULL | +----+------+------+------+--------+------------+------------+---------+--------+ 5. 查询所有员工的工资等级 -- 表: user , salgrade -- 连接条件 : user.salary >= salgrade.losal and user.salary <= salgrade.hisal select e.* , s.grade , s.losal, s.hisal from user e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal; +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | grade | losal | hisal | +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | 8 | 300001 | 500000 | | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | 8 | 300001 | 500000 | | 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 | 7 | 200001 | 300000 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 9 | 500001 | 1500000 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 4 | 80001 | 100000 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | 6 | 150001 | 200000 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | 8 | 300001 | 500000 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | 6 | 150001 | 200000 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | 6 | 150001 | 200000 | | 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 7 | 7 | 200001 | 300000 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 8 | 300001 | 500000 | | 47 | 阿狸 | 32 | 教主 | 350000 | 2021-05-03 | 1 | NULL | 8 | 300001 | 500000 | +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ select e.* , s.grade , s.losal, s.hisal from user e , salgrade s where e.salary between s.losal and s.hisal; +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | grade | losal | hisal | +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | 8 | 300001 | 500000 | | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | 8 | 300001 | 500000 | | 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 | 7 | 200001 | 300000 | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | 9 | 500001 | 1500000 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | 4 | 80001 | 100000 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | 6 | 150001 | 200000 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | 8 | 300001 | 500000 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | 6 | 150001 | 200000 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | 6 | 150001 | 200000 | | 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 7 | 7 | 200001 | 300000 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | 8 | 300001 | 500000 | | 47 | 阿狸 | 32 | 教主 | 350000 | 2021-05-03 | 1 | NULL | 8 | 300001 | 500000 | +----+------------+------+------+--------+------------+------------+---------+-------+--------+---------+ 6. 查询 "财务部" 所有员工的信息及 工资等级 -- 表: user , salgrade , dept -- 连接条件 : user.salary between salgrade.losal and salgrade.hisal , user.dept_id = dept.id -- 查询条件 : dept.name = '财务部' select e.* , s.grade from user e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '财务部'; +----+------------+------+------+--------+------------+------------+---------+-------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | grade | +----+------------+------+------+--------+------------+------------+---------+-------+ | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | 6 | | 45 | 小泽亚 | 21 | 教主 | 300000 | 2021-05-03 | 1 | 7 | 7 | +----+------------+------+------+--------+------------+------------+---------+-------+ 7. 查询 "财务部" 员工的平均工资 -- 表: user , dept -- 连接条件 : user.dept_id = dept.id select avg(e.salary) from user e, dept d where e.dept_id = d.id and d.name = '财务部'; +---------------+ | avg(e.salary) | +---------------+ | 250000.0000 | +---------------+ 8. 查询工资比 "灭绝" 高的员工信息。 -- a. 查询 "张无极2" 的薪资 select salary from user where name = '张无极2'; +--------+ | salary | +--------+ | 500000 | +--------+ -- b. 查询比她工资高的员工数据 select * from user where salary > ( select salary from user where name = '张无极2' ); +----+------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------+------+------+--------+------------+------------+---------+ | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | +----+------+------+------+--------+------------+------------+---------+ 9. 查询比平均薪资高的员工信息 -- a. 查询员工的平均薪资 select avg(salary) from user; +-------------+ | avg(salary) | +-------------+ | 359166.6667 | +-------------+ -- b. 查询比平均薪资高的员工信息 select * from user where salary > ( select avg(salary) from user ); +----+---------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+---------+------+------+--------+------------+------------+---------+ | 1 | 张无极 | 28 | 教主 | 500000 | 2022-08-05 | 0 | 5 | | 32 | 张无极2 | 28 | 教主 | 500000 | 2021-05-03 | 1 | NULL | | 39 | 王五 | 21 | 教主 | 800000 | 2021-05-03 | 1 | 3 | | 42 | 小老弟 | 21 | 教主 | 440000 | 2021-05-26 | 1 | 3 | | 46 | 修女 | 24 | 教主 | 500000 | 2021-05-03 | 1 | 4 | +----+---------+------+------+--------+------------+------------+---------+ 10. 查询低于本部门平均工资的员工信息 -- a. 查询指定部门平均薪资 1 select avg(e1.salary) from user e1 where e1.dept_id = 1; select avg(e1.salary) from user e1 where e1.dept_id = 2; -- b. 查询低于本部门平均工资的员工信息 select * from user e2 where e2.salary < ( select avg(e1.salary) from user e1 where e1.dept_id = e2.dept_id ); +----+------------+------+------+--------+------------+------------+---------+ | id | name | age | job | salary | entrydate | manager_id | dept_id | +----+------------+------+------+--------+------------+------------+---------+ | 38 | 李四 | 21 | 教主 | 220000 | 2021-05-03 | 1 | 3 | | 40 | 苍空 | 21 | 教主 | 100000 | 2021-05-03 | 1 | 3 | | 41 | 波多衣 | 21 | 教主 | 200000 | 2021-05-04 | 1 | 3 | | 43 | 狗蛋 | 31 | 教主 | 200000 | 2021-05-29 | 1 | 4 | | 44 | 金刚 | 31 | 教主 | 200000 | 2022-08-25 | 1 | 7 | +----+------------+------+------+--------+------------+------------+---------+ 11. 查询所有的部门信息, 并统计部门的员工人数 select d.id, d.name , ( select count(*) from user e where e.dept_id = d.id ) '人数' from dept d; +----+--------+------+ | id | name | 人数 | +----+--------+------+ | 3 | 策划部 | 5 | | 4 | 销售部 | 2 | | 5 | 总经办 | 1 | | 7 | 财务部 | 2 | | 8 | 公关部 | 0 | +----+--------+------+ 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称 -- 表: student , course , student_course -- 连接条件: student.id = student_course.studentid , course.id = student_course.courseid select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;
6、事务
事务简介
事务：是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即一组操作要么全部成功，要么全部失败。
默认mysql的事务是自动提交的，也就是说，当执行一条DML语句，mysql会立即隐式的提交事务
事务操作
查看/设置事务的操作方式
查看事务提交方式：select @@autocommit; (1表示自动提交，0表示手动提交)
修改事务的提交方式：set @@autocommit = 0; set @@autocommit = 1;
开启事务
当不修改默认提交方式时（@@autocommit = 1）可以采用手动开启事务的。
start transaction ；
begin;
提交事务
commit;
回滚事务
rollback;
-- ---------------------------- 事务操作 ----------------------------
-- 数据准备
create table account(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
money int comment '余额'
) comment '账户表';
insert into account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);
-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四';
select @@autocommit;
set @@autocommit = 0; -- 设置为手动提交
-- 转账操作 (张三给李四转账1000)
-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
程序执行报错 ...
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback ;
-- 方式二
-- 转账操作 (张三给李四转账1000)
start transaction ;
-- 1. 查询张三账户余额
select * from account where name = '张三';
-- 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
程序执行报错 ...
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback;
-- 查看事务隔离级别
select @@transaction_isolation;
-- 设置事务隔离级别
set session transaction isolation level read uncommitted ;
set session transaction isolation level repeatable read ;
事务的特性
| 特性 | 描述 |
| 原子性 | 事务是不可分割的最小操作单元，要么全部成功要么全部失败 |
| 一致性 | 十五万城市，必须保证所有数据都是一致的 |
| 隔离性 | 数据据库提供的隔离机制，保证事务在不受外部并影响操作的独立环境下运行 |
| 持久性 | 事务一旦提交或回归，他对数据库中的数据的该表是永久的 |
并发事务引发的问题
| 问题 | 描述 |
| 脏读 | 一个事务读取到另一个事务还没提交的数据 |
| 不可重复读 | 一个事务先后读取同一条纪律，但两次读取的数据不同 |
| 幻读 | 一个事务按条件查询数据时，没有查询到。此时另一个并发事务操作操作并进行插入数据后，第一个事务也插入同一个数据就会发现无法插入，但是查询依旧查询不到。仿佛发生幻觉。 |
不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。
事务的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| Read Uncommitted | √ | √ | √ |
| Read Committed | × | √ | √ |
| Repeatalbe Read(default) | × | × | √ |
| Serializable | × | × | × |
--查看事务隔离级别
select @@Transaction_ISOLATION
--设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE | SERIALIZABLE}
脏读(Read Uncommitted)
按照标记的顺序执行语句，即可展示隔离级别的效果；
左边事务开启，右边事务开启，右边修改数据但未提交事务，此时左边依旧可以获取到右边修改之后的值
避免脏读(Read Committed)
下图为设置为Read Committed;后可以防止脏读；
不可重复读（Read Committed）
还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题（左边事务未提交，右边事务提交后，左边事务读取的数据发生了变化）。
避免不可重复读（Repeatable read）
Repeatable Read避免了不可重复读的问题。
幻读（Repeatable read）
避免幻读(Serializable)
串行化的情况下，右侧事务新增操作会阻塞左侧事务的查询操作，当右侧事务commit后，左侧事务才可以对同一个表进行操作。（即串行化会锁住整张表）
1909
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
