02、数据查询（DQL）

最新推荐文章于 2024-05-22 14:11:15 发布

一直流浪~

最新推荐文章于 2024-05-22 14:11:15 发布

阅读量250

点赞数

分类专栏： MySQL学习笔记文章标签：字符串 java 数据库 mysql

本文链接：https://blog.csdn.net/baidu_41907361/article/details/106840962

版权

MySQL学习笔记专栏收录该内容

17 篇文章 0 订阅

订阅专栏

02、数据查询（DQL）

1、简单的查询

语法格式：

（1）查询单个字段：

select 字段名 from 表名;

（2）查询多个字段：

select 字段名1,字段名2,字段名3,…… from 表名;

（3）查询所有字段：

select * from 表名;

提示：

任何一条sql语句都要以";"结尾
sql语句不区分大小写

案例：查询员工年薪？（月薪*12）

字段可以参与数学运算

select ename,sal*12 from emp;

+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.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.00 sec)

给查询结果的列重命名

select ename,sal*12 as '年薪' from emp;

+--------+----------+
| ename  | 年薪     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.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 |
+--------+----------+

注意：

标准sql语句中要求字符串使用单引号括起来，虽然mysql数据库支持双引号，但是不建议使用，因为不通用。
as关键字可以省略

2、条件查询

语法格式：

select 字段名1,字段名2,字段名3,…… from 表名 where 条件;

执行顺序：

先from ，然后where，最后select

运算符	说明
=	等于
<> 或 !=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and …	两个值之间，等同于>= and <=
is null	为 null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in 不在这个范围中）
not	not 可以取非，主要用于is 或 in中。
like	like 模糊查询，支持%或下划线匹配

（1）查询工资等于5000的员工姓名？

mysql> select ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING  |
+-------+

（2）查询员工MARTIN的工资？

mysql> select ename,sal from emp where ename='MARTIN';
+--------+---------+
| ename  | sal     |
+--------+---------+
| MARTIN | 1250.00 |
+--------+---------+

（3）查询工资大于等于3000的员工？

mysql> select ename,sal from emp where sal >= 3000;
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

（4）查询工资不等于3000的员工？

mysql> select ename,sal from emp where sal <> 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+

（5）查询工资在1100到3000之间的员工？

mysql> select ename,sal from emp where sal >=1100 and sal <= 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

或者：
mysql> select ename,sal from emp where sal between 1100 and 3000;

（6）查询名字是首字母A到C开头的员工，其中不包括C开头，只包括名字为C的员工

mysql> select ename,sal from emp where ename between 'a' and 'c';
+-------+---------+
| ename | sal     |
+-------+---------+
| ALLEN | 1600.00 |
| BLAKE | 2850.00 |
| ADAMS | 1100.00 |
+-------+---------+

（7）查询那些人没有津贴？

mysql> select ename,sal,comm  from emp where comm is null or comm = 0;
+--------+---------+------+
| ename  | sal     | comm |
+--------+---------+------+
| SMITH  |  800.00 | NULL |
| JONES  | 2975.00 | NULL |
| BLAKE  | 2850.00 | NULL |
| CLARK  | 2450.00 | NULL |
| SCOTT  | 3000.00 | NULL |
| KING   | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS  | 1100.00 | NULL |
| JAMES  |  950.00 | NULL |
| FORD   | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+

（8）查询那些人有津贴

mysql> select ename,sal,comm  from emp where comm is not null and comm != 0;
+--------+---------+---------+
| ename  | sal     | comm    |
+--------+---------+---------+
| ALLEN  | 1600.00 |  300.00 |
| WARD   | 1250.00 |  500.00 |
| MARTIN | 1250.00 | 1400.00 |
+--------+---------+---------+

（9）查询工作岗位为SALESMAN和MANAGER的员工

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

（10）查询薪资大于1000并且部门编号为20或30的员工

mysql> select ename,sal,DEPTNO from emp where sal > 1000 and (deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename  | sal     | DEPTNO |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+

（11）查询部门编号为20或30的员工姓名

mysql> select ename,DEPTNO from emp where deptno in (20,30);
+--------+--------+
| ename  | DEPTNO |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| SCOTT  |     20 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAMES  |     30 |
| FORD   |     20 |
+--------+--------+

（13）查询名字为A开头的员工

mysql> select empno,ename from emp where ename like 'a%';

（14）查询名字中含有A的员工

mysql> select empno,ename from emp where ename like '%a%';
+-------+--------+
| empno | ename  |
+-------+--------+
|  7499 | ALLEN  |
|  7521 | WARD   |
|  7654 | MARTIN |
|  7698 | BLAKE  |
|  7782 | CLARK  |
|  7876 | ADAMS  |
|  7900 | JAMES  |
+-------+--------+

（16）查询名字第二位为a的员工

mysql> select empno,ename from emp where ename like '_a%';
+-------+--------+
| empno | ename  |
+-------+--------+
|  7521 | WARD   |
|  7654 | MARTIN |
|  7900 | JAMES  |
+-------+--------+

（15）查询名字中含有下划线的员工

mysql> select empno,ename from emp where ename like '%\_%';

注意：

字符串应该用单引号括起来
不等号：<> 或 !=
between and 是闭区间，包括边界。
between and 使用的时候，必须左小右大
between and 除了可以使用在数字，也可以使用在字符串，左闭右开
数据库中null代表为空，不是一个值，不能用等号衡量，只能使用is null或者is not null
当and和or一起使用的时候，and的优先级大于or的优先级
in等用于or
%代表0个或多个任意字符，_代表一个任意字符
\ _ 为转义，表示正常的下划线

3、排序查询

表达式	说明
asc	升序
desc	降序

按照工资升序排列员工表

mysql> select empno,sal from emp order by sal;
或者
mysql> select empno,sal from emp order by sal asc;
+-------+---------+
| empno | sal     |
+-------+---------+
|  7369 |  800.00 |
|  7900 |  950.00 |
|  7876 | 1100.00 |
|  7521 | 1250.00 |
|  7654 | 1250.00 |
|  7934 | 1300.00 |
|  7844 | 1500.00 |
|  7499 | 1600.00 |
|  7782 | 2450.00 |
|  7698 | 2850.00 |
|  7566 | 2975.00 |
|  7788 | 3000.00 |
|  7902 | 3000.00 |
|  7839 | 5000.00 |
+-------+---------+

按照工资升序排列员工表，当工资一样的时候按照名字的降序排列

mysql> select empno,ename,sal from emp order by sal asc ,ename  desc;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7900 | JAMES  |  950.00 |
|  7876 | ADAMS  | 1100.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7934 | MILLER | 1300.00 |
|  7844 | TURNER | 1500.00 |
|  7499 | ALLEN  | 1600.00 |
|  7782 | CLARK  | 2450.00 |
|  7698 | BLAKE  | 2850.00 |
|  7566 | JONES  | 2975.00 |
|  7788 | SCOTT  | 3000.00 |
|  7902 | FORD   | 3000.00 |
|  7839 | KING   | 5000.00 |
+-------+--------+---------+

找出工作岗位是SALESMAN的员工，并且按照薪资的降序排列

mysql> select empno,ename,job,sal from emp where job='SALESMAN' order by sal desc;
+-------+--------+----------+---------+
| empno | ename  | job      | sal     |
+-------+--------+----------+---------+
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
|  7521 | WARD   | SALESMAN | 1250.00 |
|  7654 | MARTIN | SALESMAN | 1250.00 |
+-------+--------+----------+---------+

注意：

默认为升序排列
靠前的字段起主导作用
order by 数字，表示使用第几列排序

4、分组函数

表达式	含义
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

注意：

所有的分组函数都是对”某一组“进行操作的。
分组函数还有一个名字叫多行处理函数
分组函数自动忽略空
分组函数不可以用在where子句当中，原因：因为group by 在where之后运行，而分组函数在group by 之后执行。
count (*)：不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(字段)：统计字段不为null的总数量

语句执行顺序

select 		5
..	
from 		1
..
where		2
..
group by	3
..
having		4
..
order by 	6
..

查询工资总和

mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+

查询最高工资

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+

查询最低工资

mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+

查询平均工资

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

查询总人数

mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+

查询拥有奖金的人数

mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+

查询公司应该分发的工资总和（分组函数自动忽略null，若没有忽略的话，得到的结果应该为null）

mysql> select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
|   2200.00 |
+-----------+

找出高出平均工资的员工（子查询）

mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+

5、单行处理函数

表达式	含义
ifnull(可能为null的数据，要替换的值)	空处理函数，若值为null，则用另一个值进行替换

计算每个员工的年薪（若数学表达式中有null，那么结果一定为null）

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 |
+--------+----------+

6、分组查询

表达式	含义
group by	按照某个字段或者某些字段进行分组
having	对分组之后的数据进行再次过滤

（1）找出每个岗位的最高薪资

mysql> select job,max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| CLERK     |  1300.00 |
| SALESMAN  |  1600.00 |
| MANAGER   |  2975.00 |
| ANALYST   |  3000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+

（2）每个工作岗位的平均工资

mysql>  select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+

（3）找出每个部门不同工作岗位的最高新资

mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno;
+--------+-----------+----------+
| 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 |
+--------+-----------+----------+

（4）找出每个部门的最高薪资，要求显示薪资大于2900的数据

第一步：找出每个部门的最高薪资
mysql> select deptno ,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     30 |  2850.00 |
|     10 |  5000.00 |
+--------+----------+
第二步：薪资大于2900的
mysql> select deptno ,max(sal) from emp group by deptno having max(sal)>2900; //效率比较低
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     10 |  5000.00 |
+--------+----------+

最佳写法：
mysql> select deptno ,max(sal) from emp where sal > 2900 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     20 |  3000.00 |
|     10 |  5000.00 |
+--------+----------+

（5）找出每个部门的平均薪资，要求显示薪资大于2900的数据

第一步：找出每个部门的平均薪资
mysql> select deptno ,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+
第二步：平均薪资大于2000的
mysql> select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     20 | 2175.000000 |
|     10 | 2916.666667 |
+--------+-------------+

注意：

分组函数一般都会和group by一起使用
分组函数会在group by之后执行。
当一条sql语句中没有froup by 的时候，整张表会自动成为一组
当sql语句中用group by的时候，select后面只能跟group by后面的列或者分组函数
在mysql中，select后面可以跟不是group by后面的列的列，但是没有意义，在Oracle数据库中直接报错

总结：

一个完整的DQL语句怎么写？

select 		5
..
from		1
..
where		2
..
group by	3
..
having		4
..
order by	6
..

7、结果去重

关于查询的结果如何去重？

在所有字段的前面加上关键字distinct

会把select所有字段作为一列进行去重

mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

查询每个部门都有那些工作岗位

mysql> select distinct deptno, job from emp order by deptno;
+--------+-----------+
| deptno | job       |
+--------+-----------+
|     10 | CLERK     |
|     10 | MANAGER   |
|     10 | PRESIDENT |
|     20 | ANALYST   |
|     20 | CLERK     |
|     20 | MANAGER   |
|     30 | CLERK     |
|     30 | MANAGER   |
|     30 | SALESMAN  |
+--------+-----------+

8、连接查询

8.1 什么是连接查询？

在实际开发中，大部分情况都不是在单张表中进行查询，而是在多张表联合查询出结果。

把一张表的结果作为另一张表的数据进行查询。

8.2 连接查询的分类

根据语法出现的年代来划分：

SQL92（一些老的DBA可能还在使用，DBA：DataBase Administrator，数据库管理员）
SQL99（比较新的语法）

根据表的连接方式来划分，包括：

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 左外连接（左连接）
- 右外连接（右连接）
全连接（基本上用不到）

8.3 笛卡尔积现象

如果两张表进行连接查询，没有任何条件的话，得到的结果是两张表结果的乘积。

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 |
+--------+------------+

如何避免笛卡尔积现象？

加条件过滤

避免笛卡尔积现象，就减少匹配次数吗？

不会，不会减少显示的有效记录，匹配次数还是之前的。

案例：找出每一个员工的部门名称，要求显示员工名和部门名

mysql> select ename,dname from emp e,dept d where e.DEPTNO = d.deptno; //这是正确的写法
+--------+------------+
| 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 |
+--------+------------+

8.4 内连接

什么是内连接？

假设A和B进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接

AB两张表没有主副之分，两种表是平等的。

8.4.1 内连接之等值连接

最大特点：等值关系

案例：查询每个员工的部门名称，要求显示员工名和部门名

SQL92：（太老了，不用了）

mysql> select ename,dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| 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 |
+--------+------------+

SQL99：（常用）

mysql> select ename,dname from emp e inner join dept d on e.DEPTNO = d.deptno;
+--------+------------+
| 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 |
+--------+------------+

语法：

select
	..
from
	A表
inner join         //inner可以省略
	B表
on
	连接条件
where
	过滤条件

SQL92 的缺点：

把表的连接条件和where过滤添加写到一起了。

SQL99的优点：

把表的连接条件和where过滤添加分离了。

8.4.2 内连接之非等值连接

最大特点：连接条件为非等值关系

案例：找出每个员工的工资等级，要求显示员工名称、工资、工资等级

mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal>=s.losal and e.sal<=hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

语法：

select
	..
from
	A表
inner join         //inner可以省略
	B表
on
	连接条件（非等值条件）
where
	过滤条件

8.4.3 内连接之自连接

最大特点：一张表看成两张表，自己连接自己。

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名

步骤一：先查看所有员工和领导信息
mysql> select empno, ename,mgr from emp;
+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SMITH  | 7902 |
|  7499 | ALLEN  | 7698 |
|  7521 | WARD   | 7698 |
|  7566 | JONES  | 7839 |
|  7654 | MARTIN | 7698 |
|  7698 | BLAKE  | 7839 |
|  7782 | CLARK  | 7839 |
|  7788 | SCOTT  | 7566 |
|  7839 | KING   | NULL |
|  7844 | TURNER | 7698 |
|  7876 | ADAMS  | 7788 |
|  7900 | JAMES  | 7698 |
|  7902 | FORD   | 7566 |
|  7934 | MILLER | 7782 |
+-------+--------+------+

步骤二：查看所有领导的信息
select 
	empno,ename 
from 
	emp  
where 
	empno 
in 
	(select mgr from emp );
+-------+-------+
| empno | ename |
+-------+-------+
|  7902 | FORD  |
|  7698 | BLAKE |
|  7839 | KING  |
|  7566 | JONES |
|  7788 | SCOTT |
|  7782 | CLARK |
+-------+-------+


步骤三：把上述两张表连接
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  |
+--------+--------+

8.5 外连接

最大特点：主表数据无条件的全部输出

什么是外连接，和内连接有什么区别？

内连接

假设A和B进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

AB两张表没有主副之分，两种表是平等的。

外连接

假设A和B表进行连接，使用外连接的话，AB两种表中有一张表是主表，一张表是副表，主要查询主表中的数据，顺便把副表中的数据捎带上，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出 NULL 与之匹配。

外连接的分类：

左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接也有左连接的写法。

语法：

select 
	..
from
	A表
left/right outer join        //outer可以省略
	B表
on
	连接条件
where 
	过滤条件

案例：找出每个员工的领导（所有员工必须全面查出来）

//这是之前的内连接，把king的员工丢了
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  |
+--------+--------+

//应使用外连接，把员工表作为主表（左外连接）
select 
	a.ename as '员工名' ,b.ename as '领导名' 
from 
	emp a 
left join
	emp b 
on 
	a.mgr =b.empno;
+--------+--------+
| 员工名  | 领导名  |
+--------+--------+
| 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  |
+--------+--------+

//应使用外连接，把员工表作为主表（左=右外连接）
select 
	a.ename as '员工名' ,b.ename as '领导名' 
from 
	emp b 
right join 
	emp a 
on 
	a.mgr =b.empno;
+--------+--------+
| 员工名  | 领导名  |
+--------+--------+
| 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  |
+--------+--------+

案例：找出那个部门没有员工

select 
	d.* 
from 
	emp e 
right join 
	dept d 
on 
	e.deptno = d.deptno 
where 
	e.empno is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

三张表的连接查询

案例：找出每一个员工的部门名称和工资等级。

 select 
 	e.empno,e.ename,d.deptno,d.dname,s.grade
 from
 	emp e
 join
 	dept d
 on
 	e.deptno = d.deptno
 join
 	salgrade s
 on
	 e.sal >= s.losal and e.sal <= s.hisal;
	 
	 
+-------+--------+--------+------------+-------+
| empno | ename  | deptno | dname      | grade |
+-------+--------+--------+------------+-------+
|  7369 | SMITH  |     20 | RESEARCH   |     1 |
|  7499 | ALLEN  |     30 | SALES      |     3 |
|  7521 | WARD   |     30 | SALES      |     2 |
|  7566 | JONES  |     20 | RESEARCH   |     4 |
|  7654 | MARTIN |     30 | SALES      |     2 |
|  7698 | BLAKE  |     30 | SALES      |     4 |
|  7782 | CLARK  |     10 | ACCOUNTING |     4 |
|  7788 | SCOTT  |     20 | RESEARCH   |     4 |
|  7839 | KING   |     10 | ACCOUNTING |     5 |
|  7844 | TURNER |     30 | SALES      |     3 |
|  7876 | ADAMS  |     20 | RESEARCH   |     1 |
|  7900 | JAMES  |     30 | SALES      |     1 |
|  7902 | FORD   |     20 | RESEARCH   |     4 |
|  7934 | MILLER |     10 | ACCOUNTING |     2 |
+-------+--------+--------+------------+-------+

案例：找出每个员工的部门名称、工资等级、以及上级领导

select 
	a.ename as '员工姓名',d.dname as '部门名称',s.grade as '工资等级',b.ename as '上级领导'
from
	emp a
join 
	dept d 
on 
	a.deptno = d.deptno
join 
	salgrade s
on
	a.sal >= s.losal and a.sal <= hisal
left join 
	emp b
on
	a.mgr =b.empno;

9、子查询

什么是子查询？子查询都可以出现在哪里？

select 语句当中嵌套select 语句，被嵌套的select语句是子查询。

子查询可以出现在哪里？

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

9.1 where子句中使用子查询

案例：找出高于平均薪资的员工信息

select * from emp where sal > (select avg(sal) from emp);

9.2 from子句中使用子查询

案例：找出每个部门平均薪水的薪资等级

第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
|     10 | 2916.666667 |
+--------+-------------+

第二步：找出每个部门平均薪水的薪资等级,将以上的查询结果作为临时表a，再让a和salgrade连接查询
select deptno,avgsal,grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) as a
join 
	salgrade s
on 
	a.avgsal >= s.losal and a.avgsal <= s.hisal;
	
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
+--------+-------------+-------+

案例：找出每个部门薪水等级的平均值

第一步：先计算每个人的薪水等级
select 
	ename,s.grade as sgrade,deptno
from 
	emp e 
join 
	salgrade s
on 
	e.sal >= s.losal and e.sal <= s.hisal;
	
+--------+--------+--------+
| ename  | sgrade | deptno |
+--------+--------+--------+
| SMITH  |      1 |     20 |
| ALLEN  |      3 |     30 |
| WARD   |      2 |     30 |
| JONES  |      4 |     20 |
| MARTIN |      2 |     30 |
| BLAKE  |      4 |     30 |
| CLARK  |      4 |     10 |
| SCOTT  |      4 |     20 |
| KING   |      5 |     10 |
| TURNER |      3 |     30 |
| ADAMS  |      1 |     20 |
| JAMES  |      1 |     30 |
| FORD   |      4 |     20 |
| MILLER |      2 |     10 |
+--------+--------+--------+	
	
第二步：对上述结果按照部门进行按照deptno分组，然后对grade求平均值
select ee.deptno, avg(sgrade)
from 
 (select 
	ename,s.grade as sgrade,deptno
    from 
        emp e 
    join 
        salgrade s
    on 
        e.sal >= s.losal and e.sal <= s.hisal) as ee
group by 
	ee.deptno;
	
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     20 |       2.8000 |
|     30 |       2.5000 |
|     10 |       3.6667 |
+--------+--------------+	
	
也可以不用子查询，直接进行分组
select 
	deptno, avg(s.grade)
from 
	emp e 
join 
	salgrade s
on 
	e.sal >= s.losal and e.sal <= s.hisal
group by 
	deptno;

9.3 select 子句中使用子查询

案例：找到每个员工所在的部门名称，要求显示员工名字和部门名

第一种方式：连接查询
select 
	e.ename,d.dname
from 
	emp e
join
	dept d 
on 
	e.deptno = d.deptno;
	
第二种方式：子查询
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 |
+--------+------------+

10、结果集集合处理

10.1 union（对结果集进行相加）

要求：两次查询结果的列的数目应该相同才可以拼接到一起，可以是两个毫不相干的结果集。

案例：找出工作岗位是 CLERK和SALESMAN的员工

第一种方式：
select ename,job from emp where job = 'CLERK' or job = 'SALESMAN';
第二种方式：
select ename,job from emp where job in ('CLERK','SALESMAN');
+--------+----------+
| ename  | job      |
+--------+----------+
| SMITH  | CLERK    |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| ADAMS  | CLERK    |
| JAMES  | CLERK    |
| MILLER | CLERK    |
+--------+----------+
第三种方式：
select ename,job from emp where job = 'CLERK'
union 
select ename,job from emp where job = 'SALESMAN';

+--------+----------+
| ename  | job      |
+--------+----------+
| SMITH  | CLERK    |
| ADAMS  | CLERK    |
| JAMES  | CLERK    |
| MILLER | CLERK    |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+

11、limit（重点）

limit是mysql特有的，其他数据库都没有，不通用。

limit取结果集中的部分数据。

语法机制：

limit startIndex,length
startIndex 表示起始位置，第一个元素下标是0
length 表示取几个

案例：取出工资前5名的员工

步骤一：先对员工按照工资进行排序
select * from emp order by sal desc;

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

步骤二：取出前5名（下面两种结果一样）
select * from emp order by sal desc limit 0,5;
select * from emp order by sal desc limit 5;  //直接写一个5，前面第一个数字默认是0

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
+-------+-------+-----------+------+------------+---------+------+--------+

案例：找出工资排序在第4到第9名直接的员工？

select * from emp order by sal desc limit 3,6;  //第4名的下标为3

+-------+--------+----------+------+------------+---------+--------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+--------+----------+------+------------+---------+--------+--------+
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |   NULL |     10 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |   0.00 |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300.00 |   NULL |     10 |
+-------+--------+----------+------+------------+---------+--------+--------+

注意：

limit是sql语句最后执行的环节

select 		5
..
from		1
..
where		2
..
group by	3
..
having		4
..
order by	6
..
limit 		7
..	;

12、分页查询

每页显示pagesize条记录

第pagenum页：limit (pagenum-1)*pagesize,pagesize;

java代码：

int pageNum = 5;
int pageSize = 10;
limit (pageNum-1)*pageSize,pageSize;

一直流浪~

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
02、数据查询（DQL）

02、数据查询（DQL）1、简单的查询语法格式：（1）查询单个字段： select 字段名 from 表名;（2）查询多个字段： select 字段名1,字段名2,字段名3,…… from 表名;（3）查询所有字段： select * from 表名;提示：任何一条sql语句都要以";"结尾sql语句不区分大小写案例：查询员工年薪？（月薪*12）字段可以参与数学运算select ename,sal*12 from emp;+--------+----------+
复制链接

扫一扫