第三章 SQL
一、SQL的定义
-
SQL（Structured Query Language）结构化查询语言，是关系数据库的标准语言
1.1 SQL的特点
-
综合统一
-
高度非过程化
-
面相集合的操作方式
-
以同一种语法结构提供多种使用方式
-
语言简洁，易学易用
二、数据定义语言（DDL）
2.1 数据定义概览
-
SQL的数据定义功能:
模式定义、表定义、视图和索引的定义
2.2 模式的定义与删除
模式的定义
CREATE SCHEMA "S-T" AUTHORIZATION BitHachi; 为用户BitHachi定义了一个模式S-T
-
如果没有指定<模式名>，那么<模式名>隐含为<用户名>
CREATE SCHEMA "S-T" AUTHORIZATION BitHachi CREATE TABLE TAB1( COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2) );
为用户BitHachi创建了一个模式S-T，并在其中定义了一个表TAB1
模式的删除
DROP SCHEMA <模式名> <CASCADE|RESTRICT>
CASCADE(级联)
-
删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT(限制)
-
如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。
2.3基本表的定义、删除与修改
2.3.1定义基本表
CREATE TABLE <表名>( <列名> <数据类型>[ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ] ……… [，<表级完整性约束条件> ] );
-
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
注意：参照表和被参照表可以是同一个表--自引用外键
Eg.我来举一个具体的例子来说明自引用外键的作用。
假设我们有一个员工管理系统,其中有一个 Employee 表,表示公司内部的员工信息。这个表中有一个 ManagerID 列,用于表示每个员工的直接上级。
在这种情况下,ManagerID 列就可以使用自引用外键的方式来实现:
-
Employee 表的结构如下:
-
EmpID (主键)
-
EmpName
-
ManagerID (外键,引用 EmpID 列)
-
-
当我们插入一个新员工记录时,如果这个员工有直接上级,那么 ManagerID 列就会引用该上级员工的 EmpID。
-
如果我们需要调整某个员工的上级关系,只需要更新该员工记录的 ManagerID 列即可。数据库会自动维护这种上下级关系的完整性。
-
我们可以通过递归查询,沿着 ManagerID 列一级一级地查找某个员工的整个上级领导链。
-
这种自引用外键的设计可以大大简化数据模型,避免了创建单独的"上级-下级"关系表。同时也提高了查询效率,因为数据库可以充分利用索引机制。
2.3.2数据类型
-
SQL中
域的概念用数据类型来实现 -
定义表的
属性时 需要指明其数据类型及长度
2.3.3模式与基本表
在定义基本表时,有三种常见的方法来指定表所属的模式(schema):
-
在 CREATE TABLE 语句中直接指定模式名:
CREATE TABLE schema_name.table_name ( -- 列定义 );
这种方式可以在创建表的时候直接指定该表属于哪个模式。模式名和表名之间用点号(.)分隔。
-
在数据库会话中设置当前默认模式,然后创建表:
SET search_path = schema_name; CREATE TABLE table_name ( -- 列定义 );
这种方式先设置当前会话的默认搜索路径(search_path)为指定的模式名,然后创建表时就不需要再重复指定模式名了。
-
在应用程序代码中指定模式名:
String sql = "CREATE TABLE schema_name.table_name (-- 列定义)"; // 在应用程序中执行上述 SQL 语句
这种方式是在应用程序代码中动态拼接 SQL 语句,并在语句中指定模式名。这种方式灵活性较高,可以根据不同的上下文动态设置模式名。
2.3.4修改基本表
ALTER TABLE <表名> 1、[ ADD <新列名> <数据类型> [ 完整性约束 ] ] 2、[ DROP <完整性约束名> ] 3、[ ALTER COLUMN<列名> <数据类型> ]；
1、新增列：不论基本表中原来是否已有数据，新增加的列一律为空值。
向Student表增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;
2、修改数据类型
ALTER TABLE Student ALTER COLUMN Sage INT; 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数
3、删除基本表中的一个属性（列）
ALTER TABLE <表名> DROP COLUMN <列名>;
4、增加约束条件
ALTER TABLE Course ADD UNIQUE(Cname); 增加课程名称必须取唯一值的约束条件
2.3.5删除基本表
标准格式：
DROP TABLE <表名>［RESTRICT| CASCADE］；
RESTRICT：
删除表是有限制的。 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象，则此表不能被删除
CASCADE：
删除该表没有限制。 在删除基本表的同时，相关的依赖对象一起删除
E.g.我来举一个例子来说明一下 DROP TABLE 语句的 RESTRICT 和 CASCADE 选项。
假设有以下两个表:
-
employees表:-
包含员工信息,如姓名、工号、部门等
-
主键为
emp_id
-
-
departments表:-
包含部门信息,如部门名称、部门编号等
-
主键为
dept_id -
外键
dept_id关联employees表的dept_id列
-
现在我们来看两种情况:
-
使用
RESTRICT选项删除employees表:DROP TABLE employees RESTRICT;
在这种情况下,由于
departments表的dept_id外键列关联了employees表,所以 SQL 引擎会阻止删除employees表,因为这会导致departments表中的外键约束失效。 -
使用
CASCADE选项删除employees表:DROP TABLE employees CASCADE;
在这种情况下,SQL 引擎会先删除
departments表中与employees表相关的外键约束,然后再删除employees表。这样就可以成功删除employees表,同时也会删除与之相关的所有对象(在这个例子中就是departments表中的外键约束)。
根据上面的例子,使用 RESTRICT 选项会在以下情况下阻止删除表:
-
被其他表的外键约束引用: 如果要删除的表被其他表的外键约束所引用,那么 SQL 引擎会阻止删除该表,因为这会导致外键约束失效。
-
被其他对象依赖: 如果要删除的表被其他数据库对象(如视图、触发器等)所依赖,那么 SQL 引擎也会阻止删除该表,因为这会导致这些对象失效。
-
被权限或角色引用: 如果要删除的表被数据库用户或角色的权限所引用,那么 SQL 引擎也会阻止删除该表,因为这会影响相关用户或角色的权限。
总之,RESTRICT 选项会在删除表时检查是否存在任何依赖关系,如果存在依赖关系,则会阻止删除操作,以确保数据库的完整性和一致性。
相比之下,CASCADE 选项会自动删除所有相关的对象,从而可以成功删除目标表,但这可能会导致一些意外的结果,所以在使用时需要谨慎考虑
三、数据字典
数据字典是数据库管理系统中非常重要的一部分,它是对数据库中所有对象的描述和定义的集合。数据字典通常包含以下信息:
-
表定义:
-
表名
-
表的描述
-
表的列定义(列名、数据类型、长度、是否允许为空等)
-
表的主键和外键定义
-
-
视图定义:
-
视图名
-
视图的描述
-
视图的定义语句
-
-
索引定义:
-
索引名
-
索引所在表
-
索引的列
-
索引类型(唯一索引、聚簇索引等)
-
-
存储过程和函数定义:
-
过程或函数名
-
输入/输出参数
-
过程或函数的定义语句
-
-
触发器定义:
-
触发器名
-
触发器所在表
-
触发事件(INSERT、UPDATE、DELETE)
-
触发器的定义语句
-
-
用户和权限定义:
-
用户名
-
用户角色
-
用户权限
-
数据字典可以帮助数据库管理员和开发人员更好地了解和管理数据库,它是数据库设计、维护和优化的重要依据。通常,数据库管理系统都会提供查看和管理数据字典的工具,如 Oracle 的 USER_/DBA_/ALL_ 视图,SQL Server 的 INFORMATION_SCHEMA 视图等。
四、数据查询语言（DQL）
SQL提供了SELECT语句进行数据查询
4.1一般格式
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ，<目标列表达式> [别名]] … FROM <表名或视图名> [别名] [ ，<表名或视图名> [别名]] … [WHERE <条件表达式>] [GROUP BY<列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]
下面是对各个部分的解释:
-
SELECT [ALL|DISTINCT] <目标列表达式> [别名] [ ，<目标列表达式> [别名]] …-
SELECT关键字用于指定要查询的列。 -
ALL关键字表示返回所有匹配的行,DISTINCT关键字表示只返回不重复的行。 -
<目标列表达式>是要查询的列的名称或表达式。 -
[别名]是为查询的列设置的别名,用于在结果集中引用该列。
-
-
FROM <表名或视图名> [别名] [ ，<表名或视图名> [别名]] …-
FROM关键字指定要查询的数据表或视图。 -
[别名]是为数据表或视图设置的别名,用于在查询中引用该表或视图。
-
-
[WHERE <条件表达式>]-
WHERE子句用于设置查询条件,只返回满足条件的行。 -
<条件表达式>是一个逻辑表达式,用于过滤数据。
-
-
[GROUP BY <列名1> [HAVING <条件表达式>]]-
GROUP BY子句用于对查询结果进行分组。 -
HAVING子句用于对分组后的结果进行过滤。
-
-
[ORDER BY <列名2> [ASC|DESC]]-
ORDER BY子句用于对查询结果进行排序。 -
ASC表示升序排序,DESC表示降序排序。
-
4.2单表查询
4.2.1 查询指定列
SELECT Sno,Sname FROM Student;
4.2.2 查询全部列
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student; //两种方式 SELECT *FROM Student;
4.2.3 查询经过计算的值
1、查全体学生的姓名及其出生年份。这里假定目前年份是2004年
SELECT Sname,2004-Sage FROM Student;
2、查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名，这里假定目前年份是2004年
SELECT Sname,'Year of Birth: ', 2004-Sage, LOWER(Sdept) FROM Student;
3、使用别名给查询结果命名
SELECT Sname NAME, 'Year of Birth: ' BIRTH, 2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT FROM Student;
4.2.4 选择表中的若干元组
关键词DISTINCT去掉表中重复的行
如果没有指定DISTINCT关键词，则默认为ALL
SELECT Sno FROM SC; /*等价于：*/ SELECT ALL Sno FROM SC;
DISTINCT去掉表中的重复的行
SELECT DISTINCT Sno FROM SC;
4.2.5 查询满足条件的元组（行）
1、比较大小
查询计算机科学系全体学生的名单 SELECT Sname FROM Student WHERE Sdept='CS'; 查询所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname,Sage FROM Student WHERE Sage < 20; 查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade<60;
2、确定范围
查询年龄在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;
3、确定集合
查询信息系（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' );
4、字符匹配
LIKE 和通配符（%、_）是 SQL 中用于模糊查询的重要概念。它们可以帮助我们在查询中使用模糊条件来匹配字符串数据。
-
LIKE 运算符
-
LIKE 运算符用于在 WHERE 子句中进行模糊匹配。
-
它允许我们使用通配符来匹配一个或多个字符。
-
常见的使用场景包括:
-
查找以某个字符串开头或结尾的记录
-
查找包含某个字符串的记录
-
查找以某个字符开头且长度为指定长度的记录
-
-
-
通配符
-
通配符用于在 LIKE 条件中进行模糊匹配。
-
常用的通配符有两种:
-
%: 匹配零个、一个或多个字符 -
_: 匹配任意单个字符
-
-
-
使用示例
-
查找姓名以 '张' 开头的学生:
SELECT * FROM Student WHERE Sname LIKE '张%'; -
查找姓名包含 '小' 字的学生:
SELECT * FROM Student WHERE Sname LIKE '%小%'; -
查找姓名为 3 个字的学生:
SELECT * FROM Student WHERE Sname LIKE '___'; -
查找姓名以 '李' 开头且第 3 个字是 '明' 的学生:
SELECT * FROM Student WHERE Sname LIKE '李_明%';
-
示例：
查询学号为201215121的学生的所有属性 SELECT * FROM Student WHERE Sno LIKE '201215121'; /*等价于：*/ SELECT * FROM Student WHERE Sno = '201215121'; 查询所有姓刘学生的姓名、学号和性别 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 '\'; --ESCAPE '＼' 表示“ ＼” 为换码字符
4.2.6 涉及空值的查询
-
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;
4.2.7 多重条件查询
-
AND和 OR来联结多个查询条件
-
AND的优先级高于OR
-
可以用括号改变优先级
查询计算机系年龄在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';
4.2.8 ORDER BY 子句
-
可以按一个或多个属性列排序； -
升序：ASC； -
降序：DESC； -
缺省值默认为为升序；
4.2.9 当排序列含空值时
-
ASC：排序列为空值的元组最后显示 -
DESC：排序列为空值的元组最先显示
查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列 SELECT Sno,Grade FROM SC WHERE Cno= '3' ORDER BY Grade DESC; 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列 SELECT * FROM Student ORDER BY Sdept,Sage DESC;
4.2.10 聚集函数
1.计数类 COUNT（[DISTINCT|ALL] *） COUNT（[DISTINCT|ALL] <列名>）
2.计算总和 SUM（[DISTINCT|ALL] <列名>）
3.计算平均值 AVG（[DISTINCT|ALL] <列名>）
3.最大最小值类 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;
4.2.11 GROUP BY子句
GROUP BY子句分组： 细化聚集函数的作用对象
-
未对查询结果分组，聚集函数将作用于整个查询结果
-
对查询结果分组后，聚集函数将分别作用于每个组
-
作用对象是查询的中间结果表
-
按指定的一列或多列值分组，值相等的为一组
HAVING短语与WHERE子句的区别：
-
作用对象不同
-
WHERE子句作用于
基表或视图，从中选择满足条件的元组 -
HAVING短语作用于
组，从中选择满足条件的组。
求各个课程号及相应的选课人数 SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno; 查询选修了2门以上课程的学生学号 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >2;
通常,我们会在以下情况下使用 GROUP BY:
-
需要对数据进行聚合计算(如 SUM、AVG、COUNT 等)时。
-
需要根据某些属性对数据进行分组,并对每个分组进行聚合计算。
4.3 连接查询
连接查询：同时涉及多个表的查询
连接条件或连接谓词：用来连接两个表的条件
一般格式：
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> [<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> 连接字段：连接谓词中的列名称
连接条件中的各连接字段类型必须是可比的，但名字不必是相同的
4.3.1 等值与非等值连接查询
等值连接：连接运算符为“=”
查询每个学生及其选修课程的情况 SELECT Student.*,SC.* FROM Student,SC WHERE Student.Sno = SC.Sno;
非等值连接：<,>,>=,<=等其他不等符号
4.3.2 自然连接
将上面的等值连接修改为自然连接 SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC WHERE Student.Sno = SC.Sno;
这个例子中由于除了Sno其他的属性再各自的表中都是唯一的，所以可以去掉前缀名，而Sno在两个表都出现了所以在使用时要加上表的前缀名Student.Sno
4.3.3 自身连接
-
自身连接：一个表与其自己进行连接
-
需要给表起别名以示区别
-
由于所有属性名都是同名属性，因此必须使用别名前缀
查询每一门课的间接先修课（即先修课的先修课） SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,Course SECOND WHERE FIRST.Cpno = SECOND.Cno;
4.4 嵌套查询
（1）嵌套查询概述
-
一个SELECT-FROM-WHERE语句称为一个
查询块 -
将一个
查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname/*外层查询/父查询*/ FROM Student WHERE Sno IN (SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= '2');
-
子查询的限制： ·不能使用ORDER BY子句·
-
层层嵌套方式反映了 SQL语言的结构化
-
有些嵌套查询可以用连接运算替代
（2）不相关子查询 子查询的查询条件不依赖于父查询
由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 （3）相关子查询 子查询的查询条件依赖于父查询
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表 然后再取外层表的下一个元组 重复这一过程，直至外层表全部检查完为止 E.g.
假设有两个表：学生表（Students）和成绩表（Grades），学生表包含学生的学号（StudentID）和姓名（Name），成绩表包含学生的学号（StudentID）和对应的成绩（Grade）。
-
不相关子查询的例子：
SELECT Name FROM Students WHERE StudentID IN (SELECT StudentID FROM Grades WHERE Grade >= 90);
在这个例子中，子查询中的条件是成绩大于等于90分的学生的学号，这个条件不依赖于父查询，因此是一个不相关子查询。父查询会根据子查询的结果来选择学生表中对应的学生姓名。
-
相关子查询的例子：
SELECT Name FROM Students s /*s是Students表的别名*/ WHERE EXISTS (SELECT * FROM Grades g WHERE g.StudentID = s.StudentID AND g.Grade >= 90);
在这个例子中，子查询中的条件是成绩大于等于90分的学生的学号，并且这个条件依赖于父查询中的学生表的学号。父查询会根据子查询的结果来选择学生表中对应的学生姓名。
相关子查询通常需要在处理每个父查询的结果时都重新执行一次子查询，而不相关子查询则不需要这样做。因此，相关子查询可能会比不相关子查询更耗费资源，但在某些情况下，相关子查询可以更精确地筛选数据。
（4）带有ANY（SOME）或ALL谓词的子查询
谓词语义：
-
ANY：任意一个值 -
ALL：所有值
需要配合使用比较运算符:
查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 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的学生
用聚集函数实现 SELECT Sname,Sage FROM Student WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= 'CS') AND Sdept <> 'CS';
（5）带有EXISTS谓词的子查询
EXISTS谓词
存在量词 ∃ 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。 若内层查询结果非空，则外层的WHERE子句返回真值 若内层查询结果为空，则外层的WHERE子句返回假值 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
NOT EXISTS谓词
若内层查询结果非空，则外层的WHERE子句返回假值 若内层查询结果为空，则外层的WHERE子句返回真值
SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= '1');
-
使用EXISTS:
-
这种情况下,查询语句会检查每个学生是否存在于SC表中选修了课程编号为'1'的记录。
-
如果存在,则该学生的姓名会被包含在最终结果集中。
-
最终结果集中包含了所有选修了课程编号为'1'的学生的姓名。
-
-
使用IN:
-
这种情况下,查询语句会先查找SC表中所有选修了课程编号为'1'的学生的学号,形成一个学号集合。
-
然后在Student表中查找学号存在于该集合中的学生,并返回他们的姓名。
-
最终结果集中包含了所有选修了课程编号为'1'的学生的姓名。
-
从结果上来看,使用EXISTS和IN得到的最终结果集是相同的,都是选修了课程编号为'1'的学生的姓名。
但是,在执行过程中,EXISTS子查询只关心是否有结果,而IN子查询需要获取具体的值集合,这可能会对查询性能产生一些影响。
总的来说,在大多数情况下,使用EXISTS和IN得到的最终结果是一致的,但在某些复杂的查询中,可能会有细微的差异。具体选择哪种方式,需要根据具体的需求和查询场景进行权衡。
使用连接运算：
SELECT Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND SC.Cno= '1';
4.5 集合查询
-
并操作UNION -
交操作INTERSECT -
差操作EXCEPT
注意**参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同**
查询计算机科学系的学生及年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19;
-
UNION：将多个查询结果合并起来时，系统自动去掉重复元组。 -
UNION ALL：将多个查询结果合并起来时，保留重复元组
INTERSECT
查询计算机科学系的学生与年龄不大于19岁的学生的交集 SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19;
查询计算机科学系的学生与年龄不大于19岁的学生的差集 实际上是查询计算机科学系中年龄大于19岁的学生 SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage <=19;
EXCEPT
五、数据操作语言（DML）
5.1 基本表的更新
（1）插入数据—INSERT INTO
两种插入数据方式
-
插入元组
-
插入子查询结果
-
可以一次插入多个元组
1、插入元组
INSERT
INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>] … )
功能:将新元组插入指定表中
INTO子句
-
属性列的顺序可与表定义中的顺序不一致
-
没有指定属性列
-
指定部分属性列
VALUES子句
-
提供的值必须与INTO子句匹配
-
值的个数
-
值的类型
［例1］ 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中
INSERT
INTO Student (Sno，Sname，Ssex，Sdept，Sage)
VALUES ('200215128'，'陈冬'，'男'，'IS'，18)；
或者
INSERT
INTO Student
VALUES ('200215126', '张成民', '男',18,'CS');
［例2］ 插入一条选课记录( ‘200215128’，'1 ')
INSERT
INTO SC(Sno,Cno)//RDBMS将在新插入记录的Grade列上自动地赋空值
VALUES ('200215128','1');
//等价
INSERT
INTO SC
VALUES ('200215128','1',NULL);
2、插入子查询结果
INSERT INTO <表名> [(<属性列1> [，<属性列2>… )] 子查询；
功能 将子查询结果插入指定表中
INTO子句(与插入元组类似)
子查询
-
SELECT子句目标列必须与INTO子句匹配 值的个数 值的类型
[例3] 对每一个系，求学生的平均年龄，并把结果存入数据库。
第一步：建表
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;
RDBMS在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则
-
实体完整性
-
参照完整性
-
用户定义的完整性
-
NOT NULL约束
-
UNIQUE约束
-
值域约束
（2）修改数据—UPDATE SET
UPDATE <表名> SET <列名>=<表达式>[，<列名>=<表达式>]… [WHERE <条件>];
功能 修改指定表中满足WHERE子句条件的元组
SET子句
-
指定修改方式
-
要修改的列
-
修改后取值
WHERE子句
-
指定要修改的元组
-
缺省表示要修改表中的所有元组
三种修改方式
-
修改某一个元组的值
-
修改多个元组的值
-
带子查询的修改语句
[例4] 将学生201215121的年龄改为22岁
UPDATE Student SET Sage=22 WHERE Sno='201215121';
[例5] 将所有学生的年龄增加1岁
UPDATE Student SET Sage= Sage+1;
[例7] 将计算机科学系全体学生的成绩置零
UPDATE SC
SET Grade=0
WHERE 'CS'=(
SELECT Sdept
FROM Student
WHERE Student.Sno = SC.Sno
);
RDBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则
-
实体完整性
-
主码不允许修改
-
用户定义的完整性
-
NOT NULL约束
-
UNIQUE约束
-
值域约束
（3）删除数据 —DELETE
三种删除方式
-
删除某一个元组的值
-
删除多个元组的值
-
带子查询的删除语句
删除一个元组的值 DELETE FROM Student WHERE Sno= '200215128'; 删除多个元组的值 DELETE FROM SC； 带子查询的删除语句 DELETE FROM SC WHERE 'CS'=( SELECT Sdept FROM Student WHERE Student.Sno=SC.Sno );
756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
