数据库复习1.docx
《数据库复习1.docx》由会员分享，可在线阅读，更多相关《数据库复习1.docx（14页珍藏版）》请在冰豆网上搜索。
数据库复习1
数据库复习
一、建库及建表
1.建立一个STU的数据库
2.在STU中建立一个”学生”表Student,它由学号Sno,姓名Sname,性别Ssex,年龄Sage,所在系Sdept五个属性组成.学号不能为空,且值唯一.姓名取值也唯一.
CREATETABLEStudent
（SnoCHAR（5）NOTNULLUNIQUE,
SnameCHAR（20）UNIQUE,
SsexCHAR
（2）,
SageINT,
SdeptCHAR（15））;
3.建立一个课程表SC,学号Sno,课程名Scourse,分数Grade学号不能为空,且值唯一
CREATETABLESC
（SnoCHAR（5）NOTNULLUNIQUE,
ScourdeCHAR（15）,
GradeINT）;.
4.向Student表添加”入学时间”列,其数据类型为日期型.
ALTERTABLEStudentADDScomeDATE;
二、插入数据
1.INSERTINTOStudentVALUES（'001','KATE','女',20,'CS'）;
2.INSERTINTOStudentVALUES（'002','JIM',男',21,'MATH'）;
3.INSERTINTOStudentVALUES（'003','JACK','男',21,'MATH'）;
4.INSERTINTOStudentVALUES（'004','LUCY','女',21,'ART'）;
5.INSERTINTOStudentVALUES（'005','LILY','女',21,'ART'）;
6.INSERTINTOStudentVALUES（'006','JHON','男',21,'IS'）;
在sc表中插入
7.INSERTINTOSCVALUES（'001','JAVA',90）;
8.INSERTINTOSCVALUES（'002','C',85）;
9.INSERTINTOSCVALUES（'003','PHP',70）;
10.INSERTINTOSCVALUES（'006','JAVA',60）;
三、查询
1.查询全体学生的详细记录.
SELECT*
FROMStudent;
2查询全体学生的姓名,学号,系.
SELECTSname,Sno,Sdept
FROMStudent;
3.查询全体学生姓名和出生年份,年龄
SELECTSnamesage2009-sage
FROMStudent;
4.在查询结果中去除重复行.
SELECTDISTINCTSdept
FROMStudent;
5.查询艺术系的学生姓名.
SELECTSname
FROMStudent
WHERESdept='ART';
6.查询18岁以下的学生姓名.
SELECTSname
FROMStudent
WHERESage<18;
7.查询20到23岁间的学生姓名.
SELECTSname
FROMSudent
WHERESageBETWEEN20AND23;
IN谓词作用是多个OR运算符
8..查询MATH,ART系的学生姓名.
SELECTSnameFROMStudent
WHERESdeptIN（'MATH','ART'）;
9..查询不是MATH,ART系的学生姓名.
SELECTSname
FROMStudent
WHERESdeptNOTIN（'MATH','ART'）;
字符匹配查询
%代表长的字符串
a%b是以a开头,以b结尾的任意长的字符串
a%是以a开头的任意长的字符串
%b是以b结尾的任意长的字符串.
_代表单个字符.
a_b是以a开头,以b结尾的长度为3的字符串
a_是以a开头的长度为2的字符串
_b是以b结尾的长度为2的字符串.
一个汉字占两个字符.
10.查询所有姓刘的学生的姓名.
SELECTSname
FROMStudent
WhereSnameLIKE‘刘%’
11..查询姓欧阳且名为三个字的学生姓名.
SELECTSname
FROMStudent
WHERESnameLIKE‘欧阳__’
12..查询第二个字为阳的学生姓名.
SELECTSnameFROMStudent
WHERESnameLIKE‘__阳%’;
13..查询有DB_Design课程的学生名.
SELECTSnameFROMStudent
WHEREScourseLIKE‘DB_Design’
空查询
1.查询所有没有成绩的课程和姓名.SELECTScourse,Sname
FROMSC,student
WHEREGradeISNULLand
student.sno=SC.sno;
2.查询所有有成绩的课程和姓名.SELECTScourse,Sname
FROMStudent,sc
WHEREGradeISNOTNULL
Andstudent.sno=SC.sno;
3.查询艺术系21岁以下的男生的姓名
SELECTSnameFROMStudent
WHERESage<=21AND
Ssex='男'ANDSdept='ART';
4..查询选了JAVA课程的学生姓名和分数,查询结果按课程分数降序排列.
ASC升序DESC降序默认的为升序
SELECTSname,GradeFROMSC,student
WHEREScourse='JAVA’and
student.sno=SC.sno
ORDERBYGradeDESC;
5..查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄按降序排列
SELECT*FROMStudent
ORDERBYSdept,SageDESC;.
6.查询学生人数
SELECTCOUNT（*）
FROMStudent;
7.计算JAVA课程的学生平均成绩.
SELECTAVG（Grade）FROMSC
WHEREScourse='JAVA';
8.查询JAVA课程的最高分
SELECTMAX（Grade）
FROMSC
WHEREScourse='JAVA';
GROUPBY将查询结果分组,值相等的为一组.
9.求各个课程号及相应的选课人数
SELECTScourse,COUNT（Sno）
FROMSCGROUPBYScourse;
10.查询选修了3门以上课程的学生学号.
SELECTSnoFROMSC
GROUPBYSno
HAVINGCOUNT（*）>3;
以前的查询都是单表查询,下面是关于多表查询的,这种查询叫作连接查询.
等值与非等值连接查询
11.查询每个学生及选修课程的情况.
学生情况放在Student表中,学生选课情况放在Sc表中.这个查询涉及两个表,两个表通过Sno联系的.
SELECTStudent.*,SC.*
FROMStudent,Sc
WHEREStudent.Sno=SC.Sno;
注意查询结果中将有两列Sno!
在等值连接种把目标中重复的属性列去掉成为自然连接.
Eg.将上例改用自然连接完成.
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Scourse,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.sno;
无重复的可不加前缀,Sno是重复的,所以加前缀.
自身连接
一个表与自身的连接.
外连接
在通常的连接中,只有符合条件的元组才会输出,但有时,想把Student表中所有的学生的信息都显示出来,要是他没选课,只会显示他的其他信息,选课信息为空.这时就用到了外连接.
12.显示Student表中所有学生的信息（包括选课信息）
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Scourse,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno（*）;
复合条件连接
13查询选了JAVA且得分90以上的所有男生
SELECTStudent.Sno,Sname
FROMStudent,SC
WHEREStudent.Sno=SC.SnoANDSC.Scourse=’JAVA’AND
SC.Grade>=90AND
Student.Ssex=’男’;
嵌套查询
将一个查询块嵌套在另一个查询块的WHERE或HAVING条件中,称为嵌套查询.
14.查询选了JAVA课程的学生
IN动词的查询
SELECTSnameFROMStudent
WHERESnoIN
（SELECTSnoFROMSC
WHEREScourse='JAVA'）;
15查询和codear在同一系的学生
SELECTSnameFROMStudent
WHERESdeptIN
（SELECTSdeptFROMStudent
WHERESname=’codear’）;
因为一个学生只能在一个系学习,所以IN也可用=取代.
用自身连接实现上述例子
SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2
WHERES1.Sdept=S2.SdeptANDS2.Sname=’codear’;
也可用表的别名来和嵌套查询实现 SELECTSno,Sname,Sdept
FROMStuentS1
WHERES1.SdeptIN （SELECTSdeptFROMStudentS2
WHERES2.Sname=’codear’）;
带有ANY或ALL谓词的子查询
16查询其他系中比信息系中某一学生年龄小的学生姓名和年龄.SELECTSname,Sag
eFROMStudent
WHERESageWHERESdept='IS'）
ANDSdept<>'IS';
相当于
SELECTSname,Sage
FROMStudent
WHERESage<（SELECTMAX（Sage）FROMStudent
WHERESdept=’IS’）
ANDSdept<>’IS’
17.查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄.SELECTSname,Sage
FROMStudent
WHERESageWHERESdept=’IS’）
ANDSdept<>’IS
相当于
SELECTSname,Sage
FROMStudent
WHERESage<（SELECTMIN（Sage）FROMStudent
WHERESdept=’IS’）
ANDSdept<>’IS
带有EXISTS谓词的子查询.
带有EXISTS谓词的子查询不返回人和数据,只返回TURE,FALSE.
18查询选了JAVA课程的学生姓名.
SELECTSnameFROMStudent
WHEREEXISTS
（SELECT*FROMSC
WHERESno=Student.SnoANDScourse='JAVA'）;
与EXISTS相对应的是NOTEXISTS
查询没选JAVA课程的学生姓名.
SELECTSnameFROMStudent
WHERENOTEXISTS
（SELECT* FROMSC
WHERESno=Studet.SnoANDScourse=’JAVA’）;
19.查询没选JAVA课程的学生姓名.
SELECTSnameFROMStudent
WHERENOTEXISTS
（SELECT*FROMSC
WHERESno=Studet.SnoANDScourde=’JAVA’）;
IN谓词,比较运算符,ANY和ALL谓词都可用EXISTS或NOTEXISTS谓词来代替,然而反之不成立.
查询计算机系的18岁下的男生.
SELECT* FROMStudent
WHERESdept=’CS’
INTERSECTSELECT*
FROMStudent
WHERESage<18;
集合查询都可用其他查询方法实现
查询计算机学生与年龄18下的学生的差集.
SELECT*FROMStudent
WHERESdept=’CS’ANDSage<18;
插入单个元组
插入学生记录（学号:
001,姓名:
KATE,性别:
女,系:
CS,年龄:
20）
INSERTINTOStudent
VALUES（'001','KATE',女',20,'CS'）;
插入查询结果
子查询结果也可嵌套在INSERTINTO语句中.
CREATETABLEDeptage
（SdeptCHAR（15）,
AvgageSMALLINT）;
INSERT
INTODeptage（Sdept,Avgage）
（SELECTSdept,AVG（Sage）
FROMStudent
GROUPBYSdept）;
修改数据
修改某一元组的值
将001的年龄改为19岁.
UPDATEStudent
SETSage=’19’
WHERESno=’001’;
修改所有元组的值
将所有学生年龄减1岁.
UPDATEStudent
SETSage=Sage-1;
带有自查询的修改语句.
自查询也可作为修改条件嵌套在UPDATE语句中.
将计算机系所有学生成绩置零.
UPDATESCSETGrade=0
WHERE‘CS’=（SELECTSdept
FROM Student
WHEREStudent.Sno=SC.Sno）;
删除数据
删除某一个元组
删除学号为001的学生记录
DELETEFORMStudent
WHERESno=’001’;
删除所有元组
删除所有学生选课记录
DELETEFORMStuent带子查询的删除语句
同样自查询也可嵌套到DELETE语句中.
删除所有计算机学生选课记录
DELETEFROMSC
WHERE‘CS’=（SELECTSdept
FROM Student
WHEREStudent.Sno=SC.Sno）;
删除表中某一列
删除Student表中列Sdept
ALTERTABLEStudentDROPCOLUMNSdept;
存储过程和触发器复习
创建一个数据库名为studentsys库，在该库中创建以下表：
表1学生基本情况表（student）
stud_id学号varchar（12）主键
stud_name姓名varchar（8）
stud_sex性别char
（2）只能为男或女
birth出生日期datetime
enroll_date入校日期datetime
nation民族varchar（10）默认值汉族
native_place籍贯varchar（20）
polity政治面貌varchar（10）
identity_id身份证号varchar（18）唯一
address家庭住址varchar（40）
postcode邮政编码char（6）
tel联系电话varchar（20）
stud_flag学籍标识char
（2）0---在校1-毕业-1异动-2休学
class_id班级编号varchar（10）外建
表2班级编码表class
class_id班级编号varchar（10）主键
class_name班级名称varchar（20）
enroll_year入学年份char（9）
dptmj专业char（20）
length学制char
（1）
class_num班级人数int
flag毕业标志char
（1）0—在校1-毕业
depart系varchar（20）
表3课程表course
course_id课程编号varchar（8）主键
course_name课程名称varchar（30）
hours学时数int检查约束>0
表4成绩表scorce
stud_id学号varchar（12）主键
course_id课程编号varchar（8）主键
term学期tinyint检查约束>0
score成绩numeric1位小数检查约束>0
test_num学分numeric1位小数检查约束>0
score_bk补考成绩numeric1位小数
re_score补考次数tinyint0表示未补考，其余表示是第几次补考
1.创建存储过程，存储过程名为student_score，要求实现如下功能：
根据学生学号，查询该学生的成绩情况，其中包括该学生的学号、姓名、性别、课程号，课程名、成绩和学分。
创建：
createprocedurestudent_score@stud_idvarchar（18）
As
Selectstudent.stud_id,stud_name,stud_sex,course.course_id,course_name,score
Fromstudent,course,scorcewherestudent.stud_id=scorce.stud_idand
course.course_id=scorce.course_idandstudent.stud_id=@stud_id
调用：
execstudent_score'01'
2.创建创建存储过程，存储过程名为student_num，要求实现如下功能：
根据课程号输出该课程的选修人数。
Createprocedurescorce_num
@course_idvarchar（8）,
@countintoutput
As
Select@count=count（*）fromscorcewherecourse_id=@course_id
调用过程：
declare@nint
execscorce_num'c1',@noutputprint'该课程的选修人数为:
'+convert（char（4）,@n）
3.创建创建存储过程，存储过程名为proc_1，要求实现如下功能：
根据班级名称输出该班级所有男生的成绩情况表，包括班级、学号、姓名、课程号，成绩、学分等。
并调用此存储过程，显示“07计网”班男生的成绩表
Createprocedureproc_1
@class_idvarchar（10）
As
Selectstudent.class_id，student.stud_id,stud_name,course.course_id,course_name,score，test_num
Fromstudent,course,scorcewherestudent.stud_id=scorce.stud_idand
course.course_id=scorce.course_idandstud_sex=‘男’and
student.class_id=@sclass_id
调用：
execproc_1'07'
4.创建创建存储过程，存储过程名为proc_2，要求实现如下功能：
输入班级名称，产生该班级的基本信息。
调用存储过程，显示“计应06”班的基本信息
Createprocedureproc_2
@class_namevarchar（20）
As
Select*fromclasswhereclass_id=@class_id
调用：
execproc_2'06'
创建创建存储过程，存储过程名为proc_3，要求实现如下功能：
输入班级编号，产生该班级的总人数
createprocclass_count@class_idvarchar（10）,@countintoutput
as
select@count=count（*）fromstudentwhereclass_id=@class_id
调用：
declare@jjint
execclass_count'1',@jjoutput
print@jj
5.在studen表中建立触发器，实现student表和score表的级联删除
Createtriggerstudentdeleteonstudent
afterdelete
asdeletefromscorcewherestud_idin（selectstud_idfromscorce）
6.在score表中建立插入触发器，当向score表中插入数据时，检查student表中学生的毕业标志，如果是1（已毕业）,则不能向成绩表中插入数据。
CREATEtriggerjjondbo.scorce
forinsert
as
ifexists（select*fromstudent,insertedwherestud_flag=1andstudent.stud_id=inserted.stud_id）
begin
raiserror（'已毕业，不能插入',16,1）
rollbacktransaction
end
7.在course表上创建delete的触发器trigger_1,实现当删除课程表中的某门课程的记录时，对应学生表中的所有有关此课程的记录都删除。
CREATETRIGGERtrigger_1ONcourse
FORDELETE
AS
begin
declare@idchar（12）
select@id=course_idfrom
deleted
DELETEscorcewherecourse_id=@id
end
8.创建触发器trigger_2，实现当修改学生基本情况表（student）中的某个学生的学号时，相应的学生成绩表（score）的学号也进行修改。
CREATETRIGGERtrigger_2onstudent
FORUPDATE
AS
begin
declare@id0char（12）
declare@idchar（12）
select@id=stud_idfrominserted
select@id0=stud_idfromd
升级会员 