目录
一、数据处理函数
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()括号内无任何参数。
12)ifnull 将null转化为一个具体数值
语法:select 字段1,字段n,ifnull(数据/字段,被当作的数) from 表名;
适用:已知null参加的任意运算所得值终为null;
情景:mysql> select ename,sal * 12 + comm as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | NULL |
| ALLEN | 19500.00 |
| WARD | 15500.00 |
| JONES | NULL |
| MARTIN | 16400.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+
解决:mysql> select ename,sal * 12 + ifnull(comm,0) as yearsal from emp;
+--------+----------+
| ename | yearsal |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 19500.00 |
| WARD | 15500.00 |
| JONES | 35700.00 |
| MARTIN | 16400.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 |
+--------+----------+
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综合:
将员工首字母小写;
select ename from emp;//拿名字
select lower(substr(ename,1,1)) from emp;//取出第一个字母大写
select substr(ename,2,length(ename)-1) from emp;//取出名字的除开第一个字母的所有
mysql> select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename)-1)) as result from emp;
+--------+
| result |
+--------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+--------+
二、分组函数多行处理函数:
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:
语法1:select distinct ___(字段) from ___(表名);
mysql> select distinct job from emp;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
注释:distinct 需要写在所有字段前,如语法2;
语法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