数据库处理函数-- 单行函数、多行函数
-- 概念：数据库函数类似于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;
