数据库设计第3章课件

1、1第3章SQL和QBESQL的目的和重要性。如何使用SELECT语句检索数据。如何使用INSERT语句插入数据。如何使用UPDATE语句更新数据。如何使用DELETE语句删除数据。如何使用CREATE TABLE语句创建新表。另一种关系数据库查询语言QBE。2第1节 结构化查询语言(SQL)一、主要特点 SQL是关系数据库的主要语言。主要特点如下：比较容易学习;非过程语言- 你只需要指定需要什么数据，而不需要关心如何得到这些数据;自由的语言格式;由标准英语单词组成，如SELECT、INSERT、 UPDATE等等;可以被许多用户使用。3二、SQL很重要SQL是第一种，也是目前为止唯一一种，能够

2、被广泛采用的标准数据库语言。SQL给开发商和用户提供了无限的商机。SQL是一种信息处理标准(Federal Information Processing Standard )，由美国联邦政府发布在 FIPS pub127.SQL也被作为基准应用在其他标准上。4三、SQL的目标理想的数据库语言应允许客户进行以下操作:创建数据库和表结构。执行基本操作，如插入、更新和删除。执行简单和复杂的查询操作。必须以用户最小的代价执行这些操作。 必须易于学习。必须是可移植的。5SQL是一种面向转换(transform-oriented) 语言，包含两个主要组成成分:用于定义数据库结构的数据定义语言(DDL);用

3、于检索和更新的数据操作语言(DML)。直到1999年SQL3发布之前, SQL并不包含流程控制命令，仅包含这些定义命令和操作命令。SQL有两种使用方法:交互式和嵌入式，可以在高级语言(如C、 C+)中应用。7第2节 数据操纵文字（Literals译为常量更合适）是SQL语句中使用的常量。所有非数字类型的数据值必须包含在单引号之中(如London)。不能用“”号。所有数字型的数据值必须不加单引号。(如65.0)。一、常量表示8SELECT 查询结果表，由一个或多个列及0个或多个行组成。FROM 指定使用某个(或某些)表。WHERE按照某些条件过滤行。GROUP BY按照相同的列值将行分组。HAV

4、ING按照某些条件过滤组数据。SELECT指定在输出结果中出现的列。ORDER BY 指定输出的排列顺序。子句的顺序不能改变。只有SELECT和FROM是必需的。二、查询语句格式SELECT DISTINCT | ALL * | columnExprn AS newName ,. FROM TableName alias , . WHERE conditionGROUP BY columnList HAVING conditionORDER BY columnList10四、查询指定列，所有行列出所有录像的种类号、名称和日租金额: SELECT catalogNo, title, dailyR

5、ental FROM Video;11五、使用DISTINCT（去掉重复值）列出所有录像的种类：SELECT categoryFROM Video;使用DISTINCT 去除重复值: SELECT DISTINCT categoryFROM Video;12六、计算字段（1）列出租借录像3天的租金: SELECT catalogNo, title, dailyRental*3 FROM Video;14八、范围查询条件列出所有年薪在$45,000到$50,000之间的员工信息：SELECT staffNo, name, position, salary FROM StaffWHERE sala

6、ry BETWEEN 45000 AND 50000;说明：BETWEEN测试包含边界两端的值。15还有一个否定形式 (NOT BETWEEN)。BETWEEN并没有增强SQL表达式的表达能力，其与比较运算符等价，可以被写作: SELECT staffNo, name, position, salaryFROM StaffWHERE salary = 45000 AND salary 40000;25十五、使用MIN、MAX和AVG列出员工年薪的最小值、最大值和平均值: SELECT MIN(salary) AS minSalary, MAX(salary) AS maxSalary, AVG

7、(salary) AS avgSalaryFROM Staff;27在SELECT列表中，除了聚合函数中使用的列，其他所有列必须出现在GROUP BY子句中。 如果WHERE子句和GROUP BY子句同时使用，则WHERE子句先被应用，然后在过滤出的结果中进行分组。ISO标准认为在进行GROUP BY时，两个null值是相等的。 即是指，group by a ，如果原始数据中a列中有N个null值的行，则这N个null值的行会被认为是同组。28十七、受限分组HAVING子句HAVING和GROUP BY子句同时作用来限制在结果表中出现的分组。HAVING与WHERE语法相似，但是WHERE子句

8、是过滤进入结果表中的单行，且不能用聚合函数，HAVING子句则是过滤进入结果表中的整个分组。在HAVING子句中使用的列名必须在GROUP BY列表中出现，或者被包含在聚合函数中。例：SELECT a , COUNT(b) , SUM(c) WHERE b = 1 GROUP BY a HAVING SUM(c) 10000 ORDER BY a;执行顺序是：WHERE首先选取原始表中所有b=1的行，然后根据a对筛选出来的行进行group，然后选择group后SUM(c) 10000的结果，最后根据a列进行排序。其实就是说，having条件中的列必须是在进行了之前where和group操作后得

9、到的这个中间结果表中存在的列。29例：找出含有超过1名员工的分公司中的员工数和工资总额:SELECT branchNo, COUNT(staffNo) AS totalStaff,SUM(salary) AS totalSalaryFROM StaffGROUP BY branchNoHAVING COUNT(staffNo) 1ORDER BY branchNo;30十八、SELECT语句排序ORDER BY 找出每个分公司的员工数和他们的年薪总和，并进行排序:SELECT branchNo, COUNT(staffNo) AS totalStaff, SUM(salary) AS tota

10、lSalaryFROM Staff GROUP BY branchNo ORDER BY branchNo;31十九、子查询SQL语句可以只包含一个SELECT，也可以将一个SELECT语句嵌入到另一个SELECT语句中。内层的SELECT语句可以作为WHERE和HAVING 子句的条件嵌套在外层的SELECT语句中，这种内层的SELECT语句叫做子查询或者嵌套查询。子查询可出现在INSERT、UPDATE和DELETE语句中。子查询语句可作为条件中的比较值出现在任何sql语句中，包括UNION等一些高级语句中。子查询实际上可以提供目标项，如INSERT语句中作为多项的值数据，可以写为 INS

11、ERT INTO TABLE_A （a，b，c） SELECT d AS a ， e AS b , f AS c FROM TABLE_C WHERE ;二十、子查询分类不相关子查询：子查询的查询条件不依赖于父查询相关子查询：子查询的查询条件依赖于父查询二十一、嵌套查询求解方法不相关子查询：是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止二十二、

12、 使用子查询找出在8 Jefferson Way分公司工作的员工:SELECT staffNo, name, position FROM StaffWHERE branchNo = (SELECT branchNo FROM Branch WHERE street=8 Jefferson Way);子查询作为关系数据库的重要查询条件，是表关系的突出表现，如TABLE_A中含有a,b,c项，TABLE_B中含有c，d项目，可以d为条件查找TABLE_A中的对应项。说明：子查询不能放在branchNo前面34内层的SELECT在Branch表中找出在8 Jefferson Way地址上的分公司的分

13、公司号 (B001)。外层的SELECT然后根据这个分公司号(B001)，检索出在该分公司中工作的员工的信息。 外层SELECT实际进行的查询是:SELECT staffNo, name, positionFROM Staff WHERE branchNo = B001;说明：35二十三、 包含聚合函数的子查询列出年薪高于平均年薪的所有员工:SELECT staffNo, name, positionFROM Staff WHERE salary (SELECT AVG(salary) FROM Staff);不能写成WHERE salary AVG(salary)。换句话说，使用子查询是为了

14、找到平均年薪的值 (41166.67)，然后使用外层查询查找出年薪高于这个平均值的员工的其他信息:SELECT staffNo, name, position FROM Staff WHERE salary 41166.67;36结果表二十四、带有EXISTS谓词的子查询1. EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义2. NOT EXIS

15、TS谓词查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相关子查询*/ WHERE Sno=Student.Sno AND Cno= 1 )；思路分析： 本查询涉及Student和SC关系。 在Student中依次取每个元组的Sno值，用此值去检查SC关系。 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系。还可以用连接运算SELECT Sname FROM Student, SCWHERE Stu

16、dent.Sno=SC.Sno AND SC.Cno= 1;查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1)；此例用连接运算难于实现 3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。 查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELEC

17、T Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨；4、用EXISTS/NOT EXISTS实现全称量词（了解）SQL语言中没有全称量词（For all）可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：(x)P ( x( P) 例；查询选修了全部课程的学生姓名。SELECT Sname FROM StudentWHERE NOT EXISTS （SELECT * FROM Course WHERE NOT E

18、XISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）；5、用EXISTS/NOT EXISTS实现逻辑蕴函(了解)例子：查询至少选修了学生200295002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要200295002学生选修了课程y，则x也选修了y。不存在这样的课程y，学生200295002选修了y，而学生x没有选。用NOT EXISTS谓词表示： SELECT DISTINCT Sno FROM SC SCX /* WHERE NOT EXISTS */ WHE

19、RE SCX.Sno 200295002 AND NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 200295002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；47二十五、子查询规则ORDER BY子句不能用在子查询中(可以在外层查询中使用)。 注：ORDER BY即使在子句中能使用也毫无意义，所以在子查询中直接把ORDER BY作为非法，减轻SQL工作量；子查询语句检索出的结果表必须由一列列名或者是一个表达式组成，除非子查询中

20、使用关键字EXISTS。 注：“一个表达式”的意思，应该指聚合函数等。 事实上，子查询不一定必须是一列的，正如之前说的，可以由多列组成，如INSERT INTO TABLE_A （a，b，c）SELECT d AS a ， e AS b , f AS c FROM TABLE_C WHERE ;关键是看子查询用在哪里。关于EXISTS语句，事实上是指，SELECT a , b , c FROM TABLE_A WHERE EXISTS (SELECT * FROM TABLE_B WHERE )；即是说，只要子查询能产生结果，则条件就为true；48默认的，子查询中的列名是子查询FROM表中存

21、在的列名，可以通过AS子句使得子查询产生的表获得别名，从而作为外部查询的目标表。 注：意思就是说可以这样： SELECT d , e , f FROM (SELECT a AS d , b AS e , c AS f from TABLE_A WHERE ) AS TABLE_C WHERE ; 也就是说，子查询实际上产生的是一个表，这个表中的值可以作为外部操作的判断的指标，这个表可以作为外部操作的目标表（如之前举例的INSERT操作）。49当子查询是比较运算中涉及两个操作数中的一个时，子查询必须出现在比较符的右侧。子查询不能用作表达式的操作数(子查询作为WHERE条件时，必须和EXIST或者

22、NOT EXIST一起使用时)。注：意思就是，即使子查询的结果为唯一的true，也不能写作：SELECT * FROM TABLE_A WHERE (SELECT FROM WHERE );即是说，子查询产生的结果不能当作布尔值使用。50二十六、多表查询到目前为止，我们提供的例子的检索结果中的列都是来自同一个表。如果需要得到来自多个表的信息，则需要执行连接操作。要执行连接操作，只需要在FROM后加上多个表名。使用逗号分隔多个表名，并在WHERE子句后指定连接列。注：两个表必须有相同的列，才能对两个表进行关联。 51在FROM子句后，也可以使用别名作为表名。别名可以跟在表名后面用一个空格隔开。

23、在列名不明确的情况下，别名可以用来指定列名的出处。注：意思就是，两个表中都有名为a的列，则TABLE_A.a就指引用的是TABLE_A中的a列。52二十八、简单连接列出所有录像以及他们的导演:SELECT catalogNo, title, category, v.directorNo, directorNameFROM Video v, Director dWHERE v.directorNo = d.directorNo;注：书写SQL时，还是多使用AS，直接空格跟别名的书写不够规范，更不易修改。Video AS v，修改时查找起来更方便。53结果中只包含两个表中directorNo列的值

24、相同(v.directorNo = d.directorNo) 的行。54二十九、使用JOIN结构替代连接的替代方法:FROM Video v JOIN Director d ON v.directorNo =d.directorNoFROM Video JOIN Director USING directorNoFROM Video NATURAL JOIN Director在任何一种情况下，都是用FROM子句替代原先的FROM和WHERE子句。然而，第一种替代方案产生了一张有两个相同directorNo列的表，而剩下的两种方案产生只包含一个directorNo列的表。一般用第一种JOIN语

25、句替代。如：SELECT catalogNo ， title ， category ， v.directorNo , directorName FROM Video AS v JOIN Director AS d ON v.directorNo = d.directorNo;其结果和上页SQL的结果是一样的55三十、四表连接列出所有录像以及他们的导演、演员名单和相应的角色: SELECT v.catalogNo, title, category, directorName, actorName, character FROM Video v, Director d, Actor a, Role

26、 r WHERE d.directorNo = v.directorNo AND v.catalogNo = r.catalogNo AND r.actorNo = a.actorNo;5657三十一、INSERT语句 INSERT INTO TableName (columnList) VALUES (dataValueList)columnList是可选项; 若省略，SQL将假定所有的列按照他们在建表时定义的顺序排列。任何省略了的列必须在建表时指定可以为null，或者指明该列可以使用默认值DEFAULT。dataValueList必须按下列规则与columnList 匹配:两个列表中的项目

27、数必须一致;两个列表中的各项的位置必须直接对应;dataValueList中的每一项数据类型必须与相应的列的数据类型兼容。58例：在Video表中插入一行: INSERT INTO VideoVALUES (207132, Die Another Day, Action， 5.00, 21.99, D1001 );就相当于:INSERT INTO Video (catalogNo , title , category , dailyRental , price , directorNo)VALUES (207132, Die Another Day, Action，5.00, 21.99, D

28、1001 );59三十二、UPDATE语句UPDATE TableName SET columnName1 = dataValue1 , columnName2 = dataValue2.WHERE searchConditionTableName可以是一个表或者一个可修改的视图。SET子句指明需要修改的一个列或多个列的列名。WHERE子句是可选的:如果省略，则修改表中指定列的所有行的数据;如果指定了WHERE子句的条件，则只有满足条件(searchCondition)的行才被修改。 60修改的数据(dataValue)必须与原始数据中该列的数据类型一致。 注：实际上更新的数据都会被强制类型转

29、换为原始数据类型。例，将Thriller类的录像的日租金提高10%:UPDATE VideoSET dailyRental = dailyRental*1.1WHERE category = Thriller;61三十三、DELETEDELETE FROM TableName WHERE searchConditionTableName可以是一个表或者一个可修改的视图。searchCondition是可选项; 如果省略，则会删除表中的所有行(但并不是删除该表)。如果指定了searchCondition ，则只有符合条件的行被删除。例，删除类别号为634817 的出租录像:DELETE FROM

30、 VideoForRentWHERE catalogNo = 63481762三十四、数据定义两种主要的SQL DDL语句:CREATE TABLE 在数据库中创建一个新表。CREATE VIEW 在数据库中创建一个新的视图。格式：CREATE TABLE TableName (columnName dataType NOT NULL UNIQUEDEFAULT defaultOption,.PRIMARY KEY (listOfColumns),UNIQUE (listOfColumns), ,FOREIGN KEY (listOfFKColumns) REFERENCES ParentTa

31、bleName (listOfCKColumns), ON UPDATE referentialAction ON DELETE referentialAction ,631、定义列columnName dataType NOT NULL UNIQUEDEFAULT defaultOptionSQL支持的数据类型有:64 2、定义主键和实体完整性使用PRIMARY KEY子句来实现实体完整性。例如: CONSTRAINT pk PRIMARY KEY (catalogNo)CONSTRAINT pk1 PRIMARY KEY (catalogNo, actorNo)3、定义外键和参照完整性使用

32、FOREIGN KEY子句在表中定义外键。SQL通过限制子表的INSERT和UPDATE操作实现参照完整性，这些操作试图在子表中创建和父表中的候选键不相匹配的外键值。注：意思就是，父表子表中都有这个列，你修改了子表中的这个列，那么父表中的相应列无法得到同步的更新，会造成数据的不同步。65如果是更新或删除父表中的和子表有匹配行的候选键值的UPDATE和DELETE操作，那么SQL通过在FOREIGN KEY子句中使用ON UPDATE和ON DELETE子操作来实现引用操作。注：意思就是，父表子表中都有这个列，你修改父表列时，可以引入FOREIGN KEY （a）ON UPDATE CASCADE 来表示当父表该列修改时，会触发子表