IT練習ノート

IT関連で調べたこと（実際は嵌ったこと）を書いています。

MySQLでの従業員サンプルデータベース導入

mysql

MySQLでの従業員サンプルデータベース導入でまとまった情報がなかったので作成してみました。

  • 情報源

https://dev.mysql.com/doc/employee/en/index.html

  • ファイル入手先

https://launchpad.net/test-db/

  • サンプルデータベースのスクリプトをダウンロードする。
[ec2-user@ip-XXX-XX-XX-XX ~]$ sudo mkdir download
[ec2-user@ip-XXX-XX-XX-XX ~]$ cd download/
[ec2-user@ip-XXX-XX-XX-XX  download]$ sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
--2014-03-02 06:19:57--  https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
launchpad.net (launchpad.net) をDNSに問いあわせています... 91.189.89.222, 91.189.89.223
launchpad.net (launchpad.net)|91.189.89.222|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 302 Moved Temporarily
場所: https://launchpadlibrarian.net/24493586/employees_db-full-1.0.6.tar.bz2 [続く]
--2014-03-02 06:19:59--  https://launchpadlibrarian.net/24493586/employees_db-full-1.0.6.tar.bz2
launchpadlibrarian.net (launchpadlibrarian.net) をDNSに問いあわせています... 91.189.89.229, 91.189.89.228
launchpadlibrarian.net (launchpadlibrarian.net)|91.189.89.229|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 26715056 (25M) [text/plain]
`employees_db-full-1.0.6.tar.bz2' に保存中

100%[====================================================================================================================>] 26,715,056  2.11MB/s 時間 26s    

2014-03-02 06:20:26 (985 KB/s) - `employees_db-full-1.0.6.tar.bz2' へ保存完了 [26715056/26715056]
  • 解凍する
[ec2-user@ip-XXX-XX-XX-XX  download]$ sudo tar jxfv  employees_db-full-1.0.6.tar.bz2 
employees_db/._load_departments.dump
employees_db/load_departments.dump
employees_db/load_dept_emp.dump
employees_db/._load_dept_manager.dump
employees_db/load_dept_manager.dump
employees_db/._load_employees.dump
employees_db/load_employees.dump
employees_db/._load_salaries.dump
employees_db/load_salaries.dump
employees_db/._load_titles.dump
employees_db/load_titles.dump
employees_db/._employees.sql
employees_db/employees.sql
employees_db/employees_partitioned.sql
employees_db/._employees_partitioned2.sql
employees_db/employees_partitioned2.sql
employees_db/employees_partitioned3.sql
employees_db/objects.sql
employees_db/test_employees_md5.sql
employees_db/test_employees_sha.sql
employees_db/Changelog
employees_db/._README
employees_db/README
  • ファイルの確認
[ec2-user@ip-XXX-XX-XX-XX  download]$ ls
employees_db  employees_db-full-1.0.6.tar.bz2
[ec2-user@ip-XXX-XX-XX-XX  download]$ cd employees_db
[ec2-user@ip-XXX-XX-XX-XX  employees_db]$ ls
Changelog      employees_partitioned.sql   load_departments.dump   load_employees.dump  objects.sql
README         employees_partitioned2.sql  load_dept_emp.dump      load_salaries.dump   test_employees_md5.sql
employees.sql  employees_partitioned3.sql  load_dept_manager.dump  load_titles.dump     test_employees_sha.sql

[ec2-user@ip-XXX-XX-XX-XX  employees_db]$ mysql -u root -p < employees.sql
Enter password: 
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
[ec2-user@ip-XXX-XX-XX-XX  employees_db]$
  • 従業員データベースのデータの確認
[ec2-user@ip-XXX-XX-XX-XX  employees_db]$ mysql -u root -p employees
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from employees
    -> ;
+----------+
| count(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.68 sec)

mysql>