Linux-关于MySQL（三）

外键的使用

给外键字段赋值时 字段的值只能在参考表的字段值的范围内选择。
外键的作用：限制字段赋值的
表的存储引擎必须是 innodb
被参考的字段必须时主键
字段类型必须一致
被参考的表 必须事先创建***

创建外键命令
create table 库.表 （字段名列表，foreign key（表1字段名）references 表2（表2字段名） on update cascade on delete cascade）engine=innodb；

案例：

mysql>  create ta.yg_tab (yg_id int primary key auto_increment,name char(10))engine=innodb;
mysql> create table zjx.gz_tab( gz_id  int  ,  pay float(7,2) , foreign key(gz_iz_id)  references yg_tab(yg_id) on update cascade  on delete cascade )engine=innodb;
mysql> desc zjx.gz_tab;
mysql> alter table zjx.gz_tab drop foreign key gz_tab_ibfk_1;
mysql> alter table zjx.gz_tab add foreign key(gz_id) references yg_tab(yg_id) on update cascade on delete cascade;

添加数据

mysql> insert into zjx.yg_tab(name) values("tom"),("jerry"),("dc");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> desc zjx.yg_tab;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| yg_id | int(11)  | NO   | PRI | NULL    | auto_increment |
| name  | char(10) | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from zjx.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     2 | jerry |
|     3 | dc    |
+-------+-------+
3 rows in set (0.00 sec)
mysql> insert into zjx.gz_tab values(1,10000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into zjx.gz_tab values(2,10000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into zjx.gz_tab values(3,10000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into zjx.gz_tab values(4,10000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zjx`.`gz_tab`, CONSTRAINT `gz_tab_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg_tab` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> 

实现同步外键

mysql> update zjx.yg_tab set yg_id=8 where yg_id=2;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from zjx.yg_tab;
+-------+-------+
| yg_id | name  |
+-------+-------+
|     1 | tom   |
|     3 | dc    |
|     8 | jerry |
+-------+-------+
3 rows in set (0.00 sec)

mysql> select * from zjx.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 10000.00 |
|     3 | 10000.00 |
|     8 | 20000.00 |
|     3 | 30000.00 |
+-------+----------+
5 rows in set (0.00 sec)

实现同步删除外键

mysql> delete from zjx.yg_tab where yg_id=3;
Query OK, 1 row affected (0.00 sec)


mysql> select * from zjx.gz_tab;
+-------+----------+
| gz_id | pay      |
+-------+----------+
|     1 | 10000.00 |
|     8 | 10000.00 |
|     8 | 20000.00 |
+-------+----------+
3 rows i