MySQL實(shí)戰(zhàn) 目錄

99版SQL語法格式區(qū)別于92版但是連接不是在用"=",它但語法格式：
SELECT 查詢列表
FROM 表1 別名
[連接類型] join 表2 別名
on 鏈接條件
WHERE 數(shù)據(jù)篩選條件......以前講過但其他部分

[連接類型]地關(guān)鍵字
內(nèi)連接L: inner（還可以忽略）
外鏈接:
左外連接left[outer]
有外連接right[outer]
全外連接full[outer]
交叉連接 cross[outer]

1.內(nèi)連接查詢

SQL寫法實(shí)現(xiàn)等值連接查詢

案例：查詢員工名和其對應(yīng)所屬的部門名

92版:

SELECT last_name,department_name
       FROM employees e, departments d
             WHERE  e.department_id = d.department_id;

99版:

SELECT last_name,department_name
       FROM employees e
             INNER JOIN departments d
             ON  e.department_id = d.department_id;

案例：查詢名字中包含e字母的員工名和其對象的部門名

SELECT last_name,department_name
       FROM employees e
             INNER JOIN departments d
             ON  e.department_id = d.department_id
             WHERE last_name LIKE '%e%';

案例：查詢所在部門個數(shù)大于3的城市名和部門個數(shù)

SELECT city,COUNT(*) FROM
          locations l
                    INNER JOIN departments d
                    ON d.location_id=l.location_id
                    GROUP BY city
                    HAVING COUNT(*) >3;

案例：查詢員工個數(shù)大于3的部門名和員工個數(shù)，降序排序

SELECT department_name ,COUNT(*) 員工個數(shù) FROM
          employees e
                    INNER JOIN departments d
                    ON e.department_id=d.department_id
                    GROUP BY department_name
                    HAVING COUNT(*) >3
                    ORDER BY 員工個數(shù) DESC;

案例：查詢員工名以及對應(yīng)的工種名和部門名，按部門名降序排列

SELECT last_name,job_title,department_name 
    FROM employees e
        INNER JOIN
      departments d
     ON e.department_id =d.department_id
     INNER JOIN
          jobs j
     ON 
    j.job_id = e.job_id
        ORDER BY department_name DESC;

99版非等值查詢

案例：查詢員工名，對應(yīng)工資,對應(yīng)級別

        SELECT last_name,salary,grade_level
        FROM employees e
        INNER JOIN
        job_grades g
        ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例：查詢同一個工資級別的員工的個數(shù)大于20的員工個數(shù)，并且按照工資級別降序排序

SELECT COUNT(*) 員工個數(shù),g.grade_level
            FROM employees e
            INNER JOIN
            job_grades g
            ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
            GROUP BY g.grade_level
            HAVING COUNT(*) >20
            ORDER BY g.grade_level DESC;

案例：查詢員工的名和其對應(yīng)的直屬領(lǐng)導(dǎo)的名字

SELECT e.last_name,m.last_name 
        FROM employees e
                INNER JOIN
                employees m
                on e.manager_id = m.manager_id;

2.外連接查詢

看這樣的場景，在ta和tb兩表中查詢沒有對應(yīng)年齡數(shù)據(jù)的學(xué)生姓名和年齡

SELECT
     tb.`name`,ta.`age`
         FROM tb
         INNER JOIN
          ta
            on tb.ta_id = ta.id
            WHERE ta.id IS NULL;

無法解決

SELECT
     tb.`name`,ta.`age`
         FROM tb
         LEFT OUTER JOIN
          ta
            on tb.ta_id = ta.id
            WHERE ta.id IS NULL;

外連接的查詢結(jié)果為主表中有的所有記錄

如果外表中有對應(yīng)數(shù)據(jù)，結(jié)果記錄上顯示對應(yīng)數(shù)據(jù)
 外表中沒有對應(yīng)的數(shù)據(jù)，結(jié)果記錄上填充null

和內(nèi)連接的區(qū)別：

內(nèi)連接：當(dāng)從表沒有記錄的時候，主、從表的記錄都丟掉
外連接:

1. 當(dāng)從表沒有記錄的時候，會保留主表的記錄，對應(yīng)從表null
2. 外連接：left join左邊的是主表，右外連接right join右邊的是主表
3. 左外連接和右外連接上互通的所以掌握一個就好

案例：在ta和tb兩表中查詢沒有對應(yīng)年齡數(shù)據(jù)的學(xué)生姓名和年齡

       SELECT
     tb.`name`,ta.`age`
         FROM tb
         LEFT OUTER JOIN
          ta
            on tb.ta_id = ta.id
            WHERE ta.id IS NULL;

案例：查詢沒有員工的部門

    SELECT department_name,e.last_name FROM departments d
           LEFT JOIN employees e
                 ON d.department_id = e.department_id
                 WHERE e.department_id IS NULL;

注意

本文用到的myemployees.sql 文件在MySQL實(shí)戰(zhàn)2 語法、篩選條件和函數(shù) 附件中