《数据库实验(共25页).doc》由会员分享，可在线阅读，更多相关《数据库实验(共25页).doc（25页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上实验一 SQL Server数据库的创建与维护1使用SSMS创建名称为“库房管理1”的数据库，要求数据文件初始大小为6MB，自动增长方式是按5%增长，日志文件初始大小为2MB。2. 使用T-SQL语句创建名称为“库房管理”的数据库，要求数据文件初始大小为6MB，自动增长方式是按5%增长，日志文件初始大小为2MB。create database 库房管理 on primary(name=库房管理, filename=E:SQL Server 实验一库房管理.mdf, size=6MB, filegrowth=5%) log on(name=库房管理_log, filen
2、ame=E:SQL Server 实验一库房管理_log.ldf, size=2MB)3. 使用T-SQL语句查看“库房管理”数据库属性。sp_helpdb 库房管理4. 使用T-SQL语句增加“库房管理”数据库的数据文件的容量。将数据文件“库房管理”的增长方式按10%的比例增长。alter database 库房管理modify file( name =库房管理,Filegrowth =10% )5. 使用T-SQL语句增加“库房管理”数据库的日志文件的容量。将日志文件“库房管理_LOG”初始大小改为3MB。alter database 库房管理modify file( name=库房管理_
3、log, size=3MB )6. 使用T-SQL语句缩减“库房管理”数据库的容量。dbcc shrinkdatabase(库房管理)7. 使用T-SQL语句修改数据库名称，将“库房管理1”更名为“库房”。sp_renamedb 库房管理1,库房8. 使用T-SQL语句删除“库房”数据库。drop database 库房9. 分离“库房管理”数据库。10. 附加“库房管理”数据库。实验二 表的创建与维护库房列名数据类型允许NULL值库房编号Char(6)位置Varchar(50)面积smallint产品列名数据类型允许NULL值产品编号Char(6)产品名Varchar(50)价格smalli
4、nt数量int库存列名数据类型允许NULL值库房编号Char(6)产品编号Char(6)数量int创建“库房”、“产品”和“库存”数据表。表结构见上表。A：建“库房”表的T-SQL语句create table 库房(库房编号 char(6) not null primary key,位置 varchar(50) not null,面积 smallint null )B：建“产品”表的T-SQL语句create table 产品(产品编号 char(6) not null,产品名varchar(50) not null,价格 smallint null ,数量 int null )C：建“库存”
5、表的T-SQL语句create table 库存(库房编号 Char(6) not null,产品编号 Char(6) not null,数量 int null )2. 用T-SQL语句删除“库房”数据表。drop table 库房3. 用SSMS创建“库房”表。4. 用T-SQL语句修改表“库房”，将“库房编号”长度设置为8。alter table 库房alter column 库房编号 char(8) not null 5. 用SSMS修改表“库房”，将“库房编号”长度设置为6。6. 使用T-SQL语句在“库房”表中添加管理员一列，其数据类型为char(10)，不能为空。alter tab
6、le 库房add 管理员 char(10) not null7. 使用T-SQL语句在“库房”表中将“管理员”字段删除。alter table 库房 drop column 管理员8. 用SSMS将“库房”表中“库房编号”设置为主键。9. 建立“库房”和“库存”数据表之间的关系。实验三 数据的更新1用T-SQL语句创建Student表，并向表中插入8行数据。表结构具体如下：Student列名数据类型是否主键默认值允许值Stu_IDChar(12)是Stu_NameVarchar(50)Stu_PasswdVarchar(50)SsexChar(2)男男/女SageintSdeptVarchar
7、(50)A、创建Student表的T-SQL语句Use stumanageGoCREATE TABLE Student( stu_ID char(12) primary key, stu_Name varchar(50), stu_Passwd varchar(50), Ssex Char(2) default(男) check (ssex=男or ssex=女), sage int, sdept varchar(50) ) B、用insert values插入8行数据,内容如下。Stu_IDStu_NameStu_PasswdSsexSageSdept1张三女21土木工程系2nullnull
8、nullnullnull3nullnulldefaultnullnull45钱二6张三女21土木工程系7nullnullnullnullnull8nullnullnullnullnull数据的T-SQL语句goinsert student(stu_ID,Stu_Name,Stu_Passwd,Ssex,Sage,Sdept) values(1,张三,女,21,土木工程系)insert student(stu_ID,Stu_Name,Stu_Passwd,Ssex,Sage,Sdept) values(2,null,null,null,null,null) insert student(stu_
9、ID,Stu_Name,Stu_Passwd,Ssex,Sage,Sdept) values(3,null,null,default,null,null)insert student(stu_ID) values(4)insert student(stu_ID,Stu_Name ) values(5,钱二)insert student values(6,张三,女,21,土木工程系)insert student values(7,null,null,null,null,null) insert student values(8,null,null,null,null,null)2. 用T-SQL
10、语句创建Stu表，用insert select插入数据，先把Student表中性别是男的记录插入到Stu表中，再把Student表中性别是女的记录插入到Stu表中，分两次操作。表结构如下：Stu列名数据类型是否主键默认值允许值Stu_IDChar(12)是Stu_NameVarchar(50)Stu_PasswdVarchar(50)SsexChar(2)男男/女SageintA：创建Stu表的T-SQL语句。create table Stu( Stu_id Char(12) primary key, Stu_name Varchar(50), Stu_Passwd Varchar(50),
11、Ssex char(2) default(男) check(ssex=男or ssex=女), Sage int, )B：把Student表中性别是男的记录插入到Stu表中。用insert select插入数据的T-SQL语句。insert Stu(stu_id,stu_name,ssex,sage) select stu_id,stu_name,ssex,sage from student where ssex=男C：再把Student表中性别是女的记录插入到Stu表中。用insert select插入数据的T-SQL语句。insert into Stu (stu_id,stu_name,s
12、age) select stu_id,stu_name,sage from student where ssex=女3. 使用行构造器的insert语句向Student表插入5行数据。具体代码如下。Stu_IDStu_NameStu_PasswdSsexSageSdept1张三女20中文系2李四男21数学系3王五女22物理系4赵六女23化学系5田七女24生物系插入数据的T-SQL语句。insert into student values (1 ,张三,女,20,中文系), (2 ,李四,男,21,数学系), (3 ,王五,女,22,物理系), (4 ,赵六,女,23,化学系), (5 ,田七,
13、女,24,生物系)4. 使用T-SQL语句将Student表中性别为null的记录，性别改为默认值（默认值为男）。修改数据的T-SQL语句。use StuManagegoupdate Student set ssex= default where ssex is null5. 使用T-SQL语句将Student表中姓名为“钱二”的性别替换为null。修改数据的T-SQL语句。update student set ssex=null where stu_name=钱二6. 使用T-SQL语句删除Student表中姓“钱”的记录。删除数据的T-SQL语句。delete from student w
14、here stu_name like 钱%7. 使用T-SQL语句将Student表中姓名为null的记录删除。删除数据T-SQL语句。delete from student where stu_name is null8. 使用T-SQL语句将Stu表中的数据全部删除，即清空表。清空表的T-SQL语句。truncate table Stu实验四 基本查询1、最基本的查询（1）查询所有学生的信息。查询的T-SQL语句。select * from student（2）查询所有学生的学号、姓名。查询的T-SQL语句。select stu_id ,stu_name from student（3）查询
15、所有学生的学号、姓名，并且将输出结果中的列名显示为“学号”、“姓名”。查询的T-SQL语句。select stu_id 学号,stu_name 姓名 from student（4）查询所有学生的姓名和入学年龄（假设表中的学生是大四的学生），并且将数据结果中的列名显示为“姓名”、“入学年龄”。查询的T-SQL语句。select stu_name 姓名,sage-3 入学年龄 from student（5）查询所有学生的姓名和入学年龄（假设表中的学生是大四的学生），并且将数据结果中的列名显示为“姓名”、“入学年龄”、“单位”，其中单位的值为“岁”。查询的T-SQL语句。select stu_nam
16、e 姓名, sage-3 入学年龄, 岁单位 from student2、where子句的使用（1）查询所有化学系学生的信息。查询的T-SQL语句。select * from student where sdept=化学系（2）查询所有化学系女学生的信息。查询的T-SQL语句。select * from student where sdept=化学系 and ssex=女（3）查询物理系和化学系中年龄在22岁以上的学生的信息。查询的T-SQL语句。select * from student where (sdept=化学系or sdept=物理系) and sage223、order by子句
17、的使用（1）查询所有学生的姓名、年龄，并按年龄从小到大的顺序排列。查询的T-SQL语句。select stu_name,sage from student order by sage（2）查询学生课程号为“1”课程的成绩，并按成绩由高到低的顺序输出学号，成绩。查询的T-SQL语句。select stu_id,score from stu_course where course_id =1 order by score desc（3）查询所有学生的姓名和入学年龄（假设表中的学生是大四的学生），并按入学年龄的升序排列。查询的T-SQL语句。select stu_name,sage-3 from s
18、tudent order by 2（4）查询所有学生的信息，按所在系名称的降序排列，在所在系名称相同的情况下，按年龄的升序排列。查询的T-SQL语句。select * from student order by sdept desc,sage4、top和percent的使用（1）查询教师的教师编号、姓名和年龄信息，只显示结果的前3行。查询的T-SQL语句。Select top(3) tea_id,tea_name,tea_age from teacher（2）查询年龄最小的三位教师的教师编号、姓名和年龄信息。A：top子句中使用with ties，查询的T-SQL语句。Select top(3
19、)with ties tea_id,tea_name,tea_age from teacher order by tea_ageB：top子句中不使用with ties，查询的T-SQL语句。Select top(3) tea_id,tea_name,tea_age from teacher order by tea_ageC：top子句中使用with ties和不使用with ties的区别是什么？区别是：使用with ties，则结果显示相同的tea_age一列时，不论个数多少，都显示出来；而不使用with ties，则只显示个数为3个。（3）查询前30%的学生的信息。查询的T-SQL语句
20、。select top(30) percent * from student5、distinct的使用（1）查询选修了课程的学生学号。查询的T-SQL语句。select distinct stu_id from stu_course（2）使用distinct和不使用distinct的区别是什么？区别是： 使用了 distinct，则只显示每种学号类型，不显示全部相同学号；而不使用distinct则把全部满足条件的结果显示出来，不考虑重复项。6、like和通配符的使用（1）查询姓“李”的学生的学号、姓名、性别和所在系的信息。查询的T-SQL语句。select stu_id,stu_name,ss
21、ex,sdept from Student where Stu_Name like 李%（2）查询不是姓“李”的学生的学号、姓名、性别和所在系的信息。查询的T-SQL语句。select stu_id,stu_name,ssex,sdept from Student where Stu_Name not like 李%（3）查询姓名是三个字且第二个字是“正”的学生的学号、姓名和性别信息。查询的T-SQL语句。select stu_id,stu_name,ssex from Student where Stu_Name like _正_（4）查询学号以“”开始的学生的学号、姓名和性别信息。查询的T
22、-SQL语句。select stu_id,stu_name,ssex from Student where Stu_ID like _（5）查询姓“李”、“王”和“郭”的学生的学号、姓名和性别信息。查询的T-SQL语句。select stu_id,stu_name,ssex from Student where Stu_Name like 李王郭%（6）查询不是姓“李”、“王”和“郭”的学生的学号、姓名和性别信息。select stu_id,stu_name,ssex from Student where Stu_Name not like 李王郭%7、between的使用（1）使用betwe
23、enand，查询年龄在20到22之间的学生的学号、姓名和性别信息。select stu_id,stu_name,ssex from Student where Sage between 20 and 22（2）不使用betweenand，查询年龄在20到22之间的学生的学号、姓名和性别信息。select stu_id,stu_name,ssex from Student where Sage=20 and Sage909、分组查询（1）计算每个系的学生人数。select sdept,COUNT( * ) from Student group by Sdept（2）查询每个学生已获得的学分。se
24、lect stu_id,SUM(credit) from stu_course group by stu_id（3）使用having子句，查询学分超过10的学生的学号和学分。select stu_id,SUM(credit) from stu_course group by stu_id having SUM(credit)10实验五 高级查询1、多表连接查询（1）查询选修了“高等数学”课程并且成绩不低于80分的学生的学号。A：要求使用“在from中用join连接多个表”的查询语句。select Student.Stu_ID from Student inner join Stu_Course
25、 on Student.Stu_ID=Stu_Course.Stu_ID join course on Stu_Course.Course_ID = Course.Course_IDwhere Course.Course_Name=高等数学 and stu_course.Score=80B：要求使用“在from中不用join连接多个表，而在where中连接多个表”的查询语句。select Stu_ID from Stu_Course,Course where Stu_Course. Course_ID = Course. Course_ID and Course_Name=高等数学and Sc
26、ore =80（2）查询“数据结构”课程不及格的学生的学号、姓名及成绩。A：要求使用“在from中用join连接多个表”的查询语句。select Student.Stu_ID,Stu_Name,Score from Student inner join Stu_Course on Student.Stu_ID=Stu_Course.Stu_IDjoin course on Stu_Course.Course_ID = Course.Course_IDwhere Course.Course_Name=数据结构 and stu_course.Score=60B：要求使用“在from中不用join连
27、接多个表，而在where中连接多个表”的查询语句。select Student.Stu_ID , Stu_Name , Score from Student,stu_course,course where course_name=数据结构 AND score60 and Course.Course_ID=Stu_Course.Course_ID and Stu_Course. Stu_ID = Student. stu_ID（3）查询每个选修了课程的学生及其选修课程的情况，要求输出学号、姓名、所选课程名及分数。select Student.Stu_ID,Student.Stu_Name,Cou
28、rse.course_name,Stu_Course.scorefrom student inner join stu_course on Student. stu_ID=Stu_Course.stu_ID join course on Stu_Course.Course_ID = Course.Course_ID （4）查询每个有学生（包括选修和未选修课程的所有学生）及其选修课程的情况。select a. *,b.* from Student a left outer join Stu_course b on a. stu_ID=b. stu_ID（5）查询所有有先修课且课程学分是4分的课程
29、编号、课程名称以及先修课的课程编号、课程名称。select a.course_ID 课程编号,a.course_name 课程名称,b.course_ID 先修课编号,b.course_name 先修课名称 from course as a join course as b on a.course_pro_ID =b.course_ID where a. Course_Credit=42、无关子查询（1）查询和马宁同年龄、同性别的教师的教师编号、姓名及性别。select Tea_ID,Tea_Name,Tea_Sex from teacherwhere Tea_Age (select Tea_
30、Age from Teacher where Tea_Name=马宁)AND Tea_Name 马宁AND Tea_Sex=(select Tea_Sex from Teacher where Tea_Name=马宁)AND Tea_Name 马宁（2）查询所有选修了2号课程的学生的学号及姓名。select Stu_ID ,Stu_name from Student where Stu_ID IN (select Stu_ID from Stu_course where course_ID =2)（3）查询选修了没有先修课的课程的学生的学号，课程号和成绩。select stu_id ,cour
31、se_id ,score from stu_course where course_id in (select course_id from course where course_pro_id is null)（4）查询选修了没有先修课的课程的学生的学号及姓名。select stu_id , stu_name from student where stu_id not in (select stu_id from stu_course)（5）查询其他系中比外语系所有学生年龄都小的学生的学号、姓名及年龄，并按年龄降序排列。select stu_id , stu_name, sage from
32、student where sage all(select sage from student where sdept = 外语系) and sdept 外语系 order by sage desc（6）查询其他系中比外语系所有学生年龄都大的学生的学号、姓名及年龄，并按年龄升序排列。select stu_id , stu_name, sage from student where sage all(select sage from student where sdept = 外语系) and sdept 外语系 order by sage （7）查询其他系中比外语系学生的最小年龄要大的学生的学
33、号、姓名及年龄，并按学号降序排列。select stu_id , stu_name, sage from student where sage any(select sage from student where sdept = 外语系) and sdept 外语系 order by stu_id desc（8）查询其他系中比外语系学生的最大年龄要小的学生的学号、姓名及年龄，并按学号升序排列。select stu_id , stu_name, sage from student where sage any(select sage from student where sdept = 外语系)
34、 and sdept 外语系 order by stu_id asc3、相关子查询（1）查询成绩小于各门课程平均值的所有学生的学号、所修课程号及成绩，并按课程号升序排列，课程号相同的按成绩降序排列。select stu_id ,course_id ,score from stu_course a where score (select AVG(score) from stu_course where course_id= a.course_id) order by course_id asc, score desc （2）查询所有选修了7号课程的学生的学号及姓名。A：使用EXISTS相关子查询
35、。select stu_id , stu_name from student a where exists (select stu_id from stu_course where stu_id=a. stu_id and course_id= 7)B：不使用EXISTS，使用无关子查询。select stu_id , stu_name from student where stu_id in (select stu_id from stu_course where course_id= 7 )C：不使用子查询，使用多表连接查询。select a.stu_id , stu_name from
36、student a inner join stu_course b on a.stu_id= b.stu_id and course_id= 7 )（3）查询所有未选修了7号课程的学生的学号及姓名。A：使用NOT EXISTS相关子查询。select stu_id , stu_name from student a where not exists (select * from stu_course where stu_id= a.stu_id and course_id= 7 )B：不使用NOT EXISTS，使用无关子查询。select stu_id , stu_name from stu
37、dent where stu_id not in (select stu_id from stu_course where course_id= 7 )4、使用子查询修改数据（1）对每一个系，求学生的平均年龄，平均年龄列命名为Avg_age，并把结果存入新建的数据表中。A：使用在INSERT中嵌套子查询的方法。新建的数据表名为Sdept_Age。create table sdept_age (sdept char(50),avg_age int ) insert into sdept_age (sdept,avg_age) select sdept, AVG(sage) from studen
38、t group by sdept B：使用SELECT INTO的方法。新建的数据表名为Sdept_Age1。select sage,sdept into sdept_age 1 from studentC：使用A、B两种方法的区别是什么？ 区别：使用insert嵌套子查询不用建表，将其它表的行添加到当前表中；select into 则可以将语句根据查询结果生成新表。（2）将所有学生的“数据处理”课程的成绩加10分。update stu_course set score =score+10 where course_id in (select course_id from course whe
39、re course_name = 数据处理)（3）删除选修“数据处理”的选课记录。delete from stu_course where course_id in (select course_id from course where course_name = 数据处理)5、使用集合运算符对查询结果并、差、交操作（1）查询在2号和3号课程中，至少选修了一门课程的学生学号及课程号，要求不要出现重复行。A：使用集合运算符。select stu_id,course_id from stu_course where course_id= 2 union select stu_id,course_i
40、d from stu_course where course_id= 3B：不使用集合运算符，而是在WHERE中使用多个条件。select stu_id,course_id from stu_course where course_id= 2 or course_id = 3（2）查询选修了2号课程但没有选修3号课程的学生的学号。A：使用集合运算符。select stu_id from stu_course where course_id= 2 except select stu_id from stu_course where course_id= 3B：不使用集合运算符，而是在WHERE中
41、使用多个条件。select stu_id from stu_course where stu_id not in (select stu_id from stu_course where course_id = 3) and course_id = 2（3）查询选修了2号课程又选修了3号课程的学生的学号。A：使用集合运算符。select stu_id from stu_course where course_id = 2 intersect select stu_id from stu_course where course_id = 3B：不使用集合运算符，使用子查询。select stu_
42、id from stu_course where stu_id in ( select stu_id from stu_course where course_id = 2) and course_id= 3（4）查询“李建丽”和“郭芳芳”同学共同选修课程的课程名。A：使用集合运算符。select course_name from course a join stu_course b on a.course_id = b. course_id join student c on c.stu_id=b.stu_id where c.stu_name = 李建丽 intersect select course_name from course a join stu_course b on a.course_id = b. course_id join student c on c.stu_id=b.stu_id where c.stu_name = 郭芳芳B：不使用集合运算符，使用子查询。select course_name from course a join