设计数据库、数据表并编程实现一、实验目的通过“数据库系统概论”书本知识学习和“数据库原理与应用”课程教学所授知识对“教学管理系统”的实验内容进行分析，通过建立数据库、数据表、数据视图以及触发器，利用SQL结构化查询语言提供的数据操纵功能，设计实现对数据的定义、修改、删除、查询、更新以及控制等操作，并按照实验指导书的各项要求完成实验内容。

二、实验内容（1）根据实验建议所示，建立“学生管理”数据库。

（2）建立数据表的方法，建立五个相关的数据表。

完成数据库的建立并定义各表的完整性约束条件。

分别使用企业管理器和SQL 语言来实现。

（3）熟练掌握并应用SQL中的数据表定义、删除和修改等操作命令；（4）熟练掌握为各表常用字段建立索引，以提高数据检索效率。

create database 学生管理;create table student ( sno Char(9),sname Char(10) not null,sbirthday Datetime,ssex Char(2) check(ssex in ('男','女')),sclass Char(20),sremark Char(100),address Char(40),zipcode Char(6),phone Char(15),email Char(40), primary key(sno) );create table course ( cno char(6) primary key,cname char(20),cpno char(6),ctime Numeric(2),credit Numeric(2),foreign key(cpno)references course(cno));create table score(sno char(9), cno char(6),primary key(sno,cno),foreign key(sno)references student(sno),foreign key(cno)references course(cno),score Numeric(3));create table teacher( Tno char(20) primary key,Tname char(20) ,Tsex Char(2) check(Tsex in ('男','女')),Department char(15),tbirthday Datetime);create table Teaching(Tno char(20),cno char(6),tdate datetime,classroom char(10),sclass char(20),primary key(Tno,cno),foreign key(Tno)references teacher(Tno),foreign key(cno)references course(cno));设计数据插入、修改、删除、查询和视图等操作并编程实现一、实验目的（1）熟练掌握数据更新语句，灵活地操作插入数据、修改数据和删除数据；（2）熟练掌握关系数据库中的完整性概念的应用；（3）掌握单表查询的基本方法；（4）掌握多表连接查询的基本方法；（5）熟练掌握以下练习，并进行以下各类查询：①选择表中的若干列、查询全部列、查询经过计算的值；②选择表中的若干元组，即消除取值重复的行与查询满足指定条件的元组（包括：比较大小、确定范围、确定集合、字符匹配、涉及空值和多种条件查询）；③对查询结果排序；④使用集函数；⑤对查询结果分组（6）熟练掌握以下练习，并进行下列各类连接查询：①等值与非等值连接查询；②自身连接；③外连接；④复合条件连接；（7）掌握嵌套查询的基本方法；（8）掌握集合查询的基本方法；（9）熟练掌握查询视图操作（10）熟练掌握更新视图操作（11）熟练关系的完整性概念，领会视图的用途（12）练习以下各类查询：①带有IN谓词的子查询；②带有比较运算符的子查询；③带有ANY或ALL谓词的子查询；④带有EXISTS谓词的子查询；⑤视图查询与更新操作；二、实验内容根据以下给定的部分数据表信息，分别对student, course, score, teacher, teching 表进行数据插入以及根据题目要求用SQL语句实现。

-- student表插入数据;insertinto student(sno,sname,sbirthday,sclass)values('011110101','章海潮','1982.02.07','信管系0101');insertinto student(sno,sname,sbirthday,sclass)values('011111204','董承悟','1982.06.06','电商系0102');-- course表插入数据insertinto course(cno,cname,credit,ctime)values('C001','数据库原理','4','64');insertinto course(cno,cname,credit,ctime)values('C002','高等数学','4','72');--score表插入数据insertinto score(sno,cno,score)values('011110101','C001','90');insertinto score(sno,cno,score)-- teacher表插入数据insertinto teacher(tno,tname,tsex,department,tbirthday)values('T001','江承基','男','信息管理系','1964.03.21');insertinto teacher(tno,tname,tsex,department,tbirthday)values('T002','梁其政','男','信息管理系','1973.12.12');-- teaching表插入数据insertinto teaching(tno,cno,tdate,classroom,sclass)values('T001','C005','2012-01-09','西二405','信管系0101');insertinto teaching(tno,cno,tdate,classroom,sclass)values('T001','C002','2013-04-10','主401','会计系0102');--（2）select * from student;--（3）select sno,sname from student;--（4）select sno,sname,sclass from student group by(sclass);--（5）select sname,year(sbirthday) from student ;--（6）select sname,year(Curdate())-year(sbirthday) as 年龄from student ;--（7）select sname from student where sclass='信管系0101';--（8）select sname,year(Curdate())-year(sbirthday) as 年龄from student where year(Curdate())-year(sbirthday) <27;--（9）select sno from score where score<='60';--（10）select student.sno,sname,o,cname from student,course,score where score=null and score.sno=student.sno and o=o;--（11）select sname,sclass,year(Curdate())-year(sbirthday) as 年龄from student where year(Curdate())-year(sbirthday)>=15 and year(Curdate())-year(sbirthday)<=28;--（12）select sname,sclass,year(Curdate())-year(sbirthday) as 年龄from student where year(Curdate())-year(sbirthday)<=15 and year(Curdate())-year(sbirthday)>=28;--（13）select sname,sbirthday,sclass from student where sclass='信管系0101' or sclass='信管系0102';--（14）select sname,sbirthday,sclass from student where sclass!='信管系0101' and sclass!='信管系0102';--（15）select * from student where sno='011113104';--（16）select * from student where sno like '0111%';--（17）select sno,sname,ssex,year(Curdate())-year(sbirthday) from student where sname like '张%';--（18）select sno,sname,ssex,year(Curdate())-year(sbirthday) from student where sname like '_海%';--（19）select sname from student where sname not like '刘%';--（20）select sname ,sclass from student where sname like '%齐%';--(21）select student.sno from student,course,score wherestudent.sno=score.sno and o=o;--（22）select cno,cname from course where cno like 'C%05';--（23）select cno,cname from course where cname like '%\_%' escape '\ ' ;--（24）select sno,cno from scoure where score!=null;--（25）select sno,min(score),max(score),avg(score) from score where score>='60';--（26）create table average(sno char(9),cno char(6),ave int,primary key(sno,cno),foreign key(sno) references student(sno),foreign key(cno) references course(cno));insertinto average(sno,cno,ave)select score.sno,o,avg(score)from student,course,scorewhere student.sno=score.sno and o=o ;--（27）update student set sbirthday='1982.08.20' where sname='马丽娟';--（28）update student set zipcode='230009';--（29）update average set ave='0';--（30）delete from average where cno='C007';--（31）delete from average ;--（32）create table tstudent ( sno Char(9),sname C har(10) not null,sbirthday Datetime,ssex Char(2) check(ssex in ('男','女')),sclass Char(20),sremark Char(100),address Char(40),zipcode Char(6),phone Char(15),email C har(40), primary key(sno) );insert into tsudent select * from student ;delete from tstudent where sno='001011%';--（33）select sno,sname from tstudent where sclass='电商系0101' andyear(Curdate())- year(sbirthday)<27;--（34）select sno from student,score where student.sno=score.sno andmin(score)>'60' and max(score)<'90';--（35）select sno,score from score where cno='C001' order by score desc;--（36）select * from student order by sclass,year(Curdate())-year(sbirthday) desc;--（37）select count(sno) from student;--（38）select count(distinct sno) from score;--（39）select max(score) from score where cno='C001';--（40）select sno,score from score where score= (select max(score) fromscore );--（41）select cno ,count(sno) as RS from score group by (cno);--（42）select sno,snamefrom student where sno in(select student.sno from student,scorewhere student.sno=score.sno and student.sclass='电商系0102'group by student.snohaving count(*)>=2) ;--（43）select o,second.cpno from course first,course second where first.cpno=o;--（44）select * from student,score where student.sno=score.sno and o='C001' and score>'90';--（45）select cname,score,student.sname from student,course,score where o=o and student.sno=score.sno;--（46）select sname from student where (not exists(select * from coursewhere (not exists (select * from scorewhere student.sno=score.sno and o=o))));--（47）select sno,sname from student where not exists( select * from course where cno in( select cno from course where course.credit='4' and not exists(select * from score b where o=score.sno and b.sno=student.sno)));--（48）select * from score where cno='C005' and score>(select score from score where cno='C003') order by sno desc;--（49）select student.sno,sname from student,score wherestudent.sno=score.sno and cno='C001' or cno='C007';--（50）select student.sno,sname from student,score where student.sno=score.sno and cno='C001' and cno='C007';--（51）select sname from student where sclass='会计系0102' andyear(Curdate())-year(sbirthday)<='27';--（52）select student.sno,sname,ssex, year(Curdate())-year(sbirthday)as 年龄from student,course,score where student.sno=score.sno and o=o and cname='数据库原理';--（53）select sname from student where ( year(Curdate())-year(sbirthday))<all (select year(Curdate())-year(sbirthday) from student where sclass='信管系0101');--（54）select sno,sname,ssex, year(Curdate())-year(sbirthday) as 年龄,sclassfrom student where sclass =(select sclass from studentwhere sname='齐振国');--（55）create view info_student as select * from student where sclass='信管系0101';--（56）create view info_student2 as select * from student where sclass='信管系0101' with check option;--（57）create view info_student1 as select * from student,score where sclass='信管系0101' and cno='C001' and student.sno=score.sno;--（58）create view info_C001_student2 as select * from student,score where sclass='信管系0101'and cno='C001' and score>'90' and student.sno=score.sno;--（59）create view vbirthday_student as select year(Curdate())-year(sbirthday) as age from student;--（60）create view vfemale_student as select * from student where ssex='女';--（61）create view vaverage_student as select sno,avg(score) from score group by;--（62）drop view info_student1;--（63）select sname from info_student2 where year(Curdate())-year(sbirthday) <'27';--（64）select sno,sname from info_C001_student1;--（65）update info_student2 set sname='潘长江' where sno='011111103';--（66）insert into info_student1(sno,sname,ssex,sbirthday) values('011111136','张艺谋','男','1987.11.9');--（67）delete from info_student1 where sno='011111135'and sname='黄健中';数据库存储过程、触发器的建立及编程操作的实现一、实验目的（1）理解掌握数据库中存储过程、触发器的原理、使用场合及方法；（2）掌握触发器在关系数据库的完整性实现中的作用；（3）掌握数据库中存储过程的建立方法和调用过程；（4）掌握数据库中触发器的建立方法；（5）理解事务的作用及定义；（6）熟练游标的作用、定义及作用：二、实验内容（1）创建两个临时表Table1, Table2。