一、外鍵
建立一個score 表，外鍵關聯student 表的stu_id
mysql> desc student;
+---------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| register_date | date | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
+---------------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 建立score表
mysql> create table score(
-> id int not null auto_increment primary key,
-> score tinyint,
-> stu_id int not null,
-> KEY `fk_student_key` (`stu_id`),
-> constraint `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc score;
+--------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| score | tinyint(4) | YES | | NULL | |
| stu_id | int(11) | NO | MUL | NULL | |
+--------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
插入資料
mysql> insert into score (score,stu_id) values (90,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score (score,stu_id) values (85,2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score (score,stu_id) values (89,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from score;
+----+-------+--------+
| id | score | stu_id |
+----+-------+--------+
| 1 | 90 | 1 |
| 2 | 85 | 2 |
| 3 | 89 | 2 |
+----+-------+--------+
3 rows in set (0.00 sec)
删除資料
删除score表中的資料不受影響，但是删除student表的資料，如果和score表相關聯，則不能删除
mysql> delete from score where id=3;
Query OK, 1 row affected (0.03 sec)
mysql> delete from student where stu_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`study`.`score`, CONSTRAINT `fk_student_key` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`))
二、Mysql NULL值處理
我們已經知道MySQL使用 SQL SELECT 指令及 WHERE 子句來讀取資料表中的資料,但是當提供的查詢條件字段為 NULL 時，該指令可能就無法正常工作。
為了處理這種情況，MySQL提供了三大運算符:
IS NULL: 當列的值是NULL,此運算符傳回true。
IS NOT NULL: 當列的值不為NULL, 運算符傳回true。
<=>: 比較操作符(不同于=運算符)，當比較的的兩個值為NULL時傳回true。
關于 NULL 的條件比較運算是比較特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。在MySQL中，NULL值與任何其它值的比較(即使是NULL)永遠傳回false，即 NULL = NULL 傳回false 。MySQL中處理NULL使用IS NULL和IS NOT NULL運算符。
三、聯合查詢
可以在SELECT, UPDATE 和 DELETE 語句中使用 Mysql 的 JOIN 來聯合多表查詢。
JOIN 按照功能大緻分為如下三類：
INNER JOIN(内連接配接,或等值連接配接)：擷取兩個表中字段比對關系的記錄。
LEFT JOIN(左連接配接)：擷取左表所有記錄，即使右表沒有對應比對的記錄。
RIGHT JOIN(右連接配接)： 與 LEFT JOIN 相反，用于擷取右表所有記錄，即使左表沒有對應比對的記錄
3.1 inner join
聯合查詢student 和score的id, name, score
mysql> select a.stu_id,a.name,b.score from student as a
-> inner join score as b
-> on a.stu_id = b.stu_id;
+--------+---------+-------+
| stu_id | name | score |
+--------+---------+-------+
| 1 | bigberg | 90 |
| 2 | zhansan | 85 |
+--------+---------+-------+
2 rows in set (0.00 sec)
3.2 left join
LEFT JOIN 關鍵字從左表(table1)傳回所有的行，即使右表(table2)中沒有比對。如果右表中沒有比對，則結果為 NULL。
mysql> select a.stu_id as id ,a.name, b.score from student as a
-> left join score as b
-> on a.stu_id = b.stu_id;
+----+---------+-------+
| id | name | score |
+----+---------+-------+
| 1 | bigberg | 90 |
| 2 | zhansan | 85 |
| 3 | lisi | NULL |
| 5 | wangwu | NULL |
| 6 | Lily | NULL |
+----+---------+-------+
5 rows in set (0.04 sec)
3.3 right join
與left join 相反
mysql> select a.stu_id, a.name, b.score from student as a
-> right join score as b
-> on a.stu_id = b.stu_id;
+--------+---------+-------+
| stu_id | name | score |
+--------+---------+-------+
| 1 | bigberg | 90 |
| 2 | zhansan | 85 |
| 5 | wangwu | 99 |
+--------+---------+-------+
3 rows in set (0.00 sec)
3.4 full join
mysql 并不直接支援full join，但可以通過其他方法解決
mysql> select a.stu_id, a.name, b.score from student as a left join score as b on a.stu_id = b.stu_id
-> union
-> select a.stu_id, a.name, b.score from student as a right join score as b on a.stu_id = b.stu_id;
+--------+---------+-------+
| stu_id | name | score |
+--------+---------+-------+
| 1 | bigberg | 90 |
| 2 | zhansan | 85 |
| 5 | wangwu | 99 |
| 3 | lisi | NULL |
| 6 | Lily | NULL |
+--------+---------+-------+
5 rows in set (0.00 sec)