阅读量： 89
查询
一、单表查询
1.子查询
SELECT MAX(age) FROM student; # 查询年龄最大的数字，显示为32
SELECT * FROM student WHERE age=32; # 查询年龄为32的所有数据
SELECT * FROM student WHERE age=(SELECT MAX(age) FROM student); # 利用子查询
# ORDER BY用于排序,DESC表示倒序
# 查询c10课程，考试层级排名前10的学生
SELECT * FROM grade WHERE courseid='c10' ORDER BY score DESC LIMIT 10;
# 查询从第12个开始往后十个
SELECT * FROM grade WHERE courseid='c10' ORDER BY score DESC LIMIT 11,10;
# 如果有并列成绩，则查询出所有并列成绩的学生
SELECT * FROM grade WHERE courseid='c10' score >= (SELECT score FROM grade WHERE courseid='c10' ORDER BY score DESC LIMIT 9,1) ORDER BY score DESC;
SELECT * FROM grade WHERE courseid='c10' score = ANY(SELECT score FROM grade WHERE courseid='c10' ORDER BY score DESC LIMIT 10) ORDER BY score DESC;
2.模糊查询
# Like关键字做模糊查询：用%代替任意字符，用_代替一个字符
# 查询姓张的人
select * from student where Sname like '张%';
# 查询名字里包含‘子’的人
select * from student where Sname like '%子%';
3.聚合函数
# 查询最大最小值
SELECT MAX(age) FROM student;
SELECT MIN(age) FROM student;
# 查询男生平均年龄
SELECT AVG(age) FROM student WHERE Ssex='男';
# 将查询出的平均成绩的列名重命名为平均年龄
SELECT AVG(age) as 平均年龄 FROM student WHERE Ssex='男';
# 统计行数
SELECT COUNT(*) FROM student;
# 统计列的总和
SELECT SUM(age) FROM student;
MySQL 函数 | 菜鸟教程 (runoob.com)
4.分组查询
# group by子句自带去重功能，可以代替distinct使用
# 查询每门学科的平均成绩,使用group by子句
select courseid,avg(score) from grade group by courseid;
# 查询平均成绩最高和最低的课程
select courseid,avg(score) 平均成绩 from grade group by courseid order by 平均成绩 desc limit 1;
select courseid,avg(score) 平均成绩 from grade group by courseid order by 平均成绩 limit 1;
# 查询平均成绩在70分以上的课程
# where 条件必须在group by 条件之前，where先执行再grop by ,此时分组后的结果无法通过where条件查询
select courseid,avg(score) as 平均成绩 from grade group by courseid where 平均成绩 > 70;
select courseid,avg(score) as 平均成绩 from grade where 平均成绩 > 70 group by courseid;
# 以上两个为错误案例，应当使用having关键字
select courseid,avg(score) as 平均成绩 from grade group by courseid having 平均成绩 > 70;
# 分组查询，可以针对多列进行分组
select sex,degree,avg(age) from student group by sex,degree;
5.其他
-
IN/NOT IN：查询条件在一个和集中 存在/不存在 ，也可以使用OR代替
-
ANY：用于条件判断的子查询，> < >= <=
# 查询本科学生比研究生学生年龄最大的小的人
select * from student where degree='本科' and age < ANY(select age from student where degree='研究生')
-
ALL：等价于再自己和中找到各个条件的与运算结果， > ALL :大于最大的， <ALL :小于最大的
-
IS NULL和IS NOT NULL：判断内容是否为空
-
UNION：联合查询，将两条sql语句的查询结果拼成一个结果集
# 查询姓张和姓王的人
select * from student where Sname like '张%' or Sname like '王%';
select * from student where Sname like '张%' union select * from student from Sname like '王%';
-
SELECT xxx：后面不跟from，直接查询xxx
二、多表查询
1.内连接
select * from student, class # 笛卡尔积
# 通过主外键进行条件过滤，得到有意义的数据
select * from student, class where student.studentid = grade.studentid
select student.studentid, sname, courseid, score, grade.createtime from student, class where student.studentid = grade.studentid
# 除了使用from a, b 这种逗号运算符进行多表连接外，也可以使用join……on
select * from student join class on student.studentid = grade.studentid
# 三表查询，查找成绩最差学生的老师是谁
select s.sname, c.teacher, g.score from student s, grade g, class c where s.studentid=g.studentid and s.classid=c.classid order by g.score limit 1;
# 在进行多表连接查询时，必须指定连接条件（通常是主外键关系），但是主外键关系没有明确在表中建立，也可以使用
2.外连接
# 左连接和右链接
left join # 以左边的表为基准，左表没有的行不连接，右表没有的行连接后显示为空
right join
# 统计每个用户的购买金额
select * from customer c left join orders o on c.customerid=o.customerid group by c.customerid
select c.customerid, c.name, sum(o.money) from customer c left join orders o on c.customerid=o.customerid group by c.customerid
select c.customerid, c.name, if(sum(o.money) is null, 0, sum(o.money)) total from customer c left join orders o on c.customerid=o.customerid group by c.customerid
# 视图；将sql语句的查询结果永久保存在一张类似于表的结构中，可以非常方便的把一些复杂的sql语句和业务逻辑封装
# 视图类似于子查询中的临时表
# 如果表结构发生变化，如果是写在视图中，就只需要修改视图一个地方就可以了
# 视图也可以做删除，修改或插入操作，会对物理表的列进行相应操作，但不建议这样做
create view customer_moeny as
select c.customerid, c.name, if(sum(o.money) is null, 0, sum(o.money)) total from customer c left join orders o on c.customerid=o.customerid group by c.customerid