数据处理函数
单行处理函数
单行处理函数：一个输入对应一个输出
select lower(ename) from emp;
-
upper 转换大写 -
substr 取子串
-
substr(被截取的字符串,起始下标, 截取的长度) -
注意起始下标是从1开始 -
找出员工名字第第二个字母是A的员工信息？ 第一种方法：模糊查询
select ename from emp where ename like '_a%';
第二种方法：substr函数
select ename from emp where substr(ename,2,1)='a';
-
concat 对字符串进行拼接 mysql> select concat(ename,sal) from emp;
+-------------------+
| concat(ename,sal) |
+-------------------+
| SMITH800.00 |
| ALLEN1600.00 |
| WARD1250.00 |
| JONES2975.00 |
| MARTIN1250.00 |
| BLAKE2850.00 |
| CLARK2450.00 |
| SCOTT3000.00 |
| KING5000.00 |
| TURNER1500.00 |
| ADAMS1100.00 |
| JAMES950.00 |
| FORD3000.00 |
| MILLER1300.00 |
+-------------------+
14 rows in set (0.00 sec)
length 取长度
mysql> select length(sal) from emp;
+-------------+
| length(sal) |
+-------------+
| 6 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 6 |
| 7 |
| 7 |
+-------------+
14 rows in set (0.00 sec)
mysql> select length(ename) from emp;
+---------------+
| length(ename) |
+---------------+
| 5 |
| 5 |
| 4 |
| 5 |
| 6 |
| 5 |
| 5 |
| 5 |
| 4 |
| 6 |
| 5 |
| 5 |
| 4 |
| 6 |
+---------------+
14 rows in set (0.00 sec)
select ename from emp where ename=trim(' king');
-
case…when…then…when…then…else…end
注意case…when…then…when…then…else…end是一个整体，是一个字段
当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。 select ename,sal,case job when'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end as newsal from emp;
-
round 四舍五入 默认保留到个位数 round（数据，1）保留到小数点后一位数 round（数据，0）保留到个位数 round（数据，-1）保留到十位数 其他的以此类推 -
rand() 生成随机数 0~1之间的小数 随机生成100以内的随机整数
select round(rand()*100)from emp;
+-------------------+
| round(rand()*100) |
+-------------------+
| 83 |
| 61 |
| 55 |
| 95 |
| 11 |
| 68 |
| 7 |
| 32 |
| 39 |
| 99 |
| 79 |
| 97 |
| 49 |
| 53 |
+-------------------+
14 rows in set (0.00 sec)
-
ifnull 可以将 null 转换成一个具体值
-
在所有的数据库当中，有null参与的数学运算，结果都为null -
ifnull函数的用法：ifnull（数据（如果这个数据为null，则会替换），替换的值） 当补助为null，将补助视为0
mysql> select ifnull(comm,0) from emp;
+----------------+
| ifnull(comm,0) |
+----------------+
| 0.00 |
| 300.00 |
| 500.00 |
| 0.00 |
| 1400.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
| 0.00 |
+----------------+
14 rows in set (0.00 sec)
-
format 数字格式化 格式化数字：format(数字, '格式')
select ename,format(sal, '$999,999') as sal from emp;
+
| ename | sal |
+
| SMITH | 800 |
| ALLEN | 1,600 |
| WARD | 1,250 |
| JONES | 2,975 |
| MARTIN | 1,250 |
| BLAKE | 2,850 |
| CLARK | 2,450 |
| SCOTT | 3,000 |
| KING | 5,000 |
| TURNER | 1,500 |
| ADAMS | 1,100 |
| JAMES | 950 |
| FORD | 3,000 |
| MILLER | 1,300 |
+
分组函数
分组函数也是多行处理函数：输入多行，输出一行
- count 计数
- sum 求和
- avg 求平均值
- max 求最大值
- min 求最小值
注意：分组函数在使用之前，一定要进行分组，然后才能使用
如果没有对数据进行分组，会默认整张表为一组
找出最高工资？
select max(sal) from emp;
+----------+
| max(sal) |
+----------+
| 5000.00 |
+----------+
找出最低工资？
select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
计算工资和：
select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
计算平均工资：
select avg(sal) from emp;
+-------------+
| avg(sal) |
+-------------+
| 2073.214286 |
+-------------+
计算员工数量？
select count(ename) from emp;
+--------------+
| count(ename) |
+--------------+
| 14 |
+--------------+
-
分组函数注意点
-
分组函数会自动忽略null，不需要对null提前处理 select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+##而不是null
-
count（*）和count（具体字段）区别： select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+
select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count(*)：统计表当中的总行数。（只要有一行数据count则++）
因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。 -
分组函数不能放在where后面 找出比最低工资高的员工信息。
select ename,sal from emp where sal > min(sal);
ERROR 1111 (HY000): Invalid use of group function
因为：where语句总是在group by 语句前面执行
分组查询
-
分组查询基本语法 select ······ from ······ group by ······ 找出每个工作岗位的工资和？
select deptno,sum(sal) from emp group by deptno;
以上这个语句的执行顺序？
先从emp表中查询数据。
根据job字段进行分组。
然后对每一组的数据进行sum(sal)
select ename,job,sum(sal) from emp group by job;
+-------+-----------+----------+
| ename | job | sum(sal) |
+-------+-----------+----------+
| SCOTT | ANALYST | 6000.00 |
| SMITH | CLERK | 4150.00 |
| JONES | MANAGER | 8275.00 |
| KING | PRESIDENT | 5000.00 |
| ALLEN | SALESMAN | 5600.00 |
+-------+-----------+----------+
以上语句在mysql中可以执行，但是毫无意义。
以上语句在oracle中执行报错。
oracle的语法比mysql的语法严格。（mysql的语法相对来说松散一些！）
重点结论：
在一条select语句当中，如果有group by语句的话，
select后面只能跟：参加分组的字段，以及分组函数。
其它的一律不能跟。
找出每个工作岗位的工资和？
技巧：两个字段联合成1个字段看。（两个字段联合分组）
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 |
+--------+-----------+----------+
-
以下关键字执行顺序 select
...
from
...
where
...
group by
...
having
...
order by
...
以上关键字只能按照这个顺序来，不能颠倒。
执行顺序？
1. from
2. where
3. group by
4. having
5. select
6. order by
-
having
使用having可以对分完组之后的数据进一步过滤。
having不能单独使用，having不能代替where
having必须和group by联合使用。
找出每个部门最高薪资，要求显示最高薪资大于3000的？
方法1：
select deptno,max(sal) from emp group by deptno having max(sal)>3000;
方法2：
select deptno,max(sal) from emp where sal>3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000.00 |
+--------+----------+
优化策略：
where和having，优先选择where，where实在完成不了了，再选择
having。
|