當(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 ;