实验二_第二实验中学

实验二由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“第二实验中学”。

DROPTABLEsc DROPTABLEstudent DROPTABLEcourse CREATETABLEstudent(snochar(8)notnullprimarykey, snamevarchar(8)notnull, sexchar(2)notnullcheck(sex='男'orsex='女')default'男', birthsmalldatetimenotnull, clanochar(3)notnull, entrance_datesmalldatetimenotnull, home_addrvarchar(40),);CREATETABLEcourse(cnochar(3)notnullprimarykey, cnamevarchar(20)notnull, total_periorsmallintcheck(total_perior>0), credittinyintcheck(credit>0 andcredit=0 andgrade

altertablestudentaddconstraintstudent_stature_checkcheck(stature

altertablestudentaddsdeptchar(20);altertablestudentaddpostcodechar(6)check(postcodelike('[1-9][0-9][0-9][0-9][0-9][0-9]'));altertablestudentdropconstraintstudent_stature_check;--删除约束 altertablestudentdropcolumnstature;

altertablestudentaddconstraintstudent_entrance_date_checkcheck(entrance_date>birth);--定义了约束名

altertablescaddconstraintsc_grade_checkdefault 0 forgrade;altertablescdropconstraintsc_grade_check;

insert intostudent(sno,sname,sex,birth,clano,entrance_date,home_addr,sdept,postcode)values('20110001','张虹','男','1992/09/11','051','2011/09/01','南京','计算机系','200413');insert intostudent(sno,sname,sex,birth,clano,entrance_date,home_addr,sdept,postcode)values('20110002','林红','女','1991/11/12','051','2011/09/01','北京','计算机系','100010');insert intostudent(sno,sname,sex,birth,clano,entrance_date,home_addr,sdept,postcode)values('20110103','赵青','男','1993/05/11','061','2011/09/01','上海','软件工程','200013');

insert

intocourse(cno,cname,total_perior,credit)values('001','高数','96','6');insert intocourse(cno,cname,total_perior,credit)values('002','C语言程序设计','80','5');insert intocourse(cno,cname,total_perior,credit)values('003','Java语言程序设计','48','3');insert intocourse(cno,cname,total_perior,credit)values('004','Visual_Basic','48','4');

insert intosc(sno,cno,grade)values('20110001','001','89');insert intosc(sno,cno,grade)values('20110001','002','78');insert intosc(sno,cno,grade)values('20110001','003','89');insert intosc(sno,cno,grade)values('20110002','002','60');insert intosc(sno,cno,grade)values('20110103','001','80');

updatecourse setcredit=4,total_perior=64 wherecno='002';

updatesc setgrade=grade*0.8 wherecno='002';delete fromsc where'C语言程序设计'=(selectcname fromcourse

wherecourse.cno=sc.cno);delete fromsc;select* fromsc