数据库复习1.docx
- 文档编号:7231589
- 上传时间:2023-01-22
- 格式:DOCX
- 页数:14
- 大小:20.24KB
数据库复习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
WHERESage WHERESdept='IS') ANDSdept<>'IS'; 相当于 SELECTSname,Sage FROMStudent WHERESage<(SELECTMAX(Sage)FROMStudent WHERESdept=’IS’) ANDSdept<>’IS’ 17.查询其他系中比信息系中所有学生年龄都小的学生姓名和年龄.SELECTSname,Sage FROMStudent WHERESage WHERESdept=’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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 复习