数据库重点Word格式.docx
《数据库重点Word格式.docx》由会员分享，可在线阅读，更多相关《数据库重点Word格式.docx（17页珍藏版）》请在冰豆网上搜索。
200012
欧阳雨
200515
刘依依
23
表3-5课程表
课程号
课程名
前修课程
学分
c01
数据库原理
c03
4
c02
信息系统
C01
数据结构
null
6
c04
DB_设计
co1
3
表3-6成绩表
成绩
70
80
95
45
78
……
实验D002:
单表查询实验
六、验证性实验
先选择要操作的数据库，用T-SQL命令：
USESTUDENT
或在工具栏的当前数据库中选择STUDENT，如图3-11所示。
工作数据库
图3-11查询编辑器界面
以下查询要求在逐个语句执行，执行后将执行结果记录下来：
1．条件查询全部数据
SELECT*FROMS
2．在SELECT关键字后指明要检索的列名
（1）查询S表的学生学号和姓名
SELECTSNO，SNAMEFROMS
（2）查询S表中的系部名
SELECTDEPAFROMS
（3）查询S表中不重复的系部名
SELECTDISTINCTDEPAFROMS
3．改变列标题的检索
（1）使用空格形式：
列名新标题
SELECTSNO学号,SNAME姓名FROMS
（2）使用“AS”形式，列名AS新标题
SELECTSNOAS学号,SNAMEAS姓名FROMS
3．有条件选择的查询
（1）在S表检索“信息系”的学生信息
WHEREDEPA=’信息系’
（2）在S表中检索姓“王”的学生信息
WHEREsnamelike‘王%’
（3）在SC表检索’C01’选修课成绩为空的选课记录
SELECTSNO，CNOFROMSC
WHEREcno=’C01’andgradeisnull
（4）检索年龄为21，18，22的学生学号、姓名
WHEREagein（21,18,22）
AgeIN{21,18,22}表示某条记录的AGE字段值是否是集合{21,18,22}中的元素，如是，则选择。
它等价于下面语句：
WHEREage=18orage=21orage=22
4．使用聚集函数
（1）查询选课表中最高分、平均分、最低分
SELECTMAX（GRADE）,AVG（GRADE）,MIN（GRADE）
FROMSC
（2）查询“C01”课程的最高分、平均分和最小成绩。
WHERECNO=‘C01’
5．对检索结果进行排序
SELECT*FROMSC
WHEREGRADEISNULL
ORDERBYSNO，CNODESC
6．进行分组统计：
（1）查询各学生的选课数
SELECTSNO，COUNT（*）
GROUPBYSNO
（2）使用HAVING字句：
“选课表”中查询选修了3门以上课程的学生学号。
SELECTSNO，COUNT（*）FROMSC
HAVINGCOUNT（*）>
=3
（3）“选课表”中按学号分组汇总学生的平均分，并按平均分的降序排列。
SELECTSNO学号,AVG（GRADE）平均分FROMSC
ORDERBY平均分DESC
（4）析下面两个SELECT语句执行结果，说明有什么不同？
SELECTSNOFROMSC
ORDERBYSNO
GO
7．在ORDERBY字句后带COMPUTE字句
（1）按学号对不及格的成绩记录进行汇总。
SELECTSNO,CNO,GRADEFROMSC
WHEREGRADE<
60
COMPUTECOUNT（GRADE）
（2）按学号对不及格的成绩记录进行明细汇总（即根据排序结果进行分组汇总）
COMPUTECOUNT（GRADE）BYSNO
实验D003：
多表查询实验
注意：
多表连接时要注意当某列名在两张表中同时存在时，在列名前要加表名以示区分。
3．表自身的连接
SELECT别名.列名，…
FROM表1AS别名1，表1AS别名2
WHERE别名1.列名=别名2.列名
自身连接时因为是对同一张表操作，为区分开来对该表操作的顺序，需要对表取两个别名，以示区别。
4．IN嵌套子查询
SELECT<
目标列表达式列表>
FROM表名
WHERE列名IN
（SELECT字句）
5．EXISTS嵌套子查询
–带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
●若内层查询结果非空，则返回真值
●若内层查询结果为空，则返回假值。
由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
所以，EXISTS子查询中一般是相关自查询，即子查询脱离父查询后不能单独执行。
思考：
如果EXISTS子查询中是不相关子查询，会有什么结果？
1．多表的连接查询（相当于做笛卡儿乘积）
SELECT*FROMC，SC
2．表的等值连接查询
（1）查询各学生的选课信息（包括学号、课程名、成绩）
因为学号和成绩在SC表中，而课程名在C表中，因此需要多表查询。
SELECTSNO，CNAME，GRADE
FROMC，SC
WHEREC.CNO=SC.CNO
（2）查询学生的选课记录，显示学生的学号、姓名、课程号、成绩。
SELECTS.SNO，SNAME，CNO，GRADE
FROMS，SC
WHERES.SNO=SC.SNO
（3）查询学生的选课记录，显示学生的学号、姓名、课程号、课程名、成绩
SELECTS.SNO，SNAME，SC.CNO，CNAME，GRADE
FROMS，SC，C
WHERES.SNO=SC.SNOANDSC.CNO=C.CNO
3．左外连接查询：
当希望左表（第一张表）中所有记录全部显示出来时，需要用左外连接操作。
INSERTINTOS（SNO,SNAME）VALUES（’20000’,’ZXX’）
FROMS
LEFTOUTERJOINSCONS.SNO=SC.SNO
4．表自身的连接
（1）查询与‘李勇’同系的学生学号
将学生表S与S本身进行等值连接（系部相等），因为S与S做连接操作时不能区分，所以，对表取一个别名。
然后将第二张表S中名字为‘李勇’的记录选择出就可。
SELECTS1.SNO
FROMSASS1，SASS2
WHERES1.DEPA=S2.DEPAANDS2.SNAME=’李勇’
（2）显示每个学生的非最高分成绩（学生自己的选课成绩中，不是最高分的选课记录显示出来）
SELECTSNO,CNO,GRADEFROMSCASSC1
WHEREGRADE<
（SELECTMAX（GRADE）FROMSCASSC2
WHERESC2.SNO=SC1.SNO）
5．IN嵌套子查询
（1）不相关的IN子查询：
子查询可以单独执行，与被嵌套的查询无关。
如，查询与‘李勇’同系的学生学号
可以先查询出‘李勇’所在的系，然后再到S表中查询与上述结果相同的记录。
SELECTSNO
FROMS
WHEREDEPAIN
（SELECTDEPAFROMSWHERESNAME=‘李勇’）
查询‘数据库原理’课程的选课人数
SELECTCOUNT（*）FROMSC
WHERECNOIN
（SELECTCNOFROMCWHERECNAME=‘数据库原理’）
（2）相关的子查询：
子查询中要用到父查询表的信息，子查询不能独立执行。
如，查询选修课程号为“C01”课程且成绩至少高于选修课程号为“C02”的同学的Cno、Sno和GRADE。
在子查询中，因为要查找该同学‘C02’课程的成绩，所以，需要父查询表中该学生的学号信息。
SELECTCNO，SNO，GRADE
FROMSCASSC1
WHERECNO=’C01’ANDGRADE>
（
SELECTGRADEFROMSCASSC2
WHERESC2.SNO=SC1.SNOANDSC2.CNO=’C02’）
6．EXISTS嵌套子查询
（1）执行以下语句，观察显示的两个查询结果
SELECTSNO，SNAME
WHEREEXISTS
（SELECT*FROMSCWHERECNO=‘C03’）
GO
（2）执行以下语句，观察显示的两个查询结果
INSERTINTOCVALUES（‘C06’，‘数据库安全’，NULL，3）
（SELECT*FROMSCWHERECNO=‘C06’）
（3）查询‘数据库原理’课程的选课人数
SELECTCOUNT（*）
（SELECT*FROMC
WHEREC.CNO=SC.CNOANDCNAME=’数据库原理’）
实验D004：
数据完整性约束实验
4．修改表结构时可以添加或删除完整性约束
ALTERTABLE表名
ADD[CONSTRAINT完整性约束名]约束
如，在S表上添加主键约束
ALTERTABLES
ADDCONSTRAINTpriKPRIMARYKEY（SNO）
每小题语句输入好后执行，观察执行后有什么结果？
想想为什么？
1．PRIMARYKEY主键约束的建立
（1）建立表时加主键约束
CREATETABLEST（SNOVARCHAR（10）PRIMARYKEY,SNAMEVARCHAR（20）NOTNULL,DEPAVARCHAR（20）,AGEINT,SEXCHAR（4））
INSERTINTOST（SNO，SNAME，DEPA）VALUES（‘1001’，‘ZXX’，NULL）
INSERTINTOST（SNO，SNAME）VALUES（‘1001’，‘MID’）
INSERTINTOST（SNO，SNAME）VALUES（NULL，‘ZXX’）
（2）在已有的表上添加约束
CREATETABLEST2（SNOVARCHAR（10）NOTNULL,SNAMEVARCHAR（20）NOTNULL,DEPAVARCHAR（20）,AGEINT,SEXCHAR（4））
INSERTINTOST2SELECT*FROMS
ALTERTABLEST2ADDCONSTRAINTpriKEYPRIMARYKEY（SNO）
INSERTINTOST2（SNO，SNAME，DEPA）VALUES（‘200512’，‘Beibei’，NULL）
运行后出现什么结果？
分析原因。
2．DEFAULT约束
CREATETABLECUST（NOVARCHAR（5）PRIMARYKEY，WEIGHTINTDEFUALT（10））
INSERTINTOCUST（NO）VALUES（‘ZY01’）
INSERTINTOCUST（NO）VALUES（‘ZY03’）
INSERTINTOCUSTVALUES（‘ZY02’，20）
SELECT*FROMCUST
3．CHECK约束
CREATETABLECUSTOMER（CUSTNOCHAR（4）NOTNULLCHECK（CUSTNOLIKE‘[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]’），CUSTNAMEVARCHAR（20））
INSERTINTOCUSTOMERVALUES（‘BJ01’，‘BeijingGridCorp.’）
INSERTINTOCUSTOMERVALUES（‘BJ1’，‘BeijingGridCorp.’）
INSERTINTOCUSTOMERVALUES（‘BJ*1’，‘BeijingGridCorp.’）
4．UNIQUE唯一性约束的建立
CREATETABLECUST1（CUSTNOCHAR（4）PRIMARYKEY，CUSTNAMEVARCHAR（20）NNIQUE，COUNTRYVARCHAR（10））
INSERTINTOCUST1（CUSTNO）VALUES（‘BJ01’）
INSERTINTOCUST1（CUSTNO）VALUES（‘BJ02’）
INSERTINTOCUST1VALUES（‘BJ03’，‘BeijingGridCorp.’）
INSERTINTOCUST1VALUES（‘BJ04’，‘BeijingGridCorp.’）
5．FOREIGNKEY外键约束的建立
ALTERTABLECADDCONSTRAINTCpriKEYPRIMARYKEY（CNO）
ALTERTABLESCADDCONSTRAINTscpriKEYPRIMARYKEY（SNO,CNO）
ALTERTABLESCADDCONSTRAINTscforKEY1FOREIGNKEY（SNO）REFERENCESST2（SNO）
ALTERTABLESCADDCONSTRAINTscforKEY2FOREIGNKEY（CNO）REFERENCESC（CNO）
INSERTINTOSC（SNO,CNO）VALUES（‘890’,’C10’）
INSERTINTOSC（SNO,CNO）VALUES（‘200518’,’C01’）
INSERTINTOSC（SNO,CNO）VALUES（‘890’,NULL）
INSERTINTOSC（SNO,CNO）VALUES（NULL,’C10’）
6．规则的建立
建立规则：
Createrulerangcountry
As
@listin（‘USA’,’China’）
规则的绑定:
Sp_bindrulerangcountry,‘cust1.country’
INSERTINTOCUST1VALUES（‘BJ05’,’YONEX’,‘GERMAN’）
INSERTINTOCUST1VALUES（‘BJ05’,’YONEX’,‘USA’）
实验D005：
数据操作与索引实验
。
1．数据的插入
（1）单记录的插入
INSERTINTOS（SNO，SNAME）VALUES（’200300’,’Paulwen’）
（2）多记录的插入
CREATETABLESBAK（SNOVARCHAR（10）NOTNULL,SNAMEVARCHAR（20）,DEPAVARCHAR（20）,AGEINT,SEXCHAR（4））
SELECT*FROMSBAK
INSERTINTOSBAKSELECT*FROMS
2．数据的删除
CREATETABLECBAK（CNOVARCHAR（10）NOTNULL,CNAMEVARCHAR（20）,CPNOVARCHAR（10），CREDITINT）
INSERTINTOCBAKSELECT*FROMC
DELETEFROMCBAKWHERECREDIT<
SELECT*FROMCBAK
3．数据的修改
UPDATESSETDEPA=‘CS’
WHEREDEPA=‘计算机系’
4．索引的建立
（1）建立非聚族索引
SELECT*FROMC
CREATEINDEXICNAMEONC（CNAME）
SELECT*FROMC/*看数据表*/
SELECT*FROMCWITH（INDEX=ICNAME）/*按索引次序看数据表*/
（2）建立聚族索引
CREATECLUSTEREDINDEXCICNOONC（CNO）
SELECT*FROMCWITH（INDEX=CICNO）
（3）建立唯一索引
CREATEUNIQUEINDEXUISNOONSC（SNO）
执行后有什么结果？
为什么会出现这个结果？
CREATEUNIQUEINDEXUISNOONSC（SNO，CNODESC）
（4）建立复合索引
CREATEINDEXIAGESNOONS（AGEDESC，SNO）
SELECT*FROMSWITH（INDEX=IAGESNO）
（5）查看表中的索引
SP_HELPINDEX‘S’
（6）删除表中的索引
DROPINDEXS.IAGESNO
5．视图及其操作
（1）视图的建立
CREATEVIEWSHOWSNO
AS
SELECTS.SNO，SNAME，AVG（GRADE）AS平均成绩
GROUPBYS.SNO，SNAME
SELECT*FROMSHOWSNO
创建信息系学生的视图
CREATEVIEWVISDEPA
SELECT*FROMSWHEREDEPA=’信息系’
（2）在视图上修改数据
UPDATEVISDEPA
SETDEPA=’IS’
SELECT*FROMVISDEPA
视图中还有数据吗？
为什么会这样？
CREATEVIEWS_SUM
SELECTSNO,SUM（GRADE）ASTOTALSCOREFROMSC
UPDATES_SUM
SETTOTALSCORE=60
执行语句，出现什么结果？
分析为什么？
（3）在视图上删除数据
DELETEFROMVISDEPAWHERESEX=‘女’
1．编程实现查询与‘李勇’同系的学生学号
DECLARE@DEPAVARCHAR（20）
SELECT@DEPA=DEPAFROMSWHERESNAME=’李勇’
SELECTSNOFROMS
WHEREDEPA=@DEPA
2．运行下面的程序，观察运行结果
DECLARE@XINT,@YINT
SET@X=0
SELECT@Y=1
WHILE@Y<
BEGIN
SET@X=@X+@Y
SELECT@Y=@Y+2
PRINTSTR（@Y）+’INTHELOOP’
IF@Y>
14
BREAK
END
PRINT‘OUTOFTHELOOP’
3．CASE函数的使用
（1）CASE后带表达式
Select性别=casesex
when‘男’then‘M’
when‘女’then‘F’
else‘输入出错’
end
Froms
（2）CASE后不带参数
SELECT性别=CASE
WHENSEX=‘男’THEN‘M’
WHENSEX=‘女’THEN‘F’
ELSE‘ERROR’
END
（3）用CASE语句进行多条件
升级会员 