MySQL的那些事儿（四）高级查询

distinct 排重

select distinct depart from teacher;

between...and... 查询区间（还可以使用运算符比较）

select * from score where degree between 60 and 80;

in/not in 同列或者关系

select * from score where degree in(85,86,88);

or 异列或者关系

select * from student where sclass='95031' or sgender='女';

order by 开启排序

asc 升序（默认）

desc 降序

select * from student order by sclass desc;

//以cno升序，相同的cno再以degree降序 
select * from score order by cno asc,degree desc;

count 统计

select count(*) from student where sclass='95031';

max,min 找最大最小值

select max(degree) as '最高分' from score;

limit a,b 分页查询

从a位置开始，查找b条记录

select * from student limit 0,3;

avg(degree) 求平均成绩

select avg(degree) from score where cno='3-105';

group by 分组

having 分组条件

//求每门课的平均成绩（把课程号相同的分为一组求平均成绩） 
select avg(degree) from score group by cno;

like/not like 模糊匹配

like 'X%' 匹配以X开头的

like '%X%' 匹配包含X的

like '%X' 匹配以X结尾的

select * from student where sname like '王%';

as 为字段取别名

select sname as stu_name,cname as cou_name,degree from student,course,score 
where student.sno=score.sno and course.cno=score.cno;

any 其中至少一个

//查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的cno,sno和degree, 
//并且按照degree从高到地次序排序。 
select * from score where cno='3-105' 
and degree>any(select degree from score where cno='3-245') 
order by degree desc;

all 其中全部

select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');

union 将两次查询结果合并(求并集、联合)

select * from teacher where depart='电子工程系' 
and tprof not in(select tprof from teacher where depart='计算机系') 
union 
select * from teacher where depart='计算机系' and 
tprof not in(select tprof from teacher where depart='电子工程系');

year(datetime) 从datetime类型的字段中取出年份

month(datetime) 从datetime类型的字段中取出月份

day(datetime) 从datetime类型的字段中取出几号

now() 获取当前datetime

 

子查询：一步一步来。

 

//练习：查询score表中至少有两名学生选修并且以3开头的课程的课程号和平均分数。 
select cno,avg(degree) from score group by cno having count(cno)>=2 and cno like '3%'; 
//练习：两表查询 
select sno,cname,degree from course,score where course.cno=score.cno; 
//练习：三表查询 
select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno; 
//练习：求“95031”班每门课的课程号、课程名及平均成绩 
select score.cno as s_cno,cname,avg(degree) from score,course where score.cno=course.cno and sno in(select sno from student where sclass='95031') group by s_cno; 
+-------+-----------------+-------------+ 
| s_cno | cname       |   avg(degree)   | 
+-------+-----------------+-------------+ 
| 3-105 | 计算机导论   |     82.0000     | 
| 3-245 | 操作系统     |     71.5000     | 
| 6-166 | 数字电路     |     80.0000     | 
+-------+-----------------+-------------+ 
//练习：查询成绩比该课程平均成绩低的同学的成绩表(复制表数据查询) 
SELECT * FROM score AS sc1 WHERE sc1.degree < (SELECT  AVG(degree) FROM score AS sc2 WHERE sc1.cno = sc2.cno);