《school数据库创建过程.doc》由会员分享，可在线阅读，更多相关《school数据库创建过程.doc（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、create database schoolon( name=school_data, filename=E:school_data.mdf, size=10mb,maxsize=500mb,filegrowth=10%),( name=school_data2, filename=E:school_data2.ldf, size=2mb,maxsize=30mb,filegrowth=5mb)use schoolcreate table t(tno varchar(8) not null,tname varchar(10) not null,tsex varchar(4),tage smal
2、lint ,tprof varchar(10),tsal smallint,tcomm smallint,tdept varchar(20)create table s(sno varchar(8) not null,sname varchar(10) not null,ssex varchar(4),sage smallint,sdept varchar(20)create table c( cno varchar(8) not null, cname varchar(20) not null, ct smallint)create table sc(sno varchar(8) not n
3、ull,cno varchar(8) not null,score smallint)朱伟() 13:39:17)create table tc( tno varchar(8) not null, cno varchar(8) not null,)insert into t(tno,tname,tsex,tage,tprof,tsal,tcomm,tdept) values (t1,李力,男,47,教授,1800,3000,计算机)insert into t(tno,tname,tsex,tage,tprof,tsal,tcomm,tdept) values (t2,王平,女,28,讲师,85
4、0,1200,信息)insert into t(tno,tname,tsex,tage,tprof,tsal,tcomm,tdept) values (t3,刘伟,男,30,讲师,1000,1200,计算机)insert into t(tno,tname,tsex,tage,tprof,tsal,tcomm,tdept) values (t4,张雪,女,51,教授,1900,3000,自动化)insert into t(tno,tname,tsex,tage,tprof,tsal,tcomm,tdept) values (t5,张兰,女,39,副教授,1500,3000,信息)select *
5、 from tinsert into s(sno,sname,ssex,sage,sdept) values (s1,赵义,女,17,计算机)insert into s(sno,sname,ssex,sage,sdept) values (s2,钱尔,男,18,信息)insert into s(sno,sname,ssex,sage,sdept) values (s3,孙珊,女,20,信息)insert into s(sno,sname,ssex,sage,sdept) values (s4,李思,男,21,自动化)insert into s(sno,sname,ssex,sage,sdept
6、) values (s5,周武,男,19,计算机)insert into s(sno,sname,ssex,sage,sdept) values (s6,吴丽,女,20,自动化)朱伟() 13:39:28select * from sinsert into sc(sno,cno,score) values (s1,c2,85)insert into sc(sno,cno,score) values (s1,c5,100)insert into sc(sno,cno,score) values (s2,c5,60)insert into sc(sno,cno,score) values (s2,
7、c6,80)insert into sc(sno,cno) values (s2,c7)insert into sc(sno,cno,score) values (s3,c2,70)insert into sc(sno,cno,score) values (s3,c4,85)insert into sc(sno,cno,score) values (s4,c2,85)insert into sc(sno,cno,score) values (s4,c3,83)insert into sc(sno,cno,score) values (s5,c2,89)select * from scinser
8、t into c(cno,cname,ct) values (c1,程序设计,60)insert into c(cno,cname,ct) values (c2,微机原理,80)insert into c(cno,cname,ct) values (c3,数字逻辑,60)insert into c(cno,cname,ct) values (c4,数据结构,80)insert into c(cno,cname,ct) values (c5,数据库,60)insert into c(cno,cname,ct) values (c6,编译原理,60)insert into c(cno,cname,
9、ct) values (c7,操作系统,60)select * from cinsert into tc(tno,cno) values (t1,c1)insert into tc(tno,cno) values (t2,c5)insert into tc(tno,cno) values (t4,c2)insert into tc(tno,cno) values (t5,c5)insert into tc(tno,cno) values (t3,c1)insert into tc(tno,cno) values (t3,c5)insert into tc(tno,cno) values (t2
10、,c7)insert into tc(tno,cno) values (t4,c3)insert into tc(tno,cno) values (t5,c7)select * from tcalter table tadd primary key(tno)alter table sadd primary key(sno)alter table cadd primary key(cno)alter table scadd primary key(sno,cno)alter table scadd foreign key(cno)references c(cno)alter table scad
11、d foreign key(sno)references s(sno)alter table tcadd primary key(tno,cno)alter table scadd check(score 0 and score =2select sname, sdept from swhere sdept=计算机 or sdept=信息order by sdept select sno,cname,ct from s,cwhere cname=(select ct from cwhere cno=c2 )order by cnameselect SUM（all sno） as 总数 from
12、 swhere sdept=计算机select s.sno,s.sname,ame,sc.scorefrom s,c,scwhere s.sno=sc.sno and o=o-查询每门课程的课程号、任课教师姓名及其选课人数； select o,t.tname count (sc.sno) as 选课人数 from c,t,sc,tc where o=o and tc.tno=t.tno and o=o-查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资； select tsal,tname from t where tname=刘伟 union select tname,tsal from
13、t where tname刘伟 and tsal1000-查询同时选修了“程序设计”和“微机原理”的学生姓名、课程名； select distinct sname,ame ,ame from s,c x,c y,sc a,sc b where o=o and o=o and ame=程序设计 and ame=微机原理 and a.sno=b.sno and a.sno=s.sno-查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。 select s.sno as 学号,s.sname as 姓名,ame as 选课名称,sc.score as 成绩from slef
14、t join sc on(s.sno=sc.sno)left join c on(o=o)-查询与“刘伟”教师职称相同的教师号、姓名和职称； select tno,tname,tprof from t where tprof=(select tprof from t where tname=刘伟)-使用ANY谓词查询讲授课程号为C5 的教师姓名select tname from twhere tno=any(select tno from tc where cno=c5)-使用IN谓词查询讲授课程号为C5的教师姓名select tname from twhere tno in (select tno from tc where cno=c5)-使用ALL谓词查询其他系中比计算机系所有教师工资都高的教师的姓名、工资和所在系别。select tname,tsal,tdept from twhere tsalall(select tsal from t where tdept=计算机)and ( tdept计算机)-查询计算机系的学生姓名及年龄不大于19岁的学生姓名。select *from swhere sdept=计算机unionselect *from swhere sage=19