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

1、第四章 关系数据库标准语言SQL,4.1 SQL概述 4.2 数据定义 4.3 数据查询 4.4 数据更新 4.5 视图,第四章 关系数据库标准语言SQL,4.1 SQL 概述 SQL（Structed Query Language），即结构化查询语言，是关系数据库的标准语言，SQL是一个通用的、功能极强的关系数据库语言。,4.1.1 SQL的产生与发展,SQL是在1974年由Boycee和Chamberlin提出的，并在IBM公司研制的关系数据库管理系统原型System R上实现。 由于SQL简单易学，功能丰富，深受用户及计算机工业界欢迎，因此被数据库厂商所采用。经各公司的不断修改、扩充和完

2、善，SQL得到业界的认可。 1986年10月美国国家标准局的数据库委员会X3H2批准了SQL作为关系数据库语言的美国标准。同年公布了SQL标准文本。 1987年国际标准化组织也通过了这一标准。 SQL标准从1986年公布以来随着数据库技术的发展不断发展，不断丰富。,4.1.2 SQL的特点,一、综合统一 SQL集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。 二、高度非过程化 SQL进行数据操作，只要提出“做什么”,而无需指明“怎么做”,因此无需了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。,4.

3、1.2 SQL的特点,三、面向集合的操作方式 SQL采用面向集合的操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。 四、以同一种语法结构提供多种使用方式 作为独立语言，它能够独立地用于联机交互的使用方式，用户可以在终端键盘上直接键入SQL命令对数据库进行操作； 作为嵌入式语言，SQL语句能够嵌入到高级语言（例如C，C+，Java）程序中，供程序员设计程序时使用。,4.1.2 SQL的特点,五、语言简洁易学 SQL功能极强，但由于设计巧妙，语言十分简洁，完成核心功能只用了9个动词。SQL接近英语口语，因此容易学习，容易使用。,4.2数据定义

4、,4.2.1基本表的定义、删除、与修改 一、定义基本表 CREATE TABLE （ 列级完整性约束条件 , 列级完整性约束条件 , ）；,4.2数据定义,例:建立一个“学生”表Student。 create table Student ( sno char(9) primary key, sname char(20) unique, ssex char(2), sage smallint, sdept char(20) );,4.2数据定义,例:建立一个“课程”表Course。 create table Course ( cno char(4) primary key, cname char

5、(40), cpno char(4), ccredit smallint, foreign key (cpno) references Course(cno) );,4.2数据定义,例：建立学生选课表SC。 create table SC ( sno char(9), cno char(4), grade smallint, foreign key (sno) references student(sno), foreign key (cno) references course(cno) ),4.2数据定义,4.2.2修改基本表 ALTER TABLE ADD 完整性约束 DROP ALTE

6、R COLUMN ；,4.2数据定义,例：向Student表增加“入学时间”列，其数据类型为日期型。 alter table Student add s_entrance datetime; 例：将年龄的数据类型由smallint改为int。 alter table Student alter column sage int;,4.2数据定义,例：增加课程名必须取唯一值的约束。 alter table course add unique(cname);,4.2数据定义,4.2.3删除基本表 DROP TABLE RESTRICT|CASCADE RESTRICT:该表的删除是有限制条件的。欲删

7、除的基本表不能被其他表的约束所引用(如CHECK,FOREIGN KEY等约束)，不能有视图，不能有触发器，不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除。 CASCADE:该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。 (SQL Server 2000中不支持RESTRICT|CASCADE),4.2数据定义,4.2.3索引的建立与删除 建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。 建立与删除索引有数据库管理员DBA或表的属主，负责完成。,4.2数据

8、定义,4.2.3索引的建立与删除 一、建立索引 CREATE UNIQUECLUSTER INDEX ON （, ） 索引的排列次序，可选ASC或DESC，缺省值为ASC。 UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。 CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织。,4.2数据定义,二、删除索引 DROP INDEX 例：索引示例。 create table abc ( a int, b int ); create unique index abcindex on abc(a desc); drop index abc.

9、abcindex; create clustered index abcindex on abc(a desc);,4.3数据查询,SELECT ALL|DISTINCT, FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;,4.3.1单表查询,单表查询是指仅涉及一个表的查询。 一、选择表中的若干列 1.查询指定列 例1.查询全体学生的学号和姓名。 select sno,sname from student; 例2.查询全体学生的姓名、学号、所在系。 select sname,sno,sdept from student;,4.3.1单表查询,2.查

10、询全部列 例3.查询全体学生的详细记录。 select * from student; 3.查询经过计算的值 例4.查询全体学生的姓名及其出生年份。 select sname,2012-sage from student; 例5.查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有系名。 select sname,year of birth:,2012-sage,lower(sdept) from student;,4.3.1单表查询,二、选择表中的若干元组 1.消除取值重复的行 例6.查询选修了课程的学生学号。 select sno from sc; select distinc

11、t sno from sc; 2.查询满足条件的元组 （1） 比较大小 例7.查询计算机科学系全体学生的名单。 select sname from student where sdept=CS;,4.3.1单表查询,例8.查询所有年龄在20岁以下的学生姓名及其年龄。 select sname,sage from student where sage20; 例9.查询考试成绩有不及格的学生的学号。 select distinct sno from sc where grade60;,4.3.1单表查询,（2）确定范围 例10.查询年龄在20-23岁（包括20岁和23岁）之间的学生的姓名、系别和年

12、龄。 select sname,sdept,sage from student where sage between 20 and 23; 例11.查询年龄不在20-23岁之间的学生姓名、系别和年龄。 select sname,sdept,sage from student where sage not between 20 and 23;,4.3.1单表查询,（3）确定集合 例12.查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。 select sname,ssex from student where sdept in(CS,MA,IS); 例13.查询既不是计算

13、机科学系、数学系，也不是信息系的学生的姓名和性别。 select sname,ssex from student where sdept not in(CS,MA,IS);,4.3.1单表查询,（4）字符匹配 谓词LIKE可以用来进行字符串的匹配。 NOT LIKE ESCAPE %代表任意长度的字符串（长度可以为0）； -（下横线）代表任意单个字符。,4.3.1单表查询,例14.查询学号为200215121的学生的详细情况。 select * from student where sno like 200215121; 例15.查询所有姓刘的学生的姓名、学号和性别。 select * fro

14、m student where sname like 刘%; 例16.查询姓“欧阳”且全名为3个汉字的学生的姓名。 select sname from student where sname like 欧阳_;,4.3.1单表查询,例17.查询名字中第2个字为“阳”字的学生的姓名和学号。 select sname,sno from student where sname like _阳%; 例18.查询所有不姓刘的学生姓名。 select sname from student where sname not like 刘%;,4.3.1单表查询,例19.查询DB_Design课程的课程号和学分

15、。 select cno,ccredit from course where cname like DB_Design escape ; 例20.查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。 select * from course where cname like DB_%i_ escape ;,4.3.1单表查询,（5）涉及空值的查询 例21.查询缺少成绩的学生的学号和相应的课程号。 select sno,cno from sc where grade is null; 例22.查所有有成绩的学生学号和课程号。 select sno,cno from sc where gr

16、ade is not null;,4.3.1单表查询,（6）多重条件查询 例23.查询计算机科学系年龄在20岁以下的学生姓名。 select sname from student where sdept=CS and sage20;,4.3.1单表查询,三、ORDER BY子句 例24.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 select sno,grade from sc where cno=3 order by grade desc; 例25.查询全体学生情况，查询结果按所在系的系号升序排列，统一系中的学生按年龄降序排列。 select * from stude

17、nt order by sdept,sage desc;,4.3.1单表查询,四、聚集函数 count(列名) max(列名) min(列名) sum(列名) avg(列名),4.3.1单表查询,例26.查询学生总人数。 select count(*) from student; 例27.查询选修了课程的学生人数。 select count(distinct sno) from sc; 例28.计算1号课程的学生平均成绩。 select avg(grade) from sc where cno=1;,4.3.1单表查询,五、GROUP BY子句 GROUP BY子句将查询结果按某一列或多列的值

18、分组，值相等的为一组。 例31. 求各个课程号及相应的选课人数。 select cno,count(sno) from sc group by cno;,4.3.1单表查询,例32.查询选修了3门以上课程的学生学号。 select sno from sc group by sno having count(*)3;,4.3.2 连接查询,若一个查询同时涉及两个以上的表，则称之为连接查询。 一、等值与非等值连接查询 连接查询的WHERE子句用来连接两个表的条件称为连接条件或连接谓词。 . 其中比较运算符主要有：=、=、)等。 此外连接谓词还可以使用下面形式： .BETWEEN.AND .,4.3

19、.2 连接查询,当连接运算为=时，称为等值连接。使用其他运算符称为非等值连接。 连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。 例33.查询每个学生及其选修课程的情况。 select student.*,sc.* from student,sc where student.sno=sc.sno;,4.3.2 连接查询,例34. 对例33用自然连接完成。 select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;,4.3.2 连接查

20、询,二、自身连接 连接操作不仅可以在两个表之间进行，也可以是一个表与自己进行连接，称为表的自身连接。 select o,second.cpno from course first,course second where first.cpno=o;,4.3.2 连接查询,例36.以student为主体列出每个学生的基本情况及其选课情况。 select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on student.sno=sc.sno,4.3.2 连接查询,四、复合条件连接 例37.查询

21、选修2号课程且成绩在90分以上的所有学生。 select student.sno,sname from student,sc where student.sno=sc.sno and o=2 and sc.grade90;,4.3.2 连接查询,四、复合条件连接 例38.查询每个学生的学号、姓名、选修的课程名及成绩。 select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and o=o;,4.3.3嵌套查询,在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块

22、。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。 select sname from student where sno in ( select sno from sc where cno=2 );,4.3.3嵌套查询,一、带有IN谓词的子查询。 例39.查询与“刘晨”在同一个系学习的学生。 select sno,sname,sdept from student where sdept in ( select sdept from student where sname=刘晨 );,4.3.3嵌套查询,例40.查询选修了课程名为“信息系统”的学生学

23、号和姓名。 select sno,sname from student where sno in ( select sno from sc where cno in ( select cno from course where cname=信息系统 ) );,4.3.3嵌套查询,子查询的查询条件不依赖于父查询，这类子查询叫作不相关子查询。 子查询的查询条件依赖于父查询，这类子查询称为相关子查询。,4.3.3嵌套查询,二、带有比较运算符的子查询 例41.找出每个学生超过他选修课程平均成绩的课程号。 select sno,cno from sc x where grade ( select avg

24、(grade) from sc y where y.sno=x.sno ),4.3.3嵌套查询,三、带有any或all谓词的子查询 子查询返回单值时可以用比较运算符，但返回多值时要用ANY或ALL谓词修饰符。而使用ANY或ALL谓词时则必须同时使用比较运算符。 例42.查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。 select sname,sage from student where sage CS,4.3.3嵌套查询,例43.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。 select sname,sage from student where sage CS,

25、4.3.3嵌套查询,例45.查询没有选修1号课程的学生姓名。 select sname from student where not exists ( select * from sc where sno=student.sno and cno=1 ),4.3.3嵌套查询,例46 查询选修了全部课程的学生姓名。 可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。,4.3.3嵌套查询,select sname from student where not exists ( select * from course where not exists( selec

26、t * from sc where sc.sno=student.sno and o=o ) ),4.3.3嵌套查询,例47.查询至少选修了学生200215122选修的全部课程的学生号码。 本查询可以用逻辑蕴含来表达：查询学号为x的学生，对所有的课程y，只要200215122学生选修了课程y，则x也选修了y。 用p表示谓词“学生200215122选修了课程y” 用q表示谓词“学生x选修了课程y” 则上述查询为,4.3.3嵌套查询,该查询可以转换为如下等价形式,它所表达的语义为：不存在这样的课程y,学生200215122选修了y,而学生x没有选。,4.3.3嵌套查询,select distinc

27、t sno from sc x where not exists ( select * from sc y where y.sno=200215122 and not exists( select * from sc z where z.sno=x.sno and o=o ) ),4.3.4集合查询,例48.查询计算机科学系的学生及年龄不大于19岁的学生。 select * from student where sdept=CS union select * from student where sage=19,4.3.4集合查询,例49.查询选修了课程1或者选修了课程2的学生。 select

28、 sno from sc where cno=1 union select sno from sc where cno=2,4.3.4集合查询,例50.查询计算机科学系的学生与年龄不大于19岁的学生的交集。 select * from student where sdept=CS intersect select * from student where sage=19/sql server2000不支持intersect,4.3.4集合查询,例51查询既选修了课程1又选修了课程2的学生。 select sno from sc where cno=1 intersect select sno

29、from sc where cno=2 /sql server2000不支持intersect,4.3.4集合查询,例52.查询计算机科学系的学生与年龄不大于19岁的学生的差集。 select * from student where sdept=CS except select * from student where sage=19 /sql server2000不支持except,4.4数据更新,数据更新操作有3种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。 4.4.1插入数据 SQL的数据插入语句INSERT通常有两种形式。 插入一个元组 插入子查询结果,4.4数据更

30、新,4.4.1插入数据 一、插入元组 插入元组的INSERT语句的格式为 INSERT INTO(,) VALUES(,);,4.4数据更新,例1.将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。 insert into student(sno,sname,ssex,sdept,sage) values(200215128,陈冬,男,IS,18),4.4数据更新,例2.将学生张成民的信息插入到Student表中。 insert into student values(200215126,张成民,男,18,CS),4.4数据

31、更新,例3.插入一条选课记录（200215128，1） insert into sc(sno,cno) values(200215128,1) insert into sc values(200215128,1,null),4.4数据更新,二、插入子查询结果 INSERT INTO (,) 子查询；,4.4数据更新,例4.对每一个系，求学生的平均年龄，并把结果存入数据库。 首先在数据库中建立一个新表，其中一列存放系名，另一列 存放相应的学生平均年龄。 create table Dept_age ( sdept char(5), Avg_age smallint );,4.4数据更新,然后对st

32、udent表按系分组求平均年龄，再把系名和平均年龄存入新表中。 insert into Dept_age(sdept,Avg_age) select sdept,avg(sage) from student group by sdept;,4.4数据更新,4.4.2修改数据 修改操作又称为更新操作，其语句一般格式为 UPDATE SET =,= WHERE ;,4.4数据更新,一、修改某一个元组的值 例5.将学生200215121的年龄改为22岁。 update student set sage=22 where sno=200215121,4.4数据更新,二、修改多个元组的值 例6.将所有学

33、生的年龄增加1岁。 update student set sage=sage+1;,4.4数据更新,三、带子查询的修改语句 例7.将计算机科学系全体学生的成绩置零。 update sc set grade=0 where CS= ( select sdept from student where student.sno=sc.sno ),4.4数据更新,4.4.3删除数据 一般格式 DELETE FROM WHERE ；,4.4数据更新,一、删除一个元组的值 例8.删除学号为200215128的学生记录 delete from student where sno=200215128;,4.4数

34、据更新,二、删除多个元组的值 delete from sc;,4.4数据更新,三、带有查询的删除语句 例10.删除计算机科学系所有学生的选课记录。 delete from sc where CS= ( select sdept from student where student.sno=sc.sno );,4.5视图,视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。 视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操作则有一

35、定的限制。,4.5.1定义视图,一、建立视图 CREATE VIEW (,) AS WITH CHECK OPTION 子查询可以是任意复杂的SELECT语句。但通常不允许含有ORDER BY子句和DISTINCT短语。 WITH CHECK OPTION表示对视图进行UPDATE,INSERT,DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）,4.5.1定义视图,create view is_student as select sno,sname,ssex from student where sdept=IS insert into is_st

36、udent values(200215129,王六,男),4.5.1定义视图,插入语句可以执行。 select * from is_student select * from student,4.5.1定义视图,create view is_student as select sno,sname,ssex from student where sdept=IS with check option,4.5.1定义视图,insert into is_student values(200215129,王六,男) 服务器: 消息 550，级别 16，状态 1，行 1 试图进行的插入或更新已失败，原因是

37、目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束的条件。 语句已终止。,4.5.1定义视图,子查询中带有order by 子句 CREATE VIEW FSTUDENT AS SELECT * FROM STUDENT WHERE SSEX=女 order by sage 服务器: 消息 1033，级别 15，状态 1，过程 FSTUDENT，行 5 除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。,4.5.1定义视图,CREATE VIEW FSTUD

38、ENT AS SELECT top 10 * FROM STUDENT WHERE SSEX=女 order by sage,4.5.1定义视图,子查询中含有DISTINCT短语 create view abc as select distinct sno from sc where grade is null,4.5.1定义视图,组成视图的属性列名或者全部省略或者全部指定。如果省略了视图的各个属性列名，则隐含该视图由子查询中的SELECT子句目标列中的诸字段组成。但在下列三种情况下必须明确指定组成视图的所有列名。 （1）某个目标列不是单纯的属性名，而是聚集函数或列表达式；,4.5.1定义视图

39、,create view test1 as select sno,count(*) from sc group by sno 服务器: 消息 4511，级别 16，状态 1，过程 test1，行 3 创建视图或函数失败，因为没有为第 2 列指定列名。,4.5.1定义视图,create view test2 as select sname,2012-sage from student 服务器: 消息 4511，级别 16，状态 1，过程 test2，行 3 创建视图或函数失败，因为没有为第 2 列指定列名。,4.5.1定义视图,（2）多表连接时选出了几个同名列作为视图的字段 （sql serve

40、r 2000中不符合） create view test3 as select student.sno,cno,grade from student,sc where student.sno=sc.sno,4.5.1定义视图,（3）需要在视图中为某个列启用新的更适合的名字。 create view test4(姓名,年龄) as select sname,sage from student,4.5.1定义视图,例1.建立信息系学生的视图。 create view is_student as select sno,sname,sage from student where sdept=IS,4.

41、5.1定义视图,例2.建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。,create view IS_Student1 as select sno,sname,sage from student where sdept=IS with check option,4.5.1定义视图,例3.建立信息系选修了1号课程的学生的视图。 create view IS_S1(sno,sname,grade) as select student.sno,sname,grade from student,sc where sdept=IS and student.sno=sc.s

42、no and o=1,4.5.1定义视图,例4.建立信息系选修了1号课程且成绩在90分以以上的学生的视图。,create view IS_S2 as select sno,sname,grade from IS_S1 where grade=90,4.5.1定义视图,例5.定义一个反映学生出生年份的视图。 create view BT_S(sno,sname,sbirth) as select sno,sname,2012-sage from student,4.5.1定义视图,例6.将学生的学号及他的平均成绩定义为一个视图。 create view S_G(Sno,Gavg) as sele

43、ct sno,avg(grade) from sc group by sno,4.5.1定义视图,若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这视图为行列子集视图。 在基本表中并不实际存在的列称为虚拟列。,带虚拟列的视图也称为带表达式的视图。,4.5.1定义视图,二、删除视图 DROP VIEW CASCADE; 例8. drop view bt_s,4.5.2查询视图,例9.在信息系学生的视图中找出年龄小于20岁的学生。,select sno,sage from is_student where sage20,4.5.1查询视图,例10.查询选修

44、了1号课程的信息系学生。 select IS_student.sno,sname from is_student,sc where is_student.sno=sc.sno and o=1,4.5.2查询视图,例11.在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。,select * from s_g where gavg=90,4.5.3更新视图,例12将信息系学生视图IS_Student中学号为200215122的学生姓名改为“刘晨”。,update is_student set sname=刘辰 where sno=200215122,4.5.3更新视图,例13向信息系学生

45、视图is_student中插入一个新的学生记录，其中学号为200215129，姓名为赵新，年龄为20岁。 insert into is_student values(200215129,赵鑫,20),4.5.3更新视图,例14.删除信息系学生视图IS_Student中学号为200215129的记录。 delete from is_student where sno=200215125,4.5.3更新视图,在关系数据库中，并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新。 把视图S_G中学号为200215122的学生的平均成绩改成90分。 update

46、s_g set gavg=90 where sno=200215121 服务器: 消息 4403，级别 16，状态 1，行 1 视图或函数 s_g 不可更新，因为它包含聚合。,4.5.3更新视图,在视图中修改数据的几个问题 对于多表视图不能使用DELETE 语句 create view score_view(sname,cno,grade) as select sname,cno,grade from student,sc where student.sno=sc.sno,4.5.3更新视图,delete from score_view 服务器: 消息 4405，级别 16，状态 1，行 1 视图或函数 score_view 不可更新，因为修改会影响多个基表。,4.5.3更新视图,除非底层表的所有非空列都已经在视图中出现否则不能使用INSERT 语句。有这个限制的原因是SQL 不知道应该将什么数据插入到NOT NULL 限制列中。 cr