文章目录
声明:很多文字和代码参考书籍:《数据库系统中文第6版》
1.select后接字段的规则
任何出现在having子句中,但没有被聚集的属性必须出现在group by子句中,否则查询就被当成是错误的。
2.SQL语句执行顺序
包含聚集、group by或having子句的查询的含义可通过
下述操作序列来定义:
1.与不带聚集的查询情况类似,最先根据from子句来计
算出一个关系。
2.如果出现了where 子句,where 子句中的谓词将应用
到from子句的结果关系上。
3.如果出现了group by子句,满足where谓词的元组通过group by子句形成分组。如果没有group by子句,满足where谓词的整个元组集被当作一个分组。
4.如果出现了having 子句,它将应用到每个分组上;不满足having子句谓词的分组将被拋弃。
5.select子句利用剩下的分组产生出查询结果中的元组,即在每个分组上应用聚集函数来得到单个结果元组。
3.对空值的聚集
聚集函数根据以下原则处理空值:除了count( * )外所有的聚集函数都忽略输人集合中
的空值。由于空值被忽略,有可能造成参加函数运算的输入值集合为空集。规定空集的count运算值为0,其他所有聚集运算在输入为空集的情况下返回一个空值。
4.对布尔数据类型的聚集
布尔数据类型可以取true,false,unknown三个值,它对应的聚集函数有some和every:
some函数是从参数集合里面取一个值,若这个值满足判断条件,结果就是true 。与之对应的是,every必须所有元素满足条件。比如
2
>
s
o
m
e
(
{
1
,
2
,
3
}
)
2>some(\{1,2,3\})
2>some({1,2,3})返回值为true,
4
>
e
v
e
r
y
(
{
1
,
2
,
3
}
)
4>every(\{1,2,3\})
4>every({1,2,3})返回值为true
SQL中any和some含义相同,SQL中some,every对应MySQL中some,all
scores表:
name | exam | score |
---|---|---|
alice | final | 100 |
index | final | 90 |
index | midterm | 90 |
zeta | final | 80 |
zeta | midterm | 80 |
选出分数比zeta同学高(只要比zeta的最低分高就行)的所有人的名字:
SELECT `name`
FROM scores
WHERE
`score`>SOME(SELECT `score` FROM scores WHERE `name`='zeta')
- Output:
5.嵌套子句查询
使用关键词in:
比如:
-
查找在2009年秋季和2010年春季学期同时开课的所有课程:
-
找出既不叫“Mozart”,"Einstein"的教师姓名:
-
还可以像下面这样多属性匹配:
结合之前的聚集函数some:
=some等价于上面的in
而<>some不等价于not in(结合定义很容易知道原因)
6.exists用于空关系测试
exits在作为参数的子查询非空时返回true值。
比如:
- 查找在2009年秋季和2010年春季学期同时开课的所有课程:
后面的子查询结果非空就会返回一个true值
except 子句/运算符用于将两个 select 语句结合在一起,并返回第一个 select 语句的结果中那些不存在于第二个 SELECT 语句结果的记录。(差集)。
- 找出选修了Biology系开设的所有课程的学生:
表student是学生信息表,表course是课程表,表takes是学生选课信息表:
查询代码:
直接理解就是只要Biology的课程集合里有学生选课集合里没有的课程,就说明这个学生没有学习Biology的全部课程,外面的not exists判定两个集合的差集非空,返回false,知道筛选到差集为空时,记录这个学生的id和姓名。
7.查询是否有重复元组
找出所有在2009年只开设一次的课程:
使用unique:
(若参数的子查询结果没有重复元组,则unique将返回true,否则false。若子查询结果为空,也会返回true)
若在unique前面加not (即not unique),则表示查询所有在2009年至少开设两次的课程
上述sql代码等价于:
8. with子句
with子句可以定义临时关系,比如:
找出大学里面具有最大预算值的系:
9.标量子查询
子查询的结果为一行一列,SQL允许只返回单个值的子查询出现在任何地方。(比如select,where等等)
找出大学里面的所有系以及它们拥有的教师数目:(这里子查询出现在select后面)
10.删除操作
P表示一个谓词,r表示一个关系
delete语句首先从r中找出所有使P(t)为真的元组t,然后把它们从r中删除。
删除操作在检查所有元组后,一次性进行删除,而不是一边删除一边检查
比如:找出工资低于大学平均工资的教师记录:
在找出所有低于平均工资的记录后再一起删除(即平均工资不会随着删除的过程而改变)
11.插入操作
想让Music系中每个修满144学分的学生成为Music系的老师,且工资为18000美元:
和删除操作类似,上面的代码是在执行完select之后再执行insert操作,而不是一边select,一边insert
11.更新操作
使用update语句:
给工资超过100000美元的教师涨3%的工资,其余教师涨5%的工资:
这里update执行的顺序很重要,若交换顺序,工资略小于100000美元的教师工资将会增长8%
利用case关键字只需要一条语句执行上面2条语句的更新:
关键词lateral可以使得from子句嵌套的子查询中使用来自from子句其他关系的相关变量
比如访问外层的相关变量I1:
书上提供的术语回顾:
下一章记录SQL中级查询的笔记,包括连接,视图,唯一性约束,授权等等知识点。