3第二章关系数据库查询语言SQL

1、第二章 关系数据库标准语言SQLn1。SQL概述nSQL是一种介于关系代数于关系演算之间的结构化查询语言，其功能不仅仅是查询。SQL是一个通用的、功能极强的关系数据库语言。n2。SQL语言的特点n2。1综合统一（集数据定义、操纵、控制功能于一体）n2。2高度非过程化（不需要知道怎么做，只要提出怎么做）n2。3面向集合的操作方式（操作的对象和结果都是集合）n2。4以同一种语法结构提供两种使用方式（既是自含的，又是嵌入的）n2。5语言简洁，易学易用（完成核心功能只用了9个单词）SQLTRANSACT-SQL语言n1。概述nTransact-sql语言是microsoft公司在SQL的基础上进行了一

2、定程度的扩展，适用于MSSQL SERVER数据库管理系统的一门语言。它提供了向SQL的完全兼容，也就是说你可以在可以使用Transact-sql的任何地方使用sql语言。变量n局部变量 是用户自定义的变量， 仅作用于程序内部。 形如： myvariablen全局变量 是sql server内部使用的变量，不局限于某一应用程序，而是任何程序均可随时调用。 形如：rowcount注释符、运算符、通配符 注释符单行注释：-多行注释：/*/运算符n算术运算：+、-、x、/、%n比较运算：、=、=、 、!=、!、!n逻辑运算：and、or、notn位运算 ：&、|、 、n连接运算：+ 运算符的

3、运算顺序运算符的运算顺序通配符通配符%_ 查询单表查询n1。查询全体学生的姓名、学号、所在系。nUse studentnSelect sname,sno,sdeptnFrom student;n2。查询全体学生的详细记录。nUse studentnSelect * from student;n3。查询全体学生的姓名及其出生年份nUse studentnSelect sname ,2002-sage nfrom student;查询单表查询n4。查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。nUse studentnSelect sname,2002-sage as BIRT

4、H YEAR，upper(sdept)nFrom student;n5。查询选修了课程的学生的学号。nUse studentnSelect sno nfrom sc;查询单表查询n6。去掉上一例中结果集中的重复行。nUse studentnSelect distinct snonFrom sc;n7。查询计算机系全体学生的名单。nUse studentnSelect snamenFrom studentnWhere sdept=cs;查询单表查询n8。查询所有年龄在20岁以下的学生姓名及其年龄。nUse student nSelect sname,sagenFrom studentnWhere

5、 sage=20; 查询单表查询n9。查询考试成绩有不及格的学生的学号。nUse student nSelect snonFrom sc nWhere score60;n10。查询年龄在2023之间的学生的姓名、系别、年龄。nSelect sname,sdept,sagenFrom studentnWhere sage between 20 and 23;查询单表查询n11。查询IS系、CS系学生的姓名和性别。nUse studentnSelect sname,ssexnFrom studentnWhere sdept IN（IS,CS）;n12。查询学号为20021的学生的详细情况。nSel

6、ect * nFrom studentnWhere sno like 20021;或查询单表查询nSelect * nFrom studentnWhere sno=20021;n使用统配符_,%进行查询。n13。查询所有以字母s开头的学生的姓名、学号和性别。nUse student nSelect sname,sno,ssexnFrom studentnWhere sname like s%；查询单表查询n14。查询姓“欧阳”且全名为三个汉字的学生的姓名。nUse studentnSelect snamenFrom studentnWhere sname like 欧阳_;n15。查询名字中第

7、2个字为阳的学生的姓名和学号。nSelect sname,snonFrom studentnWhere sname like _阳%;查询单表查询n16。查询以“DB_”开头，且倒数第三个字符为I的课程的详细情况。nSelect *nFrom coursenWhere cname like DB_%I_ escape ;n涉及空串的查询。n17。查询缺少成绩的学生的学号和相应的课程号。nSelect sno ,cnonFrom scnWhere grade is null;查询单表查询n18。查询所有有成绩的学生的学号和课程号。nSelect sno ,cnonFrom scnWhere gr

8、ade is not null;n用and和or惊醒多重条件查询n19。查询计算机系所有年龄在20岁以下的学生的姓名。nSelect snamenFrom studentnWhere sdept=cs and sage20;查询单表查询n例11中的IN谓词实际上是多个or运算符的缩写，因此，例11中的查询等价于如下形式：nSelect sname,ssexnFrom studentnWhere sdept=is or sdept=ma or sdept=cs;n对查询结果进行排序，用户可以用order by子句对查询结果按照一个或多个属性列的升序（asc）或降序（desc）排列，缺省情况下为升

9、序。n20。查询选修了103号课程的学生的学号及其成绩，查询结果按分数的降序排列。查询单表查询nSelect sno,scorenFrom scnWhere cno=103nOrder by score desc;n对于空值，若按升序排，含空值的元素降最后显示。否则，将最先显示。n21。查询全体学生的情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。nSelect * nFrom studentnOrder by sdept,sage desc;查询单表查询n使用集函数nCOUNT(DISTINCT|ALL *)统计元组个数nCOUNT(DISTINCT|ALL)统计一列中值

10、的个数nSUM(DISTINCT|ALL)计算一列值的和nAVG(DISTINCT|ALL)计算一列值的平均值nMAX(DISTINCT|ALL)计算一列之中的最大值nMIN(DISTINCT|ALL)计算一列之中的最小值查询单表查询n22。查询学生人数。nSelect count(*)nFrom student;n23。查询选修了课程的学生人数。nSelect count(distinct sno)nFrom scn24。计算101号课程的学生的平均成绩。nSelect avg(score)nFrom scnWhere cno=101;查询单表查询n对查询结果分组n24。求各个课程号及其相应

11、的选课人数nSelect cno,count(sno)nFrom scnGroup by cno;n如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用having短语指定筛选条件。n25。查询选修了3门以上课程的学生的学号。nSelect snon from sc nGroup by sno nhaving count(*) 3;连接查询n前面的查询都是针对一个表进行的，托一个查询同时涉及两个或两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接查询、外连接查询和复合条件连接查询。连接查询n等值与非等

12、值连接查询。n1。查询每个学生及其选课情况。nSelect student.*,sc.*nFrom student,scnWhere student.sno=sc.sno;n2。对上例用自然连接完成。nSelect student.sno,sname,ssex,sage,sdept,cno,scorenFrom student,scnWhere student.sno=sc.sno;连接查询n自身连接n3。查询每一门课的间接先修课。nSelect o,second.cpnonFrom course as first,course as secondnWhere first.cpno=on外连接

13、n4。分为：left outer join,full outer join,right outer joinnSelect sx.sno,sname,ssex,sage,sdept,cno,scorenFrom student as sx full outer join sc as scxnon sx.sno=scx.sno连接查询n复合条件连接n5。查询选修102号课程，且成绩在90分以上的所有学生。nSelect sx.sno,snamenFrom student as sx,sc as scxnWhere sx.sno=scx.sno and o=102 and scx.score=90

14、;n嵌套查询n6。查询与rita在同一个系学习的学生。nSelect sno,sname,sdeptnFrom student nWhere sdept inn(select sdept from student where sname=rita);连接查询n7。查询选修了课程名为“math”的学生学号和姓名。nSelect sno,sname from student Where sno inn (select sno from sc Where cno inn (select cno from course Where cname=mathn )n );n本查询可以用连接查询实现：nSel

15、ect sx. sno,snamenFrom student sx,sc scx,course cxnWhere sx.sno=scx.sno and o=o and ame=math;连接查询n带有ANY和ALL谓词的子查询nANY 大于某个（大于最小的）nALL 大于所有（大于最大的）nALL 小于所有（小于最小的）n=ANY 等于某个n!=ALL 不等于任何一个连接查询n8。查询其他系中比信息系某一学生年龄小的学生姓名和年龄。nSelect sname,sagenFrom studentnWhere sageANY(select sage n from student where sde

16、pt=IS)n and sdeptIS;连接查询n带有exists谓词的子查询n9。查询没有选修101号课程的学生的姓名。nSelect snamenFrom student sxnWhere not exists(select *n from sc scxn where sx.sno=scx.sno and o=101);连接查询n集合查询n10。查询选修了101号或者选修了102号课程的学生学号。nSelect sno from scnWhere cno=101nUnionnSelect sno from scnWhere cno=102;定义、删除与修改基本表n1。定义基本表（简单示例）

17、nCreate table student1n(sno char(10) not null unique,nSname char(10) unique,nSsex char(10),nSage int ,nSdept char(10),nSmonitor char(10),nConstraint sage_chk check(sage=18),nConstraint Sno_pk primary key(sno),nConstraint smo_fk foreign key(smonitor) references student1(sno)n)定义、删除与修改基本表n1。修改基本表（简单示例

18、）n1.1向student1表中加入入学时间nAlter table student1 add senroll datetime;n1.2将年龄的数据类型改为tinyint类型。n先在企业管理器中删除sage字段上的check约束。nAlter table student1 alter column sage tinyint;n2。删除表（简单示例）n删除student1表nDrop table student1;定义、删除与修改基本表n1。具体复杂示例请察看online booksn在index 栏中输入create table即可察看示例n请按照所列示例动手实际操作一下。n2。Sql se

19、rver中的五种约束：n2.1 primary keyn2.2 foreign keyn2.3 uniquen2.4 checkn2.5 default(不能在建表时定义缺省约束，只能添加)定义、删除与修改基本表n1。处于下列情况的列不能删除n1.1用于复制的列。n1.2用于索引的列。n1.3用于约束的列。n1.4绑定了规则的列。n2。在删除一个表之前，要先删除与此表相关联的表中的外关键字约束。当删除表后，再次表上绑定的规则会自动松绑。定义、删除与修改基本表n以下形式的列不能修改数据类型n1。列的数据类型为text、image、ntext、timestampn2。计算列或被计算列使用的列n3。

20、被复制的列n4。用于索引的列。如列为varchar或varbinary，则只能加大其长度。n5。用户统计信息的列。n6。Pk或fk约束的列。n7。Check或unique约束的列。n8。与一个缺省约束关联的列。n9。New_data_type索引有关n1。概述n建立索引是加快查询速度的有效手段。用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多种存取路径，加快查询速度。n2。主要分为簇索引和非簇索引n2.1所谓簇索引，是指索引项的顺序与表中记录的物理顺序一致的索引组织。n2.2而sort排序并不影响物理存储，只是建立sort操作之后的临时表存放于tempdb中，属于非簇索引。n

21、2.3对于经常更新的列不宜建立聚簇索引，否则会导致表中记录的物理顺序的经常变更，代价太大。n2.4一个基本表上只能建立一个聚簇索引。建立与删除索引n1。建立索引（简单示例）n为学生-课程数据库中的student,course,sc三个表建立索引。其中，student表按学号升序建唯一索引，course表按课程号升序建唯一索引，sc表按学号升序和课程号降序建唯一索引。nCreate unique index stusno on student(sno);nCreate unique index coucno on course(cno);nCreate unique index scno on

22、sc(sno asc,cno desc);n以上创建的均为非簇索引。n2。删除索引（简单示例）n在企业管理器中删除。n3。复杂的请查询books online。建立与删除索引n建立索引注意事项：n1。数据类型为text、ntext、bit的列不能见索引。n2。若变长数据类型超过了900个char时，不能建索引。n3。在使用索引创建向导创建索引时，不能将计算列包含在索引中，但在直接创建或使用create index命令时，可以对计算列创建索引，这是sqlserver2000的一大改进。数据更新插入数据n1。插入单个元组。（简单示例）nInsert nInto student(sno,sname,

23、ssex,sdept,sage)nValues(20041,jack,Male,IS,18);nInsertnInto sc(sno,cno)nValues(20041,101);数据更新插入数据n2。插入子查询结果。n新建一张表deptagenCreate table deptagen(sdept char(15),nAvgage smallint)ngonInsert nInto deptagen select sdept,avg(sage)n from student group by sdeptngo数据更新修改数据n带子查询的修改语句n3。将计算机科学系所有学生的成绩置零。nUpda

24、te scnSet score=0nWhere cs=(select sdeptn from studentn where student.sno=sc.sno)ngo数据更新删除数据n4。删除学号为“20041”的学生纪录。nDelete nFrom studentnWhere sno=20041ngon5。删除所有的纪录。nDelete nFrom studentngo视图n例1 建立信息系学生的视图。nCreate view is_studentnAs nSelect sno,sname,sagenFrom studentnWhere sdept=is视图例2 建立信息系学生的视图，并要

25、求进行修改和插入操作时仍需保证该视图只有信息系的学生。Create view is_student1AsSelect sno,sname,sageFrom studentWhere sdept = isWith check optiongo视图例3 建立信息系选修了101号课程的学生的视图Create view is_s1(sno,sname,score)AsSelect student.sno,sname,scoreFrom student,scWhere sdept=is and student.sno=sc.sno and o=101go视图例4 建立信息系选修了101号课程且成绩在90

26、分以上的学生的视图。这里的视图要建立在视图is_s1之上Create view is_s2AsSelect sno,sname,scoreFrom is_s1Where score=90go视图n定义基本表时，为了减少数据库中的冗余数据，表中只存放基本数据，由基本数据经过各种计算派生出的数据一般是不存储的。但由于视图中的数据并不实际存储，所以定义视图的时候可以根据应用的需要，设置一些派生属性列。这些派生属性由于在基本表中并不实际存在也称他们为虚拟列。带虚拟列的视图也称为带表达式的视图。n例5 定义一个反映学生出生年份的视图nCreate view bt_s(sno,sname,sbirth)n

27、AsnSelect sno,sname, 2002-sagenFrom studentngo视图n还可以用集函数和group by字句的查询来定义视图，这种视图称为分组视图。n例6 将学生的学号以及它的平均成绩定义为一个视图nCreate view s_g(sno,savg)nAsnSelect sno, avg(score)nFrom scnGroup by snongo视图n1。使用 with encryption、with check option选项，并且包含函数列。nUse pubsnCreate view emprange(enp_id,fname,lname,pubid,job_

28、id,rows)nWith encryptionnAsnSelect emp_id,fname,lname,pub_id,job_id,rowcountnFrom enployeenWhere job_id between 11 and 12nWith check optionnGo视图n若执行以下语句：nUpdate emprangenSet job_id=5nWhere emp_id=PCM98509Fn由于在视图的定义中使用了with check option，所以当对视图进行修改时，将进行检查。本例中返回了错误信息。n若执行以下语句：nUse pubsnGo nSelect c.id, c.textnFrom syscomments as c,sysobjects o