MySQL学习笔记（Day010：Employees/临时表的创建/外键约束）
@(MySQL学习)
[TOC]
一. Employees数据库安装
1. Employees数据库介绍
Employees数据库是一个用于学习和测试的数据库，大约160MB，4百万条记录
2. Employees的安装
2.1. 下载
根据官方文档的连接，我们可以找到下载该数据库的两种方式
- employees_db-full-1.0.6.tar.bz2
- github-test_db 使用
git clone进行仓库拉取
2.2. 解压和拉取
1 | [root@MyServer Downloads]> tar jxf employees_db-full-1.0.6.tar.bz2 |
解压出来的主要文件大小是不一样的，且根据网页上发布和更新的时间上看，github中的时间比较新，所以这里使用github中源作为的安装文件
2.3. 安装
1 | [root@MyServer test_db]> mysql -uroot -S /tmp/mysql.sock_57 -p < employees.sql |
2.4. 验证
1 | [root@MyServer test_db]> time mysql -uroot -S /tmp/mysql.sock_57 -p -t < test_employees_sha.sql |
至此，Employees测试数据库就安装完成了
二. 表(TABLE)
1. 表的介绍
- 表是关系数据库的核心
- 表 = 关系
- 表是记录的集合
- 二维表格模型易于人的理解
- MySQL默认存储引擎都是基于行(记录)存储
- 每行记录都是基于列进行组织的
2. 表是数据的集合
1 | select * from table_name limit 1; |
集合是无序的，上面的SQL语句的意思是 从表(集合)中随机选出一条数据，结果是不确定的, 不能简单的认为是取出第一条数据。
1 | select * from table_name order by col_name limit 1; |
只有通过order by排序之后取出的数据，才是确定的。
3. 创建表
3.1. 临时表
- 临时表的创建
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187--
-- mysql 5.7.9
--
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)
mysql> use burn_test;
Database changed
mysql> create temporary table temp_a(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table temp_a\G
*************************** 1. row ***************************
Table: temp_a
Create Table: CREATE TEMPORARY TABLE `temp_a` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 -- 使用的是innodb
1 row in set (0.00 sec)
--
-- mysql 5.6.27
--
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.27-log |
+------------+
1 row in set (0.00 sec)
mysql> create temporary table temp_a_56(a int);
Query OK, 0 rows affected (0.06 sec)
mysql> show create table temp_a_56\G
*************************** 1. row ***************************
Table: temp_a_56
Create Table: CREATE TEMPORARY TABLE `temp_a_56` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 -- 5.6 也是innodb
1 row in set (0.00 sec)
-----
--
-- 终端1 MySQL 5.7.9
--
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 10 -- 当前ID 是 10
User: root
Host: localhost
db: burn_test
Command: Query
Time: 0
State: starting
Info: show processlist -- 当前终端执行
*************************** 2. row ***************************
Id: 12
User: root
Host: localhost
db: NULL
Command: Sleep
Time: 328
State:
Info: NULL
*************************** 3. row ***************************
Id: 13
User: root
Host: localhost
db: burn_test
Command: Sleep
Time: 16
State:
Info: NULL
3 rows in set (0.00 sec)
mysql> insert into temp_a values(123);
Query OK, 1 row affected (0.00 sec)
mysql> select * from temp_a;
+------+
| a |
+------+
| 123 |
+------+
1 row in set (0.00 sec)
--
-- 终端2 MySQL 5.7.9
--
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 10
User: root
Host: localhost
db: burn_test
Command: Sleep
Time: 75
State:
Info: NULL
*************************** 2. row ***************************
Id: 12
User: root
Host: localhost
db: NULL
Command: Sleep
Time: 403
State:
Info: NULL
*************************** 3. row ***************************
Id: 13 -- 当前 ID 是 13
User: root
Host: localhost
db: burn_test
Command: Query
Time: 0
State: starting
Info: show processlist -- 当前终端执行
3 rows in set (0.00 sec)
mysql> use burn_test;
Database changed
mysql> show tables;
Empty set (0.00 sec) -- 从其他终端登录的用户(session)无法看到temp_a这个临时表
----
--
-- 临时表 和 普通表 同名问题
--
mysql> create table test_1 (a int); -- 创建一张普通的表叫做 test_1
Query OK, 0 rows affected (0.16 sec)
mysql> insert into test_1 values(23);
Query OK, 1 row affected (0.03 sec)
mysql> insert into test_1 values(24);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test_1;
+------+
| a |
+------+
| 23 | -- 可以看到插入的数据
| 24 |
+------+
2 rows in set (0.00 sec)
mysql> create temporary table test_1 (a int); -- 创建一种和test_1 同名的临时表
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_1 values(1000); -- 插入一个 不一样的值
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_1;
+------+
| a |
+------+
| 1000 | -- 只能搜索到 临时表中的数据
+------+
1 row in set (0.00 sec)
mysql> create temporary table if not exists table_name (a int); -- 使用if not exists进行判断
1：临时表是
SESSION级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的
2：当临时表和普通表同名时，当前用户只能看到同名的临时表
3：创建表时带上if not exists进行表的存在性检查；同时建议在临时表的表名前面加上统一的prefix
- 临时表的作用
- 临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
- 不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。
临时表的存储引擎
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB | -- 5.7.9 的临时表默认存储引擎就是 InnoDB
+----------------------------+--------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.27-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like "default%tmp%";
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB | -- 5.7.26 的临时表默认存储引擎也是 InnoDB
+----------------------------+--------+
1 row in set (0.00 sec)
-- 5.6 之前用的是MyISAM临时表存储位置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46#
# MySQL 5.7
#
mysql> system ls -l /tmp # 使用system 可以解析执行linux shell命令
total 20
drwxr-xr-x. 4 mysql mysql 4096 Dec 2 10:06 mysql_data
srwxrwxrwx. 1 mysql mysql 0 Dec 2 21:20 mysql.sock_56
srwxrwxrwx. 1 mysql mysql 0 Dec 2 20:51 mysql.sock_57
-rw-------. 1 mysql mysql 5 Dec 2 20:51 mysql.sock_57.lock
-rw-r-----. 1 mysql mysql 8554 Dec 2 22:04 #sqlf18_a_0.frm -- temp_1 的表结构
mysql> system ls -l /data/mysql_data/5.7/ | grep ib
-rw-r-----. 1 mysql mysql 879 Dec 2 20:47 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Dec 2 22:21 ibdata1
-rw-r-----. 1 mysql mysql 134217728 Dec 2 22:20 ib_logfile0
-rw-r-----. 1 mysql mysql 134217728 Dec 2 21:33 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Dec 2 22:33 ibtmp1 -- 这个是我们的表结构对应的数据
mysql> show variables like "innodb_temp%";
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
#-----
#
# MySQL 5.6
#
mysql> system ls -l /tmp
total 68
drwxr-xr-x. 4 mysql mysql 4096 Dec 2 10:06 mysql_data
srwxrwxrwx. 1 mysql mysql 0 Dec 2 21:20 mysql.sock_56
srwxrwxrwx. 1 mysql mysql 0 Dec 2 20:51 mysql.sock_57
-rw-------. 1 mysql mysql 5 Dec 2 20:51 mysql.sock_57.lock
-rw-rw----. 1 mysql mysql 8554 Dec 2 22:38 #sql13f3_7_0.frm -- 表结构
-rw-rw----. 1 mysql mysql 49152 Dec 2 22:38 #sql13f3_7_0.ibd -- 表数据
# 5.6.27 中没有 innodb_temp_data_file_path 变量
mysql> show variables like "innodb_temp%";
Empty set (0.00 sec)
mysql> show variables like "%innodb%temp%";
Empty set (0.00 sec)
MySQL5.7.9 把临时
表结构放在tmpdir，而数据表数据放在datadir
MySQL5.6.27 把临时表结构和表数据都放在tmpdir
4. 查看表结构
1 | mysql> show create table test_1\G -- 表结构 |
5. ALTER TABLE
ALTER TABLE语法官方文档
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test_1;
+------+------+
| a | b |
+------+------+
| 23 | NULL |
| 24 | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> alter table test_1 drop column b; -- 删除列 b
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from test_1;
+------+
| a |
+------+
| 23 |
| 24 |
+------+
2 rows in set (0.00 sec)
注意，当表记录很大的时候，
alter table会很耗时，影响性能
- ONLINE DDL
5.6以后对在线DDL操作进行了优化，以提高性能。官方文档
三. 外键约束
1. 外键的介绍
官方文档
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 摘自 MySQL官方文档
--
CREATE TABLE product ( -- 商品表
category INT NOT NULL, -- 商品种类
id INT NOT NULL, -- 商品id
price DECIMAL,
PRIMARY KEY(category, id) -- 主键是 (category, id)
) ENGINE=INNODB;
CREATE TABLE customer ( -- 客户表
id INT NOT NULL, -- 客户id
PRIMARY KEY (id) -- 主键是 id
) ENGINE=INNODB;
CREATE TABLE product_order ( -- 订单表
no INT NOT NULL AUTO_INCREMENT, -- number，自增长
product_category INT NOT NULL, -- 商品种类
product_id INT NOT NULL, -- 商品id
customer_id INT NOT NULL, -- 客户id
PRIMARY KEY(no), -- 主键是 no
INDEX (product_category, product_id), -- 对 (product_category, product_id) 做索引
INDEX (customer_id), -- 对 customer_id 做索引
FOREIGN KEY (product_category, product_id) -- 两个外键约束
REFERENCES product(category, id) -- 字段 product_category 引用自 product表的category
-- 字段 product_id 引用自 product表的id
ON UPDATE CASCADE ON DELETE RESTRICT, -- 级联跟新 和 严格模式删除
FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;
2. 外键操作
1 | -- |
外键约束，可以让数据进行一致性更新，但是会有一定的
性能损耗，线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。
