MySQL数据库学习笔记(二)

最新推荐文章于 2022-09-16 08:00:00 发布

耿*先生

最新推荐文章于 2022-09-16 08:00:00 发布

阅读量165

点赞数

本文链接：https://blog.csdn.net/weixin_52894626/article/details/112330440

版权

15、排序

15.1、查询所有员工薪资，排序

mysql> select ename,sal from emp order by sal;//默认升序
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)

15.2、怎么降序？指定降序

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 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+
14 rows in set (0.00 sec)

15.3、可以两个字段或按多个字段排序吗？

查询员工名字和薪资，要去按照薪资升序，如果薪资一样的话，再按照名字升序排列

mysql> select ename,sal from emp order by sal asc,ename asc;
// sal在前，起主导作用，只有sal相等的时候，才会考虑启用ename排序
+--------+---------+
| 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.00 sec)

15.4、了解——根据字段的位置也可以排序

mysql> select ename,sal from emp order by 2;//2表示第2列，第二列是sal.按照查询结果的第二列sal排序。
+--------+---------+
| 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)

16、综合案例

找出工资在1250到3000之间的员工信息，要求按照薪资降序排列

mysql> select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
//关键字顺序不能变：select...from...where...order by...
//以上语句的执行顺序必须掌握：第一步——from；第二部——where；第三步——select；第四步——order by（排序总是在最后执行！）
+--------+---------+
| ename  | sal     |
+--------+---------+
| 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 |
+--------+---------+
10 rows in set (0.00 sec)

————————————————————————————————————————

17、数据处理函数

17.1、数据处理函数又被称为单行处理函数

单行处理函数的特点是：一个输入对应一个输出

和单行处理函数相对于的是：多行处理函数（特点：多个输入对应一个输出）

17.2、单行处理函数常见的有哪些：

lower 转换小写

mysql> select lower(ename) as ename from emp;
//14个输入对应14个输出
+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+
14 rows in set (0.00 sec)

upper 转换大写

substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）

mysql> select substr(ename, 1, 1) as ename from emp;
//注意：起始下标从1开始，没有0
+-------+
| ename |
+-------+
| S     |
| A     |
| W     |
| J     |
| M     |
| B     |
| C     |
| S     |
| K     |
| T     |
| A     |
| J     |
| F     |
| M     |
+-------+
14 rows in set (0.00 sec)

找出员工名字的第一个字母是A开头的
方法1：模糊查询like
mysql> select ename from emp where ename like 'A%';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)
方法2：substr函数
mysql> select ename from emp where substr(ename,1,1) = 'A';
+-------+
| ename |
+-------+
| ALLEN |
| ADAMS |
+-------+
2 rows in set (0.00 sec)

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

length 取长度

mysql> select length(ename) enamelength from emp;
+-------------+
| enamelength |
+-------------+
|           5 |
|           5 |
|           4 |
|           5 |
|           6 |
|           5 |
|           5 |
|           5 |
|           4 |
|           6 |
|           5 |
|           5 |
|           4 |
|           6 |
+-------------+
14 rows in set (0.00 sec)

trim 去前后空白

mysql> select * from emp where ename = '   king';
Empty set (0.00 sec)//king前面多打了几个空格

mysql> select * from emp where ename = trim('   king');//使用trim函数把king前多打的空格给去了
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
+-------+-------+-----------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位

round 四舍五入

select 字段 from 表名;

select ‘abc’ from dept;//select后面直接跟字面量/字面值

mysql> select 'abc' as bieming from dept;
+---------+
| bieming |  ——列的名字
+---------+
| abc     |  ——原表中没有这些数据，只是借助原表的结构生成了这个表格
| abc     |
| abc     |
| abc     |
+---------+
4 rows in set (0.00 sec)
mysql> select '1000' as bieming from dept;
+---------+
| bieming |
+---------+
| 1000    |
| 1000    |
| 1000    |
| 1000    |
+---------+
4 rows in set (0.00 sec)
//结论：select后面可以跟某个表的字段名（可以看成变量名），可也以跟字面值/字面量（数据）

mysql> select round(1234.567,0) as result from dept;//0表示保留整数
+--------+
| result |
+--------+
|   1235 |
|   1235 |
|   1235 |
|   1235 |
+--------+
4 rows in set (0.00 sec)
mysql> select round(1234.567,1) as result from dept;//1表示保留一位小数
+--------+
| result |
+--------+
| 1234.6 |
| 1234.6 |
| 1234.6 |
| 1234.6 |
+--------+
4 rows in set (0.00 sec)
mysql> select round(1234.567,2) as result from dept;//2表示保留两位小数
mysql> select round(1234.567,-1) as result from dept;//-1表示保留到十位
+--------+
| result |
+--------+
|   1230 |
|   1230 |
|   1230 |
|   1230 |
+--------+
4 rows in set (0.00 sec)
mysql> select round(1234.567,-2) as result from dept;//-2表示保留到百位

rand() 生成随机数

mysql> select rand() from dept;
+---------------------+
| rand()              |
+---------------------+
| 0.11740309662875076 |
|  0.7494076208671626 |
| 0.39482884518320566 |
|  0.7259213940481855 |
+---------------------+
4 rows in set (0.01 sec)

mysql> select round(rand()*100,0) from dept;//100以内的随机数
+---------------------+
| round(rand()*100,0) |
+---------------------+
|                  45 |
|                   5 |
|                  90 |
|                  38 |
+---------------------+
4 rows in set (0.00 sec)

ifnull 可以将 null 转换成一个具体值

ifnull是空处理函数。专门处理空的。在所有数据库中，只要由null参与的数学运算，结果就是null

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

注意：null只要参加了数学运算，最终结果一定是null。为了避免这个现象，需要使用ifnull函数
ifnull函数的用法：ifnull(数据，被当作哪个值)；如果“数据”为null的时候，把这个数据结构当作哪个值

计算每个员工的年薪
年薪=（月薪+补助）*12
补助为null的时候，将补助当作0
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 |
+--------+----------+
14 rows in set (0.01 sec)

case …when…then…when…then…else…end 模拟if语句

当员工的工作岗位是MANAGER的时候，工资上调10%，当员工的工作岗位是SLALESMAN的时候，工资上调50%，其他正常。

注意（不修改数据库，只是将查询的结果显示为工资上调）

mysql> select ename,job,sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end)as newsal from emp;
+--------+-----------+---------+---------+
| ename  | job       | oldsal  | newsal  |
+--------+-----------+---------+---------+
| SMITH  | CLERK     |  800.00 |  800.00 |
| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
| WARD   | SALESMAN  | 1250.00 | 1875.00 |
| JONES  | MANAGER   | 2975.00 | 3272.50 |
| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
| CLARK  | MANAGER   | 2450.00 | 2695.00 |
| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
| KING   | PRESIDENT | 5000.00 | 5000.00 |
| TURNER | SALESMAN  | 1500.00 | 2250.00 |
| ADAMS  | CLERK     | 1100.00 | 1100.00 |
| JAMES  | CLERK     |  950.00 |  950.00 |
| FORD   | ANALYST   | 3000.00 | 3000.00 |
| MILLER | CLERK     | 1300.00 | 1300.00 |
+--------+-----------+---------+---------+
14 rows in set (0.00 sec)

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

特点：输入多行，最终输出一行

注意：分组函数在使用的时候必须先进行分组，然后才能用；如果没有对数据进行分组，整表张默认为一组

count 计数

sum 求和

avg 求平均值

max 求最大值

min 求最小值

计算最高工资
mysql> select max(sal) from emp;
+----------+
| max(sal) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.01 sec)

计算最低工资
mysql> select min(sal) from emp;
+----------+
| min(sal) |
+----------+
|   800.00 |
+----------+
1 row in set (0.00 sec)

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

计算平均工资
mysql> select avg(sal) from emp;
+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)

计算员工数量
mysql> select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
|           14 |
+--------------+
1 row in set (0.00 sec)

分组函数在使用的时候需要注意哪些？

1、分组行数自动忽略null,不需要对null进行提前处理

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

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

2、分组函数中count（*）和count（具体字段）有什么区别？

mysql> select count(*) from emp;
//count（*）：统计表中的总行数，只要有一行数据count则++
因为每一行数据不可能都为null，一行数据中有一列不为null，则该行数据就是有效的。
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

mysql> select count(comm) from emp;
//count（具体字段）：表示统计该字段下所有不为null的元素的总和
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

3、分组函数不能直接使用在where子句中——见19.2

找出比最低工资高的员工信息

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

4、所有的分组函数可以组合起来一起用

mysql> select sum(sal),avg(sal),min(sal),max(sal)from emp;
+----------+-------------+----------+----------+
| sum(sal) | avg(sal)    | min(sal) | max(sal) |
+----------+-------------+----------+----------+
| 29025.00 | 2073.214286 |   800.00 |  5000.00 |
+----------+-------------+----------+----------+
1 row in set (0.00 sec)

19、分组查询——重点*****

19.1、什么是分组查询？

在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作。这个时候我们需要使用分组查询，怎么进行分组查询呢？

select…from…group by…

计算每个部门的工资和？
计算每个工作岗位的平均薪资？
找出每个工作岗位的最高薪资？

…

19.2、将之前的关键字全部组合在一起，来看一下他们的执行顺序？

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

from

where

group by

select

order by

为什么分组函数不能直接使用在where后面？
select ename,sal from emp where sal > min(sal);//报错。
因为分组函数在使用的时候必须先分组之后才能使用。
where执行的时候，还没有分组。所以where后面不能出现分组函数。

19.3、找出每个工作岗位的工资和

实现思路：按照工作岗位分组，然后对工资求和

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

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

重点结论：在一条select语句当中，如果有group by 语句的话，select后面只能跟——参加分组的字段以及分组行数。其他的一律不能跟。

19.4、找出每个部门的做高工资？

实现思路：按照部门编号分组，求出每组的最大值

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

19.5、找出每个部门，不同工作岗位的最高薪资

mysql> select ename,job,sal,deptno from emp order by deptno;
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| MILLER | CLERK     | 1300.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| CLARK  | MANAGER   | 2450.00 |     10 |

| FORD   | ANALYST   | 3000.00 |     20 |
| ADAMS  | CLERK     | 1100.00 |     20 |
| SCOTT  | ANALYST   | 3000.00 |     20 |
| JONES  | MANAGER   | 2975.00 |     20 |
| SMITH  | CLERK     |  800.00 |     20 |

| BLAKE  | MANAGER   | 2850.00 |     30 |
| MARTIN | SALESMAN  | 1250.00 |     30 |
| TURNER | SALESMAN  | 1500.00 |     30 |
| WARD   | SALESMAN  | 1250.00 |     30 |
| JAMES  | CLERK     |  950.00 |     30 |
| ALLEN  | SALESMAN  | 1600.00 |     30 |
+--------+-----------+---------+--------+
14 rows in set (0.00 sec)


//技巧：两个字段联合看，两个字段联合分组
mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| 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 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

19.6、使用having可以对分完组的数据进一步过滤，having不能单独使用，必须与group by一起使用，having不能代替where

找出每个部门最高的薪资，要求显示最高薪资大于3000的？

第一步：找出每个部门最高薪资——按照部门编号分组，求每一组最大值。
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

第二部：要求显示最高薪资大于3000的
mysql> select deptno,max(sal) from emp group by deptno having max(sal) >3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

思考一个问题：以上的sql语句执行效率是不是低？
比较低，实际上可以这样考虑：先将大于3000的都找出来，然后再分组。

mysql> select deptno,max(sal)  from emp where sal >3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

//优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

19.7、where没办法的？？？？

找出每个部门平均薪资，要求显示平均薪资高于2500的。

//第一步：找出每个部门平均薪资
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)
//第二步：要求显示平均薪资高于2500的
mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

mysql>

20、大总结（单表的查询学完了）

select
…
from
…
where
…
group by
…
having
…
order by
…
以上关键字只能按照这个顺序来，不能颠倒。

执行顺序？

from

where

group by

having

select

order by
从某张表中查询数据，
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。
select查询出来。
最后排序输出！

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，
要求按照平均薪资降序排。
mysql> select
    -> job, avg(sal) as avgsal
    -> from
    -> emp
    -> where
    -> job <> 'MANAGER'
    -> group by
    -> job
    -> having
    -> avg(sal) > 1500
    -> order by
    -> avgsal desc;
+-----------+-------------+
| job       | avgsal      |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)