计算机软件技术基础计算机软件技术基础 数据库系统（ 3 ）

第 2 页 4.3 关系数据库语言 SQL 关系数据库 SQL （ Structured Query Language ）语言是关系 数据库的标准语言，对关系模型的发展和商用 DBMS 的研制起 着重要的作用。 SQL 发展历史  1986 年 10 月， ANSI 批准 SQL 作为关系数据库语言的美国标准， 并公布了 SQL 的标准文本 ——“ 数据库语言 SQL” （简称 SQL86 ）， 1987 国际标准化组织 ISO 通过了这一标准；  1989 年公布了 SQL89 标准，增加了对完整性约束的支持；  1992 年公布了 SQL92 标准；  1996 年公布了 SQL96 标准；  1999 年公布了 SQL99 标准；  2003 年公布了 SQL2003 标准；  SQL 标准文本的修改和完善还在继续进行 ……

第 3 页 SQL 语言的特点  一体化语言：集成 DDL 、 DML 、 DCL 于一体，共有 8 个 语句： SELECT 、 CREATE 、 ALTER 、 DROP 、 INSERT 、 DELETE 、 UPDATE 、 GRANT 、 REVOKE 。  两种使用方法，统一语法结构：自含式，嵌入式。  高度非过程化： Do what? Not how to do?  接近自然语言：语法简洁，易学、易用。  面向集合的操作方式：一次一集合。 功 能功 能操作符 数据查询 SELECT 数据定义 CREATE ， ALTER ， DROP 数据操纵 INSERT ， UPDATE ， DELETE 数据控制 GRANT ， REVOKE

第 4 页 数据定义语句 数据定义语句定义数据库的模式，包括对基本表 (table) 、视 图 (view) 和索引 (index) 等数据库对象的创建和撤销操作。数据类型： INTEGER SMALLINT DECIMAL(p[, q]) FLOAT CHAR(n) VARCHAR(n)常用语句： CREATE TABLEDROP TABLE CREATE VIEWDROP VIEW CREATE INDEXDROP INDEX

第 5 页 语法： CREATE TABLE 表名 ( 列名 数据类型 [DEFAULT 缺省值 ] [NOT NULL] [, 列名 数据类型 [DEFAULT 缺省值 ] [NOT NULL] … ] [, PRIMARY KEY( 列名 [, 列名 ] …)] [, FOREIGN KEY ( 列名 [, 列名 ] …) REFERENCES 表名 ( 列名 [, 列名 ] …)] [ ， CHECK ( 条件表达式 )] ) 注：句法中 [ ] 表示该成分是可选项。 CREATE TABLE 语句

第 6 页 举例 设教学数据库有三个关系：  STUDENT (sno, sname, ssex, sage, sdept)  COURSE (cno, cname, credit)  SC (sno, cno, grade) 基本表 STUDENT 可用下列语句创建 : STUDENT CREATE TABLE STUDENT ( sno sno CHAR(4) NOT NULL, sname sname CHAR (8) NOT NULL, sage sage SMALLINT, ssex ssex CHAR(1), sdept sdept CHAR(30), PRIMARY KEY(Sno) PRIMARY KEY(Sno)) ; 主键约束

第 7 页 COURSE CREATE TABLE COURSE ( cno cno CHAR(4) PRIMARY KEY, cname cname CHAR(50) NOT NULL, credit credit SMALLINT ) ; SC CREATE TABLE SC ( sno sno CHAR(4) NOT NULL, cno cno CHAR(4) NOT NULL, grade grade SMALLINT, PRIMARY KEY(sno, cno), FOREIGN KEY(sno) REFERENCES STUDENT(sno), FOREIGN KEY(cno) REFERENCES COURSE(cno), CHECK (grade>= 0) CHECK (grade>= 0) ) ; 外键约束 自定义约束

第 8 页 DROP TABLE 语句 语法： DROP TABLE 表名 [CASCADE | RESTRICT] 注： 撤消基本表后，基本表的定义、表中的数据、表上的索 引、以及由此表导出的视图的定义都被删除。 例： SC DROP TABLE SC ; COURSE DROP TABLE COURSE ; STUDENT DROP TABLE STUDENT ;

第 9 页 数据查询语句 数据查询语句是 SQL 语言中最复杂的部分，允许用户以灵 活的方式表达查询意图，并返回数据库中符合条件的数据。 语法： SELECT 目标表的列名或列表达式序列 FROM 基本表和 ( 或 ) 视图序列 [WHERE 行条件表达式 ] [GROUP BY 列名序列 [HAVING 组条件表达式 ] ] [ORDER BY 列名 [ASC|DESC]…] 注： SELECT 语句中， WHERE 子句称为行条件子句， GROUP 子句称为分组子句， HAVING 子句称为组条件子句， ORDER 子句称为排序子句。

第 10 页 举例（简单查询） 例 1 ：查询全部学生的所有信息。 SELECT * FROM STUDENT; 例 2 ：查询全部被选课程的课程号。 SELECT DISTINCT cno FROM SC; 例 3 ：列出选修了课程号为 ‘C6’ 的所有学生的学号和成绩，并 按分数的降序排列。 SELECT sno, grade FROM SC WHERE cno='C6' ORDER BY grade DESC;

第 11 页 表达查询条件的谓词  比较谓词：  比较谓词：＝， ， >= ， <> ; 例： cno='C6'  IN 谓词：  IN 谓词：适合于离散有穷集合的查询； 例：性别 IN (’ 男 ’,’ 女 ’)  LIKE 谓词：  LIKE 谓词：适合于字符串的模糊查询 ( 匹配符 %_) 例：书名 LIKE ‘ 数据库 %’  NULL 谓词：  NULL 谓词：判断空值 例：成绩 IS NULL  BETWEEN 谓词：  BETWEEN 谓词：适合数值型字段，可以定义一个闭区间； 例：成绩 BETWEEN 80 AND 90 ……

第 12 页 举例（使用谓词查询） 例 4 ：查询信息系（ IS ）、数学系（ MA ）以及计算机系 （ CS ）学生的姓名和性别。 SELECT sname, ssex FROM STUDENT WHERE sdept IN(‘IS’,’MA’,’CS’); 例 5 ：查询还没有分系的学生的学号和姓名。 SELECT sno, sname FROM STUDENT WHERE sdept IS NULL; 例 6 ：查询所有姓王的学生的姓名、学号和性别。 SELECT sname, sno, ssex FROM STUDENT WHERE sname LIKE ’ 王 %’;

第 13 页 举例（使用谓词查询） 例 7 ：查询课程名以 DB 开头且倒数第 3 个字符为 I 的课程的所 有信息。 SELECT * FROM COURSE WHERE cname LIKE ’DB%I_ _’; 例 8 ：查询年龄在 20 到 23 岁之间的学生姓名、系别和年龄。 SELECT sname,sdept,sage FROM STUDENT WHERE sage BETWEEN 20 AND 23;

第 14 页 集函数和分组 集函数是面向一个集合的求值函数。  SUM ：  SUM ：求总和  AVG ：  AVG ：求平均值  COUNT ：  COUNT ：求集合中元素的个数  MAX ：  MAX ：最大值  MIN ：  MIN ：最小值 分组就是对查询的结果进行分类。  GROUP 子句可以按某些属性的值对查询结果进行分组。  HAVING 子句可以对分组后的结果作进一步的筛选。  当查询语句中有 GROUP 子句时，集函数作用的对象是一个 分组的结果，而不是整个查询的结果。

第 15 页 举例（分组查询） 例 9 ：查询每门课程的课程号和平均成绩。 SELECT cno, AVG(grade) FROM SC GROUP BY cno; 例 10 ：查询选修了三门以上课程的学生学号。 SELECT sno FROM SC GROUP BY sno HAVING COUNT(*)>3; 注： WHERE 子句与 HAVING 子句的区别在于 WHERE 子句 作用于基本表或视图，从中选择满足条件的元组； HAVING 短语作用于组，从中选择满足条件的组。

第 16 页 连接查询 当查询的信息涉及到多个表中的数据时，要将这些表按某 种条件连接起来。 例 10 ：求 “ 数据结构 ” 课程成绩大于 85 分的学生姓名和成绩， 结果按成绩降序排列。 SELECT STUDENT.sname ， grade FROM STUDENT ， SC ， COURSE STUDENT.sno = SC.sno AND WHERE STUDENT.sno = SC.sno AND SC.cno = COURSE.cno SC.cno = COURSE.cno AND COURSE.cname = ' 数据结构 ' AND SC.grade > 85 ORDER BY grade DESC; 注：当不同的表中有同名属性时，属性名前要用表名限定。

第 17 页 数据操纵语句 数据操纵包括数据插入、数据删除和数据修改三种操作。数据插入  元组值的插入 语法： INSERT INTO 基本表 [( 列表 )] VALUES( 元组值 ) 作用：将一条元组值插入到表中。 例 1 ：往基本表 SC 中插入一个元组值 INSERT INTO SC VALUES(‘S004’,‘ 数据库 ’,90); 例 2 ：往基本表 SC 中插入部分元组值 INSERT INTO SC(sno, cno) VALUES(‘S004’,‘ 物理 ’);  查询结果的插入 句法： INSERT INTO 基本表名 [( 列名表 )] 子查询 作用：将子查询返回的结果数据集插入到表中。 要求：查询语句的目标列必须与 INTO 子句匹配。

第 18 页 例 3 ：将平均成绩大于 80 分的学生学号和平均成绩存入另一个 基本表 S_GRADE(Sno, Avg_grade) 。 INSERT INTO S_GRADE (Sno, Avg_grade) SELECT Sno, AVG(grade) FROM SC GROUP BY sno HAVING AVG(grade)>80; 数据删除 语法： DELETE FROM 基本表 [WHERE 条件表达式 ] 作用：从表中删除符合 WHERE 子句中删除条件的元组；若 WHERE 子句缺省，则表示要删除表中的所有元组。 例 4 ：删除学号为 ‘S001’ 的学生信息。 DELETE FROM STUDENT WHERE sno=‘S001’;

第 19 页 数据修改 语法： UPDATE 基本表名 SET 列名＝值表达式 [, 列名＝值表达式 …] [WHERE 条件表达式 ] 作用：修改指定表中满足 WHERE 子句条件的元组，若 WHERE 子句缺省，则表示对所有元组进行修改。 例 5 ：把所有学生的年龄加 1 。 UPDATE STUDENT SET Sage = Sage+1; 例 6 ：把课程号为 ‘C5’ 的课程名改为 “ 电子商务 ” 。 UPDATE COURSE SET Cname=' 电子商务 ' WHERE Cno = 'C5';

第 20 页 视 图视 图视 图视 图 视图是从一个或几个基本表（或视图）导出的一个虚表。 数据库中只存放视图的定义而不存放视图的数据，这些数据 仍放在原来的基表中。当基表中的数据发生变化时从视图中查 出的数据也随之改变了。 视图一经定义就可以对其进行查询，但对视图的更新操作有 一定的限制。视图的定义 语法： CREATE VIEW 视图名 [( 列名 [, 列名 ]…)] AS 子查询 [WITH CHECK OPTION]

第 21 页 例 1 ：建立计算机学院 98 级学生视图。 CREATE VIEW CS_STUDENT(sno, sname) AS SELECT sno, sname FROM STUDENT WHERE sdept = ‘CS’ WITH CHECK OPTION; 例 2 ：建立计算机学院选修了 5 号课程的学生视图。 CREATE VIEW CS_S1(sno,sname,grade) AS SELECT STUDENT.sno,sname,grade FROM STUDENT, SC WHERE sdept=’CS’ AND STUDENT.sno=SC.sno AND cno=’5’;

第 22 页 视图的查询 对视图的查询与对表的查询完全一样。 例如：在计算机学院学生的视图 CS_S1 中找学习了 5 号课 程且成绩为 95 分的学号、姓名。 SELECT sno, sname CS_S2 FROM CS_S2 WHERE grade=95;视图的撤消 语法： DROP VIEW 视图名 作用：从数据库中删除一个视图的定义信息。 例如：撤消视图 CS_S1 。 DROP VIEW CS_S1;

第 23 页 小 结 数据处理 数据库技术是一门用于数据处理的综合性软件技术。 可控冗余 数据独立性数据完整性数据一致性 数据库方法的特点：数据统一管理，数据共享，可控冗余， 数据独立性，数据完整性，数据一致性，数据安全性 数据库系统的组成：数据库，软件，硬件，用户 DBMS 的主要功能： DDL ， DML ，数据库运行管理，数据 库建立和维护 模式，内模式，外模式 如何保证数据独立性 数据库系统的体系结构：三级模式（模式，内模式，外模式） ＋二级映像（如何保证数据独立性） 实体和三类联系 层次模型，网状模型，关系模型 数据模型： E-R 模型（实体和三类联系），三种经典数据模 型（层次模型，网状模型，关系模型） SQL 语言：数据定义、数据查询及数据操纵语句的使用 本章结束