10_3_第7章_连接查询和子查询

（2）插入子查询结果
• INSERT语句可以与查询语句结合起来，将 数据成批地插入到表中，一般格式如下：
INSERT INTO <表名> [(<属性列1>[, <属性列2> … ] ) ] 子查询;
• 例7-44：创建一个平均成绩表T_AVG，包含学生 学号（长度为7个字符）和平均成绩（单精度数值 型），并插入相应数据。
• 也可以用下面的形式：
SELECT 姓名,成绩 FROM STU,CLASS,SGRADE WHERE CLASS.课程编号=SGRADE.课程编号 AND STU.学号 =SGRADE.学号 AND 课程名称="操作系统";
• 例7-32：查询选修了3门以上课程的学生的姓名。
SELECT 姓名 FROM STU INNER JOIN SGRADE ON STU.学号 = SGRADE.学号 GROUP BY STU.姓名 HAVING Count(课程编号)>=3;
用户的权限
• 建表的权限:属于DBA • DBA授予-->普通用户 • 基本表或视图的属主拥有对该表或视图 的一切操作权限
• 接受权限的用户:
一个或多个具体用户 PUBLIC（全体用户）
例题
例1 把查询Student表权限授给用户U1
GRANT SELECT ON Student TO U1;
GRANT SELECT ON (TABLE) Student TO U1;
• 例7-30：查询所有选修了课程的学生的学 号、姓名、课程编号和成绩。
SELECT STU.学号,姓名,课程编号,成绩 FROM STU INNER JOIN SGRADE ON STU.学 号 = SGRADE.学号;
也可以用下面的形式表述连接条件：
SELECT STU.学号,姓名,课程编号,成绩 FROM STU,SGRADE WHERE STU.学号= SGRADE.学号;
SELECT 姓名 FROM STU WHERE 学号 NOT IN(SELECT 学号 FROM SGRADE WHERE课程编号 IN("1","3","5"))
• （2）子查询处理多次*
• （3）带EXIST测试的子查询*
7.2.4 SQL数据更新功能
• 数据更新用于进行数据的插入、删除或修 改。 • 当更新操作的结果会与完整性约束相矛盾 时，RDBMS会拒绝执行该操作。
• 删除数据使用SQL中的DELETE，一般格 式如下：
DELETE FROM <表名> [WHERE <条件>]
• 注意，在SQL中不存在“逻辑删除”和 “物理删除”，DELETE语句所做的就是 真正的删除。
• （1）删除一个元组 例7-45：从SGRADE表中删除0601025学生1号 选修课记录。 DELETE FROM SGRADE WHERE 学号=”0601025” AND 课程编号 =”1” • （2）删除多个元组 例7-46：从SGRADE表中删除所有1号课程的相 关记录。 DELETE FROM SGRADE WHERE课程编号=”1”
（4）一般形式
• 连接查询的一般形式如下：
SELECT [ALL|DISTINCT] <目标列表达式1> [, <目标列 表达式2>] … FROM <基表名或视图名1> INNER JOIN <基表名或视图 名2> ON 连接条件 … [WHERE <记录过滤条件>]
• 或者用下面的形式：
SELECT [ALL|DISTINCT] <目标列表达式1> [, <目标列 表达式2>] … FROM <基表名或视图名1>,<基表名或视图名2>[, <基表 名或视图名3>] … WHERE <连接条件>
2. 数据控制
• SQL语言的数据控制语句用于对基本表和 视图进行授权以及事务控制等。其中对数 据库的安全控制通过定义某用户对某类数 据拥有指定操作权限来完成。 • 在SQL语言中，授权和收回权限分别用 REVOKE语句和GRANT语句完成。 • Access是通过在【工具】|【安全】中进行 设置来完成授权和收回权限等功能。
• （3）用子查询表达删除条件 例7-47：删除SGRADE表中“数据结构”课 程的所有选课记录。
DELETE FROM SGRADE WHERE 课程编号=(SELECT 课程编号 FROM CLASS WHERE 课程名称=”数据结构”)
3. 修改数据
• 修改数据使用SQL中的UPDATE，一般格 式如下：
7.2.5 其它SQL功能
1．视图的定义和作用 • 视图在概念上和基表等同，可以在视图的 基础上再创建视图。 • 视图机制的存在使数据库的逻辑独立性、 数据保密性和结构清晰性都得到提高。
（1）视图的定义 • SQL语言中的视图定义语句的一般格式为：
CREATE VIEW <视图名>[(<列名1>[, <列名2> … ] )] AS <子查询>
• 例7-37：选修了“操作系统”课程的学生的姓名。
SELECT 姓名 FROM STU WHERE 学号 IN(SELECT 学号 FROM SGRADE WHERE 课程编号=(SELECT 课程编号 FROM CLASS WHERE 课程名称="操作系统"))
注意： • 涉及多表查询时，有时既可以用连接查询，也可 以用嵌套查询，比较 例7-36 例7-32 • 但是当查询结果所需字段来自多个表时，就不能 用子查询而只能用连接查询，比较 例7-37 例7-31。 • 另一些查询则不能用连接查询而只能用子查询。 例如，查询没有选修1号、3号或5号课程中任何一 门的学生的姓名，就不能用连接查询，而只能用 如下的子查询：
• （2）修改多个元组 例7-49：将所有课程的学时加2学时。
UPDATE CLASS SET 学时=学时＋2
• （3）用子查询表达修改条件 例7-50：将所有学生的“高等数学”课程的 成绩设置为零。
UPDATE SET 成绩=0 WHERE 课程编号=(SELECT 课程编号 FROM CLASS WHERE 课程名称=”高等数学”)
– 首先创建平均成绩表： CREATE TABLE T_AVG( 学号 TEXT(7)， 平均成绩 SINGLE); – 再用子查询插入数据值： INSERT INTO T_AVG(学号,平均成绩) SELECT 学号,AVG(成绩) FROM SGRADE GROUP BY 学号;
2. 删除数据
1.插入数据
• 插入数据使用SQL中的INSERT语句，其一 般形式如下：
INSERT INTO <表名> [(<属性列1>[, <属性列2> … ] ) ] VALUES (<常量1>[, <常量2> … ]);
（1）插入一个元组
• 例7-42：将一条学生记录（0601025，李铭，男，地理， 1987-7-5）插入STU表。 INSERT INTO STU VALUES("0601025","李铭","男","地理",#87-7-5#) • 例7-43：如图所示的记录插入CLASS表： INSERT INTO CLASS (课程编号,课程名称,学时,学分) VALUES("2","高等数学",108,6); 或者采用下面的语句插入: INSERT INTO CLASS VALUES("2","高等数学",NULL,108,6);
• 例7-31：询选修了“操作系统”课程的学生的姓名和该 门课的成绩。
SELECT 姓名,成绩 FROM STU INNER JOIN (CLASS INNER JOIN SGRADE ON CLASS.课程编号 = SGRADE.课程编号) ON STU.学号 = SGRADE.学号 WHERE 课程名称="操作系统";
4. 嵌套查询
• 在一个SELECT语句的FROM、WHERE或 HAVING子句中嵌入另一个SELECT语句， 称为嵌套查询或子查询。外层的查询称为 父查询，内层的查询称为子查询：
– 嵌套查询执行时由内向外进行，即把子查询运 行结果作为父查询的数据源或查询条件； – 嵌套查询可以多层嵌套，适合用于解决复杂的 查询问题，体现了“结构化”的特点； – 子查询向父查询返回结果时，根据实际需要， 可以只返回一次结果值，也可以反复执行。
例题（续）
例2 把对Student表的全部权限授予用户U1
GRANT ALL PRIVILEGES ON Student TO U1; GRANT ALL PRIVILEGES ON Student,Course TO U1;
例4
例题（续）
例4 把查询Student表和修改学生学号的权限 授给用户U4 GRANT UPDATE(Sno), SELECT ON Student TO U1;
例题
例8 把用户U1修改学生学号的权限收回
REVOKE UPDATE(Sno) ON Student FROM U1;
例题（续）
例10 把用户U1对SC表的INSERT权限收回 REVOKE INSERT ON SC FROM U1; REVOKE INSERT ON SC FROM U1 CASCADE;
（1）子查询处理单次
• 例7-35：查询选修了课程的学生总数。 • 为避免重复统计选课人数，需要在计数时使用 DISTINCT短语，但在Access中不支持集函数中 的DISTINCT短语，所以不能用以下语句：