算法比较运算符
select * from S
where AGE < 27 ;
select * from S
where AGE <= 27 ;
select * from S
where AGE > 27 ;
select * from S
where AGE >= 27 ;
select * from S
where AGE = 27 ;
select * from S
where AGE <> 27 ;
逻辑运算符
select * from S
where AGE < 30
and
SEX = '男' ;
select * from S
where (AGE < 30
and
SEX = '男' )
or
(AGE > 30
and
SEX = '女' );
not 表示非(需要与and、or连用,形成 and not/ or not)
求男生年龄小于40岁,但是年龄不在21-30之间的,或者学号等于805的同学的信息。
select * from S
where SNO = '801'
or
(AGE not between 21 and 30
and not
AGE >= 40 );
其他操作符
select * from S
where AGE between 30 and 50 ;
select * from S
where AGE not between 30 and 50 ;
select * from S
where AGE in (25 , 27 , 52 );
select * from S
where AGE not in (25 , 27 , 52 );
select * from S
where DEPT is null ;
is not 可以用来判断 不是null(注意这里是is not 而不是ppt中的not is)
select * from S
where DEPT is not null ;
like 模糊查询,最常用和最好的就是%X、X%、%X%这样的
寻找名字以’刘’开头和名字以’章’结尾以及名字中间有’杰’的同学
select * from S
where SNAME like '刘%'
or
SNAME like '%章'
or
SNAME like '%杰%' ;
not like 模糊查询,最常用和最好的就是%X、X%、%X%这样的(还有其他方法具体看这里 )
寻找名字不是以’刘’开头和不是以’章’结尾以及中间没有有’杰’的同学
select * from S
where SNAME not like '刘%'
and
SNAME not like '%章'
and
SNAME not like '%杰%' ;
谓词
select SNO from S
where exists (select * from SC
where S.SNO = SC.SNO);
select SNO from S
where not exists (select * from SC
where S.SNO = SC.SNO);
select SNO from S
where 30 < all (select GRADE from SC
where S.SNO = SC.SNO)
and exists (select * from SC
where S.SNO = SC.SNO);
select SNO from S
where 30 <= some (select GRADE from SC
where S.SNO = SC.SNO)
and exists (select * from SC
where S.SNO = SC.SNO);
基础分组函数
select count (*) from S;
count(列名) 对一列的值进行求个数(不计null)
select count (DEPT) from S;
select sum (AGE) from S;
select avg (AGE) from S;
select max (AGE) from S;
select min (AGE) from S;
分组运算符
select SEX, avg (AGE) from S
where SEX is not null
group by SEX;
having 基础分组函数组成的表达式 决定分组后,显示什么
select SNO, avg (GRADE) from SC
group by SNO
having avg (GRADE) > 60 ;
order by 列名 排序方法(desc/asc)
select SNO, avg (GRADE) from SC
group by SNO
order by avg (GRADE) desc ;
整合利用
select SEX as 性别, avg (AGE) as 平均数 from S
where SEX is not null
group by SEX;
针对 ORA-00979: 不是 GROUP BY 表达式 错误的解决
学生成绩排榜(平均成绩从大到小顺序,要求给出学号、姓名和总成绩),就需要新建一个临时表 其中t是这个表的名字(中间有个空格)
select S.SNO, S.SNAME, T.SU from S, (select sno, avg (GRADE) as AV, sum (GRADE) as SU from SC
group by sno) T
where S.SNO= T.SNO
order by T.AV desc ;
附录
本文测试数据 SQL语句习题