续
(2)确定范围select sname,sdept,sage
from student
where sage not between 20 and 23
(3)确定集合
select sname,ssex
from student
where sdept not in ( 'is','ma','cs' );
(4)字符串匹配
4.1用=运算符取代like谓词;!=或< >取代not like谓词
% (百分号)代表任意长度(长度可以为0)的字符串
_ (下横线)代表任意单个字符
select sname,sno,ssex
from student
where sname not like '刘%';
4.2当用户要查询的字符串本身就含有%或_时,要使用escape '<换码字符>'短语对通符进行转义。
select *
from course
where cname like 'db\_%i_ _' escape ' \ ';
(5)涉及空值的查询
使用谓词is null或is not null,“is null”不能用“= null”代替
select sno,cno
from sc
where grade is not null
(6)多重条件查询
and的优先级高于or,可以用括号改变优先级
select sname
from student
where sdept= 'cs' and sage<20
三、对查询结果排序
升序:asc;降序:desc;缺省值为升序
asc:排序列为空值的元组最后显示;desc:排序列为空值的元组最先显示
select *
from student
order by sdept,sage desc
四、使用集函数
计数count([distinct|all] *)
count([distinct|all]<列名>)
计算总和sum([distinct|all] <列名>)
计算平均值avg([distinct|all] <列名>)
求最大值max([distinct|all] <列名>)
求最小值min([distinct|all] <列名>)
distinct短语:在计算时要取消指定列中的重复值
例1.select count(distinct sno)
from sc
例2.select avg(grade)
from sc
where cno= ' 1 ';
五、对查询结果分组
5.1使用group by子句分组
select cno,count(sno)
from sc
group by cno
5.2使用having短语筛选最终输出结果
select sno, count(*)
from sc
where grade>=90
group by sno
having count(*)>=3;
六.连接查询
6.1广义笛卡尔积
select student.* , sc.*
from student, sc
6.2等值连接(含自然连接)
a.等值连接select student.*,sc.*
from student,sc
where student.sno = sc.sno
b.自然连接
select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno = sc.sno
6.3非等值连接查询比较运算符:>、<、>=、<=、!=
6.4自身连接查询
select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept = s2.sdept and s2.sname = '刘晨'
6.5外连接查询
select student.sno<SPAN style="COLOR: #333333