CPT103备考宝典 | 关系型数据库查询总结（中）

清臻雨童
143 阅读10分钟

Hi, this is CPT103 Introduction to Databases Exam Prep Guide.

Alright, let's get straight to the point and dive into the main topic.

说在前面

这里是考前总结（无废话版）中，预计阅读时间在一小时内

由于内容过多，基础知识将写在（上），基础拓展将写在（下）中，敬请期待

文中附有英文题目及解析

详细笔记可见 MySQL从入门到入土 | 有道云笔记

本人拙见，如有不当处，还望海涵

参考文章写在文末

Over！祝食用愉快！|ू･ω･` )

绪论

SQL.png

基础拓展.png

DQL

作为SQL最重要的部分，在很多实际应用中查询操作也是最常使用的

核心：围绕SELECT关键字逐渐拓展

基础查询

SELECT 字段1, 字段2 FROM 表名 ;

想查询啥带上啥，如果查询全表，就用*

SELECT * FROM 表名 ;

设置别名

在字段或表名后跟上 AS 别名或直接跟上别名

（考试喜欢不用as，直接跟别名，让题目看起来更复杂）

SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名; 
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

去除重复

在字段前加上DISTINCT关键字

SELECT DISTINCT 字段列表 FROM 表名;

一般在查询特定字段时使用，方便直观分析，比如：

select distinct workaddress '工作地址' from emp;

条件查询

这里使用WHERE关键字来限定范围

image.png

Tips

  1. 逻辑运算符可以连接多个比较运算符

  2. 逻辑运算符可用单词代替

  3. between先最小再最大，注意顺序

  4. like类似正则匹配，_即找含几个字，而%忽略前面几个字符，只需匹配即可，即含这个字。如‘%x’，即找身份证最后一位为x的。 x%，在首；%x, 在尾；%x%在任意一个位置（%可取0）

image.png

image.png

image.png

聚合函数

类似于excel中的统计

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

SELECT 聚合函数(字段列表) FROM 表名 ;

注意：NULL值不参与任何聚合函数

image.png

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];

根据group by分组，在分完组后依然可以通过having增加条件查询

分组查询后主要包括聚合函数和分组字段（统计关系），其他字段无意义

支持多字段分组, 具体语法为 : group by columnA,columnB

执行顺序: where > 聚合函数 > having

where和having区别

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

image.png

排序查询

通过order by展示想要出现的顺序

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

  • ASC : 升序(默认值)
  • DESC: 降序

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

image.png

分页查询

通常展示时如果数据量过多，我们需要通过分页limit使数据展示更合理

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

image.png

注：途中注释错误，每页4条

实战总结

先试着写写

image.png

image.png

执行顺序 ★★★★★

学了这么多查询方法，那么把它们综合起来计算机该如何分析呢

image.png

这里要背，按照常理来不好解释

image.png

考试如果要考顺序，就会按别名的形式来考，让我们判断哪里别名用对了，如上图

总结一下

image.png

多表查询

概述

现在我们需要查看几个表中的数据，但是如果我们仅仅按照之前的思路，在from后面多加几个表名，则会出现数据爆炸

image.png 笛卡尔积，又叫cross join，是SQL中两表连接的一种方式。 假如A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，结果为m*n行。 通常我们都要在实际SQL中避免直接使用笛卡尔积，因为它会使“数据爆炸”，尤其是数据量很大的时候。

image.png 所以我们需要将两表的相同关联部分（外键）找出并保留（外键在下一章会重点提）

也就是在一定要后面加个where条件，即A表的外键=B表的外键

当然了如果员工不满足条件（null），则不会被显示

image.png

内连接

隐式即基本多表连接（默认）

用显式inner也可以省略 image.png

image.png 注意查询的字段要使用表名.字段区分

可以起别名，但起了后就不能用原名了

外连接

outer同样可省略 image.png

image.png

左外/右外可以展示左/右表的全部信息，就算在另一表中没有对应的

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺 序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自己连自己，可以用前面的内外连接任意一种方式

image.png

比方说我们需要查询员工及其所属领导的名字，但是在员工表中有员工有老板，内部相互关联

image.png

把他看成两张表，通过managerid = id 相关联

这里必须起别名，否则分不清 image.png 自连接只是一种模式,而内外链接是方法

我们这里使用的内连接，但是发现金庸没数据了，因为他在表二中为null，所以改进的话可以用左外（展示全部左表）

image.png

联合查询

当我们同时有很多查询结果，我们可以用union将多次结果综合起来

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

image.png union all相当于在查询和查询间加了一个AND关键字 image.png

去掉all相当于在查询和查询间加了一个OR关键字

image.png 两个查询的列数必须保持一致，字段类型也需保持一致

子查询（难点）

查询里套了一个查询 image.png

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询

image.png

这里我们知道部门id，但万一不知道，我们可以将两步简化成一步

image.png

同样的

image.png

列子查询

image.png 注意这里的all和any考的较多，all都满足，any任一满足

还是跟刚刚一样的方式

image.png 相当于只是条件更多 image.png 栗栗子

image.png

栗栗栗子

image.png

行子查询

image.png

还是一个思路

image.png

这也可以写成

image.png

所以综合在一起就是

image.png

表子查询

image.png

image.png

多个行，不能用=

可以相当于列子查询和行子查询结合

image.png

把子查询的结果作为一张表，再和新表连接

image.png

书写技巧

注意点：

1、顺序同DQL，注意过程中间再去改要查询的内容，开始时打*即可

2、带条件的语句可以用显式内连接，然后限定句放在where处

3、三个及以上的表，可以两两联合，再逐级合并

4、n个表格至少有n-1个连接条件

5、先找出表，连接条件，查询条件，不急着打代码

6、函数在select后，和查询对象放一起

练习

EX1

image.png

abcf

这里考察的是删除DELETE，但是我们在sql中发现了SELECT查询语句，所以这里涉及到了子查询（可以应用在任意CURD）

最简单的就是a了，比较基础的删除（0,6）的内容

b与a选项相同，因为SELECT 6和SELECT 0都是简单的选择操作，它们分别返回6和0，所以这个语句也会删除1到5的所有值

c 我们先分析子查询，相当于a的变式，只不过换成了查询，那么得到的就是a中的内容。然后外面的部分就是删去col1在查询中对应部分，也就是删掉a的，那么正确

def可以放一起，它们都是筛选出了小于6的（即1-5的数据），但是ALL要求col1的值小于子查询返回的所有值。但由于子查询可能返回多个小于6的值（例如1, 2, 3, 4, 5），这个条件可能永远不会为真，因此可能不会删除任何记录。而ANY，但只要col1的值小于子查询返回的任何一个值，条件就会为真。所以这里通过小于等于可以有效删去所有值。

EX2

image.png

4

3，注意题目中distinct去掉了重复数据Annie

2，两个都是不等于

5，只要满足一个就可以（比如Annie满足不是Brown）

2，条件是年龄 < 名字长度+18

EX3

image.png

这里考模糊查询，book是名字由deep开始（模糊查询不考虑大小写），那么b错（结束），a错（%可以为空），c错（只找后面跟一个字符的），d对（最合理的），e对（a的改进同时排除都没有的）

fg用了子查询（先看from后），相当于先缩一遍再筛选book名字，由于这里是同一个表（自查询），所以必须取别名，f对

def

EX4

image.png

这里找出赚的最多的人，那就要大于等于所有人（all），b cd 子查询，找p1不存在比他大的p2，d

EX5（难题，可跳）

image.png

7 找出num1大于num2的情况：4>3;4>3，然后由于distinct去掉一个，只剩一个4>3，加起来为7

1,2 从numbers表中选择所有独特的num1值，但这些num1值必须在numbers表中至少有一个对应的num1值大于num3，所以最后显示1,4,2，这里选两个最小的

image.png

这里有个自学内容，CASE..WHEN 是 SQL 中的一个条件表达式，它允许你在查询中执行条件逻辑。你可以使用 CASE..WHEN 来根据一个或多个条件返回不同的值。

首先看到as t 但是没用过t我们知道这是子查询+自查询，那先分析from后的，条件是num2>num3，然后查询的片段是 num3+num1再除4后的余数，并命名成num1.

CASE..WHEN结构，它检查计算得到的num1的值，并返回相应的文本描述。如果num1是1，则返回'bridge'；如果是2，则返回'moon'；如果是3，则返回'sun'；否则，返回'not found'。结果列被命名为words。

那先算子查询的，一共14条数据3,2,1,2,1,0,0,0,3,2,2,0,3,2，最后得出5,3,2

EX6

image.png

这里是材料题，要求我们学习日期函数，但这不是课本要求的，所以我们这里用排除法做（下章会提到）

首先我们要知道日期格式，"HH:MM:SS",然后所有对日期的操作都有函数（因为带了引号，不是单纯数值，不能使用四则运算和比大小）

所以排除a

bcd都是对的（作个引子，不用纠结答案）

EX7

image.png

t1晚于t2，故t1 – t2 是 t1 和 t2 之间的时间差，但由于这是日期格式，不能直接减法

如果需要用时间差，可以使用函数中的DATEDIFF

反正就是不能想当然

EX8

image.png

依照题目我们要找到超过40分钟的，这里考查的是日期格式，依照上题，c，d直接排除

a 40是数据格式，排除

b，e分别为两个函数

资料

注：这里没有数据表很难写出来，可以根据答案分析一下sql，不用尝试自己写

image.png

image.png

image.png

image.png

引用文章

黑马程序员 MySQL数据库入门到精通-哔哩哔哩

avatar
文章
阅读
粉丝