mysql中外键的一些基本操作
时间：2021-07-01 10:21:17
帮助过：10人阅读
带外键的两个表，当插入记录的时候必须先在父表中插入记录，然后在子表中插入记录
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:
0
118
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:
0
131
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中外键的一些基本操作
标签：prim 引擎 delete select error utf8 0.00 color value
