今天用到的是官网提供的数据库employees
https://dev.mysql.com/doc/employee/en/sakila-structure.html
在这个数据库中有非常多的数据，我们的目标是找到离职和在职员工
下载unzip解压软件
[root@one ~]# yum -y install unzip
已加载插件：fastestmirror
Determining fastest mirrors
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
base | 3.6 kB 00:00:00
docker-ce-stable | 3.5 kB 00:00:00
extras | 2.9 kB 00:00:00
mysql-connectors-community | 2.6 kB 00:00:00
mysql-tools-community | 2.6 kB 00:00:00
mysql80-community | 2.6 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/3): mysql-connectors-community/x86_64/primary_db | 68 kB 00:00:00
(2/3): mysql-tools-community/x86_64/primary_db | 83 kB 00:00:01
(3/3): mysql80-community/x86_64/primary_db | 128 kB 00:00:01
软件包 unzip-6.0-21.el7.x86_64 已安装并且是最新版本
无须任何处理
新建文件夹mysqldemo，将master.zip文件放入其中，并解压
[root@one ~]# mkdir mysqldemo
[root@one ~]# ll
总用量 1924
-rw-------. 1 root root 1583 6月 15 15:42 anaconda-ks.cfg
-rw-r--r--. 1 root root 2014 9月 29 16:10 bank.sql
-rw-r--r--. 1 root root 2523 6月 16 2018 Centos-7.repo
drwxr-xr-x. 3 root root 34 9月 15 10:05 gitdemo
drwxr-xr-x. 2 root root 24 7月 22 14:10 javademo
drwxr-xr-x. 3 root root 20 9月 4 15:21 mavendemo
drwxr-xr-x. 13 root root 197 6月 26 17:18 miniconda3
-rw-r--r--. 1 root root 15077 9月 24 13:33 myschool _db_bakup.sql
drwxr-xr-x. 2 root root 24 11月 10 14:13 mysqldemo
-rw-r--r--. 1 root root 813953 6月 26 17:30 Northwind.MySQL5.sql
-rw-r--r--. 1 root root 0 9月 25 15:32 one.sql
-rw-r--r--. 1 root root 8460 6月 19 14:18 one.xsh
drwxr-xr-x. 2 root root 22 7月 22 14:07 pythondemo
-rw-r--r--. 1 root root 1095999 9月 24 16:56 retail_details.sql
-rw-r--r--. 1 root root 4847 9月 25 15:17 school.sql
-rw-r--r--. 1 root root 1819 7月 15 17:37 scott.sql
drwxr-xr-x. 6 root root 4096 9月 14 16:25 software
[root@one ~]# cd mysqldemo/
[root@one mysqldemo]# ll
总用量 35832
-rw-r--r--. 1 root root 36688498 11月 10 14:13 master.zip
[root@one mysqldemo]# unzip master.zip
Archive: master.zip
e5f310ac7786a2a181a7fc124973725d7aa4ce7c
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
inflating: test_db-master/test_versions.sh
[root@one mysqldemo]# ll
总用量 35836
-rw-r--r--. 1 root root 36688498 11月 10 14:13 master.zip
drwxr-xr-x. 4 root root 4096 9月 7 12:24 test_db-master
[root@one mysqldemo]# cd test_db-master/
[root@one test_db-master]# ll
总用量 168340
-rw-r--r--. 1 root root 964 9月 7 12:24 Changelog
-rw-r--r--. 1 root root 7948 9月 7 12:24 employees_partitioned_5.1.sql
-rw-r--r--. 1 root root 6276 9月 7 12:24 employees_partitioned.sql
-rw-r--r--. 1 root root 4193 9月 7 12:24 employees.sql
drwxr-xr-x. 2 root root 69 9月 7 12:24 images
-rw-r--r--. 1 root root 250 9月 7 12:24 load_departments.dump
-rw-r--r--. 1 root root 14159880 9月 7 12:24 load_dept_emp.dump
-rw-r--r--. 1 root root 1090 9月 7 12:24 load_dept_manager.dump
-rw-r--r--. 1 root root 17722832 9月 7 12:24 load_employees.dump
-rw-r--r--. 1 root root 39806034 9月 7 12:24 load_salaries1.dump
-rw-r--r--. 1 root root 39805981 9月 7 12:24 load_salaries2.dump
-rw-r--r--. 1 root root 39080916 9月 7 12:24 load_salaries3.dump
-rw-r--r--. 1 root root 21708736 9月 7 12:24 load_titles.dump
-rw-r--r--. 1 root root 4568 9月 7 12:24 objects.sql
-rw-r--r--. 1 root root 4325 9月 7 12:24 README.md
drwxr-xr-x. 2 root root 77 9月 7 12:24 sakila
-rw-r--r--. 1 root root 272 9月 7 12:24 show_elapsed.sql
-rwxr-xr-x. 1 root root 1800 9月 7 12:24 sql_test.sh
-rw-r--r--. 1 root root 4711 9月 7 12:24 test_employees_md5.sql
-rw-r--r--. 1 root root 4715 9月 7 12:24 test_employees_sha.sql
-rwxr-xr-x. 1 root root 2013 9月 7 12:24 test_versions.sh
准备导入数据库
[root@one test_db-master]# mysql -uroot -p123QWEasd! -t < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
由于没有开启mysql服务报错
[root@one ~]# systemctl start mysqld
开启后继续利用SQL文件导入数据库
[root@one test_db-master]# mysql -uroot -p123QWEasd! -t < employees.sql
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:00:53 |
+---------------------+
[root@one test_db-master]# ll
总用量 168340
-rw-r--r--. 1 root root 964 9月 7 12:24 Changelog
-rw-r--r--. 1 root root 7948 9月 7 12:24 employees_partitioned_5.1.sql
-rw-r--r--. 1 root root 6276 9月 7 12:24 employees_partitioned.sql
-rw-r--r--. 1 root root 4193 9月 7 12:24 employees.sql
drwxr-xr-x. 2 root root 69 9月 7 12:24 images
-rw-r--r--. 1 root root 250 9月 7 12:24 load_departments.dump
-rw-r--r--. 1 root root 14159880 9月 7 12:24 load_dept_emp.dump
-rw-r--r--. 1 root root 1090 9月 7 12:24 load_dept_manager.dump
-rw-r--r--. 1 root root 17722832 9月 7 12:24 load_employees.dump
-rw-r--r--. 1 root root 39806034 9月 7 12:24 load_salaries1.dump
-rw-r--r--. 1 root root 39805981 9月 7 12:24 load_salaries2.dump
-rw-r--r--. 1 root root 39080916 9月 7 12:24 load_salaries3.dump
-rw-r--r--. 1 root root 21708736 9月 7 12:24 load_titles.dump
-rw-r--r--. 1 root root 4568 9月 7 12:24 objects.sql
-rw-r--r--. 1 root root 4325 9月 7 12:24 README.md
drwxr-xr-x. 2 root root 77 9月 7 12:24 sakila
-rw-r--r--. 1 root root 272 9月 7 12:24 show_elapsed.sql
-rwxr-xr-x. 1 root root 1800 9月 7 12:24 sql_test.sh
-rw-r--r--. 1 root root 4711 9月 7 12:24 test_employees_md5.sql
-rw-r--r--. 1 root root 4715 9月 7 12:24 test_employees_sha.sql
-rwxr-xr-x. 1 root root 2013 9月 7 12:24 test_versions.sh
[root@one test_db-master]# mysql -uroot -p123QWEasd!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> show datables
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'datables' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| bank |
| employees |
| exam3 |
| information_schema |
| mysql |
| one |
| performance_schema |
| practice |
| retail1_db |
| retail_details |
| school |
| scott |
| stu |
| student |
| sys |
+--------------------+
15 rows in set (0.01 sec)
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
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)
mysql> desc current_dept_emp;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int | NO | | NULL | |
| dept_no | char(4) | NO | | NULL | |
| from_date | date | YES | | NULL | |
| to_date | date | YES | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
题目：
