整理常用SQL语句（三）

多表查询

连接查询

内连接 A ∩ B

select * from inner table_a join table_b on table_a.id = table_b.id

select * from inner table_a as tb_a join table_b as tb_b on tb_a.id = tb_b.id

select * from inner table_a tb_a join table_b tb_b on tb_a.id = tb_b.id

-- 隐式

select * from student_table, score_table where tb_a.id = tb_b.id

-- 推荐

select * from score s inner join course c on s.courseNo = c.courseNo;

-- 结合where

select Table_A A inner join Table_B B on A.id = B.id where condiction;

多表连接

select Table_A A inner join Table_B B on A.id = B.id inner join Tabel_C C on B.name = C.name;

select * from students inner join scores on students.stuNo = score.stuNo inner join courses on score.courseNo = couerses.couerseNo;

三步法

-- 框架 -- where -- * ， 先把要查的表内容查出来，再鞋where条件，再指定显示那些字段。

左连接 A ∩ B + A

select * from student_table left join scores on student.id = scores.id;

右连接 A ∩ B + B

select * from student_table right join scores on student.id = scores.id;

自关联

select * from areas a1 inner join areas a2 on a1.id = a2.pid where name = '广东省';

子查询

子查询是嵌套到主查询里面的
子查询作为主查询的数据源或者条件
子查询可以单独运行的查询语句
主查询依赖于主查询的结构，不能独立运行

标子查询： 子查询只放回一行一列的查询

-- 年龄大于平均年龄的学生

select * from students where age > (select avg(age) from students);

列子查询： 只反回一列

-- 查询30岁的学生成绩

select * from students where studentNo in (select studentNo from students where age =30);

表级子查询： 反回一个表

-- 查询所有女生的信息

select * from (select * from students where sex = '女') as g_stu_table
inner join score sc on stu.studentNo = sc.studentNo;