MySQL基本用法

数据准备

数据
解压数据

root@ps-os:~# unzip master.zip 

加载数据

root@565583ffb44d:/usr/db# mysql -uroot -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:49

查看

root@565583ffb44d:/usr/db# mysql -u root -p employees -A
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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 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> select * from departments;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

mysql> select emp_no, dept_no from dept_manager;
+--------+---------+
| emp_no | dept_no |
+--------+---------+
| 110022 | d001    |
| 110039 | d001    |
| 110085 | d002    |
| 110114 | d002    |
| 110183 | d003    |
| 110228 | d003    |
| 110303 | d004    |
| 110344 | d004    |
| 110386 | d004    |
| 110420 | d004    |
| 110511 | d005    |
| 110567 | d005    |
| 110725 | d006    |
| 110765 | d006    |
| 110800 | d006    |
| 110854 | d006    |
| 111035 | d007    |
| 111133 | d007    |
| 111400 | d008    |
| 111534 | d008    |
| 111692 | d009    |
| 111784 | d009    |
| 111877 | d009    |
| 111939 | d009    |
+--------+---------+
24 rows in set (0.00 sec)

查询数据

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

mysql> select emp_no from employees where first_name='Georgi' and last_name='Facello';
+--------+
| emp_no |
+--------+
|  10001 |
|  55649 |
+--------+
2 rows in set (0.07 sec)

mysql> SELECT COUNT(*) FROM EMPLOYEES WHERE LAST_NAME IN ('Christ', 'Lamba', 'Baba');
ERROR 1146 (42S02): Table 'employees.EMPLOYEES' doesn't exist
mysql> SELECT COUNT(*) FROM employees WHERE LAST_NAME IN ('Christ', 'Lamba', 'Baba');
+----------+
| COUNT(*) |
+----------+
|      626 |
+----------+
1 row in set (0.06 sec)
mysql> select count(*) from employees where hire_date between '1986-12-01' and '1986-12-31';
+----------+
| count(*) |
+----------+
|     3081 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from employees where last_name regexp 'ba$';
+----------+
| count(*) |
+----------+
|     1008 |
+----------+
1 row in set (0.15 sec)

正则查询

mysql> select count(*) from employees where first_name RLIKE '^christ';
+----------+
| count(*) |
+----------+
|     1157 |
+----------+
1 row in set (0.12 sec)

mysql> select count(*) from employees where last_name not regexp '[aeiou]';
+----------+
| count(*) |
+----------+
|      148 |
+----------+
1 row in set (0.13 sec)

限定查询

mysql> select first_name, last_name from employees where hire_date < '1986-01-01' limit 10;
+------------+------------+
| first_name | last_name  |
+------------+------------+
| Bezalel    | Simmel     |
| Sumant     | Peac       |
| Eberhardt  | Terkki     |
| Otmar      | Herbst     |
| Florian    | Syrotiuk   |
| Tse        | Herber     |
| Udi        | Jansch     |
| Reuven     | Garigliano |
| Erez       | Ritzmann   |
| Premal     | Baek       |
+------------+------------+
10 rows in set (0.00 sec)

mysql> select emp_no, salary from salaries order by salary desc limit 5;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  43624 | 158220 |
|  43624 | 157821 |
| 254466 | 156286 |
|  47978 | 155709 |
| 253939 | 155513 |
+--------+--------+
5 rows in set (0.66 sec)
mysql> select emp_no, salary from salaries order by 2 desc limit 5;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  43624 | 158220 |
|  43624 | 157821 |
| 254466 | 156286 |
|  47978 | 155709 |
| 253939 | 155513 |
+--------+--------+
5 rows in set (0.64 sec)

mysql> select gender, count(*) as count from employees group by gender;
+--------+--------+
| gender | count  |
+--------+--------+
| M      | 179973 |
| F      | 120051 |
+--------+--------+
2 rows in set (0.13 sec)

mysql> select first_name, count(first_name) as count from employees group by first_name order by count desc limit 10;
+-------------+-------+
| first_name  | count |
+-------------+-------+
| Shahab      |   295 |
| Tetsushi    |   291 |
| Elgin       |   279 |
| Anyuan      |   278 |
| Huican      |   276 |
| Make        |   275 |
| Sreekrishna |   272 |
| Panayotis   |   272 |
| Hatem       |   271 |
| Vitali      |   270 |
+-------------+-------+
10 rows in set (0.22 sec)

分组查询

mysql> select year(from_date), sum(salary) as sum from salaries group by year(from_date) order by sum desc;
+-----------------+-------------+
| year(from_date) | sum         |
+-----------------+-------------+
|            2000 | 17535667603 |
|            2001 | 17507737308 |
|            1999 | 17360258862 |
|            1998 | 16220495471 |
|            1997 | 15056011781 |
|            1996 | 13888587737 |
|            1995 | 12638817464 |
|            1994 | 11429450113 |
|            2002 | 10243347616 |
|            1993 | 10215059054 |
|            1992 |  9027872610 |
|            1991 |  7798804412 |
|            1990 |  6626146391 |
|            1989 |  5454260439 |
|            1988 |  4295598688 |
|            1987 |  3156881054 |
|            1986 |  2052895941 |
|            1985 |   972864875 |
+-----------------+-------------+
18 rows in set (1.32 sec)

平均值

mysql> select emp_no, avg(salary) as avg from salaries group by emp_no order by avg desc limit 10;
+--------+-------------+
| emp_no | avg         |
+--------+-------------+
| 109334 | 141835.3333 |
| 205000 | 141064.6364 |
|  43624 | 138492.9444 |
| 493158 | 138312.8750 |
|  37558 | 138215.8571 |
| 276633 | 136711.7333 |
| 238117 | 136026.2000 |
|  46439 | 135747.7333 |
| 254466 | 135541.0625 |
| 253939 | 135042.2500 |
+--------+-------------+
10 rows in set (0.77 sec)

去重

mysql> select distinct title from titles;

+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
7 rows in set (8.85 sec)

HAVING 过滤

mysql> select emp_no, avg(salary) as avg from salaries group by emp_no having avg > 140000 order by avg desc;
+--------+-------------+
| emp_no | avg         |
+--------+-------------+
| 109334 | 141835.3333 |
| 205000 | 141064.6364 |
+--------+-------------+
2 rows in set (0.67 sec)