MySQL官方示例数据库的使用

太过爱你忘了你带给我的痛 2022-09-04 01:00 182阅读 0赞

官网介绍：[**https://dev.mysql.com/doc/index-other.html**][https_dev.mysql.com_doc_index-other.html]

mysql官网提供了如下示例数据库：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]

其中最常用的数据库为employee data和sakila。

下面以employee为例展示怎么导入这个数据库。

1、下载数据库脚本，下载地址：[**https://github.com/datacharmer/test\_db**][https_github.com_datacharmer_test_db]

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t97BkF1l-1628517346681)(https://tcs.teambition.net/storage/3128a18f1b5745a605752f923f3fabfb344c?Signature=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJBcHBJRCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9hcHBJZCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9vcmdhbml6YXRpb25JZCI6IjYwMWNkOWY1ZWI2ZGI4Yzk1MWFiNjJiZCIsImV4cCI6MTYyODUxOTg4MywiaWF0IjoxNjI4NTE2MjgzLCJyZXNvdXJjZSI6Ii9zdG9yYWdlLzMxMjhhMThmMWI1NzQ1YTYwNTc1MmY5MjNmM2ZhYmZiMzQ0YyJ9.VFWU9Y7EcPyfmWwUGrP6y6dyYebAkutoKFWi2I9isC8&download=image.png "")\]][img-t97BkF1l-1628517346681_https_tcs.teambition.net_storage_3128a18f1b5745a605752f923f3fabfb344c_Signature_eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJBcHBJRCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9hcHBJZCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9vcmdhbml6YXRpb25JZCI6IjYwMWNkOWY1ZWI2ZGI4Yzk1MWFiNjJiZCIsImV4cCI6MTYyODUxOTg4MywiaWF0IjoxNjI4NTE2MjgzLCJyZXNvdXJjZSI6Ii9zdG9yYWdlLzMxMjhhMThmMWI1NzQ1YTYwNTc1MmY5MjNmM2ZhYmZiMzQ0YyJ9.VFWU9Y7EcPyfmWwUGrP6y6dyYebAkutoKFWi2I9isC8_download_image.png]

2、将压缩包解压，这里是使用的docker，所以还需要复制到docker容器中：

# docker cp /mnt/d/360极速浏览器下载/test_db-master.zip mysql:/usr/local
    # docker exec -it mysql bash
    # cd /usr/local/
    # apt-get update
    # apt-get install unzip
    # unzip test_db-master.zip

3、导入数据库

# cd test_db-master
    # mysql < employees.sql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    INFO
    CREATING DATABASE STRUCTURE
    INFO
    storage engine: InnoDB
    INFO
    LOADING departments
    INFO
    LOADING employees
    INFO
    LOADING dept_emp
    INFO
    LOADING dept_manager
    INFO
    LOADING titles
    INFO
    LOADING salaries
    data_load_time_diff
    00:02:57

4、校验数据，可以使用test\_employees\_sha.sql  
和test\_employees\_md5.sql  
校验（这一步可以忽略）：

# time mysql -t < test_employees_sha.sql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +----------------------+
    | INFO                 |
    +----------------------+
    | TESTING INSTALLATION |
    +----------------------+
    +--------------+------------------+------------------------------------------+
    | table_name   | expected_records | expected_crc                             |
    +--------------+------------------+------------------------------------------+
    | departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
    | dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
    | dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
    | employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
    | salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
    | titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
    +--------------+------------------+------------------------------------------+
    +--------------+------------------+------------------------------------------+
    | table_name   | found_records    | found_crc                                |
    +--------------+------------------+------------------------------------------+
    | departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
    | dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
    | dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
    | employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
    | salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
    | titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
    +--------------+------------------+------------------------------------------+
    +--------------+---------------+-----------+
    | table_name   | records_match | crc_match |
    +--------------+---------------+-----------+
    | departments  | OK            | ok        |
    | dept_emp     | OK            | ok        |
    | dept_manager | OK            | ok        |
    | employees    | OK            | ok        |
    | salaries     | OK            | ok        |
    | titles       | OK            | ok        |
    +--------------+---------------+-----------+
    +------------------+
    | computation_time |
    +------------------+
    | 00:03:58         |
    +------------------+
    +---------+--------+
    | summary | result |
    +---------+--------+
    | CRC     | OK     |
    | count   | OK     |
    +---------+--------+
    
    real    3m58.321s
    user    0m0.026s
    sys     0m0.000s
    
    # time mysql -t < test_employees_md5.sql -uroot -proot
    mysql: [Warning] Using a password on the command line interface can be insecure.
    +----------------------+
    | INFO                 |
    +----------------------+
    | TESTING INSTALLATION |
    +----------------------+
    +--------------+------------------+----------------------------------+
    | table_name   | expected_records | expected_crc                     |
    +--------------+------------------+----------------------------------+
    | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
    | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
    | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
    | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
    | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
    | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
    +--------------+------------------+----------------------------------+
    +--------------+------------------+----------------------------------+
    | table_name   | found_records    | found_crc                        |
    +--------------+------------------+----------------------------------+
    | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
    | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
    | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
    | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
    | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
    | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
    +--------------+------------------+----------------------------------+
    +--------------+---------------+-----------+
    | table_name   | records_match | crc_match |
    +--------------+---------------+-----------+
    | departments  | OK            | ok        |
    | dept_emp     | OK            | ok        |
    | dept_manager | OK            | ok        |
    | employees    | OK            | ok        |
    | salaries     | OK            | ok        |
    | titles       | OK            | ok        |
    +--------------+---------------+-----------+
    +------------------+
    | computation_time |
    +------------------+
    | 00:03:26         |
    +------------------+
    +---------+--------+
    | summary | result |
    +---------+--------+
    | CRC     | OK     |
    | count   | OK     |
    +---------+--------+
    
    real    3m26.634s
    user    0m0.022s
    sys     0m0.000s

最后附上这些表的结构：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 1]

[https_dev.mysql.com_doc_index-other.html]: https://dev.mysql.com/doc/index-other.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70]: /images/20220829/7f2a8827a17945b384664f92e8e4628d.png
[https_github.com_datacharmer_test_db]: https://github.com/datacharmer/test_db
[img-t97BkF1l-1628517346681_https_tcs.teambition.net_storage_3128a18f1b5745a605752f923f3fabfb344c_Signature_eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJBcHBJRCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9hcHBJZCI6IjU5Mzc3MGZmODM5NjMyMDAyZTAzNThmMSIsIl9vcmdhbml6YXRpb25JZCI6IjYwMWNkOWY1ZWI2ZGI4Yzk1MWFiNjJiZCIsImV4cCI6MTYyODUxOTg4MywiaWF0IjoxNjI4NTE2MjgzLCJyZXNvdXJjZSI6Ii9zdG9yYWdlLzMxMjhhMThmMWI1NzQ1YTYwNTc1MmY5MjNmM2ZhYmZiMzQ0YyJ9.VFWU9Y7EcPyfmWwUGrP6y6dyYebAkutoKFWi2I9isC8_download_image.png]: /images/20220829/d854626b618a4b6ab67b49772b9d326f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMjI4MTI4NDk_size_16_color_FFFFFF_t_70 1]: /images/20220829/895cd0ba254743aaa062255328cc3e73.png