数据库系统概论 - 上机
数据库系统概论 - 上机
本文目的：
记录数据库实验过程，便于分享和回顾。
行文结构尽量按照实验的时间顺序组织。
文中举例皆为实际实验中所用语句；命名保持连贯性。
文中所有的 SQL 语句按照保留字大写、非保留字小写的规则录入。
参考资料：
《数据库系统概论（第 5 版）》，王珊，萨师煊，高等教育出版社；
实验环境：
OS: ubuntu 16.04 server;
DBMS: MariaDB
示例来源：
《数据库系统概论（第 5 版）》，3.2 学生-课程数据库。
1 环境
1.1 安装
1.1.1 在 ubuntu 系统下安装 MariaDB
apt-get install mariadb-server
apt-get install mariadb-client
1.1.2 登陆
mysql -u root -p（可能需要使用系统root账户）
2 安全性控制
2.1 创建用户
CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
-
创建一个通过密码进行身份鉴别的用户
CREATE USER 'user_test'@'localhost' IDENTIFIED BY '123456';
2.2 删除用户
DROP USER 'username'@'hostname';
-
删除一个用户
DROP USER 'user_test'@'localhost';
2.3 授予权限
GRANT privileges ON objs TO users [WITH GRANT OPTION];
其中 'WITH GRANT OPTION' 表示被授权用户可以传递权限。
-
授予某用户某个数据库中的所有表的所有操作权限
GRANT ALL ON mysql.* TO 'user_test'@'localhost'; -
授予某用户所有数据库的所有操作权限（DBA）
GRANT ALL ON *.* TO 'user_dba'@'localhost';
2.4 撤销权限
REVOKE privileges ON objs FROM users [CASCADE/RESTRICT];
-
撤销某用户对某个数据库中所有表的所有操作权限
REVOKE ALL ON mysql.* FROM 'user_test'@'localhost';
2.5 角色命名
CREATE ROLE rolename;
角色是权限的集合。
-
创建一个用于管理数据库所有表的角色
CREATE ROLE 'role_mysql_admin';
2.6 角色定义
GRANT privileges ON objs TO roles;
-
对管理数据库所有表的角色进行权限定义
GRANT ALL ON mysql.* TO 'role_mysql_admin';
2.7 角色赋予
角色既可以赋予用户，也可以赋予另一个角色。
GRANT role TO roles/users [WITH ADMIN OPTION];
模式存取权限
对象 操作类型 SCHEMA CREATE SCHEMATABLE CREATE TABLE,ALTER TABLEVIEW CREATE VIEWINDEX CREATE INDEX
数据存取权限
对象 操作类型 TABLE, VIEW SELECT,INSERT,UPDATE,DELETE,REFERENCES,ALL PRIVILEGESATTR COL SELECT,INSERT,UPDATE,REFERENCES,ALL PRIVILEGES
3 数据定义
3.1 备份与恢复
3.1.1 数据库的导入
导入数据库的用户必须具有创建数据库的权限。
mysql < import.sql
-
导入示例数据库
mysql -u user_dba -p < employees.sql
3.1.2 数据库的导出
mysqldump -u username -p -h hostname databasename > exportfile
-
导出示例数据库为数据库备份文件
mysqldump -u user_dba -p -h localhost employees > export.sql
3.2 数据库（database）
3.2.1 列出数据库
SHOW DATABASES;
3.2.2 创建数据库
CREATE DATABASE dbname;
-
创建测试数据库
CREATE DATABASE test_db;
3.2.3 选择数据库
USE dbname;
-
选择测试数据库
USE mysql;
3.3 模式（schema）
模式位于数据库和表、视图、索引之间，一个数据库可以有多个模式，一个模式可以有多个表、视图、索引。模式相当于数据库内部的一个命名空间。
3.3.1 模式定义
CREATE SCHEMA schema AUTHORIZATION user;
如果不指定模式名，则模式名隐含为用户名。
注：在 MYSQL 中，schema 和 database 是作不区分的。因此，对于 schema 的相关实验暂不进行。
-
创建一个测试模式
CREATE SCHEMA schema_test;
3.4 基本表（table）
3.4.1 定义基本表
CREATE TABLE tablename(
column datatype integrity, ...),
integrity, ... ;
-
创建学生表
创建学生表，属性有学号（sno, 列级完整性约束：主码）、姓名（sname）、性别（ssex）、年龄（sage）、系（sdept）。CREATE TABLE student (sno CHAR(9) PRIMARY KEY, sname CHAR(20), ssex CHAR(2), sage SMALLINT, sdept CHAR(20)); -
创建课程表
创建课程表，属性有课程号（cno）、课程名（cname）、先修课编号（cpno）、课程学分（ccredit）；表级完整性约束：先修课编号为引用课程表中课程号的外码。CREATE TABLE course (cno CHAR(4) PRIMARY KEY, cname CHAR(40) NOT NULL, cpno CHAR(4), ccredit SMALLINT, FOREIGN KEY (cpno) REFERENCES course(cno)); -
创建选课表
创建选课表，属性有学号（sno）、课程号（cno）、成绩（grade）；表级完整性约束：主码为（sno,cno），学号为引用学生表中学号的外码，课程号为引用课程表中课程号的外码。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)); -
思考：
- 如果引用的字段和被引用表中对应的字段的定义不一致会如何？
对于 MariaDB ，将字段修改为长度不一致不报错，但是如果字段不相容（如字符串和整形不相容），则报错。
- 如果引用的字段和被引用表中对应的字段的定义不一致会如何？
数据类型
数据类型 含义 CHAR(n) 长度为 n 的定长字符串 VARCHAR(n) 最大长度为 n 的变长字符串 CLOB 字符串大对象 BLOB 二进制大对象 INT 长整数（4B） SMALLINT 短整数（2B） BIGINT 大整数（8B） NUMERIC(p,d) 定点数，p 位整数和 d 位小数 DECIMAL(p,d) 同 NUMERIC(p,d) RAEL 机器单精度浮点数 DOUBLE PRECISION 机器双精度浮点数 FLOAT(n) 精度至少 n 位的浮点数 BOOLEAN 逻辑布尔量 DATE 日期，格式为 YYYY-MM-DDTIME 时间，格式为 HH-MM-SSTIMESTAMP 时间戳类型 INTERVAL 时间间隔类型
完整性
完整性 含义 举例 实体完整性 实体可区分（主码中的属性非空） PRIMARY KEY参照完整性 外码不能为不存在的某个值 FOREIGN KEY (key) REFERENCES tablename(colname)用户定义的完整性 根据用户需求定义 UNIQUE,NOT NULL
3.4.2 列出基本表
SHOW TABLES;
3.4.3 列出基本表的结构
DESC tablename;
3.4.4 修改基本表
ALTER TABLE tablename
[ADD [COLUMN] colname datatype [integrity]]
[ADD integrity]
[DROP [COLUMN] colname [CASCADE|RESTRICT]]
[DROP CONSTRAINT restrictname [RESTRICT|CASCADE]]
[ALTER COLUMN colname datatype]
-
向学生表中添加“入学时间（s_entrance）”列，类型为日期型
ALTER TABLE student ADD s_entrance DATE; ALTER TABLE student ADD COLUMN s_entrance DATE; -
将学生表中的年龄的数据类型更改为短整形（假设原来的是字符型）
ALTER TABLE student ALTER COLUMN sage SMALLINT;注：在本实验环境中，语法有区别，应为
ALTER TABLE student MODIFY COLUMN sage SMALLINT; -
增加课程名称必须取唯一值的条件
ALTER TABLE course ADD UNIQUE(cname);
3.4.5 删除基本表
有两种删除方式，默认为约束型。
串联型（CASCADE）：删除表，以及所有依赖该表的对象（引用、视图、触发器、存储过程、函数等）。
约束型（RESTRICT）：若存在依赖表的其它对象，则不执行删除操作。
DROP TABLE tablename [CASCADE|RESTRICT];
-
创建一个学生表的女学生的视图，然后使用两种方式删除学生表
建立视图：CREATE VIEW view_student AS SELECT sno,sname,sage FROM student WHERE ssex='女';约束删除学生表：
DROP TABLE student RESTRICT;注：MYSQL 视图不会引起约束，但是删除后视图无法继续使用。外码引用能够引起约束。MYSQL 使用串联型删除也不能删除具有外码的表。
3.5 索引（index）
索引是加快查询速度的有效手段；
索引建立后即由系统进行维护和使用（不需要用户管理）；
索引是有维护成本的（创建时以及被索引对象有修改时），不必要的索引应删除；
同样功能的索引是能不同名的存在的；
3.5.1 建立索引
CREATE [UNIQUE] [CLUSTER]
INDEX indexname
ON tablename(colname [order], ...);
-
为学生表创建按学号的升序索引。
CREATE UNIQUE INDEX indexonsno ON student(sno);
顺序（order）
key meaning ASC ascending order DESC descending order
索引类型（index type）
index type meaning UNIQUE 唯一对应索引 CLUSTER 聚簇索引
3.5.2 列出索引
SHOW INDEX FORM tablename;
-
列出学生表中的索引
SHOW INDEX FROM student;
3.5.3 修改索引
可以对索引进行重命名
ALTER INDEX oldindexname RENAME TO newindexname;
- 将学生表的学号索引修改为 indexa
说明：MYSQL 中不支持重命名索引。
3.5.3 删除索引
DROP INDEX indexname;
-
删除学生表中创建的对学号的不必要索引
DROP INDEX indexonsno;
4 数据查询
数据查询是数据库的核心操作。
SELECT [ALL|DISTINCT] 目标列表达式, ...
FROM table/view, ... | (SELECT ...) [AS] alias
[WHERE 条件表达式]
[GROUP BY colname [HAVING 条件表达式]]
[ORDER BY colname [ASC|DESC]];
4.1 单表查询
4.1.1 选择表中的若干列
查询指定列。
-
查询全体学生的学号和姓名
SELECT sno,sname FROM student;
查询全部列。
-
查询学生表的所有信息
SELECT * FROM student;
查询经过计算的值。
-
查询全体学生的姓名及其出生年份（假设制表时间为 2014 年）
SELECT sname, 2014-sage FROM student;
目标列表达式可以作别名。
-
查询全体学生的姓名及其出生年份（假设制表时间为 2014 年）
SELECT sname name, 2014-sage birthday FROM student;+--------+----------+ | name | birthday | +--------+----------+ | 李勇 | 1994 | | 刘晨 | 1995 | | 王敏 | 1996 | | 张立 | 1995 | +--------+----------+
4.1.2 选择表中的若干元组
消除取值重复的行
-
查询有选修课程的学号
SELECT DISTINCT sno FROM sc;
查询满足条件的元组
WHERE 子句查询条件
查询条件 谓词 比较 =, >, <, >=, <=, !=, <>, !>, !<, （NOT 修饰）确定范围 BETWEEN AND, NOT BETWEEN AND确定集合 IN, NOT IN字符匹配 LIKE, NOT LIKE空值 IS NULL, IS NOT NULL多重条件 AND, OR, NOT
注：
BETWEEN a AND b等价于>=a AND <=b。IN紧跟一个元组作为集合。- 字符匹配时，支持使用
'%'表示任意长度的字符以及'_'表示任意单个字符。用'\%'和'\_'进行转义。
-
查询 CS 系的全体学生姓名（等于）
SELECT sname name FROM student WHERE sdept='CS'; -
查询所有年龄在 20 岁以下的学生的学号（小于）
SELECT sno FROM student WHERE sage<20; -
查询年龄在 20-23 岁之间的学生的姓名（范围）
SELECT sname name FROM student WHERE sage BETWEEN 20 AND 23; -
查询所在系为 IS 或 MA 的学生的姓名（集合）
SELECT sname name FROM student WHERE sdept IN ('IS','MA'); -
查询姓“王”的学生姓名（字符匹配）
SELECT sname name FROM student WHERE sname LIKE '王%'; -
查询没有先修课程的课程名（空值）
SELECT cname FROM course WHERE cpno IS NULL; -
查询 CS 系的所有男生的姓名（组合条件）
SELECT sname name FROM student WHERE sdept='CS' AND ssex='男';
4.1.3 ORDER BY 子句
-
查询选课表的所有信息，并按成绩降序排列
SELECT * FROM sc ORDER BY grade DESC;
4.1.4 聚集函数
聚集函数实现了一些统计功能。
聚集函数
聚集函数 功能 COUNT(*)统计元组个数 `COUNT([DISTINCT ALL] colname)` `SUM([DISTINCT ALL] colname)` `AVG([DISTINCT ALL] colname)` `MAX([DISTINCT ALL] colname)` `MIN([DISTINCT ALL] colname)`
-
查询学生总人数
`SELECT COUNT(*);` -
查询选修了课程的学生人数
`SELECT COUNT(DISTINCT sno) FROM sc;` -
查询学生的平均年龄
`SELECT AVG(DISTINCT sage) FROM student;` -
查询所有课程的最高分数
`SELECT MAX(grade) FROM sc;`
4.1.5 GROUP BY 子句
GROUP BY 子句将查询结果按某一列或多列的值分组，值相等的为一组。
对查询结果分组的目的是细化聚集函数的作用对象。分组后聚集函数将作用于每一个组，即每一组都有一个函数值。
-
求各个课程号及相应的选课人数。
SELECT cno,COUNT(sno) FROM sc GROUP BY cno;+------+------------+ | cno | COUNT(sno) | +------+------------+ | 1 | 1 | | 2 | 2 | | 3 | 2 | +------+------------+
可以使用 HAVING 对分组应用筛选条件。
注意：WHERE 子句作用于基本表或视图，HAVING 短语作用于组。
-
查询选修了 3 门以上课程的学生学号。
SELECT sno FROM sc GROUP BY sno HAVING COUNT(*) > 3;
4.2 连接查询
- 若一个查询同时涉及两个及以上的表，则称为连接查询。
- 连接查询是数据库中最主要的查询。
4.2.1 等值与非等值连接
使用等号连接为等值连接，否则为非等值连接。
-
查询每个学生及其选修课程的情况
`SELECT student.*,sc.* FROM student,sc WHERE student.sno=sc.sno;`+-----------+--------+------+------+-------+-----------+------+-------+ | sno | sname | ssex | sage | sdept | sno | cno | grade | +-----------+--------+------+------+-------+-----------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 | +-----------+--------+------+------+-------+-----------+------+-------+
属性名中的表名前缀在不引起混淆的情况下可以不加。
-
查询学生的姓名和成绩
SELECT sname name, grade FROM student, sc WHERE student.sno=sc.sno;
如果去掉重复列，则等值连接特殊化为自然连接。此时可以将所有需要的列名显式指明。
4.2.2 自身连接
连接可以对同一个表进行，称为自身连接。对同一个表进行连接，需要对表取别名进行区分。
自身连接时注意写法。
-
查询出课程名及其对应的先修课的课程名
SELECT FIR.cname course, SEC.cname precourse FROM course FIR, course SEC WHERE FIR.cpno=SEC.cno;
4.2.3 外连接
外连接是指匹配过程中，如果出现空匹配，则保留为空值（而不是不匹配）。即保留悬浮元组。
外连接分为左外连接和右外连接，左外连接保留左边的悬浮元组，右外连接保留右边的悬浮元组。
-
查询学生表和选课表的信息
SELECT student.*, sc.* FROM student LEFT OUTER JOIN sc ON (student.sno=sc.sno);+-----------+--------+------+------+-------+-----------+------+-------+ | sno | sname | ssex | sage | sdept | sno | cno | grade | +-----------+--------+------+------+-------+-----------+------+-------+ | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | NULL | +-----------+--------+------+------+-------+-----------+------+-------+也可以用
USING关键字来给出连接条件SELECT student.*, sc.* FROM student LEFT OUTER JOIN sc USING (sno);
4.2.4 多表连接
多表连接实质是将两个表连接的结果与第三个表连接，迭代进行。
-
查询学生的学号、姓名、学修课程名及成绩
SELECT student.sno no, student.sname name, course.cname course, sc.grade grade FROM student, course, sc WHERE student.sno=sc.sno AND sc.cno=course.cno;+-----------+--------+--------------+-------+ | no | name | course | grade | +-----------+--------+--------------+-------+ | 201215121 | 李勇 | 数据库 | 92 | | 201215121 | 李勇 | 数学 | 85 | | 201215121 | 李勇 | 信息系统 | 88 | | 201215122 | 刘晨 | 数学 | 90 | | 201215122 | 刘晨 | 信息系统 | 80 | +-----------+--------+--------------+-------+
4.3 嵌套查询
在 SQL 语言中，一个 SELECT - FROM - WHERE 语句称为一个查询块，将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 子句短语的条件中的查询称为嵌套查询。
多个简单查询通过嵌套可以构成一个复杂的查询，从而增强 SQL 的查询能力。以层层嵌套的方式来构造程序正是 SQL 中“结构化”的含义所在。
能够用连接查询实现的应该尽可能用连接实现，嵌套查询相比不易优化性能。
4.3.1 带有 IN 谓词的子查询
通过 IN 进行子查询和父查询的嵌套，子查询和父查询不具有相关性，因此称为不相关子查询。
-
查询与“刘晨”在同一个系的学生姓名
先找出刘晨所在的系的结果集，再找出系在结果集中的学生集。SELECT sname name FROM student WHERE sdept IN (SELECT sdept FROM student WHERE sname='刘晨'); -
查询选修了“信息系统”的学生学号和姓名
先找出信息系统对应的课程号集，然后根据课程号找出选修的学生学号集，再根据学号找出对应的学生信息。SELECT sno, sname FROM student WHERE sno IN (SELECT sno FROM sc WHERE cno IN (SELECT cno FROM course WHERE cname='信息系统'));
4.3.2 带有比较运算符的子查询
如果能够确定子查询的结果集只有一个元素，则可以使用比较运算符。
嵌套查询的内外进行比较时，可以将内外查询联系起来，此时称为相关子查询，此时对应的查询语句称为相关嵌套查询。
-
找出每个学生超过其选修课程平均成绩的课程号
SELECT sno,cno FROM sc x WHERE grade >= (SELECT AVG(grade) FROM sc y WHERE x.sno=y.sno);内查询和外查询都是对同一张表进行查询，通过别名进行联系。
查询流程的一个实现：- 外查询给出一个 sno ；
- 内查询根据给出的 sno 求出对应的平均成绩；
- 外查询还原为普通的单层查询。
4.3.3 带有 ANY（SOME）或 ALL 谓词的子查询
ANY 和 ALL 作谓词嵌套子查询，可以实现诸如“只要任意一个”和“对所有都”语意。
-
查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
思路：先找出计算机科学系中任意一个学生，然后作ALL谓词嵌套，然后在非计算机科学系中按条件查找。SELECT sname, sage FROM student WHERE sage < ALL (SELECT sage FROM student WHERE sdept = 'CS') AND sdept != 'CS';这个问题当然还可以用聚集函数实现，找出计算机科学系中的学生年龄的最大值即可。
4.3.4 带有 EXISTS 谓词的子查询
EXISTS 谓词作嵌套子查询，可以实现如“存在”的语意。使用 EXISTS 嵌套子查询时，子查询不返回任何数据，只产生逻辑真或逻辑假。当内部子查询为空时，返回假，否则返回真。
NOT EXISTS 是 EXISTS 的直接否定，当内部子查询为空时，返回真，否则返回假。
使用 EXISTS 谓词时，内查询产生逻辑真或假，因此不需要给出查询的列形式，直接用 * 即可。
EXISTS 内查询不需要关注查询的结果，因此效率往往较高。
-
查询所有选修了 1 号课程的学生姓名
思路：从学生表中找出学生，然后判断该学生是否对应在选课表中选修了 1 号课程。SELECT sname FROM student WHERE EXISTS (SELECT * FROM sc WHERE sno=student.sno AND cno='1');当外层给出的学生对应选修了 1 号课程时，则
EXISTS子句返回真。这个例子也是内外关联的查询（相关子查询）。
查询流程的一个实现：- 外查询给出一个元组；
- 将元组的属性给内查询，并执行，如果结果非空，则
EXISTS为真，将元组放入结果集。
使用 EXISTS 实现全称量词（for all）语义。
任务例子：找出所有满足谓词 \(F(x)\) 的元组。
一阶逻辑等值演算：
查询为 \(\forall xF(x)\)，由 \(\lnot \forall xF(x)\Leftrightarrow \exist x\lnot F(x)\) （量词否定等值式），即 \(\forall xF(x)\Leftrightarrow \lnot \exist x\lnot F(x)\) 。
字面理解：所有 \(x\) 都满足性质 \(F(x)\) ，等价于，不存在这种情况，存在 \(x\) 不满足性质 \(F(x)\) 。
-
查询选修了所有课程的学生姓名
思路：转换为两次否定的查询。给出学生，然后找出选修的课程为空的的课程的集合，然后嵌套判断课程的集合为空的学生的集合（对这个学生，不存在这样的课程，满足这个学生没有选修），即为所求。SELECT sname FROM student WHERE NOT EXISTS (SELECT * FROM course WHERE NOT EXISTS (SELECT * FROM sc WHERE student.sno = sc.sno AND course.cno = sc.cno));
使用 EXISTS 实现蕴涵语意。
任务例子：找出所有若 \(F(x)\) ，则 \(G(x)\) 的元组。
一阶逻辑等值演算：
查询为：\(\forall x(F(x)\to G(x))\) ，将蕴涵语义和全称语义转换，即
\(\forall x(F(x)\to G(x))\) \(\Leftrightarrow\)
\(\lnot \exist x\lnot (F(x)\to G(x))\) \(\Leftrightarrow\)
\(\lnot \exist x\lnot (\lnot F(x)\lor G(x))\) \(\Leftrightarrow\)
\(\lnot \exist x(F(x)\land \lnot G(x))\)
字面理解：不存在 \(x\) ，满足 \(F(x)\) ，同时不满足 \(G(x)\) 。
-
查询至少选修了学号 201215122 对应学生选修的全部课程的学生学号
思路：找出 201215122 选修的全部课程，待检查的学生至少需要选修这些课程。对这个学生，不存在这样的课程，满足学生没有选修这个课程，这个课程也被 201215122 选修。与查询选修了所有课程的问题类似。将查询转换为一阶逻辑陈述：
查询学号为 \(x\) 的学生，对于所有课程 \(y\) ，只要学号为 \(201215122\) 的学生选修了课程 \(y\) ，则 \(x\) 也选修了课程 \(y\) 。
符号化：
\(F(y)\)：学号为 201215122 的学生选修了课程 \(y\) 。
\(G(x,y)\)：学生 \(x\) 选修了课程 \(y\) 。
查询为\(\forall x\forall y(F(y)\to G(x,y))\) \(\Leftrightarrow\)
\(\lnot \exist x\exist y(F(y)\land \lnot G(x,y))\)
字面含义：不存在学生 \(x\) 和 课程 \(y\) ，满足学号为 201215122 的学生选修了课程 \(y\) ，学生 \(x\) 没有选修课程 \(y\) 。SELECT DISTINCT sno FROM sc scx WHERE NOT EXISTS( SELECT * FROM sc scy WHERE scy.sno='201215122' AND NOT EXISTS ( SELECT * FROM sc scz WHERE scz.sno=scx.sno AND scz.cno=scy.cno ) );+-----------+ | sno | +-----------+ | 201215121 | | 201215122 | +-----------+
4.4 集合查询
SELECT 语句的查询结果是元组的集合，所以多个 SELECT 语句的结果可进行集合操作。集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 EXCEPT 。
-
查询计算机科学系的学生及年龄不大于 19 岁的学生。
直接对查询结果取并集，然后去重即可（注：
UNION结果自动去重，不去重可用UNION ALL）。SELECT sno FROM student WHERE sdept='cs' UNION SELECT sno FROM student WHERE sage<=19
4.5 基于派生表的查询
子查询如果出现在 FROM 子句中，则生成的临时派生表（derived table）成为主查询的查询对象。
-
找出每个学生超过其选修课程平均成绩的课程号。
SELECT sno,cno FROM sc, (SELECT sno avg_sno,AVG(grade) avg_grade FROM sc) AS avg_sc WHERE sc.sno=avg_sc.avg_sno AND sc.grade>=avg_sc.avg_grade;注：MYSQL 不支持在
AS后进行别名（如AS avg_sc(avg_sno,avg_grade)），但是可以在派生表中直接命名别名。
如果没有对列名使用聚集函数，则可以不用对列进行重命名。
4.6 SELECT 语句的一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [别名] ...
FROM <表名或视图名> [别名] ... | <SELECT 语句> [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY [列名2] [ASC|DESC]];
目标列表达式
| I | 可选格式 |
|---|---|
| 1 | * |
| 2 | <表名>.* |
| 3 | `COUNT([DISTINCT |
| 4 | [<表名>.]<属性列名表达式>... |
属性列名表达式可以是由属性列、作用于属性列的聚集函数和常量的组成的任意算术运算（+ - * /）组成的运算公式。
聚集函数
| I | 格式 |
|---|---|
| 1 | `[COUNT |
WHERE 子句的条件表达式
| I | 可选格式 |
|---|---|
| 1 | <属性列名> \(\theta\) <属性列名> |
| 2 | <属性列名> \(\theta\) <常量> |
| 3 | <属性列名> \(\theta\) `[ANY |
| 4 | `<属性列名> [NOT] BETWEEN [<属性列名> |
| 5 | `<属性列名> [NOT] IN [(<值1>...) |
| 6 | <属性列名> [NOT] LIKE <匹配串> |
| 7 | <属性列名> IS [NOT] NULL |
| 8 | [NOT] EXISTS (SELECT 语句) |
| 9 | `<条件表达式> [AND |
5 数据更新
数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。
5.1 插入数据
5.1.1 插入元组
INSERT
INTO <表名> ( <属性列>,... )
VALUES ( <常量>,... )
-
将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18 岁）插入到 Student 表中。
INSERT INTO student ( sno,sname,ssex,sdept,sage ) VALUES ('201215128','陈冬','男','IS',18);
插入元组的几个规则：
- 指明属性列时，未给定的列自动赋空值（除非列上有完整性条件非 NULL 限制）。
- 可以不指明属性列，此时必须按顺序给出所有列的值，包括空值。
5.1.2 插入子查询结果
INSERT
INTO <表名> [ ( <属性列>,... ) ]
子查询;
-
对每一个系，求学生的平均年龄，并将结果存入数据库。
CREATE TABLE dept_age ( sdept CHAR(15), avg_age SMALLINT);INSERT INTO dept_age ( sdept, avg_age ) SELECT sdept,AVG(sage) FROM student GROUP BY sdept;
5.2 修改数据
一般格式
UPDATE <表名>
SET <列名>=<表达式>, ...
[WHERE <条件>];
5.2.1 修改某一个元组的值
-
将学生 201215121 的年龄改为 22 岁。
UPDATE student SET sage=22 WHERE sno='201215121';
5.2.2 修改多个元组的值
-
将所有学生的年龄增加 1 岁。
UPDATE student SET sage=sage+1;
5.2.3 带子查询的修改语句
子查询可以用于 WHERE 子句。
-
将计算机科学系全体学生的成绩置零。
UPDATE sc SET grade=0 WHERE sno IN ( SELECT sno FROM student WHERE sdept='CS' );
5.3 删除数据
一般格式：
DELETE
FROM <表名>
[ WHERE <条件> ];
DELETE 语句只删除表中的数据。
如果省略 WHERE 子句，则删除表中所有数据。
5.3.1 删除某一个元组的值
-
删除学号为 201215128 的学生记录。
DELETE FROM student WHERE sno='201215128';
5.3.2 删除多个元组的值
-
删除所有学生的选课记录。
DELETE FROM sc;
5.3.3 带子查询的删除语句
-
删除计算机科学系所有学生的选课记录。
DELETE FROM sc WHERE sno IN ( SELECT sno FROM student WHERE sdept='CS' );
6 空值的处理
所谓空值就是“不知道”或“不存在”或“无意义”的值。
空值是一个很特殊的值，含有不确定性，对关系运算带来特殊的问题，需要做特殊的处理。
6.1 空值的产生
可以通过插入元组以及修改元组产生元组。
外连接以及空值的关系运算也能产生运算。
-
将 student 表中学生号为 201215125 的学生所属的系改为空值。
UPDATE student SET sdept=NULL WHERE sno='201215125';
6.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;
6.3 空值的约束
属性定义（或者域定义）中有 NOT NULL 约束条件的不能取空值，码属性不能取空值。
6.4 空值的算术运算、比较运算和逻辑运算
空值与另一个值（包括另一个空值）的算术运算的结果为空值，空值与另一个值（包括另一个空值）的比较运算的结果为 UNKNOWN 。有了 UNKNOWN 后，传统的逻辑运算中二值逻辑（TRUE，FALSE）就扩展成了三值逻辑（TRUE，FALSE，UNKNOWN）。
只有使 WHERE 和 HAVING 子句中的选择条件为 TRUE 的元组才能被选出作为输出结果（也即 UNKNOWN 在任何时候都不会被输出）。
7 视图
视图是从一个或几个基本表（或视图）导出的表。
数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。一旦基本表中的数据发生变换，从视图中查询出的数据也就随之改变了。视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。
视图一经定义，就可以和基本表一样被查询、被删除。可以在一个视图之上再定义新的视图，但对视图的更新操作则有一定的限制。
7.1 定义视图
7.1.1 建立视图
一般格式
CREATE VIEW <视图名> [ ( <列名>, ... ) ]
AS <子查询>
[WITH CHECK OPTION];
WITH CHECK OPTION 表示对视图进行 UPDATE、INSERT 和 DELETE 操作时要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
组成视图的属性列名或者全部省略或者全部指定。如果省略，则隐含该视图由子查询中 SELECT 子句目标列中的诸字段组成。
-
建立信息系学生的视图。
CREATE VIEW is_student AS SELECT sno,sname,sage FROM student WHERE sdept='IS'; -
建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。
CREATE VIEW is_student AS SELECT sno,sname,sage FROM student WHERE sdept='IS' WITH CHECK OPTION;加上了 WITH CHECK OPTION 子句，以后对视图进行插入、修改和删除操作时，关系数据库管理系统会自动加上
sdept='IS'的条件。
若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。
-
建立信息系选修了 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';
可以在视图之上建立视图。
-
建立信息系选修了 1 号课程且成绩在 90 分以上的学生的视图。
CREATE VIEW is_s2 AS SELECT sno,sname,grade FROM is_s1 WHERE grade>=90;
数据库中一般不存储根据计算派生出来的数据，因此可以通过视图设置派生列，也称为虚拟列。带虚拟列的视图也称为带表达式的视图。
-
定义一个反映学生出生年份的视图。
CREATE VIEW bt_s(sno,sname,sbirth) AS SELECT sno,sname,2020-sage FROM student;
7.1.2 删除视图
DROP VIEW <视图名> [CASCADE];
7.2 查询视图
可以像对基本表一样对视图进行查询。
关系数据库管理系统执行对视图的查询时，首先进行有效性检查，检查查询中涉及的表、视图是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。这一转换过程称为视图消解（view resolution）。
7.3 更新视图
更新视图是指通过视图来插入、删除和修改数据。
由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。
在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。
例如，对于学生的平均成绩视图，无法修改其平均成绩。
一般地，行列子集视图是可更新的。各个数据库管理系统一般都只允许对行列子集视图进行更新，而且各个系统对视图的更新还有更进一步的规定。
7.4 视图的作用
合理使用视图能够带来许多好处。
7.4.1 视图能够简化用户的操作
视图机制使用户可以将注意力集中在所关心的数据上。用户所做的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无须了解。
7.4.2 视图使用户能以多种角度看待同一数据
7.4.3 视图对重构数据库提供了一定程度的逻辑独立性
例如，通过建立视图，将数据库中的改变的逻辑结构映射为相同的逻辑结构，使得应用程序不必修改。
7.4.4 视图能够对机密数据提供安全保护
利用视图机制，在设计数据库应用系统时，可以对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的视图上。
例如，student 表涉及全校所有学生的学生数据，在其基础上定义各个院系的视图，从而每个院系只能查看和修改院系的学生数据。
7.4.5 适当利用视图可以更清晰地表达查询
附
Student-Course 数据库
select * from student;
+-----------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-----------+--------+------+------+-------+
| 201215121 | 李勇 | 男 | 20 | CS |
| 201215122 | 刘晨 | 女 | 19 | CS |
| 201215123 | 王敏 | 女 | 18 | MA |
| 201215125 | 张立 | 男 | 19 | IS |
+-----------+--------+------+------+-------+
select * from course;
+-----+---------------+------+---------+
| cno | cname | cpno | ccredit |
+-----+---------------+------+---------+
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | NULL | 2 |
| 3 | 信息系统 | 6 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | NULL | 2 |
| 7 | PASCAL 语言 | 6 | 4 |
+-----+---------------+------+---------+
select * from sc;
+-----------+------+-------+
| sno | cno | grade |
+-----------+------+-------+
| 201215121 | 1 | 92 |
| 201215121 | 2 | 85 |
| 201215121 | 3 | 88 |
| 201215122 | 2 | 90 |
| 201215122 | 3 | 80 |
+-----------+------+-------+
posted on 2021-01-13 23:11 amazzzzzing 阅读(438) 评论(0) 收藏 举报
浙公网安备 33010602011771号