阅读目录
1、多表查询
第一种语法格式
- 1、多表查询
- 第一种语法格式
- 第二种语法格式
- 第三种语法查询 group having
- 2、连接
- inner join 内连接
- 外连接 - 左外连接 left outer join
- 外连接 - 右外连接 right outer join
- 3、统计函数
- count () 统计记录数
- max min avg sum
- count(distinct sid)
- 4、思维导图
select * 字段名 from 表名1
[连接类型] join 表名 2 on 连接条件
[连接类型] join 表名 3 on 连接条件
where 查询条件;
select sno,sname,sex,cid,grade
from student
inner join score on student.id = score.sid
where sex = '女';
select * 字段列表 from 表名1,表名2
where 连接条件 and 查询条件;
select sno,sname,sex,cid,grade
from student,score
where student.id = score.sid and sex = '女';
select a.id,a.`name` AS '姓名',b.`subject`,c.`achievement`
from
aaa AS a
left join ccc AS c on a.id=c.uid
left join bbb AS b on c.sid=b.id
where a.id in(1,2,3)
group by c.achievement
having c.achievement>=60
order by c.achievement desc
limit 3
select sno,sname,sex,cid,grade
from student
inner join score on student.id = score.sid
inner join course on course.id = score.cid
where sno = '1308013101';
select sno,sname,sex,cid,deptname,grade
from student
left join score on student.id=score.sid
where deptname='网络131';
select sno,sname,sex,cid,deptname,grade
from student
right join score on student.id=score.sid
where deptname='网络131';
avg 平均值 max 最大值 min 最小值 sum 求和 count () 统计记录数
count () 统计记录数select count(*) as '男生人数'
from student
where sex = '男';
select
max(grade) as '最高分',
min(grade) as '最低分',
avg(grade) as '平均分',
sum(grade) as '总分'
from score join student
on student.id=score.sid
where sno = '1308013101';
select count(distinct sid) as '已选修课程学生人数' from score;