《数据库》第3章 SQL查询语言（上）——概述与单表查询

SQL(Structured Query Language，结构化查询语言)是关系数据库的标准语言

3.1 SQL概述

3.1.1 SQL 语言组成

1. 数据定义语言DDL(Data Definition Language)

定义数据库的逻辑结构：包括数据库、基本表、视图和索引等，扩展DDL还支持存储过程、函数、对象、触发器等的定义。 DDL包括3类语言，即定义、修改和删除

创建对象：CREATE
删除对象：DROP
修改对象：ALTER

2. 数据操纵语言DML(Data Manipuplation Language)

数据库数据的检索和更新：更新操作包括插入、删除和修改；

查询：SELECT
插入：INSERT
修改：UPDATE
删除：DELETE

3. 数据控制语言DCL(Data Control Language)

对数据库的对象进行授权、用户维护(包括创建、修改和删除)、完整性规则定义和事务定义等；

权限授予：GRANT
权限收回：REVOKE

4. 其他

嵌入式SQL和动态SQL：规定SQL语言在宿主语言中的使用规则；扩展SQL还包括数据库数据的重新组织、备份与恢复等功能。

3.1.2 SQL查询基本概念

基本表

• 数据库中独立存在的表称为基本表
• 在SQL中一个关系对应一个基本表
• 一个(或多个)基本表对应一个存储文件
• 一个表可以带若干索引
• 索引存放在存储文件中

视图

• 指从一个或几个基本表(或视图)导出的表，是虚表
• 只存放视图的定义而不存放对应数据

查询表

• 指查询结果对应的表，临时表

存储文件

• 指数据库中存放关系的物理文件

3.2 单表查询

3.2.1 投影运算

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

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

SELECT和FROM是必须的，其他是可选的

基本语法：

  SELECT A1, A2, ..., An
  FROM R1, R2, ..., Rm
  WHERE P

• A1, A2, …, An：代表需要查找的属性或表达式
• R1, R2, …, Rm ：代表查询所涉及的表
• P ：代表谓词(即选择条件)，如果省略WHERE子句，表示P为真
• SQL的查询结果中允许包含重复元组

执行过程（逻辑上的理解）：

• 首先对FROM 子句中R1, R2, …, Rm执行笛卡尔积
• 然后在笛卡尔积中选择使得谓词P为真的记录
• 再在A1, A2, …, An属性列中进行投影运算，不消除重复元组
  • 如需消除重复元组，必须使用关键字DISTINCT

查询指定列

--查询所有班级的班级编号、班级名称和所属学院
SELECT classNo, className, institute
    FROM Class

查询所有列

SELECT *
    FROM Class

给属性列取别名

--查询所有班级的所属学院、班级编号和班级名称。
SELECT institute 所属学院, classNo 班级编号, className 班级名称
   FROM Class

消除重复元组

  SELECT DISTINCT institute
  FROM Class

查询经过计算的列

SELECT courseNo 课程号, lower(courseName) 课程名, courseHour/16 AS 周课时
  FROM Course
-- lower()内置函数

3.2.2 选择运算

WHERE子句：实现关系代数中的选择运算。

WHERE常用的查询条件

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

比较运算

--查询2015级的班级编号、班级名称和所属学院。
SELECT classNo, className, institute
   FROM Class
   WHERE grade=2007

范围查询

• BETWEEN…AND：属性值在某范围内的元组。
• NOT BETWEEN…AND：属性值不在某范围内的元组。
• 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')

空值查询

• 空值：表示未知或不确定的值，空值表示为null
• IS null：查询属性值为空值
• IS NOT null：查询属性值不为空值
• IS不能用“=”替代

--在课程Course表中，查询先修课程为空值的课程。
SELECT *
   FROM Course
   WHERE priorCourse IS 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 '%会计%'

** 如果查询字串中本身要包含%和_，必须使用“ESCAPE <换码字符>”短语，对通配符进行转义处理。**

-- 在班级Class表中，查询班级名称中含有“16_”符号的班级名称。
SELECT className
FROM Class
WHERE className LIKE '%16\_%' ESCAPE '\' --不一定要\也可以用其他,自己定义

• 说明1：“ESCAPE ‘\’”表示\为换码字符
• 说明2：\符号后的_不是通配符，而是普通的查询符号。

逻辑查询

SQL提供AND、OR和NOT逻辑运算符：分别实现逻辑与、逻辑或和逻辑非运算。

-- 在选课Score表中，查询选修了“001”、“005”或“003”课程的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM    Score
WHERE courseNo='001' OR courseNo='005' OR courseNo='003'

--在选课Score表中，查询成绩在80～90分之间的同学学号、课程号和相应成绩。
SELECT studentNo, courseNo, score
FROM    Score
WHERE score>= 80 AND score<=90
   
--在Student表中，查询籍贯既非“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号。
SELECT studentName, native, classNo
FROM    Student
WHERE native!='南昌' AND native!='上海'

注意：不能针对同一属性进行逻辑“与”的等值运算。
下列错误

--如在选课Score表中，查询同时选修了“001”和“002”课程的同学的选课信息，如下查询是错误的，得不到结果：
SELECT *
FROM    Score
WHERE courseNo='001' AND courseNo='002'

3.2.3 排序运算

ORDER BY 子句

实现排序运算，语法格式：

   ORDER  BY  <表达式1>   [ASC | DESC] 
           [, <表达式2>   [ASC | DESC] ... ]

• 格式说明
  • <表达式1>, <表达式2>, … ：可以是属性、函数或表达式；
  • ASC：升序排序，缺省项；
  • DESC：降序排序，必须指明选项。
• 运算含义：实现查询结果的排序。
  • 在查询结果中首先按<表达式1>的值进行排序；
  • 在<表达式1>值相等的情况下再按<表达式2>值排序；
  • 依此类推。

--在学生 Student表中，查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯和所属班级编号，并按籍贯的降序排序输出。
   SELECT studentName, native, classNo
   FROM Student
   WHERE native!='南昌' AND native!='上海'
   ORDER BY native DESC

--在学生Student表中，查询“女”学生的学号、姓名、所属班级编号和出生日期，并按班级编号的升序、出生日期的月份降序排序输出。
   SELECT studentNo, studentName, classNo, birthday
   FROM Student
   WHERE sex='女'
   ORDER BY classNo, month(birthday) DESC

3.2.4 查询表

FROM子句：可以是基本关系、视图，还可以是查询表。

查询1999年出生的“女”同学基本信息。
分析：先查询出女生记录，再对查询表进行选择、投影。

   SELECT studentNo, studentName, birthday
   FROM (SELECT * FROM Student WHERE sex='女') AS a
   WHERE year(birthday)=1999

等价查询语句

   SELECT studentNo, studentName, birthday
   FROM student
   WHERE  year(birthday)=1999  AND  sex='女'

3.2.5 聚合查询

SQL查询提供丰富的数据分类、统计和计算的功能

• 统计功能：通过聚合函数来实现。
• 分类功能：通过分组子句来实现。
• 统计和分组结合：实现丰富的查询功能 。

SQL提供的聚合函数(aggregate function)

• count( [DISTINCT | ALL] {* | <列名>} )
  功能：统计关系中元组个数或一列中值的个数；

• sum( [DISTINCT | ALL] <列名> )
  功能：统计数值型列中值的总和；

• avg( [DISTINCT | ALL] <列名> )
  功能：统计数值型列中值的平均值；
  说明

DISTINCT谓词：消除<列名>中的重复值元组后再统计。
ALL谓词：缺省项，不消除<列名>中的重复值元组就统计。

--查询学生总人数,去除重复计数。
SELECT count(DISTINCT studentNo)  学生人数
FROM Score

--查询学号为“1500003”同学所选修课程的平均分。
    SELECT avg(creditHour) 平均分
    FROM Score
    WHERE studentNo=‘1500003’

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

SQL提供对数据进行分类运算（即分组运算）功能

• 分组运算目的：细化聚合函数的作用对象。
• 若对查询结果不分组，则聚合函数作用于整个查询结果。
• 若对查询结果分组，则聚合函数分别作用于每个组，查询结果按组聚合输出。
• 实现方法：通过GROUP BY和HAVING子句实现分组运算
  • GROUP BY：对查询结果按某一列或某几列分组，值相等的为同一组；
  • HAVING：对分组结果进行选择，仅输出满足条件的分组。注意：该子句必须与ROUP BY子句配合使用。

--查询每个同学的选课门数、平均分和最高分。
SELECT studentNo,  count(*) 门数,  avg(score) 平均分,       
                 max(score) 最高分
FROM  Score
GROUP  BY studentNo

• 功能分析：
  • 按StudentNo分组：将具有相同StudentNo值的元组作为一组；
  • 分组运算：对每组进行相应的计数、求平均值和求最大值。

--查询平均分在80分以上的每个同学的选课门数、平均分和最高分。
SELECT  StudentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分
FROM Score
GROUP BY StudentNo
HAVING avg(score)>=80 --对分组结果进行选择

• 功能分析
  • 按学号StudentNo分组：将StudentNo值相同的元组作为一组
  • 分组运算：进行计数、求平均值和求最大值
  • 选择：选择平均值大于等于80的分组进行结果输出。