相关文章:
- 《MySQL学习记录 (一) ----- 有关数据库的基本概念和MySQL常用命令》
- 《MySQL学习记录 (二) ----- SQL数据查询语句(DQL)》
- 《MySQL学习记录 (三) ----- SQL数据定义语句(DDL)》
- 《MySQL学习记录 (四) ----- SQL数据管理语句(DML)》
- 《MySQL学习记录 (五) ----- 存储函数、存储过程和触发器》
SQL查询语句(DQL)
一、一个完整的SQL查询语句结构
执行顺序
select 5
字段...
from 1
表名...
where 2
条件表达式...
group by 3
字段...
having 4
条件表达式...
order by 6
字段...
limit 7
startIndex,length
二、简单查询
-
语法介绍
语法格式 功能 select 字段名1,字段名2... from 表名;
查询表中一个或多个字段的记录 select * from 表名
查询表中所有字段记录 -
示例
(1) 查询表中一个或多个字段的记录mysql> select ename,sal from emp; +--------+---------+ | ename | sal | +--------+---------+ | SMITH | 800.00 | | 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 | +--------+---------+
(2) 显示字段运算结果
mysql> 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 | +--------+----------+
(3) 将查询结果中的字段按别名显示出来(as)
mysql> 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 | +--------+----------+
(4) 显示表中所有记录
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 | +-------+--------+-----------+------+------------+---------+---------+--------+
(5) 查询结果集去重(distinct)
mysql> select distinct job from emp; +-----------+ | job | +-----------+ | CLERK | | SALESMAN | | MANAGER | | ANALYST | | PRESIDENT | +-----------+
(6) 查询不同的部门都有哪几种职位
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 | +--------+-----------+
注意:
distinct
关键字只能出现在所有字段的前面,distinct
后面有多个字段时,表示多个字段联合去重。
三、条件查询
-
语法介绍
语法格式 功能 select 字段名1,字段名2... from 表名 where 条件;
条件查询 -
条件运算符
运算符 说明 =
判等 <>
或!=
不等于 <
小于 <=
小于等于 >
大于 >=
大于等于 between A and B
[A,B],等同于>= A and <= B is null
/is not null
判空 and
并且 or
或者 in
/not in
in等同于or, not in中的in等同于and not
取非,主要在is或者in中使用 like
模糊查询,支持%或者下划线匹配 -
示例:
(1) 条件查询:查询年薪小于等于20000的员工,查询结果显示姓名、编号
mysql> select empno,ename,sal*12 from emp where sal*12<=20000; +-------+--------+----------+ | empno | ename | sal*12 | +-------+--------+----------+ | 7369 | SMITH | 9600.00 | | 7499 | ALLEN | 19200.00 | | 7521 | WARD | 15000.00 | | 7654 | MARTIN | 15000.00 | | 7844 | TURNER | 18000.00 | | 7876 | ADAMS | 13200.00 | | 7900 | JAMES | 11400.00 | | 7934 | MILLER | 15600.00 | +-------+--------+----------+
(2) 条件查询:姓名首字母为A—C的员工姓名
mysql> select ename from emp where ename between 'A' and 'C'; +-------+ | ename | +-------+ | ALLEN | | BLAKE | | ADAMS | +-------+ 注意:用between...and...查询字符串时,范围是左闭右开,如:上面[A,C)
(3) 条件查询:查询员工表中谁没有津贴
mysql> select ename,comm from emp where comm is null; +--------+------+ | ename | comm | +--------+------+ | SMITH | NULL | | JONES | NULL | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+------+
(4) 条件查询:查询员工表中职位为manager和salesman的员工
mysql> select ename,job from emp where job = 'manager' or job = 'salesman'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN | +--------+----------+
mysql> select ename,job from emp where job in('manager','salesman'); +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | JONES | MANAGER | | MARTIN | SALESMAN | | BLAKE | MANAGER | | CLARK | MANAGER | | TURNER | SALESMAN |
(5) 条件查询:查询员工表中职位不为manager或者salesman的员工
mysql> select ename,job from emp where job != 'manager' and job != 'salesman'; +--------+-----------+ | ename | job | +--------+-----------+ | SMITH | CLERK | | SCOTT | ANALYST | | KING | PRESIDENT | | ADAMS | CLERK | | JAMES | CLERK | | FORD | ANALYST | | MILLER | CLERK | +--------+-----------+
mysql> select ename,job from emp where job not in('manager','salesman'); +--------+-----------+ | ename | job | +--------+-----------+ | SMITH | CLERK | | SCOTT | ANALYST | | KING | PRESIDENT | | ADAMS | CLERK | | JAMES | CLERK | | FORD | ANALYST | | MILLER | CLERK | +--------+-----------+
(6) 条件查询:找出薪资大于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 | +--------+---------+--------+
(7) 条件查询:查询员工姓名中含有’o’字符的员工
mysql> select ename from emp where ename like '%o%'; +-------+ | ename | +-------+ | JONES | | SCOTT | | FORD | +-------+ 注意:单引号`''`中嵌套特殊字符`%%`
(8) 条件查询:查询员工姓名中第二个字母为A的员工
mysql> select ename from emp where ename like '_A%'; +--------+ | ename | +--------+ | WARD | | MARTIN | | JAMES | +--------+
拓展:
- 查询名字第一个字母为A ,
like 'A%'
- 查询名字最后一个字母为T ,
like '%T'
- 查询名字中含有下划线
_
,like '%\_%'
- 查询名字第一个字母为A ,
补充模糊查询:
like
关键字一般和通配符搭配使用,下面是常用的通配符。
通配符 | 含义 |
---|---|
% | 表示 0 ~ 任意个字符 |
_ | 表示任意单个字符 |
- 查询员工名中包含字符 ‘a’ 的员工信息
select * from emp where ename like '%a%';
- 查询员工的员工名的第三个字符为 ‘n’ ,第五个字符为 ‘l’ 的员工信息
select * from emp where ename like '__n_l%';
- 查询员工名中第二个字符为 ‘_’ 的员工名
select * from emp where ename like '_\_%';
四、排序
-
语法介绍
语法 功能 order by 字段1,字段2...
根据字段数据值大小默认从上到下为升序 order by 字段 asc
根据字段数据值大小指定从上到下为升序 order by 字段 desc
根据字段数据值大小指定从上到下为降序 -
示例
(1) 按照工资降序排序,当工资相同的时候按照名字升序排序mysql> select ename,sal from emp order by sal desc,ename asc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | MARTIN | 1250.00 | | WARD | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+
注意:多个字段排序,只有当前一个字段数据值相等时(无法比较大小)才会启用后一个字段排序,如:上例只有员工工资相等时才会依据员工姓名排序。
(2) 指定第2列数据降序排序
mysql> select ename,sal from emp order by 2 desc; +--------+---------+ | ename | sal | +--------+---------+ | KING | 5000.00 | | SCOTT | 3000.00 | | FORD | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | | CLARK | 2450.00 | | ALLEN | 1600.00 | | TURNER | 1500.00 | | MILLER | 1300.00 | | WARD | 1250.00 | | MARTIN | 1250.00 | | ADAMS | 1100.00 | | JAMES | 950.00 | | SMITH | 800.00 | +--------+---------+
(3) 找出工作岗位是salesman的员工,并且要求按照工资降序排列。
mysql> select ename,sal,job from emp where job = 'salesman' order by sal desc; +--------+---------+----------+ | ename | sal | job | +--------+---------+----------+ | ALLEN | 1600.00 | SALESMAN | | TURNER | 1500.00 | SALESMAN | | WARD | 1250.00 | SALESMAN | | MARTIN | 1250.00 | SALESMAN | +--------+---------+----------+
五、分组函数
-
函数介绍
分组函数又称多行处理函数:处理多行数据,最终输出结果为1行函数名 功能 count 计数 sum 求和 avg 平均值 max 最大值 min 最小值 -
示例
(1) 求出所有员工月薪总和mysql> select sum(sal) from emp; +----------+ | sum(sal) | +----------+ | 29025.00 | +----------+
(2) 求最高月薪
mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000.00 | +----------+
(3) 求最低月薪
mysql> select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+
(4) 求出员工平均年薪
mysql> select avg(sal*12) from emp; +--------------+ | avg(sal*12) | +--------------+ | 24878.571429 | +--------------+
(5) 求出员工总人数
mysql> select count(ename) from emp; +--------------+ | count(ename) | +--------------+ | 14 | +--------------+
(6) 求出有津贴的员工数
mysql> select count(comm) from emp; +-------------+ | count(comm) | +-------------+ | 4 | +-------------+
注意:
- 分组函数(多行处理函数)会自动忽略NULL数据
count(*)
计算的是表中的总记录条数count(具体某个字段)
计算的是表中对应字段不为空的数据条数
-
补充:单行处理函数(处理几行数据,最终输出几行数据)
(1) 字段运算
mysql> select ename,(sal+comm)*12 from emp; +--------+---------------+ | ename | (sal+comm)*12 | +--------+---------------+ | SMITH | NULL | | ALLEN | 22800.00 | | WARD | 21000.00 | | JONES | NULL | | MARTIN | 31800.00 | | BLAKE | NULL | | CLARK | NULL | | SCOTT | NULL | | KING | NULL | | TURNER | 18000.00 | | ADAMS | NULL | | JAMES | NULL | | FORD | NULL | | MILLER | NULL | +--------+---------------+
注意:只要有
NULL
参与运算,则最终输出结果一定为NULL
(2)
fillnull()
空处理函数
计算员工年薪(包含每个月的津贴)mysql> select ename,(sal+ifnull(comm,0))*12 as Annualsalary from emp; +--------+--------------+ | ename | Annualsalary | +--------+--------------+ | 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 | +--------+--------------+
ifnull(comm,0)
:如果comm字段数据为NULL,则将其作为0处理
六、分组查询
-
语法介绍
语法格式 功能 group by 字段1,字段2...
按照某个字段或者某些字段进行分组 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 deptno,avg(sal) from emp group by deptno; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 20 | 2175.000000 | | 30 | 1566.666667 | | 10 | 2916.666667 | +--------+-------------+
(3) 求出每个部门不同工作岗位的最高薪资
mysql> select deptno,job,max(sal) from emp group by deptno,job order by deptno,max(sal); +--------+-----------+----------+ | deptno | job | max(sal) | +--------+-----------+----------+ | 10 | CLERK | 1300.00 | | 10 | MANAGER | 2450.00 | | 10 | PRESIDENT | 5000.00 | | 20 | CLERK | 1100.00 | | 20 | MANAGER | 2975.00 | | 20 | ANALYST | 3000.00 | | 30 | CLERK | 950.00 | | 30 | SALESMAN | 1600.00 | | 30 | MANAGER | 2850.00 | +--------+-----------+----------+
思想:从一堆人里面根据部门编号分成若干堆,再从每一堆中按照职位分成若干组,找出每一组中工资最大的。
(4) 找出不同部门最高月薪大于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 | +--------+----------+ 该方法是先条件过滤后再进分组,效率更高
注意:where条件过滤比having过滤效率更高,所以能用where时绝不用having
(5) 找出不同部门的平均月薪大于2000的部门编号
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000; +--------+-------------+ | deptno | avg(sal) | +--------+-------------+ | 20 | 2175.000000 | | 10 | 2916.666667 | +--------+-------------+ 该题目只能通过having过滤
七、连接查询
-
什么是连接查询?
在实际开发过程中,大部分情况都不是从单表中查询数据,一般都是多张表联合查询取出最终结果,这种通过多张表联合查询的查询方式就是连接查询。
-
连接查询的分类及区别
(1) 内连接:
join 表名 on 连接条件
- 等值连接:连接条件为等量关系
- 非等值连接:连接条件为非等量关系
- 自连接:同一张表看作两张表,自己与自己连接
(2) 外连接:
left/right join 表名 on 连接条件
- 左外连接:左边的表作为主表,右边的表作为副表
- 右外连接 :右边的表作为主表,左边的表作为副表
(3) 内连接与外连接的区别
-
内连接中相互连接的表没有主副之分,查询结果只会显示两张表匹配连接条件的记录。
-
外连接中相互连接的表有主副之分,主要查询主表中的数据,当副表中的数据与主表数据没有匹配上连接条件,副表会自动模拟出null来匹配主表数据。
关于对外连接对理解:主表的第一条记录会依次与副表的每一条记录匹配一次,如果没有匹配上,则副表会模拟出null来匹配主表的记录并显示到查询结果中,主表的第二条记录会依次与副表的每一条记录匹配一次,如果没有匹配上,则副表会模拟出null来匹配主表的记录并显示到查询结果中…
-
笛卡尔积现象:当两张表进行连接查询时,如果没有任何条件限制,那么最终查询结果条数为两张表记录条数乘积。
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 | 查询结果为emp表中ename字段记录与dept表中dname字段记录的笛卡尔积: 第一张表的第一条记录与第二张表的每一条记录匹配 第一张表的第二条记录与第二张表的每一条记录匹配 .... 56 rows in set (0.00 sec)
关于多表查询的第一种理解:第一张表的第一条记录与第二张表的第一条记录匹配一次,判断一下查询条件,如果条件成立则匹配成功,显示到查询结果中,然后第一张表的第一条记录又与第二张表的第二条记录匹配一次,判断一下查询条件…
第二种理解:先进行两张表的笛卡尔积,然后再依据查询条件,筛选笛卡尔积的每条记录,符合条件的就显示到结果中
-
关于表的别名
上诉sql语句可以改写成以下mysql> select e.ename, d.dname from emp as e, dept as d; mysql> select e.ename, d.dname from emp e, dept d;// as 可以省略
注意:给表起别名可以让执行效率更高并且增加可读性!
-
内连接
(1) 等值连接:找出每一个员工的部门名称,显示员工的名称和其所在的部门名称
mysql> select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;// 该语法已经舍弃,一般都使用下面的join on mysql> select e.ename,d.dname from emp e 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 | +--------+------------+
(2) 非等值连接:找出每个员工的工资等级,要求显示员工名、工资、工资等级
mysql> select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and 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 | +--------+---------+-------+
(3) 自连接:显示员工名和其领导名
mysql> select e1.ename,e2.ename as mgrname from emp e1 join emp e2 on e1.mgr = e2.empno; +--------+-------+ | ename | mgrname | +--------+-------+ | 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 | +--------+-------+ // 员工名中没有King,因为他没有上级领导,没有匹配上连接条件,则该记录会舍弃掉 // 如果想让King出现在员工名中,并且上级领导显示null,则需要使用下面的外连接
-
外连接
(1) 左外连接:显示员工名和其领导名(没有领导的员工领导名显示null)
mysql> select e1.ename,e2.ename as mgrname from emp e1 left join emp e2 on e1.mgr = e2.empno; +--------+---------+ | ename | mgrname | +--------+---------+ | 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 | +--------+---------+
(2) 右外连接:将上诉左连接改为右连接,显示结果不变
mysql> select e1.ename,e2.ename as mgrname from emp e2 right join emp e1 on e1.mgr = e2.empno;
(3) 查询公司各部门的人员数
mysql> select d.dname, count(e.ename) as numbers from dept d left join emp e on d.deptno = e.deptno group by d.dname order by numbers desc; +------------+---------+ | dname | numbers | +------------+---------+ | SALES | 6 | | RESEARCH | 5 | | ACCOUNTING | 3 | | OPERATIONS | 0 | +------------+---------+
-
三张表及以上的连接查询
(1) 找出每一个员工的部门名称、工资等级、上级领导mysql> select e1.ename,d.dname,s.grade,e2.ename as mgrname from emp e1 join dept d on e1.deptno = d.deptno join salgrade s on e1.sal between s.losal and s.hisal left join emp e2 on e1.mgr = e2.empno order by s.grade; +--------+------------+-------+---------+ | ename | dname | grade | mgrname | +--------+------------+-------+---------+ | SMITH | RESEARCH | 1 | FORD | | ADAMS | RESEARCH | 1 | SCOTT | | JAMES | SALES | 1 | BLAKE | | WARD | SALES | 2 | BLAKE | | MARTIN | SALES | 2 | BLAKE | | MILLER | ACCOUNTING | 2 | CLARK | | ALLEN | SALES | 3 | BLAKE | | TURNER | SALES | 3 | BLAKE | | JONES | RESEARCH | 4 | KING | | BLAKE | SALES | 4 | KING | | CLARK | ACCOUNTING | 4 | KING | | SCOTT | RESEARCH | 4 | JONES | | FORD | RESEARCH | 4 | JONES | | KING | ACCOUNTING | 5 | NULL | +--------+------------+-------+---------+
八、子查询
-
什么是子查询?
查询语句中嵌套select语句,被嵌套的select语句就是子查询
-
子查询可以出现的位置
select ..(select).. from ..(select).. where ..(select)..
-
案例
(1) where后嵌套子查询:找出高于平均薪资的员工信息mysql> select e.* from emp e where e.sal > (select avg(sal) from emp); +-------+-------+-----------+------+------------+---------+------+--------+ | 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 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | +-------+-------+-----------+------+------------+---------+------+--------+
(2) from后嵌套子查询:找出每个部门平均薪资的工资等级
mysql> select x.dname,x.avgsal,s.grade from (select d.dname,avg(e.sal) as avgsal from dept d left join emp e on d.deptno = e.deptno group by d.dname) x left join salgrade s on x.avgsal between s.losal and s.hisal; +------------+-------------+-------+ | dname | avgsal | grade | +------------+-------------+-------+ | ACCOUNTING | 2916.666667 | 4 | | RESEARCH | 2175.000000 | 4 | | SALES | 1566.666667 | 3 | | OPERATIONS | NULL | NULL | +------------+-------------+-------+
思路:
-
先找出每个部门的平均薪资
mysql> select d.dname,avg(e.sal) as avgsal from dept d left join emp e on d.deptno = e.deptno group by d.dname; +------------+-------------+ | dname | avgsal | +------------+-------------+ | ACCOUNTING | 2916.666667 | | RESEARCH | 2175.000000 | | SALES | 1566.666667 | | OPERATIONS | NULL | +------------+-------------+
-
把上诉的查询结果当作一个新表x,表x与salgrade表进行连接查询,条件是:
x.avgsal between s.losal and s.hisal
,注意要给使用了分组函数的字段起别名!!!
(3) from后嵌套子查询:找出每个部门平均的薪资等级
mysql> select x.dname,avg(x.grade) as avgsalgrade from (select e.ename,d.dname,s.grade from emp e left join dept d on e.deptno=d.deptno left join salgrade s on e.sal between s.losal and s.hisal) x group by x.dname; +------------+-------------+ | dname | avgsalgrade | +------------+-------------+ | RESEARCH | 2.8000 | | SALES | 2.5000 | | ACCOUNTING | 3.6667 | +------------+-------------+
(4) select后嵌套子查询:显示员工名和其部门名
mysql> select e.ename,(select d.dname from dept d where d.deptno = e.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 | +--------+------------+
-
九、补充
-
(查询结果集1) union (查询结果集2)
将查询结果集相加示例:查询出职位为salesman和manager的员工
mysql> select ename,job from emp where job='salesman' union select ename,job from emp where job='manager'; +--------+----------+ | ename | job | +--------+----------+ | ALLEN | SALESMAN | | WARD | SALESMAN | | MARTIN | SALESMAN | | TURNER | SALESMAN | | JONES | MANAGER | | BLAKE | MANAGER | | CLARK | MANAGER | +--------+----------+
注意:使用
union
拼接查询结果的前提必须是被拼接的查询结果的字段数量相等 -
limit (startIndex), (length)
取出查询结果集的部分数据示例:由高到低查询出工资前五名员工
mysql> select ename,sal from emp order by sal desc limit 0,5; +-------+---------+ | ename | sal | +-------+---------+ | KING | 5000.00 | | FORD | 3000.00 | | SCOTT | 3000.00 | | JONES | 2975.00 | | BLAKE | 2850.00 | +-------+---------+
十、注意
简单查询与条件查询:
-
任何一条sql语句以
;
结尾 -
sql语句不区分大小写
-
sql语句中字符串要求用
''
单引号括起来 -
字段别名的使用:
select 字段名 as 别名
-
字段进行运算时,如果其中有数据值为null,则运算结果一定为null
-
查询语句执行顺序:from—>where—>group by—>having—>select—>order by
-
用
between...and...
查询字符串时,范围是左闭右开 -
使用
like
进行模糊查询时,单引号''
中要嵌套特殊字符%%
-
dinstinct
去重关键字只能用在所有字段最前面
排序:
- 使用多个字段排序,只有当前一个字段的数据值相等时(无法比较大小)才会启用后一个字段排序
分组函数:
-
分组函数(多行处理函数)会自动忽略NULL数据
(1)count(*)
计算的是表中的总记录条数
(2)count(具体某个字段)
计算的是表中字段不为NULL的数据总数量 -
SQL语句中规定:分组函数不可以直接在where子句中使用
-
分组函数处理的数据范围为分好的每一组的数据,如果没有分组,那么整张表就是一组数据,因此只会输出一行数据,如果分了多组出来,就会输出多行数据。
-
只要有NULL参与运算则最终计算结果一定为NULL
分组查询:
-
分组函数一般会和
group by
联合使用,任何一个分组函数都是在group by
执行结束后再执行的 -
sql语句中没有
group by
时,整张表就是一组数据 -
当sql语句中有
group by
时,select
后面只能含有参与分组的字段和分组函数 -
where条件过滤比having过滤效率更高,所以能用where时绝不用having
连接查询:
- 使用连接查询是请给表起别名
- 内连接的表没有主副之分,可能会造成查询结果缺少;外连接的表有主副之分,一定不会造成主表的查询结果缺少
子查询:
- 通常子查询嵌套在from、where后面
- 嵌套from后面,把子查询结果当作一个新表,在使用分组函数是请起别名
- 嵌套where后面,把子查询结果当作一个值
补充:
- 使用
union
相加查询结果集,要求相加的查询结果集的字段数量相同 - 使用
limit
取出查询结果集的部分,参数1起始位置、参数2是取出长度