MySQL基础（二）

三分稚气 2021-04-14

334

本系列是基于B站尚硅谷MySQL李玉婷教程的学习笔记，形成学习内容的文字版本

视频链接：https://www.bilibili.com/video/BV12b411K7Zu?t=7&p=304

主要内容：

一、常见函数

1.字符函数

2.数学函数

3.日期函数

4.流程控制函数

二、分组函数

三、分组查询

一、常见函数

1.字符函数

1、CONCAT 拼接字符


SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;


2、LENGTH 获取字节长度


SELECT LENGTH('hello,郭襄');


3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('hello,郭襄');


4、SUBSTRING 截取子串
/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)
*/
SELECT SUBSTR('张三丰爱上了郭襄',1,3);
SELECT SUBSTR('张三丰爱上了郭襄',7);


5、INSTR获取字符第一次出现的索引


SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');


6、TRIM去前后指定的字符，默认是去空格




SELECT TRIM(' 虚  竹    ')  AS a;
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;


7、LPAD/RPAD  左填充/右填充
SELECT LPAD('木婉清',10,'a');
SELECT RPAD('木婉清',10,'a');


8、UPPER/LOWER  变大写/变小写


#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”




SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;
SELECT UPPER(last_name) FROM employees;


SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;


9、STRCMP 比较两个字符大小


SELECT STRCMP('aec','aec');




10、LEFT/RIGHT  截取子串
SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);

2.数学函数

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;

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');

4.流程控制函数

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.简单分组

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


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


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


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

2.可以实现分组前的筛选

案例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;

3.可以实现分组后的筛选

案例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;

4.可以实现排序

案例：查询没有奖金的员工的最高工资>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;

5.安多个字段分组

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


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


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

先写这么多，仅供参考，欢迎指点。

如果你有希望实现的内容，或者在过程中遇到了某些困难，欢迎交流探讨！

关注公众号，回复“尚硅谷MySQL”关键字，获取课程配套软件、课件、资料

/如有纰漏请联系我十分感谢/

长按图片，点击关注

mysql

文章转载自三分稚气，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL基础（二）

一、常见函数

1.字符函数

2.数学函数

3.日期函数

4.流程控制函数

二、分组函数

三、分组查询

1.简单分组

2.可以实现分组前的筛选

3.可以实现分组后的筛选

4.可以实现排序

5.安多个字段分组

评论