数据库处理函数-- 单行函数、多行函数

-- 概念:数据库函数类似于Java中的方法,可以接受一个或多个参数,在函数内部完成计算,并且最终返回结果

-- 使用函数的注意事项:

  -- 1,使用函数的时候是把查询结果当作参数输入给函数,所以函数的执行结果并不会影响表中的数据

  -- 2,函数并不是通用的,例如:有的函数在mysql中可以执行,但是在oracle中无法执行

 

-- 函数的分类(Java中没有类似的划分方式)

  -- 1,单行函数:每次将单条记录做参数输入给函数,得到单行记录对应的单行结果

  -- 2,多行函数:每次将多条记录当作参数输入给函数,等到多行记录对应的当行结果

  -- (如:查询最高工资,要把所有工资一次性输入给函数,最终得到所有公工资对应的最高的一个工资)

 

-- 【【1】】单行函数:每次将单条记录做参数输入给函数,得到单行记录对应的单行结果

 

-- 【1.1】LOWER(str)把字符串数据转换为小写的字符串

-- 查询员工的名字并且将其名字转换为小写的字符

SELECT *

FROM emp

WHERE BINARY LOWER(ename) = "saasab";

 

-- 【1.2】UPPER(str)把字符型数据转换为大写字符

SELECT *

FROM emp

WHERE BINARY ename = UPPER("sawece");-- 把小写的'sawece'转换为大写字符串,结果是"SAWECE",然后再去和ename进行比较

 

-- 【1.3】SUBSTR(被截取字段名称,起启下标,截取长度) 截取字符串的函数

-- 1,从员工名字的第二个字符开始截取一个字符的长度

SELECT ename "原来的名字" , SUBSTR(ename,2,1) "截取后名字"

FROM emp;

-- 2,查询名字的第二个字符是A的员工

SELECT *

FROM emp

WHERE SUBSTR(ename,2,1) = "A";

-- 3,查询条件也可以使用模糊查询

SELECT *

FROM emp

WHERE ename LIKE "_A%";

 

-- 【1.4】LENGTH(str)返回字符型数据的长度

-- 1,查询员工的名字及其名字的长度

SELECT ename "员工的名字",LENGTH(ename) "名字的长度"

FROM emp;

-- 2,查询名字的长度是5的员工

SELECT *

FROM emp

WHERE LENGTH(ename) = 5;

 

-- 【1.5】TRIM()去除字符型数据中首尾的空格,但是不会去除中间的空格

-- 更新数据

UPDATE emp SET ename=" DFS D F " WHERE empNo=1238;

-- 单行函数可以嵌套,首先执行里面的函数,然后把里面的函数结果当作外面函数的参数

SELECT ename,LENGTH(ename) "原来的长度",TRIM(ename) "去除首尾空格之后的名字",LENGTH(TRIM(ename)) "去除首尾空格后名字的长度"

FROM emp

WHERE empNo = 1238;

-- 恢复数据

UPDATE emp SET ename="DFSDF" WHERE empNo=1238;

 

-- 【1.6】ROUND()对象数值型数据进行四舍五入操作的函数

-- 查询emp表,sal字段的类型是double(7,2) 表示数值的总长度是7位,小数点占两位

DESC emp;

-- 测试double(7,2)这种类型;更新的数据是736.686小数点后三位超出了小数点两位的限制

-- 数据在储存会自动的四舍五入到小数点的后两位

UPDATE emp SET sal=136.686 WHERE empNo=1238;

-- 1,查询1238的工资,精确到不同的位数

SELECT empNo,ename,sal "原始的工资",ROUND(sal) "默认的精确到个位的工资",
                   ROUND(sal,0) "精确到小数点后0位(也就是各位)的工资",
                   ROUND(sal,1) "精确到小数点后1位的工资" ,
                   ROUND(sal,-1) "精确到小数点后-1位(也就是十位)的工资"  
FROM emp
WHERE empNo = 1238;

-- 2,把168.687这个常数精确到不同的位数

-- 下面查询语句中没有from,系统表可以忽略,使用这种方式操作常量;而有from,实际上这是从数据库的系统表进行相关的查询

SELECT 176.687,ROUND(176.687) "默认的精确到个位",
          ROUND(176.687,1) "默认精确到小数最后一位的数值",
          ROUND(176.687,-1) "精确到个位的数值";

 

-- 【1.7】RAND() 产生0-0.9999之间随机小数的函数

-- 1,产生1-100的随机整数

SELECT ROUND(RAND()*99+1); -- 0-0.9999>>0-98.9999+1>>1-99.9999>>四舍五入>>100

 

-- 【1.8】IFNULL()函数,专门用来处理null的函数

-- NULL是数据库中的一种特殊的数据,表示没有数据;在运算中如果出现NULL,结果肯定是NULL

-- 1,查询员工的年收入,年收入=(月薪+津贴)*12

SELECT empNo,ename,sal,comm,(sal+IFNULL(comm,0))*12 AS "年收入"

FROM emp;

 

-- 【1.9】CASE WHEN函数,执行分支语句的函数

-- 1,匹配工作岗位,当为"A"十,薪水上调10%,当为"B"时薪水上调50%,其他岗位薪水不变

-- 分析:判断职位,如果职位是"A",则返回sal*1.1;如果职位为"B",则返回sal*1.5;否则慧姐返回sal

SELECT empNo,ename,job,sal AS "原来的薪水",(CASE job WHEN "A" THEN sal*1.1 WHEN "B" THEN sal*1.5 ELSE sal END) AS "newsal"

FROM emp;

 

 

-- 【【2】】多行函数

-- 概念:每次将多条记录当做参数,输入给函数得到多条记录对应的单行结果;多行函数也称为分组函数,也成为组函数,也成为聚合函数

-- 使用多行函数的注意事项:

-- 1,多行函数会自动的忽略空值(NULL),不需要手动用WHEN排除空值

  -- 2,多行函数不能出现在WHERE子句中

  -- 3,多行函数不能嵌套

 

-- 【2.1】SUM() 对数值求总和

-- 1,计算员工的工资总和

SELECT SUM(sal) "SUMsal" FROM emp;

-- 2,查询emp表的津贴总和(即把不为null的数值相同)

-- 提示:因为多行函数会自动忽略空值(null)

SELECT SUM(comm) FROM emp;

-- 3,查询emp表中月收入(月收入=sal+comm)总和

SELECT SUM(sal)+SUM(comm) "total1",SUM(sal + IFNULL(comm,0)) "total2" FROM emp;

 

-- 【2.2】COUNT()函数 查询数据总数

-- COUNT() 函数使用方式

-- 1,COUNT(*):查询所有的记录总数

-- 2,COUNT(字段):查询指定字段不为null的数据总数

-- 1,查询emp表的数据总数

SELECT COUNT(*) FROM emp;

-- 2,查询津贴不为null的数据总数

SELECT COUNT(comm) FROM emp;

 

-- 【2.3】AVG() 查询平均值的AVG()函数,只能针对数值型的数据

-- 1,查询emp表的平均工资

SELECT AVG(sal) SVGsal,SUM(sal)/COUNT(*) AVGsal2 FROM emp;

-- 2,查询emp表的平均津贴

SELECT AVG(comm) AVGcomm,SUM(comm)/COUNT(comm) AVGcomm1 FROM emp;

 

-- 【2.4】MAX() 查询最大值的MAX()函数,适用三种数据类型(数值型数据,日期型数据,字符型数据)

-- 1,查询emp表的最大工资

SELECT MAX(sal) MAXsal FROM emp;

-- 2,查询最大的雇佣日期

-- 日期型数据大小由其字面值决定;越晚日期越大;如"2010"小于"2018"

SELECT MAX(hiredate) FROM emp;

-- 3,查询最大的名字

-- 字符型数据也有小大;其大小和Java中字符大小一样;由Unicode编码决定;先比较第一个再比较后面的

SELECT MAX(ename) FROM emp;

 

-- 【2.5】MIN() 查询最小值的MAX()函数,适用三种数据类型(数值型数据,日期型数据,字符型数据)

-- 1,查询emp表的最小工资

SELECT MIN(sal)MINsal FROM emp;

-- 2,查询最小的雇佣日期

-- 日期型数据大小由其字面值决定;越晚日期越大;如"2010"小于"2018"

SELECT MIN(hiredate) FROM emp;

-- 3,查询最小的名字

-- 字符型数据也有小大;其大小和Java中字符大小一样;由Unicode编码决定;先比较第一个再比较后面的

SELECT MIN(ename) FROM emp;

 

-- 【【3】】使用DISTINCT去除查询结果中的重复的记录

 -- DISTINCT的可以去除单列重复的数据,也可以去除多列重复得数据

    -- DISTINCT只能出现在SELECT之后,查询列表之前

 

-- 【3.1】,使用DISTINCT去除单列重复的数据

-- 查询员工所从事的职位

SELECT DISTINCT job FROM emp;

-- 【3.2】,使用DISTINCT去除多列重复数据

-- 查询员工的职位和部门编号

SELECT DISTINCT dadtno,job

FROM emp

ORDER BY dadtno;

 

-- 【【4】】分组查询

-- 什么是分组查询?把数据按照特定的条件划分为多组,然后分别使用组函数进行查询;把数据划分多少组最终就会得到多少条结果

-- 例如:把所有的员工按照部门编号分组将分为四组,然后查询每组的最高工资,得到四条记录

-- 分组查询需要使用GROUP BY子句

-- 分组的时候可以单个字段分组,也可以多个字段分组

 

/*分组查询的语句

SELECT 查询列表

FROM 表名

GROUP BY 分组字段

*/

 

-- 分组查询的规则:

-- 1,出现在查询列表中的字段,要么出现在组函数中,要么出现在GROUP BY子句中;这是首选方式

-- 2,或者分组字段仅仅出现在GROUP BY子句中;尽量不要使用这种方式

-- 【4.1】,安照单个字段进行分组

-- 把所有的员工按照部门编号分组查询最高工资

-- 分析:出现在查询列表中有两个字段,dadtno和sal;sal出现在组函数中;dadtno出现在GROUP BY子句中,这是符合规则

SELECT dadtno,MAX(sal) MAxsal

FROM emp

GROUP BY dadtno;

-- 2,分析:分组字段dadtno仅仅出现在GROUP BY子句中,也符合规则

SELECT MAX(sal)

FROM emp

GROUP BY dadtno;



-- 3,按照职务分组查询最高,最低,平均工资和最大雇佣日期

-- 分析:出现在查询列表中的字段有job,sal,hiredate;sal,hiredate出现在组函数中;job出现在GROUP BY子句中,也符合规则

SELECT job,MAX(sal) MAXsal,MIN(sal) MINsal,AVG(sal) AVGsal,MAX(hiredate) MAXdate

FROM emp

GROUP BY job;



-- 4,按照部门编号分组,查询最高工资及其最高工资的员工名字

-- 分析:出现在查询列表中的ename,即没有出现在组函数中,也没有出现在GROUP BY子句中,这是不符合规则

-- 这样的语句在oracle无法执行;在mysql可以执行,但查询结果是错误的

SELECT dadtno,MAX(sal) MAXsal,ename

FROM emp

GROUP BY dadtno;

 

-- 【4.2】,按照多个字段进行分组

 

-- 1,按照部门编号和职位进行分组查询最高工资

-- 分析:出现在分组查询列表中三个字段,dadtno,job,sal;sal出现在组函数中;dadtno和job出现在GROUP BY子句中,这是符合规则

SELECT dadtno,job,MAX(sal) MAXsal

FROM emp

GROUP BY dadtno,job;

 

-- 【4.3】,对分组之后的数据进行过滤,不能使用WHERE子句只能使用HAVING子句

-- 按照部门编号分组,查询平均工资;并且显示平均工资大于2000的记录

-- 按照之前查询结果过滤需要使用WHERE子句,但下面的语句是错误的,因为多行函数不能出现在WHERE子句中(错误写法)

SELECT dadtno,AVG(sal) AVGsal

FROM emp

GROUP BY dadtno

WHERE AVG(sal)>2000;

-- (正确的写法)多分组数据进行过滤,需要使用HAVING子句

SELECT dadtno,AVG(sal) AVGsal

FROM emp

GROUP BY dadtno

HAVING AVG(sal)>2000;