DQL语言进阶2

目录

进阶三：排序查询 order by

语法:①select 查询列表②from 表③【where 筛选条件】④order by 排序列表 【asc|desc】执行顺序：②③①④

特点:

注意：

 #案例5:按姓名的长度显示员工的姓名和工资【按函数排序】 

 #案例6:查询员工信息，要求先按工资降序，再按员工编号升序【按多个字段排序】

#进阶4:常见函数

好处:

特点:

分类:

一、字符函数

#1.length获取参数值的字节个数

 #2.concat 拼接字符串

#3.upper、 lower  大写，小写

​编辑#4.substr、substring   截取字符

 #5.instr 返回子串第一次出现的索引，如果找不到返回0

 #6.trim  trim一般都是用在删除字符串两边的空格。实际上，trim也可以用来删除字符串两边的指定字符。

 #7.lpad 用指定的字符实现左填充指定长度

#8. rpad 用指定的字符实现右填充指定长度

#9.replace替换

-------------------------------------------------------------------------二、数学函数

--  round: 四舍五入

-- ceil: 向上取整，返回>=该参数的最小整数

 -- floor: 向下取整，返回<=该参数的最大整数

-- truncate: 截断小数点后位数

--  mod: 取余/取模

三、日期函数    now: 当前系统日期+时间    curdate: 当前系统日期    curtime: 当前系统时间    str_to_date: 将字符转换成日期    date_format: 将日期转换成字符

#now 返回当前系统日期+时间

#curdate 返回当前系统日期，不包含时间

#curtime 返回当前时间，不包含日期

#可以获取指定的部分， 年、月、日  时分秒

#MONTHNAME 将月份以英文输出

四、其他函数    version: 版本    database: 当前库    user: 当前连接用户

五、流程控制函数   if 处理双分支

 case语句 处理多分支

-- 1.if承数:if else 的效果

-- 2.case函数的使用一:switch case的效果

#3.case 函数的使用二:买似于多重if

#案例: 查询员工工资情况,显示级别

 #案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

#案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

 #案例:按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

​编辑

 #按多个字段分组#案例:查询每个部门每个工种的员工的平均工资

---

进阶三：排序查询 order by

语法:
①select 查询列表
②from 表
③【where 筛选条件】
④order by 排序列表 【asc|desc】
执行顺序：②③①④

特点:

1.asc代表的是升序,desc代表的是降序
如果不写，默认是升序
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面,limit子句除外

注意：

  * 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件

#案例1:查询员工信息,要求工资从高到低排序（降序）
SELECT * FROM employees ORDER BY salary DESC;

#案例2:查询部门编号>=90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT *
FROM employees
WHERE department_id>=90 
ORDER BY hiredate ASC;

 #案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC; 

  #案例4:按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC; 

 #案例5:按姓名的长度显示员工的姓名和工资【按函数排序】 

SELECT LENGTH(last_name) 字节长度,last_name,salary FROM employees
ORDER BY LENGTH(last_name) DESC;

 #案例6:查询员工信息，要求先按工资降序，再按员工编号升序【按多个字段排序】

SELECT *
FROM employees
ORDER BY salary DESC, employee_id ASC;

#进阶4:常见函数

概念:类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处:

1、隐藏了实现细节

2、提高代码的重用性
调用: select函数名(实参列表)【from表】;

特点:

①叫什么(函数名)

②干什么(函数功能)

分类:

1、单行函数
如concat、 length、 ifnull等(主要是处理，有返回值)

2、分组函数
功能:做统计使用，又称为统计函数、聚合函数、组函数
 

一、字符函数

#1.length获取参数值的字节个数

SELECTLENGTH('john " ) ;   
SELECTLENGTH《"张三丰hahaha' ) ;

SHOW VARIABLES LIKE ' %char%' 

 #2.concat 拼接字符串

SELECT CONCAT(last_name, '_',first_name) 姓名 FROM employees;

#3.upper、 lower  大写，小写

SELECT UPPER('john');
SELECT LOWER('joHn');
#示例:将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

#4.substr、substring   截取字符

-- 注意：索引从1开始截取
#截取从指定索引处后面所有字符
SELECT SUBSTR('阿珍爱上了阿强',6) out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('阿珍爱上了阿强',1,2) out_put;

#案例:姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2))) out_put FROM employees;
 

 

 #5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('阿珍爱上了阿强','阿强') AS out_put;

 #6.trim  trim一般都是用在删除字符串两边的空格。实际上，trim也可以用来删除字符串两边的指定字符。

SELECT TRIM('          Ombra      ') AS out_put;

 #7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD('Alessio',12,'* ' )AS out_put;

#8. rpad 用指定的字符实现右填充指定长度

SELECT RPAD('Ombra',12,'$' ) AS out_put;

 

#9.replace替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')AS out_put;

 

-------------------------------------------------------------------------
二、数学函数

--  round: 四舍五入

SELECT ROUND(-1.52);   #-2
SELECT ROUND(-1.25);   #-1

-- ceil: 向上取整，返回>=该参数的最小整数

 SELECT CEIL(-1.23);   -- -1
 SELECT CEIL(1.23);    -- 2

 
-- floor: 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-1.23);    -- -2
SELECT FLOOR(1.23);    -- 1

-- truncate: 截断小数点后位数

SELECT TRUNCATE(6.96999,2);   #6.96
SELECT TRUNCATE(696999,2);    #696999

--  mod: 取余/取模

# mod(a,b):  a-a/b*b  
# mod(-10,-3): -10-(-10)/(-3)*(-3)=-1
SELECT MOD(-10,-3);    -- -1 
 

三、日期函数
    now: 当前系统日期+时间
    curdate: 当前系统日期
    curtime: 当前系统时间
    str_to_date: 将字符转换成日期
    date_format: 将日期转换成字符

#now 返回当前系统日期+时间

SELECT NOW();   #2022-09-09 10:31:01

#curdate 返回当前系统日期，不包含时间

SELECT CURDATE();   #2022-09-09

#curtime 返回当前时间，不包含日期

SELECT CURTIME();    #10:31:02

#可以获取指定的部分， 年、月、日  时分秒

SELECT YEAR(NOW()) 年；  #2022
SELECT YEAR('1999-11-08') 年;  #1999

SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;  #9

#MONTHNAME 将月份以英文输出

SELECT MONTHNAME(NOW()) 月;  #September

-- str_to_date: 将字符转换成日期
SELECT STR_TO_DATE('1999-7-8','%Y-%M-%d') AS out_put;

#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees 
WHERE hiredate = '1992-4-3';

SELECT * FROM employees 
WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');

 --     date_format: 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;  #2022年09月09日

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name, DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 
FROM employees
WHERE commission_pct IS NOT NULL; 

四、其他函数
    version: 版本
    database: 当前库
    user: 当前连接用户

五、流程控制函数
   if 处理双分支

 case语句 处理多分支

情况1：处理等值判断
 情况2：处理条件判断

-- 五、流程控制函数

-- 1.if承数:if else 的效果

SELECT IF(10>5,'大','小');  #大

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，好家伙','有奖金,笑嘻了') 备注
FROM employees;

-- 2.case函数的使用一:switch case的效果

-- -- java中
-- switch(变量或表达式){
-- case常量1:语句1; break;

--...
-- default:语句n; break;}
-- 
-- mysql中
-- case要判断的字段或表达式
-- when常量1 then要显示的值1或语句1;
-- when常量2 then要显示的值2或语句2;...
-- else要显示的值n或语句n;
-- end

/*案例:查询员工的工资,要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资*/
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary* 1.1
WHEN 40 THEN salary* 1.2
WHEN 50 THEN salary* 1.3
ELSE salary
END AS 新工资
FROM employees;

#3.case 函数的使用二:买似于多重if

/*
java中:if(条件1){
                        语句1;
}else if(条件2){
                        语句2;
}
……
else{
            语句n;
}

mysql中:
when 条件1 then要显示的值1或语句1
when 条件2 then要显示的值2或语句2
……
else 要显示的值n或语句n
end
*/

#案例: 查询员工工资情况,显示级别

如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别

SELECT salary,
case
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

 #案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

#①查询每个工种有奖金的员工的最高工资
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;
#②根据①结果继续筛选，最高工资>12000
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) >12000;

 

 

#案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

#1查询每个领导手下的员工固定最低工资
SELECT MIN(salary) , manager_id
FROM employees
GROUP BY manager_id
#②添加筛选条件:编号>102
SELECT MIN(salary) , manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
#③添加筛选条件:最低工资>5000
SELECT MIN(salary) , manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

 #案例:按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

①查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);
#②添加筛选条件
SELECT COUNT(*) c,LENGTH (last_name) len_name
FROM employees
GROUP BY len_name
HAVING c>5;

 #按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资

SELECT AVG(salary), department_id,job_id
FROM employees
GROUP BY job_id, department_id;
#添加排序
#案例:查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
SELECT AVG(salary), department_id,job_id
FROM employees
GROUP BY job_id, department_id
ORDER BY AVG(salary) DESC;