数据库注释.docx
- 文档编号:3965684
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:13
- 大小:20.70KB
数据库注释.docx
《数据库注释.docx》由会员分享,可在线阅读,更多相关《数据库注释.docx(13页珍藏版)》请在冰豆网上搜索。
数据库注释
droptableSC;/*删除表CS.*,**/
droptableStudent;
droptableCourse;
createtableStudent/*创建表表明student*/
(Snochar(8)primarykey,/*列名称,类型(宽度)主键*/
SnameVarchar(8),
Sexchar
(2)default'男',/*default默认值*/
BirthSmalldatetime,
Classnochar(3),
Entrance_dateSmalldatetime,
Home_addrVarchar(40),
);
createtableCourse
(Cnochar(3)primarykey,
CnameVarchar(20),
Total_periorsmallint,check(Total_perior>0),/*约束check*/
Credittinyint,check(Credit>0andCredit<=6),
);
createtableSC
(Snochar(8),
Cnochar(3),
Gradetinyint,check(Grade>0andGrade<=100),
primarykey(Sno,Cno),/*两个主键*/
foreignkey(Sno)referencesStudent(Sno),/*外键(将表连接起来)*/
/*命名*/foreignkey(Cno)referencesCourse(Cno),
);
altertableStudentaddstaturenumeric(4,2)nullconstraintCK__Student__staturecheck(stature<3.0);/*给表增加ststure列,允许为空值,添加约束*/
altertableStudentaddSdeptchar(20)notnull;
altertableStudentaddPostcodechar(6)nullcheck(Postcodelike'[1-9][0-9][0-9][0-9][0-9][0-9]');
altertableStudentdropconstraintCK__Student__stature;/*删除stature约束*/
altertableStudentdropcolumnstature;/*删除stature列*/
altertableStudentaddconstraintck_datecheck(Entrance_date>Birth);/*在student表中添加约束*/
altertableSCaddconstraintGrade_defaultdefault0forGrade;/*给表SC中的grade列增加默认值约束*/
altertableSCdropconstraintGrade_default;/*删除grade的默认值*/
insertintoStudentvalues('20110001','张虹','男','1992-09-11','051','2011-09-01','南京','计算机系','200413');/*插入数据*/
insertintoStudentvalues('20110002','林红','女','1991-11-12','051','2011-09-01','北京','计算机系','100010');
insertintoStudentvalues('20110003','赵青','男','1993-05-11','061','2011-09-01','上海','软件工程','200013');
insertintoCoursevalues('001','高数',96,6);
insertintoCoursevalues('002','C语言程序设计',80,5);
insertintoCoursevalues('003','Java语言程序设计',48,3);
insertintoCoursevalues('004','Visual_Basic',48,4);
insertintoSCvalues('20110001','001',89);
insertintoSCvalues('20110001','002',78);
insertintoSCvalues('20110001','003',89);
insertintoSCvalues('20110002','002',60);
insertintoSCvalues('20110003','001',80);
/*updateCoursesetCredit=4whereCno='002';/*把course表中的课程号为002的学分改成4,*/
updateCoursesetTotal_perior=64whereCno='002';/*总学时改成64*/
updateSCsetGrade=Grade*0.8whereCno='002';/*把SC表中选修了002的同学的成绩*80%*/
deletefromSCwhereCnoin(selectCnofromCoursewhereCname='C语言程序设计');/*删除SC表中选了C语言的学生的选课记录*/
deletefromSC;/*删除所有学生选课记录*/
/*查询*/
select*fromStudent;/*查询student表中全体学生的全部信息*/
selectSno,SnamefromStudent;/*查询全体学生的学号,姓名*/
selectSno学号,Sname姓名,Home_addr家庭地址信息fromStudent;/*查询时分别加上别名学号、等等、信息*/
select*fromSCwhereGrade>80;/*查询成绩大于80的学生信息*/
select*fromSCwhereGradebetween75and80;*/
selectSnofromSCwhereGrade>80andCno='002';/*查询选了002且成绩大于80的学生信息*/
selectSno,CnofromSCwhereGradeisnull;/*查询没有成绩的学生信息*/
select*fromCoursewhereCnoin(selectCnofromCoursewhereCname='C语言程序设计'orCname='高数');/*从course表中查询高数,C语言的信息*/
select*fromSCwhereGradebetween70and80;/*查询成绩在70-80的学生选课信息*/
select*fromStudentwhereSnamelike'张%';/*从S表中查询所有姓张的同学的选课信息*/
select*fromStudentwhereSnamelike'_红%'orSnamelike'_虹%';/*检索第二个字是红或虹的同学信息*/
selectCreditfromCoursewhereCnamelike'Visual\_Basic'escape'\';/*查询课程名为VB的课程学分
selectTop3*fromCourse;/*检索出前三个课程信息*/
selectTop20percent*fromCourse;/*检索出前20%的课程信息*/
selectdistinctCnofromSC;/*检索出学生已选课程号,要求显示不重复(消除重复行)*/
selectSname,year(getdate())-YEAR(Birth)年龄fromStudent;/*利用getdate函数查询全体学生的姓名及年龄*/
select*fromStudentorderbyClassno,Sno;/*用orderby函数对查询结果进行排序(这里先按照班号排序,然后按照学号*/
selectSname,year(getdate())-YEAR(Birth)年龄fromStudentorderbyyear(getdate())-YEAR(Birth)desc;/*查询全体学生的姓名及年龄,按照年龄的降序排列*/
selectCOUNT(Sno)fromStudent;/*查询学生总人数*/
selectAVG(Grade)平均分,MAX(Grade)最高分,MIN(Grade)最低分fromSCwhereCno='002';/*计算002课程号的学生平均成绩,最高、最低分*/
selectClassno,COUNT(Sno)fromStudentgroupbyClassno;/*查询各班级总人数*/
selectSno,SUM(Grade)总分fromSCgroupbySnohavingSUM(Grade)>150;/*汇总总分大于150分的学生的学号及总成绩*/
selectCno,COUNT(Sno)人数fromSCgroupbyCno;/*查询各个课程号相应的选课人数*/
/*selectSnofromSCgroupbySnocomputeSUM(Grade);*/
select*fromStudentwhereSex='男'and(year(getdate())-YEAR(Birth))>(selectyear(getdate())-YEAR(Birth)fromStudentwhereSname='林红');/*查询年纪比林红大的*/
selectStudent.Sno,Sname,Course.Cno,Cname,GradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno;/*查询所有学生的选课信息,包括****/
selectStudent.Sno,Sname,Cname,GradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno;/*查询以选课的学生的学号,姓名,课程号,成绩*/
selectStudent.Sno,SnamefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.CnoandCourse.Cname='C语言程序设计';/*查询选修了C语言的学生的学号和姓名*/
selectSno,Sname,Home_addrfromStudentwhereClassnoin(selectClassnofromStudentwhereSname='张虹');/*查询与张虹同班的学生的学号姓名住址*/
selectSno,SnamefromStudentwhereClassno!
='051'and(year(getdate())-YEAR(Birth))>(selecttop1year(getdate())-YEAR(Birth)fromStudentorderbyyear(getdate())-YEAR(Birth)desc);/*查询其他班级中比051班所有同学年龄大的学生的学号,姓名*/
selectStudent.Sno,Sname,Cname,GradefromStudent,Course,SCwhereStudent.Sno=SC.SnoandCourse.Cno=SC.Cno;/*查询学生的学号,姓名,学习课程及成绩*/
selectdistinctStudent.Sno,Course.Cno,GradefromStudent,SC,CoursewhereStudent.Sno=SC.SnoandCourse.Cno=SC.CnoandStudent.Snoin(selectStudent.SnofromStudent,SC,CoursewhereStudent.Sno=SC.SnoandCourse.Cno=SC.CnoandCname='高数')andGrade>(selecttop1GradefromSCorderbyGrade)orderbyGradedesc;/*查询选修了高数且成绩至少高于选修课程号为002课程的学生的学号,课程号,成绩,并按成绩从高到低排序*/
selectStudent.Sno,SUM(Grade)总分fromStudent,SCwhereStudent.Sno=SC.SnoandGrade>=60groupbyStudent.Snohavingcount(Student.Sno)>3orderbySUM(Grade)desc;/*查询选修3门以上课程的学生的学号、总成绩(不统计不及格课程)按照总分降序排列*/
selectAVG(Grade)平均分fromSC,CoursewhereCourse.Cno=SC.CnoandCourse.Cnamelike'%3'groupbyCourse.CnohavingCOUNT(SC.Sno)>3;/*查询多余3名学生选修的并以3结尾的课程号的平均成绩*/
selectStudent.Sno,Sname,MAX(Grade)最高分,MIN(Grade)最低分fromStudent,SCwhereStudent.Sno=SC.SnogroupbyStudent.Sno,Snamehaving(MAX(Grade)-5)>MIN(Grade);查询最高分与最低之差大于5分的学生的学号姓名最高最低分*/
createtableStudent_other/*创建一个表同student
(Snochar(8)primarykey,
SnameVarchar(8),
Sexchar
(2)default'男',
BirthSmalldatetime,
Classnochar(3),
Entrance_dateSmalldatetime,
Home_addrVarchar(40),
);
/*altertableStudent_otheraddstaturenumeric(4,2)nullconstraintCK__Student__staturecheck(stature<3.0);
altertableStudent_otheraddSdeptchar(20)notnull;
altertableStudent_otheraddPostcodechar(6)nullcheck(Postcodelike'[1-9][0-9][0-9][0-9][0-9][0-9]');
altertableStudent_otherdropconstraintCK__Student__stature2;
altertableStudent_otherdropcolumnstature;
altertableStudent_otheraddconstraintck_date2check(Entrance_date>Birth);*/
insertintoStudent_othervalues('20110001','张虹','男','1992-09-11','051','2011-09-01','南京','计算机系','200413');
insertintoStudent_othervalues('20110002','林红','女','1991-11-12','051','2011-09-01','北京','计算机系','100010');
insertintoStudent_othervalues('20110004','赵岛','男','1993-05-11','061','2011-09-01','上海','软件工程','200013');
select*fromStudent,Student_otherwhereStudent.Sno=Student_other.Sno;/*查询同时出现在这2个表中的记录
select*fromStudentfullouterjoinStudent_otheron(Student.Sno=Student_other.Sno);
select*fromCourseleftouterjoinSCon(Course.Cno=SC.Cno);/*查询2个表中的所有记录
select*fromStudentfullouterjoin(CourseleftouterjoinSCon(Course.Cno=SC.Cno))on(Student.Sno=SC.Sno);
go
createviewstuview1asselect*fromStudentwhereSex='男'withcheckoption;/*创建一个名字S1的水平视图,从数据库中的STUDENT表中查询出所有男生的资料,并在创建视图时withcheckoption*/
go
createviewstuview2withencryptionasselectCno,Cname,Total_periorfromCoursewhereCredit>3;/*创建一个S2投影视图,从C表中查询学分大于3的所有课程的课程号、课程名、总学时,并给该视图加密*/
go
createviewstuview3asselectSC.Sno,Cno,GradefromSC,StudentwhereSC.Sno=Student.SnoandSex='女'andClassno='051';/*创建S3视图,能检索051班所有女生的学号,课程号及成绩。
*/
go
createviewstuview4as/*创建一个S4视图,能检索出每位选课学生的学号,姓名,总成绩。
*/
selectSC.Sno,Sname,SUM(Grade)SS
fromSC,Student
whereSC.Sno=Student.Sno
groupbySC.Sno,Sname;
go
select*
fromstuview4;
go
sp_helpstuview1;/*查看视图S1的创建信息(通过SP_HELP)*/
go
select*/*查看视图的定义脚本*/
fromsysobjects
wherename='stuview1';
go
selecttextfromsysobjects,syscomments
wherename='stuview1'andsysobjects.id=syscomments.id;
go
sp_helptextstuview1;
go
select*
fromsyscomments
wheretext='stuview1';
go
sp_helptextstuview3;/*查看加密S3的定义脚本*/
go
alterviewstuview2asselectCno,Cname,CreditfromCoursewhereTotal_perior>60;/*修改视图S2。
使其从数据库C表中查询总学时大于60的所有课程的课程号、课程名、学分*/
go
select*
fromstuview2;
go
sp_renamestuview4,stuv4;/*将视图S4改名为stuv4*/
dropviewstuv4;/*将视图4删除*/
altertableStudentaltercolumnSdeptchar(20)null;/*从视图S1中查询出班级为051,名字张虹的资料*/
Select*fromstuview1whereClassno='051'andSname='张虹';
insertintostuview1(Sno,Sname,Classno,Sex,Home_addr,Entrance_date,Birth)/*向S1中插入一行数据,*/
values('20110005','许华','054','男','南京','2011-09-01','1983-01-09');
insertintostuview1(Sno,Sname,Classno,Sex,Home_addr,Entrance_date,Birth)
values('20110006','赵静','054','女','南京','2011-09-01','1983-11-09');
updatestuview1sethome_addr='扬州市'whereclassno='054'andsname='许华';/*将S1表中的许华同学地址改为扬州市*/
deletefromstuview1wheresname='许华';
ifexists(selectnamefromsysobjectswherename='stu_pr'andtype='P')
begindropprocedurestu_prprint'已删除'end/*删除S1表中许华同学的信息*/
else
beginprint'不存在,可创建!
'end
go
实验6
createprocedurestu_pras/*创建一个名为PR的存储过程,能查询出051班学生的所有资料,包括基本信息,选课信息(含未选课同学的信息)要求创建前判断该存储过程是否已经创建,如果有了先删除,并给出已删除否则就输出不存在!
*/
select*fromstudentleftouterjoinsc
on(student.sno=sc.sno)leftouterjoincourse
on(o=o)
whereClassno='0
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 注释