當(dāng)查詢結(jié)果的列來源于多張表時(shí)兽愤，需要將多張表連接成一個(gè)大的數(shù)據(jù)集，再選擇合適的列返回
mysql支持三種類型的連接查詢亚脆，分別為：
- 內(nèi)連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)
- 右連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)祷肯，右表特有的數(shù)據(jù)，對(duì)于左表中不存在的數(shù)據(jù)使用null填充
- 左連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)市框，左表特有的數(shù)據(jù)霞扬，對(duì)于右表中不存在的數(shù)據(jù)使用null填充
語法
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
示例：
-- 連接查詢
-- inner join ... on
-- 交集
-- select ... from 表A inner join 表B
select * from student inner join classes;
-- 查詢 學(xué)生信息及對(duì)應(yīng)班級(jí)信息
select * from student inner join classes on student.cls_id = classes.id;
-- 查詢 學(xué)生信息及對(duì)應(yīng)班級(jí)名字 不顯示班級(jí)id
select student.*,classes.name as class_name from student inner join classes on student.cls_id = classes.id;
-- 查詢 學(xué)生姓名 對(duì)應(yīng) 班級(jí)名字
select student.name,classes.name as class_name from student inner join classes on student.cls_id = classes.id;
-- 給數(shù)據(jù)表起名字
select s.*,c.name as class_name from student as s inner join classes as c on s.cls_id = c.id;
-- 查詢信息 班級(jí)名字顯示在第一列
select c.name,s.* from student as s inner join classes as c on s.cls_id = c.id;
-- 按照班級(jí)進(jìn)行排序
select classes.name,student.* from student inner join classes on student.cls_id = classes.id order by classes.name asc;
-- left join .. on ..
-- 以左邊的表的信息為基準(zhǔn)取值
-- 查詢 每位學(xué)生對(duì)應(yīng)的班級(jí)信息
select * from student left join classes on student.cls_id = classes.id;
-- 查詢沒有班級(jí)的學(xué)生信息
select * from student left join classes on student.cls_id = classes.id having classes.id is null ;
