CMU SSD7: Database Systems

1. CMU SSD7: Database Systems Lu Wei School of Software Northwestern Polytechnical University

2. Unit2.1 关系数据库标准语言 • 第一节 SQL概貌及特点 • 第二节 数据定义 • 介绍postgres数据库系统安装和使用 • 第三节 查询 • 第四节 数据更新 • 第五节 视图 • 第六节 SQL的数据控制功能 • 第七节 事务 Lu Wei

3. 第一节 SQL概貌及特点 • 1.SQL简介 • 2.SQL特点 • 3.SQL语言的基本概念 Lu Wei

4. 1.SQL简介 结构化查询语言SQL（Structured Query Language）是一种介于关系代数与关系演算之间的语言，其功能包括查询、操纵、定义和控制四个方面，是一个通用的、功能极强的关系数据库语言。目前已成为关系数据库的标准语言。 SQL语言的版本包括：SQL-89，SQL-92，SQL3（ SQL-99 ）。 Lu Wei

5. 2.SQL特点 SQL语言之所以能够为用户和业界所接受，成为国际标准，是因为它是一个综合的、通用的、功能极强同时又简洁易学的语言。SQL语言集数据查询（data query）、数据操纵（data manipulation）、数据定义（data definition）和数据控制（data control）功能于一体，充分体现了关系数据语言的特点和优点。 其主要特点包括： Lu Wei

6. 1.综合统一 SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求，这就为数据库应用系统开发提供了良好的环境，例如用户在数据库投入运行后，还可根据需要随时地逐步地修改模式，并不影响数据库的运行，从而使系统具有良好的可扩充性。 Lu Wei

7. 2.高度非过程化 非关系数据模型的数据操纵语言是面向过程的语言，用其完成某项请求，必须指定存取路径。而用SQL语言进行数据操作，用户只需提出“做什么”，而不必指明“怎么做”，因此用户无需了解存取路径，存取路径的选择以及SQL语句的操作过程由系统自动完成。这不但大大减轻了用户负担，而且有利于提高数据独立性。 Lu Wei

8. 3.面向集合的操作方式 SQL语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 非关系数据模型采用的是面向记录的操作方式，任何一个操作其对象都是一条记录。例如查询所有平均成绩在80分以上的学生姓名，用户必须说明完成该请求的具体处理过程，即如何用循环结构按照某条路径一条一条地把满足条件的学生记录读出来。 Lu Wei

9. 4.以同一种语法结构提供两种使用方式 SQL语言既是自含式语言，又是嵌入式语言。 作为自含式语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作。作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序员设计程序时使用。而在两种不同的使用方式下，SQL语言的语法结构基本上是一致的。这种以统一的语法结构提供两种不同的使用方式的作法，为用户提供了极大的灵活性与方便性。 Lu Wei

10. 5.语言简洁，易学易用 SQL语言功能极强，但由于设计巧妙，语言十分简洁，完成数据定义、数据操纵、数据控制的核心功能只用了9个动词：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE，如表3-1所示。而且SQL语言语法简单，接近英语口语，因此容易学习，容易使用。 Lu Wei

11. SQL语言的动词 Lu Wei

12. 3.SQL语言的基本概念 SQL语言支持关系数据库三级模式结构，如下页图所示。其中： 外模式对应于视图（View）和部分基本表（Base Table）； 模式对应于基本表； 内模式对应于存储文件 Lu Wei

13. SQL用户 用户1 用户2 用户3 用户4 VIEW 视图V1 视图V2 Base Table 基本表B1 基本表B2 基本表B3 基本表B4 存储文件S1 存储文件S2 存储文件S3 存储文件S4 Stored file SQL对关系数据库的支持 Lu Wei

14. 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。一些基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。 　　 • 存储文件的逻辑结构组成了关系数据库的内模式。存储文件的物理文件结构是任意的。 • 视图是从基本表或其他视图中导出的表，它本身不独立存储在数据库中，也就是说数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表。 用户可以用SQL语言对视图和基本表进行查询。在用户眼中，视图和基本表都是关系，而存储文件对用户是透明的。 Lu Wei

15. 第二节 数据定义 • 1.SQL数据类型 • 2.域 • 3.完整性约束 • 4.创建删除数据库 • 5.基本表 • 6.索引 Lu Wei

16. 1.SQL数据类型 1.SQL标识符（注意不能和内部标识符冲突） 2.SQL标量数据类型 3.标量运算符 Lu Wei

17. 2.域 定义域的一般格式为： CREATE DOMAIN DomainName [AS] dataType [DEFULT defaultOption] [CHECK searchCondition] 删除域的一般格式为： DROP DOMAIN DomainName [RESTRICT|CASCADE] Lu Wei

18. 【例】 CREATE DOMAIN SexType AS CHAR DEFAULT ‘M’ CHECK(VALUE IN(‘M’,’F’)); sex SexType NOT NULL Lu Wei

19. 3.完整性约束 1.实体完整性（通过主键和唯一键实现） PRIMARY KEY(columnName) columnName dataType NOT NULL UNIQUE(columnName) 2.引用完整性（通过外键实现） FOREIGN KEY(columnName) REFERENCES tableName.columnName ON [UPDATE|DELETE] [CASCADE|SET NULL|SET DEFAULT|NO ACTION] Lu Wei

20. 3.用户定义完整性（通过检查约束实现） CHECK(condition) 【例】 CHECK (Salary >= 0) CREATE ASSERTION AssertionName CHECK(condition) Lu Wei

21. 4.创建删除数据库 CREATE SCHEMA [Name| AUTHORIZATION CreatorIdentifier] DROP SCHMA Name [RESTRICT|CASCADE] The Restrict option removes the schema if the database has no data in it, that is, if all tables are empty. The Cascade option removes everything including data (tuples) and definitions (tables, domains, etc). Lu Wei

22. 5.基本表 定义基本表一般格式如下： CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件] [，<列名> <数据类型> [列级完整性约束条件]...) [,<表级完整性约束条件>]; Lu Wei

23. 其中<表名>是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。其中<表名>是所要定义的基本表的名字，它可以由一个或多个属性（列）组成。 建表的同时通常还可以定义与该表有关的完整性约束条件，这些完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由DBMS自动检查该操作是否违背这些完整性约束条件。 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。 Lu Wei

24. 下面我们以一个“学生－课程”数据库为例说明SELECT语句的各种用法。下面我们以一个“学生－课程”数据库为例说明SELECT语句的各种用法。 “学生－课程”数据库中包括三个表： （1）“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno （2）“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno Lu Wei

25. （3）“学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO) 例1 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。 Lu Wei

26. CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15)); 定义表的各个属性时需要指明其数据类型及长度。不同的数据库系统支持的数据类型不完全相同。 Lu Wei

27. 修改基本表一般格式为： ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>] [MODIFY<列名> <数据类型>]; 其中<表名>指定需要修改的基本表； ADD子句用于增加新列和新的完整性约束条件； DROP子句用于删除指定的完整性约束条件； MODIFY子句用于修改原有的列定义。 Lu Wei

28. 例2向Student表增加“入学时间”列，其数据类型为日期型例2向Student表增加“入学时间”列，其数据类型为日期型 不论基本表中原来是否已有数据，新增加的列一律为空值。 例3将年龄的数据类型改为半字长整数 修改原有的列定义有可能会破坏已有数据。 例4删除关于学号必须取唯一值的约束 ALTER TABLE Student ADD Scome DATE; ALTER TABLE Student MODIFY Sage SMALLINT; ALTER TABLE Student DROP UNIQUE(Sno); Lu Wei

29. 删除基本表一般格式为： DROP TABLE <表名>[RESTRICT|CASCADE] With the CASCADE option, the table and all references to it are removed. With the RESTRICT option, the table is removed if it is not referenced. 例5删除Student表 基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。因此执行删除操作一定要格外小心。 DROP TABLEStudent; Lu Wei

30. 6.索引 创建索引一般格式为：CREATE [UNIQUE] [CLUSTER] INDEX <索引名><索引名> ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...); 其中，<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC（升序）和DESC（降序）两种，缺省值为ASC。 Lu Wei

31. UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。例如，执行下面的CREATE INDEX语句： 将会在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sno值的升序存放。用户可以在最常查询的列上建立聚簇索引以提高查询效率。显然在一个基本表上最多只能建立一个聚集索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。 CREATE CLUSTED INDEX Stusname ON Student(Sname); Lu Wei

32. 例6为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。例6为学生-课程数据库中的Student、Couse、SC三个表建立索引。其中Student表按学号升序建唯一索引，Couse表按课程号升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON Student(Sno); CREATE UNIQUE INDEX Coucno ON Couse(Cno); CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); Lu Wei

33. 删除索引一般格式为：DROP INDEX<索引名>; 例7删除Student表的Stusname索引 索引一经建立，就由系统使用和维护它，不需用户干预。建立索引是为了减少查询操作的时间，但如果数据增删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。删除索引时，系统会同时从数据字典中删去有关该索引的描述。 DROP INDEX Stusname; Lu Wei

34. 介绍postgres数据库系统安装和使用 Lu Wei

35. 第三节 查询 一般格式为： SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]... FROM <表名或视图名>[,<表名或视图名>] ... [WHERE <条件表达式>] [GROUP BY <列名1>[HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]]; Lu Wei

36. SELECTA1 , A2 , ..., An FROMR1,R2,…,Rm WHERE F  πA1,A2,...,An(σF(R1×R2×...×Rm)) Lu Wei

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

38. 1.单表查询 2.连接查询 3.嵌套查询 4.集合查询 Lu Wei

39. 1.单表查询 一、选择表中的若干列 1.查询指定列 例1查询全体学生的学号与姓名 例2查询全体学生的姓名、学号、所在系 SELECT Sno,Sname FROM Student; SELECT Sname, Sno, Sdept FROM Student; Lu Wei

40. 2.查询全部列 例3查询全体学生的详细记录 3.查询经过计算的值 SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是有关表达式，即可以将查询出来的属性列经过一定的计算后列出结果。 例4查全体学生的姓名及其出生年份 SELECT * FROM Student; SELECT Sname, 2006-Sage FROM Student; Lu Wei

41. 例5查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名例5查全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名 SELECT Sname, 'Year of Birth:‘, 2006- Sage, ISLOWER(Sdept) FROM Student; SELECT Sname NAME, 'Year of Birth:‘ BIRTH,2006-Sage BIRTHDAY,ISLOWER(Sdept) DEPARTMENT FROM Student; Lu Wei

42. 二、选择表中的若干元组 1.消除取值重复的行 例6查所有选修过课的学生的学号 SELECT Sno FROM SC; SELECT DISTINCT Sno FROM SC; Lu Wei

43. 2.查询满足条件的元组 查询满足指定条件的元组可以通过WHERE子句实现。WHERE子句常用的查询条件如表3-3所示。 表3-3 常用的查询条件查询条件 谓 词 比较 比较运算符 确定范围 BETWEEN AND, NOT BETWEEN AND 确定集合 IN, NOT IN 字符匹配 LIKE, NOT LIKE 空值 IS NULL, IS NOT NULL 多重条件 AND, OR Lu Wei

44. (1)比较 例7 查计算机系全体学生的名单 例8 查所有年龄在20岁以下的学生姓名及其年龄 SELECT Sname FROM Student WHERE Sdept = 'CS'; SELECT Sname, Sage FROM Student WHERE Sage <20; 或 SELECT Sname, SageFROM Student WHERE NOT Sage>= 20; Lu Wei

45. 例9查考试成绩有不及格的学生的学号 (2)确定范围 例10查询年龄在20至23岁之间的学生的姓名、系别、和年龄 SELECT DISTINCT Sno FROM Course WHERE Grade <60; SELECT Sname, Sdept, SageFROM Student WHERE Sage BETWEEN 20 AND 23; Lu Wei

46. 例11 查询年龄不在20至23岁之间的学生姓名、系别和年龄。 (3)确定集合 例12查信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别 SELECT Sname, Sdept, Sage FROM StudentWHERE Sage NOT BETWEEN 20 AND 23; SELECT Sname, Ssex FROM Student WHERE Sdept IN ('IS', 'MA', 'CS'); Lu Wei

47. 例13查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别例13查既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别 (4)字符匹配 谓词LIKE可以用来进行字符串的匹配。其一般语法格式如下： [NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]  其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。 SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('IS', 'MA', 'CS') Lu Wei

48. %(百分号) 代表任意长度（长度可以为0）的字符串。_(下横线) 代表任意单个字符。 例15查所有姓刘的学生的姓名、学号和性别 例16查姓“欧阳”且全名为三个汉字的学生的姓名 SELECT Sname, Sno, SsexFROM Student WHERE Sname LIKE ‘刘%’; SELECT Sname FROM Student WHERE Sname LIKE '欧阳__'; Lu Wei

49. 例17查名字中第二字为“阳”字的学生的姓名和学号例17查名字中第二字为“阳”字的学生的姓名和学号 例18查所以不姓刘的学生姓名 SELECT Sname, Sno FROM Student WHERE Sname LIKE ‘__阳%’; SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%'; Lu Wei

50. 例19查DB_Design课程的课程号和学分 例20查以”DB_”开头，且倒数第三个字符为i的课程的详细情况 SELECT Cno, Ccredit FROM Course WHERE Cname LIKE ’DB$_Design’ ESCAPE ’$’; SELECT * FROM Course WHERE Cname LIKE ’DB\_%i__’ ESCAPE ’\’; Lu Wei