数据库语句应用_百度文库

1、-建立数据库mydog- create database mydog; -建立学生表s- create table s (sno char(10 primary key, sname char(10, ssex char(2, sage smallint, sdept char(20, ; -创建课程表c- create table c (cno char(4 primary key, cname char(10, credit char(3, ; -创建选课表sc- create table sc (sno char(10, cno char(4, tname char(10, grade

2、smallint, primary key(sno,cno, foreign key(snoreferences s(sno, foreign key(cnoreferences c(cno, ; -给学生表添加内容- insert into s values('102030100','林黛玉','女','19','中文系' insert into s values('102030101','陈琼','女','29','数学系' ins

3、ert into s values('102030102','所罗门','女','28','哲学系' insert into s values('102030103','索罗斯','男','25','金融系' insert into s values('102030104','巴菲特','男','19','证券学' insert into s values(

4、'102030105','盖茨比','男','34','计算机' insert into s values('102030106','大卫王','男','22','计算机' insert into s values('102030107','林英','女','22','计算机' insert into s values('102030109'

5、,'党天魁','男','22','计算机' -给课程表里插入内容- insert into c values('01','英语','2' insert into c values('02','数学','4' insert into c values('03','语文','16' insert into c values('04','哲学','20&#

6、39; insert into c values('05','金融','10' insert into c values('06','物理','8' -给选课表里添加内容- insert into sc values('102030100','01','张跃',null; insert into sc values('102030100','02','王英',57; insert into sc valu

7、es('102030100','06','李林',75; insert into sc values('102030101','01','胡浩',89; insert into sc values('102030102','02','王英',90; insert into sc values('102030103','03','王致',78; insert into sc values('1020

8、30104','04','胡浩',88; insert into sc values('102030106','01','张跃',56; insert into sc values('102030105','05','王英',100; insert into sc values('102030106','06','王致',null; insert into sc values('102030108'

9、;,'06','李林',60; insert into sc values('102030109','02','党天魁',56; insert into sc values('102030105','06','王英',80; drop table sc; drop table c; drop table s; -注意此处删除表时要先删除表sc后删除有外键约束的表- create unique index stusno on s(sno; -怎样应用索引- -查询课程表

10、的内容- select cno 课程号,cname 课程名,credit 学分 from c; -删除选课表里课程号为00的课程记录- delete from sc where cno='00' -查询课程表的内容- select * from c; -修改学生表里一个具体的值- update s set ssex='男' where sname='所罗门' create view jsgv(sno,cno,grade as select s.sno,cno,grade from sc,s where sdept='计算机' an

11、d sc.sno=s.sno with check option; -注意此处运行时分号不可取- select * from jsgv ; create view csw(sno,sname,sex,age as select sno,sname,ssex,sage from s where sdept='计算机' with check option; drop view csw; select sno from jsgv select * from csw where 2010-age<27; select sno from jsgv group by sno havi

12、ng count(*>=2; -drop view jsgv; select sno,grade from jsgv where cno='02'and grade<60; create view csw(sno,sname,sex,sage as select sno,sname,ssex,sage from s where sdept='计算机' select * from csw where sno='102030106' insert into s values ('102030188','林宛如

13、9;,'女',22,'计算机' update sc set grade=(1+0.05*grade where cno='06' and sno in (select sno from s where sdept='计算机' and sc.sno=s.sno ; delete from s where sno='102030188' select count(distinct cno 总人数 from sc; select s.sno,sname,cno from s,sc where s.sno=sc.sno a

14、nd sc.grade is null;-smallint 定义为 null时用 is null select sno from sc where grade>(select avg(grade from sc,c where cname='数学'and o=o; select o,cname from sc,c where tname='王致' and o=o; select distinct s.sno,sname-o-多写了一个o 要不然加上distinct即去掉重复列- from s,sc where s.sno=sc.sno and sc.sno

15、 in( select sno from sc where tname='张跃' -统计胡浩老师所授每门课程的学生平均成绩- select o,avg(grade 平均成绩-用了聚集函数使用时要注意有些列名用不了 比如 canme ,group by 中用了o from sc,c where o=o and tname='胡浩' group by o; select cno,count(sno 总人数 from sc group by cno having count(sno>=2 order by count(sno asc,cno desc; sele

16、ct sno,sname from s where sdept='计算机'and ssex='女' select sname 姓名,(2010-sage 出生年份,sdept 所在系 from s; select sno from s where sno not in (select sno from sc; select * from s where sname like'所%' select sno from sc where cno=( select cno from c where cname='数学' -注意此处betw

17、een之后sage=- select * from s where sage between ( select sage from s where sname='林英' and 28 union select * from s where sage between 28 and ( select sage from s where sname='林英' grant select ,update(grade on table sc; -查询学生表的内容- select * from s; -查询选课表的内容- select * from sc; -创建一个触发器-

18、 create trigger update_sal after update on s for each row as begin if(new.sno!=old.sno then insert into s values(new.sno,new.sname,new.ssex,new.sdept; end if; end; - -创建mydog表- create table mydog (dno char(10 primary key, dname char(10 unique, dage char(2 , dsex char(2; -给mydog表添加内容- insert into mydog values('10203030','天王','3','男' insert into mydog values('10203031','虎王','3','男' insert into mydog values('10203032','狮王','3','男' insert into mydog values('10203033',