第三章 关系数据库标准语言 SQL

1. 第三章关系数据库标准语言SQL

2. SQL语言 • SQL概述 • SQL数据查询功能 • SQL数据定义功能 • SQL数据更新功能 • 嵌入式SQL

3. SQL概述 • 历史 • 1974年，由Boyce和Chamberlin提出，称为SEQUEL（Structured English Query Language）。 • 1975-1979年，在IBM的San Jose研究室研制的System R上实现。 • 1981年， IBM在推出SQL/DS关系数据库时，将其命名为SQL (Structured Query Language)。 • 现在，不仅关系型数据库系统，甚至非关系型数据库系统都将SQL作为其数据库语言。SQL语言已成为当今最为成功和应用最广的数据库语言。

4. SQL概述（I） • 有关标准 随着SQL语言应用的日益广泛，ANSI和ISO先后制定了多个SQL标准： • SQL-86：较为简单，主要包括数据定义语言、数据操纵语言、嵌入式语法等几个部分。 • SQL-89：增加了对完整性约束的支持。 • SQL-92：也称SQL2，是SQL-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 • SQL-3：正在讨论中的新的标准，将增加对面向对象模型的支持。

5. SQL概述（II） • 特点 • 综合统一 集数据定义，数据操纵，数据查询和数据控制于一体。 单一的结构----关系，带来了数据操作符的统一。 • 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。 • 面向集合的操作方式 操作的对象和操作的结果均为集合。 • 以同一种语法结构提供两种使用方式 SQL既是自含式语言，又是嵌入式语言

6. SQL概述（III） • 语言简捷，易学易用 类似于英语自然语言

7. 用户 SQL 视图1 视图2 外模式 基本表1 基本表2 基本表3 基本表4 概念模式 存储文件1 存储文件2 内模式 SQL对关系数据库模式的支持

8. SQL对关系数据库模式的支持 • 基本表 • 是本身独立存在的表，一个（或多个）基本表对应一个存储文件 • 存储文件 • 由数据块构成的存储空间，用于存储基本表、索引等。其逻辑结构组成了关系数据库的内模式 • 视图 • 是从一个或几个基本表中导出的表，其本身不独立存储于数据库中

9. 例子表 STUDENT COURSE SC

10. SQL数据查询功能 • 查询语句SELECT的语法： Select [ALL|DISTINCT]<目标表达式>[{,<目标表达式>}] From <表名或视图名>[{,<表名或视图名>}] [Where <条件表达式>] [Group By <分组列> [{,<分组列>}][HAVING <条件表达式>]] [Order By <排序列> <排序方式>[{,<排序列> <排序方式>}]

11. SQL数据查询功能 • 其语义是：从FROM子句给定的表中，检索出满足WHERE子句给定检索条件的记录，并根据SELECT子句从这些记录中选出指定的列（属性）所构成的结果集合。SELECT和FROM两个子句定义了投影操作。WHERE子句给出了选取操作的定义（当涉及多表操作时，WHERE子句要同时给出连接操作条件）。可见，查询语句作为整体代表着关系代数中的投影、选取、连接等操作的组合。但是，由于在查询语句中并不规定投影、选取、连接等操作的执行顺序。因此，它比关系代数更一般，更少过程化。

12. SQL数据查询功能 • 单表查询 • 连接查询 • 嵌套查询 • 集合查询

13. 单表查询 • 查询全体学生的姓名与学号 • Select Sname, Sno From Student; • 查询全体学生的全部属性 • Select * From Student; • 查看全体学生的姓名及其出生年份 • Select Sname, 2001-Sage From Student;

14. 单表查询（续I） • 查询全体学生所在学校、姓名、出生年份和所在系（用小写字母显示系名）。 • Select “北航”, Sname, 2001-Sage, ISLOWER(Sdept) From Student; • Select “北航” School, Sname, 2001-Sage Birth, ISLOWER(Sdept) From Student;

15. 单表查询（续II）

16. 单表查询（续III） • 查询选修了课程的学生学号 • Select Distinct Sno From STUDENT; • 查询计算机系全体学生的姓名 • Select Sname From Student where Sdept=‘CS’; • 查询所有年龄在20岁以下的学生姓名及其年龄 • Select Sname From Student where Sage<20;

17. 单表查询（续IV） • 查询年龄在20~23（含20和23岁）之间的学生姓名、性别和年龄。 • Select Sname, Ssex, Sage From Student Where Sage BETWEEN 20 AND 23; • 查询数学系和计算机系的学生的姓名和性别。 • Select Sname, Ssex From Student Where Sdept IN (‘MA’, ‘CS’);

18. 单表查询（续V） • 查询所有姓刘的学生的学号、姓名 • Select Sno, Sname From Student Where Sname Like ‘刘%’ • 查询所有姓刘，并且名字为两个汉字的学生的学号、姓名 • Select Sno, Sname From Student Where Sname Like ‘刘__’

19. 单表查询（续VI） • 查询所选课程没有成绩的学生的学号，所选课程号。 • Select Sno, Cno From SC Where Grade IS NULL

20. 单表查询（续VII） • ORDER BY子句可以将查询结果按照一个或多个属性列的升序（ASC）或降序（DESC）排序。 • 查询全体学生的情况，并按所在系的升序和年龄的降序排列 • Select * From Student Order By Sdept Asc, Sage Desc

21. 单表查询（续VIII） • 聚集函数 • Count() 求元组个数 • Sum() 对数值列求总和 • Avg() 求数值列的平均值 • Max() 求最大值 • Min() 求最小值

22. 单表查询（续IX） • 查询学生总数 • Select Count(*) From Student; • 查询选修了课程的学生人数 • Select Count(Distinct Sno) From SC; • 计算1号课程的学生平均成绩 • Select AVG(Grade) From SC Where Cno=‘1’; • 查询选修1号课程的学生最高分数 • Select MAX(Grade) From SC Where Cno=‘1’;

23. 单表查询（续X） • 查询各门课及其相应选课人数。 Select Cno, Count(Sno) From SC Group By Cno; • 查询选课至少为三门的学生的学号及其选课门数。 Select Sno, Count(Sno) From SC Group By Sno Having Count(Sno)>=3;

24. 连接查询 • 连接条件 • 连接查询中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为： [<表名> .] <列名> <比较运算符> [<表名> .] <列名> • 其中的<列名>称为连接字段， 连接条件的各个连接字段类型必须是可比的，但不必是相同的。<比较运算符>主要有：=，>，<，>=，<=，!=。当连接运算符为等号时，称为等值连接，否则称非等值连接。

25. 连接查询（续I） • 查询每个学生及其所选课程的情况 • Select Student.*, SC.* From Student, SC Where Student.Sno = SC.Sno; • 查询每一门课的间接先修课（即先修课的先修课） • Select FIRST.Cno, SECOND.Cpno From Course FIRST, Course SECOND Where FIRST. Cpno = SECOND. Cno • 查询所有学生的学号、姓名、所选修课程的课程号和成绩 • Select Student.Sno, Sname, Cno, Grade From Student, SC Where Student.Sno=SC.Sno(*)

26. 嵌套查询 • 在SQL语言中，一个SELECT-FROM-WHERE 称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。上层的查询块称为外层查询或父查询，下层查询称为内层查询或子查询。外层查询利用子查询来获取检索条件的条件值，并根据这个条件值确定外层查询的结果集合。通过子查询，可以利用一系列简单查询来构造一个复杂查询。 • 子查询中的SELECT语句中不能使用ORDER BY子句，ORDER BY子句只能对最终查询结果排序。

27. 嵌套查询（续I） • 不相关子查询 • 不相关子查询的执行顺序是由内向外，即每个子查询在上一级查询处理前求解，子查询的结果集合作为其外部查询的检索条件的条件值。 • 相关子查询 • 相关子查询的查询条件引用了其外部查询的某个属性值，从而其执行依赖于其外部查询。其执行顺序是：首先取外部查询选取一行记录（称为候选行），然后子查询利用候选行中相关列的值查询结果数据。接着外部查询利用子查询返回的结果集合判断候选行是否满足检索条件，若满足，则把它放入外部查询的结果集合。重复上述步骤直到处理完外部查询的每一行候选行。

28. 嵌套查询（续II） • 查询与刘晨在同一个系学习的学生。 • Select Sno, Sname, Sdept From Student Where Sdept = (Select Sdept From Student Where Sname = ‘刘晨’);

29. 嵌套查询（续III） • 查询选修了课程名为‘信息系统’的学生的学号和姓名。 Select Sno, Sname From Student Where Sno IN (Select Sno From SC Where Cno IN (Select Cno From Course Where Cname = ‘信息系统’));

30. 嵌套查询（续IV） • 查询其他系中比信息系中某一学生年龄小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept <> ‘IS’ And Sage < Any(Select Sage From Student Where Sdept = ‘IS’)

31. 嵌套查询（续V） • 查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄。 Select Sname, Sage From Student Where Sdept <> ‘IS’ And Sage < All(Select Sage From Student Where Sdept = ‘IS’)

32. 嵌套查询（续VI） • 查询选择了1号课程的学生姓名。 Select Sname From Student Where Exists (Select * from SC Where Sno=Student.Sno And Cno=‘1’) • 查询没有选择1号课程的学生姓名。 Select Sname From Student Where Not Exists (Select * from SC Where Sno=Student.Sno And Cno=‘1’)

33. 嵌套查询（续VII） • 查询选修了全部课程的学生姓名 Select Sname From Student Where Not Exists (Select * from Course Where Not Exists (Select * from SC Where Sno=Student.Sno And Cno=Course.Cno));

34. 集合查询 • 查询选修了1号或2号课程的学生 Select Sno From SC Where Cno=‘1’ Union Select Sno From SC Where Cno=‘2’

35. 集合查询（续I） • 查询选修了1号课程的学生和选修了2号课程的学生的交集 Select Sno From SC Where Cno=‘1’ And Sno In (Select Sno From SC Where Cno=‘2’)

36. 集合查询（续II） • 查询计算机系的学生与年龄不大于19岁的学生的差集 Select * from Student Where Sdept = ‘CS’ And Sage > 19;

37. SQL数据更新功能 • Insert语句 • Update语句 • Delete语句

38. Insert语句 • 插入数据 Insert Into <表名>[(<属性列>[{,<属性列>}])] Values(<值>[{,<值>}]) • 插入子查询结果 Insert Into <表名>[(<属性列>[{,<属性列>}])] <子查询>

39. Insert语句（续I） Insert Into Student Values (‘95020’, ‘陈冬’, ‘IS’, 18); Insert Into SC (Sno, Cno) Values(‘95020’, ‘1’); Insert Into Dept_Age (Sdept, Avgage)Select Sdept, AVG(Sage) From Student Group By Sdept;

40. Update语句 • 修改数据 Update <表名> Set <列名>=<表达式>[{, <列名>=<表达式>}] [Where <条件>]

41. Update语句（续I） • 将学生95001的年龄改为22岁 Update Student Set Sage = 22 Where Sno=‘95001’ • 将所有学生的年龄增加1岁 Update Student Set Sage=Sage+1

42. Update语句（续II） • 将计算机系全体学生的成绩置零。 Update SC Set Grade = 0 Where ‘CS’ = (Select Sdept From Student Where Student.Sno = SC.Sno)

43. Delete语句 • 删除数据 Delete From <表名> [Where <条件>]

44. Delete语句（续I） • 删除学号为95019的学生的记录 Delete From Student Where Sno=‘95019’ • 删除所有学生的选课记录 Delete From SC

45. Delete语句（续II） • 删除计算机系所有学生的选课记录 Delete From SC Where ‘CS’ = (Select Sdept From Student Where Student.Sno = SC.Sno)

46. SQL数据定义功能 • 定义、删除、修改基本表 • 定义、删除索引 • 定义、删除视图

47. SQL数据定义功能（续I）

48. 定义、删除、修改基本表 • 定义基本表 Create Table <表名> (<列名><数据类型>[<列级完整性约束>] [{,<列名><数据类型>[<列级完整性约束>]}] [{, [<表级完整性约束>]}]

49. 定义、删除、修改基本表（续I） • SQL92的数据类型 • char（n）：固定长度的字符串。 • varchar（n）：可变长字符串。 • int：整数。 • smallint：小整数类型。 • numeric（p，d）：定点数共p位，小数点右边q位。 • Real, double precision ：浮点数与双精度浮点数,精度与机器有关。 • Float(n)：n位的精度浮点数。 • date：日期（年、月、日）。 • time：时间（小时、分、秒）。 • interval：两个date或time类型数据之间的差

50. 定义、删除、修改基本表（续II） • 完整性约束 • NULL/NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK