MySQL--------SQL基础应用3-CSDN博客

本文链接：https://blog.csdn.net/weixin_42384085/article/details/105672607
# 1,排序查询
/*
 语法：
   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;
SELECT * FROM employees ORDER BY salary;

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

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

# 案例4：按别名排序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
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 ASC,employee_id DESC;

# 1.查询员工的姓名和部门编号和年薪，按年薪降序，按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
# 2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT salary,last_name
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
# 3.查询邮箱中包含e的员工信息,并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
#------------------------------------------------------------------------

# 常见函数
/*
 概念：类似java的方法,将一组逻辑语句封装在方法中，对外暴露方法名
 好处：1.隐藏了实现细节 2.提高了代码的重用性
 调用：select 函数名(实参列表) [from 表];
 特点：
	1.叫什么(函数名)
	2.干什么(函数功能)

 分类：
	1.单行函数 如：concat、length、ifnull等
	
	2.分组函数
	   功能：做统计使用，又称为统计函数、聚合函数、组函数

*/

# 单行函数
# 1.字符函数
# length
SELECT LENGTH('john');
SELECT LENGTH('张三丰hehe');
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,3) out_put;

# 案例：姓名中首字母大写，其它字符小写然后拼接
SELECT
  CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(first_name)) 姓名
FROM employees;

# 5.instr 返回子串第一次出现的索引，找不到返回0
SELECT INSTR('abcdefghijk','hijk')AS out_put;

# 6.trim  去掉前后字符
SELECT LENGTH(TRIM('    AAAA    ')) AS out_put;

# 7.lpad 用指定字符左填充指定长度
SELECT LPAD('aaaa',10,'*') AS out_put;

# 8.rpad 用指定字符右填充指定长度
SELECT RPAD('aaaa',10,'*') AS out_put;

# 9.replace 替换
SELECT REPLACE('aaaaaaaaaaaa','a','b') AS out_put;
#----------------------------------------------------------------

# 数学函数
# 1.round 四舍五入
SELECT ROUND(-1.1);
SELECT ROUND(1.567,2);

# 2.ceil 向上取整
SELECT CEIL(1.01);

# 3.floor 向下取整
SELECT FLOOR(9.99);

# 4.truncate 截断
SELECT TRUNCATE(1.69,1);

# 5.mod取余
/*
 mod(a,b) : a-a/b*b
*/
SELECT MOD(-10,-3);
SELECT 10%3;
#---------------------------------------------------------------------

# 日期函数
# 1.now 返回当前系统日期+时间
SELECT NOW();

# 2.curdate 返回当前系统日期,不包含时间
SELECT CURDATE();

# 3.curtime 返回当前时间，不包含日期
SELECT CURTIME();

# 可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1991-1-1') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
#返回英文

# 4.str_to_date：将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('1991-8-21','%Y-%c-%d') out_put;

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

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

# 5.date_format:将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')AS out_put;
# 查询有奖金的员工名和入职日期（xx月/xx日  xx年）
SELECT 
	last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

#---------------------------------------------------------------------
# 流程控制函数
# 1.if函数:if else 的效果
SELECT IF(10<5,'大','小');

SELECT 	
	last_name,commission_pct,
	IF(commission_pct IS NULL,
	'没奖金','有奖金') 备注
FROM employees;

# 2.case函数的使用
# 1：switch case
/*
 java中：
 switch(变量或表达式){
	case 常量1:语句1;break;
	...
	default:语句n;break;
 }

 mysql中：
 case 要判断的字段或表达式
 when 常量1 then 要显示的值1或语句1;
 ...
 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函数的使用2：类似于 多重if
/*
 java中：
 

 mysql中：
 case	
 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;

# 分组函数
/*
 分类：
 sum：求和
 avg：平均值
 max：最大值
 min：最小值
 count：计算个数
*/

# 1.简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;


# 查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary) mx_sal,MIN(salary) mi_sal,
ROUND(AVG(salary),2) ag_sal,SUM(salary) sm_sal
FROM employees;

# 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence
FROM employees;

# 查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id=90;
#---------------------------------------------------------------------
# 分组查询
/*
 语法：
    select 分组函数,列（要求出现在group by的后面）
    from 表
    [where 筛选条件]
    group by 分组的列表
    [order by 子句]
    
 注意：查询列表必须特殊,要求是分组函数和group by后出现的字段
 
 特点：
      1、分组查询中的筛选条件分为两类
      分组前筛选    原始表            group by子句的前面  where
      分组后筛选    分组后的结果集    group by子句的后面  having
      
      1.分组函数做条件肯定是放在having子句中
      2.能用分组前筛选的，就优先采用分组前筛选
      3.group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开
      没有顺序要求）表达式或函数
      4.也可以添加排序（排序放在整个分组查询的最后）
*/
# 查询每个部门的平均工资
SELECT AVG(salary) FROM employees;

# 案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

# 案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

# 添加筛选条件
# 案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

# 案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;


# 添加复杂的筛选条件
# 案例1：查询哪个部门的员工个数>2
# 1.查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
# 2.2根据1的结果进行筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

# 案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
# 1.查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id;

# 2.根据1结果继续筛选，最高工资>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

# 2.添加筛选条件：编号>102
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;

# 3.添加筛选条件，最低工资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

# 按表达式或函数分组
# 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
# 1.查询每个长度的员工个数
SELECT
  COUNT(*),
  LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name);

# 2.添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY len_name
HAVING COUNT(*)>5;

# 按多个字段进行分组
# 案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

# 添加排序
SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC;