第3章 关系数据库标准语言SQL（重点） | 数据库知识点整理

第3章 关系数据库标准语言SQL（重点）

了解

• SQL语言发展过程
• 关系数据库技术和关系数据库管理系统RDBMS产品的发展过程

掌握

• SQL语言的特点和优点
• 面向过程的语言和SQL语言的区别
• 关系数据库系统为数据库应用系统的开发提供良好环境，减轻了用户负担，提高用户生产率的原因
• 用SQL语言完成对数据库的增删改查，特别是各种查询

知识点

• SQL的特点
  • 综合统一。SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体
  • 高度非过程化。用 SQL 语言进行数据操作， 只要提出 “做什么”，而无需指明 “怎么做”，因此无需了解存取路径，存取路径的选择以及 SQL 语句的操作过程由系统自动完成。
  • 面向集合的操作方式。 SQL 语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。
  • 以同一种语法结构提供两种使用方式。 sQL 语言既是自含式语言，又是嵌入式语言。作为自含式语言， 它能够独立地用于联机交互的使用方式； 作为嵌入式语言， 它能够嵌入到高级语言程序中，供程序员设计程序时使用。
  • 语言简捷，易学易用。

  在DROP TABLE时，RESTRICT和CASCADE的区别

  • RESTRICT表示表的删除是有限制条件的。要删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则表不能被删除
  • CASCADE表示表的删除没有限制条件，在删除基本表的同时，相关的依赖对象（如视图）都将被删除

  有两个关系S（A，B，C，D）和T（C，D，E，F），写出与下列查询等价的SQL表达式

  • (1)SELECT * FROM S WHERE A=10
  • (2)SELECT DISTINCT A,B FROM S
  • (3)SELECT A,B,S.C,S.D,E,F FROM S,T WHERE S.C=T.C AND S.D=T.D
  • (4)SELECT A,B,S.C,S.D,T.C,T.D,E,F FROM S,T WHERE S.C=T.C
  • (5)SLEECT A,B,S.C,S.D,T.C,T.D,E,F FROM S,T WHERE A<E
  • (6)SELECT S1.C,S1.D,T.C,T.D,E,F FROM T,(SELECT DISTINCT C,D FROM S) AS S1

  用SQL语句建立第2章习题6中的4个表，针对建立的4个表用SQL语言完成第2章习题6中的查询

  • 建S表 S(SNO,SNAME,STATUS,CITY);
    ​CREATE TABLE S(SNO CHAR(3).​ SNAME CHAR(10), STATUS CHAR(2), CITY CHAR(10)​​​)​;
  • 建P表 P(PNO,PNAME,COLOR,WEIGHT);
    CREATE TABLE P(PNO CHAR(3), PNAME CHAR(10), COLOR CHAR(4), WEIGHT INT​​​);​
  • 建J表 J(JNO,JNAME,CITY);
    CREATE TABLE J(JNO CHAR(3), JNAME CHAR(10),CITY CHAR(10)​​);​
  • 建SPJ表 SPJ(SNO,PNO,JNO,QTY);
    CREATE TABLE SPJ(SNO CHAR(3), PNO CHAR(3),JNO CHAR(3), QTY INT​​​);​
  • 求供应工程J1零件的供应商号码SNO
    SELECT SNO​FROM SPJWHERE JNO='J1';​
  • 求供应工程J1零件P1的供应商号码SNO
    SELECT SNOFROM SPJWHERE JNO='J1' AND PNO='P1';​​
  • 求供应工程J1零件为红色的供应商号码SNO
    SELECT SNOFROM SPJWHERE JNO='J1' AND PNO IN (SELECT PNO FROM P WHERE COLCO=‘红’​​);或SELECT SNOFROM SPJ,PWHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='红';​​​​​​​​​
  • 求没有使用天津供应商生产的红色零件的工程号JNO
    SELECT JNOFROM JWHERE NOT EXISTS (SELECT * ​FROM SPJ WHERE SPJ.JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE CITY='天津'​​) AND​ PNO IN (SELECT PNO FROM P WHERE COLOR='红'​​)​​​​);​​​或SELECT JNOFROM JWHERE NOT EXISTS (SELECT * FROM SPJ,S,P WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND S.CITY='天津' AND P.COLOR='红'​​​);​​​​​
  • 求至少用了供应商S1所供应的全部零件的工程号JNO
    SELECT DISTINCT JNOFROM SPJ SPJZWHERE NOT EXISTS (SELECT * ​FROM SPJ SPJX WHERE SNO='S1'​ AND NOT EXISTS​ (SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO​​​)​);​​​

  针对习题3中的4个表，用SQL语言完成以下各项操作

  • 找出所有供应商的姓名和所在城市。
    SELECT SNAME,CITY ​FROM S；
  • 找出所有零件的名称、颜色、重量。
    SELECT PNAME,COLOR,WEIGHT ​FROM P；
  • 找出使用供应商 S1 所供应零件的工程号码。
    SELECT DIST JNO ​FROM SPJ ​WHERE SNO='S1'；
  • 找出工程项目 J2 使用的各种零件的名称及其数量。
    SELECT P.PNAME,SPJ.QTY ​FROM P,SPJWHERE P.PNO=SPJ.PNO ​AND SPJ.JNO='J2';​
  • 找出上海厂商供应的所有零件号码。
    SELECT DISTINCT PNO ​FROM SPJ​WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海'​​);​
  • 找出使用上海产的零件的工程名称。
    SELECT JNAME ​FROM SPJ,S,JWHERE S.SNO=SPJ.SNO AND S.CITY=' 上海 ' AND J.JNO=SPJ.JNO或​SELECT JNAME 　　 FROM J 　　 WHERE JNO IN 　　 (SELECT JNO 　　 FROM SPJ, S 　　 WHERE SPJ. SNO=S.SNO 　　 AND S.CITY='上海');
  • 找出没有使用天津产的零件的工程号码。
    SELECT JNO FROM J WHERE NOT EXISTS 　　 (SELECT * 　　 FROM SPJ 　　 WHERE SPJ.JNO=J.JNO AND SNO IN 　　 (SELECT SNO 　　 FROM S 　　 WHERE CITY=‘天津’))； 或 SELECT JNO FROM J WHERE NOT EXISTS 　　 (SELECT *1 　　 FROM SPJ, S 　　 WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO AND ​S.CITY=‘天津’)；​
  • 把全部红色零件的颜色改成蓝色。
    UPDATE P SET COLOR='蓝' WHERE COLOR='红' ; ​
  • 由S5供给J4的零件P6改为由S3供应，请作必要的修改。
    UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6'; ​
  • 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。
    DELETE FROM SPJ WHERE SNO='S2'; 或 DELETE FROM S WHERE SNO='S2'; ​注意删除顺序，应该先从SPJ表中删除供应商S2所供应零件的记录，然后从从S表中删除S2。
  • 请将 (S2，J6，P4，200) 插入供应情况关系。
    INSERT INTOSPJ(SNO, JNO, PNO, QTY) VALUES (S2，J6，P4，200); 或 INSERT INTO SPJ VALUES (S2，P4，J6，200);

  基本表、视图，两者的区别和联系

  • 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。
  • 视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。视图在概念上与基本表等同，用户可以如同基本表那样使用视图，可以在视图上再定义视图。

  视图的优点

  • 视图能够简化用户的操作。
  • 视图使用户能以多种角度看待同一数据。
  • 视图对重构数据库提供了一定程度的逻辑独立性。
  • 视图能够对机密数据提供安全保护。

  所有的视图是否都可以更新？为什么？

  • 不是。视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新，所以，并不是所有的视图都是可更新的。如《概论》3.5.1中的视图S_G（学生的学号及他的平均成绩）
    CREAT VIEW S_G(Sno，Gavg)AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；
  • 要修改平均成绩，必须修改各科成绩，而我们无法知道哪些课程成绩的变化导致了平均成绩的变化。

  哪类视图是可以更新的，哪类视图是不可更新的？ 各举一例说明

  • 基本表的行列子集视图一般是可更新的。如《概论》3.5.3中的例1。
  • 若视图的属性来自集函数、表达式，则该视图肯定是不可以更新的。如《概论》3.5.3中的S_G视图。

  综合题
  请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。针对该视图完成下列查询：（1）找出三建工程项目使用的各种零件代码及其数量。（2）找出供应商S1的供应情况。

  • 建视图
    CREATE VIEW V_SPJ ASSELECT SNO, PNO, QTYFROM SPJWHERE JNO=（SELECT JNOFROM JWHERE JNAME='三建'）;
  • 对该视图查询
    (1) 找出三建工程项目使用的各种零件代码及其数量。SELECT PNO, QTYFROM V_SPJ;(2) 找出供应商S1的供应情况。SELECT PNO, QTY FROM V_SPJWHERE SNO='S1';

补充

• • SQL是【非过程化】的语言，用户无须指定存取路径
  • 对表中数据进行删除的操作是【DELECT】
  • 数据库中建立索引的目的是为了【加快存取速度】
  • 视图是数据库系统三级模式中的【外模式】
  • 基本表和视图一样，都是关系
  • 可以使用SQL对基本表和视图进行操作
  • 可以从基本表或视图上定义视图，视图不仅可以从单个基本表导出，还可以从多个基本表导出
  • 视图不存储数据
  • SELECT子句中的目标列可以是表中的属性列，也可以是表达式
  • 使用ANY或ALL谓词时必须与比较运算符同时使用
  • SQL语言具有【数据定义】【数据查询】【数据操纵】【数据控制】的功能
  • SQL语句中用来消除重复的关键词是【DISTINCT】
  • 若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和列，但保留了主码，这类视图称为【行列子集视图】
  • SQL语言的数据定义功能包括【模式定义】【表定义】【视图定义】【索引定义】
  • 在视图上不能完成的操作是【在视图上定义新的表】
  • CREATE、DROP、ALTER实现【数据定义】功能
  • 删除一个视图的命令是【DROP】
  • 删除表S的命令是【DROP TABLE S】
  • 在基本表S中增加一列CN（课程名），可用【ALTER TABLE S ADD (CN CHAR(8))】
  • 在基本表S中删除一个属性“年龄”，可用【ALTER TABLE S DROP Age】
  • 有关系S（S＃，SNAME，SAGE），C（C＃，CNAME），SC（S＃，C＃，GRADE）。其中S＃是学生号，SNAME是学生姓名，SAGE是学生年龄， C＃是课程号，CNAME是课程名称。要查询选修“ACCESS”课的年龄不小于20的全体学生姓名的SQL语句是SELECT SNAME FROM S，C，SC WHERE子句。这里的WHERE子句的内容是【S.S# = SC.S# and C.C# = SC.C# and SAGE>=20 and CNAME=‘ACCESS’】
  • 设关系数据库中一个表S的结构为S（SN，CN，grade），其中SN为学生名，CN为课程名，二者均为字符型；grade为成绩，数值型，取值范围0－100。若要把“张二的化学成绩80分”插入S中，则可用【INSERT INTO S VALUES（’张二’，’化学’，80）】
  • 设关系数据库中一个表S的结构为：S（SN，CN，grade），其中SN为学生名，CN为课程名，二者均为字符型；grade为成绩，数值型，取值范围0－100。若要更正王二的化学成绩为85分，则可用【UPDATE S SET grade＝85 WHERE SN＝’王二’ AND CN＝’化学’】
  • 在SQL语言中，子查询是【嵌入到另一个查询语句之中的查询语句】
  • SQL是一种【关系数据库】语言
  • 有关系S（S＃，SNAME，SEX），C（C＃，CNAME），SC（S＃，C＃，GRADE）。其中S＃是学生号，SNAME是学生姓名，SEX是性别， C＃是课程号，CNAME是课程名称。要查询选修“数据库”课的全体男生姓名的SQL语句是SELECT SNAME FROM S，C，SC WHERE子句。这里的WHERE子句的内容是【A.S.S# = SC.S# and C.C# = SC.C# and SEX=’男’ and CNAME=’数据库’】
  • CREATE TABLE SC （S# CHAR（6） NOT NULL，C# CHAR（3） NOT NULL，SCORE INTEGER，NOTE CHAR（20））；向SC表插入如下行时，【（’200823’，’101’，NULL，NULL）】行可以被插入
  • 假设学生关系S（S＃，SNAME，SEX），课程关系C（C＃，CNAME），学生选课关系SC（S＃，C＃，GRADE）。要查询选修“Computer”课的男生姓名，将涉及到关系【S，C，SC】
  • 【GRANT】不是数据定义语句
  • 相关子查询和不相关子查询
    • 在嵌套查询中，如果子查询的查询条件不依赖于父查询，称为不相关子查询
    • 如果子查询的查询条件依赖于父查询，称为相关子查询
  • ANY和ALL谓词与聚集函数或IN谓词可能存在的等价转换关系
  • 综合题
    • 关系R包含A、B、C三个属性，包含的数据如下
    • 写出对查询语句SELECT * FROM R WHERE X；当X为下列条件时的查询结果
      • A IS NULL
        空的结果集
      • A>8 AND B<20
        空的结果集
      • A>10 OR B<20
      • C+10>25
      • EXISTS(SELECT B FROM R WHERE A=10)
      • C IN (SELECT B FROM R)
        空的结果集