Baza testowa EMPLOYEES w MySQL

Dziś pokażę jak zainstalować bazę testową EMPLOYEES. Na początek musimy sobie ściągnąć ją ze strony:
https://github.com/datacharmer/test_db/archive/master.zip

Skompresowany plik test_db-master.zip (około 35MB) należy skopiować np do folderu domowego usera MYSQL.
Po skopiowaniu rozpakowujemy plik a następnie wchodzimy do utworzonego folderu test_db-master.
Jeśli na serwerze nie ma zainstalowanego programu UNZIP należy go zainstalować poleceniem:
yum install unzip.x86_64

Rozpakowujemy plik test_db-master.zip
[mysql@localhost ~]$ unzip test_db-master.zip

Archive:  test_db-master.zip
6c60bc6b4a49af6aff1b8baea6ed4e4621fee06e
   creating: test_db-master/
  inflating: test_db-master/Changelog
  inflating: test_db-master/README.md
  inflating: test_db-master/employees.sql
  inflating: test_db-master/employees_partitioned.sql
  inflating: test_db-master/employees_partitioned_5.1.sql
   creating: test_db-master/images/
  inflating: test_db-master/images/employees.gif
  inflating: test_db-master/images/employees.jpg
  inflating: test_db-master/images/employees.png
  inflating: test_db-master/load_departments.dump
  inflating: test_db-master/load_dept_emp.dump
  inflating: test_db-master/load_dept_manager.dump
  inflating: test_db-master/load_employees.dump
  inflating: test_db-master/load_salaries1.dump
  inflating: test_db-master/load_salaries2.dump
  inflating: test_db-master/load_salaries3.dump
  inflating: test_db-master/load_titles.dump
  inflating: test_db-master/objects.sql
   creating: test_db-master/sakila/
  inflating: test_db-master/sakila/README.md
  inflating: test_db-master/sakila/sakila-mv-data.sql
  inflating: test_db-master/sakila/sakila-mv-schema.sql
  inflating: test_db-master/show_elapsed.sql
  inflating: test_db-master/sql_test.sh
  inflating: test_db-master/test_employees_md5.sql
  inflating: test_db-master/test_employees_sha.sql

Uruchamiamy wczytanie plików do naszej bazy:

[mysql@localhost test_db-master]$ mysql -u mysql -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
data_load_time_diff
00:00:32

Baza EMPLOYEES została utworzona i napełniona danymi.

Teraz logujemy się do bazy na usera MYSQL i możemy podłączyć się do bazy:

[mysql@localhost test_db-master]$ mysql -u mysql -p

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

Copyright (c) 2000, 2016, 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> 

Sprawdzamy czy baza istnieje:
mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
| sys                |
| testbaza1          |
+--------------------+
6 rows in set (0.00 sec)

Podłączamy się
mysql> use employees

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Wyświetlamy listę tabel w bazie:
mysql> show tables;

+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

Sprawdzamy status tabel
mysql> show table status;

+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name                 | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| current_dept_emp     | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL              |     NULL | NULL           | VIEW    |
| departments          | InnoDB |      10 | Dynamic    |       9 |           1820 |       16384 |               0 |        16384 |         0 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:14 | NULL       | cp1250_general_ci |     NULL |                |         |
| dept_emp             | InnoDB |      10 | Dynamic    |  331570 |             36 |    12075008 |               0 |      5783552 |   4194304 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:21 | NULL       | cp1250_general_ci |     NULL |                |         |
| dept_emp_latest_date | NULL   |    NULL | NULL       |    NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL              |     NULL | NULL           | VIEW    |
| dept_manager         | InnoDB |      10 | Dynamic    |      24 |            682 |       16384 |               0 |        16384 |         0 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:21 | NULL       | cp1250_general_ci |     NULL |                |         |
| employees            | InnoDB |      10 | Dynamic    |  299379 |             50 |    15220736 |               0 |            0 |   4194304 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:17 | NULL       | cp1250_general_ci |     NULL |                |         |
| salaries             | InnoDB |      10 | Dynamic    | 2838426 |             35 |   100270080 |               0 |            0 |   4194304 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:46 | NULL       | cp1250_general_ci |     NULL |                |         |
| titles               | InnoDB |      10 | Dynamic    |  443021 |             46 |    20512768 |               0 |            0 |   4194304 |           NULL | 2017-04-13 14:48:14 | 2017-04-13 14:48:25 | NULL       | cp1250_general_ci |     NULL |                |         |
+----------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
8 rows in set (0.00 sec)

Zawartość kilku tabel (5 wierszy bo jest ich spora ilość):
mysql> select * from employees limit 5;

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)



mysql> select * from departments limit 5;

+---------+------------------+
| dept_no | dept_name        |
+---------+------------------+
| d009    | Customer Service |
| d005    | Development      |
| d002    | Finance          |
| d003    | Human Resources  |
| d001    | Marketing        |
+---------+------------------+
5 rows in set (0.00 sec)

mysql> select * from dept_manager limit 5;

+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
| 110022 | d001    | 1985-01-01 | 1991-10-01 |
| 110039 | d001    | 1991-10-01 | 9999-01-01 |
| 110085 | d002    | 1985-01-01 | 1989-12-17 |
| 110114 | d002    | 1989-12-17 | 9999-01-01 |
| 110183 | d003    | 1985-01-01 | 1992-03-21 |
+--------+---------+------------+------------+
5 rows in set (0.00 sec)

mysql> select * from salaries limit 5;

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

Jako że bazę odzyskiwaliśmy (tworzyliśmy) z usera MYSQL on ma tylko dostęp do danych. Jeśli byśmy chcieli aby inni też mieli dostęp do danych musimy przyznać userom odpowiednie uprawnienia.

create user employees@'%' identified by '123456';
grant all on employees.* to employees@'%' identified by '123456';
grant all on employees.* to employees@'localhost' identified by '123456';
flush privileges;

Możemy się wylogować:
mysql> exit

Ten wpis został opublikowany w kategorii MySQL (MariaDB) i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz