MySQL查询命令-DQL

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)

DEPT //员工部门表
  部门编号  部门名       地址
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

SALGRADE //工资等级表
  等级	最少	  最高
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

常用命令

登录	mysql -uroot -p密码

查看全部数据库	show databases;

创建数据库	create database 数据库名

进入（使用）数据库	use 数据库名

查看当前使用的数据库中的所有表	show tables;

初始化数据	source 数据库文件地址（例如： source D: \XiaoK. sq1）

删除数据库	drop database 数据库名

查看表结构	desc 表名

查看当前使用的是哪个数据库	select database();

查看数据库版本	select version();

终止语句	\c

查询表的全部内容	select * from 表名

查看其中库中的表	show tables from exam;

查看创建表的语句	show create table 表名；

如果表存在则删除此表	drop table if exists 表名；

一个完整的DQL语句

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

查询

简单的查询语句

select 字段名1,字段名2,... from 表名;

案例1：查询所有字段

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.01 sec)

案例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 |
+--------+----------+
14 rows in set (0.00 sec)

案例3：给查询结果的列重命名

其中如果需要更改所查询的字段名则使用一下语句

select ename,sal*12 as yearsal from emp;

其中as也可省略，如果更改的字段名为中文则使用单引号括起来

条件查询

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

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

案例1：找出哪些人津贴为NULL

在数据库中null不是一个值，不能用等号衡量，使用语法是 is null 或 is not null

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 |
+--------+------+
10 rows in set (0.00 sec)

案例2：找出哪些人津贴不为NULL

mysql> select ename,comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

案例3：找出哪些人没有津贴

mysql> select ename,comm from emp where comm is null or comm = 0;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| TURNER | 0.00 |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+
11 rows in set (0.00 sec)

案例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 |
+--------+----------+
7 rows in set (0.00 sec)

案例5：找出薪资大于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 |
+--------+---------+--------+
9 rows in set (0.00 sec)

案例6：找出工作岗位是manager和salesman的员工，使用in

在这里in等同于or

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 |
+--------+----------+
7 rows in set (0.00 sec)

案例7：找出工资不是800和5000的所有人

这里使用的是 not in

mysql> select ename,sal from emp where sal not in (800,5000);
+--------+---------+
| 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 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)
同样可以用
mysql> select ename,sal from emp where sal<>800 and sal<>5000;

模糊查询

在模糊查询中，有两个特殊的符号，一个是%，一个是_

%代表任意多个字符，_代表任意1个字符

案例1：找出名字中含有O的

mysql> select ename from emp where ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD  |
+-------+
3 rows in set (0.00 sec)

案例2：找出名字中第二个字母是o的

mysql> select ename from emp where ename like '_o%';
+-------+
| ename |
+-------+
| JONES |
| FORD  |
+-------+
2 rows in set (0.00 sec)

同理：找出名字中第三个字母是o的可得

select ename from emp where ename like '_ _o%';

案例3：找出名字中有下划线的

select ename from emp where ename like '%\_%';

使用转义字符

排序

案例1：按照工资升序，找出员工名和薪资

这里使用到了 order by 默认是升序排列

如何指定是升序或者是降序呢？asc表示升序，desc表示降序

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

如果需要让他降序排列则

select ename,sal from emp order by sal desc;

案例2：按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

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 |
+--------+---------+
14 rows in set (0.00 sec)

注意：越靠前的字段越能起到主导作用，只有当前面的字段无法起到排序的时候，才会启用后面的字段

排序order by也可以用户按列

案例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 |
+--------+---------+----------+
4 rows in set (0.00 sec)

执行顺序
select 
	*		3
from
	表名	   1
where
	条件	   2
order by
	.....   4
order by 是最后执行的

分组函数

分组函数一共有5个

分组函数还有另一个名字：多行处理函数

多行处理函数的特点：输入多行，最终输出的结果是1行

分组函数自动忽略null，不需要ifnull函数

count	计数
sum	求和
avg	取平均值
min	取最小值
max	取最大值

案例1：找出员工工资总和

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

案例2：找出最高工资

select max(sal) from emp;

案例3：找出最低工资

select min(sal) from emp;

案例4：找出平均工资

select avg(sal) from emp;

案例5：找出总人数

select count(*) from emp;

select count(ename) from emp;

分组函数自动忽略NULL

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

单行处理函数

什么是单行处理函数？

输入一行，输出一行

案例：计算每个员工的年薪

select ename , (sal+comm) * 12 from emp;

结果出现：

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 |
+--------+---------------+
14 rows in set (0.00 sec)

重点：所有数据库规定，只要有NULL参与运算的结果一定是NULL 例：1000+null = null

ifnull() 空处理函数

ifnull(可能为NULL的数据,被当做什么处理)，属于单行处理函数

所以可解决以上问题

mysql> select ename,(sal+ifnull(comm,0))*12 from emp;
+--------+-------------------------+
| ename  | (sal+ifnull(comm,0))*12 |
+--------+-------------------------+
| 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.00 sec)

案例：找出工资高于平均工资的员工？

会出现以下错误

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

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 |
+-------+---------+
6 rows in set (0.00 sec)

注意：分组函数不能直接使用在where语句中

count(*)和count(具体的某个字段)，他们有什么区别？

count(*):不是统计某个字段中数据的个数，而是统计总记录条数

count(sal):表示统计sal字段中不为NULL的数据总数量。

group by 和 having

注意：分组函数一般都会和group by 联合使用，并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行，当一条sql语句没有group by，整张表的数据就会自成一组。

group by ：按照某个字段或者某些字段进行分组。

having ： having是对分组以后的数据进行再次过滤

案例：找出每个工作岗位的最高薪资

mysql> select ename,job,max(sal) from emp group by job;
+-------+-----------+----------+
| ename | job       | max(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST   |  3000.00 |
| SMITH | CLERK     |  1300.00 |
| JONES | MANAGER   |  2975.00 |
| KING  | PRESIDENT |  5000.00 |
| ALLEN | SALESMAN  |  1600.00 |
+-------+-----------+----------+
5 rows in set (0.00 sec)

以上在mysql 当中，查询结果是有的，但是结果没有意义，在Oracle数据库当中会报错，语法错误，Oracle的语法规则比MySQL语法规则严谨。

记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。

案例：每个工作岗位的平均薪资？

select job,avg(sal) from emp group by job;

多个字段能不能联合起来一块分组？

案例：找出每个部门不同工作岗位的最高薪资？

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

案例：找出每个部门的最高薪资，要求显示薪资大于2900的数据

第一步：找出每个部门的最高薪资

select max(sal),deptno from emp group by deptno;

第二步：找出薪资大于2900

select max(sal),deptno from emp group by deptno having max(sal) > 2900; //这种方式效率低，不推荐

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

案例：找出每个部门的平均薪资，要求显示薪资大于2000的数据

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

select deptno,avg(sal) from emp where avg(sal) >2000 group by deptno; //这样是错误的

去重 distinct

1、关于查询结果集的去重？

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 job; //这样的sql语句是错误的

记住：distinct只能出现所有字段的最前面，如果出现在最前面则表示后面所有联合起来的字段去重

案例：统计岗位的数量？

mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+
1 row 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 |
...

**关于表的别名：**

select e.ename,d.dname from emp e ,dept d;

好处：一、执行效率高二、可读性好

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

mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //SQL92 语句已经过时，不使用

内连接

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。

语法：
select
	...
from
	...
inner join		//其中的inner是可以省略不写的
	...
on
	...

等值连接

特点：条件是等量关系。

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

SQL99 (常用的)

语法：
select
	...
form 
	...
join
	...
on //类似where 后面跟的是条件
	...
也可 join ... inner ...

mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;(SQL92 过时的)
mysql> select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;(SQL99 常用)
+--------+------------+
| 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,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.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 |
+--------+---------+-------+
14 rows in set (0.00 sec)

自连接

特点：一张表看作两张表，自己连接自己。

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

语法：
select 
	a.ename,b.ename
from
	emp a
join
	emp b
on
	a.mgr = b.empno;
-------------------------------
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)
//因为KING对应的领导是NULL所以匹配不到

外连接

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

外连接的分类
- 左外连接（左连接）：表示坐标的这张表是主表
- 右外连接（右连接）：表示右边的这张表是主表

语法：
select
	...
from
	...
left/right outer join		//其中的outer是可以省略不写的
	...
on
	...

案例：找出每个员工的上级领导

mysql> select
    -> a.ename emp,b.ename boss
    -> from
    -> emp a
    -> left join
    -> emp b
    -> on
    -> a.mgr=b.empno;
+--------+-------+
| emp    | boss  |
+--------+-------+
| 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)

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

mysql> select d.* from emp e right join dept d on e.deptno=d.deptno where e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)

同样可一使用以下方法
mysql> select d.dname,count(e.ename) from emp e right outer join dept d on e.deptno=d.deptno group by dname;
+------------+----------------+
| dname      | count(e.ename) |
+------------+----------------+
| ACCOUNTING |              3 |
| OPERATIONS |              0 |
| RESEARCH   |              5 |
| SALES      |              6 |
+------------+----------------+
4 rows in set (0.00 sec)

三张表怎么连接查询？

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

格式：
select
	e.ename,d.dname,s.grade
from
	emp e
join
	dept d
on
	e.deptno=d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal;
结果：
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+
14 rows in set (0.00 sec)

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

select
	e.ename,d.dname,s.grade,a.ename
from 
	emp e
join
	dept d
on
	e.deptno=d.deptno
join
	salgrade s
on
	e.sal between s.losal and s.hisal
left join
	emp a
on
	e.mgr=a.empno;
结果：
+--------+------------+-------+-------+
| ename  | dname      | grade | ename |
+--------+------------+-------+-------+
| SMITH  | RESEARCH   |     1 | FORD  |
| ALLEN  | SALES      |     3 | BLAKE |
| WARD   | SALES      |     2 | BLAKE |
| JONES  | RESEARCH   |     4 | KING  |
| MARTIN | SALES      |     2 | BLAKE |
| BLAKE  | SALES      |     4 | KING  |
| CLARK  | ACCOUNTING |     4 | KING  |
| SCOTT  | RESEARCH   |     4 | JONES |
| KING   | ACCOUNTING |     5 | NULL  |
| TURNER | SALES      |     3 | BLAKE |
| ADAMS  | RESEARCH   |     1 | SCOTT |
| JAMES  | SALES      |     1 | BLAKE |
| FORD   | RESEARCH   |     4 | JONES |
| MILLER | ACCOUNTING |     2 | CLARK |
+--------+------------+-------+-------+
14 rows in set (0.00 sec)

子查询

什么是子查询？子查询都可以出现在哪里？
子查询可以出现在哪里？
	select
		...(select)
	form
		...(select)
	where
		...(select)

where子句中使用子查询

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

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

from后面嵌套子查询

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

第一步：先找出每个部门平均薪水的薪资等级
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)
第二步：将以上查询的结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
mysql> select
    ->  t.deptno,t.avgsal,s.grade
    -> from
    ->  (select deptno,avg(sal) avgsal from emp group by deptno) t
    -> join
    ->  salgrade s
    -> on
    ->  avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
3 rows in set (0.00 sec)

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

第一步：找出每个员工的薪水等级。
mysql> select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and
 s.hisal;
+--------+---------+--------+-------+
| ename  | sal     | deptno | grade |
+--------+---------+--------+-------+
| SMITH  |  800.00 |     20 |     1 |
| ALLEN  | 1600.00 |     30 |     3 |
| WARD   | 1250.00 |     30 |     2 |
| JONES  | 2975.00 |     20 |     4 |
| MARTIN | 1250.00 |     30 |     2 |
| BLAKE  | 2850.00 |     30 |     4 |
| CLARK  | 2450.00 |     10 |     4 |
| SCOTT  | 3000.00 |     20 |     4 |
| KING   | 5000.00 |     10 |     5 |
| TURNER | 1500.00 |     30 |     3 |
| ADAMS  | 1100.00 |     20 |     1 |
| JAMES  |  950.00 |     30 |     1 |
| FORD   | 3000.00 |     20 |     4 |
| MILLER | 1300.00 |     10 |     2 |
+--------+---------+--------+-------+
14 rows in set (0.00 sec)
第二步：基于以上结果，继续按照deptno分组，求grade平均值
mysql> select e.deptno,avg(s.grade)
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal
    -> group by e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
|     10 |       3.6667 |
|     20 |       2.8000 |
|     30 |       2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)

在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)

union

可以将查询结果集相加

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

第一种：select ename,job from emp where job='salesman' or job='manager';
第二种：select ename,job from emp where job in ('salesman','manager');
第三种:union
select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';

union可以实现把两张不相干的表中的数据拼接在一起显示，前提必须是要显示相同的列数

例如：把员工名和部门名拼接在一块显示
mysql> select ename from emp
    -> union
    -> select dname from dept;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
18 rows in set (0.00 sec)

limit ★

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

limit是MySQL特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
limit是sql语句最后执行的一个环节
语法：limit startIndex,length
startIndex表示起始位置，可以省略不写，默认为0
length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）

mysql> select ename,sal from emp order by sal desc limit 0,5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

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

mysql> select  ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
6 rows in set (0.00 sec)

通用标准分页sql

每页显示5条记录：
第1页：0,  5
第2页：5， 5
第3页：10，5
第4页：15，5
第5页：20，5
...
所以可得：每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize , pageSize
pageSize : 代表每页显示多少条记录
pageNo	 : 代表显示第几页

java代码{
	int pageNo = 2;//页码是2
	int pageSize = 10;//每页显示10条
	
	limit (pageNo - 1) * pageSize , pageSize;
}

posted @ 2020-07-19 15:24 MrXiaoK 阅读(465) 评论(0) 收藏举报

刷新页面返回顶部

MrXiaoK

MySQL查询命令-DQL