4.DQL语言 - 《TechNote》

1. sql规范
2.基础查询
- 2.1语法
- 2.2 示例
3.条件查询
5.常见函数
- 5.1 简介
- 5.1 案例
6.流程控制函数
7. 分组函数
8.分组查询
- 8.1 语法
9.连接查询
10 子查询
11 分页查询
12 联合查询

1. sql规范

1.不区分大小写，但建议关键字大写，表名、列名小写；
2.每条命令最好用分号结尾；
3.每条命令根据需要，可以进行缩进或换行；
4.注释
#
—
/注释内容/

2.基础查询

2.1语法

select 查询列表 from 表名;
特点：
1）查询列表可以是：表中的字段，常量值，表达式，函数
2）查询结果是一个虚拟的表格

2.2 示例

1）查询表中的单个字段

mysql> select last_name from employees;
+-------------+
| last_name   |
+-------------+
| K_ing       |
| Kochhar     |
| De Haan     |
| Hunold      |
| Ernst       |
| Austin      |
| Pataballa   |
+-------------+

2）查询表中的多个字段

mysql> select last_name,salary from employees;
+-------------+----------+
| last_name   | salary   |
+-------------+----------+
| K_ing       | 24000.00 |
| Kochhar     | 17000.00 |
| De Haan     | 17000.00 |
| Hunold      |  9000.00 |
| Ernst       |  6000.00 |
| Austin      |  4800.00 |
+-------------+----------+

3）查看表中的所有字段

select * from employees;

4）查询常量值

mysql> select 100;
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
mysql> select 'chenwendong';
+-------------+
| chenwendong |
+-------------+
| chenwendong |
+-------------+
1 row in set (0.00 sec)

5）查询表达式

mysql> select 100%98;
+--------+
| 100%98 |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

6）查询函数

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.33    |
+-----------+
1 row in set (0.00 sec)

7）起别名

方式1：使用as
mysql> select 100%98 as 结果;
+--------+
| 结果   |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)
mysql> select last_name as 姓,first_name as 名 from employees;
+-------------+-------------+
| 姓          | 名          |
+-------------+-------------+
| K_ing       | Steven      |
| Kochhar     | Neena       |
| De Haan     | Lex         |
| Hunold      | Alexander   |
| Ernst       | Bruce       |
| Austin      | David       |
+-------------+-------------+
方式2：使用空格
mysql> select 100%98 结果;
+--------+
| 结果   |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)
mysql> select last_name 姓,first_name 名 from employees;
+-------------+-------------+
| 姓          | 名          |
+-------------+-------------+
| K_ing       | Steven      |
| Kochhar     | Neena       |
| De Haan     | Lex         |
| Hunold      | Alexander   |
| Ernst       | Bruce       |
+-------------+-------------+
案例：查询salary，显示结果为out put
mysql> select salary   "out put" from employees;
+----------+
| out put  |
+----------+
| 24000.00 |
| 17000.00 |
| 17000.00 |
|  9000.00 |
|  6000.00 |
|  4800.00 |
|  4800.00 |
|  4200.00 |
| 12000.00 |
|  9000.00 |
+----------+
10 rows in set (0.00 sec)

8）去重
案例：查询员工表中涉及到的所有部门编号

mysql> select distinct  department_id from employees;
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.01 sec)

9）’+’号的作用
mysql中的+号仅仅能做为运算符

两个都是数值型，则做加法运算
mysql> select 100+90;
+--------+
| 100+90 |
+--------+
|    190 |
+--------+
1 row in set (0.00 sec)
其中一方为字符型，试图将字符型转换成数值型，如果转换成功，继续做加法运算，转换失败，字符型当0处理
mysql> select '100'+90;
+----------+
| '100'+90 |
+----------+
|      190 |
+----------+
1 row in set (0.00 sec)
mysql> select 'cwd' + 90;
+------------+
| 'cwd' + 90 |
+------------+
|         90 |
+------------+
1 row in set, 1 warning (0.00 sec)
只要其中一方为null，运算后均为null
mysql> select null + 90 ;
+-----------+
| null + 90 |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)
mysql> select 'cwd'+null;
+------------+
| 'cwd'+null |
+------------+
|       NULL |
+------------+
1 row in set, 1 warning (0.00 sec)
可以对列进行判断，如果为null，当0处理；
mysql> select concat(last_name,ifnull(commission_pct,0)) from employees limit 10 ;
+--------------------------------------------+
| concat(last_name,ifnull(commission_pct,0)) |
+--------------------------------------------+
| K_ing0.00                                  |
| Kochhar0.00                                |
| De Haan0.00                                |
| Hunold0.00                                 |
| Ernst0.00                                  |
| Austin0.00                                 |
| Pataballa0.00                              |
| Lorentz0.00                                |
| Greenberg0.00                              |
| Faviet0.00                                 |
+--------------------------------------------+
10 rows in set (0.00 sec)

案例：查询员工名和姓连接成一个字段，并显示为姓名

mysql> select concat(last_name,first_name) as 姓名 from employees;
+-----------------+
| 姓名            |
+-----------------+
| K_ingSteven     |
| KochharNeena    |
| De HaanLex      |
| HunoldAlexander |
| ErnstBruce      |
| AustinDavid     |
| PataballaValli  |
| LorentzDiana    |
| GreenbergNancy  |
| FavietDaniel    |
+-----------------+
10 rows in set (0.00 sec)

3.条件查询

3.1语法

select 查询列表 from 表名 where 筛选条件；
分类
1.按条件表达式筛选
条件运算符：> < = != <> >= <=

2.按逻辑表达式筛选
逻辑运算符： && || ! 或者 and or not
作用：用于连接条件表达式

3.模糊查询
like
between and
in
is null

3.2示例

1）查询员工工资>12K员工的信息；

mysql> select employee_id,first_name,last_name,salary from employees where salary > 12000 ;
+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|         100 | Steven     | K_ing     | 24000.00 |
|         101 | Neena      | Kochhar   | 17000.00 |
|         102 | Lex        | De Haan   | 17000.00 |
|         145 | John       | Russell   | 14000.00 |
|         146 | Karen      | Partners  | 13500.00 |
|         201 | Michael    | Hartstein | 13000.00 |
+-------------+------------+-----------+----------+
6 rows in set (0.00 sec)

2）查询部门编号不等于90号的员工名和部门编号

mysql> select last_name, department_id from employees where department_id <>90;
+-------------+---------------+
| last_name   | department_id |
+-------------+---------------+
| Hunold      |            60 |
| Ernst       |            60 |
| Austin      |            60 |
+-------------+---------------+

3）查询工资在10-20K之间的员工名、工资、以及奖金

mysql> select last_name,salary,ifnull(commission_pct,0) from employees where salary between 10000 and 20000;
+-----------+----------+--------------------------+
| last_name | salary   | ifnull(commission_pct,0) |
+-----------+----------+--------------------------+
| Kochhar   | 17000.00 |                     0.00 |
| De Haan   | 17000.00 |                     0.00 |
| Greenberg | 12000.00 |                     0.00 |
| Raphaely  | 11000.00 |                     0.00 |
| Russell   | 14000.00 |                     0.40 |
| Partners  | 13500.00 |                     0.30 |
| Errazuriz | 12000.00 |                     0.30 |
| Cambrault | 11000.00 |                     0.30 |
| Zlotkey   | 10500.00 |                     0.20 |
| Tucker    | 10000.00 |                     0.30 |
| K_ing     | 10000.00 |                     0.35 |
| Vishney   | 10500.00 |                     0.25 |
| Ozer      | 11500.00 |                     0.25 |
| Bloom     | 10000.00 |                     0.20 |
| Abel      | 11000.00 |                     0.30 |
| Hartstein | 13000.00 |                     0.00 |
| Baer      | 10000.00 |                     0.00 |
| Higgins   | 12000.00 |                     0.00 |
+-----------+----------+--------------------------+

4）查询部门编号不是在90-100之间，或者工资高于15K的员工信息

mysql> select last_name,salary,department_id from employees where department_id <90 or department_id >110 or salary >15000 limit 10 ;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| K_ing     | 24000.00 |            90 |
| Kochhar   | 17000.00 |            90 |
| De Haan   | 17000.00 |            90 |
| Hunold    |  9000.00 |            60 |
| Ernst     |  6000.00 |            60 |
| Austin    |  4800.00 |            60 |
| Pataballa |  4800.00 |            60 |
| Lorentz   |  4200.00 |            60 |
| Raphaely  | 11000.00 |            30 |
| Khoo      |  3100.00 |            30 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)
mysql> select last_name,salary,department_id from employees where not (department_id >=90 and department_id <=110) or salary >15000 limit 10;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| K_ing     | 24000.00 |            90 |
| Kochhar   | 17000.00 |            90 |
| De Haan   | 17000.00 |            90 |
| Hunold    |  9000.00 |            60 |
| Ernst     |  6000.00 |            60 |
| Austin    |  4800.00 |            60 |
| Pataballa |  4800.00 |            60 |
| Lorentz   |  4200.00 |            60 |
| Raphaely  | 11000.00 |            30 |
| Khoo      |  3100.00 |            30 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)

5）查询员工名中包含字符a的员工信息

mysql> select last_name from employees where last_name like '%a%' limit 10 ;
+-----------+
| last_name |
+-----------+
| Kochhar   |
| De Haan   |
| Austin    |
| Pataballa |
| Faviet    |
| Sciarra   |
| Urman     |
| Raphaely  |
| Baida     |
| Tobias    |
+-----------+
10 rows in set (0.00 sec)

6）查询员工名中第三个字符为n，第五个字符为l的员工名和工资

mysql> select last_name, salary from employees where last_name like '__n_l%';
+-----------+---------+
| last_name | salary  |
+-----------+---------+
| Hunold    | 9000.00 |
+-----------+---------+
1 row in set (0.00 sec)

7）查询员工名中第二个字符为_的员工名

mysql> select last_name from employees where last_name like '_\_%';
+-----------+
| last_name |
+-----------+
| K_ing     |
| K_ing     |
+-----------+
2 rows in set (0.00 sec)

8）查询员工编号在100-120之间的员工名和工资

mysql> select employee_id ,last_name,salary from employees where employee_id between 100 and 120 limit 10;
+-------------+-----------+----------+
| employee_id | last_name | salary   |
+-------------+-----------+----------+
|         100 | K_ing     | 24000.00 |
|         101 | Kochhar   | 17000.00 |
|         102 | De Haan   | 17000.00 |
|         103 | Hunold    |  9000.00 |
|         104 | Ernst     |  6000.00 |
|         105 | Austin    |  4800.00 |
|         106 | Pataballa |  4800.00 |
|         107 | Lorentz   |  4200.00 |
|         108 | Greenberg | 12000.00 |
|         109 | Faviet    |  9000.00 |
+-------------+-----------+----------+
10 rows in set (0.00 sec)

9）查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

mysql> select last_name,job_id from employees where job_id in ('IT_PROT','AD_VP','AD_PRES');
+-----------+---------+
| last_name | job_id  |
+-----------+---------+
| K_ing     | AD_PRES |
| Kochhar   | AD_VP   |
| De Haan   | AD_VP   |
+-----------+---------+
3 rows in set (0.00 sec)

10）查询没有奖金的员工名和奖金率

mysql> select last_name,commission_pct from employees where commission_pct is null;
+-------------+----------------+
| last_name   | commission_pct |
+-------------+----------------+
| K_ing       |           NULL |
| Kochhar     |           NULL |
| De Haan     |           NULL |
| Hunold      |           NULL |
| Ernst       |           NULL |
| Austin      |           NULL |
| Pataballa   |           NULL |
| Lorentz     |           NULL |
+-------------+----------------+
mysql> select last_name,commission_pct from employees where commission_pct <=> null;
+-------------+----------------+
| last_name   | commission_pct |
+-------------+----------------+
| K_ing       |           NULL |
| Kochhar     |           NULL |
| De Haan     |           NULL |
| Hunold      |           NULL |
| Ernst       |           NULL |
| Austin      |           NULL |
| Pataballa   |           NULL |
| Lorentz     |           NULL |
+-------------+----------------+
is null 只能判断null值
<=>        既可以判断null值，又可以判断普通的数值

3.3 基础查询练习

查询工资大于 12000 的员工姓名和工资 ```sql mysql> select salary,last_name from employees where salary > 12000; +—————+—————-+ | salary | last_name | +—————+—————-+ | 24000.00 | K_ing | | 17000.00 | Kochhar | | 17000.00 | De Haan | | 14000.00 | Russell | | 13500.00 | Partners | | 13000.00 | Hartstein | +—————+—————-+ 6 rows in set (0.00 sec)


2. 查询员工号为 176 的员工的姓名和部门号和年薪
```sql
mysql> select employee_id,last_name,department_id,salary*12*(1+ifnull(commission_pct,0)) 
from employees where empployee_id =176
+-------------+-----------+---------------+----------------------------------------+
| employee_id | last_name | department_id | salary*12*(1+ifnull(commission_pct,0)) |
+-------------+-----------+---------------+----------------------------------------+
|         176 | Taylor    |            80 |                              123840.00 |
+-------------+-----------+---------------+----------------------------------------+
1 row in set (0.00 sec)

选择工资不在 5000 到 12000 的员工的姓名和工资 ```sql mysql> select last_name,salary from employees
where salary not between 5000 and 12000 limit 10; +—————-+—————+ | last_name | salary | +—————-+—————+ | K_ing | 24000.00 | | Kochhar | 17000.00 | | De Haan | 17000.00 | | Austin | 4800.00 | | Pataballa | 4800.00 | | Lorentz | 4200.00 | | Khoo | 3100.00 | | Baida | 2900.00 | | Tobias | 2800.00 | | Himuro | 2600.00 | +—————-+—————+ 10 rows in set (0.00 sec)


4. 选择在 20 或 50 号部门工作的员工姓名和部门号
```sql
mysql> select last_name,department_id from employees 
where department_id in(20,50) limit 10;
+-------------+---------------+
| last_name   | department_id |
+-------------+---------------+
| Hartstein   |            20 |
| Fay         |            20 |
| Weiss       |            50 |
| Fripp       |            50 |
| Kaufling    |            50 |
| Vollman     |            50 |
| Mourgos     |            50 |
| Nayer       |            50 |
| Mikkilineni |            50 |
| Landry      |            50 |
+-------------+---------------+
10 rows in set (0.00 sec)

选择公司中没有管理者的员工姓名及 job_id ```sql mysql> select last_name,job_id from employees where manager_id <=> null; +—————-+————-+ | last_name | job_id | +—————-+————-+ | K_ing | AD_PRES | +—————-+————-+ 1 row in set (0.00 sec)


6. 选择公司中有奖金的员工姓名，工资
```sql
mysql> select last_name, salary,commission_pct from employees 
where commission_pct is not null limit 10;
+-----------+----------+----------------+
| last_name | salary   | commission_pct |
+-----------+----------+----------------+
| Russell   | 14000.00 |           0.40 |
| Partners  | 13500.00 |           0.30 |
| Errazuriz | 12000.00 |           0.30 |
| Cambrault | 11000.00 |           0.30 |
| Zlotkey   | 10500.00 |           0.20 |
| Tucker    | 10000.00 |           0.30 |
| Bernstein |  9500.00 |           0.25 |
| Hall      |  9000.00 |           0.25 |
| Olsen     |  8000.00 |           0.20 |
| Cambrault |  7500.00 |           0.20 |
+-----------+----------+----------------+
10 rows in set (0.00 sec)

选择员工姓名的第三个字母是 a 的员工姓名 ```sql mysql> select last_name from employees where last_name like ‘__a%’; +—————-+ | last_name | +—————-+ | Grant | | Grant | | Whalen | +—————-+ 3 rows in set (0.00 sec)


8. 选择姓名中有字母 a 和 e 的员工姓名
```sql
mysql> select last_name from employees 
where last_name like '%a%' and last_name like '%e%' limit 10;
+------------+
| last_name  |
+------------+
| De Haan    |
| Faviet     |
| Raphaely   |
| Colmenares |
| Nayer      |
| Markle     |
| Philtanker |
| Patel      |
| Davies     |
| Partners   |
+------------+
10 rows in set (0.00 sec)

显示出表 employees 表中 first_name 以 ‘e’结尾的员工信息 ```sql mysql> select first_name from employees where first_name like ‘%e’; +——————+ | first_name | +——————+ | Bruce | | Irene | | Mozhe | | Renske | | Nanette | | Janette | | Louise | | Danielle | | Vance | +——————+ 9 rows in set (0.00 sec)


10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
```sql
mysql> select last_name ,job_id from employees 
where department_id between 80 and 100 limit 10 ;
+-----------+--------+
| last_name | job_id |
+-----------+--------+
| Russell   | SA_MAN |
| Partners  | SA_MAN |
| Errazuriz | SA_MAN |
| Cambrault | SA_MAN |
| Zlotkey   | SA_MAN |
| Tucker    | SA_REP |
| Bernstein | SA_REP |
| Hall      | SA_REP |
| Olsen     | SA_REP |
| Cambrault | SA_REP |
+-----------+--------+
10 rows in set (0.00 sec)

显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位 ```sql mysql> select last_name,job_id from employees where manager_id in (100,101,110) limit 10 ; +—————-+————+ | last_name | job_id | +—————-+————+ | Kochhar | AD_VP | | De Haan | AD_VP | | Greenberg | FI_MGR | | Raphaely | PU_MAN | | Weiss | ST_MAN | | Fripp | ST_MAN | | Kaufling | ST_MAN | | Vollman | ST_MAN | | Mourgos | ST_MAN | | Russell | SA_MAN | +—————-+————+ 10 rows in set (0.00 sec)


<a name="qxmoy"></a>
## 4.排序查询
<a name="YNINC"></a>
### 4.1 语法
select 查询列表 from 表 order by 排序列表 asc | desc  （升序|降序），默认是升序<br />order by子句可以支持单个字段、多个字段、表达式、函数、别名<br />order by子句一般放在查询语句的最后面，limit字句除外
<a name="VD4N0"></a>
### 4.2 案例
1）查询员工信息，要求工资从高到低排序
```sql
mysql> select last_name,salary from employees 
order by salary desc limit 10;
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| K_ing     | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
| Russell   | 14000.00 |
| Partners  | 13500.00 |
| Hartstein | 13000.00 |
| Higgins   | 12000.00 |
| Greenberg | 12000.00 |
| Errazuriz | 12000.00 |
| Ozer      | 11500.00 |
+-----------+----------+
10 rows in set (0.00 sec)

2）查询部门编号》=90的员工信息，按入职的先后顺序进行排序

mysql> select last_name ,hiredate from employees 
where department_id >=90 order by hiredate;
+-----------+---------------------+
| last_name | hiredate            |
+-----------+---------------------+
| K_ing     | 1992-04-03 00:00:00 |
| Kochhar   | 1992-04-03 00:00:00 |
| De Haan   | 1992-04-03 00:00:00 |
| Greenberg | 1998-03-03 00:00:00 |
| Faviet    | 1998-03-03 00:00:00 |
| Chen      | 2000-09-09 00:00:00 |
| Sciarra   | 2000-09-09 00:00:00 |
| Urman     | 2000-09-09 00:00:00 |
| Popp      | 2000-09-09 00:00:00 |
| Higgins   | 2016-03-03 00:00:00 |
| Gietz     | 2016-03-03 00:00:00 |
+-----------+---------------------+
11 rows in set (0.00 sec)

3）按年薪的高低显示员工的信息和年薪

mysql> select last_name, salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees 
order by 年薪 desc limit 10;
+-----------+-----------+
| last_name | 年薪      |
+-----------+-----------+
| K_ing     | 288000.00 |
| Russell   | 235200.00 |
| Partners  | 210600.00 |
| Kochhar   | 204000.00 |
| De Haan   | 204000.00 |
| Errazuriz | 187200.00 |
| Ozer      | 172500.00 |
| Cambrault | 171600.00 |
| Abel      | 171600.00 |
| K_ing     | 162000.00 |
+-----------+-----------+
10 rows in set (0.00 sec)

4）按姓名的长度显示员工的姓名和工资

mysql> select last_name, salary*12*(1+ifnull(commission_pct,0)) from employees 
order by length(last_name) desc limit 10;
+-------------+----------------------------------------+
| last_name   | salary*12*(1+ifnull(commission_pct,0)) |
+-------------+----------------------------------------+
| Mikkilineni |                               32400.00 |
| Colmenares  |                               30000.00 |
| Philtanker  |                               26400.00 |
| Livingston  |                              120960.00 |
| Cambrault   |                              171600.00 |
| Bernstein   |                              142500.00 |
| Pataballa   |                               57600.00 |
| Cambrault   |                              108000.00 |
| Greenberg   |                              144000.00 |
| Errazuriz   |                              187200.00 |
+-------------+----------------------------------------+
10 rows in set (0.00 sec)

5）查询员工信息，要求先按工资排序，再按员工编号排序

mysql> select employee_id,last_name,salary from employees 
order by salary asc, employee_id desc  limit 10;
+-------------+------------+---------+
| employee_id | last_name  | salary  |
+-------------+------------+---------+
|         132 | Olson      | 2100.00 |
|         136 | Philtanker | 2200.00 |
|         128 | Markle     | 2200.00 |
|         135 | Gee        | 2400.00 |
|         127 | Landry     | 2400.00 |
|         191 | Perkins    | 2500.00 |
|         182 | Sullivan   | 2500.00 |
|         144 | Vargas     | 2500.00 |
|         140 | Patel      | 2500.00 |
|         131 | Marlow     | 2500.00 |
+-------------+------------+---------+
10 rows in set (0.01 sec)

5.常见函数

5.1 简介

类似于Java的方法，将一组逻辑语句封装在方法中，对外暴露方法名；
好处：
1.隐藏了实现细节
2.提高了代码的重用性

调用：slect 函数名(实参列表) 【from 表】

分类：
1.单行函数：concat、length、ifnull等
2.分组函数，做统计使用，又称为统计函数

5.1 案例

1）字符函数
1.1）length 获取参数值得字节个数；

mysql> select length('aaa');
+---------------+
| length('aaa') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
mysql> select length('陈文东');
+---------------------+
| length('陈文东')    |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

1.2）concat 拼接字符串

mysql> select concat(last_name,'_',first_name) as 姓名 from employees limit 10;
+------------------+
| 姓名             |
+------------------+
| K_ing_Steven     |
| Kochhar_Neena    |
| De Haan_Lex      |
| Hunold_Alexander |
| Ernst_Bruce      |
| Austin_David     |
| Pataballa_Valli  |
| Lorentz_Diana    |
| Greenberg_Nancy  |
| Faviet_Daniel    |
+------------------+
10 rows in set (0.00 sec)

1.3）upper、lower

mysql> select upper('cwd');
+--------------+
| upper('cwd') |
+--------------+
| CWD          |
+--------------+
1 row in set (0.00 sec)
mysql> select lower('CWD');
+--------------+
| lower('CWD') |
+--------------+
| cwd          |
+--------------+
1 row in set (0.00 sec)

1.4）把姓变大写，名变小写，然后拼接

mysql> select concat(upper(last_name),'_',lower(first_name)) from employees limit 10;
+------------------------------------------------+
| concat(upper(last_name),'_',lower(first_name)) |
+------------------------------------------------+
| K_ING_steven                                   |
| KOCHHAR_neena                                  |
| DE HAAN_lex                                    |
| HUNOLD_alexander                               |
| ERNST_bruce                                    |
| AUSTIN_david                                   |
| PATABALLA_valli                                |
| LORENTZ_diana                                  |
| GREENBERG_nancy                                |
| FAVIET_daniel                                  |
+------------------------------------------------+
10 rows in set (0.00 sec)

1.5）substr、substring截取字符串
截取从指定索引处后面的所有字符

mysql> select substr('大虎是一只猫',4);
+--------------------------------+
| substr('大虎是一只猫',4)       |
+--------------------------------+
| 一只猫                         |
+--------------------------------+
1 row in set (0.00 sec)

截取从指定索引处指定字符长度的字符

mysql> select substr('大虎是一只猫',4,2);
+----------------------------------+
| substr('大虎是一只猫',4,2)       |
+----------------------------------+
| 一只                             |
+----------------------------------+
1 row in set (0.00 sec)

1.6）姓名中首字符大写，其他字符小写然后用_拼接，显示出来

mysql> select concat(substr(last_name,1,1),'_',substr(last_name,2)) from employees limit 10;
+-------------------------------------------------------+
| concat(substr(last_name,1,1),'_',substr(last_name,2)) |
+-------------------------------------------------------+
| K__ing                                                |
| K_ochhar                                              |
| D_e Haan                                              |
| H_unold                                               |
| E_rnst                                                |
| A_ustin                                               |
| P_ataballa                                            |
| L_orentz                                              |
| G_reenberg                                            |
| F_aviet                                               |
+-------------------------------------------------------+
10 rows in set (0.00 sec)

1.7）instr返回子字符串在大字符串中第一次出现的索引，如果找不到返回0

mysql> select instr('大虎是我家的猫','猫');
+--------------------------------------+
| instr('大虎是我家的猫','猫')         |
+--------------------------------------+
|                                    7 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select instr('大虎是我家的猫','狗');
+--------------------------------------+
| instr('大虎是我家的猫','狗')         |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>

1.8）trim，去除字符串中指定的内容

mysql> select length(trim('   大虎    '));
+-------------------------------+
| length(trim('   大虎    '))   |
+-------------------------------+
|                             6 |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select (trim('a' from 'aaa大虎aaa'));
+---------------------------------+
| (trim('a' from 'aaa大虎aaa'))   |
+---------------------------------+
| 大虎                            |
+---------------------------------+
1 row in set (0.00 sec)

1.9）lpad用指定的字符实现左填充指定长度，rpad一样。

mysql> select lpad('大虎',10,'a');
+-----------------------+
| lpad('大虎',10,'a')   |
+-----------------------+
| aaaaaaaa大虎          |
+-----------------------+
1 row in set (0.00 sec)
mysql>

1.20）replace 替换

mysql> select replace('大虎是一只猫','猫','猪');
+-------------------------------------------+
| replace('大虎是一只猫','猫','猪')         |
+-------------------------------------------+
| 大虎是一只猪                              |
+-------------------------------------------+
1 row in set (0.00 sec)

1.21）round 四舍五入

mysql> select round(1.65);
+-------------+
| round(1.65) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

1.22）ceil向上取整

mysql> select ceil(1.02);
+------------+
| ceil(1.02) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

1.23）向下取整

mysql> select floor(1.02);
+-------------+
| floor(1.02) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

1.24）截断，取小数点后几位

mysql> select truncate(1.222222222,1);
+-------------------------+
| truncate(1.222222222,1) |
+-------------------------+
|                     1.2 |
+-------------------------+
1 row in set (0.00 sec)

1.25）取模

mysql> select (10%3);
+--------+
| (10%3) |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

1.26）now 返回当前系统日期+时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-06-09 05:08:12 |
+---------------------+
1 row in set (0.00 sec)
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-06-09 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:10:09  |
+-----------+
1 row in set (0.00 sec)
获取年
mysql> select YEAR(curdate());
+-----------------+
| YEAR(curdate()) |
+-----------------+
|            2021 |
+-----------------+
1 row in set (0.00 sec)
mysql> 
mysql> select last_name,YEAR(hiredate) 年 from employees limit 10 ;
+-----------+------+
| last_name | 年   |
+-----------+------+
| K_ing     | 1992 |
| Kochhar   | 1992 |
| De Haan   | 1992 |
| Hunold    | 1992 |
| Ernst     | 1992 |
| Austin    | 1998 |
| Pataballa | 1998 |
| Lorentz   | 1998 |
| Greenberg | 1998 |
| Faviet    | 1998 |
+-----------+------+
10 rows in set (0.00 sec)
mysql> select last_name, hiredate from employees where hiredate ='1992-4-3';
+-----------+---------------------+
| last_name | hiredate            |
+-----------+---------------------+
| K_ing     | 1992-04-03 00:00:00 |
| Kochhar   | 1992-04-03 00:00:00 |
| De Haan   | 1992-04-03 00:00:00 |
| Hunold    | 1992-04-03 00:00:00 |
| Ernst     | 1992-04-03 00:00:00 |
+-----------+---------------------+
5 rows in set (0.00 sec)

1.27）str_to_date将字符串通过指定的格式转换成日期

mysql> select last_name, hiredate from employees where hiredate = str_to_date('4-3,1992','%c-%d,%Y');
+-----------+---------------------+
| last_name | hiredate            |
+-----------+---------------------+
| K_ing     | 1992-04-03 00:00:00 |
| Kochhar   | 1992-04-03 00:00:00 |
| De Haan   | 1992-04-03 00:00:00 |
| Hunold    | 1992-04-03 00:00:00 |
| Ernst     | 1992-04-03 00:00:00 |
+-----------+---------------------+
5 rows in set (0.00 sec)

1.28）date_format 将日期转换为字符串

mysql> select date_format(now(),'%Y年%c月%d日');
+--------------------------------------+
| date_format(now(),'%Y年%c月%d日')    |
+--------------------------------------+
| 2021年6月08日                        |
+--------------------------------------+
1 row in set (0.00 sec)

6.流程控制函数

1）if函数

mysql> select if (10>5,'大','小');
+-----------------------+
| if (10>5,'大','小')   |
+-----------------------+
| 大                    |
+-----------------------+
1 row in set (0.00 sec)
mysql> select if (10<5,'大','小');
+-----------------------+
| if (10<5,'大','小')   |
+-----------------------+
| 小                    |
+-----------------------+
1 row in set (0.00 sec)
mysql> select last_name, commission_pct,if(commission_pct is null,'没奖金，呵呵','有奖金，哈哈') 备注 from employees;
+-------------+----------------+--------------------+
| last_name   | commission_pct | 备注               |
+-------------+----------------+--------------------+
| K_ing       |           NULL | 没奖金，呵呵       |
| Kochhar     |           NULL | 没奖金，呵呵       |
+-------------+----------------+--------------------+

2）case函数
1）语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

案例：查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

mysql> select salary as 原工资,department_id ,
    -> case department_id 
    -> when 30 then salary*1.1
    -> when 40 then salary*1.2
    -> when 50 then salary*1.3
    -> else salary 
    -> end as 新工资
    -> from employees;
+-----------+---------------+-----------+
| 原工资    | department_id | 新工资    |
+-----------+---------------+-----------+
|  24000.00 |            90 |  24000.00 |
|  17000.00 |            90 |  17000.00 |
|  17000.00 |            90 |  17000.00 |
+-----------+---------------+-----------+

2）
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end
案例：查询员工的工资情况：
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别

mysql> select salary, 
case 
when salary>20000 then 'A' 
when salary >15000 then 'B'
when salary >10000 then 'C' 
else 'D' 
end as 工资级别 
from employees;
+----------+--------------+
| salary   | 工资级别     |
+----------+--------------+
| 24000.00 | A            |
| 17000.00 | B            |
| 17000.00 | B            |
|  9000.00 | D            |
|  6000.00 | D            |

7. 分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和，avg平均值，max最大值，min最小值，count计算个数

1）求工资相关值

mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
|   691400.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)
mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
|     2100.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
|    24000.00 |
+-------------+
1 row in set (0.00 sec)

特点：
1）sum、avg一般用于处理数值型
2）max、min、count可以处理任何类型

计算日期查：

mysql> select datediff(now(),'1991-09-17');
+------------------------------+
| datediff(now(),'1991-09-17') |
+------------------------------+
|                        10857 |
+------------------------------+
1 row in set (0.00 sec)

8.分组查询

8.1 语法

select 分组函数,列
from 表
where 筛选条件
group by 分组的列表
order by 字句
注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段

1）查询每个工种的最高工资

mysql> select max(salary),job_id from employees group by job_id;
+-------------+------------+
| max(salary) | job_id     |
+-------------+------------+
|     8300.00 | AC_ACCOUNT |
|    12000.00 | AC_MGR     |
|     4400.00 | AD_ASST    |
|    24000.00 | AD_PRES    |
|    17000.00 | AD_VP      |
|     9000.00 | FI_ACCOUNT |
|    12000.00 | FI_MGR     |
|     6500.00 | HR_REP     |
|     9000.00 | IT_PROG    |
|    13000.00 | MK_MAN     |
|     6000.00 | MK_REP     |
|    10000.00 | PR_REP     |
|     3100.00 | PU_CLERK   |
|    11000.00 | PU_MAN     |
|    14000.00 | SA_MAN     |
|    11500.00 | SA_REP     |
|     4200.00 | SH_CLERK   |
|     3600.00 | ST_CLERK   |
|     8200.00 | ST_MAN     |
+-------------+------------+
19 rows in set (0.00 sec)

2）查询每个区域的部门个数

mysql> select count(*), location_id from departments group by location_id;
+----------+-------------+
| count(*) | location_id |
+----------+-------------+
|        1 |        1400 |
|        1 |        1500 |
|       21 |        1700 |
|        1 |        1800 |
|        1 |        2400 |
|        1 |        2500 |
|        1 |        2700 |
+----------+-------------+
7 rows in set (0.00 sec)

3）查询邮箱中包含e字符的，每个部门的平均工资

mysql> select department_id,avg(salary) from employees 
where email like '%e%' group by department_id;
+---------------+--------------+
| department_id | avg(salary)  |
+---------------+--------------+
|            10 |  4400.000000 |
|            20 | 13000.000000 |
|            30 |  6750.000000 |
|            50 |  3221.052632 |
|            60 |  5100.000000 |
|            70 | 10000.000000 |
|            80 |  9781.250000 |
|            90 | 17000.000000 |
|           100 |  9733.333333 |
|           110 |  8300.000000 |
+---------------+--------------+
10 rows in set (0.00 sec)

4）查询有奖金的每个领导手下员工的最高工资

mysql> select max(salary),manager_id from employees 
where commission_pct is not null group by manager_id;
+-------------+------------+
| max(salary) | manager_id |
+-------------+------------+
|    14000.00 |        100 |
|    10000.00 |        145 |
|    10000.00 |        146 |
|    10500.00 |        147 |
|    11500.00 |        148 |
|    11000.00 |        149 |
+-------------+------------+
6 rows in set (0.00 sec)

5）添加复杂的筛选条件
查询哪个部门的员工个数>2
思路：先查询每个部门的员工个数，根据查询结果再次筛选

mysql> select count(*),department_id  from employees 
group by department_id having count(*)>2;
+----------+---------------+
| count(*) | department_id |
+----------+---------------+
|        6 |            30 |
|       45 |            50 |
|        5 |            60 |
|       34 |            80 |
|        3 |            90 |
|        6 |           100 |
+----------+---------------+
6 rows in set (0.00 sec)

6）查询每个工种有奖金的员工的最高工资>12K的工种编号和最高工资

mysql> select job_id,max(salary) from employees 
where commission_pct is not null 
group by job_id 
having max(salary) >12000;
+--------+-------------+
| job_id | max(salary) |
+--------+-------------+
| SA_MAN |    14000.00 |
+--------+-------------+
1 row in set (0.00 sec)

7）查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

mysql> select manager_id,min(salary) from employees 
where manager_id >102 
group by manager_id 
having min(salary)>5000;
+------------+-------------+
| manager_id | min(salary) |
+------------+-------------+
|        108 |     6900.00 |
|        145 |     7000.00 |
|        146 |     7000.00 |
|        147 |     6200.00 |
|        148 |     6100.00 |
|        149 |     6200.00 |
|        201 |     6000.00 |
|        205 |     8300.00 |
+------------+-------------+
8 rows in set (0.00 sec)

8）查询每个部门每个工种的平均工资

mysql> select department_id, job_id,avg(salary) from employees 
group by department_id, job_id;
+---------------+------------+--------------+
| department_id | job_id     | avg(salary)  |
+---------------+------------+--------------+
|          NULL | SA_REP     |  7000.000000 |
|            10 | AD_ASST    |  4400.000000 |
|            20 | MK_MAN     | 13000.000000 |
|            20 | MK_REP     |  6000.000000 |
|            30 | PU_CLERK   |  2780.000000 |
|            30 | PU_MAN     | 11000.000000 |
|            40 | HR_REP     |  6500.000000 |
|            50 | SH_CLERK   |  3215.000000 |
|            50 | ST_CLERK   |  2785.000000 |
|            50 | ST_MAN     |  7280.000000 |
|            60 | IT_PROG    |  5760.000000 |
|            70 | PR_REP     | 10000.000000 |
|            80 | SA_MAN     | 12200.000000 |
|            80 | SA_REP     |  8396.551724 |
|            90 | AD_PRES    | 24000.000000 |
|            90 | AD_VP      | 17000.000000 |
|           100 | FI_ACCOUNT |  7920.000000 |
|           100 | FI_MGR     | 12000.000000 |
|           110 | AC_ACCOUNT |  8300.000000 |
|           110 | AC_MGR     | 12000.000000 |
+---------------+------------+--------------+
20 rows in set (0.00 sec)

9）查询每个部门每个工种的平均工资，并且按平均工资的高低显示

mysql> select department_id, job_id,avg(salary) from employees 
group by department_id, job_id 
order by avg(salary) desc;
+---------------+------------+--------------+
| department_id | job_id     | avg(salary)  |
+---------------+------------+--------------+
|            90 | AD_PRES    | 24000.000000 |
|            90 | AD_VP      | 17000.000000 |
|            20 | MK_MAN     | 13000.000000 |
|            80 | SA_MAN     | 12200.000000 |
|           110 | AC_MGR     | 12000.000000 |
|           100 | FI_MGR     | 12000.000000 |
|            30 | PU_MAN     | 11000.000000 |
|            70 | PR_REP     | 10000.000000 |
|            80 | SA_REP     |  8396.551724 |
|           110 | AC_ACCOUNT |  8300.000000 |
|           100 | FI_ACCOUNT |  7920.000000 |
|            50 | ST_MAN     |  7280.000000 |
|          NULL | SA_REP     |  7000.000000 |
|            40 | HR_REP     |  6500.000000 |
|            20 | MK_REP     |  6000.000000 |
|            60 | IT_PROG    |  5760.000000 |
|            10 | AD_ASST    |  4400.000000 |
|            50 | SH_CLERK   |  3215.000000 |
|            50 | ST_CLERK   |  2785.000000 |
|            30 | PU_CLERK   |  2780.000000 |
+---------------+------------+--------------+
20 rows in set (0.00 sec)

9.连接查询

9.1 简介

连接查询又称为多表查询
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
分类：
按年代分类：
sql92标准
sql99标准
按功能分类：
内连接：
等值连接
非等值连接
自连接

外连接：
左外
右外
全外

交叉连接：

9.2 案例

1）查询女神名和对应的男神名

mysql> select name, boyName from beauty ,boys where beauty.boyfriend_id = boys.id;
+------------+-----------+
| name       | boyName   |
+------------+-----------+
| Angelababy | 黄晓明    |
| 热巴       | 鹿晗      |
| 周芷若     | 张无忌    |
| 小昭       | 张无忌    |
| 王语嫣     | 段誉      |
| 赵敏       | 张无忌    |
+------------+-----------+
6 rows in set (0.01 sec)

2）查询员工名对应的部门名

mysql> select last_name, department_name from employees,departments 
where employees.department_id = departments.department_id limit 10 ;
+------------+-----------------+
| last_name  | department_name |
+------------+-----------------+
| Whalen     | Adm             |
| Hartstein  | Mar             |
| Fay        | Mar             |
| Raphaely   | Pur             |
| Khoo       | Pur             |
| Baida      | Pur             |
| Tobias     | Pur             |
| Himuro     | Pur             |
| Colmenares | Pur             |
| Mavris     | Hum             |
+------------+-----------------+
10 rows in set (0.00 sec)

3）查询员工名，工种号，工种名

mysql> select e.last_name,e.job_id,j.job_title   from employees  e ,jobs  j  
where e.job_id = j.job_id;
+-------------+------------+---------------------------------+
| last_name   | job_id     | job_title                       |
+-------------+------------+---------------------------------+
| Gietz       | AC_ACCOUNT | Public Accountant               |
| Higgins     | AC_MGR     | Accounting Manager              |
| Whalen      | AD_ASST    | Administration Assistant        |
| K_ing       | AD_PRES    | President                       |
| Kochhar     | AD_VP      | Administration Vice President   |
+-------------+------------+---------------------------------+

4）查询有奖金的员工名、部门名

mysql> select e.last_name , d.department_name,e.commission_pct 
from employees e ,departments d 
where e.department_id = d.department_id and e.commission_pct is not null limit 10;
+-----------+-----------------+----------------+
| last_name | department_name | commission_pct |
+-----------+-----------------+----------------+
| Russell   | Sal             |           0.40 |
| Partners  | Sal             |           0.30 |
| Errazuriz | Sal             |           0.30 |
| Cambrault | Sal             |           0.30 |
| Zlotkey   | Sal             |           0.20 |
| Tucker    | Sal             |           0.30 |
| Bernstein | Sal             |           0.25 |
| Hall      | Sal             |           0.25 |
| Olsen     | Sal             |           0.20 |
| Cambrault | Sal             |           0.20 |
+-----------+-----------------+----------------+
10 rows in set (0.00 sec)

5）查询城市名中第二个字符为o的部门名和城市名

mysql> select d.department_name ,l.city from departments d, locations l  
where d.location_id = l.location_id 
and d.department_name like '_o%';
+-----------------+---------+
| department_name | city    |
+-----------------+---------+
| Cor             | Seattle |
| Con             | Seattle |
| Con             | Seattle |
| Con             | Seattle |
| NOC             | Seattle |
| Gov             | Seattle |
+-----------------+---------+
6 rows in set (0.00 sec)

6）查询每个城市的部门个数

mysql> select count(*), city from departments d,locations l 
where d.location_id = l.location_id  group by city;
+----------+---------------------+
| count(*) | city                |
+----------+---------------------+
|        1 | London              |
|        1 | Munich              |
|        1 | Oxford              |
|       21 | Seattle             |
|        1 | South San Francisco |
|        1 | Southlake           |
|        1 | Toronto             |
+----------+---------------------+
7 rows in set (0.00 sec)

7）查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

mysql> select d.department_name,e.manager_id,min(e.salary) from departments d ,employees e   
where e.department_id = d .department_id and e.commission_pct is not null  
group by department_name,manager_id;
+-----------------+------------+---------------+
| department_name | manager_id | min(e.salary) |
+-----------------+------------+---------------+
| Sal             |        100 |      10500.00 |
| Sal             |        145 |       7000.00 |
| Sal             |        146 |       7000.00 |
| Sal             |        147 |       6200.00 |
| Sal             |        148 |       6100.00 |
| Sal             |        149 |       6200.00 |
+-----------------+------------+---------------+
6 rows in set (0.00 sec)

8）查询每个工种的工种名和员工的个数，并且按员工个数降序

mysql> select j.job_title ,count(*) from employees e ,jobs j 
where e.job_id = j.job_id 
group by job_title 
order by count(*) desc;
+---------------------------------+----------+
| job_title                       | count(*) |
+---------------------------------+----------+
| Sales Representative            |       30 |
| Shipping Clerk                  |       20 |
| Stock Clerk                     |       20 |
| Purchasing Clerk                |        5 |
| Stock Manager                   |        5 |
| Accountant                      |        5 |
| Programmer                      |        5 |
| Sales Manager                   |        5 |
+---------------------------------+----------+

9）查询员工名，部门名和所在的城市

mysql> select e.last_name,d.department_name,l.city from employees e, departments d ,locations l  
where e.department_id = d.department_id and d.location_id = l.location_id limit 10 ;
+------------+-----------------+---------+
| last_name  | department_name | city    |
+------------+-----------------+---------+
| Whalen     | Adm             | Seattle |
| Hartstein  | Mar             | Toronto |
| Fay        | Mar             | Toronto |
| Raphaely   | Pur             | Seattle |
| Khoo       | Pur             | Seattle |
| Baida      | Pur             | Seattle |
| Tobias     | Pur             | Seattle |
| Himuro     | Pur             | Seattle |
| Colmenares | Pur             | Seattle |
| Mavris     | Hum             | London  |
+------------+-----------------+---------+
10 rows in set (0.00 sec)

非等值连接：
1）查询员工的工资和工资级别

mysql> select salary , grade_level from employees e ,job_grades jg 
where salary between lowest_sal and highest_sal limit 10;
+----------+-------------+
| salary   | grade_level |
+----------+-------------+
| 24000.00 | E           |
| 17000.00 | E           |
| 17000.00 | E           |
|  9000.00 | C           |
|  6000.00 | C           |
|  4800.00 | B           |
|  4800.00 | B           |
|  4200.00 | B           |
| 12000.00 | D           |
|  9000.00 | C           |
+----------+-------------+
10 rows in set (0.00 sec)

自连接：
1）查询员工名和上级名

mysql> select e.employee_id,e.last_name, m.employee_id,m.last_name from employees e ,employees m  
where e.manager_id = m.employee_id limit 10 ;
+-------------+-----------+-------------+-----------+
| employee_id | last_name | employee_id | last_name |
+-------------+-----------+-------------+-----------+
|         101 | Kochhar   |         100 | K_ing     |
|         102 | De Haan   |         100 | K_ing     |
|         103 | Hunold    |         102 | De Haan   |
|         104 | Ernst     |         103 | Hunold    |
|         105 | Austin    |         103 | Hunold    |
|         106 | Pataballa |         103 | Hunold    |
|         107 | Lorentz   |         103 | Hunold    |
|         108 | Greenberg |         101 | Kochhar   |
|         109 | Faviet    |         108 | Greenberg |
|         110 | Chen      |         108 | Greenberg |
+-------------+-----------+-------------+-----------+
10 rows in set (0.00 sec)

9.3 sql99语法

语法：
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
where 筛选条件
group by 分组调教
having 筛选条件
order by 排序条件
分类：
内连接：inner
外连接：
    左外：left [outer]
  右外：right [outer]
  全外：full [outer]
交叉：cross

内连接

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
分类：
等值
非等值
自连接

1）查询员工名、部门名

mysql> select e.last_name,d.department_name from employees e 
inner join departments d
on e.department_id = d.department_id limit 10 ;
+------------+-----------------+
| last_name  | department_name |
+------------+-----------------+
| Whalen     | Adm             |
| Hartstein  | Mar             |
| Fay        | Mar             |
| Raphaely   | Pur             |
| Khoo       | Pur             |
| Baida      | Pur             |
| Tobias     | Pur             |
| Himuro     | Pur             |
| Colmenares | Pur             |
| Mavris     | Hum             |
+------------+-----------------+
10 rows in set (0.00 sec)

2）查询名字中包含e的员工名和工种名

mysql> select e.last_name, j.job_title from employees e 
inner join jobs j on e.job_id  = j.job_id 
where  e.last_name like '%e%' limit 10;
+------------+-------------------------------+
| last_name  | job_title                     |
+------------+-------------------------------+
| De Haan    | Administration Vice President |
| Ernst      | Programmer                    |
| Lorentz    | Programmer                    |
| Greenberg  | Finance Manager               |
| Faviet     | Accountant                    |
| Chen       | Accountant                    |
| Raphaely   | Purchasing Manager            |
| Colmenares | Purchasing Clerk              |
| Weiss      | Stock Manager                 |
| Nayer      | Stock Clerk                   |
+------------+-------------------------------+
10 rows in set (0.00 sec)

3）查询部门个数>3的城市名和部门个数

mysql> select l.city, count(*) from departments d 
inner join locations l 
on l.location_id = d.location_id  
group by l.city having count(*)>3;
+---------+----------+
| city    | count(*) |
+---------+----------+
| Seattle |       21 |
+---------+----------+
1 row in set (0.00 sec)

4）查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序；

mysql> select d.department_name,count(*) from employees e 
inner join departments d 
on e.department_id = d.department_id 
group by department_name having count(*) >3 
order by count(*) desc;
+-----------------+----------+
| department_name | count(*) |
+-----------------+----------+
| Shi             |       45 |
| Sal             |       34 |
| Fin             |        6 |
| Pur             |        6 |
| IT              |        5 |
+-----------------+----------+
5 rows in set (0.00 sec)

5）查询员工名、部门名、工种名并按部门降序

mysql> select e.last_name, d.department_name,j.job_title from employees e 
inner join departments d  on e.department_id = d.department_id  
inner join jobs  j on e.job_id = j.job_id 
order by department_name desc limit 10;
+-----------+-----------------+----------------+
| last_name | department_name | job_title      |
+-----------+-----------------+----------------+
| Geoni     | Shi             | Shipping Clerk |
| Patel     | Shi             | Stock Clerk    |
| Nayer     | Shi             | Stock Clerk    |
| Bull      | Shi             | Shipping Clerk |
| Davies    | Shi             | Stock Clerk    |
| Landry    | Shi             | Stock Clerk    |
| Cabrio    | Shi             | Shipping Clerk |
| Vargas    | Shi             | Stock Clerk    |
| Bissot    | Shi             | Stock Clerk    |
| Dilly     | Shi             | Shipping Clerk |
+-----------+-----------------+----------------+
10 rows in set (0.00 sec)

非等值连接
1）查询员工的工资级别

mysql> select salary,grade_level  
from employees e 
inner join job_grades g  
on e.salary between g.lowest_sal and g.highest_sal;
+----------+-------------+
| salary   | grade_level |
+----------+-------------+
| 24000.00 | E           |
| 17000.00 | E           |
| 17000.00 | E           |
|  9000.00 | C           |
|  6000.00 | C           |
|  4800.00 | B           |
|  4800.00 | B           |
|  4200.00 | B           |
| 12000.00 | D           |
|  9000.00 | C           |
|  8200.00 | C           |
|  7700.00 | C           |
|  7800.00 | C           |
|  6900.00 | C           |
| 11000.00 | D           |
+----------+-------------+

2）查询工资级别个数>2的个数，并且按工资级别降序

mysql> select g.grade_level,count(*) 
from employees e 
inner join job_grades g on e.salary
between g.lowest_sal and g.highest_sal 
group by g.grade_level
having count(*) > 20 order by g.grade_level desc;
+-------------+----------+
| grade_level | count(*) |
+-------------+----------+
| C           |       38 |
| B           |       26 |
| A           |       24 |
+-------------+----------+
3 rows in set (0.00 sec)

自连接
1）查询员工的名字，上级的名字

mysql> select e.employee_id,e.last_name, m.employee_id,m.last_name from employees e  
inner join employees m  
on e.manager_id = m.employee_id limit 10 ;
+-------------+-----------+-------------+-----------+
| employee_id | last_name | employee_id | last_name |
+-------------+-----------+-------------+-----------+
|         101 | Kochhar   |         100 | K_ing     |
|         102 | De Haan   |         100 | K_ing     |
|         103 | Hunold    |         102 | De Haan   |
|         104 | Ernst     |         103 | Hunold    |
|         105 | Austin    |         103 | Hunold    |
|         106 | Pataballa |         103 | Hunold    |
|         107 | Lorentz   |         103 | Hunold    |
|         108 | Greenberg |         101 | Kochhar   |
|         109 | Faviet    |         108 | Greenberg |
|         110 | Chen      |         108 | Greenberg |
+-------------+-----------+-------------+-----------+
10 rows in set (0.00 sec)

外连接
应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
1.外连接的查询结果为主表中的所有记录
如果从表中有和主表匹配的，则显示匹配的值
如果从表中没有和主表匹配的，则显示null
外连接结果=内连接结果+主表中有而从表中没有的记录

2.左外连接：left join左边的是主表
右外连接：right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果

mysql> select b.name from beauty b  left outer join boys bo  on b.boyfriend_id = bo.id where bo.id is null;
+-----------+
| name      |
+-----------+
| 柳岩      |
| 苍老师    |
| 周冬雨    |
| 岳灵珊    |
| 双儿      |
| 夏雪      |
+-----------+
6 rows in set (0.00 sec)

2）查询哪个部门没有员工

mysql> select d.* , e.employee_id from departments d 
left outer join employees e on  d.department_id = e.department_id 
where e.employee_id is null;
+---------------+-----------------+------------+-------------+-------------+
| department_id | department_name | manager_id | location_id | employee_id |
+---------------+-----------------+------------+-------------+-------------+
|           120 | Tre             |       NULL |        1700 |        NULL |
|           130 | Cor             |       NULL |        1700 |        NULL |
|           140 | Con             |       NULL |        1700 |        NULL |
|           150 | Sha             |       NULL |        1700 |        NULL |
|           160 | Ben             |       NULL |        1700 |        NULL |
|           170 | Man             |       NULL |        1700 |        NULL |
|           180 | Con             |       NULL |        1700 |        NULL |
|           190 | Con             |       NULL |        1700 |        NULL |
|           200 | Ope             |       NULL |        1700 |        NULL |
|           210 | IT              |       NULL |        1700 |        NULL |
|           220 | NOC             |       NULL |        1700 |        NULL |
|           230 | IT              |       NULL |        1700 |        NULL |
|           240 | Gov             |       NULL |        1700 |        NULL |
|           250 | Ret             |       NULL |        1700 |        NULL |
|           260 | Rec             |       NULL |        1700 |        NULL |
|           270 | Pay             |       NULL |        1700 |        NULL |
+---------------+-----------------+------------+-------------+-------------+
16 rows in set (0.00 sec)

10 子查询

含义：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句称为主查询或外查询
分类：
按子查询出现的位置：
select 后面：仅仅支持标量子查询
from 后面：支持表子查询
where 或having后面：支持标量子查询，列子查询，行子查询
exists后面：支持表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

标量子查询
1）谁的工资比Abel高

mysql> select last_name,salary from employees where salary > (select salary from employees where last_name = 'Abeel');
+-----------+----------+
| last_name | salary   |
+-----------+----------+
| K_ing     | 24000.00 |
| Kochhar   | 17000.00 |
| De Haan   | 17000.00 |
| Greenberg | 12000.00 |
| Russell   | 14000.00 |
| Partners  | 13500.00 |
| Errazuriz | 12000.00 |
| Ozer      | 11500.00 |
| Hartstein | 13000.00 |
| Higgins   | 12000.00 |
+-----------+----------+
10 rows in set (0.00 sec)

2）返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

mysql> select last_name,job_id,salary from employees where salary >(select salary from employees where employee_did =143) and job_id =(select job_id from employees where employee_id =141);
+-------------+----------+---------+
| last_name   | job_id   | salary  |
+-------------+----------+---------+
| Nayer       | ST_CLERK | 3200.00 |
| Mikkilineni | ST_CLERK | 2700.00 |
| Bissot      | ST_CLERK | 3300.00 |
| Atkinson    | ST_CLERK | 2800.00 |
| Mallin      | ST_CLERK | 3300.00 |
| Rogers      | ST_CLERK | 2900.00 |
| Ladwig      | ST_CLERK | 3600.00 |
| Stiles      | ST_CLERK | 3200.00 |
| Seo         | ST_CLERK | 2700.00 |
| Rajs        | ST_CLERK | 3500.00 |
| Davies      | ST_CLERK | 3100.00 |
+-------------+----------+---------+
11 rows in set (0.01 sec)

3）返回公司工资最少的员工的last_name，job_id，和salary

mysql> select last_name,job_id,salary from employees where salary =(select min(salary) from employees);
+-----------+----------+---------+
| last_name | job_id   | salary  |
+-----------+----------+---------+
| Olson     | ST_CLERK | 2100.00 |
+-----------+----------+---------+
1 row in set (0.00 sec)

4）查询最低工资大于50号部门最低工资的部门id和其最低工资

mysql> select min(salary),department_id from employees 
where salary>(select min(salary) from employees where department_id=50) 
group by department_id;
+-------------+---------------+
| min(salary) | department_id |
+-------------+---------------+
|     7000.00 |          NULL |
|     4400.00 |            10 |
|     6000.00 |            20 |
|     2500.00 |            30 |
|     6500.00 |            40 |
|     2200.00 |            50 |
|     4200.00 |            60 |
|    10000.00 |            70 |
|     6100.00 |            80 |
|    17000.00 |            90 |
|     6900.00 |           100 |
|     8300.00 |           110 |
+-------------+---------------+
12 rows in set (0.00 sec)

列子查询
1）查询location_id是1400或1700的部门中的所有员工姓名

mysql> select last_name from employees 
where department_id 
in (select distinct  department_id from departments whhere location_id in (1400,1700));
+------------+
| last_name  |
+------------+
| Hunold     |
| Ernst      |
| Austin     |
| Pataballa  |
| Lorentz    |
| Whalen     |
| Raphaely   |
| Khoo       |
| Baida      |
| Tobias     |
| Himuro     |
| Colmenares |
| K_ing      |
| Kochhar    |
| De Haan    |
| Greenberg  |
| Faviet     |
| Chen       |
| Sciarra    |
| Urman      |
| Popp       |
| Higgins    |
| Gietz      |
+------------+
23 rows in set (0.00 sec)

子查询案例
1）查询和Zlotkey相同部门的员工姓名和工资

mysql> select last_name,salary from employees 
where department_id=(select department_id from employees where last_name = 'Zlotkey');
+------------+----------+
| last_name  | salary   |
+------------+----------+
| Russell    | 14000.00 |
| Partners   | 13500.00 |
| Errazuriz  | 12000.00 |
| Cambrault  | 11000.00 |
| Zlotkey    | 10500.00 |
+------------+----------+

2）查询工资比公司平均工资高的员工的员工号，姓名和工资

mysql> select employee_id,last_name,salary from employees where salary>(select avg(salary) from employees);
+-------------+------------+----------+
| employee_id | last_name  | salary   |
+-------------+------------+----------+
|         100 | K_ing      | 24000.00 |
|         101 | Kochhar    | 17000.00 |
|         102 | De Haan    | 17000.00 |
|         103 | Hunold     |  9000.00 |
|         108 | Greenberg  | 12000.00 |
+-------------+------------+----------+

11 分页查询

应用场景：当要显示的数据，一页显示不完，需要分页提交sql请求。
语法：
select 查询列表
from 表
join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段
limit offset ,size
offset 要显示条目的其实索引
起始索引从0开始
SIZE要显示的条目个数

select 查询列表 from 表 limit (page -1)* size,size;

1）案例：查询前5条员工信息

mysql> select * from employees limit 0,5;
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email    | phone_number | job_id  | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
|         100 | Steven     | K_ing     | SKING    | 515.123.4567 | AD_PRES | 24000.00 |           NULL |       NULL |            90 | 1992-04-03 00:00:00 |
|         101 | Neena      | Kochhar   | NKOCHHAR | 515.123.4568 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         102 | Lex        | De Haan   | LDEHAAN  | 515.123.4569 | AD_VP   | 17000.00 |           NULL |        100 |            90 | 1992-04-03 00:00:00 |
|         103 | Alexander  | Hunold    | AHUNOLD  | 590.423.4567 | IT_PROG |  9000.00 |           NULL |        102 |            60 | 1992-04-03 00:00:00 |
|         104 | Bruce      | Ernst     | BERNST   | 590.423.4568 | IT_PROG |  6000.00 |           NULL |        103 |            60 | 1992-04-03 00:00:00 |
+-------------+------------+-----------+----------+--------------+---------+----------+----------------+------------+---------------+---------------------+
5 rows in set (0.00 sec)

2）查询第11-第25条

mysql> select * from employees limit 10,15;
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name  | email    | phone_number | job_id     | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
|         110 | John        | Chen       | JCHEN    | 515.124.4269 | FI_ACCOUNT |  8200.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         111 | Ismael      | Sciarra    | ISCIARRA | 515.124.4369 | FI_ACCOUNT |  7700.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         112 | Jose Manuel | Urman      | JMURMAN  | 515.124.4469 | FI_ACCOUNT |  7800.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         113 | Luis        | Popp       | LPOPP    | 515.124.4567 | FI_ACCOUNT |  6900.00 |           NULL |        108 |           100 | 2000-09-09 00:00:00 |
|         114 | Den         | Raphaely   | DRAPHEAL | 515.127.4561 | PU_MAN     | 11000.00 |           NULL |        100 |            30 | 2000-09-09 00:00:00 |
|         115 | Alexander   | Khoo       | AKHOO    | 515.127.4562 | PU_CLERK   |  3100.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         116 | Shelli      | Baida      | SBAIDA   | 515.127.4563 | PU_CLERK   |  2900.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         117 | Sigal       | Tobias     | STOBIAS  | 515.127.4564 | PU_CLERK   |  2800.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         118 | Guy         | Himuro     | GHIMURO  | 515.127.4565 | PU_CLERK   |  2600.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         119 | Karen       | Colmenares | KCOLMENA | 515.127.4566 | PU_CLERK   |  2500.00 |           NULL |        114 |            30 | 2000-09-09 00:00:00 |
|         120 | Matthew     | Weiss      | MWEISS   | 650.123.1234 | ST_MAN     |  8000.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
|         121 | Adam        | Fripp      | AFRIPP   | 650.123.2234 | ST_MAN     |  8200.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
|         122 | Payam       | Kaufling   | PKAUFLIN | 650.123.3234 | ST_MAN     |  7900.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
|         123 | Shanta      | Vollman    | SVOLLMAN | 650.123.4234 | ST_MAN     |  6500.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
|         124 | Kevin       | Mourgos    | KMOURGOS | 650.123.5234 | ST_MAN     |  5800.00 |           NULL |        100 |            50 | 2004-02-06 00:00:00 |
+-------------+-------------+------------+----------+--------------+------------+----------+----------------+------------+---------------+---------------------+
15 rows in set (0.00 sec)

3）有奖金的员工信息，并且工资较高的前10名显示出来

mysql> select * from employees where commission_pct is not null limit 10;
+-------------+-------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name  | last_name | email    | phone_number       | job_id | salary   | commission_pct | manager_id | department_id | hiredate            |
+-------------+-------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
|         145 | John        | Russell   | JRUSSEL  | 011.44.1344.429268 | SA_MAN | 14000.00 |           0.40 |        100 |            80 | 2002-12-23 00:00:00 |
|         146 | Karen       | Partners  | KPARTNER | 011.44.1344.467268 | SA_MAN | 13500.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         147 | Alberto     | Errazuriz | AERRAZUR | 011.44.1344.429278 | SA_MAN | 12000.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         148 | Gerald      | Cambrault | GCAMBRAU | 011.44.1344.619268 | SA_MAN | 11000.00 |           0.30 |        100 |            80 | 2002-12-23 00:00:00 |
|         149 | Eleni       | Zlotkey   | EZLOTKEY | 011.44.1344.429018 | SA_MAN | 10500.00 |           0.20 |        100 |            80 | 2002-12-23 00:00:00 |
|         150 | Peter       | Tucker    | PTUCKER  | 011.44.1344.129268 | SA_REP | 10000.00 |           0.30 |        145 |            80 | 2014-03-05 00:00:00 |
|         151 | David       | Bernstein | DBERNSTE | 011.44.1344.345268 | SA_REP |  9500.00 |           0.25 |        145 |            80 | 2014-03-05 00:00:00 |
|         152 | Peter       | Hall      | PHALL    | 011.44.1344.478968 | SA_REP |  9000.00 |           0.25 |        145 |            80 | 2014-03-05 00:00:00 |
|         153 | Christopher | Olsen     | COLSEN   | 011.44.1344.498718 | SA_REP |  8000.00 |           0.20 |        145 |            80 | 2014-03-05 00:00:00 |
|         154 | Nanette     | Cambrault | NCAMBRAU | 011.44.1344.987668 | SA_REP |  7500.00 |           0.20 |        145 |            80 | 2014-03-05 00:00:00 |
+-------------+-------------+-----------+----------+--------------------+--------+----------+----------------+------------+---------------+---------------------+
10 rows in set (0.00 sec)

12 联合查询

union 联合，将多条查询语句的结果合并成一个结果