看了这个你就会MySQL了（二）！！！-CSDN博客

本文链接：https://blog.csdn.net/weixin_43822955/article/details/117631205

看了这个你就会MySQL了（二）！！！

数据查询语言DQL

简单查询

查询一个字段

select+字段名+from+表名

mysql> select dname from dept;
+------------+
| dname      |
+------------+
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
4 rows in set (0.43 sec)

查询两个或多个字段

使用逗号分隔字段

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

查询所有字段

select * from 表名

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

给查询的列起别名

使用as关键字起别名，它只是将查询结果显示为别名，原表列名未更改。

//未起别名之前
mysql> select deptno,dname from dept;
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.03 sec)

//起别名之后
mysql> select deptno,dname as name from dept;
+--------+------------+
| deptno | name       |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
|     40 | OPERATIONS |
+--------+------------+
4 rows in set (0.03 sec)

也可以将as关键字省略

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

查询结果的计算

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

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

再对其起个别名

mysql> select ename,sal*12 '年薪' 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 |
+--------+----------+
14 rows in set (0.05 sec)

条件查询

什么是条件查询

条件查询指查出符合条件的
语法格式：

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

等于=

mysql> select empno,ename from emp where sal = 800;
+-------+-------+
| empno | ename |
+-------+-------+
|  7369 | SMITH |
+-------+-------+
1 row in set (0.02 sec)

不等于!=

可以使用!= 或者<>

mysql> select ename from emp where sal != 800;
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
13 rows in set (0.03 sec)

mysql> select ename from emp where sal <> 800;
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
13 rows in set (0.03 sec)

小于<

mysql> select empno,ename,sal from emp where sal < 1250;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7369 | SMITH |  800.00 |
|  7876 | ADAMS | 1100.00 |
|  7900 | JAMES |  950.00 |
+-------+-------+---------+
3 rows in set (0.02 sec)

小于等于<=

mysql> select empno,ename,sal from emp where sal <= 1250;
+-------+--------+---------+
| empno | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7521 | WARD   | 1250.00 |
|  7654 | MARTIN | 1250.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
+-------+--------+---------+
5 rows in set (0.03 sec)

大于>


mysql> select empno,ename,sal from emp where sal > 2500;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
5 rows in set (0.02 sec)

大于等于>=

mysql> select empno,ename,sal from emp where sal >= 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
3 rows in set (0.03 sec)

两个值之间between…and…

between…and… 等同于 >= and <=(and是并且的意思)

mysql> select empno,ename,sal from emp where sal between 2500 and 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
4 rows in set (0.03 sec)

mysql> select empno,ename,sal from emp where sal >= 2500 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal     |
+-------+-------+---------+
|  7566 | JONES | 2975.00 |
|  7698 | BLAKE | 2850.00 |
|  7788 | SCOTT | 3000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
4 rows in set (0.03 sec)

查看空或者不空is null / is not null

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

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

并且and

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

或者or

mysql> select
    -> 	   empno,ename,job,sal
    -> from
    -> 	   emp
    -> where
    -> 	   job = 'manager' or job = 'salesman';
+-------+--------+----------+---------+
| empno | ename  | job      | sal     |
+-------+--------+----------+---------+
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7521 | WARD   | SALESMAN | 1250.00 |
|  7566 | JONES  | MANAGER  | 2975.00 |
|  7654 | MARTIN | SALESMAN | 1250.00 |
|  7698 | BLAKE  | MANAGER  | 2850.00 |
|  7782 | CLARK  | MANAGER  | 2450.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
+-------+--------+----------+---------+
7 rows in set (0.03 sec)

and和or同时出现时优先级问题

mysql> select
    -> 		*
    -> from
    -> 		emp
    -> where
    -> 		sal > 2500 and deptno = 10 or deptno = 20;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.03 sec)

分析以上语句，找出工资大于2500并且部门编号为10的员工，或者20部门所有员工找出来。
要想找出部门10或者部门20中，工资大于2500的该怎么找？
加括号改变优先级：

mysql> select
    -> 		* 
    -> from
    -> 		emp
    -> where 
    ->		sal > 2500 and (deptno = 10 or deptno = 20);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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 |
+-------+-------+-----------+------+------------+---------+------+--------+
4 rows in set (0.03 sec)

包含in

相当于多个or，not in代表不在这个范围

mysql> select
    -> 		*
    -> from
    -> 		emp
    -> where
    -> 		job = 'manager' or job = 'salesman';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.04 sec)

mysql> select
    -> 		*
    -> from
    -> 		emp
    -> where
    -> 		job in('manager','salesman');
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  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 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.04 sec)

not in表示不在这个范围

mysql> select
    -> 		ename,sal
    -> from
    -> 		emp
    -> where
    -> 		sal not in(800,5000,3000,1600);
+--------+---------+
| ename  | sal     |
+--------+---------+
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
9 rows in set (0.04 sec)

模糊查询 like

找出字符串中带有某个字符的

找出名字中带字母O的

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

找出以什么结尾的

找出名字以H结尾的

mysql> select
    -> 		ename
    -> from
    -> 		emp
    -> where
    -> 		ename like '%H';
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.03 sec)

找出以什么开头的

找出名字以S开头的

mysql> select
    -> 		ename
    -> from
    -> 		emp
    -> where
    -> 		ename like 'S%';
+-------+
| ename |
+-------+
| SMITH |
| SCOTT |
+-------+
2 rows in set (0.03 sec)

找出以第二个字母

找出第二个字母是C的

mysql> select 
    -> 		ename
    -> from
    -> 		emp
    -> where
    -> 		ename like '_C%';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.03 sec)

找出以第三个字母

找出第三个字母是R的


mysql> select
    -> 		*
    -> from
    -> 		emp
    -> where
    -> 		ename like '__R%';
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000.00 | NULL    |     20 |
+-------+--------+----------+------+------------+---------+---------+--------+
4 rows in set (0.03 sec)

排序

升序

order by默认升序
加asc升序

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 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| KING   | 5000.00 |
+--------+---------+
14 rows in set (0.03 sec)

降序

加上desc关键字使它降序

mysql> select 
    -> 		ename,sal
    -> from
    -> 		emp
    -> order by
    -> 		sal 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 |
+--------+---------+
14 rows in set (0.04 sec)

多个字段排序

先按照一个字段排序，要是该字段一样，再按照另一个字段排序

mysql> select
    -> 		ename,sal
    -> from
    -> 		emp
    -> order by
    -> 		sal asc,ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| MARTIN | 1250.00 |
| WARD   | 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.04 sec)

sal在前，主导，当sal相等后，ename排序。

数据处理函数

转换小写函数lower

mysql> select
    -> 		lower(ename) as ename 
    -> from
    -> 		emp;
+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.04 sec)

转换大写upper

mysql> select
    -> 		upper(ename) name
    -> from
    -> 		emp;
+--------+
| name   |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+
14 rows in set (0.03 sec)

取子串substr

substr(被截取的字符串,起始下标，截取长度)
注意：下标从1开始，没有0

mysql> select
    -> 		ename
    -> from
    -> 		emp
    -> where
    -> 		substr(ename,1,1) = 'A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.03 sec)

查找员工第一个字母是A的员工

字符串拼接concat

mysql> select
    -> 		concat(empno,ename)
    -> from
    -> 		emp;
+---------------------+
| concat(empno,ename) |
+---------------------+
| 7369SMITH           |
| 7499ALLEN           |
| 7521WARD            |
| 7566JONES           |
| 7654MARTIN          |
| 7698BLAKE           |
| 7782CLARK           |
| 7788SCOTT           |
| 7839KING            |
| 7844TURNER          |
| 7876ADAMS           |
| 7900JAMES           |
| 7902FORD            |
| 7934MILLER          |
+---------------------+
14 rows in set (0.03 sec)

取长度length

mysql> select
    -> 		ename,length(ename) enamelength
    -> from
    -> 		emp;
+--------+-------------+
| ename  | enamelength |
+--------+-------------+
| SMITH  |           5 |
| ALLEN  |           5 |
| WARD   |           4 |
| JONES  |           5 |
| MARTIN |           6 |
| BLAKE  |           5 |
| CLARK  |           5 |
| SCOTT  |           5 |
| KING   |           4 |
| TURNER |           6 |
| ADAMS  |           5 |
| JAMES  |           5 |
| FORD   |           4 |
| MILLER |           6 |
+--------+-------------+
14 rows in set (0.03 sec)

去空格trim

select * from emp where ename = trim（‘ KING’）；

四舍五入round

mysql>  select round(1236.567, 0) as result from emp;
+--------+
| result |
+--------+
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
| 1237   |
+--------+
14 rows in set (0.04 sec)

保留1位小数

mysql> select
    -> 		round(1236.567,1)
    -> 		as result
    -> from
    -> 		emp;
+--------+
| result |
+--------+
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
| 1236.6 |
+--------+
14 rows in set (0.02 sec)

生成随机数

生成100以内的随机数

mysql> select
    -> 		round(rand()*100,0)
    -> from
    -> 		emp;
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  99 |
|                   9 |
|                  45 |
|                  99 |
|                  59 |
|                  98 |
|                  14 |
|                  78 |
|                  44 |
|                  89 |
|                  14 |
|                   2 |
|                  66 |
|                  26 |
+---------------------+
14 rows in set (0.03 sec)

空处理函数

ifnull 可以将null转换位具体值，因为在数据库中，只要有NULL参与数学运算，最终结果为NULL
空参与运算，结果为空：

mysql> select ename,sal+comm as salcomm from emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  | NULL    |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | NULL    |
| MARTIN | 2650.00 |
| BLAKE  | NULL    |
| CLARK  | NULL    |
| SCOTT  | NULL    |
| KING   | NULL    |
| TURNER | 1500.00 |
| ADAMS  | NULL    |
| JAMES  | NULL    |
| FORD   | NULL    |
| MILLER | NULL    |
+--------+---------+
14 rows in set (0.03 sec)

加空处理之后：

mysql> select
    -> 		ename,(sal + ifnull(comm,0)) as salcomm
    -> from
    -> 		emp;
+--------+---------+
| ename  | salcomm |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | 2975.00 |
| MARTIN | 2650.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 |
+--------+---------+
14 rows in set (0.03 sec)

分组函数（多行处理函数）

count 计数

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

最大值max

mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.02 sec)

最小值min

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

求和sum

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

求平均值avg

mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.02 sec)

分组查询

在实际应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。

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

以上关键字的顺序不能颠倒，需要记忆。
执行顺序是什么？
1. from
2. where
3. group by
4. select
5. order by

为什么分组函数不能直接使用在where后面？
select ename,sal from emp where sal > min(sal);//报错。
因为分组函数在使用的时候必须先分组之后才能使用。
where执行的时候，还没有分组。所以where后面不能出现分组函数。
select sum(sal) from emp;
这个没有分组，为啥sum()函数可以用呢？因为select在group by之后执行。
例：按照工作岗位分组，然后对工资求和。

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

以上这个语句的执行顺序？
先从emp表中查询数据。
根据job字段进行分组。
然后对每一组的数据进行sum(sal)

连接查询

内连接

内连接：A和B连接，AB两张表没有主次关系。平等的。

等值连接

条件是等量关系，称为等值连接
案例：查询每个员工所在部门名称，显示员工名和部门名？
emp e和dept d表进行连接。条件是：e.deptno = d.deptno

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

自连接

案例：查询员工的上级领导，要求显示员工名和对应的领导名？
技巧：一张表看成两张表。

mysql> select
    -> 		a.ename '员工名',b.ename '领导名'
    -> 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.05 sec)

外连接

在外连接当中，两张表连接，产生了主次关系。
带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

左连接

left代表什么：表示将join关键字左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表。

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

右连接

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。

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

三四张表的连接

mysql> select
    -> 		e.ename,e.sal,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  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+
14 rows in set (0.14 sec)

子查询

where子句的子查询

案例：找出比最低工资高的员工姓名和工资？
第一步：查询最低工资

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

第二步：找出大于800的

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

第三步：合并一二步

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

from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）
案例：找出每个岗位的平均工资的薪资等级。
第一步：找出每个岗位的平均工资（按照岗位分组求平均值）

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

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。

mysql> select
    -> 		*
    -> from
    -> 		salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.03 sec)

将上面两个表进行表连接

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

select后出现的子查询

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

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