数据库实验-电子科技大学

计算机专业类课程实验报告课程名称：数据库系统及应用学院：计算机科学与工程专业：计算机科学与技术学生姓名：李鹤鸣指导教师：郑莉华日期：2016年4月24日

电子科技大学实验报告实验一实验名称：创立，备份与恢复数据库实验学时：2实验内容和目的：本实验要求学生掌握创立数据库的方法及相关操作，向数据库中添加样本数据，学习SQLSERVER数据库的恢复和备份。创立数据库，命名为DB+学号，如：DB000000001该数据库包括5个表：建议：在定义表中字段时，尽量使用意义的英文单词。‘系别代码表’ 表名：dep‘教师表’ 表名：teacher‘学生表’ 表名：stud‘课程表’ 表名：course‘选课表’ 表名：sc为每个表准备大约5-10条记录，使用Insert语句将这些数据插入到相应表中数据录入完成后，将数据库备份到磁盘上，在以后的的试验中备用。实验原理：使用数据库管理系统DB、DDL创立数据库及数据库对象。实验器材〔设备、元器件〕计算机，MicrosoftSQLsever2014实验步骤：启动SQLSERVER创立数据库：STUD创立表‘系别代码表’表名：dep其结构如下：〔该表的主键为’系代码’〕字段名称字段类型字段大小/格式是否可为空系代码depidvarchar8否PK系名depnamevarchar20否Notnull‘教师表’表名：teacher其结构如下：〔该表的主键为’教师号’〕字段名称字段类型字段大小/格式是否可为空教师号tidVarchar8否PK教师名tnameVarchar8否Notnull职称titleVarchar10是所属院系编号depidVarchar20是 ‘学生表’ 表名：student 其结构如下：〔该表的主键为’学生号’〕字段名称字段类型字段大小/格式是否可为空学号sidVarchar11否PK学生名snameVarchar8否Notnull性别sexChar2否院系编号depidVarchar20是出生年月birthdDate是邮箱semailVarchar20是家庭地址homeaddrVarchar40是‘课程表’表名：course其结构如下：〔该表的主键是课程号〕字段名称字段类型字段大小/格式是否可为空课程号cidvarchar8否PK课程名cnamevarchar30否Notnull先修课程号cid_prevarchar8是学分creditsnumeric3(小数位数1)否Notnull‘选课表’表名：sc其结构如下：〔该表的主键是课程号〕字段名称字段类型字段大小/格式是否可为空学号sidvarchar8否Notnull,PK课程号cidvarchar8否Notnull,PK教师号tidvarchar8否成绩scoreinteger是备份数据库恢复数据库PK:主键，Notnull:不能取空值实验数据及结果分析：Createtabledep(depidvarchar(8)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnullprimarykey,tidvarchar(8)notnull,scoreinteger);insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','信软');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintoteacher(tid,tname,title,depid)values('200601','刘琦','讲师','03');insertintoteacher(tid,tname,title,depid)values('200602','林闲','讲师','02');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','04');insertintoteacher(tid,tname,title,depid)values('200608','孙峰','讲师','05');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲珑','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肃','男','05','1994.5.26','8794164@163','湖北省武汉市');insertintocourse(cid,cname,cid_pre,credits)values('00101','离散数学','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大学物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','数据结构','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率论','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','计算机导论','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200601','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200608','84');insertintosc(sid,cid,tid,score)values('201405','00105','200602','86');实验结论、心得体会和改良建议：按步骤进行试验，本实验相对简单。

电子科技大学实验报告实验二实验名称：数据库的完整性实验学时：4实验内容和目的：通过设置表的检查约束、外键约束体会数据库完整性的含义，约束条件下数据修改操作的限制，以及实现修改操作的技巧。表结构中的完整性约束条件如下：PK:主键，FK:外键，其它::约束条件‘系别代码表’表名：dep其结构如下：〔该表的主键为’系代码’〕字段名称字段类型字段大小/格式是否可为空约束条件系代码depidvarchar8否PK系名depnamevarchar20否Notnull‘教师表’表名：teacher其结构如下：〔该表的主键为’教师号’〕字段名称字段类型字段大小/格式是否可为空约束条件教师号tidVarchar8否PK教师名tnameVarchar8否Notnull职称titleVarchar10是所属院系编号depidVarchar20是 ‘学生表’ 表名：student 其结构如下：〔该表的主键为’学生号’〕字段名称字段类型字段大小/格式是否可为空约束条件学号sidVarchar11否PK学生名snameVarchar8否Notnull性别sexChar2否院系编号depidVarchar20是FK出生年月birthdDate是邮箱semailVarchar20是家庭地址homeaddrVarchar40是‘课程表’表名：course其结构如下：〔该表的主键是课程号〕字段名称字段类型字段大小/格式是否可为空约束条件课程号cidvarchar8否PK课程名cnamevarchar30否Notnull先修课程号cid_prevarchar8是学分creditsnumeric3(小数位数1)否Notnull‘选课表’表名：sc其结构如下：〔该表的主键是课程号〕字段名称字段类型字段大小/格式是否可为空约束条件学号sidvarchar8否Notnull,PK,FK课程号cidvarchar8否Notnull,PK,FK教师号tidvarchar8否Notnull,FK成绩scoreinteger是0<score<100设置选课表的三个外键约束〔学号，课程号，教师号〕设置教师表，学生表中的院系字段(depid)的外键约束设置学生表中姓名字段为非空字段〔必须有数据，不能是空值〕设置选课表中成绩字段的取值范围是0到100设置学生表中性别字段的取值为’男’或’女’设置学生表电子邮件字段的取值必须包含@符号向’系别代码表’添加数据，数据如下：向’教师表’添加数据,2条向’学生表’添加数据,5条以上向’课程表’添加数据,3条以上向’选课表’添加数据,10条实验原理：数据库库的完整性、约束条件、结构化查询语言。实验器材〔设备、元器件〕计算机，MicrosoftSQLsever2014.实验步骤：恢复数据库，将数据库备份stud恢复到SQLSERVER中执行SQL命令完成实验内容备份数据库实验数据及结果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','信软');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲珑','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肃','男','05','1994.5.26','8794164@163','湖北省武汉市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','离散数学','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大学物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','数据结构','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率论','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','计算机导论','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');实验结论、心得体会和改良建议：注意sql语句语法，认真编写代码根本就可以顺利完成实验。电子科技大学实验报告实验三一实验名称：数据的修改二实验学时：2三实验内容和目的：练习UPDATE、DELETE命令的使用，实现对数据的修改和删除。将院系中，原院系名’IS’改为’Information‘在选课表中，删除计算机科学与工程系学生选修2号课程的记录在选课表中，删除软件工程系学生选课1号课程的纪录记录学号为2406010103的同学由原来的计算机科学与工程系转入信息平安系，学号更改为2406030102，在数据库中做出相应修改。四实验原理：使用结构化查询语言，在满足约束条件的情况下完成数据修改五实验器材〔设备、元器件〕计算机，MicrosoftSQLsever六实验步骤：恢复数据库，将数据库备份stud恢复到SQLSERVER中执行SQL命令完成实验内容备份数据库七实验数据及结果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(20)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(20)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','软件工程');insertintodep(depid,depname)values('03','信息平安');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintodep(depid,depname)values('06','通信');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010101','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020101','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020102','孔玲珑','女','02','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406030101','雷破','男','03','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406040101','李肃','男','04','1994.5.26','8794164@163','湖北省武汉市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010102','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010103','石心远','男','01','1996.12.30','45161664@163','吉林省长春市');insertintocourse(cid,cname,cid_pre,credits)values('0001','离散数学','0002','2');insertintocourse(cid,cname,cid_pre,credits)values('0002','大学物理下','0003','2');insertintocourse(cid,cname,cid_pre,credits)values('0003','数据结构','0004','3');insertintocourse(cid,cname,cid_pre,credits)values('0004','概率论','0005','4');insertintocourse(cid,cname,cid_pre,credits)values('0005','计算机导论','0001','1');insertintocourse(cid,cname,cid_pre,credits)values('0006','计算机网络','0007','2');insertintocourse(cid,cname,cid_pre,credits)values('0007','数字逻辑','0006','2');insertintosc(sid,cid,tid,score)values('2406010101','0002','200604','80');insertintosc(sid,cid,tid,score)values('2406020101','0001','200605','76');insertintosc(sid,cid,tid,score)values('2406010102','0003','200605','80');insertintosc(sid,cid,tid,score)values('2406030101','0004','200604','84');insertintosc(sid,cid,tid,score)values('2406040101','0005','200604','86');insertintosc(sid,cid,tid,score)values('2406010102','0006','200605','87');insertintosc(sid,cid,tid,score)values('2406010103','0007','200604','95');updatedepsetdepname='information'wheredepname='IS';//修改IS系名称为informationdeletefromscwheresidlike'240601010_'andcid='0002';deletefromscwheresidlike'240602010_'andcid='0001';//删除计算机学院选择1号课程与软件学院选择2号课程的同学deletefromscwheresid='2406010103';updatestudentsetsid='2406030102'anddepid=’03’wheresname='石心远';insertintosc(sid,cid,tid,score)values('2406030102','0007','200604','95');//计算机学院学号为2406010103的同学转入信息平安学院，学号变为2406030102，并修改相关数据八实验结论、心得体会和改良建议：熟练掌握delete，update，rename的相关语法用法，确定结构并设计好相关代码即可。

电子科技大学实验报告实验四1实验名称：简单查询、多表查询2实验学时：23实验内容和目的：练习用SELECT查询语句，设置查询条件，实现单表查询。练习使用SELECT语句从多个表中查询数据，表的内连接、左外连接、右外连接的使用以及设置连接条件，理解连接条件和查询条件的在目的和功能上的区别。查询年龄在20—22之间的学生姓名〔通过出生日期和当前日期计算年龄，方法见第八章〕查询年龄在20—22之间的学生姓名、院系和年龄查询姓’张’的学生的学号、姓名、邮件地址查询所有有成绩〔成绩不为空〕的学生学号和课程号查询选修了2号课程成绩在60分以下的所有学生的学号、姓名、学生的邮件地址、课程名、教师姓名和教师的邮件地址及课程成绩查询既选修了1号课程，又选修了2号课程的学生学号4实验原理：结构化查询语言、表的连接、关系运算5实验器材〔设备、元器件〕计算机，MicrosoftSQLsever20146实验步骤：恢复数据库，将数据库备份stud恢复到SQLSERVER中执行SQL命令完成实验内容7实验数据及结果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','信软');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲珑','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肃','男','05','1994.5.26','8794164@163','湖北省武汉市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','离散数学','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大学物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','数据结构','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率论','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','计算机导论','00010','1');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');insertintosc(sid,cid,tid,score)values('201405','00102','200604','56');selectsname姓名,depid院系号,datediff(year,birthd,getdate())as年龄fromstudentwheredatediff(year,birthd,getdate())between20and22;//选择年龄在20到22岁之间的学生输出姓名，院系，年龄selectsid学号,sname姓名,semail邮件地址fromstudentwheresnamelike'张%';//选择姓张的学生输出学号，姓名，邮件地址selectsid学号,cid课程号fromscwherescoreisnotnull;//选择成绩不为空的同学输出学号和课程号selectsc.sid学号,sname姓名,semail邮件地址,cname课程名,tname教师姓名, temail教师邮件地址,score成绩fromsc,student,teacher,coursewheresc.cid='00102'andsc.score<60and sc.sid=student.sidand sc.tid=teacher.tidand sc.cid=course.cid;//选择选修2号课程并且不及格的同学输出学号，姓名，邮件地址，课程名，教师姓名，教师邮件地址和成绩selectsid学号fromscswherecidin('00101','00102')andexists(selectsidfromscwheresid=s.sidandcidin('00101','00102')andcid<>s.cid)//选择同时选修一号和二号课程的同学输出学号8实验结论、心得体会和改良建议：认真学习select语句语法，内连接外连接，连接条件的相关语法用法，设计代码即可

电子科技大学实验报告实验五1实验名称：分组统计查询 2实验学时：43实验内容和目的：练习使用聚集函数count(),max(),min(),avg()等在SQL命令中实现统计功能。使用GROUPBY子句实现分组查询，以及聚集函数在分组查询中的应用。体会分组查询的功能特点。查询选修数据库并成绩在６０分以上的人数求每个学生的选课的门数，显示学号和选课门数求每个学生选课的总学分数，显示学号和学分求每个学生的总成绩，显示学号和总成绩查询获得’数据库’课程最高分的学生姓名及成绩求每门课程的平均成绩，并显示课程名及平均成绩求每门课程的学生选修人数，并显示课程名及选修人数求选修了5门以上课程的学生姓名及邮件地址4实验原理：结构化查询语言、分组查询、集函数5实验器材〔设备、元器件〕计算机，MicrosoftSQLsever20146实验步骤：恢复数据库，将数据库备份stud恢复到SQLSERVER中执行SQL命令完成实验内容7实验数据及结果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(8)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(8)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','信软');insertintodep(depid,depname)values('03','通信');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201401','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201402','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201403','孔玲珑','女','03','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201404','雷破','男','04','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201405','李肃','男','05','1994.5.26','8794164@163','湖北省武汉市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('201406','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintocourse(cid,cname,cid_pre,credits)values('00101','离散数学','10001','2');insertintocourse(cid,cname,cid_pre,credits)values('00102','大学物理下','00100','2');insertintocourse(cid,cname,cid_pre,credits)values('00103','数据结构','10000','3');insertintocourse(cid,cname,cid_pre,credits)values('00104','概率论','10002','4');insertintocourse(cid,cname,cid_pre,credits)values('00105','计算机导论','00010','1');insertintocourse(cid,cname,cid_pre,credits)values('00106','数据库','00020','3');insertintosc(sid,cid,tid,score)values('201401','00101','200605','80');insertintosc(sid,cid,tid,score)values('201401','00102','200604','69');insertintosc(sid,cid,tid,score)values('201401','00103','200605','79');insertintosc(sid,cid,tid,score)values('201401','00104','200604','54');insertintosc(sid,cid,tid,score)values('201401','00105','200604','98');insertintosc(sid,cid,tid,score)values('201401','00106','200604','65');insertintosc(sid,cid,tid,score)values('201402','00102','200604','76');insertintosc(sid,cid,tid,score)values('201402','00101','200605','85');insertintosc(sid,cid,tid,score)values('201402','00106','200604','85');insertintosc(sid,cid,tid,score)values('201403','00103','200605','80');insertintosc(sid,cid,tid,score)values('201403','00104','200604','80');insertintosc(sid,cid,tid,score)values('201404','00104','200604','84');insertintosc(sid,cid,tid,score)values('201404','00103','200605','75');insertintosc(sid,cid,tid,score)values('201405','00105','200604','86');insertintosc(sid,cid,tid,score)values('201405','00101','200605','94');insertintosc(sid,cid,tid,score)values('201405','00102','200604','56');selectcount(*)as数据库成绩在60分以上人数fromscwherecid='00106'andscore>60;//筛选数据库成绩在60以上的人数selectsid学号,count(cid)as选课门数fromscgroupbysid;//学生的选课数selectsid学号,sum(credits)as总学分fromsc,coursewheresc.cid=course.cidgroupbysc.sid;//每个学生的总学分selectsid学号,sum(score)as总成绩fromscgroupbysid;selectA.sid学号,A.score数据库最高分fromscAwhereA.score=(selectmax(B.score)fromscBwhereA.cid='00106'andB.cid='00106'groupbyB.cid)//数据库的最高分selectcname课程名,avg(sc.score)平均成绩fromcourse,scwherecourse.cid=sc.cidgroupbycourseame;//求每门课的平均成绩selectcname课程名,count(sid)选修人数fromcourse,scwherecourse.cid=sc.cidgroupbycourseame;//求每门课的选修人数selectnew.sid学号,student.semail邮件地址,new.选课数from(selectsid,count(cid)as选课数fromscgroupbysid)asnew,studentwherenew.sid=student.sidandnew.选课数>5;//求选课门数大于5的学生学号和邮件地址8实验结论、心得体会和改良建议：学习好聚合语句用法，要求5和要求8比拟难，需要嵌套查询

电子科技大学实验报告实验六1实验名称：集合操作、子查询2实验学时：23实验内容和目的：IN、EXISTS、NOTEXISTS运算在WHERE子句中的应用；静态集合和由SELECT命令产生的动态结果集运算。查询其他系中比信息系(depid=’IS’)某一学生年龄小的学生姓名和年龄查询没有选修任何课程的学生姓名、所在院系及邮件地址 查询选修了全部课程的学生姓名查询既选修了1号课程，又选修了2号课程的学生姓名4实验原理：结构化查询语言、集合运算、子查询5实验器材〔设备、元器件〕操作系统：Windows数据库：MSSQLSERVER6实验步骤：恢复数据库，将数据库备份stud恢复到SQLSERVER中执行SQL命令完成实验内容7实验数据及结果分析：Createtabledep(depidvarchar(20)primarykey,depnamevarchar(20)notnull);Createtableteacher(tidvarchar(8)primarykey,tnamevarchar(8)notnull,titlevarchar(10),depidvarchar(20),foreignkey(depid)referencesdep(depid));Createtablestudent(sidvarchar(20)primarykey,snamevarchar(8)notnull,sexchar(2)notnull,depidvarchar(20),birthddate,semailvarchar(20),homeaddrvarchar(40),check(sexin('男','女')),check(semaillike'%@%'));Createtablecourse(cidvarchar(8)primarykey,cnamevarchar(30)notnull,cid_prevarchar(8),creditsnumeric(3,1)notnull);Createtablesc(sidvarchar(20)notnull,cidvarchar(8)notnull,tidvarchar(8)notnull,scoreinteger,check(scorebetween0and100),foreignkey(sid)referencesstudent(sid),foreignkey(cid)referencescourse(cid),foreignkey(tid)referencesteacher(tid));insertintodep(depid,depname)values('01','计算机');insertintodep(depid,depname)values('02','软件工程');insertintodep(depid,depname)values('03','IS');insertintodep(depid,depname)values('04','电工');insertintodep(depid,depname)values('05','自动化');insertintodep(depid,depname)values('06','通信');insertintoteacher(tid,tname,title,depid)values('200605','李迁','教授','01');insertintoteacher(tid,tname,title,depid)values('200604','曹薇','讲师','02');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010101','张澄','男','01','1994.10.15','13456289@163','江苏省南京市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020101','明青云','男','02','1996.1.11','465187@163','河北省邯郸市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406020102','孔玲珑','女','02','1995.6.25','97644163@163','福建省泉州市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406030101','雷破','男','03','1994.2.14','4941647@163','广东省深圳市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406040101','李肃','男','04','1994.5.26','8794164@163','湖北省武汉市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010102','林清','女','01','1995.5.28','8745156@163','湖北省孝感市');insertintostudent(sid,sname,sex,depid,birthd,semail,homeaddr)values('2406010103','石心远','男','01','1996.12.30','