mysql分组获取第一条数据

MySQL数据库提供了很多函数，其中包括：

l 数学函数；

l 字符串函数；

l 日期和时间函数；

l 条件判断函数；流程控制函数；

l 系统信息函数；

l 加密函数；

l 格式化函数。

我们主要掌握前四种函数就可以啦~

第六章 常见函数

什么是函数？

为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用。

我们可以：

1、自定义方法(函数)

2、调用方法(函数)

叫什么 ：函数名

干什么 ：函数功能

常见函数：

一、字符函数

1、CONCAT 拼接字符

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

2、LENGTH 获取字节长度

SELECT LENGTH('hello,郭襄');

3、CHAR_LENGTH 获取字符个数

SELECT CHAR_LENGTH('hello,郭襄');

注意：一个中文字符相当于三个字节（utf-8编码下）

4、SUBSTRING 截取子串

注意：起始索引从1开始。（python是从0开始的哦~）

方式1：substr(str,起始索引，截取的字符长度)

方式2：substr(str,起始索引)

SELECT SUBSTR('张三丰爱上了郭襄',1,3);
SELECT SUBSTR('张三丰爱上了郭襄',7);

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

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

结果为3

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

注：10代表期望的字符数量

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

#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;

思考过程：

①姓首字符大写：SELECT UPPER(SUBSTR(first_name,1,1)) FROM employees;

②其他字符小写：SELECT LOWER(SUBSTR(first_name,2)) FROM employees;

③名所有字符大写：SELECT UPPER(last_name) FROM employees;

9、STRCMP 比较两个字符大小

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

情况	结果
左边大	1
右边大	-1
相等	0

注：英文字符按abc的顺序，默认a大于b。

10、LEFT/RIGHT 截取子串

SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);

公式	含义
CONCAT(S1,S2,......,Sn)	连接S1,S2,......,Sn为一个字符串
CONCAT(s, S1,S2,......,Sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
REPEAT(str, n)	返回str重复n次的结果
LOWER(s)/LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	比较字符串s1,s2
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

---

二、数学函数

1、ABS 绝对值

SELECT ABS(-2.4);

2、CEIL 向上取整

返回大于等于该参数的最小整数

SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

分别返回-1，1，1

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);
SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;

原理：

a%b = a-(INT)a/b*b

-10%3 = -10 - (-10)/3*3 = -1

其中-10/3取整为-3，-3*3=-9，-10-（-9）=-1

公式	含义
ABS（x）	返回x的绝对值
CEIL（x）	返回大于x的最小整数值
FLOOR（x）	返回大于x的最大整数值
MOD(x,y)	返回x/y的模
RAND(x)	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方

---

三、日期函数

1、NOW

SELECT NOW();

返回日期+时间：'2020-12-30 17:32:11'

2、CURDATE

SELECT CURDATE();

返回日期：

2020-12-30

3、CURTIME

SELECT CURTIME();

返回时间：17:32:11

4、DATEDIFF

SELECT DATEDIFF('2020-12-29', '2020-12-30');

结果为-1

5、DATE_FORMAT

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

结果为'1998年08月16日 00小时00分钟00秒'

6、STR_TO_DATE 按指定格式解析字符串为日期类型

-- 选择录用日期在1998-3-15前的人
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

公式	含义
CURDATE()/CURRENT_DATE()	返回当前日期
CURTIME()/CURRENT_TIME()	返回当前时间
NOW() SYSDATE() CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) MONTH(date) DAY(date) HOUR(time) MINUTE(time) SECOND(time)	返回具体的时间值
WEEK(date) WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，…，周六是7
WEEKDAY(date)	返回周几，注意：周1是0，周2是1，…，周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY,…,SUNDAY
MONTHNAME(date)	返回月份：January，…
DATEDIFF(date1,date2) TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

（1）DATE_ADD(datetime, INTERVAL, expr type)

• 增加一年：SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
• 减少一年：SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
• 增加一年零一个月：SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);#需要单引号

expr type类型：

• YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
• YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND

（2）DATE_FORMAT(datetime, fmt)和STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份（2019）	%y	2位数字表示年份（19）
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,…）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02,...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%h和%I(大写i)	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l(小写L)	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

---

四、流程函数

1、IF函数

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

结果返回：好

2、CASE函数

①情况1 ：实现等值判断

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：添加筛选条件
#①查询emp表中记录数：
SELECT COUNT(employee_id) FROM employees;
#②查询emp表中有佣金的人数：
SELECT COUNT(salary) FROM employees;
#③查询emp表中月薪大于2500的人数：
SELECT COUNT(salary) FROM employees WHERE salary>2500;
#④查询有领导的人数：
SELECT COUNT(manager_id) FROM employees;

★count的补充介绍

#1、统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;
-- 与COUNT(*)效果一致，但数据量大的话还是*比较高效

#2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;

思考：每个部门的总工资、平均工资？

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

---

第八章 分组查询

语法：

select 查询列表

from 表名

where 筛选条件

group by

having

order by 排序列表;

执行顺序：

①from子句

②where子句

③group by 子句

④having子句

⑤select子句

⑥order by子句

特点：

①查询列表往往是分组函数和被分组的字段

②分组查询中的筛选分为两类

	筛选的基表	使用的关键词	位置
分组前筛选	原始表	where	group by 的前面
分组后筛选	分组后的结果集	having	group by的后面

where——group by ——having

分组函数做条件只可能放在having后面！！！

1) 简单的分组

#案例1：查询每个工种的员工平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

#案例2：查询每个领导的手下人数
SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

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

#案例1：查询邮箱中包含a字符的每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id
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
#分析1：查询每个部门的员工个数
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING  COUNT(*)>5;

#案例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的最低工资
#分析1：查询每个领导手下员工的最低工资
SELECT MIN(salary) 最低工资,manager_id
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：筛选刚才的结果，看哪个最高工资>6000
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;