实验二 SQL定义语言

Create table stud
(sno char(8) primary key,
sname char(4) not null unique, --学生姓名
ssex char(2) default '男' check(ssex='男' or ssex='女'),
sage tinyint,
sdept char(20))

create table cour
(cno char(2) PRimary key,
cname varchar(50),
cpno char(2),
ccredit tinyint)

create table scc
(sno char(8),
cno char(2),
grade tinyint,
constraint pk_gra primary key(sno,cno),
constraint fk_stu foreign key(sno) references stud(sno),
constraint fk_cour foreign key(cno) references cour(cno),
constraint ck_grad check(grade>=0 and grade<=100) )

go
insert into stud(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
insert into stud(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS')
insert into stud(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
insert into stud(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS')
insert into stud(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ')
insert into cour(cno, cname,ccredit,cpno) values('1', '数据库', 4, '5')
insert into cour(cno, cname,ccredit,cpno) values('2', '数学', 6, null)
insert into cour(cno, cname,ccredit,cpno) values('3', '信息系统', 3, '1')
insert into cour(cno, cname,ccredit,cpno) values('4', '操作系统', 4, '6')
insert into cour(cno, cname,ccredit,cpno) values('5', '数据结构', 4, '7')
insert into cour(cno, cname,ccredit,cpno) values('6', '数据处理', 3, null)
insert into cour(cno, cname,ccredit,cpno) values('7', 'PASCAL语言', 4, '6')
insert into scc(sno,cno,grade) values('95001', '1' ,92)
insert into scc(sno,cno,grade) values('95001', '2' ,85)
insert into scc(sno,cno,grade) values('95001', '3' ,88)
insert into scc(sno,cno,grade) values('95002', '2' ,90)
insert into scc(sno,cno,grade) values('95002', '3' ,80)
insert into scc(sno,cno,grade) values('95003', '2' ,85)
insert into scc(sno,cno,grade) values('95004', '1' ,58)
insert into scc(sno,cno,grade) values('95004', '2' ,85)

alter table stud add scome date //1）STUDENT表中增加一个字段入学时间scome，
alter table stud drop column sdept//2）删除STUDENT表中sdept字段；

exec sp_helpconstraint'sc' --找到sc表的外键名
alter table sc drop constraint fk_cou_cno //3）删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束；

alter table sc add constraint fk_cou_cno
foreign key(cno) references course(cno)//增加外键

Create table studd
(sno char(8) primary key,
sname char(4) not null unique,
ssex char(2) default '男' check(ssex='男' or ssex='女'),
sage tinyint,
sdept char(20))
drop table studd//重新定义一个简单表，然后用SQL语言DROP语句删除该表结构;

create unique index sstud on stud sname desc//用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

drop index sstud on stud//用SQL语言DROP语句删除索引;