1 帶外鍵的兩個表，當插入記錄的時候必須先在父表中插入記錄，然後在子表中插入記錄2 父表3 mysql>create table provinces(4 ->id smallint unsigned primary key auto_increment,5 -> pname varchar(30) not null
6 ->);7
8 檢視資料表引擎9 mysql>show create table provinces;10 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 | Table | Create Table |
12 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
13 | provinces |CREATE TABLE `provinces` (14 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,15 `pname` varchar(30) NOT NULL,16 PRIMARY KEY (`id`)17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
18 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 1 row in set (0.00sec)20
21 子表(子表中的外鍵的資料類型和父表中對應做外鍵的鍵的資料類型要一緻，不然會出錯)22 創造外鍵23 mysql>create table users(24 ->id smallint unsigned primary key auto_increment,25 -> username varchar(30) not null,26 ->pid smallint unsigned,27 ->foreign key (pid) references provinces (id)28 ->);29 Query OK, 0 rows affected (0.65sec)30
31 cascade 從父表删除或更新且自動删除或更新子表中比對的行32 set null從父表删除或更新行，并設定子表中的外鍵列為null。如果使用該選項，必須保證33 子表列沒有指定not null
34 restrict 拒絕對父表的删除或更新操作35 no action 标準SQL關鍵字，在MySQL中雨restrict相同36
37 建立資料表，加上cascade38 mysql>create table users1(39 ->id smallint unsigned primary key auto_increment,40 -> username varchar(30) not null,41 ->pid smallint unsigned,42 -> foreign key (pid) references provinces (id) on delete cascade ->);43 Query OK, 0 rows affected (0.65sec)44
45
46 對資料表進行添加删除47 mysql> insert provinces (pname) values ('A');48 Query OK, 1 row affected (0.11sec)49
50 mysql> insert provinces (pname) values ('B');51 Query OK, 1 row affected (0.08sec)52
53 mysql> insert provinces (pname) values ('C');54 Query OK, 1 row affected (0.05sec)55
56 mysql> select * fromprovinces;57 +----+-------+
58 | id | pname |
59 +----+-------+
60 | 1 | A |
61 | 2 | B |
62 | 3 | C |
63 +----+-------+
64 3 rows in set (0.00sec)65
66 mysql> insert users1 (username, pid) values('Mike', 3);67 Query OK, 1 row affected (0.09sec)68
69 由于父表中不存在id等于7的情況，是以報錯70 mysql> insert users1 (username, pid) values('John', 7);71 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t1`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)72 mysql> insert users1 (username, pid) values('John', 2);73 Query OK, 1 row affected (0.24sec)74
75 mysql> insert users1 (username, pid) values('Tom', 1);76 Query OK, 1 row affected (0.09sec)77
78 mysql> select * fromusers1;79 +----+----------+------+
80 | id | username | pid |
81 +----+----------+------+
82 | 1 | Mike | 3 |
83 | 3 | John | 2 |
84 | 4 | Tom | 1 |
85 +----+----------+------+
86 3 rows in set (0.00sec)87
88 删除父表中id為3的記錄89 mysql> delete from provinces where id = 3;90 Query OK, 1 row affected (0.15sec)91
92 mysql> select * fromprovinces;93 +----+-------+
94 | id | pname |
95 +----+-------+
96 | 1 | A |
97 | 2 | B |
98 +----+-------+
99 2 rows in set (0.00sec)100
101 對子表産生了影響102 mysql> select * fromusers1;103 +----+----------+------+
104 | id | username | pid |
105 +----+----------+------+
106 | 3 | John | 2 |
107 | 4 | Tom | 1 |
108 +----+----------+------+
109 2 rows in set (0.00sec)110
111 資料表的更新操作：112 update set指令用來修改表中的資料。113 update set指令格式：update 表名 set 字段=新值,… where條件;114
115 mysql> update users1 set username = 'wuxie' where id = 3;116 Query OK, 1 row affected (0.15sec)117 Rows matched: 1 Changed: 1 Warnings: 0
118
119 mysql> select * fromusers1;120 +----+----------+------+
121 | id | username | pid |
122 +----+----------+------+
123 | 3 | wuxie | 2 |
124 | 4 | Tom | 1 |
125 +----+----------+------+
126 2 rows in set (0.00sec)127
128 mysql> update users1 set username = 'John', pid = 3 where id = 1;129 Query OK, 0 rows affected (0.00sec)130 Rows matched: 0 Changed: 0 Warnings: 0
131
132 mysql> select * fromusers1;133 +----+----------+------+
134 | id | username | pid |
135 +----+----------+------+
136 | 3 | wuxie | 2 |
137 | 4 | Tom | 1 |
138 +----+----------+------+
139 2 rows in set (0.00 sec)