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;