数据库系统第三章 关系数据库标准语言SQL

第三章 关系数据库标准语言SQL

(一) SQL概述

SQL（Structured Query Language）:结构化查询语言，是关系数据库的标准语言。
SQL是一个通用的、功能极强的关系数据库语言。

1. SQL的产生与发展

SQL标准的进展过程：

标准 大致页数 发布日期 标准 大致页数 发布日期
SQL/86 1986年10月 SQL2003 3600页 2003年
SQL/89(FIPS 127-1) 120页 1989年 SQL2008 3777页 2006年
SQL/92 622页 1992年 SQL2011 2010年
SQL99(SQL3) 1700页 1999年

2. SQL的特点

  1. 综合统一
    1. 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
    2. 可以独立完成数据库生命周期中的全部活动：
      定义关系模式，插入数据，建立数据库；对数据库中的数据进行查询和更新；数据库重构和维护；数据库安全性、完整性控制等。
    3. 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。
    4. 数据操作符统一。
  2. 高度非过程化
    1. 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径。
    2. SQL只要提出“做什么”，无须了解存取路径。
    3. 存取路径的选择以及SQL的操作过程由系统自动完成。
  3. 面向集合的操作方式
    1. 非关系数据模型采用面向记录的操作方式，操作对象是一条记录。
    2. SQL采用集合操作方式
      操作对象、查找结果可以是元组的集合；
      一次插入、删除、更新操作的对象可以是元组的集合。
  4. 以同一种语法结构提供多种使用方式
    1. SQL是独立的语言
      能够独立地用于联机交互的使用方式。
    2. SQL又是嵌入式语言
      SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用。
  5. 语言简洁，易用易学
    SQL功能极强，完成核心功能只用了9个动词。
SQL功能 动词
数据查询 SELECT
数据定义 CREATE，DROP，ALTER
数据操纵 INSERT，UPDATE，DELETE
数据控制 GRANT，REVOKE

3. SQL基本概念(三层结构)

SQL支持关系数据库三级模式结构。

image-20230122163618455

  1. 基本表
    • 本身独立存在的表；
    • SQL中一个关系就对应一个基本表；
    • 一个（或多个）基本表对应一个存储文件；
    • 一个表可以带若干索引。
  2. 存储文件
    • 逻辑结构组成了关系数据库的内模式；
    • 物理结构是任意的，对用户透明。
  3. 视图
    • 从一个或几个基本表导出的表；
    • 数据库中只存放视图的定义而不存放视图对应的数据；
    • 视图是一个虚表；
    • 用户可以在视图上再定义视图。

(二) 本章使用到的表

学生-课程模式S-T：
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)

学号Sno 姓名Sname 性别Ssex 年龄Sage 所在系Sdept
200215121 李勇 男 20 CS
200215122 刘晨 女 19 CS
200215123 王敏 女 18 CS
200215125 张力 男 19 IS

课程表：Course(Cno,Cname,Cpno,Ccredit)

课程号Cno 课程名Cname 先行课Cpno 学分Ccredit
1 数据库 5 4
2 数学 2
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 2
7 PASCAL语言 6 4

学生选课表：SC(Sno,Cno,Grade)

学号Sno 课程号Cno 成绩Grade
200215121 1 92
200215121 2 85
200215121 3 88
200215122 2 90
200215122 3 80

(三) 数据定义

SQL的数据定义功能：模式定义、表定义、视图和索引的定义。

操作对象 创建 删除 修改
模式 CREATE SCHEME DROP SCHEME
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX ALTER INDEX

1. 模式的定义与删除

(1) 模式的定义

  1. 定义模式
    语句格式如下：

    CREATE SCHEMA  <模式名>  AUTHORIZATION  <用户名>；

说明：

  1. 若没有指定模式名，那么<模式名>隐含为<用户名>。

  2. 在CREATE SCHEMA中科院接受CREATE TABLE，CREATE VIEW和GRANT子句。格式如下：

CREATE SCHEMA  <模式名>  AUTHORIZATION  <用户名>[<表定义子句>]|<视图定义子句>|<授权定义子句>]；

CREATE SCHEMA TEST AUTHORIZATION ZHANGCREATE TABLE TAB1 (
    COL1 SMALLINT,
    COL2 INT,
    COL3 CHAR(20),
    COL4 NUMERIC(10.3),
    cOL5 DECIMAL(5.2)
);

(2) 模式的删除

DROP SCHEMA  <模式名> <CASCADE|RESTRICT>；

说明：

  1. CASCADE和RESTRICT必须二选一。

  2. CASCADE（级联）：删除模式的同时把该模式中所有的数据库对象全部删除。

    即使模式中有数据，也会全部删除。级联的内容都会删除的，在后面删除列的时候也会这样

  3. RESTRICT（限制）：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。

    如果模式中有数据，则删除失败

执行创建模式语句必须拥有DBA权限，或者DBA授予在CREATE SCHEMA的权限。

2. 基本表的定义、删除与修改

(1) 基本表的定义

语句格式

CREATE TABLE <表名>(
    <列名> <数据类型>[ <列级完整性约束条件> ]
    [，<列名> <数据类型>[ <列级完整性约束条件>] ]
    ………
    [，<表级完整性约束条件> ]
);

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

注意这个标级完整性约束

【例】建立“学生”表Student，学号是主码，姓名取值唯一

CREATE TABLE student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2), # 中文的一个字符相当于英文的两个字符
    Sage SMALLINT,   # 1个字节的int
    Sdept CHAR(20)
);

【例】建立一个“课程”表Course

CREATE TABLE Course(
    Cno char(4) PRIMARY KEY,
    Cname char(40),
    Cpno char(4),
    Credit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course(Cno)  # Cpno是先导课的课程号，是参照列，被参照表是Course，被参照列是Cno
);

【例】建立一个“学生选课”表SC

CREATE TABLE SC(
	Sno char(9),
	Cno char(4),
	Grade SMALLINT,
	PRIMARY KEY(Sno, Cno), #主键涉及到两列，放在表级完整性定义中
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
);

(2) 数据类型

数据类型 含义 说明
CHAR(n) 长度为n的定长字符串 CHAR(9)，即使存储'1'，实际也会使用9个字符的存储单元，定长
VARCHAR(n) 最大长度为n的变长字符串 VARCHAR(9)，即使存储'1'，只会使用1个字符的存储单元，变长
INT 长整数也可以写作（INTEGER）
SMALLINT 短整数
NUMERIC(p,d) 定点数，由p位数字（不包括符号、小数点）组成，小数点后面有d位数字 NUMERIC(10,3)，那么就有7位整数，3位小数。大精度的小数使用这个
REAL 取决于机器精度的浮点数
Double Precision 取决于及其精度的双精度浮点数
FLOAT(n) 浮点数，精度至少为n位数字
DATE 日期，包含年、月、日，格式为YYYY-MM-DD
TIME 时间，包含一日的时、分、秒，格式为HH-MM-SS

(3) 模式与表

可以认为一个数据库有多个模式，一个模式有多个表

每一个基本表都属于某一个模式，一个模式包含多个基本表。

搜索路径

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式。

  1. 显示当前的搜索路径：
SHOW search_path;
  1. 搜索路径的当前默认值是：
$user，PUBLIC；
  1. DBA用户可以设置搜索路径。
SET search_path TO "S-T"，PUBLIC；（"S-T"是模式名）

实际上这些不用管的，因为在登录的时候就会为用户匹配一个模式

  • 若搜索路径中的模式名都不存在，系统将给出错误。
  • 若搜索路径中的存在模式，RDBMS会使用模式列表中第一个存在的模式作为数据库对象的模式名。
创建基本表

（以定义一个学生-课程模式S-T为例）

  1. 创建表是给出模式名。
CREATE TABLE "S-T" .Student(......)；/*模式名为S-T*/
CREATE TABLE "S-T" .CourdeStudent(......)；
CREATE TABLE "S-T" .SCt(......)；
  1. 再创建模式语句中同时创建表。
CREATE SCHEME TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1(
    COL1 SMALLINT,
    COL2 INT,
    COL3 CHAR(20),
    COL4 NUMERIC(10,3),
    COL5 DECIMAL(5,2)
);

  1. 设置所属模式，再创建表名中不必给出模式名。

    DBA用户设置搜索路径，然后定义基本表。

SET search_path TO  "S-T"，PUBLIC；
CREATE TABLE Student  (......);

(4) 修改基本表

语句格式：

ALTER TABLE  <表名>
[ADD [COLUMN]  <新列名>  <数据类型>  [完整性约束]]
[ADD  <表级完整性约束>]
[DROP  [COLUMN]  <列名>  [CASCADE|RESTRICT]]
[DROP  CONSTRAINT  <完整性约束名>  [RESTRICT|CASCADE]]
[ALTER  COLUMN  <列名>  <数据类型>]；

说明：

  1. <表名>是要修改的基本表。
  2. ADD子句用于增加新列、新的列级完整性约束条件和新的表级完整性约束条件。
  3. DROP COLUMN子句用于删除表中的列。
    • 如果指定了CASCADE短语 ，则自动删除引用该列的其他对象。（会把将这列作为外键的所有的列都删除）
    • 如果指定了RESTRICT短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列。
  4. DROP CONSTRAINT子句用于删除指定的完整性约束条件。
  5. ALTER COLUMN子句用于修改原有的列定义，包括修改列名和数据类型。
  6. 不论基本表中原来是否已有数据，新增加的列一律为空值。

【例】向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE;

【例】****将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student MODIFY COLUMN Sage INT;

【例】增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);

注意，这里的SQL语句与mysql的语句还是有区别的

  1. 只修改列的数据类型的方法
alter table 表名 modify column 列名 新的列的类型

这里使用的是modify

  1. 同时修改列名和列的数据类型的方法
alter table 表名 change column 旧列名 新列名 新的列类型

这里使用的是change

(5) 删除基本表

DROP TABLE  <表名>[RESTRICT|CASCADE]；

说明：

  1. RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用。如果存在依赖该表的对象，则此表不能被删除。

  2. CASCADE：删除该表没有限制。在删除基本表的同时，相关的依赖对象一起删除。

    SC表的Sno依赖于Student的Sno，如果使用Cascade方式删除student表，SC表也会一并去掉

  3. 基本表定义被删除，数据被删除，表上建立的索引、视图、触发器等一般也将被删除。

    这不管是RESTRICT还是CASCADE方式都是如此

image-20230122205729520

3. 索引的建立与删除

(1) 概述

  1. 建立索引的目的：加快查询速度。

  2. 谁可以建立索引：DBA或表的属主（即建立表的人）。

  3. DBMS一般会自动建立以下列上的索引：

    • PRIMARY KEY；
    • UNIQUE；

  4. 谁维护索引：DBMS自动完成。（删除行或者添加行，索引会自动更新）

  5. 使用索引：DBMS自动选择是否使用索引及使用那些索引。

  6. RDBMS(R是关系的意思)中索引一般采用B+树、HASH索引来实现。

    • B+树索引具有动态平衡的优点
    • HASH索引具有查找速度快的特点。

    采用B+树还是HASH索引则由具体的RDBMS来决定。

  7. 索引是关系数据库内部实现技术，属于内模式的范畴。

  8. CREATE INDEX语句定义索引时，可以定义索引是唯一索引、非唯一索引或聚簇索引。

索引就是一个目录，目录记录着数据的物理存储地址，假如我们以Student的表的Sno列作为索引，那么就会形成以下目录

目录号 Sno(索引添加的列) 实际物理地址
1 001 E:/xxxx
2 003 C:/xxxx
3 123 D:/xxxx

(2) 建立索引

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；
  • 这个次序是指最终目录的次序
  • 如果不写[UNIQUE] [CLUSTER]，默认是非唯一索引
唯一索引
  1. UNIQUE表明此索引每一个索引值只对应唯一的数据。

【例】为S-T数据库中的Student，Course，SC三个表建立索引

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);
索引的次序

上面的SC表的索引设置了属性

假如原来表的结构如下

Sno Cno
002 0001
001 0000
002 0003
001 0002
001 0004

最终索引的顺序就会先按照Sno顺序，Cno倒序

目录号 Sno(顺序) Cno(倒序) 物理地址
1 001 0004
2 001 0002
3 001 0000
4 002 0003
5 002 0001
聚簇索引
  1. CLUSTER表示要建立的索引是聚簇索引。聚簇索引是指索引顺序与表中记录的物理顺序一致的索引组织。

相当于书的目录

在建立聚簇索引时回生成一个目录，然后更改数据对应的物理地址，使得对应数据存储地址的先后数据与索引的先后顺序一致

原先数据的存储地址一般是不连续的，先后顺序也没有确定

  • 在最经常查询的列上建立聚簇索引以提高查询效率；

    因为索引更改了数据的物理地址，使其前后顺序与索引一致

  • 一个基本表上最多只能建立一个聚簇索引；

    因为数据的物理地址是会改变的，只能跟随一个列进行改变

  • 经常更新的列不宜建立局促索引。

    否则会经常变动物理地址

(3) 删除索引

语句格式：

DROP INDEX <索引名>；
DROP INDEX <索引名> ON <表名>;
DROP INDEX <表名>.<索引名>;

删除索引时，系统会从数据字典中删去有关该索引的描述。

4. 数据字典

  1. 数据字典是关系数据库管理系统内部的一组系统表。
  2. 数据字典记录了数据库中所有的定义信息，包括模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。
  3. RDBMS执行SQL数据定义时，实际就是更新数据字典。

(四) 数据查询

语法格式

SELECT [ALL|DISTINCT]<目标列表达式>
[，<目标列表达式>]...
FROM <表名或视图名>[，<表名或视图名>]...
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]]；

注意：

  1. 语句中的字母不分大小写。
  2. 语句中的“，；”等标点符号为英文状态下的半角字符。
  3. [ ]中的内容，不是语句必须的内容，只有为了实现某些功能时才添加。

1. 单表查询

功能：对一个表的内容进行查询。

(1) 选择表中的若干列

查询指定列

格式：在SELECT后面指定列名，FROM后面列所在的表名。

【例】查询全体学生的学号与姓名。

SELECT Sno,Sname 
FROM Student;

image-20230122220800731

查询全部列
  • 选出所有属性列：在SELECT关键字后面列出所有列名 ，将<目标列表达式>指定为 *
SELECT  Sno,Sname,Ssex,Sage,Sdept 
FROM Student;
# 两种方式
SELECT * FROM Student;

image-20230122220846785

查询经过计算的值

SELECT子句的<目标列表达式>可以为：

  • 算术表达式
  • 字符串常量
  • 函数
  • 列别名
a. 算术表达式

【例】查全体学生的姓名及其出生年份。这里假定目前年份是2004年

SELECT Sname,2004-Sage 
FROM Student;

这里2004-Sage是一个表达式

image-20230122221057038

b. 字符串常量及函数

【例】查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名，这里假定目前年份是2004年。

 SELECT Sname,'Year of Birth: ', 2004-Sage, LOWER(Sdept)
 FROM Student;

image-20230122221219774

c. 列别名
SELECT Sname NAME,'Year of Birth: '  BIRTH,
2000-Sage  BIRTHDAY,
LOWER(Sdept)  DEPARTMENT
FROM Student;

image-20230122221247997

(2) 选择表中的若干行(元组)

DISTINCT去掉表中重复的行
  • 如果没有指定DISTINCT关键词，则缺省为ALL
SELECT Sno FROM SC;
/*等价于：*/
SELECT ALL  Sno  FROM SC;

注意这个ALL放的位置

image-20230124234041118

【例】查询选修了课程的学生学号。指定DISTINCT关键词，去掉表中重复的行

SELECT DISTINCT Sno
FROM SC;

image-20230124234116812

查询满足条件的元组（行）

条件查询三段论

这种查询的三个关键词是select from where

  • select是要查的列
  • from是数据来源的表
  • where是查询条件

from的表分别来自select查询的列的表，以及where的条件的表，因此寻找from的表就从这两个点找表

  • 常用的查询条件
查询条件 谓词 说明
比较 =，＞，＜，≥，≤，<>，!>，!<，!=；NOT+上述比较运算符 一般不等于是 <>
确定范围 BETWEEN AND，NOT BETWEEN AND
确定集合 IN，NOT IN
字符匹配 LIKE，NOT LIKE
空值 IS NULL，IS NOT NULL NULL不是0，不能用=来判断
多重条件（逻辑运算） AND，OR，NOT
a. 比较大小

【例】查询计算机科学系全体学生的名单。

SELECT Sname
FROM Student
WHERE Sdept='CS';

【例】 查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage 
FROM Student 
WHERE Sage < 20;

【例】查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno
FROM  SC
WHERE Grade<60;
b. 确定范围[NOT] BETWEEN AND

确定范围的时候多使用between，而不是用两个<、>号

谓词：

  • BETWEEN … AND …
  • NOT BETWEEN … AND …

【例】 查询年龄在20~23岁（包括20岁和23岁）之间的学生的

SELECT Sname,Sdept,Sage
FROM  Student
WHERE Sage 
BETWEEN 20 AND 23;

【例】 查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECT Sname,Sdept,Sage
FROM  Student
WHERE Sage NOT BETWEEN 20 AND 23;
c. 确定集合[NOT] IN

谓词：

  • IN <值表>,
  • NOT IN <值表>

【例】 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM  Student
WHERE Sdept IN ( 'IS','MA','CS' );

【例】 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' );
d. 字符匹配[NOT] LIKE（通配符的使用）

谓词：

  • [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]

【例】 查询学号为201215121的学生的详细情况。

SELECT *    
FROM  Student  
WHERE  Sno LIKE '201215121';
/*等价于：*/ 
SELECT  * 
FROM  Student 
WHERE Sno = '201215121';

这种情况往往还是使用等于

  1. 匹配串为含通配符的字符串

【例】 查询所有姓刘学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex
FROM Student
WHERE  Sname LIKE '刘%';

【例】 查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECT Sname
FROM   Student
WHERE  Sname LIKE '欧阳_';

%和_的区别

通配符 说明
% 可以匹配任意多个的字符，比如 '刘%' 刘后面只能跟一个字符
_ 只能匹配一个的字符，比如 '_刘_' 刘前后都只有一个字符

【例】 查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_阳%';

【例】 查询所有不姓刘的学生姓名。

SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%';

使用换码字符’'将通配符转义为普通字符

ESCAPE '＼' 表示“ ＼” 为换码字符

这个换码字符是可以自定义的

【例】 查询DB_Design课程的课程号和学分。

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

当然这种情况往往是用等于号的

【例】查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

SELECT  *
FROM   Course
WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\';
e. 涉及空值的查询

谓词：

  • IS NULL
  • IS NOT NULL
  • “IS” 不能用 “=” 代替

【例】某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno
FROM  SC
WHERE  Grade IS NULL;

【例】查所有有成绩的学生学号和课程号。

SELECT Sno,Cno
FROM  SC
WHERE  Grade IS NOT NULL;
f. 多重条件查询

逻辑运算符：

  • AND和 OR来联结多个查询条件
  • AND的优先级高于OR
  • 可以用括号改变优先级

可用来实现多种其他谓词

  • [NOT] IN
  • [NOT] BETWEEN … AND …

【例】查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname
FROM  Student
WHERE Sdept= 'CS' AND Sage<20;

【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'IS','MA','CS' );
/*可改写为：*/
SELECT Sname,Ssex
FROM   Student
WHERE  Sdept= 'IS' OR Sdept= 'MA' OR Sdept= 'CS';
ORDER BY子句
  • 可以按一个或多个属性列排序；
  • 升序：ASC；
  • 降序：DESC；
  • 缺省值为升序；

当排序列含空值时：（空值默认为最大值）

  • ASC：排序列为空值的元组最后显示。
  • DESC：排序列为空值的元组最先显示。

【例】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECT Sno,Grade
FROM  SC
WHERE  Cno= '3'
ORDER BY Grade DESC;

【例】查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT  *
FROM  Student
ORDER BY Sdept,Sage DESC;
#可以等同于 ORDER BY Sdept ASC,Sage DESC;
聚集函数
函数 说明
COUNT （[DISTINCT| ALL]*） 统计元组个数
COUNT ([DISTINCT|ALL]<列名>) 统计一列中值的个数
SUM （[DISTINCT|ALL]<列名>） 计算一列值的总和（此列必须是数值型）
AVG （[DISTINCT|ALL]<列名>） 计算一列值的平均值（此值必须是数值型）
MAX （[DISTINCT|ALL]<列名>） 求一列值的最大值
MIN （[DISTINCT|ALL]<列名>） 求一列值的最小值

【例】查询学生总人数。

SELECT COUNT(*)
FROM  Student;

【例】查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)
FROM SC;

【例】计算2号课程的学生平均成绩。

SELECT AVG(Grade)
FROM SC
WHERE Cno= '2';

【例】查询选修2号课程的学生最高分数。

SELECT MAX(Grade)
FROM SC
WHERE Cno= '2';

【例】查询学生201215121选修课程的总学分数。

SELECT SUM(Ccredit)
FROM  SC, Course
WHERE Sno='201215121' AND SC.Cno=Course.Cno;

注意
WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句。

WHERE AVG(*) > 90

这样的方式是不对的

GROUP BY子句

GROUP BY子句分组，细化聚集函数的作用对象

  • 未对查询结果分组，聚集函数将作用于整个查询结果
  • 对查询结果分组后，聚集函数将分别作用于每个组
  • 作用对象是查询的中间结果表
  • 按指定的一列或多列值分组，值相等的为一组
HAVING & WHERE：
  • 作用对象不同
    • WHERE子句作用于基表或视图，从中选择满足条件的元组
    • HAVING短语作用于组，从中选择满足条件的组
  • WHERE子句中是不能用聚集函数作为条件表达式的。

【例】求各个课程号及相应的选课人数。

SELECT Cno,COUNT(Sno)
FROM    SC
GROUP BY Cno;

【例】查询选修了2门以上课程的学生学号。

SELECT Sno
FROM  SC
GROUP BY Sno
HAVING  COUNT(*) >2;

从上面的案例发现，HAVING也可以对没有被SELECT选中的列操作

总结：如果要求对聚集函数进行判断，一定会使用HAVING的，只有HAVING后面可以使用聚集函数

2. 连接查询

  • 连接查询：同时涉及多个表的查询
  • 连接条件或连接谓词：用来连接两个表的条件
  1. 在进行多表查询的时候，先要使用等值连接或者自然连接将两个表连接在一起，然后进行操作
  2. 但实际上，进行连接的时候系统并不会在物理上创建一个表，而是仅仅生成一个中间表在内存中，使用结束之后表就没有了

(1) 连接操作的执行方法

① 嵌套循环法(NESTED-LOOP)
  • 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
  • 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
  • 重复上述操作，直到表1中的全部元组都处理完毕
  • 就相当于嵌套循环遍历两个表
  • 也就是说，连接操作时并不会生成笛卡尔集
② 排序合并法(SORT-MERGE)

常用于=连接

  1. 首先按连接属性对表1和表2排序
  2. 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
  3. 找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续
  4. 重复上述操作，直到表1或表2中的全部元组都处理完毕为止

这个算法值得学习

③ 索引连接(INDEX-JOIN)
  1. 对表2按连接字段建立索引
  2. 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

这样是通过索引提高了查询表二的速度，而不用全部遍历了

(2) 等值连接与非等值连接

一般格式：

格式一：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

其中，比较运算符有：=、>、<、>=、<=、!=

[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

连接字段：连接谓词中的列名称

  • 连接条件中的各连接字段类型必须是可比的，但名字不必是相同的

当连接运算符为“=”称为等值连接，其他运算符称为非等值连接。

等值连接：连接运算符为=

【例】查询每个学生及其选修课程的情况

SELECT  Student.*,SC.*
FROM     Student,SC
WHERE  Student.Sno = SC.Sno;

自然连接

【例】用自然连接完成上例题

自然连接就是将重复的列去掉，select后面不用通配符，而是全一个一个写出来

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM     Student,SC
WHERE  Student.Sno = SC.Sno;

在多表连接的时候，建议在SELECT都写成table.column的形式，这样提高了可阅读性。养成这个习惯

(3) 自身连接

自身连接：一个表与其自己进行连接

  • 需要给表起别名以示区别
  • 由于所有属性名都是同名属性，因此必须使用别名前缀

【例】查询每一门课的间接先修课（即先修课的先修课）

我们先来看下面的表

image-20230125225303248

两个表是完全相同的

1号课程的先修课是5,5的先修课是7，二者的连接条件就是FIRST.Cpno和SECOND.Cno

但是显示的数据是FIRST.Cno与SECOND.Cpno

严格根据三段论来写

SELECT  FIRST.Cno,SECOND.Cpno
FROM  Course  FIRST,Course  SECOND
WHERE FIRST.Cpno = SECOND.Cno;

(4) 外连接

普通连接与外连接的区别：

  • 普通连接操作只输出满足连接条件的元组。
  • 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出。
SELECT column_name(s)
FROM table1//左表
<xxx JOIN> table2//右表
ON table1.column_name=table2.column_name;

外连接分为：

  • 左外连接：列出左边关系中所有的元组。
    LEFT OUT JOIN 表名 ON
  • 右外连接：列出右边关系中所有的元组。
    RIGHT OUT JOIN 表名 ON
  • 内连接：INNER JOIN (JOIN)
  • 全外连接：FULL JOIN (FULL OUTER JOIN)

我们以下面两个表来演示

image-20230125230502114

① INNER JOIN (JOIN)

INNER JOIN：关键字在表中存在至少一个匹配时返回行。

image-20230125230433708

SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC INNER JOIN Course ON (SC.Cno=Course.Cno);
/*INNER JOIN 与 JOIN结果相同*/
SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC  JOIN Course ON (SC.Cno=Course.Cno);

image-20230125230541683

事实上，这个INNER JOIN就是普通的连接查询，都不为NULL时才显示

② LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN：关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

image-20230125230628901

SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC LEFT JOIN Course ON (SC.Cno=Course.Cno);
/*LEFT JOIN 与 LEFT OUTER JOIN结果相同*/
SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC LEFT OUTER JOIN Course ON (SC.Cno=Course.Cno);

image-20230125230643537

③ RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN：关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

image-20230125230706632

SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC RIGHT JOIN Course ON (SC.Cno=Course.Cno);
/*RIGHT JOIN 与 RIGHT OUTER JOIN结果相同*/
SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC RIGHT OUTER JOIN Course ON (SC.Cno=Course.Cno);

image-20230125230724913

④ FULL JOIN (FULL OUTER JOIN)

FULL JOIN：关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

image-20230125230742274

SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC FULL JOIN Course ON (SC.Cno=Course.Cno);
/*FULL JOIN 与 FULL OUTER JOIN结果相同*/
SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC FULL OUTER JOIN Course ON (SC.Cno=Course.Cno);

image-20230125230802856

(5) 多表查询

也叫复合条件连接：WHERE子句中含多个连接条件

有n个表连接，where后面就至少得有n-1个查询条件，避免生成笛卡尔集

【例】查询选修2号课程且成绩在88分以上的所有学生

SELECT Student.Sno, Sname
FROM    Student,SC
WHERE Student.Sno = SC.Sno AND   
/* 连接谓词, 可以将连接谓词放在一行写，其他条件放在另一行*/
SC.Cno= '2' AND SC.Grade > 88;     
/* 其他限定条件 */

【例】查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade
FROM    Student,SC,Course  /*多表连接*/
WHERE Student.Sno = SC.Sno 
and SC.Cno = Course.Cno;

3. 嵌套查询

概述

  • 一个SELECT-FROM-WHERE语句称为一个查询块。

  • 嵌套查询定义：是指将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。

    嵌套查询只能放在WHERE和HAVING中，不能放在SELECT和FROM后面

    在后面基于派生表的查询中，就可以放在那里么，总之那个就不叫嵌套查询了

先看一个例子

 SELECT Sname/*外层查询/父查询*/
 FROM Student
 WHERE Sno IN
(SELECT Sno    /*内层查询/子查询*/
 FROM SC
 WHERE Cno= '2');

说明：

  • 子查询中不能使用ORDER BY子句。

  • 层层嵌套方式反映了SQL语言的结构化。

  • 有些嵌套查询可以用连接运算替代。

    嵌套查询实际上就是先将一个表中要用的数据查出来，根据这些数据在另一个表中查询，那么就可以先将两个表连接起来，最终只要一个表的数据即可

  • 术语：外层查询（父查询）、内层查询（子查询）。

相关子查询和不相关子查询及其执行过程

下面来说一下这两种子查询及其执行原理

  • 不相关子查询：子查询的查询条件不依赖于父查询。
    • 由里向外逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
  • 相关子查询：子查询的查询条件依赖于父查询，整个查询语句称为嵌套查询。
    • 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
    • 然后再取外层表的下一个元组
    • 重复这一过程，直至外层表全部检查完为止

(1) 带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是个集合，用IN谓词表示父查询的条件在子查询结果的集合中。

【例】查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成

  1. 确定“刘晨”所在系名
SELECT  Sdept  
FROM     Student                            
WHERE  Sname= '刘晨';

得到的结果是CS

  1. 查找所有在CS系学习的学生。
SELECT  Sno,Sname,Sdept     
FROM    Student                 
WHERE  Sdept= 'CS';
  1. 将第一步查询嵌入到第二步查询的条件中
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept  IN
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨');

这里使用了IN，实际上，在后面可以使用=的

  • 此查询为不相关子查询。

【例】查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname /*③ 最后在Student关系中取出Sno和Sname*/
FROM    Student  
WHERE Sno  IN
(  SELECT Sno  	/*② 然后在SC关系中找出选修了3号课程的学生学号*/
	 FROM    SC      
	 WHERE  Cno IN
		( SELECT Cno   /*① 首先在Course关系中找出 “信息系统”的课程号,为3号*/
		  FROM Course    
		  WHERE Cname= '信息系统'
     )
);

分三部来走，由内向外逐一进行

用连接查询实现

SELECT Student.Sno, Student.Sname
FROM    Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno 
# 上面的一行是连接条件
# 下面的一行是查询条件
AND Course.Cname='信息系统';

可以看到，SELECT选择的内容都是来自Student表中的，因为嵌套查询就是从子查询表中查出来用在父查询表中的，因此可以先将其连接起来

(2) 带有比较运算符的子查询

  • 带有比较运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户能确切知道内层查询返回的是单个值时，可以用>、<、=、>=、<= 、!=或< >等比较运算符。

    要求，要明确知道返回值是单个值

  • 与ANY或ALL谓词配合使用

【例】假设一个学生只可能在一个系学习，并且必须属于一个系，则在前面可以用= 代替IN：

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept  =
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨');
/*两种方式都可以*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨')
    = Sdept ;

image-20230126104900737

【例】找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno
FROM  SC  x
WHERE Grade >=(SELECT AVG(Grade)  /*相关子查询*/ 
   	           FROM  SC y
              WHERE y.Sno=x.Sno );

上例子可能的执行过程

  1. 从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层查询。
SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121';
  1. 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询：
SELECT Sno, Cno
FROM  SC x
WHERE Grade >=88;
  1. 执行这个查询，得到（200215121，1)，（200215121，3）
  2. 外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为:

image-20230126105052031

(3) 带有ANY（SOME）或ALL谓词的子查询

谓词语义：

  • ANY：任意一个值
  • ALL：所有值

需要配合使用比较运算符:

运算符 说明
>ANY 大于子查询结果中的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
≥ANY 大于等于子查询结果中的某个值
≥ALL 大于等于子查询结果中的所有值
≤ANY 小于等于查询结果中的某个值
≤ALL 小于等于子查询结果中的所有值
=ANY 等于查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值

[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage
FROM    Student
WHERE Sage < ANY (SELECT  Sage
                 FROM    Student
                  WHERE Sdept= 'CS')
   			 AND Sdept <> 'CS' ; /*父查询块中的条件 */

执行过程

  1. RDBMS执行此查询时，首先处理子查询，找出 CS系中所有学生的年龄，构成一个集合(20，19)
  2. 处理父查询，找所有不是CS系且年龄小于 20 或 19的学生

用聚集函数实现[例42]

SELECT Sname,Sage
FROM   Student
WHERE Sage < (SELECT MAX(Sage)
             FROM Student
             WHERE Sdept= 'CS')
          AND Sdept <> 'CS';

[例43] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

  • 方法一：用ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sage < ALL (SELECT Sage
                 FROM Student
                  WHERE Sdept= 'CS')
              AND Sdept <> 'CS';
  • 方法二：用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage < (SELECT MIN(Sage)
               FROM Student
               WHERE Sdept= 'CS')
            AND Sdept <> 'CS';

用聚集函数实现子查询要比直接用ANY、ALL效率更高。ANY、ALL谓词与聚集函数、IN谓词的等价

= <>或!= < <= > >=
ANY IN – <MAX <=MAX >MIN >=MIN
ALL – NOT IN < MIN <=MIN >MAX >=MAX

总之多用聚集函数来代替ANY和ALL

(4) 带有EXISTS谓词的子查询

之前使用ALPHA语句的时候就有存在量词∃与全称量词∀，EXISTS就是在SQL语句中实现这个逻辑的

EXISTS

EXISTS谓词代表存在量词∃，带有EXISTS谓词的子查询只返回逻辑真值“TRUE"或逻辑假值”FALSE“。

  • 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

    • 若内层查询结果非空，则外层的WHERE子句返回真值
    • 若内层查询结果为空，则外层的WHERE子句返回假值

    EXISTS的执行逻辑：

    从外表中拿出一个元组到EXIST里面进行判断，如果判断条件为真，则将这个元组放到结果中去；否则进行下一个

    反复执行直到所有的元组都判断完成为止

  • 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

[例44]查询所有选修了1号课程的学生姓名。

思路分析：

  • 本查询涉及Student和SC关系
  • 在Student中依次取每个元组的Sno值，用此值去检查SC关系
  • 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname送入结果关系
  1. 用嵌套查询
SELECT Sname
FROM Student
WHERE EXISTS(SELECT *
             FROM SC
              WHERE Sno=Student.Sno 
   			AND Cno= '1');
  1. 用连接查询
SELECT Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno 
AND SC.Cno= '1';

EXISTS和连接查询的区别

  1. EXISTS是从外表不断拿一个元组到EXISTS里面进行判断
  2. 连接查询是会先生成一张大表作为中间表，在中间表中查询

选择

  1. EXISTS的效率更高，使用内存更少，但是语义不如连接查询清晰
  2. 连接查询反之
  3. 根据情况选择
NOT EXISTS

[例45] 查询没有选修1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
             FROM SC
              WHERE Sno=Student.Sno 
   			 AND Cno= '1');
EXISTS与其他符号的替换
  • 一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
  • 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换

【例】查询与“刘晨”在同一个系学习的学生

  1. 可以使用IN来实现
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept  IN
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨');
  1. 使用=来实现
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept  =
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨');
  1. 使用EXISTS实现
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS(SELECT *
           FROM Student S2
           WHERE S2.Sdept = S1.Sdept
   		   AND S2.Sname = '刘晨');
  1. 尽管使用的是两张相同的表，但是要在内外用别名区分开来
  2. 将S1中的元组拿进去与S2的元组进行比较
EXISTS/NOT EXISTS实现全称量词

SQL语言中没有全称量词∀。可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

image-20230126114352701

下面使用图形来解释一下

image-20230126114607578

  1. 我们要求都满足P的x
  2. ￢P就是黄色的部分，可以先求出黄色的部分
  3. 然后再取反，￢(∃x(￢P)) = (∀x)P

[例46] 查询选修了全部课程的学生姓名。

可以查出所有的课号，然后用ALL来进行比较

思路：

  • 使用谓词来表示例题：选择一个学生，对于任意一个课程，都选择了
  • 那么可以等价于选择一个学生，不存在一个课程他没选
SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
                 FROM Course
                 WHERE NOT EXISTS(SELECT *
                                  FROM SC
                                  WHERE Sno= Student.Sno
                                   AND Cno= Course.Cno)
                 );

理解

  • 首先从Student里面取出第一个学生，此时Student.Sno = 1，代入到最内层的判断中
    • 然后从Course中取出第二个课程，此时Course.Cno = 1，带入到内圈的判断中
      • 然后从SC中取出第一个元组，Sno = 1， Cno = 1，满足条件，说明存在，则EXISTS为真，NOT EXISTS为假的；由于是只要存在一个为真，就不用再往下查找SC的元素了
    • 然后从Course里取出第二个课程，此时Course.Cno = 2，代入到内圈的判断中
      • 然后从SC中取出第一个元组，Sno = 1，Cno = 1，不满足条件，继续找下一个元组
      • 遍历所有的SC之后，发现没有Sno = 1， Cno = 2的条件，说明这个为假
      • 则最内层的NOT EXISTS返回true
    • 然后由于返回true，则Course不会再遍历，直接返回true
  • 最终最外层的NOT EXISTS返回false，则说明这个学生不符合条件，继续遍历下一个学生

由上可得，EXISTS只要满足一个存在条件，就会停止遍历，但是NOT EXISTS会遍历所有的条件。利用两个NOT EXISTS就会进行三重循环。

Student放在外层是因为最终要查找的是在Student中，事实上三个表的顺序可以更变

这道题也可以不使用EXISTS来完成

在做软件编程的时候，追求清晰第一，效率第二，因此还是不用EXIST了

当然可以用COUNT来进行比较，但是这样的方式貌似不是通用的

EXISTS/NOT EXISTS实现逻辑蕴函
  • SQL语言中没有蕴函(Implication)逻辑运算
  • 可以利用谓词演算将逻辑蕴函谓词等价转换为：

image-20230126141633790

(￢p)∨q

[例47]查询至少选修了学生201215122选修的全部课程的学生号码。

也就是说：一个课程被201215122学生选了->这个课程也会被其他学生选了

那么可以转化：不存在这样的课程 y，201215122选修了y，学生x没有选

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS(SELECT *   # 不存在这样的课程
                FROM SC SCY
                WHERE SCY.Sno = '201215122'  #q：201215122选修了这门课程
   			    AND NOT EXISTS(SELECT *      #p：学生x选修了这门课程， 则NOT EXISTS就是￢p：学生x没有选这门课程
                                FROM SC SCZ
                                WHERE SCZ.Sno=SCX.Sno   # 这里指定学生x
   					        	AND SCZ.Cno=SCY.Cno  # 201215122选修了的课程
   							)
   				);

这里的意思就是严格韩兆上面的转化来的，仔细分析

4. 集合查询

参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同

(1) 并操作UNION

  • UNION：将多个查询结果合并起来时，系统自动去掉重复元组。（UNION会默认去掉重复元素）
  • UNION ALL：将多个查询结果合并起来时，保留重复元组

[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。

  1. 方法一：集合查询
SELECT * FROM Student WHERE Sdept= 'CS'
UNION #这里如果加ALL会包含重复元素
SELECT * FROM Student WHERE Sage<=19;
  1. 方法二：连接查询
SELECT  DISTINCT  *
FROM Student
WHERE Sdept= 'CS'  
OR  Sage<=19;

并操作实际上就是或的关系

DISTINCT也起到了去重的作用

(2) 交操作INTERSECT

[例50] 查询计算机科学系的学生与年龄不大于19岁的学生的交集

  1. 方法一：集合查询
SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;
  1. 方法二：连接查询
SELECT *
FROM Student
WHERE Sdept= 'CS' 
AND  Sage<=19;

交操作是与的关系

(3) 差操作

[例52] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

  1. 方法一：集合查询
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT  *
FROM Student
WHERE Sage <=19;
  1. 方法2：连接查询
SELECT *
FROM Student
WHERE Sdept= 'CS' 
AND  Sage>19;

5. 基于派生表的查询

  • 子查询仅可以出现在WHERE子句中，还可以出现在FROM子句中不，这时子查询生成的临时派生表成为主查询的查询对象。

【例】找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, (SELECT AVG(Grade), Sno FROM sc GROUP BY Cno)
AS Avg_sc(avg_sno, avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno AND SC.Grade > Avg_sc.avg_grade;

先找到课程的平均成绩，然后找学生

  • 注意这里，要给派生表七个别名，列明也要起别名
  • 如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其默认属性。

【例】查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student, (SELECT Sno FROM SC where Cno = '1') AS SC1
WHERE Student.Sno = SC1.Sno;

注意这里自己犯了一个错误

SELECT Sname
FROM Student, (SELECT Sno FROM SC where Cno = '1') AS SC1, # 自己写的时候这里加了一个逗号，错了 
WHERE Student.Sno = SC1.Sno;
  • 注意：通过FROM子句生成派生表时，AS关键字可以省略，但必须为派生关系指定一个别名。而对于基本表，别名是可选择项。

(五) 数据更新

1. 插入数据INSERT INTO VALUES

(1) 插入元组

语句格式

INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>]    …           )

功能:将新元组插入指定表中

说明：

  • INTO子句：属性列的顺序可与表中的顺序不一致，没有指定属性列的默认插入全部。
  • VALUES子句：提供的值必须与INTO子句匹配，值与属性列的个数和值的类型要一致。

INTO如果后面没有属性列，那么VALUES的属性列的个数和数据类型必须与原表一致，不过不确定的可以用NULL。

不能认为VALUES后面如果少了的话会自动认为是前面补齐，后面NULL

［例1］ 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERT INTO  Student (Sno，Sname，Ssex，Sdept，Sage)
VALUES ('200215128'，'陈冬'，'男'，'IS'，18)；

［例2］ 将学生张成民的信息插入到Student表中。

INSERT INTO  Student
VALUES ('200215126', '张成民', '男',18,'CS');

［例3］ 插入一条选课记录( ‘200215128’，'1 ')。

INSERT INTO SC(Sno,Cno)
# RDBMS将在新插入记录的Grade列上自动地赋空值
VALUES ('200215128','1');
# 等价
INSERT INTO SC
VALUES ('200215128','1',NULL);

如果是主码的话，是不能省略或用NULL赋值的

插入元组可以同时插入多条的，中间用逗号隔开

(2) 插入子查询结果

将子查询结果插入指定表中

[例4] 对每一个系，求学生的平均年龄，并把结果存入数据库。

  1. 建表
CREATE  TABLE  Dept_age(
    Sdept  CHAR(15),       /* 系名*/
    Avg_age SMALLINT		  /*学生平均年龄*/
);
  1. 插入数据
INSERT INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept;

同样的要求

SELECT子句目标列必须与INTO子句匹配，值的个数、类型都要一致。

插入子查询结果也可以同时插入多条的

RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则

  • 实体完整性
  • 参照完整性
  • 用户定义的完整性
  • NOT NULL约束
  • UNIQUE约束
  • 值域约束

2. 修改数据 UPDATE SET

语法格式

UPDATE  <表名>
SET  <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>];

功能：修改指定表中满足WHERE子句条件的元组。
说明：

  • SET子句：指定修改方式，修改的列，修改后取值。

  • WHERE：指定要修改的元组，缺省表示修改所有元组。

  • 在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则。

    比如说Student和SC表，SC表中的Sno外键引用了Student的Sno，不能修改了Student里面的Sno但不修改SC中的Sno

    可以先删除SC中的Sno，然后修改Student的Sno，然后插入

  1. 修改某一个元组的值

[例5] 将学生201215121的年龄改为22岁

UPDATE  Student
SET Sage=22
WHERE  Sno='201215121';
  1. 修改多个元组的值

[例6] 将所有学生的年龄增加1岁

UPDATE Student
SET Sage= Sage+1;
  1. 带子查询的修改语句

[例7] 将计算机科学系全体学生的成绩置零。

UPDATE SC
SET  Grade=0
WHERE  'CS'=( 
				SELECT  Sdept
				FROM  Student
				WHERE  Student.Sno = SC.Sno
			);

3. 删除数据 DELETE FROM

语句格式:

DELETE FROM<表名> 
[WHERE<条件>]；

功能：删除指定表中满足WHERE子句条件的元组。
说明：
WHERE子句：指定要删除的元组，缺省表示要删除表中的全部元组，表的定义仍在。

  1. 删除某一个元组的值
DELETE FROM Student
WHERE Sno= '200215128';
  1. 删除多个元组的值
DELETE
FROM SC;
  1. 带子查询的删除语句

[例10] 删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE  'CS'=(
   				SELECT Sdept
   				FROM Student
   				WHERE Student.Sno=SC.Sno
   			);

(六) 空值的处理

空值的存在是因为取值有不确定性，对关系运算带来特殊的问题，所以需要做特殊的处理。
SQL语言中允许某些元组的某些属性取空值，一般有以下几种情况：

  • 该属性有值，但当前不知道它的具体值。
  • 该属性不应该有值。
  • 由于某种原因不便于填写。

1. 空值的产生

INSERT INTO SC(Sno,Cno)
# RDBMS将在新插入记录的Grade列上自动地赋空值
VALUES ('200215128','1');
# 等价
INSERT INTO SC
VALUES ('200215128','1',NULL);

在更新数据的时候也可以产生控制

2. 空值的判断

判断一个属性的值是否为空值，用IS NULL或IS NOT NULL来表示

【例】从Student表中找出漏填了数据的学生信息

SELECT *
FROM Student
WHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL

3. 空值的约束条件

  1. 属性定义（或者域定义）中有NOT NULL约束条件的不能取空值；

  2. (主)码属性不能取空值。

  3. 加了UNIQUE限制的属性不能取空值（这里老师讲错了）

    加了UNIQUE限制的不能取相同值，如果取相同值，一定是NULL，也就是UNIQUE列中可以有多个NULL

  4. 外键可以为NULL，如果有值，一定是引用的列的值

4. 空值的运算

(1) 算数运算

空值与另一个值（包括另一个空值）的算术运算的结果为空值；

(2) 比较运算

空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN;

注意这个UNKNOWN，放在where后面既不算true，也不算false，在完成业务的时候要考虑值为null的情况

(3) 逻辑运算

逻辑运算主要是两个boolean值比较，这里主要讲解UNKNOW的逻辑运算

X Y X AND Y X OR Y NOT X
UNKNOW T UNKNOW T UNKNOW(U的反还是U)
F UNKNOW(U和F的AND是U，不确定U是不是正确的) UNKNOW(U和F的OR是U，因为不确定是不是正确的)
UNKNOW UNKNOW UNKNOW

总之UNKNOW的值按着理解来就行，比如T AND U，有一个正确必定是正确的

【例】找出选修1号课程的不及格的学生

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1'

以上查询的不及格的学生不包括缺考学生（如果缺考设置为NULL的话），因为NULL < 60 = UNKNOW，这样的话应该补充条件

SELECT Sno
FROM SC
WHERE Grade < 60 AND Cno = '1' OR Grade IS NULL;

但是事实上，会为特殊情况设置编码，比如Grade = -1表示缺考，Grade = -2表示作弊等等

(七) 视图

1. 概述

(1) 视图的特点

视图的特点

  • 虚表，是从一个或几个基本表（或视图）导出的表
  • 只存放视图的定义，不存放视图对应的数据
  • 基表中的数据发生变化，从视图中查询出的数据也随之改变

(2) 视图的作用

视图的作用：

  1. 视图能够简化用户的操作

  2. 视图使用户能以多种角度看待同一数据

    不同的应用可能有相同的数据

  3. 视图对重构数据库提供了一定程度的逻辑独立性

  4. 视图能够对机密数据提供安全保护

    比如身份证号存在基本表中，视图可以不显示这个

  5. 适当的利用视图可以更清晰的表达查询

(3) 基于视图的操作

基于视图的操作

  • 查询
  • 删除
  • 受限更新
  • 定义基于该视图的新视图

视图的UPDATE、DELETE、INSERT INTO(有受限)，与基本表同步。

2. 定义视图

(1) 建立视图

语句格式
CREATE  VIEW <视图名>  [(<列名>  [，<列名>]…)]
AS  <子查询>
[WITH  CHECK  OPTION]；

  • 组成视图的属性列名：全部省略或全部指定

    如果全部省略的话，就会用子查询的属性列名来代替，但是不能选择性的给定属性名

  • 子查询不允许含有ORDER BY子句和DISTINCT短语

    后面涉及更改视图，如果视图用这两个定义，那么更改就会出错

  • RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。

  • 在对视图查询时，按视图的定义从基本表中将数据查出。

[例1] 建立信息系IS学生的视图。

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM    Student
WHERE  Sdept= 'IS';

[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系IS的学生 。

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM  Student
WHERE  Sdept= 'IS'
WITH CHECK OPTION;

WITH CHECK OPTION的作用：受限更新，也就是在更新视图的时候会受到限制（对视图执行修改语句实际上也就是对基本表执行修改语句，修改视图的行反映到基本表上，所以应该将where后面的语句给填上）

  • 修改操作：自动加上Sdept= 'IS’的条件
  • 删除操作：自动加上Sdept= 'IS’的条件
  • 插入操作：自动检查Sdept属性值是否为’IS’
    • 如果不是，则拒绝该插入操作
    • 如果没有提供Sdept属性值，则自动定义Sdept为’IS’
基于多个基表的视图

[例3] 建立信息系选修了1号课程的学生视图。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS 
SELECT Student.Sno,Sname,Grade
FROM  Student,SC
WHERE  Sdept= 'IS' 
AND Student.Sno=SC.Sno 
AND SC.Cno= '1';

这里设置了视图的列名，就会一一对应上

基于视图的视图

[例4] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM  IS_S1
WHERE  Grade>=90;
带表达式的视图

[例5] 定义一个反映学生出生年份的视图。

CREATE  VIEW BT_S(Sno,Sname,Sbirth)
AS 
SELECT Sno,Sname,2000-Sage
FROM  Student;

SELECT语句里面可以有表达式，建立视图的时候也可以有。

如果这样，在更改视图的时候是不是也可以响应的更改了？

分组视图

[例6] 将学生的学号及他的平均成绩定义为一个视图

CREATE  VIEW S_G(Sno,Gavg)
AS  
SELECT Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

同样的，分组视图也不会实际上建立一个表，但是注意，这个表是动态生成的，如果修改基本表的值，AVG也会响应的修改

每次打开这个视图都会执行这个SELECT语句创建视图

不指定属性列
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
FROM  Student
WHERE Ssex='女';

这里SELECT语句是没有属性列的，就会自动按顺序匹配

缺点：

  • 修改基表Student的结构后，Student表与F_Student视图的映象关系被破坏，导致该视图不能正确工作。

    但是如果指定了属性列就会好多

(2) 删除视图

语句的格式:

DROP VIEW <视图名> [CASCADE];
  • 该语句从数据字典中删除指定的视图定义
  • 如果该视图上还导出了其他视图，使用CASCADE级联删除语句，把该视图和由它导出的所有视图一起删除(但是不会删除基本表)
  • 删除基表时，由该基表导出的所有视图定义都必须显式地使用DROP VIEW语句删除

［例8］ 删除视图IS_S1

DROP VIEW IS_S1;

假设视图IS_S1还导出了IS_S2，该方法会拒绝执行

正确的删除

DROP VIEW IS_S1 [CASCADE];

tips:如果我们在删除视图A的时候不确定是否从视图A导出了视图B，那么就可以直接加上[CASCADE]

也就是在删除视图的时候都加上[CASCADE]

3. 查询视图

视图定义后，用户就可以像基本表一样对视图进行查询了。

由于视图的数据来源于基本表，有的时候会有错误的情况

(1) 视图消解法

  1. RDBMS实现视图查询的方法----视图消解法

    • 进行有效性检查。

      比如视图S1来源于Student，在查询S1的时候是会检查Student是否存在的，如果被删除了，查询失败

    • 转换(修正)成等价的对基本表的查询。

    • 执行修正后的查询。

关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解(view resolution)。

[例9] 在信息系学生的视图中找出年龄小于等于20岁的学生。

SELECT   Sno,Sage
FROM      IS_Student
WHERE   Sage<=20;

视图消解转换后的查询语句为：

SELECT  Sno，Sage       
FROM  Student
WHERE  Sdept= 'IS'  AND  Sage<=20；

[例10] 查询选修了1号课程的信息系学生

SELECT  IS_Student.Sno,Sname
FROM     IS_Student,SC
WHERE  IS_Student.Sno =SC.Sno AND SC.Cno= '1';

(2) 视图消解法的局限性

  • 有些情况下，视图消解法不能生成正确查询。

[例11]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *
FROM   S_G
WHERE  Gavg>=90;

S_G视图的子查询定义：

CREATE VIEW S_G (Sno,Gavg)
AS 
SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

S_G是Sno与平均成绩的表

视图消解法修改后的语句为

SELECT Sno,AVG(Grade)
FROM     SC
WHERE  AVG(Grade)>=90
GROUP BY Sno;

但是前面所学，聚集函数不能放在where后面的，只能放在having后面

SELECT  Sno,AVG(Grade)
FROM  SC
GROUP BY Sno
HAVING AVG(Grade)>=90;

4. 更新视图

说明：

  • 更新视图是指通过视图来插入、删除和数据，因为视图不适宜存储数据，因此对视图的更新操作将通过视图消解，转化为对实际表的更新操作。

  • 注意：

    为防止在更新视图时出错，定义视图时要加上WITH CHECK OPTION子句。

更新视图的时候，是会依赖建立视图时的条件

(1) 更新数据

[例12] 将信息系学生视图IS_Student中学号201215125的学生姓名改为“刘辰”。

UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= '201215125';

转换后的语句：

UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= '201215125' 
AND Sdept= 'IS';

(2) 插入数据

[例13] 向信息系学生视图IS_Student中插入一个新的学生记录：200215111，赵新，20岁

  1. 视图IS_Student的定义
CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM    Student
WHERE  Sdept= 'IS';
  1. 插入数据
INSERT INTO IS_Student
VALUES('200215111','赵新',20);
  1. 转换为对基本表的更新：
INSERT INTO Student(Sno,Sname,Sage,Sdept)
VALUES('200215129','赵新2',20,'IS');

(3) 删除数据

[例14]删除信息系学生视图IS_Student中学号为200215129的记录

DELETE FROM IS_Student
WHERE Sno= '200215129';

转换为对基本表的更新：

DELETE
FROM Student
WHERE Sno= '200215129' AND Sdept= 'IS';

(4) 更新视图的限制

  • 更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新

例：视图S_G为不可更新视图。

CREATE VIEW S_G(Sno, Gavg)
AS
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno;

将学生的学号及他的平均成绩定义为一个视图

UPDATE  S_G
SET Gavg = 90
WHERE  Sno= '200215121';

这个对视图的更新无法转换成对基本表SC的更新，因为平均成绩是由计算得出的，系统无法修改各科成绩，使平均成绩到达90分

  • 允许对行列子集视图进行更新
  • 对其他类型视图的更新不同系统有不同限制
posted @ 2023-10-07 17:50  Crispy·Candy  阅读(89)  评论(0)  编辑  收藏  举报