西北农林科技大学信息工程学院.ppt

《西北农林科技大学信息工程学院.ppt》由会员分享，可在线阅读，更多相关《西北农林科技大学信息工程学院.ppt（75页珍藏版）》请在三一文库上搜索。

1、西北农林科技大学信息工程学院,数据库系统概论 An Introduction to Database System 第三章 关系数据库标准语言SQL,An Introduction to Database System,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 查询 3.5 数据更新 3.6 视图 3.7 小结,An Introduction to Database System,3.1 SQL概述,SQL（Structured Query Language），即结构化查询语言，自1974年提出，经各公司的不断修改、扩充和完善，最

2、终发展成为关系数据库的标准语言。 是一个通用的、功能极强的关系数据库语言。,An Introduction to Database System,SQL标准从1986年公布以来随着数据库技术的发展不断发展和丰富。 标准 大致页数 发布日期 SQL/86 1986年10月 SQL/89 120页 1989年 SQL/92 622页 1992年 SQL/99 1700页 1999年 SQL2003 360页 2003年,3.1 SQL概述,An Introduction to Database System,3.1 SQL概述,SQL的特点 1. 综合统一 SQL语言集数据定义语言DDL、数据操纵

3、语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动，包括定义关系模式、插入数据建立数据库、查询、更新、维护、数据库重构、数据库安全性控制等一系列操作要求 。 2. 高度非过程化 只需提出“做什么”，而不必指明“怎么做” 3. 面向集合的操作方式 4. 以同一种语法结构提供两种使用方法 SQL语言既是自含式语言，又是嵌入式语言 5. 语言简洁，易学易用,An Introduction to Database System,3.1 SQL概述,5. 语言简捷，易学易用,An Introduction to Database System,SQL的基本概

4、念,SQL语言支持关系数据库三级结构。 外模式对应于视图和部分基本表 模式对应于基本表 内模式对应于存储文件 用户可以用SQL语言对基本表和视图进行查询或其他操作，基本表和视图一样，都是关系。 基本表是本身独立存在的表，在SQL中一个关系就对应一个表。 一个或多个基本表对应一个存储文件，一个表可以带若干个索引，索引也存放在存储文件中。 视图是从一个或几个基本表导出的表。,An Introduction to Database System,第三章 关系数据库标准语言SQL,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 查询 3.5 数据更新 3.6 视图 3.7 小结

5、,An Introduction to Database System,3.2 学生-课程数据库,在本章中用学生-课程数据库作为一个例子来讲解SQL的数据定义、数据操作、数据查询和数据控制语句的具体应用。 学生-课程数据中包括以下3个表 学生表：Student(Sno,Sname,Ssex,Sage,Sdept) 课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade),An Introduction to Database System,3.2 学生-课程数据库,Student表,An Introduction to Databas

6、e System,3.2 学生-课程数据库,Course表,An Introduction to Database System,3.2 学生-课程数据库,SC表,An Introduction to Database System,3.1 SQL概述 3.2 学生-课程数据库 3.3 数据定义 3.4 查询 3.5 数据更新 3.6 视图 3.7 小结,第三章 关系数据库标准语言SQL,An Introduction to Database System,3.3 数据定义,SQL的数据定义语句,An Introduction to Database System,3.3 数据定义,3.3.1

7、 模式的定义与删除 3.3.2 基本表的定义、删除与修改 3.3.3 索引的建立与删除,An Introduction to Database System,3.3.1 模式的定义与删除,一、定义模式 模式定义语句： CREATE SCHEMA AUTHORIZATION 其中： 如果没有指定，那么隐含为 要创建模式，调用该命令的用户必须拥有DBA权限，或者获得了DBA授予的CREATE SCHEMA的权限。,An Introduction to Database System,3.3.1 模式的定义与删除,例1定义一个学生-课程模式 S-T CREATE SCHEMA “S-T” AUTHO

8、RIZATION WANG; 为用户WANG定义了一个模式S-T 例2CREATE SCHEMA AUTHORIZATION WANG； 隐含为用户名WANG 如果没有指定，那么隐含为,An Introduction to Database System,3.3.1 模式的定义与删除,定义模式实际上定义了一个命名空间 在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。 在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT字句。 CREATE SCHEMA AUTHORIZATION |,An Introduction to Da

9、tabase System,3.3.1 模式的定义与删除,例3 CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(COL1 SMALLINT, COL2 INT, COL3 CHAR(20), COL4 NUMERIC(10,3), COL5 DECIMAL(5,2) 为用户ZHANG创建了一个模式TEST，并在其中定义了一个表TAB1。,An Introduction to Database System,3.3.1 模式的定义与删除,二、删除模式 DROP SCHEMA 其中： CASCADE和RESTRICT两者必选其一 C

10、ASCADE(级联)：删除模式的同时把该模式中所有的数据库对象全部删除 RESTRICT(限制)：如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。当该模式中没有任何下属的对象时才能执行。,An Introduction to Database System,3.3.1 模式的定义与删除,例4 DROP SCHEMA TEST CASCADE； 删除模式TEST同时该模式中定义的表TAB1也被删除,An Introduction to Database System,3.3.2 基本表的定义、删除与修改,一、定义基本表 CREATE TABLE （ ， ， ）； 其中

11、： ：所要定义的基本表的名字 ：组成该表的各个属性（列） ：涉及相应属性列的完整性约束条件 ：涉及一个或多个属性列的完整性约束条件,An Introduction to Database System,例题,例5 建立一个“学生”表S，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE S (Sno CHAR(5) Sname CHAR(8) Ssex CHAR(2), Sage INT, Sdept CHAR(15) )；,Primary key,unique,An Intro

12、duction to Database System,例题 （续）,An Introduction to Database System,定义基本表（续）,常用完整性约束 实体完整性约束：PRIMARY KEY 参照完整性约束：FOREIGN KEY 用户定义的完整性约束：CHECK 唯一性约束：UNIQUE 非空值约束：NOT NULL PRIMARY KEY与 UNIQUE的区别？,An Introduction to Database System,定义基本表（续）,例6 建立一个“课程表”C，它由课程号(Cno)、课程名(Cname)、先修课(Cpno)、学分(Ccredit)构成。

13、CREATE TABLE C ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) , Cpno CHAR(4) REFERENCES C(Cno), Ccredit SMALLINT );,An Introduction to Database System,例题 （续）,例7 建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno, Cno)为主码。成绩grade取值在1100之间。 CREATE TABLE SC( Sno CHAR(5) not null, Cno CHAR(4) not null, Grade in

14、t Primary key (Sno, Cno), Foreign key (sno) references S(sno), Foreign key (cno) references C(cno);,check(grade=0 and grade=100),An Introduction to Database System,3.3.2 基本表的定义、删除与修改,二、数据类型 关系模型中一个很重要的概念是域，SQL中域的概念用数据类型来实现 定义表的属性时 需要指明其数据类型及长度 选用哪种数据类型 取值范围 要做哪些运算 不同的RDBMS中支持的数据类型不完全相同,An Introducti

15、on to Database System,3.3.2 基本表的定义、删除与修改,三、模式与表 每一个基本表都属于某一个模式 一个模式包含多个基本表 定义基本表所属模式 方法一：在表名中明显地给出模式名 Create table “S-T”.Student（）; /*模式名为 S-T*/ Create table “S-T”.Cource（）; Create table “S-T”.SC（）; 方法二：在创建模式语句中同时创建表 方法三：设置所属的模式,An Introduction to Database System,3.3.2 基本表的定义、删除与修改,四、修改基本表 ALTER TAB

16、LE ADD 完整性约束 DROP ALTER COLUMN ； 其中： ：要修改的基本表 ADD子句：增加新列和新的完整性约束条件 DROP子句：删除指定列和完整性约束条件 ALTER COLUMN子句：用于修改列名和数据类型,An Introduction to Database System,例题,例8 向S表增加“入学时间”列，其数据类型为日期型。 ALTER TABLE S ADD Scome DATE； 不论基本表中原来是否已有数据，新增加的列一律为空值。,An Introduction to Database System,例题,删除属性列 例9 将属性列Scome删除： Alt

17、er Table S drop column Scome；,An Introduction to Database System,例题,例10 将年龄的数据类型改为半字长整数。 ALTER TABLE S ALTER COLUMN Sage SMALLINT 注：修改原有的列定义有可能会破坏已有数据,An Introduction to Database System,例题,例11 删除学生姓名必须取唯一值的约束。 ALTER TABLE S DROP UNIQUE(Sname)；,An Introduction to Database System,3.3.2 基本表的定义、删除与修改,五、

18、删除基本表 语句格式： DROP TABLE RESTRICT|CASCADE; RESTRICT：删除表是有限制的。 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象，则此表不能被删除 CASCADE：删除该表没有限制。 在删除基本表的同时，相关的依赖对象一起删除,An Introduction to Database System,例题,例12 删除S表 DROP TABLE S CASCADE ; 基本表定义被删除，数据被删除 表上建立的索引、视图、触发器等一般也将被删除,An Introduction to Database System,例题,例13若表上建有视图，选择

19、RESTRICT时表不能删除 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM S WHERE Sdept=IS； DROP TABLE S RESTRICT; -ERROR: cannot drop table Student because other objects depend on it,An Introduction to Database System,例题,例13如果选择CASCADE时可以删除表，视图也自动被删除 DROP TABLE S CASCADE; -NOTICE: drop cascades to view I

20、S_Student SELECT * FROM IS_Student; -ERROR: relation “IS_Student “ does not exist 注意：不同的数据库产品在遵循SQL标准的基础上具体实现细节和处理策略上会与标准有差别，见课本P89。,An Introduction to Database System,3.3.3 建立与删除索引,建立索引是加快查询速度的有效手段 建立索引 DBA或表的属主（即建立表的人）根据需要建立 有些DBMS自动建立以下列上的索引 PRIMARY KEY UNIQUE 维护索引 DBMS自动完成 使用索引 DBMS自动选择是否使用索引以及使

21、用哪些索引,An Introduction to Database System,3.3.3 建立与删除索引,An Introduction to Database System,3.3.3 建立与删除索引,建立索引的考虑 索引为性能所带来的好处是有代价的，因为索引在数据库中会占用一定的存储空间。 在对数据进行插入、更改和删除操作时，需要对索引进行维护，这需要花费额外的时间。 在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。,An Introduction to Database System,3.3.3 建立与删除索引,创建索引的方法可以分为直接方法和间接方法

22、直接创建索引的方法就是使用CREATE INDEX语句直接创建 间接创建索引就是通过创建其他对象而附加创建了索引，例如在表中定义主键约束或唯一约束时，同时也创建了索引。,An Introduction to Database System,一、建立索引,语句格式 CREATE UNIQUE CLUSTER INDEX ON (, )； 用指定要建索引的基本表名字 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔 用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC UNIQUE表明此索引的每一个索引值只对应唯一的数据记录 CLUSTER表示要建立的索引是聚簇索引,An I

23、ntroduction to Database System,例题,例14 为学生-课程数据库中的S，C，SC三个表建立索引。其中S表按学号升序建唯一索引，C表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。 CREATE UNIQUE INDEX Stusno ON S (Sno)； CREATE UNIQUE INDEX Coucno ON C (Cno)； CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)；,An Introduction to Database System,建立索引 （续）,唯一值索引 对于已含重复值的属

24、性列不能建UNIQUE索引 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束,An Introduction to Database System,建立索引 （续）,聚簇索引 建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致 例： CREATE CLUSTER INDEX Stusname ON S (Sname)； 在S表的Sname（姓名）列上建立一个聚簇索引，而且S表中的记录将按照Sname值的升序存放,An Introduction to D

25、atabase System,建立索引 （续）,在一个基本表上最多只能建立一个聚簇索引 聚簇索引的用途：在最经常查询的列上建立聚簇索引，可以提高查询效率 聚簇索引的适用范围 很少对基表进行增删操作 很少对其中的变长列进行修改操作,An Introduction to Database System,二、删除索引,语句格式： DROP INDEX ； 删除索引时，系统会从数据字典中删去有关该索引的描述。 例15 删除S表的Stusname索引。 DROP INDEX Stusname；,An Introduction to Database System,3.4 查 询,3.4.1 概述 3.4

26、.2 单表查询 3.4.3 连接查询 3.4.4 嵌套查询 3.4.5 集合查询 3.4.6 小结,An Introduction to Database System,3.4.1 概述,语句格式 SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；,An Introduction to Database System,语句格式,SELECT子句：指定要显示的属性列 FROM子句：指定查询对象(基本表或视图) WHERE子句：指定查询条件 GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元

27、组为一个组。通常会在每组中作用集函数。 HAVING短语：筛选出只有满足指定条件的组 ORDER BY子句：对查询结果表按指定列值的升序或降序排序,An Introduction to Database System,3.3 查 询,3.3.1 概述 3.3.2 单表查询 3.3.3 连接查询 3.3.4 嵌套查询 3.3.5 集合查询 3.3.6 小结,An Introduction to Database System,3.3.2 单表查询,查询仅涉及一个表，是一种最简单的查询操作 一、选择表中的若干列 二、选择表中的若干元组 三、对查询结果排序 四、使用集函数 五、对查询结果分组,An

28、Introduction to Database System,3.3.2 单表查询,一、选择表中的若干列 1. 查询指定列 2. 查询全部列 3. 查询经过计算的值,An Introduction to Database System,3.3.2 单表查询,二、选择表中的若干元组 1. 消除取值重复的行(Distinct) 2. 查询满足条件的元组,An Introduction to Database System,2.查询满足条件的元组,WHERE子句常用的查询条件,An Introduction to Database System,（4）字符串匹配,谓词Like可以用来进行字符串的匹

29、配。格式如下： NOT LIKE ESCAPE 查找指定的属性列值与相匹配的元组。可以是一个完整的字符串，也可以含有通配符和_。,An Introduction to Database System,通配符,% (百分号) 代表任意长度（长度可以为0）的字符串 例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串 _ (下横线) 代表任意单个字符 例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串,An Introduction to Database System,ESCAPE 短语,当用户要查询的字符串本身就

30、含有 % 或 _ 时，要使用ESCAPE 短语对通配符进行转义。,An Introduction to Database System,例题,使用换码字符将通配符转义为普通字符 例16 查询以“DB_“开头，且倒数第3个字符为i的课程的详细情况。 SELECT * FROM C WHERE Cname LIKE DB_%i_ _ ESCAPE ；,An Introduction to Database System,(5) 涉及空值的查询,使用谓词 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例17 某些学生选修课程后没有参加考试，所以有选课

31、记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；,An Introduction to Database System,(6) 多重条件查询,用逻辑运算符AND和OR来联结多个查询条件 AND的优先级高于OR 可以用括号改变优先级 可用来实现多种其他谓词 NOT IN NOT BETWEEN AND ,An Introduction to Database System,三、对查询结果排序,使用ORDER BY子句 可以按一个或多个属性列排序 升序：ASC；降序：DESC；缺省值为升序 当排序

32、列含空值时 ASC：排序列为空值的元组最后显示 DESC：排序列为空值的元组最先显示,An Introduction to Database System,对查询结果排序（续）,例18 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 003 ORDER BY Grade DESC；,An Introduction to Database System,对查询结果排序（续）,例19 查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列。 SELECT * FROM S ORDER

33、BY Sdept，Sage DESC；,An Introduction to Database System,四、使用集函数,5类主要集函数 计数 COUNT（DISTINCT|ALL *） COUNT（DISTINCT|ALL ） 计算总和 SUM（DISTINCT|ALL ） 计算平均值 AVG（DISTINCT|ALL ）,An Introduction to Database System,使用集函数（续）,求最大值 MAX（DISTINCT|ALL ） 求最小值 MIN（DISTINCT|ALL ） DISTINCT短语：在计算时要取消指定列中的重复值 ALL短语：不取消重复值 AL

34、L为缺省值,An Introduction to Database System,使用集函数 （续）,例20 查询选修了课程的学生人数。 SELECT COUNT(Sno) FROM SC； 例21 计算001号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC WHERE Cno=001；,(DISTINCT Sno),An Introduction to Database System,五、对查询结果分组,使用GROUP BY子句将查询结果表按某一列或多列值分组，值相等的为一组。 细化集函数的作用对象 未对查询结果分组，集函数将作用于整个查询结果 对查询结果分组后，集

35、函数将分别作用于每个组,An Introduction to Database System,使用GROUP BY子句分组,例22 求各个课程号及相应的选课人数。 SELECT Cno，COUNT(Sno) FROM SC GROUP BY Cno；,An Introduction to Database System,对查询结果分组 （续）,GROUP BY子句的作用对象是查询的中间结果表 分组方法：按指定的一列或多列值分组，值相等的为一组 使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和集函数,An Introduction to Database System,

36、使用HAVING短语筛选最终输出结果,例23 查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(sno) 3；,An Introduction to Database System,例题,例24 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数 SELECT Sno, COUNT(Cno) FROM SC WHERE Grade=90 GROUP BY Sno HAVING COUNT(Cno)=3;,An Introduction to Database System,使用HAVING短语筛选最终输

37、出结果,只有满足HAVING短语指定条件的组才输出 HAVING短语与WHERE子句的区别：作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组。,课堂练习 1、查询学生修课表中的全部数据。 2、查询信息学院的学生的姓名、年龄。 3、查询成绩在70-80分（包括70和80分）之间的学生的学号、课程号和成绩。 4、查询水建学院年龄在18-20岁（包括18和20岁）之间且性别为“男”的学生的姓名和年龄。 5、查询001号课程的最高成绩。 6、查询植保学院学生的最大年龄和最小年龄。 7、统计每个学院的学生人数。 8、统计每门课程的修课人数和考试最高分。 9、统计每个学生的选课门数和考试总成绩，并按总成绩的递减顺序显示结果。 10、查询总成绩超过200分的学生，要求列出学号和总成绩。,