数据库第3章上机报告.doc

实验二实习报告内容：(1)连接后打开左面的树状表， Microsoft Sql Server-SQL Server组-LOCAL 下面有数据库，数据转换服务，管理，复制，安全性等等，右击数据库，选择新建数据库(2开始-MS SQL Serve -查询分析器（或在企业管理器界面中：开始-查询分析器）-在查询分析器下拉选择框中，选中GrandeManager，使之成为当前操作的数据库，在编辑框中键入SQL语句创建Worker表思考题：1：三种；主数据文件的后缀是mdf；次数据文件的后缀是ndf；日志文件的后缀是ldf。2：1)字符型数据：1.char(n)：长度为n的固定长度字符串，其中n最大可以是8000;2.varchar(n):表示一个长度为n的固定长度字符串，其中n最大可以达到800。 3.text：如果字符长度超过8000个，则可使用text数据类型，长度达2GB。2)整型数据类型：1.Bigint：8个字节，64位 2.Int:4个字节，32位 3.Smallint：2个字节，16位 3.Tinyint：1个字节，8位3)精确数据类型：decinal和numeric4)浮点数据类型：1.float：7位有效数字，4个字节或15位有效数字，8个字节2.Real：4个字节5)货币型数据类型：1.money 2.smallmoney6)日期时间型数据类型：1.Datetime：8个字节 2.Smalldatetime:4个字节7)统一字符数据类型8) 布尔数据类型实验三：CREATE TABLE Student(Sno Char(7)NOT NULL UNIQUE,Sname VarChar(20)NOT NULL,Ssex Char(2)NOT NULL,Sage Smallint NOT NULL,Clno Char(5) Not NULL);INSERT INTO StudentVALUES(2000101, 李勇, 男, 20, 00311);INSERT INTO StudentVALUES(2000102, 刘诗晨, 女, 19, 00311);INSERT INTO StudentVALUES(2000103, 王一鸣, 男, 20, 00312);INSERT INTO StudentVALUES(2000104, 张婷婷, 女, 21, 00312);INSERT INTO StudentVALUES(2001101, 李勇敏, 女, 19, 01311);INSERT INTO StudentVALUES(2001102, 贾向东, 男, 22, 01311);INSERT INTO StudentVALUES(2001103, 陈宝玉, 男, 20, 01311);INSERT INTO StudentVALUES(2001104, 张逸凡, 男, 21, 01311);CREATE TABLE Course(Cno Char(1)NOT NULL,Cname VarChar(20)NOT NULL,Credit Smallint NOT NULL);INSERT INTO CourseVALUES(1, 数据库, 4);INSERT INTO CourseVALUES(2, 离散数学, 3);INSERT INTO CourseVALUES(3, 管理信息系统, 2);INSERT INTO CourseVALUES(4, 操作系统, 4);INSERT INTO CourseVALUES(5, 数据结构, 4);INSERT INTO CourseVALUES(6, 数据处理, 2);INSERT INTO CourseVALUES(7, C语言, 4);CREATE TABLE Class(Clno Char(5) NOT NULL,Speciality VarChar(20) NOT NULL,Inyear Char(4) NOT NULL,Number Integer NOT NULL,Monitor Char(7) NOT NULL,);INSERT INTO ClassVALUES(00311, 计算机软件, 2000, 120, 2000101);INSERT INTO ClassVALUES(00312, 计算机应用, 2000, 140, 2000103);INSERT INTO ClassVALUES(01311, 计算机软件, 2001, 220, 2001103);CREATE TABLE Grade(Sno Char(7),Cno Char(1),Gmark Numeric(4,1);INSERT INTO GradeVALUES(2000101, 1, 92);INSERT INTO GradeVALUES(2000101, 3, 88);INSERT INTO GradeVALUES(2000101, 5, 86);INSERT INTO GradeVALUES(2000102, 1, 78);INSERT INTO GradeVALUES(2000102, 6, 55);INSERT INTO GradeVALUES(2000103, 3, 65);INSERT INTO GradeVALUES(2000103, 6, 78);INSERT INTO GradeVALUES(2000103, 5, 66);INSERT INTO GradeVALUES(2000104, 1, 54);INSERT INTO GradeVALUES(2000104, 6, 83);INSERT INTO GradeVALUES(2001101, 2, 70);INSERT INTO GradeVALUES(2001101, 4, 65);INSERT INTO GradeVALUES(2001102, 2, 80);INSERT INTO GradeVALUES(2001102, 4, 90);INSERT INTO GradeVALUES(2000103, 1, 83);INSERT INTO GradeVALUES(2000103, 2, 76);INSERT INTO GradeVALUES(2000103, 4, 56);INSERT INTO GradeVALUES(2000103, 7, 88);11.alter table Studentadd Nation varchar(20);alter table Studentdrop column Nation;/ 删除新增的属性Nationinsert into Gradevalues(2001110,3,80);/ 向成绩表中插入记录(2001110,3,80)update Gradeset Gmark = 70where Sno = 2001110;/ 将学号为“”的学生的成绩修改为分delete from Gradewhere Sno = 200110;/ 删除学号为“”的学生的成绩记录create index IX_Classon Student(Clno);/ 在学生表的Clno属性上创建一个名为IX_Class的索引，以班级号的升序排序 drop index Student.IX_Class;/ 删除IX_Class索引思考题：1：规定相应的不能为空实验四：select distinct Cnofrom Grade;/ 找出所有被学生选修了的课程号 select*from Studentwhere Clno = 01311 and Ssex = 女;/ 找出班女学生的个人信息 select Sname, Ssex, 2016-Sage as Year from Student where Clno = 01311 or Clno = 01312; /找出班和班的学生姓名和、性别、出生年份 select* from Student where Sname like 李% /找出姓李的学生的个人信息 select Number from Class where Clno in (select Clno from Student where Sname = 李勇);/ 找出李勇所在班级的学生人数select AVG(Gmark) as 平均成绩, MAX(Gmark) as 最高分, MIN(Gmark) as 最低分from Gradewhere Cno in (select Cnofrom Coursewhere Cname = 操作系统);/ 找出课程名为操作系统的平均成绩，最高分，最低分select count (distinct Sno) as 学生人数from Grade/选修了课程的学生人数select count (distinct Sno) as 学生人数from Gradewhere Cno in (select Cno from Coursewhere Cname = 操作系统);/ 选修了操作系统的学生人数 SELECT Sname 学生姓名 FROM(SELECT * FROM StudentWHERE Clno in(SELECT Clno FROM ClassWHERE Speciality=计算机软件 AND Inyear=2000) ) ruanjianWHERE ruanjian.Sno not in(SELECT DISTINCT Sno FROMGrade)with ruanjian as(SELECT Sno,SnameFROM Student join Classon Student.Clno=Class.ClnoWHERE Class.Speciality=计算机软件 and Class.Inyear=2000)SELECT Sname 学生姓名 FROM ruanjianWHERE not exists(SELECT * FROM GradeWHERE ruanjian.Sno=Grade.Sno);/ 找出级计算机软件班的成绩为空的学生姓名2、不对，聚合函数不应该出现在where语句中，除非该聚合位于HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用思考题：如果遇到子查询,就基本上都需要别名.或者认为实际表名写起来太麻烦时,可以用一个字母的别名来代替实验五： select*from Studentwhere Sname李勇 and Clno in(SELECT Clnofrom Studentwhere Sname = 李勇);/ 找出和李勇在同一个班级的学生的信息 select*from Studentwhere Sname李勇 and exists(select*from Gradewhere Student.Sno = Grade.Sno and Grade.Cno in(select Cnofrom Gradewhere Sno in(select Snofrom Studentwhere Sname = 李勇)/ 找出所有与学生李勇有相同选修课程的学生信息select*from Studentwhere Sage (select Sagefrom Studentwhere Sname = 李勇)/ 找出年龄介于学生李勇和岁之间的学生信息 select Sno as 学号, Sname as 姓名from Studentwhere exists(select*from Grade where Cno in(Select Cno from Coursewhere Cname = 操作系统) ) /选修了课程是操作系统的学生学号和姓名 select Sname as 姓名from Studentwhere not exists(select*from Gradewhere Student.Sno = Grade.Sno and Cno = 1);/ 找出没有选修号课程的所有学生姓名 select Sname as 姓名from Studentwhere not exists(select Cno from Courseexceptselect Cnofrom Gradewhere Student.Sno = Grade.Sno)/ 找出选修了全部课程的学生姓名select Sno as 学号, Gmark as 成绩from Gradewhere Cno = 3order by Gmark desc /查询选修了号课程的学生学号及成绩,并按成绩的降序排列select*from Studentorder by Clno asc,Sage desc;/ 查询全体学生的信息，要求查询结果按班级号升序排列，同一班级学生按年龄降序排列select Grade.Cno as 课程号, COUNT(Grade.Cno) as 选课人数from Grade join Course on Grade.Cno = Course.Cnogroup by Grade.Cno;/ 求每个课程号及相应的选课人数select Sno as 学号 from Gradegroup by Snohaving COUNT(Sno)3;/ 查询选修了3门以上课程的学生学号思考题：1：UNION 命令只会选取不同的值，UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。2：选用连接词查询好。 因为连接词查询,效率比嵌套查询高。3：库函数可以使用在SELECT选取目标、HAVING字句、WHERE子句，不能使用在GROUP BY列名。实验六： update Gradeset Gmark = 0where Sno in (select Snofrom Studentwhere Clno=01311);/ 将班的全体学生的成绩置零 delete from Gradewhere Sno in(Select Clnofrom Studentwhere Clno in(select Clnofrom Classwhere Speciality = 计算机软件 and Inyear = 2001);/ 删除级计算机软件的全体学生的选课记录delete from Gradewhere Sno in(select Sno from Student where Sname = 李勇)update Class set Number = Number - 1where Clno in(select Clno from Studentwhere Sname = 李勇)update Class set Monitor = casewhen Monitor =(select Sno from Student where Sname = 李勇)thenendfrom Classwhere Clno in(select Clnofrom student where Sname=李勇)delete from Studentwhere Sname = 李勇/学生李勇已经退学，从数据库中删除有关他的记录alter table Classadd avage Smallint nullupdate classset Cage = casewhen Clno=00311then(select AVG(Sage)from Student where Clno=00312)when Clno =01311then(select AVG(Sage)from Studentwhere Clno =01311)endfrom Class/对每个班，求学生的平均年龄，并把结果存入数据库create view Stu_01311_1as select Student.Sno, Sname, Gmarkfrom Student,Gradewhere Cno = 1and Clno = 01311and Student.Sno = Grade.Sno with check option/建立班选修了号课程的学生视图Stu_01311_2create view Stu_01311_2as select*from Stu_01311_1where Gmark1990;/ 查询以后出生的学生姓名SELECT * FROM Stu_yearWHERE Sno in (SELECT Sno FROM Stu_01311_2);/ 查询班选修了号课程并且成绩不合格的学生的学号、姓名、出生年份2正确思考题：DROP是删除表；DELETE是删除表中的记录实验七：1create view Stu_01312_1AS select *From student,Gradewhere clno=01312 and