第5-1章 关系数据库标准语言-SQL

1、江苏科技大学数据库系统原理及应用第五章 关系数据库标准语言SQL2022/9/21An Introduction to Database System第五章 关系数据库标准语言SQL5.1 SQL的功能及特点5.2 数据定义语句5.3 数据查询语句5.4 数据更新语句5.5 嵌入式SQL5.6 数据控制机制和语句2022/9/21An Introduction to Database System5.1 SQL的功能及特点SQL（Structured Query Language） 结构化查询语言，是关系数据库的标准语言SQL是一个通用的、功能极强的关系数据库语言2022/9/21An Int

2、roduction to Database System5.1 SQL的功能及特点 标准 大致页数 发布日期SQL/86 1986.10SQL/89(FIPS 127-1) 120页 1989年SQL/92 622页 1992年SQL99 1700页 1999年SQL2003 2003年SQL标准的进展过程：2022/9/21An Introduction to Database System5.1 SQL的功能及特点SQL语句分类（功能分类）SQL的特点2022/9/21An Introduction to Database System5.1 SQL的功能及特点SQL语句分类（1）数据定义

3、语句 DDL 定义关系数据库的模式、外模式和内模式，以实现对基本表、视图以及索引文件的定义、修改和删除等操作。（2）数据操纵语句 DML 包括数据查询和数据更新两种数据操作语句：数据查询指对数据库中的数据查询、统计、分组、排序操作；数据更新指数据的插入、删除、修改等数据维护操作。（3）数据控制语句 DCL通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。2022/9/21An Introduction to Database System5.1 SQL的功能及特点SQL语句分类SQL的特点2022/9/21An Introduction to Database S

4、ystem5.1 SQL的功能及特点SQL的特点1、两种使用方式2、语言简单，易学易用（九个动词）3、支持三级模式2022/9/21An Introduction to Database System5.1 SQL的功能及特点1、两种使用方式：SQL是独立的语言 能够独立地用于联机交互的使用方式SQL又是嵌入式语言 SQL能够嵌入到高级语言程序中2022/9/21An Introduction to Database System5.1 SQL的功能及特点2、语言简单，易学易用完成核心功能只用了9个动词。2022/9/21An Introduction to Database System5.

5、1 SQL的功能及特点SQL视图2视图1基本表2基本表1基本表3基本表4存储文件2存储文件1外模式模 式内模式3、支持三级模式结构2022/9/21An Introduction to Database System5.1 SQL的功能及特点基本表本身独立存在的表SQL中一个关系就对应一个基本表一个(或多个)基本表对应一个存储文件一个表可以带若干索引存储文件逻辑结构组成了关系数据库的内模式物理结构是任意的，对用户透明视图从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表用户可以在视图上再定义视图2022/9/21An Introduction to Data

6、base System第五章 关系数据库标准语言SQL5.1 SQL的功能及特点5.2 数据定义语句5.3 数据查询语句5.4 数据更新语句5.5 嵌入式SQL5.6 数据控制机制和语句2022/9/21An Introduction to Database System5.2 数据库定义语言举例，学生课程数据库S_T（教材P119）: 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno) 学生选课表：SC(Sno,Cno,Grade) 2022/9/21An Introduction to Database Sys

7、tem5.2 数据库定义语言SQL的数据定义功能: 数据库定义、表定义、视图和索引的定义 注意：P117 ,约定符号和语法规定的相关说明2022/9/21An Introduction to Database System5.2 数据库定义语言5.2.1数据库的定义和维护（补充部分）5.2.2基本表的定义和维护5.2.3索引的定义和维护5.2.4视图的定义和维护（这部分在5.4后讲）2022/9/21An Introduction to Database System5.2.1数据库的定义和维护1、建立数据库 例1 CREATE DATABASE S_T； 2、删除数据库 例2 DROP DA

8、TABASE S_T ； 2022/9/21An Introduction to Database System5.2.1数据库的定义和维护定义模式实际上定义了一个命名空间在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。2022/9/21An Introduction to Database System5.2 数据库定义语言5.2.1数据库的定义和维护（补充部分）5.2.2基本表的定义和维护5.2.3索引的定义和维护5.2.4视图的定义和维护2022/9/21An Introduction to Database System5.2.2基本表的定义和维护1、基本表的定义

9、2、基本表的修改3、基本表的删除2022/9/21An Introduction to Database System5.2.2基本表的定义和维护1、定义基本表一般格式如下：CREATE TABLE （ ， ， ）；2022/9/21An Introduction to Database System定义基本表（续）数据类型：需要标明长度类型表示类型说明数值型数据SMALLINT半字长二进制整数。15bits数据INTEGER或INT全字长（四字长）整数。31bits数据DECIMAL(p，q)十进制数，共p位，其中小数点后q位。0qp，q=0时可省略不写FLOAT双字长浮点数字符型数据CHA

10、RTER(n)或CHAR(n)长度为n的定长字符串VARCHAR(n)最大长度为n的变长字符串特殊数据类型GRAPHIC(n)长度为n的定长图形字符串VARGRAPHIC(n)最大长度为n的变长图形字符串日期时间型DATE日期型，格式为YYYY-MM-DDTIME时间型，格式为HH.MM.SSTIMESTAMP日期加时间2022/9/21An Introduction to Database System定义基本表（续）完整性约束类型：1) NOT NULL或NULL约束。NOT NULL约束不允许字段值为空，而NULL约束允许字段值为空。2) UNIQUE约束。惟一性约束3) DEFAULT

11、约束。默认值约束DEFAULT （默认值） FOR （列名）4) CHECK约束。检查约束。CHECK（约束条件）5) PRIMARY KEY约束。定义主码，保证惟一性和非空性。6) FOREIGN KEY约束。用于定义参照完整性。FOREIGN KEY (外码) REFERENCES 被参照表名(与外码对应的主码名)2022/9/21An Introduction to Database System定义基本表（续）完整性约束定义方法：表级完整性约束：完整性约束条件涉及到该表的多个属性列，则必须定义在表级上；列级完整性约束完整性约束条件涉及到该表的单个属性列，则即可以定义在列级，也可以定义在

12、表级上；CONSTRAINT 约束；2022/9/21An Introduction to Database System定义基本表（续）例5-3 建立学生_课程数据库中的表。 CREATE TABLE 学生(学号 CHAR(5) NOT NULL UNIQUE，姓名 CHAR(8) NOT NULL，年龄 SMALLINT，性别 CHAR(2)，所在系 CHAR(20)，DEFAULT C1 20 FOR 年龄，CONSTRAINT C2 CHECK(性别 IN (男，女)；CONSTRAINT C3 PRIMARY KEY(课程号)PRIMARY KEY(课程号)PRIMARY KEY20

13、22/9/21An Introduction to Database System定义基本表（续）CREATE TABLE 课程(课程号 CHAR(5) PRIMARY KEY，课程名 CHAR(20)，先行课 CHAR(5)；CONSTRAINT C1 FOREIGN KEY(先行课) REFERENCES 课程(课程号）2022/9/21An Introduction to Database System定义基本表（续）CREATE TABLE 选课(学号 CHAR(5)，课程号 CHAR(5)， 成绩 SMALLINT，CONSTRAINT C3 CHECK(成绩 BETWEEN 0 A

14、ND 100)CONSTRAINT C4 PRIMARY KEY(学号，课程号)，CONSTRAINT C5 FOREIGN KEY(学号) REFERENCES 学生(学号)，CONSTRAINT C6 FOREIGN KEY(课程号) REFERENCES 课程(课程号)；2022/9/21An Introduction to Database System5.2.2基本表的定义和维护2、基本表的修改一般格式：ALTER TABLE表名 ADD(新列名数据类型完整性约束 ，n) DROP完整性约束名 MODIFY(列名数据类型，n)；2022/9/21An Introduction to

15、Database System修改基本表（续）例5-2 向课程表中增加“学时”字段。 ALTER TABLE 课程 ADD 学时 SMALLINT；不论基本表中原来是否已有数据，新增加的列一律为空值。删除 ：ALTER TABLE 课程 DROP 学时 ； 例5-3删除学生表中对年龄的默认值的定义。 ALTER TABLE 学生 DROP C1；2022/9/21An Introduction to Database System修改基本表（续）补充例1将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。 ALTER TABLE 学生 MODIFY Sage INT；补充例2增加课

16、程名称必须取唯一值的约束条件。 ALTER TABLE 课程 ADD UNIQUE(Cname); 补充例3添加参照完整性条件ALTER TABLE Course 课程 constraint foreign key(Cpno) references course(Cno);2022/9/21An Introduction to Database System5.2.2基本表的定义和维护3、删除基本表 DROP TABLE RESTRICT| CASCADE；RESTRICT：删除表是有限制的。欲删除的基本表不能被其他表的约束所引用如果存在依赖该表的对象，则此表不能被删除CASCADE：删除该表

17、没有限制。在删除基本表的同时，相关的依赖对象一起删除 2022/9/21An Introduction to Database System删除基本表(续) 补充例4 删除Student表 DROP TABLE Student CASCADE ;基本表定义被删除，数据被删除表上建立的索引、视图、触发器等一般也将被删除 2022/9/21An Introduction to Database System删除基本表（续）补充例5若表上建有视图，选择RESTRICT时表不能删除 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student

18、 WHERE Sdept=IS； DROP TABLE Student RESTRICT; -ERROR: cannot drop table Student because other objects depend on it2022/9/21An Introduction to Database System删除基本表（续）序号 标准及主流数据库的处理方式依赖基本表的对象SQL99Kingbase ESORACLE 9iMS SQLSERVER 2000RCRCC1.索引无规定2.视图保留保留保留3.DEFAULT，PRIMARY KEY，CHECK（只含该表的列）NOT NULL 等约束

19、4.Foreign Key5.TRIGGER6.函数或存储过程保留保留保留保留保留DROP TABLE时，SQL99 与 3个RDBMS的处理策略比较R表示RESTRICT , C表示CASCADE 表示不能删除基本表，表示能删除基本表，保留表示删除基本表后，还保留依赖对象 2022/9/21An Introduction to Database System5.2 数据库定义语言5.2.1数据库的定义和维护（补充部分）5.2.2基本表的定义和维护5.2.3索引的定义和维护5.2.4视图的定义和维护2022/9/21An Introduction to Database System5.2.3

20、索引的定义和维护什么是索引？索引文件数据文件2022/9/21An Introduction to Database System5.2.3索引的定义和维护索引的作用1) 使用索引可以明显地加快数据查询速度。2) 使用索引唯一性功能可保证数据的惟一性。3) 使用索引可以加快连接速度。建立索引的原则1) 索引的建立和维护由DBA和DBMS完成。2) 大表应当建索引，小表则不必建索引。3) 对于一个基本表，不要建立过多的索引。4) 根据查询要求建索引。2022/9/21An Introduction to Database System5.2.3索引的定义和维护1、建立索引2、删除索引2022/9

21、/21An Introduction to Database System5.2.3索引的定义和维护1、建立索引一般格式CREATE UNIQUE CLUSTER INDEX ON (, )；说明：UNIQUE：表示该索引的每个索引值只对应唯一的数据记录CLUSTER（聚簇）：表示基本表中数据的物理顺序要与索引项的排列顺序一致。次序：ASC（升序）、DESC（降序）2022/9/21An Introduction to Database System建立索引（续）例在Student表的Sname（姓名）列上建立一个聚簇索引CREATE CLUSTER INDEX 学生姓名ON 学生(姓名)；在

22、最经常查询的列上建立聚簇索引以提高查询效率 一个基本表上最多只能建立一个聚簇索引 经常更新的列不宜建立聚簇索引 2022/9/21An Introduction to Database System建立索引（续） 例5-4为学生_课程数据库中的学生、课程和选课三个表建立索引。其中：1、学生表按学号升序建立索引；CREATE UNIQUE INDEX 学号ON 学生(学号)；2、课程表按课程号升序建惟一索引；CREATE UNIQUE INDEX 课程号 ON 课程(课程号)；3、选课表按学号升序和课程号降序建惟一索引。CREATE UNIQUE INDEX 选课号 ON 选课(学号 ASC，课

23、程号 DESC)；2022/9/21An Introduction to Database System5.2.3索引的定义和维护2、删除索引一般格式：DROP INDEX ；删除索引时，系统会从数据字典中删去有关该索引的描述。例5-5 删除学生表的“学生姓名”索引 DROP INDEX 学生姓名；2022/9/21An Introduction to Database System5.2 数据库定义语言5.2.1数据库的定义和维护（补充部分）5.2.2基本表的定义和维护5.2.3索引的定义和维护5.2.4视图的定义和维护（该部分在5.4后讲）2022/9/21An Introduction

24、to Database System补充 mysql:1、show schemas/databases; 显示所有数据库2、use 库名； 修改当前使用库3、show tables；列出当前使用库中所有表4、describe 表名；显示表结构2022/9/21An Introduction to Database SystemDo you have any questions ?2022/9/21An Introduction to Database System第五章 关系数据库标准语言SQL5.1 SQL的功能及特点5.2 数据定义语句5.3 数据查询语句5.4 数据更新语句5.5 嵌入式

25、SQL5.6 数据控制机制和语句2022/9/21An Introduction to Database System5.3 数据查询语句基本语法SELECT ALL|DISTINCT目标列组FROM数据源WHERE元组选择条件GROUP BY分列组HAVING 组选择条件ORDER BY排序列1排序要求1 ，n；2022/9/21An Introduction to Database System5.3 数据查询语句举例，学生课程数据库S_T（同时见教材P127）: 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpn

26、o) 学生选课表：SC(Sno,Cno,Grade) 2022/9/21An Introduction to Database SystemStudent表学 号Sno姓 名Sname性 别 Ssex年 龄 Sage所 在 系 Sdept200215121200215122200215123200515125李勇刘晨王敏张立男女女男20191819CSCSMAIS2022/9/21An Introduction to Database SystemCourse表课程号Cno课程名Cname先行课Cpno1234567数据库数学信息系统操作系统数据结构数据处理PASCAL语言516762022/

27、9/21An Introduction to Database SystemSC表学 号Sno 课程号 Cno 成绩 Grade 200215121 200215121 200215121 200215122 200215122 1 2 3 2 3 92 85 88 90 802022/9/21An Introduction to Database System5.3 数据查询语句5.3.1、简单查询（包含了教材5.3中的5.）5.3.2、连接查询5.3.3、嵌套查询5.3.4、组合查询 2022/9/21An Introduction to Database System5.3.1、简单查询

28、简单查询：查询仅涉及一个表，最简单的查询语句。一、 选择表中的若干列二、 选择表中的若干元组三、 ORDER BY子句四、 聚集函数五、 GROUP BY子句 5.3.52022/9/21An Introduction to Database System一、 选择表中的若干列1、查询指定列补充例6 查询全体学生的学号与姓名。SELECT 学号，姓名FROM 学生 ；例5-9求数学系学生的学号和姓名。SELECT 学号，姓名FROM 学生 WHERE 所在系=数学系；2022/9/21An Introduction to Database System一、 选择表中的若干列（续）2. 查询全部

29、列在SELECT关键字后面列出所有列名 将指定为 *补充例7 查询全体学生的详细记录。SELECT 学号，姓名，性别，年龄，所在系 FROM 学生； 或SELECT *FROM 学生； 2022/9/21An Introduction to Database System一、 选择表中的若干列（续）3. 查询经过计算的值SELECT子句的可以为：算术表达式字符串常量函数列别名 2022/9/21An Introduction to Database System查询经过计算的值（续）一、 选择表中的若干列（续）3. 查询经过计算的值（续）补充例8 查全体学生的姓名及其出生年份。SELECT 姓

30、名，2004-年龄 FROM Student；/*假定当年的年份为2004年*/ 输出结果： 姓名 2004-年龄 李勇 1984 刘晨 1985 王敏 1986 张立 1985 2022/9/21An Introduction to Database System一、 选择表中的若干列（续）使用列别名改变查询结果的列标题:补充例8修改如下 SELECT 姓名，2004-年龄 出生年份 FROM Student输出结果： 姓名 出生年份 李勇 1984 刘晨 1985 王敏 1986 张立 1985 2022/9/21An Introduction to Database System3.4.

31、1 单表查询 查询仅涉及一个表：一、 选择表中的若干列二、 选择表中的若干元组三、 ORDER BY子句四、 聚集函数五、 GROUP BY子句2022/9/21An Introduction to Database System二、选择表中的若干元组SELECT中，如果没有指定DISTINCT关键词，则缺省为ALL 5-10 查询选修了课程的学生学号。 SELECT Sno FROM SC；等价于：SELECT ALL Sno FROM SC；结果为： Sno2002151212002151212002151212002151222002151222022/9/21An Introducti

32、on to Database System二、选择表中的若干元组（续）指定DISTINCT关键词，去掉表中重复的行 SELECT DISTINCT Sno FROM SC； 执行结果： Sno2002151212002151222022/9/21An Introduction to Database System二、选择表中的若干元组（续）查 询 条 件谓 词比 较=，=，=，!=，!，!；NOT+上述比较运算符确定范围BETWEEN AND，NOT BETWEEN AND确定集合IN，NOT IN字符匹配LIKE，NOT LIKE空 值IS NULL，IS NOT NULL多重条件（逻辑运算

33、）AND，OR，NOT表 常用的查询条件2022/9/21An Introduction to Database System二、选择表中的若干元组（续）1、 比较补充例9 查询计算机科学系全体学生的名单。 SELECT 姓名 FROM 学生 WHERE 所在系=计算机； 补充例10 查询所有年龄在20岁以下的学生姓名及其年龄。 SELECT 姓名，年龄 FROM 学生 WHERE 年龄 20；补充例11 查询考试成绩有不及格的学生的学号。 SELECT DISTINCT 学号 FROM 选课 WHERE 成绩60； 2022/9/21An Introduction to Database S

34、ystem二、选择表中的若干元组（续）2、确定范围谓词: BETWEEN AND NOT BETWEEN AND 补充例12 查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄 SELECT Sname，Sdept，SageFROM StudentWHERE Sage BETWEEN 20 AND 23； 补充例13 查询年龄不在2023岁之间的学生姓名、系别和年龄 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23； 2022/9/21An Introduction to Databa

35、se System二、选择表中的若干元组（续）3、确定集合谓词：IN , NOT IN 补充例14 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECT Sname，SsexFROM StudentWHERE Sdept IN ( IS，MA，CS );补充例15 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECT Sname，SsexFROM Student WHERE Sdept NOT IN ( IS，MA，CS );2022/9/21An Introduction to Database System二、选择表中的若干元组（续

36、）4、字符匹配谓词： NOT LIKE ESCAPE 1) 匹配串为固定字符串2) 匹配串为含通配符的字符串3) 使用换码字符将通配符转义为普通字符2022/9/21An Introduction to Database System二、选择表中的若干元组（续）4、字符匹配(续）匹配串为固定字符串补充例16 查询学号为200215121的学生的详细情况。 SELECT * FROM Student WHERE Sno LIKE 200215121；等价于： SELECT * FROM Student WHERE Sno = 200215121 ；2022/9/21An Introduction

37、 to Database System二、选择表中的若干元组（续）4、字符匹配(续）2) 匹配串为含通配符的字符串补充例17 查询所有姓刘学生的姓名、学号和性别。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 刘%；补充例18 查询姓欧阳且全名为三个汉字的学生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 欧阳_；%任意长度字符通配符_单字符通配符2022/9/21An Introduction to Database System二、选择表中的若干元组（续）补充例19 查询名字中第2个字为“阳”字的学生的姓名和学号。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _阳%；补充例20 查询所有不姓刘的学生姓名。 SELECT Sname FROM Student WHERE Sname NOT LIKE 刘%；2022/9/21An Introduction to Database System二、选择表中的若干元组（续）4、字