数据库例题.doc

数据库例题第三章关系数据库标准语言SQL33数据定义例题1 定义一个学生-课程模式S-TCreate schema “S-T” Authorization WANG;说明：为用户WANG定义一个模式S-T，需要用户有足够的权限！命令尾加分号，所有标点符号必需是半角符号！命令的大小写的作用？模式名的引号是否必需？例题2 定义没有模式名的模式Create schema Authorization WANG;说明：该语句没有指定，隐含为用户WANG例题3 创建模式同时创建基本表Create schema TEST Authorization ZHANGCreate table TAB1 (COL1 smallint,COL2 int,COL3 char(20),COL4 numeric(10,3),COL5 decimal(5,2);说明：该语句为用户ZHANG创建了一个模式TEST，并且在其中定义一个表TAB1例题4 删除模式Drop schema ZHANG cascade;说明：删除模式ZHANG，使用cascade参数，同时删除已经定义的表TAB1如果使用参数restrict，则表示只删除没有实体表的模式，有实体表则拒绝删除例题5 建立一个“学生”表StudentCreate table Student(Sno Char(9) Primary key,/*列级完整性约束条件，Sno是主码*/Sname Char(20) unique,/*Sname取唯一值*/Ssex char(2),Sage smallint,Sdept char(20);说明：创建一个空的“学生”表Student，并加一些限定条件（Sname的唯一性不合理，用于后面例子进行修改）例题6 建立一个“课程”表CourseCreate table Course(Cno Char(4) Primary key,/*列级完整性约束条件，Cno是主码*/Cname Char(40),Cpno char(4),/*Cpno的含义是先修课*/Ccredit Smallint,foreign key Cpno references Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/);说明：参照表与被参照表是同一表SQL 2008需要使用：foreign key (Cpno) references Course(Cno)例题7 建立学生选课表SCCreate table SC (Sno Char(9),Cno Char(4),Grade smallint,primary key (Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/ foreign key (Sno) references Student(Sno) ,/*表级完整性约束条件，Sno是外码，被参照表是Student*/ foreign key (Cno) references Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course*/ );说明：两个主码，同时都是外码例题8 向Student表增加“入学时间”列，其数据类型为日期型Step1:先创建基本表Create table Student(Sno Char(9) Primary key,/*列级完整性约束条件，Sno是主码*/Sname Char(20) unique,/*Sname取唯一值*/Ssex char(2),Sage char(2),Sdept char(20);GoStep2:alter table Student2 Add S_entrance Datetime;Go说明：不论基本表中原来是否已有数据，新增加列一律为空值例题9 将年龄的数据类型由字符型改为整数型alter table Student alter column Sage int;例题10 增加课程名称必须取唯一值的约束条件。先创建基本表Step1:Create table Course(Cno Char(4) Primary key,/*列级完整性约束条件，Cno是主码*/Cname Char(40),Cpno char(4),/*Cpno的含义是先修课*/Ccredit Smallint,foreign key Cpno references Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/);GoStep2:alter table Course add unique(Cname);Go例题11 删除Student表drop table student2 cascade;drop table student2; -cascade;-SQL Sever中不支持cascade参数例题12 若表上建有视图，选择restrict时表不能删除；cascade时可以删除表，视图也自动被删除。-本例在SQL Server上不能通过测试/*Student表上建立视图*/create view IS_StudentASselect Sno,Sname,SageFrom StudentWhere Sdept=IS;/*删除Student表*/drop table Student restrict;-error XXX/*删除Student表*/drop table Student cascade;-success XXXselect * from IS_Student;-error例题13 创建聚簇索引create cluster index Stusname ON Student(Sname); -XXXcreate clustered index Stusname ON Student(Sname);例题14 创建unique索引create unique index Stusno ON Student(Sno);create unique index Coucno ON Course(Cno);create unique index SCno ON SC(Sno asc,Cno desc);例题15 删除索引-drop index Stusname;-XXXdrop index Course.Coucno;在SQL Server中需要指定索引的表名称3.4 数据查询/*3.4 数据查询* 查询示例需预先录入的数据*/INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215121,李勇,男,20,CS);INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215122,刘晨,女,19,CS);INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215123,王敏,女,19,MA);INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215125,张立,男,18,IS);/*受参照关系影响，不能按课程号顺序插入数据*/INSERT INTO myDB.dbo.Course (Cno,Cname,Ccredit) VALUES (2,数学,2);INSERT INTO myDB.dbo.Course (Cno,Cname,Ccredit) VALUES (6,数据处理,2);INSERT INTO myDB.dbo.Course (Cno,Cname,Cpno,Ccredit) VALUES (4,操作系统,6,3);INSERT INTO myDB.dbo.Course (Cno,Cname,Cpno,Ccredit) VALUES (7,PASCAL语言,6,4);INSERT INTO myDB.dbo.Course (Cno,Cname,Cpno,Ccredit) VALUES (5,数据结构,7,4);INSERT INTO myDB.dbo.Course (Cno,Cname,Cpno,Ccredit) VALUES (1,数据库,5,4);INSERT INTO myDB.dbo.Course (Cno,Cname,Cpno,Ccredit) VALUES (3,信息系统,1,4);INSERT INTO myDB.dbo.SC (Sno,Cno,Grade) VALUES (200215121,1,92);INSERT INTO myDB.dbo.SC (Sno,Cno,Grade) VALUES (200215121,2,85);INSERT INTO myDB.dbo.SC (Sno,Cno,Grade) VALUES (200215121,3,88);INSERT INTO myDB.dbo.SC (Sno,Cno,Grade) VALUES (200215122,2,90);INSERT INTO myDB.dbo.SC (Sno,Cno,Grade) VALUES (200215122,3,80);例题1 查询全体学生的学号与姓名select Sno, SnameFrom Student;例题2 查询全体学生的姓名、学号、所在系select Sname, Sno, SdeptFrom Student;例题3 查询全体学生的详细记录select *From Student;例题4 查询全体学生的姓名及其出生年份select Sname, 2004-SageFrom Student;例题5 查询全体学生的姓名、出生年份和所在院系（小写表示）select Sname,Year of Birth:, 2004-Sage,lower(Sdept)From Student;select Sname Name,Year of Birth: Birth, 2004-Sage BirthDay,lower(Sdept) DeparymentFrom Student;例题6 查询选修了课程的学生学号select SnoFrom SC;select distinct SnoFrom SC;例题7 查询计算机系全体学生的名单select SnameFrom Studentwhere Sdept = CS;例题8 查询20岁以下的学生姓名及年龄select Sname,SageFrom Studentwhere Sage 20;例题9 查询成绩不及格的学生的学号select distinct SnoFrom SCwhere Grade 60;例题10 查询年龄在2023之间select Sname,Sdept,SageFrom Studentwhere Sage between 20 and 23;例题11 查询年龄不在2023之间select Sname,Sdept,SageFrom Studentwhere Sage not between 20 and 23;例题12 查询CS、MA、IS系的学生姓名和性别select Sname,Sdept,SageFrom Studentwhere Sdept in (CS,MA,IS);例题13 查询非CS、MA系的学生姓名和性别select Sname,Sdept,SageFrom Studentwhere Sdept not in (CS,MA);例题14 查询学号为200215121的学生详细情况select *From Studentwhere Sno like 200215121;select *From Studentwhere Sno = 200215121;例题15 查询刘姓学生详细情况select *From Studentwhere Sname like 刘%;例题16 查询姓“欧阳”且命名为3个汉字的学生姓名INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215126,欧阳天,男,18,IS);INSERT INTO myDB.dbo.Student (Sno,Sname,Ssex,Sage,Sdept) VALUES (200215127,欧阳天地,男,18,IS);select SnameFrom Studentwhere Sname like 欧阳_;-SQL标准中，1个_代表一个汉字-2008版本中，欧阳_与欧阳_有差异，尤其是单个_可以代表一个汉字例题17 查询第2个字是“阳”的学生姓名select Sname,SnoFrom Studentwhere Sname like _阳%;-此处的_数量有不同结果例题18 查询不姓“刘”的学生姓名select Sname,Sno,SsexFrom Studentwhere Sname not like 刘%; 例题19 查询DB_Design课程的课程号和学分INSERT INTO myDB.dbo.Course (Cno,Cname,Ccredit) VALUES (8,DB_Design,2);select *From Coursewhere Cname like DB_Design escape ;-使用转义符例题20 查询“DB_”开头，且倒数第3个字符为i的课程的课程号和学分INSERT INTO myDB.dbo.Course (Cno,Cname,Ccredit) VALUES (9,DB_Intro,2);select *From Coursewhere Cname like DB_%i_ escape ;-使用转义符例题21 查没有成绩的学生INSERT INTO myDB.dbo.SC (Sno,Cno) VALUES (200215122,1);select Sno,CnoFrom SCwhere Grade is null;例题22 查所有有成绩的学生select Sno,CnoFrom SCwhere Grade is not null;select distinct SnoFrom SCwhere Grade is not null;例题23 查计算机科学系年龄在20岁以下的学生select Sname,SageFrom Studentwhere Sdept=CS and Sage 3;例题33 查询每个学生及其选修课程的情况select Sno From SCGroup by SnoHaving COUNT(*) 3;例题34 自然连接实现查询每个学生及其选修课程的情况select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFrom Student, SCwhere Student.Sno = Sc.Sno;例题35 自身连接查询间接先修课程的情况select First.Cno, Second.CpnoFrom Course First,Course Secondwhere First.Cpno=Second.Cno;/*取别名*/-select * From Student as First;-select * From Student as Second;-go例题36 外连接(不兼容)/*out - outer; left outer join = left join*/select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFrom Student left out Join SC On (Student.Sno = Sc.Sno);-outer/*也可以使用Using来去掉结果中的重复值*/-From Student left out Join SC Using(SC.Sno);select Student.Sno, Sname, Ssex, Sage, Sdept, Sc.Cno, Sc.GradeFrom Student left Join SC On (Student.Sno = Sc.Sno);例题37 查询选修2号课程且成绩在90分以上的所有学生select Student.Sno, SnameFrom Student ,SCwhere Student.Sno = Sc.Sno and /*连接谓词and*/SC.Cno=2 and SC.Grade 90 ; /*其他限定条件*/例题38 查询每个学生的学号、姓名、选修的课程名及成绩select Student.Sno, Sname, Cname, GradeFrom Student ,SC, Coursewhere Student.Sno = Sc.Sno and Sc.Cno = Course.Cno;例题39 查询与“刘晨”在同一个系学习的学生分步查询：Step1：确定“刘晨”所在的系select SdeptFrom Studentwhere Sname=刘晨;Step2：查找所有该系的学生select Sno, Sname, SdeptFrom Studentwhere Sdept =CS;嵌套查询：（解法1）select Sno, Sname, SdeptFrom Studentwhere Sdept in (select SdeptFrom Studentwhere Sname=刘晨);自身连接：（解法2）select S1.Sno, S1.Sname, S1.SdeptFrom Student S1, Student S2where S1.Sdept = S2.Sdept and S1.Sname = 刘晨;解法1、2表明，同一个查询可以有多个查询方法，其执行效率可能会有较大差别-这就是高水平开发人员所关心内容！例题40 查询选修了课程为“信息系统”的学生学号和姓名嵌套查询：（解法）select Sno, SnameFrom Studentwhere Sno In (select SnoFrom SCwhere Cno in (select CnoFrom Coursewhere Cname=信息系统);连接查询：（解法）select Student.Sno, SnameFrom Student,SC,Coursewhere Student.Sno = SC.Sno AndSC.Cno = Course.Cno AndCourse.Cname=信息系统;例题41 找出每个学生超过他选修课平均成绩的课程号select Sno, CnoFrom SC xWhere Grade = (Select Avg(Grade)From SC yWhere y.Sno = x.Sno);例题42 查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄解法：（any）select Sname, SageFrom StudentWhere Sage Any (Select SageFrom StudentWhere Sdept=CS)And Sdept CS;解法：（max）select Sname, SageFrom StudentWhere Sage (Select Max(Sage)From StudentWhere Sdept=CS) And Sdept CS;例题43 查询其他系中比计算机科学系所有学生年龄都小的学生姓名和年龄解法：（all）select Sname, SageFrom StudentWhere Sage All (Select SageFrom StudentWhere Sdept=CS)And Sdept CS;解法：（min）select Sname, SageFrom StudentWhere Sage (Select Min(Sage)From StudentWhere Sdept=CS)And Sdept CS;例题44 查询所有选修了1号课程的学生姓名select SnameFrom StudentWhere exists (Select * From SCWhere Sno = Student.Sno And Cno=1);例题45 查询没有选修了1号课程的学生姓名select SnameFrom StudentWhere not Exists (Select *From SCWhere Sno=Student.Sno And Cno=1);例题46 查询选修了全部课程的学生姓名select SnameFrom StudentWhere not Exists (Select *From CourseWhere not Exists (Select * From SCWhere Sno=Student.Sno And Cno=Course.Cno);例题47 查询至少选修了200215122选修的全部课程的学生姓名号码Select Distinct SnoFrom SC ScxWhere not Exists (Select *From SC ScyWhere Scy.Sno =200215122 and not Exists (Select * From SC SczWhere Scz.Sno=Scx.Sno And Scz.Cno=Scy.Cno);例题48 查询计算机科学系的学生及年龄不大于19岁的学生Select *From StudentWhere Sdept=CSUnionSelect *From StudentWhere Sage=19;例题49 查询选修了课程1或者选修课程2的学生Select *From SCWhere Cno=1UnionSelect *From SCWhere Cno=2;例题50 查询计算机科学系的学生及年龄不大于19岁的学生的交集Select *From StudentWhere Sdept=CSIntersectSelect *From StudentWhere Sage=19另一种解法：Select *From StudentWhere Sdept=CS and Sage=19;例题51 查询选修了课程1又选修课程2的学生Select SnoFrom SCWhere Cno=1IntersectSelect SnoFrom SCWhere Cno=2;另一种解法：Select SnoFrom SCWhere Cno=1 and Sno In(Select SnoFrom SCWhere Cno=2);例题52 查询计算机科学系的学生及年龄不大于19岁的学生的差集Select *From StudentWhere Sdept=CSExceptSelect *From StudentWhere Sage19;3.5数据更新例题1 将一个新学生元组插入到Student表中INSERTINTO Student(Sno,Sname,Ssex ,Sdept,Sage) VALUES(200215128,陈冬,男,IS,18);例题2 将学生张成民的信息插入到Student表中/*没有属性名，插入的属性必需和定义表顺序一致！*/INSERTINTO Student VALUES(200215126,张成民,男,18,CS);例题3 插入一条选课记录INSERTINTO SC(Sno,Cno) VALUES(200215128,1);或者：INSERTINTO SC VALUES(200215128,1,NULL);例题4 对每个系，求学生的平均年龄，并把结果存入数据库Create table Dept_age (Sdept Char(15), Avg_age Smallint);goINSERTINTO Dept_age (Sdept, Avg_age) Select Sdept, Avg(Sage) From Student Group by Sdept;例题5 修改20215121学生的年龄Update studentSet Sage = 22Where Sno=20215121;例题6 将所有学生的年龄加1岁Update studentSet Sage = Sage+1;例题7 将计算机科学系全体学生的成绩置为0Update SCSet Grade = 0Where CS= (Select Sdept From Student Where Student.Sno=Sc.Sno);例题8 删除学号为200215128的学生记录DeleteFrom StudentWhere Sno=200215128;例题9 删除所有学生选课记录DeleteFrom SC;例题10 删除计算机科学系所有学生选课记录DeleteFrom SCWhere CS= (Select Sdept From Student Where Student.Sno=Sc.Sno);3.6视图例题1 建立信息系学生的视图Create view IS_StudentASSelect Sno,Sname,SageFrom StudentWhere Sdept=IS;例题2 建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生Create view IS_StudentASSelect Sno,Sname,SageFrom StudentWhere Sdept=ISWith check option;-SQL Server有问题例题3 建立信息系选修了1号课程的学生的视图Create view IS_S1(Sno, Sname, Grade)ASSelect Student.Sno,Sname,GradeFrom Student,SCWhere Sdept=IS And Student.Sno=Sc.Sno and SC.Cno=1;例题4 建立信息系选修了1号课程且成绩在90分以上的学生的视图Create view IS_S2ASSelect Sno,Sname,GradeFrom IS_S1Where Grade=90;例题5 建立一个反映学生出生年份的视图Create view BT_S2(Sno, Sname, Sbirth)ASSelect Sno,Sname,2004-SageFrom Student;例题6 将学生的学号和他的平均成绩定义为一个视图Create view S_G(Sno, Gavg)ASSelect Sno,Avg(Grade)From SCGroup by Sno;例题7 将Student表中所有女生记录定义为一个视图Create view F_Student(F_Sno,name,sex,age,dept)ASSelect *From StudentWhere Ssex=女;例题8 删除视图Drop view BT_S2;Drop view IS_S1;Drop view IS_S1 Cascade;-xxx例题9 在信息系学生视图中找出年龄小于20岁的学生Select Sno,SageFrom IS_StudentWhere Sage =90;例题12 在视图中更改学生姓名Update IS_StudentSet Sname=刘辰Where Sno=200215122;例题13 向视图中插入记录insertinto IS_Studentvalues(200215129,赵新,20);-并没有更新Sdept的值为IS，实际为null-而且，当创建视图IS_Student带参数“With check option”时才能执行上面的操作；-否则就报错例题14 删除视图中记录deletefrom IS_StudentWhere Sno=200215129;问题：1. 显示/不显示系统表SQL Sever2000:在Enterprise Manager 中右健单击服务器名，在弹出菜单中选择： 编辑SQL Server 注册属性。有一个选项，不显示系统数据库和系统对象。SQL Sever2005:存放在Views-System Views中，如Sys. Columns中记录“列”的数据SQL Server 2005中的系统数据现在存储在隐藏的“资源”表中，这个表只能被服务器自身直接访问。低级用户(和数据库管理员)必须使用新的一系列的分类视图，这些视图显示了从各种我们看不到