mysql中外鍵的一些基本操作

標籤：prim   引擎   delete   select   error   utf8   0.00   color   value   

  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.00 sec) 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.65 sec) 30  31 cascade    從父表刪除或更新且自動刪除或更新子表中匹配的行 32 set null    從父表刪除或更新行，並設定子表中的外鍵列為null。如果使用該選項，必須保證 33 子表列沒有指定not null 34 restrict    拒絕對父表的刪除或更新操作 35 no action    標準SQL關鍵字，在MySQL中雨restrict相同 36  37 建立資料表，加上cascade 38 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.65 sec) 44  45  46 對資料表進行添加刪除 47 mysql> insert provinces (pname) values (‘A‘); 48 Query OK, 1 row affected (0.11 sec) 49  50 mysql> insert provinces (pname) values (‘B‘); 51 Query OK, 1 row affected (0.08 sec) 52  53 mysql> insert provinces (pname) values (‘C‘); 54 Query OK, 1 row affected (0.05 sec) 55  56 mysql> select * from provinces; 57 +----+-------+ 58 | id | pname | 59 +----+-------+ 60 |  1 | A     | 61 |  2 | B     | 62 |  3 | C     | 63 +----+-------+ 64 3 rows in set (0.00 sec) 65  66 mysql> insert users1 (username, pid) values(‘Mike‘, 3); 67 Query OK, 1 row affected (0.09 sec) 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.24 sec) 74  75 mysql> insert users1 (username, pid) values(‘Tom‘, 1); 76 Query OK, 1 row affected (0.09 sec) 77  78 mysql> select * from users1; 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.00 sec) 87  88 刪除父表中id為3的記錄 89 mysql> delete from provinces where id = 3; 90 Query OK, 1 row affected (0.15 sec) 91  92 mysql> select * from provinces; 93 +----+-------+ 94 | id | pname | 95 +----+-------+ 96 |  1 | A     | 97 |  2 | B     | 98 +----+-------+ 99 2 rows in set (0.00 sec)100 101 對子表產生了影響102 mysql> select * from users1;103 +----+----------+------+104 | id | username | pid  |105 +----+----------+------+106 |  3 | John     |    2 |107 |  4 | Tom      |    1 |108 +----+----------+------+109 2 rows in set (0.00 sec)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.15 sec)117 Rows matched: 1  Changed: 1  Warnings: 0118 119 mysql> select * from users1;120 +----+----------+------+121 | id | username | pid  |122 +----+----------+------+123 |  3 | wuxie    |    2 |124 |  4 | Tom      |    1 |125 +----+----------+------+126 2 rows in set (0.00 sec)127 128 mysql> update users1 set username = ‘John‘, pid = 3 where id = 1;129 Query OK, 0 rows affected (0.00 sec)130 Rows matched: 0  Changed: 0  Warnings: 0131 132 mysql> select * from users1;133 +----+----------+------+134 | id | username | pid  |135 +----+----------+------+136 |  3 | wuxie    |    2 |137 |  4 | Tom      |    1 |138 +----+----------+------+139 2 rows in set (0.00 sec)

 

mysql中外鍵的一些基本操作