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);