您的位置: 首页  >  文章  >  mysql数据导入到数据库

mysql数据导入到数据库

分类: 文章 • 2023-12-03 23:06:58

虽然MySQL安装包中不像SQL Server和Oracle那样提供示例数据库，但官方也提供示例数据库以供学习使用。

官方示例数据库 

下载地址

http://dev.mysql.com/doc/index-other.html

Documenation - Other Docs - Example Databases 示例库和文档放到了一起，在employee data 下选择 Download  跳转到下载界面。

mysql数据导入到数据库




导入employees数据库

上传文件到任意路径下

[sql] view plain copy
  1. [[email protected] home1]# ls  
  2. employees_db-full-1.0.6.tar.bz2  mysql         mysql-5.6.21.tar.gz  


解压文件，时间较长

[sql] view plain copy
  1. [[email protected] home1]# tar -xjf employees_db-full-1.0.6.tar.bz2  


解压后，可以看见有如下文件

[sql] view plain copy
  1. [[email protected] employees_db]# ll  
  2. total 164680  
  3. -rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog  
  4. -rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql  
  5. -rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql  
  6. -rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql  
  7. -rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql  
  8. -rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump  
  9. -rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump  
  10. -rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump  
  11. -rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump  
  12. -rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump  
  13. -rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump  
  14. -rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql  
  15. -rw-r--r--. 1 501 games      2211 Jul 30  2008 README  
  16. -rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql  
  17. -rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql  

导入文件employees.sql (需要制定用户名和密码，不然会报错+_+) 

[sql] view plain copy
  1. [[email protected] employees_db]# mysql -t < employees.sql  
  2. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)  


指定root用户(可以指定其他用户)

[sql] view plain copy
  1. [[email protected] employees_db]# mysql -t < employees.sql -uroot -proot123  
  2. Warning: Using a password on the command line interface can be insecure.  
  3. +-----------------------------+  
  4. | INFO                        |  
  5. +-----------------------------+  
  6. | CREATING DATABASE STRUCTURE |  
  7. +-----------------------------+  
  8. +------------------------+  
  9. | INFO                   |  
  10. +------------------------+  
  11. | storage engine: InnoDB |  
  12. +------------------------+  
  13. +---------------------+  
  14. | INFO                |  
  15. +---------------------+  
  16. | LOADING departments |  
  17. +---------------------+  
  18. +-------------------+  
  19. | INFO              |  
  20. +-------------------+  
  21. | LOADING employees |  
  22. +-------------------+  
  23. +------------------+  
  24. | INFO             |  
  25. +------------------+  
  26. | LOADING dept_emp |  
  27. +------------------+  
  28. +----------------------+  
  29. | INFO                 |  
  30. +----------------------+  
  31. | LOADING dept_manager |  
  32. +----------------------+  
  33. +----------------+  
  34. | INFO           |  
  35. +----------------+  
  36. | LOADING titles |  
  37. +----------------+  
  38. +------------------+  
  39. | INFO             |  
  40. +------------------+  
  41. | LOADING salaries |  
  42. +------------------+  


检查导入的数据是否成功(两种检测方法任选一种就可以)

使用test_employees_sha.sql文件校验数据

[sql] view plain copy
  1. [[email protected] employees_db]# mysql -t < test_employees_sha.sql -uroot -proot123  
  2. Warning: Using a password on the command line interface can be insecure.  
  3. +----------------------+  
  4. | INFO                 |  
  5. +----------------------+  
  6. | TESTING INSTALLATION |  
  7. +----------------------+  
  8. +--------------+------------------+------------------------------------------+  
  9. | table_name   | expected_records | expected_crc                             |  
  10. +--------------+------------------+------------------------------------------+  
  11. | employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |  
  12. | departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |  
  13. | dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |  
  14. | dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |  
  15. | titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |  
  16. | salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |  
  17. +--------------+------------------+------------------------------------------+  
  18. +--------------+------------------+------------------------------------------+  
  19. | table_name   | found_records    | found_crc                                |  
  20. +--------------+------------------+------------------------------------------+  
  21. | employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |  
  22. | departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |  
  23. | dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |  
  24. | dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |  
  25. | titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |  
  26. | salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |  
  27. +--------------+------------------+------------------------------------------+  
  28. +--------------+---------------+-----------+  
  29. | table_name   | records_match | crc_match |  
  30. +--------------+---------------+-----------+  
  31. | employees    | OK            | ok        |  
  32. | departments  | OK            | ok        |  
  33. | dept_manager | OK            | ok        |  
  34. | dept_emp     | OK            | ok        |  
  35. | titles       | OK            | ok        |  
  36. | salaries     | OK            | ok        |  
  37. +--------------+---------------+-----------+  


使用test_employees_md5.sql文件校验数据

[sql] view plain copy
  1. [[email protected] employees_db]# mysql -t < test_employees_md5.sql -uroot -proot123  
  2. Warning: Using a password on the command line interface can be insecure.  
  3. +----------------------+  
  4. | INFO                 |  
  5. +----------------------+  
  6. | TESTING INSTALLATION |  
  7. +----------------------+  
  8. +--------------+------------------+----------------------------------+  
  9. | table_name   | expected_records | expected_crc                     |  
  10. +--------------+------------------+----------------------------------+  
  11. | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |  
  12. | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |  
  13. | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |  
  14. | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |  
  15. | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |  
  16. | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |  
  17. +--------------+------------------+----------------------------------+  
  18. +--------------+------------------+----------------------------------+  
  19. | table_name   | found_records    | found_crc                        |  
  20. +--------------+------------------+----------------------------------+  
  21. | employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |  
  22. | departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |  
  23. | dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |  
  24. | dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |  
  25. | titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |  
  26. | salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |  
  27. +--------------+------------------+----------------------------------+  
  28. +--------------+---------------+-----------+  
  29. | table_name   | records_match | crc_match |  
  30. +--------------+---------------+-----------+  
  31. | employees    | OK            | ok        |  
  32. | departments  | OK            | ok        |  
  33. | dept_manager | OK            | ok        |  
  34. | dept_emp     | OK            | ok        |  
  35. | titles       | OK            | ok        |  
  36. | salaries     | OK            | ok        |  
  37. +--------------+---------------+-----------+  


以上示例数据库导入完成



employee组织结构图

mysql数据导入到数据库


Mysql导入SQL数据库提示Failed to open file 'load_departments.dump'

 

linux下：
  1. tar -xjf $HOME/Downloads/employees_db-full-1.0.4.tar.bz2  
  2. //解压缩，进入目录  
  3. cd employees_db/  
  4. //导入数据库root为用户名  
  5.  mysql -t -u root -p < employees.sql  
  6.    

    Windows下:
      在MySQL命令窗口中，首先切换到employees_db目录下，然后输入命令：
        F:\employees_db> mysql -t -u root -p < employees.sql 
    Mysql导入SQL数据库提示Failed to open file 'load_departments.dump'报错处理如下：

mysql数据导入到数据库

mysql数据导入到数据库
解决办法：

select 'LOADING employees' as 'INFO'; source D:\employees_db\load_employees.dump;
select 'LOADING departments' as 'INFO'; source D:\employees_db\load_departments.dump;
select 'LOADING dept_emp' as 'INFO'; source D:\employees_db\load_dept_emp.dump;

select 'LOADING titles' as 'INFO'; source D:\employees_db\load_titles.dump;
select 'LOADING salaries' as 'INFO'; source D:\employees_db\load_salaries.dump;
select 'LOADING dept_manager' as 'INFO'; source D:\employees_db\load_dept_manager.dump;

相关推荐