关系数据库标准SQL语言.ppt

1、1,第三章 标准SQL语言,3.1 数据定义 3.2 数据操纵 3.3 视图,2,3.1 数据定义概述1,SQL(Structured Query Language，结构化查询语言)语言是由IBM的圣约瑟研究室为其关系数据库管理系统SYSTEM R开发的一种查询语言 。 SQL语言功能强大，可以完成数据查询、数据定义和数据控制等功能，几乎贯穿了数据库生命周期中的全部活动。,3,3.1 数据定义概述2,1986年美国国家标准化学会 (American National Standards Institute,ANSI)批准SQL语言作为关系数据库的标准语言. 1987年国际标准化组织 (Inte

2、rnational Organization for Standardization,ISO)也通过了这一标准。现在应用比较多的是SQL-92标准。,4,3.1数据定义 SQL语言的特点1,SQL语言是介于关系代数和元组演算之间的一种语言。语法简单，易学易用。 如: SELECT * FROM USER,5,3.1数据定义 SQL语言的特点2,高度非过程化的语言 SQL语言是一个非过程化的语言，因为它一次处理一个记录，对数据提供自动导航。SQL允许用户在高层的数据结构上工作，而不对单个记录进行操作，可操作记录集，其基本思想是首先获取用户指定约束条件的一组记录，然后对这些记录进行某种操作。 SQ

3、L语言不要求用户指定对数据的存放方法， 这种特性使用户更易集中精力于要得到的结果 ，减轻了用户的负担，并有利于提高数据的独立性。,6,3.1数据定义 SQL语言的特点3,面向集合语言 采用集合操作方式，操作对象和查找结果可以是元组集合，而不仅仅是一个记录。 充分利用了关系数据库的优点，极大地提高了系统运行的效率。 例如：用户可以在数据库管理系统软件模块中直接使用SQL命令，修改或查询数据库。,7,3.1数据定义 SQL语言的特点4,可以独立使用，也可以嵌入到宿主语言 既是自含式语言，又是嵌入式语言。 例如，SQL可以嵌入到高级语言（如C+, BASIC等）中，应用程序员和高级用户可以用SQL语

4、言开发应用程序。,8,3.1数据定义 SQL语言的特点5,功能强大，集查询、定义、操纵和控制于一体 使用SQL语言可以定义关系模式、创建数据库、插入数据、更新数据、复杂查询、维护数据、重构数据库、控制数据库安全等等。 几乎可以满足数据库所有操作。,9,3.1数据定义句法定义1,创建表 建立表结构的SQL语句格式如下： CREATE TABLE ( 列级完整性约束条件, 列级完整性约束条件,. 表级完整性约束条件) 完整性约束是指在建立表的同时创建的完整性约束条件，可以在列级，也可以在表级。常用的有：NOT NULL(字段中不允许有空值)，UNIQUE(字段取值唯一)， PRIMARY KEY(

5、字段为主键)等。,10,3.1数据定义句法定义2,例：使用SQL语句建立一个表，表名为stu_info，字段包括学号NO（10位）、姓名NAME（最多4个汉字）、性别GENDER、住址ADDRESS（最多20个汉字）。其中，学号、姓名和性别为必填项；学号唯一。 CREATE TABLE stu_info ( NO char(10) NOT NULL UNIQUE, NAME char(8) NOT NULL, GENDER char(1) NOT NULL, ADDRESS char(40) 因为一个汉字占用两个字节，因此姓名使用char(8)表示。性别可以用M/F代表，用一个字符即可表示。,

6、字段取值唯一,11,3.1数据定义句法定义3,修改表 修改表结构的SQL语句格式如下： ALTER TABLE ADD 完整性约束条件 DROP MODIFY . . ADD子句用于为现有的表增加字段。 DROP子句用于删除一个指定的完整性约束 NOT NULL(字段中不允许有空值)，UNIQUE(字段取值唯一)，PRIMARY KEY(字段为主键)等。 MODIFY子句用于修改表的某个字段的名称或类型。,12,3.1数据定义句法定义4,例：在上节建立的表(表名为stu_info)中增加“出生日期”字段BIRTHDAY，该字段为必填项。 ALTER TABLE stu_info ADD BIR

7、THDAY DATE NOT NULL,13,3.1数据定义句法定义5,删除表 删除表的SQL语句格式如下： DROP TABLE 表删除后，表的结构定义从数据字典中消失，表的索引和视图被一起删除，表中的所有数据也全部被删除。 例：删除stu_info表 DROP TABLE stu_info,14,3.1数据定义句法定义6,索引的概念 索引是数据库的一种功能，对表建立索引可以加快对索引字段的查询、排序和分组操作。 比如，图书馆中的书名索引卡、作者索引卡，就类似于在图书数据库中建立了两个索引，可以加快检索速度。 例如，如果在“姓名”字段中搜索某一学生的姓名，则可以创建该字段的索引，以加快搜索的

8、速度。 基本表上可以建立多个索引，但过多的索引会使数据库自身维护负担加重，使数据插入和更新的效率降低。,15,3.1数据定义句法定义7,建立索引 建立索引的SQL语句格式如下： CREATE UNIQUE CLUSTER INDEX ON ( , . . ) 索引名是指为某个表创建的索引的名称； UNIQUE(字段取值唯一)表明本索引的每一个索引值只对应唯一的数据记录，例如，每个学生的学号唯一对应一个学生的信息； CLUSTER表示要建立聚簇索引，即索引项的顺序和表记录的物理顺序一致的索引。聚簇索引可以提高查询效率，但更新记录会带来额外的开销。,16,3.1数据定义句法定义8,例：为表stu_

9、info建立按照姓名和生日的索引 Idx_1 CREATE UNIQUE INDEX Idx_1 ON stu_info(NAME, BIRDAY),17,3.1数据定义句法定义9,删除索引 删除索引的SQL语句格式如下： DROP INDEX 例：删除Idx_1索引。 DROP INDEX Idx_1 ON stu_info,18,3.1数据定义数据查询1,SELECT语句的格式为 SELECT ALL|DISTINCT ,. . FROM ,. WHERE GROUP BY HAVING ORDER BY ASC|DESC 根据WHERE子句的条件表达式，从FROM中指定的表或视图中找出所

10、有满足条件的元组，再根据目标字段选取记录中的属性值，形成一个最终的结果表。如果有GROUP子句，则将结果按照列名1进行分组，属性列值相等的元组为一个组。如果GROUP子句还带有HAVING，表示只有满足某种条件的组才输出。如果有ORDER子句，表示结果表是按照列名2升序(ASC)或降序(DESC)排序过的。,19,3.1数据定义数据查询2,学生信息表（表名为：stu_info）,教师信息表（表名为：tea_info）,20,3.1数据定义数据查询3,课程表（表名为：cur_info）,21,3.1数据定义数据查询4,成绩表（表名为：sco_info）,22,3.1数据定义数据查询5,简单查询

11、例：输出所有学生的信息。 SELECT * FROM stu_info 结果为： SNO NAME GE BIRDAY CLASS - - - - - - 021601 李平 男 11-NOV-80 自-0205 021635 刘小梅 女 16-JAN-80 机-0203 021608 张力 男 03-MAR-79 自-0205 021638 王珊 女 08-JUN-81 机-0203 这里的*代表所有列。,23,3.1数据定义数据查询6,例：输出所有教师的姓名和职称信息。 SELECT NAME, TITLE FROM tea_info 结果为： NAME TITLE - - 李奇 讲师 张

12、学成 教授 薛智永 教授,24,3.1数据定义数据查询7,例：输出所有学生属于哪些班。 SELECT CLASS FROM stu_info - 自-0205 机-0203 自-0205 机-0203 这里班级有重复。如果去除重复的元组，可用DISTINCT SELECT DISTINCT CLASS FROM stu_info 结果为： - 机-0203 自-0205,25,3.1数据定义数据查询8,用WHERE子句查询 用WHERE子句可以查找满足一定条件的元组，WHERE子句常用的查询条件如下：,26,3.1数据定义数据查询9,例：输出所有男同学的姓名和学号。 SELECT NAME,

13、SNO FROM stu_info WHERE GENDER=男 结果为： NAME SNO - - 李平 021601 张力 021608,27,3.1数据定义数据查询10,例：输出1980年5月15日以后出生的女生的所有信息。 SELECT * from stu_info WHERE GENDER=女 AND BIRDAY #1980-05-15# 结果为： SNO NAME GE BIRDAY CLASS - - - - - 021638 王珊 女 08-JUN-81 机-0203,28,3.1数据定义数据查询11,例：输出分数在90至95区段的所有学生的学号。 SELECT DISTI

14、NCT SNO FROM sco_info WHERE SCORE BETWEEN 90 AND 95 结果为： SNO - 021601 021608 021638,29,3.1数据定义数据查询12,例：输出所有不是机械学院和信息学院系的老师的信息。 SELECT * FROM tea_info WHERE DEPT NOT IN (机械学院,信息学院) 结果为： TNO NAME GE TITLE DEPT - - - - - 805 李奇 女 讲师 基础部 808 张学成 男 教授 基础部,30,3.1数据定义数据查询13,例：输出所有姓张的学生的信息。 SELECT * FROM st

15、u_info WHERE NAME LIKE 张* 结果为： SNO NAME GE BIRDAY CLASS - - - - - 021608 张力 男 03-MAR-79 自-0205 SQL语句中，*是极为重要的模糊查询手段，*其中代表任意长度的字符串。 例如，名字含有“小”，可以使用*小*；名字的最后一个子是“军”，可以用*军 ，等等。 注：SQL92标准中规定而使用*而不是%，一般大型数据库 如Oracle，SQL Server等均使用%,31,3.1数据定义数据查询14,查询结果排序 可以使用ORDER BY子句对输出结果表排序，缺省为升序（ASC），也可以使用降序（DESC）。

16、例：输出所有学生信息，结果按照学号降序排列。 SELECT * FROM stu_info ORDER BY SNO DESC 结果为： SNO NAME GE BIRDAY CLASS - - - - - 021638 王珊 女 08-JUN-81 机-0203 021635 刘小梅 女 16-JAN-80 机-0203 021608 张力 男 03-MAR-79 自-0205 021601 李平 男 11-NOV-80 自-0205,32,3.1数据定义数据查询15,例：输出所有女生信息，结果按照姓名和出生日期排列（即先按照姓名排序，姓名相同的学生再按照出生日期排序）。 SELECT *

17、FROM stu_info WHERE GENDER=女 ORDER BY NAME, BIRDAY 结果为： SNO NAME GE BIRDAY CLASS - - - - - 021635 刘小梅 女 16-JAN-80 机-0203 021638 王珊 女 08-JUN-81 机-0203,33,3.1数据定义数据查询16,查询结果分组 可以使用GROUP BY将查询结果按照某一列或多列进行分组，值相同的分为一组。 例：统计每门课程的选课人数。 SELECT CNO,COUNT(SNO) FROM sco_info GROUP BY CNO 结果为： CNO COUNT(SNO) -

18、- 005036 4 005067 4 005132 2,34,3.1数据定义数据查询17,分组后，还可以使用HAVING关键字进行筛选。 例：统计选课人数少于3人的课程编号和选课人数。 SELECT CNO,COUNT(SNO) FROM sco_info GROUP BY CNO HAVING COUNT(SNO)3 结果为： CNO COUNT(SNO) - - 005132 2,复习,35,3.1数据定义数据查询18,集函数 SQL语言提供了集函数以增强检索功能，常见的集函数如下表所示(如果在语句中使用DISTINCT，那么在计算时将取消输出列中的重复值 )：,36,3.1数据定义数据

19、查询19,例：统计所有课程的总数。 SELECT COUNT(*) FROM cur_info 结果为： COUNT(*) - 4 例：计算学号为021608的学生的平均成绩。 SELECT AVG(SCORE) FROM sco_info WHERE SNO=021608 结果为： AVG(SCORE) - 93,37,3.1数据定义数据查询20,多表连接查询 关系数据库中，多个表可以使用连接谓词连接起来。连接谓词可以使用一些比较运算符和BETWEEN（如 BETWEEN 90 AND 100）等，其中最为常用的连接谓词是等号，称为等值连接。 下面以两个表为例，说明连接的基本过程：先在表1中

20、找到第1个元组，然后在表2中找到满足连接条件的所有元组，并和表1的第一个元组连接起来，形成结果表中的第1部分；然后在表1中找到第2个元组，继续上述过程，结果再继续添加到结果表中；. .，依次处理表1中的所有元组直到结束。,38,3.1数据定义数据查询21,例：输出“高等数学”课程的编号、学时和主讲老师的姓名、职称。 SELECT cur_info.CNO,cur_info.PERIOD, tea_info.NAME,tea_info.TITLE FROM cur_info, tea_info WHERE cur_info.TNO=tea_info.TNO AND cur_info.DESCP=

21、高等数学,39,3.1数据定义数据查询22,在指明列名时，这里使用了连接符.，表示某个表的列名。有时表名比较长，SQL语句比较复杂时，也可以指定表的别名，用来简化SQL语句。例如前面的SQL语句: SELECT cur_info.CNO,cur_info.PERIOD, tea_info.NAME,tea_info.TITLE FROM cur_info, tea_info WHERE cur_info.TNO=tea_info.TNO AND cur_info.DESCP=高等数学 或：SELECT A.CNO,A.PERIOD,B.NAME,B.TITLE FROM cur_info A,

22、 tea_info B WHERE A.TNO=B.TNO AND A.DESCP=高等数学 如果列名在参加连接的各表中唯一，也可以省略表名的前缀，但为了SQL语句的可读性，最好还是加上表名的前缀。,40,3.1数据定义数据查询23,例：输出“005036”课程成绩高于“021638”同学的所有同学的学号和成绩。 SELECT C.SNO, C.SCORE FROM sco_info C, sco_info D WHERE C.SCORED.SCORE AND D.SNO=021638 AND C.CNO=005036 AND D.CNO=005036 这是一个表自身连接的例子，运行结果为：

23、SNO SCORE - - 021601 96 021635 100,41,3.1数据定义数据查询21,例：输出张力同学的“微机基础”课程的成绩。这一个三个表连接的例子 SELECT stu_info.NAME, cur_info.DESCP, sco_info.SCORE FROM stu_info, cur_info, sco_info WHERE stu_info.SNO=sco_info.SNO AND cur_info.CNO=sco_info.CNO AND stu_info.NAME=张力 AND cur_info.DESCP=微机基础,三个表两两连接,42,3.1数据定义数据查

24、询25,嵌套查询 当把一个查询作为另一个查询的条件时，前者的查询块嵌入后者的WHERE或HAVING子句中，形成嵌套查询。嵌套查询增强了SQL的查询能力，几个简单查询可以结合构成功能强大的复合命令。 嵌套查询的求解方法一般是由里向外逐层求解。注意子查询的SELECT语句中不能使用ORDER BY子句，ORDER BY只用于最终的结果表排序。,43,3.1数据定义数据查询26,例：输出所有与张力同班的学生。 SELECT * FROM stu_info WHERE CLASS IN (SELECT CLASS FROM stu_info WHERE NAME=张力) 结果为： SNO NAME

25、GE BIRDAY CLASS - - - - - 021601 李平 男 11-NOV-80 自-0205 021608 张力 男 03-MAR-79 自-0205 谓词IN是嵌套查询中经常使用的谓词，系统在运行SQL语句时，先处理内层的语句，得到张力的班级信息后，进一步得到与之同班的学生信息。,44,3.1数据定义数据查询27,当内层查询返回的值一定是单值时，可以使用比较运算符。 例：输出李奇老师任课班级的所有学生的成绩。 SELECT * FROM sco_info WHERE CNO=(SELECT B.CNO FROM tea_info A, cur_info B WHERE A.T

26、NO=B.TNO AND A.NAME=李奇) 注意: 子查询不能位于等号之前。 运行结果为： SNO CNO SCORE - - - 021601 005067 91 021635 005067 88 021608 005067 96 021638 005067 91,课程表,45,3.1数据定义数据查询28,例：输出成绩比该课程平均成绩低的学生的成绩信息。 SELECT * FROM sco_info A WHERE SCORE ( SELECT AVG(SCORE) FROM sco_info B WHERE A.CNO=B.CNO ) 结果为： SNO CNO SCORE - - -

27、021608 005036 92 021638 005036 93 021601 005067 91 021635 005067 88 021638 005067 91 021601 005132 87,46,3.1数据定义数据查询29,在处理子查询返回值时，也可以使用ANY和ALL谓词。 例如，ANY表示大于子查询结果中的某一个值； ALL表示大于子查询结果中的所有值。 例：输出其他课程中比005036课程所有分数更低的学号、课程号和成绩。 SELECT * FROM sco_info WHERE SCORE005036,47,3.1数据定义数据查询30,上题用集函数的SQL语句为： SEL

28、ECT * FROM sco_info WHERE SCORE005036 一般来说，采用集函数的SQL语句的查询效率要高一些。,48,3.1数据定义数据查询31,带有EXISTS的子查询 EXISTS谓词表示是否存在的逻辑值。带有EXISTS的子查询不返回任何元组，而是逻辑值真（TURE）或假（FALSE）。 例：输出所有选修了005132课程的学生信息。 SELECT * FROM stu_info WHERE EXISTS ( SELECT * FROM sco_info WHERE SNO=stu_info.SNO AND CNO=005132 ),49,50,3.1数据定义数据查询3

29、2,例：输出未选修005132课程的学生信息。 SELECT * FROM stu_info WHERE NOT EXISTS ( SELECT * FROM sco_info WHERE SNO=stu_info.SNO AND CNO=005132 ) 结果为： SNO NAME GENDER BIRDAY CLASS - - - - - 021635 刘小梅 女 16-JAN-80 机-0203 021638 王珊 女 08-JUN-81 机-0203,51,3.2 数据操纵,插入数据 SQL语言的数据插入使用INSERT语句 INSERT INTO ( , . .) VALUES (

30、, . .) 说明： 如果选择了VALUES前的字段名称，则VALUES后的常量必须与其一一对应；如果没有字段名称，则VALUES后要给出所有字段的值； 常量的表示：如果是字符型或者日期型，其值要用单引号或括起来； 对于在INSERT语句中未出现的字段，记录插入时被赋予NULL；而对应有NOT NULL约束或作为索引的字段，必须给出非空值。,52,3.2 数据操纵,例：向stu_info表中插入一个学生的记录：学号=031156， 姓名=王兵，性别=男，出生日期=1980年5月16日， 专业=计算机。 INSERT INTO stu_info VALUES (031156,王兵,M,#1980

31、-05-16#,计算机),53,例：向stu_info表中插入一个学生的记录： 学号=031123，姓名=李倩，性别=女。 INSERT INTO stu_info (SNO,NAME,GENDER) VALUES (031123,李倩,F),3.2 数据操纵,54,3.2 数据操纵,修改数据 数据修改使用UPDATE语句，格式为： UPDATE SET = , = . . WHERE 说明： UPDATE语句的功能是修改满足WHERE条件的所有记录； 如果没有WHERE，则修改表中所有的记录。 语句中可以嵌套子查询。,55,3.2 数据操纵,例：将学号为031156学生的出生日期改为1981

32、年2月3日。 UPDATE stu_info SET BIRDAY = #1981-02-03# WHERE SNO = 031156,56,3.2 数据操纵,例：将所有学生的出生日期改为1981年2月3日。 UPDATE stu_info SET BIRDAY = #1981-02-03#,57,3.2 数据操纵,删除数据 删除数据使用DELETE语句，格式为： DELETE FROM WHERE 说明： 功能是删除满足WHERE条件的所有记录； 如果没有WHERE，则表中所有的记录都被删除。,58,3.2 数据操纵,例：删除学号为031156学生的记录。 DELETE FROM stu_i

33、nfo WHERE SNO = 031156,59,3.3 视图,视图是一种虚拟的表，视图在定义后，可以和基本表一样在数据查询和数据操纵语句中使用。 但是，数据库中只有视图的定义，并不专门存放视图对应的数据。换句话说，系统在内部自动进行转换，将对视图的一切操作转换为对基本表的操作。 合理使用视图，可以为用户提供数据的不同观察角度，隐蔽不感兴趣的数据，简化用户的操作。,60,3.3 视图,定义视图 定义视图使用CREATE VIEW语句，格式为： CREATE VIEW ( ,. .) AS WITH CHECK OPTION 说明：子查询中不允许含有ORDER BY(排序)和DISTINCT(

34、去除重复)； 组成视图的字段或者全部省略，或者全部指定； WITH CHECK OPTION表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的记录满足视图定义中的子查询条件。,61,3.3 视图,例：建立stu_info表中所有男生的视图M_STU，要求新输入学生后仍保证该视图只有男生。 CREATE VIEW M_STU AS SELECT NO,NAME,ADDRESS FROM stu_info WHERE GENDER=M WITH CHECK OPTION,同步更新,62,3.3 视图,例：找出所有姓张的男生的学号。 SELECT NO FROM M

35、_STU WHERE NAME LIKE 张* 系统在执行这条SQL语句时，实际上先转换为： SELECT NO FROM stu_info WHERE NAME LIKE 张* AND GENDER=M,63,3.3 视图,例：在视图M_STU中更新姓名为王中的学生记录，将其地址更新为上海浦东。 UPDATE M_STU SET ADDRESS=上海浦东 WHERE NAME=王中 系统在执行这条SQL语句时，实际上先转换为： UPDATE stu_info SET ADDRESS=上海浦东 WHERE NAME=王中 AND GENDER=M,64,3.3 视图,删除视图 删除视图使用DROP VI