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. 创建表