数据库 SQL查询语言理论基础

数据库语言高度非过程化：描述做什么，不涉及怎么做。

SQL基本结构包括3个子句：

• SELECT子句 对应投影运算，指定查询结果中所需要的属性或表达式
• FROM子句 对应笛卡尔积，给出查询所涉及的表，表可以是基本表、视图或查询表
• WHERE子句 对应选择运算（包括连接运算所转化的选择运算），指定查询结果元组所需要满足的选择条件
  SELECT和FROM是必须的，其他是可选的
• DISTINCT 用来消除重复元组的

单表查询

列属性

一、查询指定的列

选取表中的全部列或指定列，通过SELECT确定要查询的属性
例如：查询所有班级的班级编号、班级名称和所属学院
SELECT classNo, className, institute
FROM Class

二、消除重复的元组

需要消除重复元组，使用DISTINCT关键字
[例] 查询所有学院的名称。
SELECT institute
FROM Class
这个例子没有消除重复元组，结果如下

SELECT DISTINCT institute
FROM Class
这个消除重复元组，结果如下：

三、查询所有的列

可使用两种方法：
1、将所有的列在SELECT子句中列出(可以改变列的显示顺序)；
2、使用*符号，*表示所有属性，按照表定义时的顺序显示所有属性
[例] 查询所有班级的全部信息。
SELECT classNo, className, classNum, grade, institute
FROM Class
或
SELECT *
FROM Class

四、给属性取别名

查询所有班级的所属学院、班级编号和班级名称，要求用中文显示列名
SELECT institute 所属学院, classNo 班级编号, className 班级名称
FROM Class
该查询可使用AS关键字取别名：
SELECT institute AS 所属学院, classNo AS 班级编号, className AS 班级名称
FROM Class

五、查询经过计算的列

查询每个班级编号、班级名称以及该班级现在为几年级，并将班级编号中大写字母改为小写字母输出
SELECT lower(classNo) 班级编号, className, year(getdate()) - grade AS 年级
FROM Class

• 函数lower()将大写字母改为小写字母
• 函数getdate()获取当前系统的日期
• 函数year()提取日期中的年份

行属性

一、选择运算

WHERE子句可实现关系代数中的选择运算
WHERE常用的查询条件有：

• 比较运算：>、>=、<、<=、=、<>(或!=)
• 范围查询：[NOT] BETWEEN <值1> AND <值2>
• 集合查询： [NOT] IN <集合>
• 空值查询：IS [NOT] null
• 字符匹配查询： [NOT] LIKE <匹配字符串>
• 逻辑查询：AND、OR、NOT

二、比较运算

使用比较运算符>、>=、<、<=、=、<>(或!=)
[例] 查询2007级的班级编号、班级名称和所属学院。
SELECT classNo, className, institute
FROM Class
WHERE grade=2007
[例] 在学生Student表中查询年龄大于或等于19岁的同学学号、姓名和出生日期。
SELECT studentNo, studentName, birthday
FROM Student
WHERE year(getdate()) - year(birthday)>=19

三、范围查询

BETWEEN…AND用于查询属性值在某一个范围内的元组，这种一般可以用逻辑and来表示
NOT BETWEEN…AND用于查询属性值不在某一个范围内的元组，这种一般可以用逻辑or来表示
BETWEEN后是属性的下限值，AND后是属性的上限值
[例] 在选课Score表中查询成绩在80～90分之间的同学学号、课程号和相应成绩
SELECT studentNo, courseNo, score
FROM Score
WHERE score BETWEEN 80 AND 90

四、集合查询

IN用于查询属性值在某个集合内的元组
NOT IN用于查询属性值不在某个集合内的元组
IN后面是集合，可以是具体的集合，也可以是查询出来的元组集合
[例] 在选课Score表中查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM Score
WHERE courseNo IN (‘001’, ‘005’, ‘003’)
[例] 在学生 Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号。
SELECT studentName, native, classNo
FROM Student
WHERE native NOT IN (‘南昌’, ‘上海’)

五、空值查询

空值表示未知或不确定的值，空值表示为null
IS null用于查询属性值为空值
IS NOT null用于查询属性值不为空值
IS不能用“=”替代
[例] 在课程Course表中查询先修课程为空值的课程信息。
SELECT *
FROM Course
WHERE priorCourse IS NULL
[例] 在课程Course表中查询有先修课程的课程信息。
SELECT *
FROM Course
WHERE priorCourse IS NOT NULL

六、字符匹配查询

LIKE用于字符匹配查询，语法格式为：
[NOT] LIKE <匹配字符串> [ESCAPE <换码字符>]

• 查询的含义是：
  如果在LIKE前没有NOT，则查询指定的属性列值与<匹配字符串>相匹配的元组；
  如果在LIKE前有NOT，则查询指定的属性列值不与<匹配字符串>相匹配的元组。
• <匹配字符串>可以是一个具体的字符串，也可以包括通配符%和_
• %表示任意长度的字符串
  ab%，表示所有以ab开头的任意长度的字符串；
  zhang%ab，表示以zhang开头，以ab结束，中间可以是任意个字符的字符串。
• 符号_(下划线)表示任意一个字符
  ab_，表示所有以 ab开头的3个字符的字符串，其中第3个字符为任意字符；
  a_ b表示所有以a开头，以b 结束的4个字符的字符串，且第2、3个字符为任意字符。
  [例] 在班级Class表中查询班级名称中含有会计的班级信息
  SELECT *
  FROM Class
  WHERE className LIKE ‘%会计%’
  注意：匹配字符串必须用一对单引号括起来
  [例] 在学生Student表中查询所有姓王且全名为3个汉字的同学学号和姓名
  SELECT studentNo, studentName
  FROM Student
  WHERE studentName LIKE '王_’
  注意：在中文SQL-Server中，如果匹配字符串为汉字，则一个下划线代表一个汉字；如果是西文，则一个下划线代表一个字符
  [例3.16] 在学生Student表中查询名字中不含有“福”的同学学号和姓名。
  SELECT studentNo, studentName
  FROM Student
  WHERE studentName NOT LIKE ‘%福%’
• 如果查询的字符串本身就含有%和_，就必须使用“ESCAPE <换码字符>”短语，对通配符进行转义处理
  [例3.18] 在班级Class表中查询班级名称中含有“08_”符号的班级名称
  SELECT className
  FROM Class
  WHERE className LIKE ‘%08_%’ ESCAPE ‘’
  “ESCAPE ‘\’”表示\为换码字符
  紧跟在\符号后的_不是通配符，而是普通的用户要查询的符号
  查询的结果如下：
  
• 这里有一个很重要的点：不能对同一个属性进行and运算
  如在选课Score表中查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：
  SELECT *
  FROM Score
  WHERE courseNo=‘001’ AND courseNo=‘002’
  要实现该查询，需要使用连接运算或嵌套子查询

七、排序算法

ORDER BY <表达式1> [ASC | DESC] [, <表达式2> [ASC | DESC], … ]
ASC是升序排序，我们默认都是升序排序的，DESC是降序排序
该运算含义是：
在查询结果中首先按<表达式1>的值进行排序
在<表达式1>值相等的情况下再按<表达式2>值排序
依此类推
*
[例3.24] 在学生 Student表中查询籍贯不是“南昌”或“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。
SELECT studentName, native, classNo
FROM Student
WHERE native!=‘南昌’ AND native!=‘上海’
ORDER BY native DESC

查询表

FROM子句后面可以是基本关系、视图，还可以是(子)查询表，当后面是一个子查询表的时候，代表是对这个子查询进行查询

连接查询（多表连接）

连接查询包含：等值连接、自然连接、非等值连接、自表连接、外连接

等值与非等值连接

该运算在WHERE子句中加入连接多个关系的连接条件

• 格式为：
  WHERE [<表1>.]<属性名1> <比较运算符> [<表2>.]<属性名2>
  [ <逻辑运算符>
  [<表3>.]<属性名3> <比较运算符> [<表4>.]<属性名4> … ]
• 比较运算符包括：
  、>=、<、<=、=、<>(或!=)
• 当比较运算符为=时，表示等值连接
• 其他运算为非等值连接
• WHERE子句的连接谓词中的属性称为连接属性,连接属性之间必须具有可比性，也就是这两个属性之间有包含关系

一、等值连接
当比较运算符为=时，表示等值连接

[例] 查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。(这里值得注意的是，不能在同一个表中对同一个属性进行and操作，这样系统会搜索结果是无)
本查询语句为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, (SELECT * FROM Score WHERE courseNo=‘002’) c
WHERE b.courseNo=‘001’
AND a.studentNo=b.studentNo // 表a与表b的连接条件
AND a.studentNo=c.studentNo // 表a与表c的连接条件
ORDER BY a.studentNo
该查询还可以表示为：
SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, Score c
WHERE a.studentNo=b.studentNo // 表a与表b的连接条件
AND a.studentNo=c.studentNo // 表a与表c的连接条件
AND b.courseNo=‘001’ // 表b上的选择条件
AND c.courseNo=‘002’ // 表c上的选择条件
ORDER BY a.studentNo
这个查询特别要注意的是：c.courseNo, c.score 也要一起输出

二、外连接
在一般的连接中，只有满足连接条件的元组才被检索出来，对于没有满足连接条件的元组是不作为结果被检索出来的。
在实际应用中，往往需要将不满足连接条件的元组也检索出来，只是在相应的位置用空值替代，这种查询称为外连接查询

1、左外连接：
连接结果中包含左关系中的所有元组，对于左关系中没有连接上的元组，其右关系中的相应属性用空值替代

2、右外连接：
连接结果中包含右关系中的所有元组，对于右关系中没有连接上的元组，其左关系中的相应属性用空值替代

3、全外连接
连接结果中包含左、右关系中的所有元组
对左关系中没有连接上的元组，其右关系中的相应属性用空值替代
对右关系中没有连接上的元组，其左关系中的相应属性用空值替代

聚合查询

在聚合函数遇到空值时，除count(*)外所有的函数皆跳过空值，只处理非空值。

例如：[例] 查询平均分在80分以上的每个同学的选课门数、平均分和最高分。
SELECT StudentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分
FROM Score
GROUP BY StudentNo
HAVING avg(score)>=80

• 按学号StudentNo分组，将StudentNo值相同的元组作为一组
• 然后对每组进行计数、求平均值和求最大值
• 并判断平均值是否大于等于80，如果是则输出该组，否则丢弃该组，不作为输出结果

例如：[例] 查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分，并按学号排序输出。
SELECT a.studentNo, studentName, sum(creditHour)
FROM Student a, Course b, Score c
WHERE a.studentNo=c.studentNo AND c.courseNo=b.courseNo AND score>=60
GROUP BY a.studentNo, studentName – 输出结果的需要
HAVING sum(creditHour)>=28
ORDER BY a.studentNo
本例输出结果中需要同时包含学号和姓名
因此，GROUP BY子句需要按“a.studentNo, studentName”进行聚合，不能仅按“a.studentNo”进行聚合，否则无法输出