MySQL表的进阶知识(上):https://developer.aliyun.com/article/1520464
四、查询
1、聚合查询
以下演示以school表为基础:
a、聚合函数查询
聚合函数有:
涉及数字的聚合函数遇到null值会进行忽略。
聚合函数查询可以搭配where子句。
例如:统计school表中的元组个数:
select count(*) from school;
例如:求出学校表的姓张的平均工资:
select avg(salary) from school where name like '张%';
例如:求出学校表的最高工资:
select max(salary) from school;
b、group by
分组查询:对表中的数组先进行分组,再进行相关查询。
例如:查询每个职位的最低工资:
select posts, min(salary) from school group by posts;
同样,group by也可以搭配where子句使用:
例如:查询除了院长的职位的最高工资:
select posts, max(salary) from school where posts!="院长" group by(posts);
c、having
having对group by分组之后的结果进行筛选,而where是在group by分组之前进行筛选。
例如:查询school表中的平均工资在一万以上的:
select posts,avg(salary) from school group by posts having avg(salary)>10000;
2、联合查询
在实际开发中,通常使用的是联合查询,也就是联合多个表进行查询,那么就必须了解表之间的笛卡尔积:
笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尔积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
那么两个表的笛卡尔积:
a,b两个表进行笛卡尔积,笛卡尔积表的列数=a的列数+b的列数,笛卡尔积表的行数=a的行数*b的行数,
通俗来讲,笛卡尔积就是两个表全排列的所有情况。
后续操作是在以下三个表的基础上:
学生表:
课程表:
分数表:
a、内连接
两个表之间的某一字段相等的连接。
语法格式:
select 字段名 from 表1,表2 where 连接条件 and 其他条件;
select 字段名 from 表1 join 表2 on 连接条件 and 其他条件;
连接条件指的是两个表中的相同字段名相等。
例如:查找Java成绩高于85的学生信息 :
select student.name,course.name,score from student,course,score where student.id=score.student_id and course.id=score.course_id and course.name='Java' and score>85; --或者 select student.name,course.name,score from student join course join score on student.id=score.student_id and course.id=score.course_id and course.name='Java' and score>85;
查询结果:
例如:按照学生的平均分进行排名:
select student.id,student.name,avg(score) as avg from student,course,score where student.id=score.student_id and course.id=score.course_id group by(student.id) order by(avg) desc; --或者 select student.id,student.name,avg(score) as avg from student join course join score on student.id=score.student_id and course.id=score.course_id group by(student.id) order by(avg) desc;
查询结果:
select student.name,course.name,score from student left join score on student.id=score.student_id left join course on course.id=score.course_id;
b、外连接
通俗来讲,就是把一张表的某些字段连接到另一张表上。
外连接可分为:左外连接和右外连接。
左外连接的语法格式:
select 字段名 from 表1 left join 表2 on 连接条件;
右外连接的语法格式:
select 字段名 from 表1 right join 表2 on连接条件;
例如:查询所有学生的各科成绩:
select student.name,course.name,score from student left join score on student.id=score.student_id left join course on course.id=score.course_id;
查询结果:
c、自连接
在同一张表中进行自身的查询,就是将行与行之间的查询转换成列与列之间的查询。
例如:在score表中查询Java成绩高于C++的:
select s1.student_id,s1.course_id,s1.score,s2.course_id,s2.score from score as s1,score as s2 where s1.student_id=s2.student_id and s1.course_id=12 and s2.course_id=13 and s1.score>s2.score;
查询结果:
3、子查询
是指在select查询语句中再嵌套一个查询语句。
例如:查询选修Java的学生姓名:
select student.name from student where id in (select student_id from score where course_id=12);
4、合并查询
与 or 关键字查询结果基本一致,只是or是属于单表查询,而合并查询是多表查询。
语法格式:
select …… union select……:对查询结果去重
select …… union all select……:对查询结果不去重
例如:查询选修Java或者分数大于90的学生id:
select student_id from score where course_id=12 union select student_id from score where score>90;
查询结果: