昨天在說MySQL的分組(group by)時，最後有提到為什麼會多出來一行--> null, 原來null表示所有人加起來的數。
mysql> select name,sum(scores) as scores_count from teacher group by name with rollup;
+--------+--------------+
| name | scores_count |
+--------+--------------+
| 張三 | 158 |
| 李四 | 88 |
| 柳九 | 100 |
| 王五 | 94 |
| 胡八 | 92 |
| 趙六 | 49 |
| NULL | 581 |
+--------+--------------+
7 rows in set (0.00 sec)
這個null看起來很難受，能不能顯示為 總計、總量什麼的呢？
答案是可以的。但是需要用到coalesce文法。文法為:
select coalesce(a,b,c);
如果a == null, 則選擇b; 如果b==null,則選擇c；如果a!=null,則選擇a;如果a b c 都為null ，則傳回為null。那麼當name != null的時候，就顯示使用者的名字，如果名字為null時，就顯示為 總數。其實和java裡的循環很像。
我們來看看執行個體:
mysql> select coalesce(name,"總數"),sum(scores) from teacher group by name with rollup;
+-------------------------+-------------+
| coalesce(name,"總數") | sum(scores) |
+-------------------------+-------------+
| 張三 | 158 |
| 李四 | 88 |
| 柳九 | 100 |
| 王五 | 94 |
| 胡八 | 92 |
| 趙六 | 49 |
| 總數 | 581 |
+-------------------------+-------------+
7 rows in set (0.03 sec)
OK 好了！
接下來，說下MySQL中join的使用。這可是MySQL中的一個大重點！
先說下用法: 可以從多個資料庫表中讀取資料；
使用join在兩個或多個資料庫表中查詢資料；
可以在 SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。
join按照功能大緻分為三大類:
inner join 内連接配接或等值連接配接: 擷取兩個表中字段比對關系的記錄 可以忽略inner 直接寫 join
left join 左連接配接: 擷取左表所有記錄，即使右表沒有對應比對的記錄
right join 右連接配接: 擷取右表所有記錄，即使左表沒有對應比對的記錄
先看内連接配接 inner join
直接上執行個體:
mysql> select * from teacher;
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 張三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 趙六 | 2020-06-28 | 49 |
| 5 | 張三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
+----+--------+------------+--------+
8 rows in set (0.01 sec)
mysql> select * from course;
+----+----------+
| id | name |
+----+----------+
| 1 | 張三 |
| 2 | lucy |
| 3 | jack |
| 4 | Tom |
| 5 | Shanshan |
| 6 | Liulang |
| 7 | 胡扯 |
| 8 | 張三 |
| 9 | 李四 |
+----+----------+
9 rows in set (0.00 sec)
我們通過和teacher、course有聯系的name字段來擷取對應的scores值
mysql> select a.id,a.name,a.time,a.scores,b.id,b.name from teacher a inner join course b on a.name = b.name;
+----+--------+------------+--------+----+--------+
| id | name | time | scores | id | name |
+----+--------+------------+--------+----+--------+
| 1 | 張三 | 2020-06-28 | 77 | 1 | 張三 |
| 5 | 張三 | 2020-06-29 | 81 | 1 | 張三 |
| 1 | 張三 | 2020-06-28 | 77 | 8 | 張三 |
| 5 | 張三 | 2020-06-29 | 81 | 8 | 張三 |
| 2 | 李四 | 2020-06-28 | 88 | 9 | 李四 |
+----+--------+------------+--------+----+--------+
5 rows in set (0.00 sec)
我們可以看到對應的文法為:
select 你想顯示的表名(多個表的都可以)對應的列名 from 資料庫表a inner join 資料庫表b on 兩個資料庫表之間的聯系
inner join是交集，兩個表中都有的東西，如下圖
我們試試把inner去掉，隻要 join看看是不是結果也一樣
mysql> select a.id,a.name,a.time,a.scores,b.id,b.name from teacher a join course b on a.name = b.name;
+----+--------+------------+--------+----+--------+
| id | name | time | scores | id | name |
+----+--------+------------+--------+----+--------+
| 1 | 張三 | 2020-06-28 | 77 | 1 | 張三 |
| 5 | 張三 | 2020-06-29 | 81 | 1 | 張三 |
| 1 | 張三 | 2020-06-28 | 77 | 8 | 張三 |
| 5 | 張三 | 2020-06-29 | 81 | 8 | 張三 |
| 2 | 李四 | 2020-06-28 | 88 | 9 | 李四 |
+----+--------+------------+--------+----+--------+
5 rows in set (0.00 sec)
沒錯，結果是一樣的
然後我們對結果再進行下優化，顯示的資料有些重複，比如最後的id和name就顯示重複了，沒必要
mysql> select a.id,a.name,a.time,a.scores from teacher a join course b on a.name = b.name;
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 張三 | 2020-06-28 | 77 |
| 5 | 張三 | 2020-06-29 | 81 |
| 1 | 張三 | 2020-06-28 | 77 |
| 5 | 張三 | 2020-06-29 | 81 |
| 2 | 李四 | 2020-06-28 | 88 |
+----+--------+------------+--------+
5 rows in set (0.00 sec)
我們可以把資料庫表b要顯示的資料都删除掉，這樣顯示就好了很多
然後再核查下，資料顯示的是否正确
teacher表和course表的name字段中，相同的有: 張三、李四
其中teacher表中，有2個張三，course表中有2個張三，結果表中有四個張三
teacher表中有1個李四，course表中沒有李四，結果表中有1個李四
正确！
我們再來試試如果兩個表中的字段不比對，沒有對應的值，會有什麼結果
執行個體如下:
mysql> select a.id,a.name,a.time,a.scores from teacher a join course b on a.name = b.id;
Empty set (0.00 sec)
看吧，果然是空的～～哈哈
下面我們來看下 left join
left join 與 join 有所不同。MySQL LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。
上面傳回為空的例子拿到這裡就很好，大家可以看下
mysql> select a.id,a.name,a.time,a.scores from teacher a left join course b on a.name = b.id;
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 張三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 趙六 | 2020-06-28 | 49 |
| 5 | 張三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
+----+--------+------------+--------+
8 rows in set (0.01 sec)
這是teacher表中所有的資料嗎？
mysql> select * from teacher;
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 張三 | 2020-06-28 | 77 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 趙六 | 2020-06-28 | 49 |
| 5 | 張三 | 2020-06-29 | 81 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
+----+--------+------------+--------+
8 rows in set (0.00 sec)
嗯，沒錯，是的
如果兩個資料庫表的列有對應關系，會怎麼樣呢？
mysql> select a.id,a.name,a.time,a.scores from teacher a left join course b on a.name = b.name;
+----+--------+------------+--------+
| id | name | time | scores |
+----+--------+------------+--------+
| 1 | 張三 | 2020-06-28 | 77 |
| 5 | 張三 | 2020-06-29 | 81 |
| 1 | 張三 | 2020-06-28 | 77 |
| 5 | 張三 | 2020-06-29 | 81 |
| 2 | 李四 | 2020-06-28 | 88 |
| 3 | 王五 | 2020-06-28 | 66 |
| 4 | 趙六 | 2020-06-28 | 49 |
| 6 | 胡八 | 2020-06-29 | 92 |
| 7 | 柳九 | 2020-06-29 | 100 |
| 8 | 王五 | 2020-06-29 | 28 |
+----+--------+------------+--------+
10 rows in set (0.00 sec)
就是把左邊表中的資料和右邊表中也符合a.name = b.name的資料加到了一起
同樣，right join 也是一樣，會讀取右邊資料表的全部資料，即便左邊邊表無對應資料。
mysql> select a.id,b.name,a.scores from teacher a right join course b on a.name = b.name;
+------+----------+--------+
| id | name | scores |
+------+----------+--------+
| 1 | 張三 | 77 |
| 1 | 張三 | 77 |
| 2 | 李四 | 88 |
| 5 | 張三 | 81 |
| 5 | 張三 | 81 |
| NULL | lucy | NULL |
| NULL | jack | NULL |
| NULL | Tom | NULL |
| NULL | Shanshan | NULL |
| NULL | Liulang | NULL |
| NULL | 胡扯 | NULL |
+------+----------+--------+
11 rows in set (0.01 sec)
結果表就是 inner join結果+ 右表除了inner部分的資料，然後沒有的資料補齊null