数据操作及完整性操作实验代码

-*************************************实验二 *******************************************
--*******************************数据操作及完整性操作*********************************

create database js

use  js
drop table 院系
create table 院系( 编号     smallint   Primary key,
                   名称     char(20) not null,
                   负责人   char(10),
                   办公地点 char(20),
                   constraint uniquea unique (名称)
                   )


create table 学生( 学号 char(8) Primary key,
                    院系 smallint,
                    姓名 char(10),
                    性别 char(2) check(性别 in('男','女')),
                    生源 char(6),  
                    状态 char(4) check(状态 in('正常','留级','休学','退学')))




drop table 教师
create table 教师( 教师编号 char(8) Primary key,
                   院系 smallint  foreign key references 院系(编号),
                   姓名 char(10),
                   性别 char(2) check(性别 in('男','女')),
                   职称 char(6) check(职称 in('教授','副教授','讲师','助教')),
                   专业 char(10))
 
drop table 课程           
create table 课程( 课程编号 char(8)Primary key,
                   课程名称 char(20)not null,
                   责任教师 char(8)foreign key references 教师(教师编号),
                   学时 smallint not null,
                   课程性质 char(10) check(课程性质 in('公共基础','专业基础','专业选修','任意选修')))
              
             
drop table 选课
create table 选课( 学号 char(8)foreign key references 学生(学号) on delete cascade,
                   课程编号 char(8) foreign key references 课程(课程编号),
                   成绩 smallint  check(成绩>=0 and 成绩<=100)default null,
                   primary key(学号,课程编号))




--实验要求
alter table 学生 add 平均成绩 smallint default null


alter table 课程 add constraint  chk_xueshi check(学时%8=0) 
alter table 课程  alter column 学时 smallint not null


alter table 院系 drop  constraint uniquea
alter table 院系 alter column 名称 varchar(30)
alter table 院系 add  constraint uniquea unique(名称)


alter table 教师 add 工资 numeric(5,2)
   


--院系(编号，名称，负责人，办公地点)
  insert into 院系 values(1101,'计科系','袁明','36#312')
  insert into 院系 values(1102,'外语系','张明','7#308')
  insert into 院系 values(1103,'电子系','李广','38#314')
  insert into 院系 values(1104,'管理系','杨飞','4#311')
  insert into 院系 values(1105,'会计系','张敏','6#312')
  insert into 院系 values(1106,'生物系','王飞','4#218')
  insert into 院系 values(1107,'机械系','王康','37#212')
  insert into 院系 values(1108,'化工系','王菲','8#218')
  insert into 院系 values(1109,'建工系','王冉','43#306')
  insert into 院系 values(1110,'旅游系','邹飞','41#308')






--(学号，院系，姓名，性别，生源，状态)
 insert into 学生 values(1,1101,'李一','女','亳州','正常')
 insert into 学生 values(3,1101,'李二','男','安庆','正常')
 insert into 学生 values(5,1101,'李三','男','合肥','正常')
 insert into 学生 values(7,1102,'李四','女','安庆','正常')
 insert into 学生 values(9,1102,'李五','女','合肥','正常')
 insert into 学生 values(11,1102,'李六','女','淮南','留级')
 insert into 学生 values(13,1104,'李七','男','合肥','正常')
 insert into 学生 values(15,1104,'李八','男','合肥','正常')
 insert into 学生 values(17,1104,'李九','男','亳州','正常')
 insert into 学生 values(19,1106,'王一','女','合肥','正常')
 insert into 学生 values(21,1106,'王二','男','亳州','正常')
 insert into 学生 values(23,1106,'王三','女','安庆','正常')
 insert into 学生 values(25,1108,'王四','男','六安','正常')
 insert into 学生 values(27,1108,'王五','男','合肥','正常')
 insert into 学生 values(29,1108,'王六','女','安庆','正常')
 insert into 学生 values(2,1103,'王七','女','合肥','正常')
 insert into 学生 values(4,1103,'王八','男','亳州','正常')
 insert into 学生 values(30,1103,'王九','男','淮南','正常')
 insert into 学生 values(6,1105,'张一','女','六安','正常')
 insert into 学生 values(8,1105,'张二','女','合肥','正常')
 insert into 学生 values(10,1105,'张三','女','亳州','正常')
 insert into 学生 values(12,1107,'张四','男','安庆','退学')
 insert into 学生 values(14,1107,'张五','男','亳州','正常')
 insert into 学生 values(16,1107,'张六','男','亳州','正常')
 insert into 学生 values(18,1109,'张七','男','北京','正常')
 insert into 学生 values(20,1109,'张八','男','上海','正常')
 insert into 学生 values(22,1109,'张九','男','广州','正常')
 insert into 学生 values(24,1110,'孙一','男','合肥','正常')
 insert into 学生 values(26,1110,'孙二','男','北京','休学')
 insert into 学生 values(28,1110,'孙三','女','邯郸','正常')






--教师(教师编号，院系，姓名，性别，职称，专业)
  insert into 教师 values(1,1101,'王玉','男','助教','计算机')
  insert into 教师 values(2,1103,'熊二','男','教授','电子信息')
  insert into 教师 values(3,1105,'王怡','女','讲师','会计')
  insert into 教师 values(4,1107,'李飞','男','副教授','机械制造')
  insert into 教师 values(5,1109,'王鹏','女','教授','建工绘图')
  insert into 教师 values(6,1102,'王菊','女','教授','日语')
  insert into 教师 values(7,1104,'李婷','女','讲师','物流管理')
  insert into 教师 values(8,1106,'王梅','女','助教','生物工程')
  insert into 教师 values(9,1108,'王华','男','副教授','粉体')
  insert into 教师 values(10,1110,'王建','男','讲师','旅游管理')








--课程(课程编号，课程名称，责任教师，学时，课程性质)
  insert into 课程 values(11101,'C++',2,38,'专业基础')
  insert into 课程 values(11102,'高数',1,88,'公共基础')
  insert into 课程 values(11103,'会计',3,63,'专业基础')
  insert into 课程 values(11104,'机械制造',4,96,'专业选修')
  insert into 课程 values(11105,'建工绘图',5,81,'专业选修')
  insert into 课程 values(11106,'日语',6,56,'任意选修')
  insert into 课程 values(11107,'物流管理',7,64,'专业基础')
  insert into 课程 values(11108,'旅游管理',10,30,'公共基础')
  insert into 课程 values(11109,'生物工程',8,80,'专业基础')
  insert into 课程 values(11110,'粉体',9,64,'任意选修')








--(学号，课程编号，成绩)
 insert into 选课 values(1,11101,90)
 insert into 选课 values(3,11101,98)
 insert into 选课 values(5,11101,90)
 insert into 选课 values(7,11101,95)
 insert into 选课 values(9,11101,60)
 insert into 选课 values(11,11101,70)
 insert into 选课 values(13,11101,80)
 insert into 选课 values(15,11101,20)
 insert into 选课 values(17,11101,90)
 insert into 选课 values(19,11101,100)
 insert into 选课 values(7,11106,66)
 insert into 选课 values(9,11106,78)
 insert into 选课 values(11,11106,53)
 insert into 选课 values(13,11107,65)
 insert into 选课 values(15,11107,67)
 insert into 选课 values(17,11107,66)
 insert into 选课 values(24,11108,65)
 insert into 选课 values(26,11108,76)
 insert into 选课 values(28,11108,56)
 insert into 选课 values(2,11108,98)
 insert into 选课 values(4,11108,56)
 insert into 选课 values(6,11108,65)
 insert into 选课 values(8,11108,56)
 insert into 选课 values(10,11108,76)
 insert into 选课 values(12,11108,78)
 insert into 选课 values(14,11108,76)
 insert into 选课 values(16,11108,45)
 insert into 选课 values(18,11108,54)
 insert into 选课 values(20,11108,45)
 insert into 选课 values(22,11108,98)
 insert into 选课 values(24,11108,34)
 insert into 选课 values(30,11108,65)
 insert into 选课 values(32,11108,56)
 insert into 选课 values(34,11108,87)
 insert into 选课 values(6,11103,76)
 insert into 选课 values(8,11103,89)
 insert into 选课 values(10,11103,94)
 insert into 选课 values(18,11105,65)
 insert into 选课 values(20,11105,76)
 insert into 选课 values(22,11105,78)
 insert into 选课 values(1,11110,54)
 insert into 选课 values(3,11110,75)
 insert into 选课 values(5,11110,84)
 insert into 选课 values(19,11109,29)
 insert into 选课 values(21,11109,76)
 insert into 选课 values(23,11109,89)
 insert into 选课 values(2,11102,91)
 insert into 选课 values(4,11102,95)
 insert into 选课 values(30,11102,96)




--插入
--违背实体完整性
insert into 学生 values(14,1107,'张五','男','亳州','正常')


--违背参照完整性
insert into 院系 values(1210,'旅游系','王飞','41#308')


--违背用户自定义完整性
insert into 学生 values(6,1105,'张美','女','六安','国外')




--删除
--拒绝删除
delete from 院系 where 编号=1101


--空值删除
select * from 学生 where 学号=98
--这不是空值删除，在2008版本中操作


--级联删除
delete from 学生 where 学号=10


--更新

update 学生 set 生源=null where 学号=10

注意：第一个表拉进来是因为级联删除的需要