MySql学习笔记10——连接查询(sql92语法)

大家好，欢迎来到IT知识分享网。

含义: 连接查询 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象:表1有m行，表2有n行，结果=m*n行

发生原因:没有有效的连接条件

如何避免:添加有效的连接条件

分类:

student表

teacher表

level表

sql92标准

1.等值连接

①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

查询每个学生对应的各科教师的名字

select student.name,student.subject,teacher.name from student , teacher

where student.teacher_id = teacher.id

可以为表取别名:

①提高语句的简洁度

②区分多个重名的字段

注意:如果为表起了别名，则查询的字段就不能使用原来的表名去限定

select st.name,st.subject,te.name from student as st, teacher as te

where st.teacher_id = te.id

两个表的顺序可以调换:

select st.name,st.subject,te.name from teacher as te ,student as st

where st.teacher_id = te.id

加筛选条件查询:

查询每个学生对应的语文教师的名字

select st.name,st.subject,te.name from teacher as te ,student as st

where st.teacher_id = te.id and st.subject =’语文’

加分组查询:

查询张行老师学生的语文成绩平均分

select st.subject,te.name,avg(st.score) from teacher as te ,student as st

where st.teacher_id = te.id and te.name =’张行’ and st.subject =’语文’

group by st.subject,te.name

加排序查询:

查询张行老师学生的语文成绩(由高到低排序)

select st.name,st.subject,te.name,st.score from teacher as te ,student as st

where st.teacher_id = te.id and te.name =’张行’ and st.subject =’语文’

group by st.name,st.subject,te.name,st.score

order by st.score desc

2.非等值连接

查询学生各科成绩的等级

select st.name,st.subject,st.score le.level from student as st,level as le

where st.score between le.lowest_score and le.highest_score