一、DQL查询表中数据
DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。
select
from
where
order by
having
函数
group by
limit
1.简单查询
格式：select 字段名，字段名，... from 表名；
注意：
- 查询的所有字段可以用*代替。
- 查询部分字段，必须手动指定查询的字段。
- 查询的字段可以进行数字计算，函数操作...。
- 为字段定义别名，仅改变展示出来的字段名，数据库中的字段名不会改变。
select eid, ename, sex, salary, hire_date, dept_name from emp;
select * from emp;
select ename, salary from emp;
select ename, ename, salary from emp;
-- 定义别名，仅改变展示的名称
select eid, ename, sex, salary, hire_date, dept_name from emp;
select eid as '编号', ename as '姓名', sex as '性别',
salary as '薪资', hire_date as '入职日期', dept_name as '部门名称' from emp;
-- 定义别名简化，as可以省略不写
select eid '编号', ename '姓名', sex '性别',
salary '薪资', hire_date '入职日期', dept_name '部门名称' from emp;
-- 查询到的数据去重
-- 将dept_name中查询到的值去除重
select distinct dept_name from emp;
select distinct dept_name '部门名称' from emp;
-- 查询到数据进行函数计算
select eid as '编号', ename as '姓名', sex as '性别', salary as '薪资',
salary+1000 as '薪资', hire_date as '入职日期', dept_name as '部门名称' from emp;
select eid as '编号', ename as '姓名', sex as '性别', salary as '薪资',
salary+1000 as '薪资', hire_date as '入职日期', dept_name as '部门名称' from emp;
select eid as '编号', ename as '姓名', sex as '性别', salary as '薪资',
salary*12 as '年薪', hire_date as '入职日期', dept_name as '部门名称' from emp;2.条件查询
2.1 条件查询（where）
格式：select 字段名，字段名，... from 表名 where 条件；
关键字: where
执行顺序：
- 从哪个表查询 from。
- 按照指定条件过滤 where。
- 指定返回的字段值 select。
条件：
- 比较： > < >= <= != <>。
- and ，&& 多个条件同时成立。
- between ... and ... 区间值（前后包含）。
- or， || 多个条件成立任意一个即可。
- in(值,值,...) 等于任意一个值即可。
- 值是否为null：is null ，is not null。
-- 查询黄蓉的所有信息
select * from emp where ename = '黄蓉';
-- 查询黄蓉部分信息
-- 执行过程：1.从emp表中查询数据 2.where条件过滤 3.按照指定的字段返回
select eid, ename, salary from emp where ename = '黄蓉';
#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;
#3 查询薪水价格不是5000的所有员工信息
select * from emp where salary != 5000;
select * from emp where salary <> 5000;
#4 查询薪水价格大于6000元的所有员工信息
select * from emp where salary > 6000;
#5 查询薪水价格在5000到10000之间的员工信息
select * from emp where salary>=5000 and salary<=10000;
select * from emp where salary>=5000 && salary<=10000;
select * from emp where salary between 5000 and 10000; -- 前后包含
#6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary=3600 or salary=7200 or salary=20000;
select * from emp where salary=3600 || salary=7200 || salary=20000;
select * from emp where salary in(3600, 7200, 20000); -- 等于任意一个即可
-- null的判断
select * from emp where salary is null;
select * from emp where dept_name is not null;
2.模糊查询
2.1 模糊（like）
格式： select 字段,字段,... from 表名 where 字段 like xxx;
关键字：like
模糊查询中的通配符：
1. %： 0~n个字符。
2. _： 1个字符。
#1 查询含有'八'字的员工信息
select * from emp where ename like '%八%';
#2 查询以'孙'字开头的员工信息
select * from emp where ename like '孙%';
#3 查询第二个字为'兔'的员工信息
select * from emp where ename like '_兔%';3.排序（order by）
排序：将查询出的结果按照指定的要求排序。
关键字：order by
1.单列排序：按照指定列中的值进行排序。
格式： select 字段,字段,... from 表 [where 条件] order by 字段[desc]；
2.多列排序：按照指定多个列中的值进行排序，先按照指定的第一个列中的值排序，出现重复再按照第二个列中的值排序，...
格式： select 字段,字段,... from 表 [where 条件] order by 字段[desc],字段[desc];注：排序默认为升序，添加desc变为降序。
-- 查询所有教学部门的员工信息，薪资升序排列
select * from emp where dept_name='教学部' order by salary; -- 默认使用升序
select * from emp where dept_name='教学部' order by salary asc; -- 手动指定为升序
select * from emp where dept_name='教学部' order by salary desc; -- 手动指定为降序
-- 先按照薪资降序，出现相同的薪资，再按照入职日期升序排序
select * from emp order by salary desc,hire_date;4.函数
函数：方法。将具体的操作封装在方法中，调用方法来完成相关的操作。
函数分类：
1.单行函数：对一行中的数据进行操作，操作多少行返回多少行。
- 字符串函数
- 数值函数
- 日期与时间函数
- 流程控制函数
- 其他函数
2.多行函数：对一列中的数据进行操作，最终返回一个结果。
4.1 单行函数
4.1.1 字符串函数
-- 1.将字母转换为小写
select eid, ename, ename, sex, salary, hire_date, dept_name from emp;
select eid, ename, lower(ename), sex, salary, hire_date, dept_name from emp;
-- 2.将字母转换为大写
select eid, ename, upper(ename), sex, salary, hire_date, dept_name from emp;
-- 3.返回占用的字节数（utf-8编码中一个中文占用3个字节）
select eid, ename, length(ename), sex, salary, hire_date, dept_name from emp;
-- 4.字符串拼接
select eid, ename, sex, salary, hire_date, dept_name, concat(dept_name, ' 门') from emp;
-- 5.左侧填充
select eid, ename, lpad(ename, 5, 'a'), sex, salary, hire_date, dept_name from emp;
-- 6.右侧填充
select eid, ename, rpad(ename, 7, 'oxx'), sex, salary, hire_date, dept_name from emp;
-- 7.去除字符串左右空格
-- 去除左空格
select eid, ename, ltrim(ename), sex, salary, hire_date, dept_name from emp;
-- 去除右空格
select eid, ename, rtrim(ename), sex, salary, hire_date, dept_name from emp;
-- 去除左右空格
select eid, ename, trim(ename), sex, salary, hire_date, dept_name from emp;
-- 8.替换指定的字符
select eid, ename, replace(ename, "悟", "x"), sex, salary, hire_date, dept_name from emp;
-- 9.字符翻转
select eid, ename, reverse(ename), sex, salary, hire_date, dept_name from emp;
-- 10.字符串截取，从1开始
select eid, ename, substr(ename, 1, 2), substring(ename, 1, 2), sex, salary, hire_date, dept_name from emp;
insert into emp(ename) values(" abc "),(" b c d ")
#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat("编号：", eid), concat("姓名：", ename), concat("性别：", sex) from emp;
#2 查询emp表所有数据, 将ename第二个字符都换为 某
-- insert(1,2,3,4) 1:字符串 2:从那个开始 3:几个字符 4:替换的新字符
select eid, ename, insert(ename, 2, 2, "某某"), sex, salary, hire_date, dept_name from emp;
#3 查询emp表所有数据, ename只显示姓
select eid, substr(ename, 1, 1), sex, salary, hire_date, dept_name from emp;
select eid, concat(substr(ename, 1, 1), "某某"), sex, salary, hire_date, dept_name from emp;4.1.2 数值函数
对数字进行操作。
select abs(1), abs(-2) from dual; -- dual虚表，例如直接操作值，值不属于某个表的字段，可以使用虚表。
-- 返回num的绝对值
select abs(1), abs(-2); -- dual虚表可以省略
-- 返回大于num的最小整数（向上取整），返回小于num的最大整数（向下取整），返回圆周率的值，返回num的n次方，返回0~1之间的随机数，返回x四舍五入后的值该值保留到小数点后n位，返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）
select ceil(3.5), floor(3.5), mod(3,2), pi(), pow(2,3), rand(), round(1.245, 2), truncate(1.245,2);4.1.3 日期函数
select curdate(), curtime(), now(), sysdate();
select year("2020-10-11"), month("2020-10-11"), day("2020-10-11");4.1.4 流程控制函数
| IF(condition, t, f) | 如果条件condition为真，则返回t，否则返回f |
| IFNULL(value1, value2) | 如果value1不为null，则返回value1，否则返回value2 |
| NULLIF(value1, value2) | 如果value1等于value2，则返回null，否则返回value1 |
| CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END | 如果条件condition1为真，则返回result1，···，否则返回result |
#1 查询emp表所有数据, 薪资 >= 10000 高工资 其他 低工资
select eid, ename, sex, salary, hire_date, dept_name from emp;
select eid, ename, sex, salary, if(salary>=10000, "高工资", "低工资"), hire_date, dept_name from emp;
#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, sex, salary "月薪", salary*12 "年薪", hire_date, dept_name from emp;
select eid, ename, sex, salary "月薪", salary*12 "年薪", salary*12+30000 "年薪含年终奖", hire_date, dept_name from emp;
select eid, ename, sex, salary "月薪", salary*12 "年薪", ifnull(salary, 0)*12+30000 "年薪含年终奖", hire_date, dept_name from emp;
#3 查询emp表所有数据, 薪资 >=3000 加把劲 >=5000 加油哦 >=9000 坚持住 >= 15000 优秀 其他 不及格
select eid, ename, sex, salary,
case
when salary>=15000 then "优秀"
when salary>=9000 then "坚持住"
when salary>=5000 then "加油哦"
when salary>=3000 then "加把劲"
else "不及格"
end, hire_date, dept_name from emp;4.1.5 其它函数
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前MySQL的版本号 |
| USER() | 返回当前登录的用户名 |
| INET_ATON(IP) | 返回IP地址的数字表示 |
| INET_NTOA | 返回数字代表的IP地址 |
| PASSWORD(str) | 实现对字符串str的加密操作 |
| FORMAT(num, n) | 实现对数字num的格式化操作，保留n位小数 |
| CONVERT(data, type) | 实现将数据data转换成type类型的操作 |
4.2 多行函数
多行函数会忽略null空值。
多行函数也称为分组函数, 聚合函数
| count(字段) | 统计指定列不为null的记录行数 |
| sum(字段) | 计算指定列的数值和 |
| max(字段) | 计算指定列的最大值 |
| min(字段) | 计算指定列的最小值 |
| avg(字段) | 计算指定列的平均值 |
#1 查询员工的总数
-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段
select count(eid) from emp;
-- 所有字段匹配查询
select count(*) form emp;
-- 增加一列
select 1 from emp;
-- 效率更高推荐使用
select count(1) from emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ...
select
sum(salary) '总薪水',
max(salary) '最高薪水',
min(salary) '最小薪水',
avg(salary) '平均薪水'
from emp;
#3 查询薪水大于4000员工的个数
select count(1) from emp where salary > 4000;
#4 查询部门为'教学部'的所有员工的个数
select count(1) from emp where dept_name = '教学部';
#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';
#6 查询部门的个数
select dept_name from emp; -- 9个
select count(dept_name) from emp; -- 8个
-- 部门去重之后， 统计个数
select count(distinct dept_name) from emp; -- 3个4.3 面试题
5.分组（group by）
5.1 分组相关
分组查询指的是使用group不要语句。对查询的信息进行分组，相同数据作为一组。
语法格式：
select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];group by分组过程:
5.2 注意事项
注意事项:
-
分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作。
-
查询其他字段没有意义 。
-
需要在分组后，对数据进行过滤，where的作用 是在分组前过滤。
-
select语句执行顺序：from -- where --- group by --having -- select -- order by。
-
分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于where。
5.3 where和having的区别
| 过滤方式 | 特点 |
|---|---|
| where | 分组之前的过滤后边不能写多行函数 |
| having | 分组之后的过滤后边可以写多行函数 |
演示:
#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称及最小薪资
-- 每个部门的最小工资
select dept_name, min(salary) from emp group by dept_name;
select dept_name, min(salary) from emp group by dept_name having min(salary) < 4000;
#2 统计平均工资大于6000的部门名称
-- 每个部门的平均工资
select dept_name, max(salary) from emp group by dept_name;
select dept_name, max(salary) from emp group by dept_name having max(salary) > 6000;
#3 统计人数小于4个人部门的平均工资
-- 每个部门的平均工资和人数
select dept_name, avg(salary), count(1) from emp group by dept_name;
select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;
#4 统计每个部门最高工资, 排除最高工资小于10000的部门
-- 每个部门的最高工资
select dept_name, max(salary) from emp group by dept_name
select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;6.limit关键字
作用：
-
limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)。
-
limit 语法是 MySql的方言, 用来完成分页。
语法格式：
select 字段1, 字段2 ... from 表名 limit offset, length;参数说明
-
offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据。
-
length 返回的行数。
演示:
#1 查询emp表中的前5条数据
select * from emp limit 5; -- 不指定从哪行还是, 默认从0开始
select * from emp limit 0, 5;
#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3二、SQL执行流程
1. SQL语句执行流程
1.1 执行流程图示
1.2 各个组件介绍
1.连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等。
2.缓存（Cache&Buffer ）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。缓存里面包含表缓存、记录缓存、权限缓存等。
3.解析器：主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。
4.预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。
5.优化器（Optimizer ）：在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。
6.执行器：包含执行SQL命令。获取返回结果。生成执行计划等。
7.存储引擎：访问物理文件的媒介。
1.3 执行流程详细说明
1.客户端向服务器端发送SQL命令和连接参数。
2.服务器端连接模块连接并验证。
3.缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。
4.解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行。
5.预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。
6.优化器根据开销自动选择最优执行计划，生成执行计划。
7.执行器执行执行计划，访问存储引擎接口。
8.存储引擎访问物理文件并返回结果。
9.如果开启查询缓存，缓存管理器把结果放入到查询缓存中。
10.返回结果给客户端。
三、多表
1.单表的缺点
冗余, 同一个字段出现大量重复的数据。
2.多表中的外键
2.1 表关系分析
-
员工表中有一个字段dep_id与部门表中的主键对应字段，这个字段就叫做关系属性。
-
拥有关系属性的员工表被称为从表, 与关系属性对应的主键所在的表叫做主表。
2.2 外键约束
外键：指的是在主表中与从表的主键对应的的那个字段, 如员工表的dep_id, 就是外键。
外键约束 ：使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。
2.3 创建外键约束
方式一：
create table emp(
eid int primary key auto_increment,
ename varchar(15),
age int,
did int,
constraint emp_dept_fk foreign key(did) references dept(did)
);
desc emp;方式二：
create table emp(
eid int primary key auto_increment,
ename varchar(15),
age int,
did int
);
alter table emp add constraint e_d_fk foreign key(did) references dept(did);2.4 删除外键约束
格式：alter table 从表名 drop foreign key 外键约束的名称;
alter table employee drop foreign key emp_dep_fk;2.5 外键约束的注意事项
1.外键约束作用
1.操纵从表
- 向从表中添加外检值时，添加的外检值必须在关联主表的主键值中存在。
- 修改从表中外键值时，修改后的外键值必须在关联主表的主键值中存在。
2.操纵主表
- 删除主表中数据时，必须满足从表中的外键值在主表的主键值中存在。
- 变更主表中主键值时，必须满足从表中的外键值在主表的主键值中存在。
/*
外键约束:
1.删除主表中数据时，必须满足从表中的外键值在主表的主键值中存在
2.变更主键中主键值是，必须满足从表中的外键值在主表的主键值中存在
*/
# 方式一：
-- 1.手动删除从表中关联的数据
-- > Affected rows: 3
delete from emp where did=1;
-- 2.删除主表中的数据
-- > Affected rows: 1
delete from dept where did=1;
# 方式二：
-- 1.手动将从表中关联的外键值设置为null
-- > Affected rows: 3
update emp set did=null where did=2;
-- 2.删除主表中的数据
-- > Affected rows: 1
delete from dept where did=2;2. 创建和删除表
- 删除表时：先删除从表，再删除主表。
- 创建表时：先创建主表，再创建从表。
3.外键约束设置
- RESTRICT(默认) 和No action：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。
- setNull：删除或更新主表中数据时，将关联从表中的外键值设置为null。
- cascade：删除主表中数据时，将关联从表中的数据删除。更新主表中主键值时，将关联从表中的外键值变为新的主键值。
四、 多表查询
1.多表关系设计
| 表与表之间的3种关系 | 举例 |
|---|---|
| 一对多关系(最常见) | 员工表和部门表 学生表和班级表 |
| 多对多关系 | 学生表和课程表 用户表和角色表 |
| 一对一关系(使用较少) | 一对一的关系可以设计成一张表 |
2. 笛卡尔积
交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。
笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义 。笛卡尔积便于理解连接查询的原理。
语法格式：select 字段名 from 表1 cross join 表2;
select * from products cross join category; 3.多表查询分类
3.1 内连接
通过条件匹配两张表中的数据，能匹配就显示，不能匹配就不显示。
语法格式：
- SQL92：select 字段名，.... from 表1，表2 where 条件；
- SQL99：select 字段名，... from 表1 [inner] join 表2 where 条件；
3.1.1 等值内连接
判断条件为等值判断，等值内连接：
SQL92：select 字段,... from 表1,表2 where 字段1=字段2;
SQL99：select 字段,... from 表1 join 表2 on 字段1=字段2;
不同表之间：
-- 只获取家电类别及产品信息
select * from category c,products p where c.cid=p.cid and c.cid='c001';
select * from category c join products p on c.cid=p.cid where c.cid='c001';
-- 只获取家电类别及产品信息 排序
select * from category c,products p where c.cid=p.cid and c.cid='c001' order by price;
select * from category c join products p on c.cid=p.cid where c.cid='c001' order by price;
-- 统计所有类别下产品的数量
select cname, count(1) from category c,products p where c.cid=p.cid group by cname;
select cname, count(1) from category c join products p on c.cid=p.cid group by cname;同一张表（自连接）：
-- 自连接
-- 查询所有的员工及员工的领导
-- 自连接的内连接
select e2.empno, e2.ename, e1.empno, e1.ename from emp1 e1, emp1 e2 where e1.empno = e2.mgr;
select e2.empno, e2.ename, e1.empno, e1.ename from emp1 e1 join emp1 e2 on e1.empno = e2.mgr;3.1.2 非等值内连接
判断条件为非等值判断，非等值内连接：
- SQL92：select 字段,... from 表1,表2 where 字段1 between 字段2 and 字段3;
- SQL99：select 字段,... from 表1 join 表2 on 字段1 between 字段2 and 字段3;
-- 内连接，非等值连接
select * from emp1, salgrade where sal between losal and hisal;
select * from emp1 join salgrade on sal between losal and hisal;3.2 外连接
通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。
3.2.1 左外连接
以左表为主，左表中的数据全部显示，右表没有匹配的数据以null填充。
select 字段,... from 表1 left [outter] join 表2 on 条件;
非等值：
-- 左外连接，非等值连接
select * from emp1 left join salgrade on sal between losal and hisal;等值：
select * from category c left outer join products p on c.cid=p.cid;
select * from category c left join products p on c.cid=p.cid;自连接：
-- 自连接的左外连接
select e1.empno, e1.ename, e2.empno, e2.ename from emp1 e1 left join emp1 e2 on e1.empno = e2.mgr;3.2.2 右外连接
以右表为主，右表中的数据全部显示，左表没有匹配的数据以null填充。
select 字段,... from 表1 right [outter] join 表2 on 条件;
非等值：
select * from emp1 right join salgrade on sal between losal and hisal;等值：
-- 左外连接：左表数据全部显示,右表没有匹配的数据以null填充111
select * from category c left outer join products p on c.cid=p.cid;
select * from category c left join products p on c.cid=p.cid;自连接：
-- 自连接的右外连接
select e1.empno, e1.ename, e2.empno, e2.ename from emp1 e1 right join emp1 e2 on e1.empno = e2.mgr;3.2.3 全外连接
MySQL 中不支持 FULL OUTER JOIN 连接，可以使用 union 实现全完连接。
语法结构：(SELECT 列名 FROM 表名 LEFT OUTER JOIN 表名 ON 连接条件)
UNION
(SELECT 列名 FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件)。
select * from category c left join products p on c.cid=p.cid
union
select * from category c right join products p on c.cid=p.cid;
1550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
