/*带有比较运算符的子查询*/ /*相关子查询*/ select Sno,Cno from SC x where Grade >= (
select avg(Grade) from SC y where y.Sno=x.Sno )
/*带有any(some)或all谓词的子查询*/
select Sname,Sage from Student where Sage<any(
/*嵌套查询*/ /*in谓词*/ select Sno,Sname,Sdept from Student
where Sdept in( select Sdept from Student where Sname='刘晨'
)/*此查询可以利用自身连接代替select S1.Sno,S1.Sname,S1.Sdept from Student S1,Student S2 where S1.Sdept=S2.Sdept and ='刘晨'*/
select Sname from Student where Sname like '欧阳__'/*注意一个汉字两个字符*/
select Sname,Sno from Student where Sname like '__阳%'
select Sname,Sno from Student where Sname not like '刘%'
/*为查询结果指定列标题*/ select Sname name,'year of birth' birth,2014-Sage birthday,lower(Sdept) department from Student
/*消除取值重复的行*/ select distinct Sno from SC
select Sno,Sname from Student where Sno in(
select Sno from SC where Cno in (
select Cno from Course where Cname='信息系统' ) ) /*以上代码的连接方式*/ select Student.Sno,Sname from Student,Course,SC where ame='信息系统' and o=o and Student.Sno=Sc.Sno
select Sage from Student where Sdept='CS' )and Sdept <>'CS' select Sname,Sage from Student where Sage<all( select Sage from Student where Sdept='Cs' )and Sdept<>'CS'
/*聚集函数*/ select count(*)/*貌似distinct和*是不能同时使用的*/
from SC
select count(distinct Sno) from Student
select avg(Grade) from SC where Cno=1 /*计算一号课程的平均成绩*/
select max(Grade) from SC where Cno=2 /*计算号课程的最高成绩*/
/*字符匹配*/ select Cno,Ccredit from Course where Cname like 'DB\_Desigen' escape '\' /*转码字符由自己定义*/
/*涉及空值的查询*/ select Sno,Cno from SC where Grade is null /*注意is不能用=替代*/
题目： 数据库原理实验报告
课本内容练习
一、创建表 create table Student(
Sno char(9) primary key, Sname char(20) unique, Ssex char (2), Sage smallint, Sdept char(20) ) create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint, foreign key (Cpno) references Course(Cno) ); create table SC( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno) ) create table Dept_age( Sdept char(15), Avg_age smallint, ) 二、插入 insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215121','李勇','男',20,'CS') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215122','刘晨','女',19,'CS')
select * from student where sno <> '200215121'
select * from student where sno != '200215121'/*不含有通配符的时候可以使用“=;<>;!=”代替like，not
like*/
select Sname,Sno,Ssex from Student where Sname like '刘%'
三、查询 select Sno,Sname from Student;
/*查询经过计算的值*/ select Sname,2014-Sage from Student
/*指定字符通过小写输出*/ select Sname,'year of birth',2014-Sage,lower(Sdept) from Student
/*有时in谓词可看做多个or的缩写
/*order by子句*/ select Sno,Grade from SC where Cno='3' order by Grade desc /*asc:升序；desc：降序，缺省升序*/
select * from Student orrt
into SC(Sno,Cno,Grade) values('200215121','1',92) insert into SC(Sno,Cno,Grade) values('200215121','2',85) insert into SC(Sno,Cno,Grade) values('200215121','3',88) insert into SC(Sno,Cno,Grade) values('200215122','2',90) insert into SC(Sno,Cno,Grade) values('200215122','3',80)
select sum(Ccredit) from SC,Course where Sno='200215121' and o=o 的总学分*/
/*查询学号为的学生选修课程
/*group by子句*/ select Cno,COUNT(Sno) from SC group by Cno
/*比较大小查询*/ select Sname,Sage from Student where Sage>19
select Sname from Student where Sdept='CS'
/*确定范围*/ select Sname,Sdept,Sage from Student where Sage between 20 and 30
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215123','王敏','女',18,'MA') insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('200215125','张立','男',19,'IS')
select Sno,Cno from SC where Grade is not null
/*多重条件查询*/ select Sname from Student
where Sdept='CS' and Sage<20 /*and优先级比or高*/
select Sname,Ssex from Student where Sdept='CS' or Sdept='IS' or Sdept='MA' */
/*having 短语*/ select Sno from SC group by Sno having COUNT(*)>2
/*连接查询*/ select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno /*自然连接，两个表中不重复的属性名可以不加前缀*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno /*自身连接，要为表取别名，此时必须都要有前缀*/ select o,second.Cpno from Course first,Course second where first.Cpno=o /*外连接*/ select Student.Sno,Sname,Ssex,Sdept,Cno,Grade from Student right outer join SC on(Student.Sno=Sc.Sno) /*复合条件连接*/ select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and o='2' and SC.Grade>80 /*复合条件连接*/ select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and o=o