CREATE DATABASE jxgl GO USE jxgl Go Create Table Student (Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), Sname VARCHAR(20), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Ssex CHAR(2) DEFAULT'男' CHECK(Ssex='男' OR Ssex='女'), Sdept CHAR(2));
Create Table Course (Cno CHAR(2) NOT NULL PRIMARY KEY(Cno), Cname VARCHAR(20), Cpno CHAR(2), Ccredit SMALLINT);
create table sc (sno char(5) not null constraint s_f foreign key references student(sno), cno char(2) not null, grade smallint check((grade is null)or(grade between 0 and 100)), primary key(sno,cno), foreign key (cno) references course(cno));
insert into student values('98001','钱横',18,'男','CS');
insert into student values('98002','王林',19,'女','CS');
insert into student values('98003','李民',20,'男','IS');
insert into student values('98004','赵三',16,'女','MA');
insert into Course values('1','数据库系统','5',4);
insert into Course values('2','数学分析',null,2);
insert into Course values('3','信息系统导论','1',3);
insert into Course values('4','操作系统_原理','6',3);
insert into Course values('5','数据结构','7',4);
insert into Course values('6','数据处理基础',null,4);
insert into Course values('7','C语言','6',3);
insert into sc values('98001','1',87);
insert into sc values('98001','2',67);
insert into sc values('98001','3',90);
insert into sc values('98002','2',95);
insert into sc values('98002','3',88);
insert into Student values('98011','张静',27,'女','CS');
insert into Student values('99201','石科',21,'男','CS') insert into Student values('99202','宋笑',19,'女','CS') insert into Student values('99203','王欢',20,'女','IS') insert into Student values('99204','彭来',18,'男','MA') insert into Student values('99205','李晓',22,'女','CS') insert into Student(Sno,Sname,Sage) values('98012','李四',16);
insert into Student values('99010','赵青江',18,'男','CS') insert into Student values('99011','张丽萍',19,'女','CH') insert into Student values('99012','陈景欢',20,'男','IS') insert into Student values('99013','陈婷婷',16,'女','PH') insert into Student values('99014','李军',16,'女','EH') insert into SC select sno,cno,null from Student,Course where Sdept = 'CS' and cno='5';
insert into Student select cast(cast(sno as integer)+1 as char(5)),sname+'2',sage,ssex,sdept from Student where Sname='赵三';
go
select * from Student;
select *from Student update Student set sname='李明',Sage=23 where sno='98003' select *from Student update student set student.Sage = Student.Sage+1 from (select top(3) * from student order by sno) as stu3 where stu3.sno=Student.sno;
select *from Student update top(3) percent Student set student.Sage=Student.Sage+1;
select *from Student select *from sc update sc set grade=(select avg(grade) from sc where cno='3') where sno = '98001' and cno='3' select *from sc select *from sc update sc set grade=0 where cno='2' and sno in(select sno from Student where sname = '王林') select *from sc select * into TSC from sc select * from TSC select *from sc delete from sc where 'CS'=(select sdept from Student where Student.Sno=sc.sno) select *from sc insert into sc select * from TSC delete from sc truncate table sc insert into sc values('99010','1',87) insert into sc values('99010','3',80) insert into sc values('99010','4',87) insert into sc values('99010','6',85) insert into sc values('99011','1',52) insert into sc values('99011','2',47) insert into sc values('99011','3',53) insert into sc values('99011','5',45) insert into sc values('99012','1',84) insert into sc values('99012','4',67) insert into sc values('99012','5',81) insert into sc(sno,cno)values('99010','2') insert into sc(sno,cno)values('99012','3') select * from sc --2 select * into TS from Student delete from TS select * from TS --3 insert into SC select sno,cno,60 from Student where Sdept = 'IS' and cno='7';
// 如果from student course 会进行广义笛卡尔积，会导致重复出现；或者使用distinct 来约束
select * from SC select * from Student insert into TS select * from Student where Ssex='女' and Sage<=16;
INSERT INTO TS SELECT * FROM STUDENT WHERE SNO IN (SELECT SNO FROM SC GROUP BY SNO HAVING MAX(GRADE)<60 ) --6 update Student set sname='刘华',Sage=sage+1 where sno='99011' select * from SC --7 update sc set grade =null where grade<60 and cno in(select cno from Course where Cname='数据库系统') select * from SC --8 update student set student.Sage = Student.Sage+1 from (select top(4) * from student order by sno) as stu3 where stu3.sno=Student.sno;
update sc set grade=null where cno='3' and sno in(select sno from Student where sname = '王林') select *from sc --10 update sc set grade=grade*1.05 where grade<(select avg(grade) from sc) and sno in(select sno from Student where ssex = '女') select *from sc --11 update sc set grade = grade*0.98 where grade<=80 and cno='2' update sc set grade = grade * 0.99 where grade >80 and cno='2' --12 select * into t11 from Student select * into t12 from Course select * into t13 from SC select * from sc --13 delete from sc where grade IS null select * from sc --14 delete from sc where sno in (select sno from Student where Sname='钱横')
delete from sc where sno ='98005' delete from Student where sno='98005' --16 delete from sc where sno in(select sno from Student where Sname like '张%') select * from Student delete from Student where Sname like '张%' --17 delete from student delete from course --18 insert into student select * from t1 insert into sc select * from t2 insert into course select * from t3
