MySQL高级查询二

一、内连接查询

信息表：主表		成绩表：从表
用内连接查询学生的姓名和成绩（只查询有成绩的）内连接 关键字 inner join on

select x.name,s.grade from xinxi x inner join score s on x.id=s.xid;
select x.name,s.grade from score s inner join xinxi x on x.id-s.xid;

两种效果一样

二、用笛卡尔积查询学生的姓名和成绩（只查询有成绩的）

select x.id,s.grade,s.xid from score s,xinxi x where x.id=s.xid;

三、用左外连接查询学生的姓名和成绩（如果主表在前查询结果有空值（没有成绩的））

select x.id,s.grade from xinxi x left join score s on x.id=s.xid;(有空值)
select x.id,s.grade from score s left join xinxi x on x.id=s.xid;

四、用右外连接查询学生的姓名和成绩（如果主表在后查询结果有空值）

select x.id,s.grade from xinxi x right join score s on x.id=s.xid;
select x.id,s.grade from score s right join xinxi x on x.id=s.xid;

五、三表连接查询学生的姓名、课程名和成绩

select x.name,c.name,s.grage from xinxi x
inner join score s on x.id=s.xid;
inner join course c on c.id=s.id

六、聚合函数

1、求平均年龄

select avg(age) from xinxi;

2、求最大最小年龄

select max(age),min(age) from xinxi;

3、求总人数

select count (*) from xinxi;
select count (1) from xinxi;
select count(id) from xinxi;
select count(name) from xinxi

4、四种查总数的区别
select count (*) ：对于返回的结果集，一行行的判断，如果count函数的参数不是NULL，累计值就加1，最后返回累加值。
select count (1) ：InnoDB引擎会遍历整张表，但不取值。server层对于返回的每一行，放一个数字"1"进去，判断是不可能为空的，按行累加。
select count(id（主键）) ：InnoDB引擎会遍历整张表，把每一行的id取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
select count(字段)：表示返回满足条件的数据行里面，字段值不为NULL的总个数。

七、查询学生的课程编号和平均分（group by 必须和聚合函数搭配使用）

select cid,avg(grade) from score group by cid;

八、多列分组查询学生的学号、课程编号和平均分（查询多个学生的多门课程的平均分）

select xid,cid avg(grade) from score group by xid,cid;

九、查询补考学生的成绩的平均分

select xid,cid,avg(grade) from score group by xid,cid having count(cid)>1;

十、内连接查询补考学生的成绩的平均分

select x.name,s.cid,avg(grade) from score s
inner join xinxi x on x.id=s.xid group by x.name ,s.cid having count(s.cid)>1;