计算机数据库课件第三章关系数据库标准语言

第三章关系数据库标准语言SQL（StructuredQueryLanguage）结构化查询语言，是关系数据库的标准语言。学习建议：一定要习惯使用帮助！本课程以SQLServer2005为应用背景。SQLServer2005的安装和启动1、安装。2、SQLServer服务的启动开始→程序→MicrosoftSQLServer2005→配置工具→SQLServerConfigurationManager3、启动开始→程序→MicrosoftSQLServer2005→SQLServerManagementStudio4、两种身份验证：Windows身份验证和SQLServer身份验证。SQLServer2005系统数据库1、master数据库：主要记录了SQLServer的所有系统信息。2、tmpdb数据库：为临时表和其他临时存储需求提供存储空间。3、model数据库：建立所有用户数据库的模板。4、msdb数据库：数据库在SQLServer代理程序调度报警和作业时使用。数据库的创建与使用

createdatabasetest

on(name=testdata,filename='e:\lxd\td.mdf')

logon(name=testlog,filename='e:\lxd\tdlog.ldf')参数name：数据库文件逻辑名，它必须在全部数据库逻辑名中唯一。参数filename：存储数据和日志的物理文件名及路径。注意：数据文件后缀mdf，日志文件后缀ldf。

createdatabasenew_db

onprimary

(

name=new_db,

filename='d:\new_db.mdf',

size=5mb,

maxsize=50mb,

filegrowth=10%

)该命令自动建立日志文件。use命令：

usenew_db使用new_db数据库。数据库的修改与维护1、修改数据库数据文件的初始大小：

alterdatabasenew_db

modifyfile

(

name=new_db,

size=15mb

)

注意：数据库逻辑名必须与最初数据库定义时的逻辑名一致。2、修改数据库名：

execsp_renamedb'new_db',

'old_db'

调用存储过程，第一个参数为原数据库名，第二个参数为新数据库名。3、删除数据库：

dropdatabaseold_db4、查看系统中有哪些数据库

execsp_helpdb5、查看new_db数据库定义信息

execsp_helpdbnew_db数据库的分离、附加与备份、还原1、分离数据库：

execsp_detach_db

new_db2、附加数据库：

createdatabasenew_dbon

(filename='d:\new_db.mdf'

),

(filename='d:\new_db_log.ldf'

)forattach3、备份数据库：

backupdatabasenew_dbtodisk='e:\ll\newdb.bak'4、还原数据库：

restoredatabasenew_dbfromdisk='e:\ll\newdb.bak'关系模式的设计属性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)约束PrimarykeyNotnullStudent属性名CnoCnameCpnoCcredit域char(4)Char(20)Char(4)smallint约束PrimarykeyUniqueForeignkeyCourse属性名SnoCnoCrade域Char(9)Char(4)smallint约束ForeignkeyForeignkeyPrimarykeySC注意：具有参照关系的属性所对应的域必须一致。关系模式的定义必须小心仔细！考虑数据的类型、大小；考虑数据值在应用中的各种情况。常用数据类型常用数据类型含义char(n)长度为n的定长字符串varchar(n)最大长度为n的变长字符串bigint大整型（8字节）int整型（4字节）smallint短整型（2字节）tinyint微整型（1字节）decimal(p,s)numeric(p,s)定点数，由p位十进制数位（小数位占s位）构成的数。1<=p<=38，默认为19；0<=s<=p，默认为0。money8字节整数，精确到万分之一。smallmoney4字节整数，精确到万分之一。float[(n)]浮点数，尾数位数为n，1<=n<=53，默认为53。real等同于float(24)datetime时间日期型。格式化为YYY-MM-DDHH:MM:SS。精确到3.33毫秒。smalldatetime同datetime，精确到1分钟。text存储大容量长度可变字符串

数据类型决定了数据在计算机中的存储格式、长度和精度。基本表的定义createtable

Student(

Sno

char(9)

primarykey,

Sname

char(20)notnull,

Ssex

char(2),

Sage

smallint,

Sdept

char(20)

)属性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)约束PrimarykeyNotnullStudent问题：该列取值可以重复吗？查看表的基本定义信息：

execsp_helpstudent表主码的定义。基本表的定义（续）createtableCourse(

Cno

char(4)

primarykey,

Cname

char(20)unique,

Cpno

char(4),

Credit

smallint,

foreignkey(Cpno)

references

Course(Cno))属性名CnoCnameCpnoCcredit域char(4)Char(20)Char(4)smallint约束PrimarykeyUniqueForeignkeyCoursecreatetableSC(

Sno

char(9),

Cno

char(4),

Grade

smallint,

primarykey

(Sno,Cno),

foreignkey(Sno)

references

Student(Sno),

foreignkey(Cno)

references

Course(Cno))属性名SnoCnoCrade域Char(9)Char(4)smallint约束ForeignkeyForeignkeyPrimarykeySC问题：该列取值可以为空吗？参照完整性的定义。表主码的定义基本表数据的录入SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19CS2008003王敏女18MA2008004张立男19ISStudentCnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64CourseSnoCnoCrade20080011922008001287200800214520080033SC你可以试图输入一些非法数据。问题：创建一个无任何约束的表，录入多个数据相同的行会怎样？基本表的常用操作1、表重命名

execsp_rename'student',

'stu'2、列重命名

execsp_rename'student.sname',

'name',

'column'3、添加新列

altertablestudent

add

sentrance

datetime注意：新增列的数据为空。4、更改列数据类型

altertablestudentaltercolumnsageint注意：若表中已有数据，数据类型不相容时不能进行列数据类型的更改。5、删除列

altertablestudentdropcolumnsentrance删除表

droptablestudent注意：若该表与其它表关联（如外键的参照等），则删除失败。什么是索引SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19CS2008003王敏女18MA2008004戴旭男19ISStudentSnamePointer戴旭李勇刘晨王敏唯一索引SdeptIndexSdeptPointerCSISMA由于Sno为主码，故在基本表记录上自动进行直接物理排序（聚集索引）。由于Sname属性有unique约束，故自动建立一个唯一索引。属性名SnoSnameSsexSageSdept域Char(9)Char(20)Char(2)smallintChar(20)约束PrimarykeyuniqueStudent新建的索引索引小结1、建立索引的目的是为了加快查询的速度。2、索引分为聚集索引和非聚集索引两大类。3、聚集索引会针对表中数据在索引项上直接进行物理排序。4、若属性有primarykey约束，则针对该主码自动建立聚集索引。5、一个表只能建立一个聚集索引。6、若属性有unique约束，则针对该属性自动建立唯一非聚集索引。7、若表中某列已有数据，且数据中有重复值，则建立唯一索引将会失败。8、针对某列创建唯一索引，可保证该列数据取值的唯一性，相当于在该列上定义了unique约束。9、一个表中可建立多个索引，甚至是索引名不同，而内容相同的索引。10、索引需要占用物理存储空间，且该空间随表中数据的增大而增大。11、对表中数据进行增删改时，增大了数据和索引的维护成本。什么情况下需要建立索引下列情况需要我们考虑是否应该建立索引：1、是否表的数据量会很大？2、是否对表中数据的增删改操作很少？3、是否建立索引后不会严重地影响系统效率？注意：一个表中最好不要建立很多的索引。索引的建立与删除1、建立聚集索引

createclusteredindex

CI_Student_Sno

onstudent(sno)2、建立聚集唯一索引

createuniqueclusteredindex

CUI_Student_Sno

on

student(sno)3、建立非聚集唯一索引

createuniqueindex

UI_Student_Sno

on

student(sno)4、建立非聚集不唯一索引

createindex

idx_Student_Sno

on

student(sno)5、在多个列上建索引并指定升序或降序

createindex

idx_SC_Sno_Cnoonsc(sno

asc,cno

desc)6、索引删除

dropindexidx_Student_Sno

onstudent索引默认为升序数据查询select[distinct]<目标列表达式>[,<目标列表达式>]…[into<新表名>]from<表名或视图名>[,<表名或视图名>]…[where<条件表达式>][groupby<列名>[having<条件表达式>]][orderby<列名>[asc|desc]]学习建议：通过语句的运算过程来理解和掌握。选择表中的若干列1、查询全体学生的学号与姓名

selectsno,snamefromstudent2、查询全体学生的全部属性值

select*fromstudent3、取消取值重复的记录

selectdistinctsdeptfromstudentselectdistinctsdept,ssexfromstudent问题：如何理解？问题：查询结果取消重复行吗？属性列可以是表达式1、查询全体学生的姓名与出生年份

selectsname,2011-sagefromstudent2、查询结果是什么？

selectsname,'YearofBirth',2011-sage,lower(sdept)fromstudent3、问题：你知道SQLServer中都提供了哪些函数吗？问题：什么是表达式？在帮助的索引中按关键词“函数”进行查找。属性列的别名利用别名来改变查询结果的列标题。如：

selectsnameNAME,2011-sageBIRTHDAYfromstudentselect'YearofBirth'asbirth,lower(sdept)asdeptfromstudentas可省略返回查询结果的前n行数据利用topn可以得到查询结果的前n行记录的数。如：

selecttop2*fromstudentselecttop2sno,snamefromstudent

selectdistincttop3ssex

fromstudent注意：1、topn只能出现在第1个属性名之前。2、该语句语法错误：selecttop3distinctssex

fromstudent3、当n大于结果行数时，则返回全部的查询结果。into子句1、自动创建一个新表，并将查询结果的值存入该新表。如：

selectsno,

cno

intoxk

fromsc注意：新的xk表属性类型来自于sc表。但并不具有码、参照完整性等其它约束的定义。2、将查询结果存入到一个新的临时表。

selectcname,

ccredit

into#kc

fromcourse注意：1、可以对#kc

表进行增删改查询操作。如：select*from#kc2、该临时表将在会话期结束后被删除。该表存入系统的tempdb数据库中。完成下列查询要求1、求我校都哪些系招收学生了？2、求哪些学生选修了课程？求出他（她）们的学号。3、求哪些课程被学生选修了？求出它们的课程名。4、求我校所开设的全部课程的学分总和是多少？参考答案1、求我校都哪些系招收学生了？

selectdistinctsdeptfromstudentwheresdeptisnotnull2、求哪些学生选修了课程？求出他（她）们的学号。

selectdistinctsnofromsc3、求哪些课程被学生选修了？求出它们的课程号。

selectdistinctcnofromSC4、求我校所开设的全部课程的学分总和是多少？

selectsum(credit)fromcourse常用的查询条件查

询

条

件谓

词比

较=，>，<，>=，<=，!=，<>，!>，!<确定范围BETWEENAND，NOTBETWEENAND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL逻辑运算AND，OR，NOT查询满足条件的元组1、查询CS系全体学生的姓名。

selectsnamefromstudentwheresdept='CS'2、查询所有年龄在20岁以下的学生姓名及年龄。

selectsname,sagefromstudentwheresage<203、查询不是CS系的学生姓名。

selectsnamefromstudentwheresdept<>'CS'问题：若表中的数据值为小写的cs呢？（字符串内的字符不区分大小写）含不含20？（自然语言的不精确性）问题：若sdept属性数据含有空值呢？结论：空值不参与比较运算。谓词and、or、not1、查询CS系年龄在20岁以下的学生的姓名。

selectsnamefromstudentwheresdept='cs'andsage<202、查询CS系和IS系全体学生的姓名及所在系。

selectsname,sdeptfromstudentwheresdept='cs'orsdept='is'

3、查询不是CS系学生的姓名。

selectsnamefromstudentwheresdept<>'cs'

问题：你能理解吗？问题：它的等价形式是什么？谓词between…and…1、查询年龄在20~23岁之间的学生的姓名及年龄。

selectsname,sagefromstudentwheresagebetween20and232、查询年龄不在20~23岁之间的学生的姓名及年龄。

selectsname,sagefromstudentwheresagenotbetween20and233、问题：你能利用其它运算写出上述两条查询的等价形式吗？注意：包括20和23。谓词in1、查询CS系、MA系和IS系全体学生的姓名和性别。

selectsname,ssexfromstudentwheresdeptin('cs','ma','is')2、查询既不是CS系、MA系，也不是IS系的学生姓名。

selectsname,ssexfromstudentwheresdeptnotin('cs','ma','is')3、问题：你能用其它运算写出上述两条查询的等价形式吗？谓词like1、查询所有不姓刘的学生姓名。

selectsnamefromstudentwheresnamenotlike'刘%'2、查询所有姓刘的但姓名只有两个字的学生姓名。

selectsnamefromstudentwheresnamelike'刘_'运算符含义%与任意长度的字符串匹配。（长度可为0）_与长度为0或1的单个字符匹配。（一个汉字当作单字符）[]与指定范围内的单个字符匹配。如[张王李][^]与不属于指定范围内的单个字符匹配。如[^张王李]谓词like（续）3、查询所有姓刘、姓李和姓张的学生姓名。

selectsnamefromstudentwheresnamelike'[刘李张]%'4、查询不姓李、不姓张，但名叫海燕的学生姓名。

selectsnamefromstudentwheresnamelike'[^李张]海燕'5、若查询字符串中本身含有通配符，则需要进行转义。如：

selectsnamefromstudentwheresnamelike'刘\_%'escape'\'6、注意：含通配符时的like不可以用=代替。\为转义（换码）字符。它只限定紧跟在它后面的通配符，习惯上人们通常使用\字符作为转义符。涉及空值的查询1、查询所有缺考的学生学号及相应的课程号。

selectsno,cnofromscwheregradeisnull2、查询所有有成绩的学生学号及相应的课程号。

selectsno,cnofromscwheregradeisnotnull注意：is不能用=代替。完成下列查询要求1、求哪些学生还没有被分配系?2、求CS系中所有男同学的学生姓名和年龄。3、我校开设的课程中哪些课程名以“数据”两个字开头？4、求哪些学生的姓名中第2个字是“立”？5、求哪些学生的成绩为优秀，求出该学生的学号及相应的课程号。6、求既不是CS系，也不是MA系的学生中年龄不小于20的学生姓名。参考答案1、求哪些学生还没有被分配系?

select*fromstudentwheresdeptisnull2、求CS系中所有男同学的学生姓名和年龄。

selectsname,sagefromstudentwheresdept='cs'andssex='男'3、我校开设的课程中哪些课程名以“数据”两个字开头？

selectcnamefromcoursewherecnamelike'数据%'4、求哪些学生的姓名中第2个字是“立”？

select*fromstudentwheresnamelike'_立%'5、求哪些学生的成绩为优秀，求出该学生的学号及相应的课程号。

selectsno,cnofromscwheregrade>=906、求既不是CS系，也不是MA系的学生中年龄不小于20的学生姓名。

selectsnamefromstudentwheresdeptnotin('cs','ma')andsage>=20

selectsnamefromstudentwhere(sdept<>'cs'andsdept<>'ma')and

sage>=

20orderby子句1、查询所有学生的姓名和年龄，要求按年龄由小到大排序。

selectsname,sagefromstudentorderbysageasc2、查询CS系的全体学生，要求按年龄升序排列，年龄相同的按姓名的降序排列。

select*fromstudentwheresdept='cs'orderbysage,sname

desc3、问题：若排序属性列上有空值，则如何排序呢?

（空值当成最小值。）注意：asc

可以省略，即默认为升序。topnwithties子句在有orderby子句时，可以使用topnwithties子句。例如：selecttop1withties*fromstudentorderbysage该语句的功能是查询出年龄最小的学生的全部属性数据。注意：若使用topnwithties时，则返回结果不一定只有n行记录，而是和排序后的第n行记录的排序列取值相同的所有行都会被查询出来。又如：查询学生中成绩最高的学生学号。selecttop1withtiessnofromscorderbygrade

desc聚集函数注意：1、没有分组时，函数是在整个查询结果的基础上进行计算。2、<列名>只能是一个列名。3、应用avg函数时，若列为整型类型，则计算结果也为整型，并进行下取整。4、除count(*)函数外，其它聚集函数都跳过空值而只处理非空值。聚集函数含义count([distinct]*)统计元组个数count([distinct]<列名>)统计一列中值的个数sum([distinct]<列名>)计算一列值的总和（此列必须是数值型）avg([distinct]<列名>)计算一列的平均值（此列必须是数值型）max([distinct]<列名>)求一列值中的最大值min([distinct]<列名>)求一列值中的最小值聚集函数的使用1、查询学生总人数。

selectcount(*)fromstudent2、查询选修了课程的学生总人数。

selectcount(distinct

sno)fromsc3、计算选修1号课程的学生平均成绩。

selectavg(grade)fromscwherecno='1'分组1、什么叫分组？

将某个表或查询结果按某一列或多列的值分组，值相等的为一组。2、例如：将sc表按sno属性分组。SnoCnoCrade20080011922008001287200800214520080033200800119220080012872008002145200800333、再如：将student表按ssex属性分组SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立19IS2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立19ISgroupby子句问题：结果的值是什么意思？SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立19IS2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立19IS

selectssexfromstudentgroupbyssexselectsdeptfromstudentgroupbyssex

selectcount(*)

fromstudent

groupbyssexSsex男女分析下列语句的执行结果。语法错误，说明什么？121问题：它的等价查询是什么？groupby子句（续）切记：分组后聚集函数将作用于每一个分组，即每一个组都有一个函数值。SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立NULL19NULL2008001李勇男20CS2008002刘晨女19MA2008003王敏女18MA2008004张立NULL19NULL

selectssex,count(*)fromstudentgroupbyssexselectssex,count(sdept)fromstudentgroupbyssex

selectssex,count(distinctsdept)

fromstudent

groupbyssexSsex男1女2NULL1分析下列语句的执行结果。Ssex男1女1NULL0Ssex男1女2NULL0groupby子句示例1、求每门课程号及相应的选课人数。

selectcno

课程号,count(*)选课人数

fromscgroupbycno2、求每门课程的课程号、选课人数及该课的平均成绩。

selectcno

课程号,

count(*)

总人数,

avg(grade)

平均分

fromsc

groupbycno3、求每个学生的学号及其平均成绩。

selectsno

学号,avg(grade)平均分

fromscgroupbysno4、问题：如何查询平均成绩及格的学生学号及其平均成绩？having短语1、求平均成绩及格的学生学号及其平均成绩。

selectsno,

avg(grade)

fromsc

groupbysno

havingavg(grade)

>=

602、求选修了3门以上课程的学生学号。

selectsnofromscgroupbysnohavingcount(*)>33、问题：having短语与where子句有什么区别吗？（作用对象不同：前者作用于组，后者作用于表或视图。）SnoCnoCrade20080011902008001287200800215020080033SC分组查询举例1、求CS系中男女学生的数量分别是多少？

selectssex,

count(*)

fromstudent

wheresdept

=

'cs'

groupbyssex2、求各系中每个年龄段的学生总人数，要求结果中对系进行排序，同一个系的按年龄排序。即想得到右表所示的结果。

selectsdept,

sage,

count(*)

snum

fromstudent

groupbysdept,

sage

orderbysdept,

sagesdeptsagesnumcs201is192ma181连接查询1、下列查询得到什么结果?

select*selectstudent.*,sc.*

fromstudent,scfromstudent,sc2、笛卡尔积也称为交叉连接。

select*

fromstudentcrossjoinsc3、问题：连接运算是什么样的运算了？例如：求选修了1号课程的学生姓名。关系代数：πsname(σcno='1'(studentsc))

等价于：πsname(σcno='1‘∧student.sno=sc.sno(student×sc))4、问题：你还记得它的运算过程和结果吗？三者等价连接查询的执行过程SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19CS2008003王敏女18MA2008004张立男19ISStudentSnoCnoCrade20080011922008001287200800214520080033SCStudent.SnoSnameSsexSageSdeptSC.SnoCnoGrade2008001李勇男20CS20080011922008001李勇男20CS20080012872008002刘晨女19CS20080021452008003王敏女18MA20080033Student和SC关系在Student.Sno与SC.Sno两属性上做等值连接Sname李勇刘晨按Cno=1选择再投影连接查询示例1、求选修了1号课程的学生姓名。

selectsnamefromstudent,scwherestudent.sno=sc.snoandcno='1'2、求选修了2号课程且成绩为优秀的所有学生学号和姓名。

selectstudent.sno,snamefromstudent,scwherestudent.sno=sc.snoandcno='2'andgrade>=903、求选修了DB课程的学生学号和姓名。（即打印课程的学生名单）

selectstudent.sno,snamefromstudent,sc,coursewherestudent.sno=sc.snoando=oandcname='DB'这个问题如何求解？求每一门课程直接先行课的直接先行课。CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64CourseCnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Course自身连接求每一门课程直接先行课的直接先行课。CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Course起别名叫firstCnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Course起别名叫secondselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=o问题：它的执行结果是什么？自身连接（续）CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Courseselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=o执行结果CnoCpno17354NULL567NULL问题：若想得到右表所示的结果该咋办呢？CnoCpno173556自身连接（续）CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Courseselecto,second.cpnofromcoursefirst,coursesecondwherefirst.cpno=oandsecond.cpnoisnotnull执行结果问题：若又想得到右表所示的结果又该咋办呢？（你还记得外连接吗？）CnoCpno173556CnoCpno172NULL354NULL566NULL7NULL外连接CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27C语言64Courseselectfirst.cno,second.cpnofromcoursefirstleftjoincoursesecondon

(first.cpno

=second.cno)表自身的左外连接。执行结果CnoCpno172NULL354NULL566NULL7NULL1、左外连接

select*fromstudentleftouterjoinscon

(student.sno

=

sc.sno)左外连接可省略连接条件外连接（续）1、右外连接

select*

fromstudentrightouterjoinscon

(student.sno

=

sc.sno)2、外连接

select*

fromstudentfullouterjoinscon

(student.sno

=

sc.sno)3、注意：外连接不满足交换律。4、求IS系全体学生的选课情况。

select*

fromstudentleftjoinscon

(student.sno

=

sc.sno)

wheresdept

=

'IS'

问题：如果不用左外连接会出现什么结果？注意：where子句的条件不能写在外连接条件处。关于外连接的格式连接：

select*

fromstudent,scwherestudent.sno

=

sc.sno它还有一种等价形式：

select*

fromstudentjoinscon

(student.sno

=

sc.sno)外连接：

select*

fromstudentleftjoinscon

(student.sno

=

sc.sno)

select*fromstudentrightjoinscon

(student.sno

=

sc.sno)

select*fromstudentfulljoinscon

(student.sno

=

sc.sno)这种形式现在很少使用select*

fromstudent,sc

wherestudent.sno*=sc.snoEXECsp_dbcmptlevel'student','80'完成下列查询要求1、打印李勇的成绩单（即求李勇所选修的课程名及其成绩）。2、求不及格和缺考的学生所在系、学号、姓名及相应课程名，要求按系排序，同一个系的按学号排序。3、求CS系不及格和缺考的学生学号、姓名及相应课程名，要求按学号排序。4、求既不是CS系，也不是MA系缺考学生的学号、姓名及相应课程名。5、求选修BD课程的学生平均成绩。6、求每一门课程的学生平均成绩，要求输出课程名及对应的平均成绩，并按平均成绩由大到小排序。7、求李勇所选修的总学分（即成绩及格的课程学分总和）。参考答案1、打印李勇的成绩单（即求李勇所选修的课程名及其成绩）。

selectcname,grade

fromstudent,course,sc

wherestudent.sno=sc.snoand

o=oandsname='李勇'2、求不及格和缺考的学生所在系、学号、姓名及相应课程名，要求按系升序排序，同一个系的按学号升序排序。

selectsdept,student.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oand(grade<60orgradeisnull)

orderbysdept,student.sno3、求CS系不及格和缺考的学生学号、姓名及相应课程名，要求按学号排序。

selectstudent.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oand(grade<60orgradeisnull)

andsdept='cs'

orderbystudent.sno参考答案（续）4、求既不是CS系，也不是MA系缺考学生的学号、姓名及相应课程名。

selectstudent.sno,sname,cname

fromstudent,course,sc

wherestudent.sno=sc.snoando=oandgradeisnull

andsdeptnotin('cs','ma')5、求选修BD课程的学生平均成绩。

selectavg(grade)

fromcourse,sc

whereo=oandcname='DB'6、求每一门课程的学生平均成绩，要求输出课程名及对应的平均成绩，并按平均成绩由大到小排序。

selectcname,avg(grade)agrade

fromcourse,sc

whereo=o

groupbycname

orderbyagradedesc7、求李勇所选修课程得到的总学分（即成绩及格的课程学分总和）。

selectsum(credit)

fromstudent,course,sc

wherestudent.sno=sc.snoando=oandsname='李勇'andgrade>=60嵌套查询求选修了1号课程的学生姓名。

selectsname

fromstudent,sc

wherestudent.sno=sc.snoandcno='1'

换个思路：

selectsname

fromstudent

wheresnoin

(selectsno

fromsc

wherecno='1')特点：内层子查询条件中所涉及的属性与外层无关。将这样的子查询称为不相关子查询。问题1：你知道该语句是怎么执行的吗？问题2：哪种表达方式好？问题3：你还有别的办法吗？嵌套查询（续）1、问题：下列语句的执行结果是什么？

select*

fromsc

wherecno='1'

2、问题：将student与tmpT做自然连接后再做sname的投影得到什么？3、查询表达如下：

selectsname

fromstudent,(select*fromscwherecno='1')

tmpT

wherestudent.sno=tmpT.sno4、问题：下列语句的执行结果是什么？

selectsname,tmpT.grade

fromstudent,(select*fromscwherecno='1')

tmpT

wherestudent.sno=tmpT.sno这也是一种嵌套：嵌套表SnoCnoCrade20080011922008002145给结果关系起个名，叫tmpT嵌套查询（续）1、求没选修1号课程的学生姓名。

selectsname

fromstudent,sc

wherestudent.sno=sc.snoandcno<>

'1'

问题：该语句的执行结果是什么？它对吗？2、下列语句的执行结果是什么？

selectsname

fromstudent

wheresnonotin

(selectsno

fromsc

wherecno='1')3、问题：求不是CS系的学生姓名，下面的语句对吗？

selectsname

fromstudent

wheresdept<>'cs'orsdeptisnull4、你能理解这是为什么吗？嵌套查询举例求与李勇在同一个系的学生姓名。1、selectsname

fromstudent

wheresdeptin

(

selectsdept

fromstudent

wheresname

=

'李勇'

)2、selectsname

fromstudent,

(selectsdeptfromstudentwheresname='李勇')tmpT

wherestudent.sdept

=

tmpT.sdept3、selectfirst.sname

fromstudentfirst,

studentsecond

wherefirst.sdept

=

second.sdept

and

second.sname

='李勇'

问题：求选修DB课程的学生学号和姓名。你能写出三种不同查询语句吗？注意：若能确定子查询的结果一定返回的是单值，则可用比较运算符代替in。相关子查询下面的语句是如何执行的？执行结果是什么？

selectsno,

cno

fromscx

wheregrade>=

(

selectavg(grade)

fromsc

wheresno

=

x.sno)SnoCnoCrade20080011922008001287200800214520080033SCx取出第1个元组的值，并将该值传递到内层。执行如下的语句：

selectsno,

cno

fromscx

where92>=

(

selectavg(grade)

fromsc

wheresno

=2008001)取出第k个元组的值重复进行再取出第2个元组的值，并将该值传递到内层。执行如下的语句：

selectsno,

cno

fromscx

where87>=

(

selectavg(grade)

fromsc

wheresno

=2008001)……直到x中的全部元组处理完毕相关子查询：即子查询条件中涉及到了外层关系的属性。不相关子查询与相关子查询比较不相关子查询：子查询的查询条件不依赖于父查询由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。相关子查询：子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，利用该元组值执行内层查询，若此时子查询结果使得外层的WHERE子句表达式为真，则该元组的值放入结果表；然后再取外层表的下一个元组值，重复上述过程，直至外层表全部元组处理完毕为止。子查询内不可以使用orderby子句。相关子查询举例求选修1号课程的学生姓名。想法：①

在student关系中取出一个元组。如：第1个元组；②利用该元组的Sno属性值2008001到SC关系找有没有Sno为该值且Cno为1的元组，若在SC关系中查得有这样的元组，则说明在①中取出的元组符合要求，将其对应的Sname属性值输出；③

重复上述过程，直到Student关系的全部元组依次处理完毕。

selectsname

fromstudent

wheresnoin

(

selectsno

fromsc

wherestudent.sno

=

snoandcno='1')SnoSnameSsexSageSdept2008001李勇男20CS2008002刘晨女19CS2008003王敏女18MA2008004张立男19ISStudentSnoCnoCrade20080011922008001287200800214520080033SC注意：这里其实仅关心子查询的结果有还是没有元组。exists谓词exists谓词代表存在量词，只产生“真”或“假”逻辑值。可用于判定子查询的结果是否为空。1、当子查询的结果非空，该谓词返回“真”2、当子查询的结果为空，该谓词返回“假”上例的语句可描述如下：

selectsname

fromstudent

whereexists

(

select*

fromsc

wherestudent.sno

=

snoandcno='1')问题1：你知道该语句是如何执行的吗？问题2：求没选修1号课程的学生姓名，如何表达？exists谓词（续）求没选修1号课程的学生姓名。

selectsname

fromstudent

wherenotexists

(

select*

fromsc

wherestudent.sno

=

snoandcno='1')问题：你能理解吗？any、all谓词1、谓词含义①any：某个（值）②all：所有（值）2、例如：

>any大于子查询结果中的某个值

>all大于子查询结果中的所有值（这是什么意思呢？）3、等价转换空白处通常没有实际意义。=<><<=>>=anyin<max<=max>min>=minallnotin<min<=min>max>=maxany(some)、all谓词举例查询其他系中比CS系某一学生年龄小的学生姓名和年龄。selectsname,sagefromstudentwheresage<any

(

selectsage

fromstudent

wheresdept='CS')

andsdept<>'CS'selectsname,sagefromstudentwheresage<(selectmax(sage)

fromstudent

wheresdept='CS')

andsdept<>'CS'等价union、interset、except子句并操作：select*fromstudentwheresdept='cs'unionselect*fromstudentwheresdept='ma'交操作：selectsname,sagefromstudentwheresage>20intersectselectsname,sagefromstudentwheresdept='cs'差操作：select*fromstudentexceptselect*fromstudentwheresdept='cs'问题：它们的执行结果是什么？注意：1、需要满足集合运算的条件。

2、并操作结果会取消重复的元组。插入数据insert语句的格式：

insertinto<表名>[<属性列1>[，<属性列2>…]]values(<常量1>[，<常量2>]…)insertintostudent(sno,

sname,

sage)values('5',

'陈冬',

18)insertintostudentvalues('6',

'张飞',

'男',

23,

'IS')insertintosc(sno,cno,grade)values('5','2',null)例如：值序列必须与属性序列对应一致。其它属性赋值为空。值序列必须与表的列定义顺序一致。但这并不是一个好习惯。此处需要明确给出空值插入数据（续）注意：只有符合数据库完整性要求的数据才能被成功插入到表中。指出下列语句的错误。insertintostudent(sname,

sage)values('陈冬',

18)insertintostudent(sno,ssex,sname,sage,sdept)values('6',

'张飞',

'男',

'23',

'IS')insertintoscvalues('5','ab',-98)SQLServer可自动将数串转换成数。但这不是好习惯。插入子查询结果第一步：建表

createtabledept_age(sdeptchar(20)primarykey,

avg_age

smallint)第二步：插入数据

insertintodept_age(sdept,avg_age)selectsdept,avg(sage)fromstudentgroupbysdept对每一个系，求学生的平均年龄，并把结果存入数据库。注意属性列的数据类型问题。select

sdept,

avg(sage)avg_ageintodept_agefromstudentgroupbysdept这两者有什么区别吗？修改数据update语句格式：

update<表名>set<列名>=<表达式>[，<列名>=<表达式>]…[where<条件>]修改指定表中满足where子句条件的元组的指定列值。注意：修改后的数据只有满足完整性要求时才能修改成功。1、将1号学生的年龄改为22。

updatestudent

setsage=22

wheresno='1'2、下面语句执行是结果是什么？

updatestudent

setsage=22修改数据（续）3、将全体学生的年龄增加1岁。

updatestudent

setsage=sage+14、将DB课程的先行课置空，学分改为6。

updatecourse

setcpno=null,credit=6

wherecname='DB'5、将CS系全体学生的成绩置零。

updatesc

setgrade=0

where'cs'=

(

selectsdept

fromstudent

wheresno=sc.sno

)问题：你还能写出什么形式吗？修改数据（续）将CS系全体学生的成绩置零。

updatesc

setgrade=0

where'cs'=

(

selectsdept

fromstudent

wheresno=sc.sno

)updatescsetgrade=0whereexists

(select*

fromstudent

wheresno=sc.snoandsdept='cs')updatescsetgrade=0wheresnoin

(selectsno

fromstudent

wheresdept='cs')修改数据（续）updatescsetgrade=0whereexists

(

selectsno

fromstudent,

course

wheresno

=

sc.snoandcno=oandsdept='cs'andcname='db')7、将CS系选修DB课程的学生成绩置零。删除数据delete语句格式：

deletefrom<表名>[where<条件>]删除指定表中满足where子句条件的元组。注意：元组删除后仍能满足完整性要求才能删除成功。1、将