第2章数据查询语言DQL

zdb呀

已于 2024-05-22 13:39:44 修改

阅读量8k

点赞数 7

分类专栏： # MySQL 文章标签： mysql 数据库 database

于 2021-12-06 22:42:12 首次发布

本文链接：https://blog.csdn.net/qq_39236499/article/details/121758197

版权

MySQL 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

文章目录

第2章数据查询语言DDL

第2章数据查询语言DDL

DQL：数据查询语言：select、from、where

2.1 基础查询

（1）数据的导入

source 路径\文件名.sql

在这里插入图片描述

（2）查看表的结构

desc 表名;

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(4)      | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| SAL      | double(7,2) | YES  |     | NULL    |       |
| COMM     | double(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

（3）简单查询DQL

1、查询一个字段

select 字段名 from 表名;

2、查询多个字段：使用逗号隔开

select name1,name2 from table_name;

3、查询所有字段

## 方法一：把每个字段写上，逗号隔开
select a,b,c,d,e from tablename;

## 方法二：
select * from tablename;

方法二缺点：效率低，可读性差；实际开发中不建议使用，后面的学习为了方便用这个

（4）给查询的列起别名

## 方式一
select 字段名 as 字段别名 from 表名;

## 方式二：省略as
select 字段名 字段别名 from 表名;

例：要是起别名有空格，或者别名是中文

mysql> select deptno,dname 'dept name' from dept;
+--------+------------+
| deptno | dept name  |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.00 sec)

注意：在所有的数据库当中，字符串统一使用单引号括起来。

案例：计算员工年薪
1.查员工名字和工资：sal * 12

mysql> select ename, sal as monthsal, (sal*12) as yearsal from emp;

2.起中文别名加单引号

mysql> select ename, sal '月薪', (sal*12) '年薪' from emp;

2.2 where

1. 大于、小于、等于、不等于

## 等于
select name from table_name where name='张三'

## 不等于
select empno, ename, sal from emp where sal!=800;
select empno, ename, sal from emp where sal<>800;  //两句查询的结果都一样

## 大于
select empno, ename, sal from emp where sal>2000;

## 大于等于
select empno, ename, sal from emp where sal>=3000;

2. between … and

等同于 >= and <=，闭区间

案例：查询薪资在2450和3000之间的员工信息？包括2450和3000

mysql> select empno, ename, sal from emp where sal>=2450 and sal<=3000;
mysql> select empno, ename, sal from emp where sal between 2450 and 3000;  //两句功能一样

3. is null （is not null ）

案例：查询哪些员工的津贴/补助为null

mysql> select empno, ename, sal from emp where comm is null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7566 | JONES  | 2975.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
10 rows in set (0.00 sec)

mysql> select empno, ename, sal from emp where comm is not null;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7844 | TURNER | 1500.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)

注意：在数据库当中null不能使用等号进行衡量。需要使用is null因为数据库中的null代表什么也没有，它不是一个值。

4. and、or

案例：查询工作岗位是MANAGER**并且（或者）**工资大于2500的员工信息

mysql> select empno, ename, job, sal from emp where job='MANAGER' and sal>2500;
+-------+-------+---------+---------+
| empno | ename | job     | sal     |
+-------+-------+---------+---------+
|  7566 | JONES | MANAGER | 2975.00 |
|  7698 | BLAKE | MANAGER | 2850.00 |
+-------+-------+---------+---------+
2 rows in set (0.00 sec)

mysql> select empno, ename, job, sal from emp where job='MANAGER' or sal>2500;
+-------+-------+-----------+---------+
| empno | ename | job       | sal     |
+-------+-------+-----------+---------+
|  7566 | JONES | MANAGER   | 2975.00 |
|  7698 | BLAKE | MANAGER   | 2850.00 |
|  7782 | CLARK | MANAGER   | 2450.00 |
|  7788 | SCOTT | ANALYST   | 3000.00 |
|  7839 | KING  | PRESIDENT | 5000.00 |
|  7902 | FORD  | ANALYST   | 3000.00 |
+-------+-------+-----------+---------+
6 rows in set (0.00 sec)

注意：and优先级比or高。

select * from emp where sal > 2500 and deptno = 10 or deptno = 20;    //两语句功能一样
select * from emp where (sal > 2500 and deptno = 10) or deptno = 20;

select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);   //这句与上面不一样

5. in、not in

案例：查询工作岗位是MANAGER或SALESMAN的员工；查询薪资是800和5000的员工信息

mysql> select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');

mysql> select * from emp where sal in (800,5000);

案例：查询薪资不是800,5000,3000的员工信息

mysql> select * from emp where sal not in (800,5000,3000);

6. like

称为模糊查询，支持%或下划线匹配

%：匹配任意多个字符
_：任意一个字符

1. 找出名字中含有O的

mysql> select ename from emp where ename like '%O%';

2.找出名字以T结尾的

mysql> select ename from emp where ename like '%T';

3.找出名字以K开始的

mysql> select ename from emp where ename like 'K%';

4.找出第二个字每是A的

mysql> select ename from emp where ename like '_A%';

5.找出名字中有“ _”的，转义字符实现

mysql> select name from t_student where name like '%\_%'; // \转义字符。
		+----------+
		| name     |
		+----------+
		| jack_son |
		+----------+

2.3 order by

（1）升序：asc

查询所有员工薪资，排序。升序

select ename,sal from emp order by sal;     //两句语句一样，默认就为升序
select ename,sal from emp order by sal asc;

（2）降序：desc

select ename,sal from emp order by sal desc;

（3）多个字段的排序

案例：查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。
案例分析：分主次。sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。

mysql> select ename,sal from emp order by sal asc, ename asc;

（4）按字段位置排序

了解一下，不建议这样排序。因为不健壮，很容易列顺序发生改变。

select ename,sal from emp order by 2;   //按照第二列排序  索引从1开始
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

2.4 单行处理函数

即数据处理函数。当然还有多行处理函数
单行处理函数的特点：一个输入对应一个输出
多行处理函数的特点：多个输入对应一个输出

（1）lower()

select lower(ename) as ename from emp;

（2）upper()

select upper(ename) as ename from emp;

（3）substr()

注意：起始索引从1开始

mysql> select substr(ename,1,1) as ename from emp;
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+
14 rows in set (0.01 sec)

案例：挑选首字母为A的人名

mysql> select ename from emp where ename like 'A%';
mysql> select ename from emp where substr(ename,1,1)='A';   //两句功能一样
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

（4）length()

案例：获取名字的长度

mysql> select ename, length(ename) enameLength from emp;
+--------+-------------+
| ename  | enameLength |
+--------+-------------+
| SMITH  |           5 |
| ALLEN  |           5 |
| WARD   |           4 |
| JONES  |           5 |
| MILLER |           6 |
+--------+-------------+
14 rows in set (0.01 sec)

（5）concat()

案例：将empno字段和ename字段拼接起来

mysql> select concat(empno,ename) from emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7934MILLER          |
+---------------------+
14 rows in set (0.00 sec)

（6）trim()

mysql> select * from emp where ename='  KING';
Empty set (0.00 sec)

mysql> select * from emp where ename=trim('  KING');
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

（7）case…when…then…when…then…else…end

案例：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。

mysql> select ename,job,sal oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)

（8）round()

案例：保留整数

mysql> select round(123.456, 0) as result from emp limit 5;   //保留整数
+--------+
| result |
+--------+
|    123 |
|    123 |
|    123 |
|    123 |
|    123 |
+--------+
5 rows in set (0.00 sec)

mysql> select round(123.456, 1) as result from emp limit 5;  //四舍五入保留一位小数
+--------+
| result |
+--------+
|  123.5 |
|  123.5 |
|  123.5 |
|  123.5 |
|  123.5 |
+--------+
5 rows in set (0.00 sec)


mysql> select round(123.456, -2) as result from emp limit 5;  //保留到百分位
+--------+
| result |
+--------+
|    100 |
|    100 |
|    100 |
|    100 |
|    100 |
+--------+
5 rows in set (0.00 sec)

（9）rand()

案例：生成1以内的随机数

mysql> select rand() from emp limit 5;
+----------------------+
| rand()               |
+----------------------+
|   0.3314896806436495 |
|  0.41996393764388185 |
|  0.10535067702210571 |
|   0.2668616029125281 |
| 0.018256014229968205 |
+----------------------+
5 rows in set (0.00 sec)

案例：生成100以内的随机数，round和rand组合使用

mysql> select round(rand()*100,0) from emp limit 10;
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  22 |
|                   4 |
|                  55 |
|                  57 |
+---------------------+
10 rows in set (0.00 sec)

（10）ifnull()

ifnull是空处理函数。专门处理空的。
在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

注意：NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。

ifnull函数用法：ifnull(数据, NULL赋予其他值)

例：补助为NULL的时候，将补助当做0

mysql> select ename, (sal+ ifnull(comm,0))*12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)

2.5 多行处理函数

即分组函数

注意：分组函数在使用时必须先分组，然后才能用。如果你有对数据进行分组，整张表默认为一组。

1. 五个分组函数

count()：计数
sum()：求和
avg()：平均值
max()：最大值
min()：最小值

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.00 sec)

mysql> select count(sal) from emp;
+------------+
| count(sal) |
+------------+
|         14 |
+------------+
1 row in set (0.00 sec)

2. 使用时注意事项

第一点：分组函数自动忽略NULL，不需要提前对NULL进行处理。

例如：求sum时不会把NULL加进去。

第二点：分组函数中count(*)和count(具体字段)有什么区别？

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表当中的总行数。

第三点：分组函数不能够直接使用在where子句中
例：找出比最低工资高的员工信息

mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function

第四点：所有的分组函数可以组合起来一起用

mysql> select sum(sal),max(sal),min(sal),avg(sal),count(*) from emp;
+----------+----------+----------+-------------+----------+
| sum(sal) | max(sal) | min(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |  5000.00 |   800.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+
1 row in set (0.00 sec)

2.6 group by

什么是分组查询？
在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。
如：
计算每个部门的工资和
计算每个工作岗位的平均薪资
找出每个工作岗位的最高薪资

关键字的执行顺序

select
	...
from
	...
where
	...
group by
	...
order by
	...

结论：select语句中，如果有group by语句，select后面只能跟参加分组的字段，以及分组函数，其它的一律不能跟。

例：找出每个工作岗位的工资和

mysql> select job, sum(sal) from emp group by job order by sal;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
| ANALYST   |  6000.00 |
| MANAGER   |  8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)

例：找出每个部门的最高薪资

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

实例：找出“每个部门，不同工作岗位”的最高薪资

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

1. having

having可以对分完组之后的数据进一步过滤；having必须和group by联合使用。

having不能单独使用，having不能代替where
优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

实例：找出每个部门最高薪资，要求显示最高薪资大于3000的

mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

无法使用的where的情况：找出每个部门平均薪资，要求显示平均薪资高于2500的。

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

2. 总结

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

执行顺序？

from
where
group by
having
select
order by

实例：找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。

mysql>  select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;

mysql>  select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;

mysql> select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc;    //三句功能一样
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

2. distinct

注意：原表数据不会被修改，只是查询结果去重

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

这样编写是错误的，语法错误：select ename, distinct job from emp;
注意：distinct只能出现在所有字段的最前方

例：distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重

mysql> select job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| SALESMAN  |     30 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| SALESMAN  |     30 |
| CLERK     |     20 |
| CLERK     |     30 |
| ANALYST   |     20 |
| CLERK     |     10 |
+-----------+--------+
14 rows in set (0.00 sec)

mysql> select distinct job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+
9 rows in set (0.00 sec)

例：去除重复之后统计一下工作岗位的数量

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.01 sec)

2.7 连接查询

1. 简介

（1）什么是连接查询
从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字；这种跨表查询，多张表联合起来查询数据，被称为连接查询。

（2）连接查询的分类
根据语法年代的分类：
SQL92：1992年的时候出现的语法
SQL99：1999年的时候出现的语法
我们这里重点学习SQL99.(这个过程中简单演示一个SQL92的例子)

根据表连接的方式分类：
内连接：等值连接、非等值连接、自连接

外连接：左外连接（左连接）、右外连接（右连接）、全连接（不讲）

（3）当两张表进行连接查询时，没有任何条件的限制会发生什么现象？
案例：查询每个员工所在部门名称？

mysql> select ename,deptno from emp;
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
| MILLER |     10 |
+--------+--------+
14 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select ename, dname from emp,dept;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | ACCOUNTING |
| SMITH  | RESEARCH   |
| SMITH  | SALES      |
| SMITH  | OPERATIONS |
| ALLEN  | ACCOUNTING |
| ALLEN  | RESEARCH   |
| ALLEN  | SALES      |
| ALLEN  | OPERATIONS |
| WARD   | ACCOUNTING |
| WARD   | RESEARCH   |
| WARD   | SALES      |
| WARD   | OPERATIONS |
| JONES  | ACCOUNTING |
| JONES  | RESEARCH   |
| JONES  | SALES      |
| JONES  | OPERATIONS |
| MARTIN | ACCOUNTING |
| MARTIN | RESEARCH   |
| MARTIN | SALES      |
| MARTIN | OPERATIONS |
| BLAKE  | ACCOUNTING |
| BLAKE  | RESEARCH   |
| BLAKE  | SALES      |
| BLAKE  | OPERATIONS |
| CLARK  | ACCOUNTING |
| CLARK  | RESEARCH   |
| CLARK  | SALES      |
| CLARK  | OPERATIONS |
| SCOTT  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| SCOTT  | SALES      |
| SCOTT  | OPERATIONS |
| KING   | ACCOUNTING |
| KING   | RESEARCH   |
| KING   | SALES      |
| KING   | OPERATIONS |
| TURNER | ACCOUNTING |
| TURNER | RESEARCH   |
| TURNER | SALES      |
| TURNER | OPERATIONS |
| ADAMS  | ACCOUNTING |
| ADAMS  | RESEARCH   |
| ADAMS  | SALES      |
| ADAMS  | OPERATIONS |
| JAMES  | ACCOUNTING |
| JAMES  | RESEARCH   |
| JAMES  | SALES      |
| JAMES  | OPERATIONS |
| FORD   | ACCOUNTING |
| FORD   | RESEARCH   |
| FORD   | SALES      |
| FORD   | OPERATIONS |
| MILLER | ACCOUNTING |
| MILLER | RESEARCH   |
| MILLER | SALES      |
| MILLER | OPERATIONS |
+--------+------------+
56 rows in set (0.00 sec)

最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。

（4）怎么避免笛卡尔积现象
连接时加条件，满足这个条件的记录被筛选出来

select 
	emp.ename, dept.dname 
from 
	emp, dept
where
	emp.deptno = dept.deptno;

代码如下：

mysql> select emp.ename, dept.dname from emp, dept where emp.deptno = dept.deptno;
mysql> select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;  //两个结果一样

思考：最终查询的结果条数是14条，但是匹配的过程中，匹配的次数减少了吗？
还是56次，只不过进行了四选一。次数没有减少。

注意：表的连接次数越多效率越低，所以尽量避免表的连接次数。

2. 内连接：inner

（1）等值连接
案例：查询每个员工所在部门名称，显示员工名和部门名

SQL92语法：

select 
	e.ename,d.dname
from
	emp e, dept d
where
	e.deptno = d.deptno;

sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。

SQL99语法：
inner可以省略（带着inner可读性更好）

mysql> select
    -> e.ename,d.dname
    -> from
    -> emp e
    -> inner join            //inner可以省略
    -> dept d
    -> on
    -> e.deptno = d.deptno;  // 条件是等量关系，所以被称为等值连接。

sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

（2）非等值连接
案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

（3）自连接
实例：查询员工的上级领导，要求显示员工名和对应的领导名

mysql> select empno, ename, mgr from emp limit 5;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
+-------+--------+------+
5 rows in set (0.00 sec)

mysql> select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
+--------+--------+
| 员工名      | 领导名     |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
13 rows in set (0.00 sec)

3. 外连接

内连接的特点：完成能够匹配上这个条件的数据查询出来。

mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)

mysql> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。

（1）左外连接
outer是可以省略的

select 
	e.ename,d.dname
from
	dept d 
left outer join   //outer可以省略
	emp e
on
	e.deptno = d.deptno;

注意：

带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

思考：外连接的查询结果条数一定是 >= 内连接的查询结果条数的嘛？正确

实例：查询每个员工的上级领导，要求显示所有员工的名字和领导名

mysql> select a.ename, b.ename from emp a left join emp b on a.mgr=b.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| KING   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)

4. 多张表连接

select 
	...
from
	a
join
	b
on
	a和b的连接条件
join
	c
on
	a和c的连接条件
right join
	d
on
	a和d的连接条件

一条SQL中内连接和外连接可以混合。都可以出现！

5. 子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。

子查询都可以出现在哪里呢？

	select
		..(select).
	from
		..(select).
	where
		..(select).

（1）where子句中的子查询
案例：找出比最低工资高的员工姓名和工资

mysql> select ename,sal from emp where sal>min(sal);
ERROR 1111 (HY000): Invalid use of group function

错误原因：where子句中不能直接使用分组函数。

mysql> select ename,sal from emp where sal>(select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

（2）from子句中的子查询
注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的薪资等级。

mysql> select t.*, s.grade
    -> from (select job,avg(sal) as avgsal from emp group by job) t
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| ANALYST   | 3000.000000 |     4 |
| MANAGER   | 2758.333333 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

（3）select后面出现的子查询（这个内容不需要掌握，了解即可！！！）
实例：找出每个员工的部门名称，要求显示员工名，部门名

mysql> select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

6. union

案例：查询工作岗位是MANAGER和SALESMAN的员工

mysql> select ename, job from emp where job='MANAGER' or job='SALESMAN';
mysql> select ename,job from emp where job in ('MANAGER', 'SALESMAN');

mysql> select ename, job from emp where job='MANAGER'
    -> union
    -> select ename, job from emp where job='SALESMAN';   //三条语句结果一样
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.01 sec)

union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻…
但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

比如：

a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000

a 连接 b一个结果：10 * 10 --> 100次
a 连接 c一个结果：10 * 10 --> 100次
使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

注意事项：
错误：union在进行结果集合并的时候，要求两个结果集的列数相同。

select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';
ERROR 1222 (21000): The used SELECT statements have a different number of columns

MYSQL可以，oracle语法严格，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。

mysql> select ename,job from emp where job = 'MANAGER'
    -> union
    -> select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+

7. limit

作用：显示部分查询结果，通常使用在分页查询当中。

完整用法：limit startIndex, length

实例：按照薪资降序，取出排名在前5名的员工

mysql> select ename,sal from emp order by sal desc limit 5;
mysql> select ename,sal from emp order by sal desc limit 0,5;   //第一个参数为起始索引，默认从0开始  第二个参数为长度
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.01 sec)

注意：mysql当中limit在order by之后执行！

实例：取出工资排名在[3-5]名的员工

mysql> select ename,sal from emp order by sal desc limit 2,3;  //下标2开始，长度为3
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

分页

每页显示3条记录
第1页：limit 0,3
第2页：limit 3,3
第3页：limit 6,3
第4页：limit 9,3
第pageNo页：limit (pageNo - 1) * pageSize , pageSize

2.8 DQL语句的总结

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

zdb呀

关注

7
点赞
踩
25

收藏

觉得还不错? 一键收藏
打赏
0
评论
第2章数据查询语言DQL

MySQL学习第1章 MySQL概述1.1 前言1.2 MySQL安装1.3 常见的指令（1）MySQL的启动和关闭语句（2）MySQL的登录语句（3）MySQL的退出语句（4）查看MySQL的版本号（5）查看所有数据库（6）使用特定数据库（7）创建数据库（8）查看当前使用的数据库下所有表（9）查看当前使用的数据库1.4 SQL语句的五种分类第2章 DQL语言：数据查询语言2.1 基础查询（1）数据的导入（2）查看表中的数据（3）查看表的结构（4）简单查询DQL（5）给查询的列起别名2.2 条件查询：whe
复制链接

扫一扫