Part 3 数据库应用（占比 17%）——下

【后续会持续更新CDA Level I&II备考相关内容，敬请期待】

【考试大纲】

【考试内容】

【备考资料】

4、单表查询

4.1、虚拟结果集

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户。

那么什么是结果集呢？在DBMS的图形化操作界面中，通过查询语句查询出来的数据以表的形式展示，并且存放在内存当中，我们就称这个表是虚拟结果集，而查询返回的结果也是一张虚拟表。

4.2、运算符及聚合函数

• 运算符
  
• 聚合函数
  

4.3、SQL 语句的书写顺序和执行逻辑

• SQL语句的书写顺序

SELECT <目标列组>
FROM <数据源>
[WHERE <元组选择条件>]
[GROUP BY <分组列组> [HAVING <组选择条件>]]
[ORDER BY <排序列1> <排序要求1> [,...n]]
[limit 所需记录行数];

• SQL的执行逻辑
  SQL语句执行顺序：FROM–>ON -->JOIN–>WHERE–>GROUP BY–>AGG_FUNC–>WITH–>HAVING–>SELECT–>UNION–>DISTINCT–>ORDER BY–>LIMIT。在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的数据。
  • FROM：选择FROM后面跟的表，产生虚拟表1。
  • ON：ON是JOIN的连接条件，符合连接条件的行会被记录在虚拟表2中。
  • JOIN：如果指定了LEFT JOIN，那么保留表中未匹配的行就会作为外部行添加到虚拟表2中，产生虚拟表3。如果有多个JOIN链接，会重复执行步骤1~3，直到处理完所有表。
  • WHERE：对虚拟表3进行WHERE条件过滤，符合条件的记录会被插入到虚拟表4中。
  • GROUP BY：根据GROUP BY子句中的列，对虚拟表4中的记录进行分组操作，产生虚拟表5。
  • AGG_FUNC：常用的 Aggregate 函数包涵以下几种：（AVG：返回平均值）、（COUNT：返回行数）、（FIRST：返回第一个记录的值）、（LAST：返回最后一个记录的值）、（MAX： 返回最大值）、（MIN：返回最小值）、（SUM： 返回总和）。
  • WITH 对虚拟表5应用ROLLUP或CUBE选项，生成虚拟表 6。
  • HAVING：对虚拟表6进行HAVING过滤，符合条件的记录会被插入到虚拟表7中。
  • SELECT：SELECT到一步才执行，选择指定的列，插入到虚拟表8中。
  • UNION：UNION连接的两个SELECT查询语句，会重复执行步骤1~9，产生两个虚拟表9，UNION会将这些记录合并到虚拟表10中。
  • DISTINCT 将重复的行从虚拟表10中移除，产生虚拟表 11。DISTINCT用来删除重复行，只保留唯一的。
  • ORDER BY: 将虚拟表11中的记录进行排序，虚拟表12。
  • LIMIT：取出指定行的记录，返回结果集。

4.4、单表查询示例

• 基本查询：去重查询、设置别名

--查询表所有数据
select * from tb_name;
--去重查询stu_name
select distinct stu_name from tb_name;
select stu_name from tb_name group by stu_name;
--设置列别名
select stu_name as name from tb_name;
--设置表别名为b
select stu_name from tb_name b where b.age>=18;

• 条件查询：多条件查询、空值查询、模糊查询

--多条件查询
select * from tb_name where a in (1,2);
select * from tb_name where a=1 or a=2;
select * from tb_name where a=1 and b=2;
--空值查询
select * from tb_name where a is not null;--限制为a列非空的记录
select * from tb_name where a is null;--限制为a列为空的记录
--模糊查询
select * from tb_name where a like '%a%';--包含字母a
select * from tb_name where a like '_a%';--满足字母开头，第二个字母是a这两个条件的字符串

• 分组查询：分组聚合、分组后筛选

--分组聚合
select dt,sum(revenue) as revenue from tb_name where dt>=20230201 group by dt;
--分组后筛选
select 
	dt
	,sum(revenue) as revenue
	,avg(expo_pv) expo_pv 
from tb_name 
where dt=20230201 
group by dt
having sum(revenue)>=1000
;

• 查询结果排序、限制查询结果数量

--查询结果排序
select id,sum(score) as score from tb_name group by id order by score desc;
--限制查询结果数量
select 
	dt
	,stu_name
	,avg(eng_score) as eng_score
	,avg(math_score) as math_score
from tb_name
where gender='male'
group by dt,stu_name
order by eng_score desc,math_score asc
limit 10;

5、多表查询

• 概念：多表查询指的是将两个以上的数据表通过关键字段连接在一起，并从不同表中取不同字段进行查询的方法；
• 关键字段：用来连接两表的内容信息能够匹配的上的字段
• 多表查询的条件
  • 相连的两表中都需要有关键字段；
  • 关键字段中的记录信息能够匹配得上；
  • 最理想的连接状态是两表中的两个关键字段都是主键，而且两个主键的值能够一一匹配的上；
  • 语法：select <select_list> from 表1 xx join 表2 on 表1.key=表2.key 。其中xx代表连接的方向，可以是inner、left、right等关键字。在连接语句前边的表是左表，在连接语句后面的表是右表；

5.1、多表查询的对应关系

• 由于关键字段中有重复值的表为多表，没有重复值的表为一表，而在一张表中主键是没有重复值的，有重复值的是非主键字段，所以一对一，多对多和一对多的三种对应关系又可分别称为：
  • 一对一：主键连主键
  • 多对多：非主键连非主键
  • 一对多：主键连非主键
• 一对一：指多表关联的时候，关联字段值一一对应，一条记录对应一条记录。一对一对应关系在实际应用中是最不可能出现的，因为主键是记录单位，表中所有的字段都是围绕主键进行的，所以当两表的主键相同时是可以放在一个表中的。
• 一对多：指多表关联的时候，关联字段值一对多，一条记录对应多条记录。一对多对应关系是正确的连接关系,但也不是所有的汇总值都正确。
• 多对多：指多表关联的时候，关联字段值多对多，多条记录对应多条记录（记录数可能不一样）。多对多对应关系在实际应用中可能出现，但是不能用，因为汇总时会出现重复项

5.2、连接方式

5.2.1、内连接查询（inner join）

内连接查询（inner join）：按照连接条件合并两个表，返回满足条件的行。

--语法
Select <select_list> from A INNER JOIN B on A.key=B.key;
--示例
select a.id,a.stu_name,b.age 
from table_a a 
[inner] join table_b b on a.id=b.id

5.2.2、左连接查询（left join）

• 左连接查询（left join）：结果中除了包括满足连接条件的行外，还包括左表中的所有行；

--语法
Select <select_list> from A left join B on A.key=B.key;
--示例
select a.id,a.stu_name,b.age 
from table_a a 
left join table_b b on a.id=b.id

5.2.3、右连接查询（right join）

• 右连接查询（right join）：结果中除了包括满足连接条件的行外，还包括右表中的所有行；

--语法
Select <select_list> from A right join B on A.key=B.key;
--示例
select b.id,a.stu_name,b.age 
from table_a a 
right join table_b b on a.id=b.id

5.2.4、联合查询（right join）

• 联合查询
  • union：用于合并两个或多个select语句的结果集，并消去表中任何重复行；
  • union all：用于合并两个或多个select语句的结果集，保留重复行；

  select t1.* from t1  union/union all select t2.* from t2;
  

• 全连接查询(full join)：全连接会返回两张表中全部的记录，本质上是对两个表中的记录取并集；全连接没有主附表的区别，按照连接条件可以匹配到的记录会返回匹配后的结果，匹配不到的记录用null进行填充。

  select 
  	* --包含t1和t2的列合并，为空的列补null，在做全量表的时候会用到
  from t1 full join t2 on t1.key=t2.key
  --等价于下面的
  Select * from t1 left join t2 on key1=key2
  Union
  Select * from t1 right join t2 on key1=key2;
  

5.2.5、等值连接和不等值连接

不等值连接一般用在左连接或右连接中。

--等值连接，以上的都是等值连接
Select * from t1 right join t2 on t1.key=t2.key;
--不等值连接
Select * from t1 right join t2 on t1.key>=t2.key;
Select * from t1 right join t2 on t1.key<=t2.key;

6、子查询

6.1、子查询分类

分为7中子查询，分别是：where型子查询、from型子查询、in子查询、exists子查询、any子查询、all子查询、比较运算符子查询

goods货物表，good_id表的主键,cat_id栏目的编号

• where型子查询：

--查出每个栏目最新的商品(以good_id为最大为最新商品)
select cat_id,good_id,good_name 
from goods 
where good_id in(selct max(good_id) from goods group by cat_id);

• from型子查询：

--查出每个栏目最新的商品(以good_id为最大为最新商品)
select * from (
select cat_id,good_id,good_name 
from goods order by cat_id asc, good_id desc
) as tep 
group by cat_id;

• in子查询：

--查询年龄为20岁的员工部门
 select * from department where did in
 (
 SELECT did from employee where age=20
 );

• exists子查询:

--查询是否存在年龄大于21岁的员工
select * from department where EXISTS 
(
SELECT did from employee where age>21
);

• any子查询：

--查询满足条件的部门，did大于子查询中的任何一个即可
select * from department where did> any 
(
SELECT did from employee 
);

• all子查询：

--查询满足条件的部门，did大于子查询中所有的
select * from department where did> all
(
SELECT did from employee 
);

• 比较运算符子查询：

--查询赵四是哪个部门的
select * from department where did= all
(
SELECT did from employee where name='赵四'
); 

6.2、子查询总结

• where型子查询：指把内部查询的结果作为外层查询的比较条件。
• from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。
• in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较。
• exists子查询：把外层的查询结果，拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。
• any子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
• all子查询：内层子查询返回的结果需同时满足所有内层查询条件。
• 比较运算符子查询：子查询中可以使用的比较运算符如 “>” “<” “= ” “!=”

6.3、子查询操作符

1. And：用来联合多个条件进行查询，条件与条件间是“和”的意思。即条件表达式1 and 条件表达2…and 条件表达式n；
2. Or：用来联合多个条件进行查询，条件与条件间是“或”的意思。即即条件表达式1 or 条件表达式2…or 条件表达式n；
3. in：判断某个字段的值是否在指定的集合中，如果在集合中则满足查询条件，如果不在则不满足查询条件。即in/not in （元素1，元素2，…元素n）
4. between ：判断某个字段的值是否在指定的范围内，如果在则满足查询条件，如果不在则不满足查询条件。【not】 between 取值1 and 取值2；
5. like：用来匹配字符串是否相等，如果字段的值与指定的字符串相匹配，则满足查询条件，如果与指定的字符串不匹配，则不满足查询条件；[not] like 字符串，字符串参数的值可以是一个完整的字符串，也可以是包含%或者_的通配符，其中%代表任意长度的字符串；
   
6. is null：用来判断字段的值是否为空值（null），如果字段的值为空值，则满足查询条件，如果字段的值是非空值，则不满足查询条件。Is [not] null；
7. distinct：用来消除重复记录。Select distinct 字段名；
8. any、all、exists、as
   

6.4、操作符与子查询的组合应用

子查询：写在（）中，把内层查询结果当做外层查询参照的数据表来用；

1. any:表示满足其中任意一个条件，使用any关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句；
2. all：表示满足所有条件，使用all时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句；
3. Exist：表示存在，使用exist关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，就返回真值，否则返回假值，当返回真值时，外层查询语句将进行查询，当返回假值时，外层查询语句不进行查询或查询不出任何记录；
4. As重命名：可以将表或字段名重新命名为别的名称使用，只在查询中有效；
5. Limit：查询后只显示limit指定数字的行数结果；

6.5、函数

6.5.1、常用函数

6.5.2、常用的字符串函数

6.5.3、常用的日期及时间函数

6.5.4、开窗函数

感兴趣的可以自行百度，玩转开窗函数，也可以参阅：mysql开窗函数

对数据的每一行，都使用与该行相关的行进行计算并返回计算结果，有几条记录执行完返回结果还是几条；

over（）函数 以及Partition by 子句：
类似group by子句，在over（）函数使用它来指定用来分组的一个或者多个字段，开窗函数在不同的分组内分别执行聚会运算，并将每个组的计算聚合结果显示在组内每条记录中；用法over（Partition by 字段名）；

6.5.6、逻辑函数

逻辑函数：用来对表达式进行判断，根据满足的条件不同，执行相应的流程；

1. 空值函数 ifnull（expression，alt_value）;
2. 判断函数if（expr1,expr2,expr3）;
3. 逻辑表达式 case when … then…end;

6.5.6、其他函数

• Group_concat()：实现一行转多行。返回由属于一组的列值连接组合而成的结果，常与关键字group by 一起使用，能够将分组后指定的字段值都显示出来；
• Cast（）：将一个值转换为指定的数据类型；例如cast(dt as int)，将dt字段转换成int类型

【以上，敬请指正】