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

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

2. 本章主要内容 • SQL概述 • SQL数据定义 • SQL数据查询 • SQL数据更新 • 视图 • SQL数据控制 • Transat-SQL

3. §3.1 SQL概述 • SQL语言是1974年由Boyce和Chamberlin提出的，1975年至1979年IBM公司的System R系统实现了这种语言。 • 第一个SQL标准是1986年10 月由ANSI公布的，1987 年OSI通过了这一标准。 • 自SQL成为国际标准语言后，各个数据库厂家纷纷推出各自支持的SQL软件，SQL已成为关系数据库领域中的一个主流语言。

4. §3.1 SQL概述 • SQL的特点： • 综合统一 • 高度非过程化 • 面向集合的操作方式 • 以同一种语法结构提供两种使用方式 • 语言简洁，易学易用

5. SQL功能 SQL命令 数据定义 CREATE， ALTER ， DROP 数据更新 INSERT， UPDATE， DELETE 数据查询 SELECT 数据控制 GRANT， REVOKE §3.1 SQL概述

6. §3.1 SQL概述 • SQL语言支持三级模式结构（图3-1） 用户 用户 应用系统2 应用系统1 视图1 视图2 外模式 模式 存储模式 基本表1 基本表2 基本表3 基本表4 存储文件1 存储文件2

7. §3.1 SQL概述 • 基本表是本身独立存在的表，在 SQL 中一个关系对应一个表，一些基本表对应一个存储文件，一个基本表可以带若干索引，索引存放在存储文件中。 • 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中。

8. §3.1 SQL概述 • 所有的基本表构成了系统的模式，一个数据库系统只有一个模式。 • 所有的存储文件和索引构成了系统的内模式。一个数据库 系统只有一个内模式。 • 一些视图的集合或一些视图和一些基本表的集合构成了系统的外模式，一个数据库系统有多个外模式，其中每一个外模式对应一个应用。

9. §3. 2 数据定义 • 关系数据库的基本对象是表、视图和索引，因此SQL的数据定义功能包括定义表、定义视图和定义索引。 • 由于视图是基于基本表的虚表，索引是依附于基本表的，因此SQL一般不提供修改实体定义和修改索引定义的操作。用户如果想修改视图定义或索引定义，只能先将他们删除掉，然后再重建。

10. §3. 2 数据定义---定义基本表 • CREATE TABLE <表名> • （ <列名> <数据类型> [<列级完整性约束条件>]） • [， <列名> <数据类型> [<列级完整性约束条件>]. . . ] • [，<表级完整性约束条件>] ）

11. §3. 2 数据定义--数据类型 SMALLINT 半字长二进制整数 INTEGER或INT 全字长二进制整数 DECIMAL(p[,q])或DEC (p[,q]) 压缩十进制数，共p位，其中小数 点后有q位 0<q<p<15, q=0时可以省略 FLOAT 双字长浮点数 CHARACTER(n)或CHAR(n) 长度为n的定长字符串 VARCHAR(n) 最大长度为n的变长字符串 GRAPHIC(n) 长度为n的定长图形字符串 VARGRAPHIC(n) 最大长度为n的变长图形字符串 DATE 日期型，格式为YYYY-MM-DD TIME 时间型，格式为HH.MM.SS TIMESTAMP 日期加时间 • 定义表的各个属性时，需要指明其长度和类型，例如 IBM DB2主要支持如下数据类型。

12. §3. 2 数据定义----数据类型 SQL Server 2000中的数据类型：

13. §3. 2 数据定义---定义基本表 • 例1：定义一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept 5个属性组成，其中学号属性为主属性，学生的姓名非空且是唯一的，性别默认为‘男’, 并且学生的年龄在16岁和70 岁之间。 CREATE TABLE Student （Sno char（5）， Snane char（20） NOT NULL UNIQUE ， Ssex char（2） default ‘男’, Sage tinyint CONSTRAINT C3 CHECK （Sage >=16 and Sage<=70), Sdept char（2）, CONSTRAINT PK_Sno PRIMARY KEY（Sno）)；

14. §3. 2 数据定义---定义基本表 例2：定义一个课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、 学分Credit 4个属性组成，其中Cno属性为主码, 课程名是唯一的。 • CREATE TABLE Course • （Cno char（2）， • Cname char（2）Unique, • cpno char(2)， • Credit tinyint, • CONSTRAINT PK-Cno PRIMARY KEY（Cno） ）；

15. §3. 2 数据定义---定义基本表 例3： 定义一个学生选课表SC，它由学号Sno、课程号 Cno、成绩Grade 3个属性组成，其中Sno、Cno属性组为主码, Sno为外码, 参照关系为Student,Cno 为外码, 参照关系为Course. CREATE TABLE SC （Sno char（5）， Cno char（2）, Grade tinyint（2）， CONSTRAINT PK-SC PRIMARY KEY（Sno,Cno）, CONSTRAINT FK-Sno FOREIGN KEY（Sno） REFERENCES Student（Sno）, CONSTRAINT FK-Cno FOREIGN KEY（Cno） REFERENCES Course（Cno） ）；

16. §3. 2 数据定义---修改基本表 • 修改基本表 ALTER TABLE <表名> [ADD <新列名> <数据类型> [完整性约束]] [DROP <完整性约束名>] [MODIFY <列名><数据类型>]; 注意：SQL没有提供删除属性列的语句，用户只能间接实现这一功能，即先将原表中要保留的列及其内容复制到一个新表中，然后删除原表，并将新表重命名为原表名

17. §3. 2 数据定义---修改基本表 例4：向Student 表增加“入学时间”列，其数据类型为日期型 ALTER TABLE Student ADD Scome DATE; 例5：将年龄的数据类型改为半字长整数 ALTER TABLE Student ALTER COLUMN Sage SMALLINT; 例6：删除学号必须取唯一值的约束 ALTER TABLE Student DROP UNIQUE(Sno);

18. §3. 2 数据定义---删除基本表 DROP TABLE <表名>; 例7：删除基本表 Student. DROP TABLE Student ;

19. §3. 2数据定义---定义索引 CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名>[<次序>][,<列名>[<次序>]]…); 其中：Unique 表示此索引的每一个索引值只对应唯一的数据记录。 Cluster表示要建立的索引是聚簇索引。 表名：要创建索引的基本表的名字。 索引可以建立在该表的一列或多列上，各列名之间用都好分隔。 每个列名后面还可以用次序指定索引值的排列次序，包括ASC 和、DESC两种，默认值为ASC.

20. §3. 2 数据定义---定义索引 • 例8：为 Student, Course, SC三个表建立索引。其中 Student • 表按学号升序建立唯一聚簇索引，Course 表按课程号升序建立唯一索引，SC表按学号升序和课程号降序建立唯一索引。 • CREATE Unique Cluster INDEX Stud ON Student (Sno); • CREATE Unique INDEX Cnoc ON Course(Cno); • CREATE Unique INDEX ON SC (Sno ASC,Cno DESC);

21. §3. 2 数据定义----删除索引 • 索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作时间，但如果数据增删改频繁。系统会花费许多时间来维护索引。这时可以删除一些不必要的索引。 • 删除索引语句格式： • DROP INDEX <索引名> • 例9：删除Student表的Stusno索引。 • DROP INDEX Stusno;

22. 3.3 数据查询 • 建立数据库的目的是为了查询数据，因此说数据查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语言具有语言灵活的使用方式和丰富的功能。 • 查询语句一般格式为： • SELECT [ALL |DISTINCT]<目标列表达式>[,<目标列表达式>]… • FROM<表名或视图名>[,<表名或视图名>]… • [WHERE<条件表达式>] • [GROUP BY <列名1>[HAVING<条件表达式>]] • [ORDER BY<列名2>[ASC|DESC]];

23. 3.3 数据查询 • 整个SELECT语句的含义是，根据WHERE子句的表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。 • 如果有GROUP子句，则将结果按<列名1>的值进行分组,该属性列值相等的元组为一个组,每个组产生结果表中的一条记录. • 通常会在每组中作用集函数. • 如果GROUP子句带HAVING短语,则只有满足指定条件的组才予输出. • 如果有ORDER子句,则结果表还要按<列名2>的值的升序或降序排序.

24. “学生-课程”数据库中包括三个表 • 1. “学生”表Student由学号(Sno),姓名(Sname),性别 • (Ssex),年龄(Sage),所在系(Sdept)5个属性组成,可记为 • Student(Sno,Sname,Sage,Sdept). 其中Sno为主码. • 2. “课程”表Course由课程号(Cno),课程名(Cname),先修课号(Cpno),学分(Ccredit)4个属性组成,可记为: • Course(Cno,Cname,Cpno, Ccredit). 其中Cno为主码. • 3. “学生选课”表SC由学号(Sno),课程号(Cno), • 成绩(Grade)3个属性组成,可记为: • SC (Sno,Cno,Grade) • 其中(Sno,Cno)为主码.

25. Student SC Course

26. 3.3.1 单表查询 • 单表查询是指仅涉及一个数据库表的查询,比如选择一个表中的某些列值,选择一个表中的某些特定行等.单表查询是一种最简单的查询操作. • 一. 选择表中的若干列 • 选择表中的全部列或部分列,这类运算又称为投影.其变化方式主要表现在SELECT子句的<目标列表达式>上. • 1) 查询指定列 • 在很多情况下,用户只对表中的一部分属性列感兴趣,这时可以通过在SELECT子句的<目标列>中指定要查询的属性,有选择地列出感兴趣的列.

27. 3.3.1 单表查询 • 例1:查询全体 学生的学号和姓名. • SELECT Sno,Sname • FROM Student; • 例2:查询全体学生的姓名,学号,所在系. • SELECT Sno,Sname,Sdept • FROM Student;

28. 2) 查询全部列 • 将表中的所有属性列都选出来,可以有两种方法.一种 • 方法就是在SELECT关键字后面列出所有列名.如果列的 • 显示顺序与其在基表中的顺序相同,也可以简单的将 • <目标列表达式>指定为*.

29. 例3 查询全体学生的详细记录 • SELECT * • FROM Student; • 该SELECT语句实际上是无条件地把Student 表的全 • 部信息都查询出来, 所以也称为全表查询, 这是最简单 • 的一种查询.

30. 3) 查询经过计算的值 • SELECT子句的 <目标列表达式> 不仅可以是表中的 • 属性列,也可以是有关系表达式,即可以将查询出来的属性列经 • 过一定的计算后列出结果.

31. 例4 查询全体学生的姓名及其出生年份 • SELECT Sname, 2008-Sage • FROM Student; • 本例中,<目标列表达式>中第二项不是通常的 • 列名,而是一个计算表达式, 是用当前的年份 • (假设为2008年)减去学生的年龄,这样,所得的 • 即是学生的出生年份.

32. 输出的结果为: • Sname 2008—Sage • ……… …………… • 李勇 1988 • 刘晨 1989 • 王名 1990 • 张立 1989 • <目标列表达式>不仅可以是算术表达式, 还可以是字 • 符串常量,函数等.

33. 例5 查询全体学生的姓名,出生年份和所有系,要求用小写字母表示所有系名 • SELECT Sname ,`Year of Birth` ,2008-Sage, • LOWER(Sdept) • FROM Student ;

34. 结果为: • Sname ‘Year of Birth:’ 2003-Sage ISLOWER(Sdept) • ………. ……………… …………. …………………. • 李勇 Year of Birth: 1988 cs • 刘晨 Year of Birth: 1989 is • 王名 Year of Birth: 1990 ma • 张立 Year of Birth: 1989 is

35. 用户可以通过指定列名来改变查询结果标题。例如, 对于上例,可以如下定义列别名 • SELECT Sname NAME, ‘Year of Birth:’ BIRTH, • 2003-Sage BIRTHDAY, • LOWER(Sdept) DEPARTMENT • FROM Student;

36. 结果为: • NAME BIRTH BIRTHDAY DEPARTMENT • ……….. …..….. ….………… …..…………. • 李勇 Year of Birth: 1988 cs • 刘晨 Year of Birth: 1989 is • 王名 Year of Birth: 1990 ma • 张立 Year of Birth: 1989 is

37. 二、选择表中的若干元组 • 通过<目标列表达式>的各种变幻,可以根据实际需要,从一 • 个指定的表中选择出所有元组的全部或部分列.如果只 • 想选择部分元组或部分列,则还需要指定DISTINCT短 • 语或指定WHERE子句.

38. 1) 消除取值重复的行 • 两个本来并不完全相同的元组,投影到指定 • 的某些列上后,可能变成完全相同的行了.

39. 例6 查询所有选修过课的学生的学号 • SELECT Sno • FROM SC; 执行上面的SELECT语句后,结果为: 假设SC表中有如下数据 Sno ……. 95001 95001 95001 95002 95002 Sno Cno Grade …… …… ……. 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80

40. 该查询结果里包含了许多重复的行.如果想去掉结果表中的重复行,必须指定 DISTINCT短语: • SELECT DISTINCT Sno • FROM SC; 执行结果为: Sno 95001 95002

41. 如果没有指定DISTINCT短语,则缺省ALL,即要求结果表中保留取值重复的行.也就是说:如果没有指定DISTINCT短语,则缺省ALL,即要求结果表中保留取值重复的行.也就是说: • SELECT Sno • FROM SC; • 与 • SELECT ALL Sno • FROM SC; • 完全等价.

42. 查询满足条件的元组 • 查询满足指定条件的元组可以通过 WHERE子句 • 实现.WHERE子句常用的查询条件如表3-4所示.

43. 表3.4 常用的查询条件

44. (1) 比较大小 • 用于进行比较的运算符一般包括： • = 等于 • > 大于 • < 小于 • <= 小于等于 • >= 大于等于 • !=或<> 不等于 • 有些产品中包括: • !> 不大于 • !< 不小于 • 逻辑运算符NOT可与比较运算符同用

45. 例7：查询计算机科学系全体学生的名单 • Select Sname • From Student • Where Sdept=‘CS’; • 例8：查询所有年龄在20岁以下的学生姓名及其年龄 • Select Sname, Sage • From Student • Where Sage<20;

46. 例9：查询考试成绩有不及格的学生的学号 • Select Distinct Sno • From SC • Where Grade>=60

47. (2) 确定范围 • 谓词Between…. And …和 Not Between …And …可以用来查找属性值在（或不在）指定范围内的元组 • 其中 Between后是范围的下限，and 后是范围的上限

48. 例10. 查询年龄在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 • Select Sname，Sdept, Sage • From Student • Where Sage Between 20 and 23; • 例11.查询年龄不在20～23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 • Select Sname，Sdept, Sage • From Student • Where Sage not Between 20 and 23

49. (3) 确定集合 • 谓词IN可以用来查找属性值属于指定集合的元组 • 例12：查询计算机科学系（CS），数学系（MA）和信息系（IS）学生的姓名和性别 • Select Sname, Ssex • From Student • Where Sdept in (‘CS’,’MA’,’IS’);

50. 例13. 查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别 • Select Sname，Ssex • From Student • Where Sdept not in (‘CS’,’MA’,’IS’);