(1)三种方法
select c.id,c.name,p.name from city as c inner join province as p on p.id=c.pid
select c.id,c.name,p.name from city as c join province as p on p.id=c.pid
select c.id,c.name,p.name from city as c,province as p where p.id=c.pid
select c.id,c.name,p.name from province as p left join city as c on p.id=c.pid
select c.id,c.name,p.name from city as c right join province as p on p.id=c.pid
考点：left join , right join join inner join
left join 和right join 后面必须有on
join和inner join 后面可以是on也可以是where
(2)select count(c.id),c.pid,p.name as pname from city as c join province as p on p.id=c.pid GROUP by c.pid
select count(c.pid),p.name from city as c join province as p where p.id=c.pid group by p.id order by pid
select p.pid,p2.name,p.c from (select count(id) as c,pid from city GROUP by pid) as p left join province as p2 on p2.id=p.pid order by p.c desc
考点：1 group by分组 和count统计函数的使用
2 sql查询每科成绩最高的人和分数
select s1.name,s1.class,s1.source from grade s1 join (select class,max(source) as source from grade group by class) as s2 on s1.class=s2.class and s1.source= s2.source
select g.class,g.name,tt.maxSource from grade as g inner join (select id,max(source) as maxSource from grade GROUP by class) as tt on g.source=tt.maxSource
考点：1 group by 和max等统计函数的使用
2 left join 关联查询
注意点：后面on 一定是两个表的分数字段相等
思路：1 先用一个子查询，找出每科的最高分
2 再用join 做关联查询
3
select c.name,
count(c.id) as cnum,u.sex from ms_chejiu as c inner join ms_user as u on u.id=c.uid group by c.name,u.sex order by cnum desc
select c.name,case when u.sex=1 then "男" else "女" end as xb,count(c.id) from ms_chejiu c join ms_user u on u.id=c.uid group by c.name,u.sex
考点：1 group by 分组，这里需要注意的是用了两个分组
2 count 统计函数
3 join 关联表
4 case end 分支结构
