MySQL基础（二）

1、ABS 绝对值
SELECT ABS(-2.4);
2、CEIL 向上取整  返回>=该参数的最小整数
SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);


3、FLOOR 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);


4、ROUND 四舍五入
SELECT ROUND(1.8712345);
SELECT ROUND(1.8712345,2);


5、TRUNCATE 截断


SELECT TRUNCATE(1.8712345,1);


6、MOD 取余


SELECT MOD(-10,3);
a%b = a-(INT)a/b*b
-10%3 = -10 - (-10)/3*3   = -1


SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;
复制

1、NOW
SELECT NOW();


2、CURDATE


SELECT CURDATE();


3、CURTIME
SELECT CURTIME();




4、DATEDIFF
SELECT DATEDIFF('1998-7-16','2019-7-13');


5、DATE_FORMAT


SELECT DATE_FORMAT('1998-7-16','%Y年%M月%d日 %H小时%i分钟%s秒') 出生日期;






SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期 
FROM employees;






6、STR_TO_DATE 按指定格式解析字符串为日期类型
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');
复制

1、IF函数


SELECT IF(100>9,'好','坏');




#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;






2、CASE函数


①情况1 ：类似于switch语句，可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END




案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变


显示 部门编号，新工资，旧工资


SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  newSalary
FROM employees;




②情况2：类似于多重IF语句，实现区间判断
CASE 
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...


ELSE 结果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  a
 FROM employees;
复制

/*
用于实现将一组数据进行统计计算，最终得到一个值，又称聚合函数和统计函数
分组函数清单：
sum（字段名）：求和
avg()：求平均
max():
min():
count():计算非空字段的个数,也就是他本身就去掉了空值


*/


案例1：查询员工的信息表中，工资和、工资平均、最低和最高


SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;


案例2：添加筛选条件


#查询表中的记录数
SELECT COUNT(employee_id) FROM employees;


#查询表中有佣金的人数；
SELECT COUNT(salary) FROM employees;


#查询月薪大于2500的人数
SELECT COUNT(salary) FROM employees WHERE salary>2500;


#查询有领导的人数
SELECT COUNT(manager_id) FROM employees;


count补充


SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id=30;




#搭配distinct实现去重的统计
#需求：查询有员工的部门个数（逻辑是去掉部门的重复项）


SELECT COUNT(DISTINCT department_id) FROM employees;


思考：每个部门的总工资、平均工资？使用group by分组查询


SELECT department_id,SUM(salary),AVG(salary) FROM employees
GROUP BY department_id;
复制

案例1：查询每个工种的员工平均工资


SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;


案例2：查询每个领导的手下人数


SELECT manager_id,COUNT(*) FROM employees GROUP BY manager_id;
复制

案例1：邮箱中包含a的每个部门的最高的工资


SELECT MAX(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id;


案例2：查询每个领导手下有奖金的员工的平均工资


SELECT AVG(salary),manager_id FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id;
复制

案例1：查询那个部门的员工个数大于5


SELECT COUNT(*),department_id FROM employees 
GROUP BY department_id;


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


SELECT job_id,MAX(salary) FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id
HAVING MAX(salary)>12000;        


#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id , MIN(salary) FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary) > 5000;
复制

案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序


分析1 ：按工种分组，查询有奖金的员工的最高工资


SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id;


分析2 ：筛选刚刚结果


SELECT MAX(salary) 最高工资 , job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000;


分析3： 升序


SELECT MAX(salary) 最高工资 , job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;
复制

案例：查询每个工种每个部门的最低工资，并按最低工资降序


题目中就是讲同一工种同一部门才是一组


SELECT MIN(salary)最低工资 , job_id , department_id
FROM employees
GROUP BY job_id,department_id;
复制