mysql数据库：单行处理/多行处理/分组查询/去重（老杜）

不是周末

已于 2023-03-27 09:03:48 修改

阅读量249

点赞数

文章标签： mysql 数据库

于 2022-10-03 14:25:40 首次发布

本文链接：https://blog.csdn.net/m0_69364468/article/details/127151949

版权

一、数据处理函数

  1.1单行处理函数：
       释义：数据处理函数又称单行处理函数。
       单行特点：一个输入对应输出，一行处理完成后执行下一行，得多个结果。

   1.2单行常见处理函数：
       语法：select 函数（字段名） from 表名;
       01)lower           转换小写
           mysql> select lower (ename) as ename2,job from emp;

02)upper 转换大写
mysql> select upper(ename) as ename1,job from emp;

       03)substr       取子串（被截取的字符串，起始下标，截取长度）
           mysql> select substr(ename,1,1) as result from emp;
           注释：起始第一位从1开始，而非0
               +--------+
               | result |
               +--------+
               | S |
               | A |
               | W |
               | J |
               | M |
               | B |
               | C |
               | S |
               | K |
               | T |
               | A |
               | J |
               | F |
               | M |
               +--------+
           找出员工名字第一个字母为A的信息；
           法一、模糊查询：
           mysql>select ename from emp where ename like 'A%';
           注释：不要忘了where条件，以及引号打在最外而非只是字符串外面；
           法二、substr函数：
           mysql>select * emp where substr(ename,1,1)='A';

04)concat 字符串的拼接
mysql> select concat (empno,ename) from emp;

05)length 取长度
mysql> select length(ename),job from emp;

       06)trim           去空格
           使用场景：
           mysql> select job from emp where job=传过来的数据，活动的；
           mysql> select job from emp where job=trim(' clerk');
           mysql> select job from emp where job = trim(' clerk');

07)str_to_date 字符串转化为日期

08)date_format 格式化日期(日期转化为特定格式字符串)

09)format 设置千分位

       10)round       四舍五入
           补充知识：
           select ___ from 表名。
           a)___可以是字段名
           b)___可以是"字面量/字面值"（无原因）
               此时“生成”一列字面量。
           mysql> select 20 from emp;
           +----+
           | 20 |
           +----+
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           | 20 |
           +----+
           mysql> select 'abc' from dept;
           +-----+
           | abc |
           +-----+
           | abc |
           | abc |
           | abc |
           | abc |
           +-----+
           结论：字段名可等同看作（变量名），字面量/字面值看作（数据）

           语法round：
           round(数,保留位数)
           mysql> select round(2234.5423,1) from dept;//四舍五入保留1位
           +--------------------+
           | round(2234.5423,1) |
           +--------------------+
           | 2234.5 |
           | 2234.5 |
           | 2234.5 |
           | 2234.5 |
           +--------------------+
           mysql> select round(3145.543,-1) from dept;//保留到十位
           +--------------------+
           | round(3145.543,-1) |
           +--------------------+
           | 3150 |
           | 3150 |
           | 3150 |
           | 3150 |
           +--------------------+

       11)rand()       生成随机数(不是真正意义上的随机数)
           mysql> select rand() from dept;//生成0-1之间的随机数
           +--------------------+
           | rand() |
           +--------------------+
           | 0.1583118514700903 |
           | 0.818772075528998 |
           | 0.618924853968364 |
           | 0.6383080739884712 |
           +--------------------+
           mysql> select round(rand()*100,0) from dept;//生成100以内的随机数
           +---------------------+
           | round(rand()*100,0) |
           +---------------------+
           | 33 |
           | 76 |
           | 79 |
           | 67 |
           +---------------------+
           注释：rand()括号内无任何参数。

       13）case..when..then..when..then..else..end
           注释：select 再次声明不修改数据库，只是将显示结果显示情况调整；
               这里的括号与其他函数有差别，需要囊括函数本身；
           当员工工作岗位是MANAGER时，工资上调10%；工作岗位时salesman时，工资上调50%；
           语法错误：
           mysql> select
               -> ename,job,sal as oldsal,
               -> case job when 'manager' then sal*1.1 as newsal when 'salesman' then sal*1.5 as newsal else sal as newsal end
               -> from
               -> emp;
           语法修改：
           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 |
           +--------+-----------+---------+---------+

   1.3综合：

二、分组函数多行处理函数：

   2.1多行处理函数：
           释义：分组处理函数又称多行处理函数。
           多行特点：多个输入，对应一个输出，多行处理后得到一个结果。
           特点：多行处理函数与单行处理函数相对应。
           注释：分组函数需先分组，才可使用；
               若未分组，则默认省略group by整张表分为一组。

   2.2多行常见处理函数（5个）
           sum       求和
           count   计数
           avg       平均
           max       最大值
           min       最小值

   2.2.1使用
       找工资和：
       mysql> select sum(sal) from emp;
           +----------+
           | sum(sal) |
           +----------+
           | 29025.00 |
           +----------+
       计算员工数量：
       mysql> select count(ename) from emp;
           +--------------+
           | count(ename) |
           +--------------+
           | 14 |
           +--------------+
       计算平均工资：
       mysql> select avg(sal) from emp;
           +-------------+
           | avg(sal) |
           +-------------+
           | 2073.214286 |
           +-------------+
       找工资最大值：
       mysql> select max(sal) from emp;
           +----------+
           | max(sal) |
           +----------+
           | 5000.00 |
           +----------+
       找工资最小值：
       mysql> select min(sal) from emp;
           +----------+
           | min(sal) |
           +----------+
           | 800.00 |
           +----------+

2.2.3注意事项：

1)分组函数自动忽略null，null是无，而不是值0，不需要提前处理，不参加运算；

       2) count(*)统计表的行数；//只要存在数据库内，则不会发生整行全空的情况，至少一行内有一列数据；
       count(字段)统计不为null的行数;
           mysql> select count(*) from emp;
           +----------+
           | count(*) |
           +----------+
           | 14 |
           +----------+
           mysql> select count(comm) from emp;
           +-------------+
           | count(comm) |
           +-------------+
           | 4 |
           +-------------+

       3)分组函数不可放置于where语句中使用；
           案例：查询大于最小值的员工工资；
           错误：select sal from emp where sal > min(sal);
           ？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？？
           分组函数不可放在where后；
           解释：分组函数放在了where之后，而分组函数需要先分组才能够使用，where执行时，还还未分组
           下一问题：

       4)分组函数可以全部同时使用；
           mysql> select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
           +----------+----------+----------+-------------+----------+
           | sum(sal) | min(sal) | max(sal) | avg(sal) | count(*) |
           +----------+----------+----------+-------------+----------+
           | 29025.00 | 800.00 | 5000.00 | 2073.214286 | 14 |
           +----------+----------+----------+-------------+----------+
           注释：中英括号容易切换错误

三、分组查询(☆☆☆☆☆)

3.1语法：select.....from.....group by.....(having)
注释：having不是必要项，对group by 进一步筛选，having使用时必须与group by 连用；

   3.2使用场景
       实际开法中需要进行分组后对每组数据进行操作，使用分组查询。
       计算每个部门的工资和？
       计算每个部门的平均工资？
       找出每个工作岗位的最高薪资？

   3.3关键字执行顺序
       关键字的书写顺序：
       1)select
       2)from
       3)where
       4)group by
       5)order by
       ！不可颠倒
       ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
       执行：
       1)from
       2)where
       3)group by
       4)select
       5)order by
       回到？？？？？？？？？？？？？？
       情景一分析：where sal>min(sal)的结构明显违反分组group by执行在前where在后的结构，此处执行顺序是先where后group by,执行where时此时未分组。
               where中包括了min(sal)一整块：
               未分组原因--负责分组的函数group by 执行顺序在where层级之后，现在还在where层级，还未执行到group by层级；
               where层包括了（sal>min(sal)）一整块；
               此时where层级需要结束进行下一层级就需要结束其中包含的分组函数；
               但由于where层级未结束因此不可开启分组函数层级的执行；
               因此陷入僵持；
       情景二分析：select min(sal) from emp;的结构满足select时（执行顺序：group by在语法执行结构中已经结束）
       注释：不要混淆group by 与五个分组函数

3.4案例

       3.4.1按照工作岗位分组，对工资求和：
           思路： sum(sal)---group by job
           注释：有group by函数的时候，select后面只能跟分组字段（job）以及执行函数（sum）;
               此时select后面添加的其他字段（ename,empno）没有意义，但能算；
               oracal或其他数据库软件会报错，更加严谨；
           错误操作：
               mysql> select ename,empno,job,sum(sal) from emp group by job;
               +-------+-------+-----------+----------+
               | ename | empno | job | sum(sal) |
               +-------+-------+-----------+----------+
               | SMITH | 7369 | CLERK | 4150.00 |
               | ALLEN | 7499 | SALESMAN | 5600.00 |
               | JONES | 7566 | MANAGER | 8275.00 |
               | SCOTT | 7788 | ANALYST | 6000.00 |
               | KING | 7839 | PRESIDENT | 5000.00 |
               +-------+-------+-----------+----------+
           订正：mysql> select job,sum(sal) as sals from emp group by job;
               +-----------+---------+
               | job | sals |
               +-----------+---------+
               | CLERK | 4150.00 |
               | SALESMAN | 5600.00 |
               | MANAGER | 8275.00 |
               | ANALYST | 6000.00 |
               | PRESIDENT | 5000.00 |
               +-----------+---------+

       3.4.2查找每个部门的最高薪资：
           思路：max(sal)---group by deptno
               mysql> select deptno,max(sal) as max from emp group by deptno;
               +--------+---------+
               | deptno | max |
               +--------+---------+
               | 20 | 3000.00 |
               | 30 | 2850.00 |
               | 10 | 5000.00 |
               +--------+---------+
           注释：所选字段需要包含在表格当中。
           错误：mysql> select dname,max(sal) as max from emp group by dname;

       3.4.3查询每个部门，不同岗位的最高薪资：
           我的思路：max(sal),group by(deptno,job )
           思路：select
                   depton,job,max(sal)---> 2)group by 有的字段拿到select后，max函数
               from
                   emp
               group by
                   depton,job--------------> 1)两个字段联合看作一个字段

       3.4.4查询每个部门最高薪资，要求显示最高薪资大于3000的：
           我的思路：group by depton--->select max(sal) --->where sal > 3000
           思路：按照部门编号求每一组最大值
               select depton,max(sal) from emp group by depton;
               要求薪资大于3000
               select
                  deptno,max(sal)
               from
                   emp
               group by
                   depton
               having
                   max(sal) >3000;
           不同点：我的思路--先筛选出所有工资中大于3000的。
               思路-------筛选出最高工资后，留大于3000。
               都正确：思路一省略步骤没有浪费，目前更优。
           总结：where与having优先选择where。

       3.4.5where无法完成的：
           查询每个部门平均薪资，要求显示平均薪资高于2500的
           我的思路：group by depton---avg(sal)---having avg(sal) > 2500
           注释：不可使用where avg(sal)>2500；
           原因：涉及到先分组再查询的函数执行顺序问题。
           思路：找出部门平均薪资：
               select depton,avg(sal) from emp group by depton;
               要求显示平均薪资高于2500的
               select
                   deptno,avg(sal)
               from
                   emp
               group by
                   depton
               having
                   avg(sal) > 2500;

四、总结--单表结束（关于分组查询）

   4.1函数关键字
       select
       from
       where
       group by
       having
       order by

   4.2执行顺序
       from
       where
       group by
       having
       select
       order by
       查询某张表的数据：
       首先where锁定查询范围--其次group by对查询数据进行分组--接着having进一步筛选--select查询出来--最后order by排序输出

   4.3综合案例
       找出每个岗位的平均薪资，要求显示出平均薪资大于1500的，除manager之外，按照平均薪资降序排列：
       我的思路：group by job--select job,avg(sal)---where job <>'manager'--having avg(sal) > 1500--order by avg(sal) desc
       思路：同上
       完善：部门与岗位需要分清；avg(sal)可以重命名；order by 的格式没记清；desc 与 asc--降序与升序；
       mysql> select job,avg(sal) from emp where job<>'manager' group by job having avg(sal) >1500 order by avg(sal) desc;
       +-----------+-------------+
       | job | avg(sal) |
       +-----------+-------------+
       | PRESIDENT | 5000.000000 |
       | ANALYST | 3000.000000 |
       +-----------+-------------+

五、查询结果去重distinct：

       语法2：select distinct___(字段1),___(字段2) from___(表名);
           mysql> select distinct job,sal from emp;
           +-----------+---------+
           | job | sal |
           +-----------+---------+
           | CLERK | 800.00 |
           | SALESMAN | 1600.00 |
           | SALESMAN | 1250.00 |
           | MANAGER | 2975.00 |
           | MANAGER | 2850.00 |
           | MANAGER | 2450.00 |
           | ANALYST | 3000.00 |
           | PRESIDENT | 5000.00 |
           | SALESMAN | 1500.00 |
           | CLERK | 1100.00 |
           | CLERK | 950.00 |
           | CLERK | 1300.00 |
           +-----------+---------+
       注释：此时去重部分为字段1、2共同重复的

       错误写法：select ___(字段1)，distinct（字段2） from ____(表名)；
           mysql> select sal,distinct job from emp;
           ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1