7. Employees、临时表的创建 & 外键约束

一. Employees

1. Employees数据库介绍

• Employees 数据库是一个用于学习和测试的数据库，大约160MB，4百万条记录

2. Employees的安装

2.1 安装

[root@MyServer test_db]> mysql -uroot -p < employees.sql

2.2 验证

[root@vm-1 employees_db]# time mysql -uroot -p -t < test_employees_sha.sql
Enter password:
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| dept_emp     |           331603 | f16f6ce609d032d6b1b34748421e9195c5083da8 |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| employees    | OK            | ok        |
| departments  | OK            | ok        |
| dept_manager | OK            | ok        |
| dept_emp     | OK            | ok        |
| titles       | OK            | ok        |
| salaries     | OK            | ok        |
+--------------+---------------+-----------+

real    0m36.910s
user    0m0.010s
sys     0m0.029s

 

二. 表(TABLE)

1. 表的介绍

• 表是关系数据库的核心
• 表= 关系
• 表是记录的集合
• 二维表格模型易于人的理解
• MySQL默认存储引擎都是基于行(记录)存储
• 每行记录都是基于列进行组织的

 

2. 表是数据的集合

select * from table_name limit 1;

集合是无序的，上面的SQL语句的意思是从表(集合)中随机选出一条数据，结果是不确定的,不能简单的认为是取出第一条数据。

select * from table_name order by col_name limit 1;

只有通过order by排序之后取出的数据，才是确定的。

 

3. 创建表

3. 1 临时表

• 临时表的创建

[root@mysql.sock][test]> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.06 sec)

[root@mysql.sock][test]> create temporary table temp_a(a int);
Query OK, 0 rows affected (0.01 sec)

[root@mysql.sock][test]> show create table temp_a;
+--------+------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                         |
+--------+------------------------------------------------------------------------------------------------------+
| temp_a | CREATE TEMPORARY TABLE `temp_a` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@mysql.sock][test]> show processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist  -- 当前终端执行
1 row in set (0.00 sec)

[root@mysql.sock][test]> select * from temp_a;
+------+
| a    |
+------+
|  123 |
+------+
1 row in set (0.00 sec)

--
-- 临时表和普通表同名问题
--
[root@mysql.sock][test]> create table test_1 (a int);
Query OK, 0 rows affected (0.27 sec)

[root@mysql.sock][test]> insert into test_1 values(1);
Query OK, 1 row affected (0.06 sec)

[root@mysql.sock][test]> insert into test_1 values(2);
Query OK, 1 row affected (0.01 sec)

[root@mysql.sock][test]> create temporary table test_1 (a int);
Query OK, 0 rows affected (0.00 sec)

[root@mysql.sock][test]> insert into test_1 values(100);
Query OK, 1 row affected (0.00 sec)

[root@mysql.sock][test]> select * from test_1;
+------+
| a    |
+------+
|  100 |  -- 只能搜到临时表中的数据
+------+
1 row in set (0.00 sec)

create temporary table if not exists table_name (a int);  -- 使用if not exists进行判断
[root@mysql.sock][test]> create temporary table if not exists test_1 (a int);
Query OK, 0 rows affected, 1 warning (0.01 sec)

[root@mysql.sock][test]> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1050 | Table 'test_1' already exists |
+-------+------+-------------------------------+
1 row in set (0.00 sec)

临时表是 SESSION 级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的

当临时表和普通表同名时，当前用户只能看到同名的临时表

创建表时带上 if not exists 进行表的存在性检查；同时建议在临时表的表名前面加上统一的 prefix

 

• 临时表的作用
  • 临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
  • 不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。

 

• 临时表的存储引擎

[root@mysql.sock][test]> select version();
+-----------+
| version() |
+-----------+
| 5.7.9-log |
+-----------+
1 row in set (0.00 sec)

[root@mysql.sock][test]> show variables like "%default%tmp%";
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |  -- 默认是InnoDB
+----------------------------+--------+
1 row in set (0.00 sec)
-- 5.6 之前用的是MyISAM

 

•  临时表存储位置

[root@mysql.sock][test]> show variables like "tmpdir";
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| tmpdir        | /data/mysql_data/ |  -- 临时表存储的路径
+---------------+-------------------+
1 row in set (0.00 sec)

[root@mysql.sock][test]> system ls -l /data/mysql_data/
total 2187532
-rw-rw---- 1 mysql mysql         56 Mar 31 22:26  auto.cnf
drwxr-x--- 2 mysql mysql       4096 Apr  1 23:33  dbt3_s1
drwxr-x--- 2 mysql mysql        272 Apr 23 10:47  employees
-rw-rw---- 1 mysql mysql     128812 Apr 24 09:55  error.log
-rw-r----- 1 mysql mysql      17438 Apr 23 10:51  ib_buffer_pool
-rw-rw---- 1 mysql mysql   79691776 Apr 24 10:17  ibdata1
-rw-r----- 1 mysql mysql 1073741824 Apr 24 10:04  ib_logfile0
-rw-r----- 1 mysql mysql 1073741824 Apr 24 10:04  ib_logfile1
-rw-r----- 1 mysql mysql   12582912 Apr 24 10:17  ibtmp1  -- 临时表对应的数据
drwx------ 2 mysql mysql       4096 Apr  1 21:33  mysql
-rw-rw---- 1 mysql mysql        143 Mar 31 22:19  mysql-bin.000001
-rw-rw---- 1 mysql mysql        143 Mar 31 22:19  mysql-bin.000002
-rw-rw---- 1 mysql mysql        143 Mar 31 22:27  mysql-bin.000003
-rw-rw---- 1 mysql mysql        285 Mar 31 23:22  mysql-bin.000004
-rw-r----- 1 mysql mysql        177 Mar 31 23:30  mysql-bin.000005
-rw-r----- 1 mysql mysql         95 Mar 31 23:23  mysql-bin.index
-rw-rw---- 1 root  root           5 Apr 24 09:56  mysqld_safe.pid
-rw-r--r-- 1 root  root           5 Mar 31 23:24  mysql_upgrade_info
drwxr-x--- 2 mysql mysql       8192 Mar 31 23:24  performance_schema
-rw-r----- 1 mysql mysql       7450 Apr 24 09:56  slow.log
-rw-r----- 1 mysql mysql       8554 Apr 24 09:58 '#sql612_2_0.frm'  -- 临时表的表结构文件
-rw-r----- 1 mysql mysql       8554 Apr 24 10:04 '#sql612_2_1.frm'  -- 临时表的表结构文件
-rw-r----- 1 mysql mysql       8554 Apr 24 10:17 '#sql612_2_4.frm'  -- 临时表的表结构文件
drwxr-x--- 2 mysql mysql       8192 Mar 31 23:24  sys
drwx------ 2 mysql mysql       4096 Apr 24 10:04  test
-rw-r----- 1 mysql mysql          5 Apr 24 09:56  vm-1.pid

[root@mysql.sock][test]> show variables like "innodb_temp%";
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |  -- 表示临时表空间自动增长且没有上限，可以调整其大小，修改my.cnf，如：innodb_temp_data_file_path = "ibtmp1:12M:autoextend:max:500M"
+----------------------------+-----------------------+
1 row in set (0.00 sec)

-- 5.6.27 中没有innodb_temp_data_file_path 变量

MySQL5.7.9把临时表结构放在tmpdir，而数据表数据放在datadir

MySQL5.6.27把临时表结构和表数据都放在tmpdir

 

 4. 查看表结构

[root@mysql.sock][test]> show table status like "test_1"\G  -- 查看表结构的元数据信息
*************************** 1. row ***************************
           Name: test_1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-04-24 10:04:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

 

三. 外键约束

1. 外键的介绍

-- 摘自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. 外键操作

-- 表结构 摘自MySQL 官方文档
mysql> create tableparent (
    id int not null,
    primary key(id)
  ) engine=innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> create tablechild (
    id int, 
    parent_id INT,
    indexpar_ind (parent_id),
    foreign key(parent_id) 
    referencesparent(id)
    on delete cascade on update cascade -- 比官网例子增加update cascade 
  ) engine=innodb;
Query ok, 0 rows affected (0.15 sec)

mysql> insert into child values(1,1);  -- 我们插入一条数据，id=1，parent_id=1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent`(`id`) ON DELETE CASCADE) -- 直接报错了，因为此时parent表中没有任何记录

mysql> insert into parent values(1);  -- 现在parent中插入记录
Query OK, 1 row affected (0.03 sec)

mysql> insert into child values(1,1);  -- 然后在child中插入记录，且parent_id是在parent中存在的
Query OK, 1 row affected (0.02 sec)

mysql> insert into child values(1,2);  -- 插入parent_id=2的记录，报错。因为此时parent_id=2的记录不存在
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent`(`id`) ON DELETE CASCADE)

mysql> select * from child; 
+------+-----------+
| id   | parent_id |
+------+-----------+
| 1    | 1         |  -- parent_id = 1
+------+-----------+
1 row in set(0.00sec)

mysql> select * from parent;
+----+
| id |
+----+
| 1  |  -- 根据表结构的定义（Foreign_key），这个值就是child表中的id
+----+
1 row in set(0.00sec)

mysql> update parent set id=100 where id=1; 
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from parent;
+-----+
| id  |
+-----+
| 100 |  -- 已经设置成了100
+-----+
1 row in set(0.00sec)

mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
| 1    | 100       |  -- 自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新
+------+-----------+
1 row in set(0.00sec)

mysql> delete from parent where id=100;  -- 删除这条记录
Query OK, 1 row affected (0.03 sec)

mysql> select * from parent;  -- id=100的记录已经被删除了
Empty set(0.00sec)

mysql> select * from child;  -- id=1，parent_id=100的记录跟着被删除了。on delete cascade的作用
Empty set(0.00sec)

mysql> alter table child drop foreign key child_ibfk_1;  -- 删除 之前的外键
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table child add foreign key(parent_id) 
-> references parent(id) on update cascade on delete restrict;  -- 使用严格模式
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into parent values(50);
Query OK, 1 row affected (0.03 sec)

mysql> insert into child values(3,50); 
Query OK, 1 row affected (0.03 sec)

mysql> insert into child values(3,51);  -- 和之前一样会提示错误
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent`(`id`) ON UPDATE CASCADE)

mysql> delete from parent where id=50;  -- 删除失败了，因为是restrict模式
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY(`parent_id`) REFERENCES `parent`(`id`) ON UPDATE CASCADE)  -- 注意，delete 后面说明都不写表示no action == restrict

--外键约束，可以让数据进行一致性更新，但是会有一定的 性能损耗 ，线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。