實驗二 SQL定義語言

標籤：des   ar   sp   for   資料   on   ad   ef   時間   

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語句刪除索引;

實驗二 SQL定義語言