多表联查:

注:表不可拆分

两张表一起查询:

select * from tb_stu,r_cou_stu;

选定特定信息查询:

select * from tb_stu,r_cou_stu
where tb_stu.id = r_cou_stu.sid;

取别名查询方法一:

select * from tb_stu as s,r_con_stu as r
where s.id = r.sid;

取别名查询方法二:

select s.stuname, s.id from tb_stu as s,r_con_stu as r
where s.id = r.sid;

查询tb_stu和tb_con两张表的内容

select s.*,c.* from tb_stu s, tb_con c,r_con_stu r
where s.id = r.sid and c.id = r.cid;

查询里面显示学生名字和他选的课程:

select s.stuname,c.conname from tb_stu s, tb_con c,r_con_stu r
where s.id = r.sid and c.id = r.cid;
连接查询(join):
select * from tb_stu as s join r_cou_stu as r on s.id = r.sid;

加入条件:

select * from tb_stu as s join r_cou_stu as r on s.id = r.sid 
where s.id = 4;

三个表一个条件查询

select * from tb_stu as s join r_cou_stu as r on s.id = r.sid
join tb_con as c on r.cid = c.id where s.id =4;

要确保两张表要有关系

左右外连接必须带过滤条件

左外连接:

select * from tb_stu as s left join r_cou_stu as r on s.id = r.sid;

三张表同时关联左外连接且stu的id为null

select * from tb_stu as s left join r_cou_stu as r on s.id = r.sid
left join tb_con as c on r.cid = c.id where s.id is null;

三张表同时关联左外连接且stu的id不为null

select * from tb_stu as s join r_cou_stu as r on s.id = r.sid
left join tb_con as c on r.cid = c.id where s.id is not null;

一对多的关系时,多的来记录少的信息只用一张表

多对多时一定是用关联关系表进行多表联查

查询单一信息

select sid from r_cou_stu;

查询没选课的人:

select  stuname from tb_stu where id not in(1,1,2,2,2,3,3,4);

子查询用一个select语句主查询内包含一个子查询:

select  stuname from tb_stu where id not in(select sid from r_cou_stu);

查询选java的学生姓名全部是子查询:

select stuname from tb_stu where id in (
select sid from r_cou_stu where cid in(
select id from tb_con where conname = 'JAVA'));

查询选java的学生姓名(方法二):

select stuname from (select s.stuname , c.conname from tb_stu s,
r_cou_stu r,tb_con c where s.id = r.sid and c.id = r.cid) t 
where conname = 'JAVA'

子查询的()外必须给别名

聚合函数:

全部选过课的学生:

select *from tb_stu s,r_cou_stu r where s.id = r.sid;

分组(报错,直播15:45)

select *from tb_stu s,r_cou_stu r where s.id = r.sid group by s.id;

查询选各种课的人数统计:

select c.conname '课程名',count(c.id)'人数' from
 tb_con c, r_cou_stu r where c.id = r.cid group by c.id;

筛选大于两个人数以上的课程(方法一):

select * from(select c.conname '课程名',count(c.id)'人数' from 
tb_con c, r_cou_stu r where c.id = r.cid group by c.id) t 
where t.人数>=2;

方法二:

select c.conname '课程名',count(c.id)'人数' from 
tb_con c, r_cou_stu r where c.id = r.cid group by c.id 
having count(c.id) >= 2;
select * from(select c.conname cname,count(c.id) ccount 
from tb_con c, r_cou_stu r where c.id = r.cid group by c.id) t 
where t.ccount >= 2;
select c.conname cname,count(c.id) ccount 
from tb_con c, r_cou_stu r where c.id = r.cid group by c.id;

向关联表中加入成绩:

alter table r_cou_stu add score int;

查找最高,最低成绩:

select r.cid ,min(score),max(score) from r_cou_stu r 
group by r.cid;

没有挂过科的学生姓名(方法一):

select s.stuname from (select r.sid ,min(score) 
from r_cou_stu r group by r.sid having min(score) >=60)
as t ,tb_stu s where s.id = t.sid;

方法二:

having 的用法:当聚合函数执行完了要将聚合函数(group用来聚合)的结果当做条件还要再一次过滤的时候就可以调用having

顺序是先是from------> join------->from的结果----->where------>group------->having

select s.stuname from r_cou_stu r, tb_stu s where s.id = r.sid 
group by s.stuname having min(r.score) >= 60;

排序过程:

升序:

select s.stuname,c.conname,r.score from tb_stu s,tb_con c,
r_cou_stu r where s.id = r.sid and c.id = r.cid  order by r.score;

降序:

select s.stuname,c.conname,r.score from tb_stu s,tb_con c,
r_cou_stu r where s.id = r.sid and c.id = r.cid  
order by r.score desc;

练习题:

1.查询出来平均分最高的科目?

select conname,cid,maxavg from(select c.conname, r.cid,avg(score) 
as maxavg from r_cou_stu r,tb_con c where c.id = r.cid 
group by r.cid ) t group by cid 
having max(maxavg)= (select max(maxavg) from (select r.cid,
avg(score) as maxavg from r_cou_stu r group by r.cid) as t);

输出结果:

±--------±-----±--------+
| conname | cid | maxavg |
±--------±-----±--------+
| C++ | 4 | 73.6000 |
±--------±-----±--------+

2.查询出来平均分最高的学生?

一些小步骤:

select r.sid, avg(score) from r_cou_stu r group by r.sid;
select stuname from(select r.sid,avg(score) from r_cou_stu r 
group by r.sid having avg(score) >=85)  as t,tb_stu s 
where s.id = t.sid;//平均成绩大于85的人

输出结果:

1、

±-----±-----------+
| sid | avg(score) |
±-----±-----------+
| 1 | 65.7500 |
| 2 | 63.7500 |
| 3 | 65.2500 |
| 4 | 85.7500 |
| 5 | 76.5000 |
±-----±-----------+

优化的部分:

> select stuname,sid,maxavg from(select s.stuname, r.sid,avg(score)
    -> as maxavg from r_cou_stu r,tb_stu s where s.id = r.sid
    -> group by r.sid ) t group by sid
    -> having max(maxavg)= (select max(maxavg) from (select r.sid,
    -> avg(score) as maxavg from r_cou_stu r group by r.sid) 
as t);

输出结果:

±--------±-----±--------+
| stuname | sid | maxavg |
±--------±-----±--------+
| 马 | 4 | 85.7500 |
±--------±-----±--------+

3.查询出来每个挂的学生分别挂了哪些科目。

select s.stuname,c.conname,r.score from tb_stu s, tb_con c,
r_cou_stu r where s.id = r.sid and c.id = r.cid 
having r.score < 60;

输出结果:

±--------±--------±------+
| stuname | conname | score |
±--------±--------±------+
| 马杨杨 | 数据库 | 59 |
| 常娜 | JAVA | 56 |
| 宋健 | JAVA | 51 |
| 宋健 | C++ | 50 |
| 谢阳 | 数据库 | 57 |
±--------±--------±------+

所有学生的各个科目成绩:

select stuname,conname ,score from
(select s.stuname,c.conname,r.score from tb_stu s, tb_con c, 
r_cou_stu r where s.id = r.sid and c.id = r.cid ) t;

±--------±--------±------+
| stuname | conname | score |
±--------±--------±------+
| 马杨杨 | JAVA | 72 |
| 马杨杨 | HTML | 60 |
| 马杨杨 | 数据库 | 59 |
| 马杨杨 | C++ | 72 |
| 常娜 | JAVA | 56 |
| 常娜 | HTML | 67 |
| 常娜 | 数据库 | 63 |
| 常娜 | C++ | 69 |
| 宋健 | JAVA | 51 |
| 宋健 | HTML | 82 |
| 宋健 | 数据库 | 78 |
| 宋健 | C++ | 50 |
| 谭捷 | JAVA | 82 |
| 谭捷 | HTML | 76 |
| 谭捷 | 数据库 | 96 |
| 谭捷 | C++ | 89 |
| 谢阳 | JAVA | 90 |
| 谢阳 | HTML | 71 |
| 谢阳 | 数据库 | 57 |
| 谢阳 | C++ | 88 |
±--------±--------±------+
20 rows in set (0.00 sec)

4.查询出来每个挂科学生,挂了几科?

select s.stuname,c.conname,count(s.stuname) from
 tb_stu s, tb_con c,r_cou_stu r where s.id = r.sid 
and c.id = r.cid and score <60 group by s.stuname;

输出结果:

±--------±--------±------------+
| stuname | conname | count(s.id) |
±--------±--------±------------+
| 马杨杨 | 数据库 | 1 |
| 常娜 | JAVA | 1 |
| 宋健 | JAVA | 2 |
| 谢阳 | 数据库 | 1 |
±--------±--------±------------+

5.查询出来指定科目的最高分是谁?

select s.stuname ,f.conname,r.score from (
select max(r.score) as MaxScore,r.cid as cid,c.conname as conname
from r_cou_stu r , tb_con c where r.cid = c.id
group by r.cid )f,r_cou_stu r ,tb_stu s
where r.cid = f.cid and r.score = f.MaxScore and r.sid=s.id;

输出结果:

±--------±--------±------+
| stuname | conname | score |
±--------±--------±------+
| 宋健 | HTML | 82 |
| 谭捷 | 数据库 | 96 |
| 谭捷 | C++ | 89 |
| 谢阳 | JAVA | 90 |
±--------±--------±------+

6.查询出来个人考的最高分的学科是哪个。

select f.stuname ,c.conname,r.score from (
select max(r.score) as MaxScore,r.sid as sid,s.stuname as stuname
from r_cou_stu r , tb_stu s where r.sid = s.id
group by r.sid )f,r_cou_stu r ,tb_con c
where r.sid = f.sid and r.score = f.MaxScore and r.cid=c.id;

输出结果:

±--------±--------±------+
| stuname | conname | score |
±--------±--------±------+
| 马杨杨 | JAVA | 72 |
| 马杨杨 | C++ | 72 |
| 常娜 | C++ | 69 |
| 宋健 | HTML | 82 |
| 谭捷 | 数据库 | 96 |
| 谢阳 | JAVA | 90 |
±--------±--------±------+

7.查询出来每个科目的平均分并按照降序排序。

select c.conname,avg(score) from
 r_cou_stu r,tb_con c where c.id = r.cid
 group by r.cid order by avg(score) desc;

输出结果:

±--------±-----------+
| conname | avg(score) |
±--------±-----------+
| C++ | 73.6000 |
| HTML | 71.2000 |
| 数据库 | 70.6000 |
| JAVA | 70.2000 |
±--------±-----------+

8.查询出来所有学生的平均分,并按照升序排序。

select s.stuname,avg(score) from
r_cou_stu r,tb_stu s where s.id = r.sid
group by r.sid order by avg(score);

输出结果:

±--------±-----------+
| stuname | avg(score) |
±--------±-----------+
| 常娜 | 63.7500 |
| 宋健 | 65.2500 |
| 马杨杨 | 65.7500 |
| 谢阳 | 76.5000 |
| 谭捷 | 85.7500 |
±--------±-----------+
5 rows in set (0.00 sec)