mysql数据库（2）---单行函数、多行函数、分组查询【入门2】

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

-- 概念：数据库函数类似于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;