第二章 DQL语言的学习2（单行函数、分组函数和分组查询）

最新推荐文章于 2023-09-26 21:25:54 发布
最新推荐文章于 2023-09-26 21:25:54 发布
阅读量210 收藏
点赞数 1
分类专栏： # MySQL_基础+高级篇- 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43857827/article/details/107973426
版权

2、数据查询语言（DQL）学习

  • 基础查询
  • 条件查询
  • 排序查询
  • 单行函数
  • 分组函数
  • 分组查询
  • 连接查询
  • 子查询
  • 分页查询
  • union联合查询
# 进阶4：常见函数
/*
概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处：隐藏了实现细节；提高代码的重复使用性；
调用：select 函数名（实参数列表） 【from 表】；
特点：
	叫什么（函数名）
	干什么（函数功能）
	
常见函数分类：
	1、单行函数（处理）
	2、分组函数（统计）

*/

2.4 单行函数

(1) 字符函数

#一、字符函数
#length 获取参数值的直接个数
SELECT LENGTH('john');  #4
# 中文三个字节，英文和数字一个字节
SELECT LENGTH('张三丰hhhh12');  #15
# 获取字符个数
SELECT CHAR_LENGTH(s)('张三丰hhhh12');  #9

#concat 拼接字符串
SELECT CONCAT(last_name,'-',first_name) AS 姓名 FROM employees;

在这里插入图片描述

#upper变大写、lower变小写
SELECT UPPER('john');  #JOHN
SELECT LOWER('joHn');  #john
# 案例：将姓变大写，名字变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'-',LOWER(first_name)) AS 姓名 FROM employees;

在这里插入图片描述

#substr(substring)前面是后面的简写，索引从1开始，在指定位置开始向后截取字符串
#或者指定截取长度
SELECT SUBSTR('李莫愁爱上陆展元',6) AS out_put;  #陆展元
SELECT SUBSTR('李莫愁爱上陆展元',6,2) AS out_put; #陆展

#案例：姓名中首字符大写，其他字符小写，然后用-拼接
SELECT 
	CONCAT(UPPER(SUBSTR(last_name,1,1)),'-',LOWER(SUBSTR(last_name,2))) AS 姓名 
FROM employees;

在这里插入图片描述

LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符

select left("张三丰",2);  #张三
select right("张三丰",1);  #丰
select left("张三丰",4);  #张三丰

在这里插入图片描述

#instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('李莫愁爱上陆展元',6) AS out_put;  #0
SELECT INSTR('李莫愁爱上陆展元','爱上') AS out_put;  #4

#trim 不指定参数就是去空格（前后去空格，中间不去）
SELECT TRIM('   李  莫愁    ') AS out_put; #李  莫愁
SELECT TRIM('a' FROM 'aa  aaa  李莫 愁  aa  aaaa') AS out_put;#  aaa  李莫 愁  aa  

#lpad 用指定的字符实现左填充以达到指定长度
#rpad 右填充
SELECT LPAD('李莫愁',10,'*') AS out_put;
SELECT LPAD('李莫愁',2,'*') AS out_put;  #李莫

在这里插入图片描述 在这里插入图片描述

# STRCMP 比较两个字符大小,前面大于后面返回1
select strcmp('abc','acb');   #-1
select strcmp('acb','abc');   #1
select strcmp('acb','acb');   #0

在这里插入图片描述

#replace 替换，有几个替换几个
SELECT REPLACE('周芷若周芷若张无忌爱上周芷若周芷若周芷若','周芷若','赵敏') AS out_put;

在这里插入图片描述
(2)数学函数
在这里插入图片描述

# 二、数学函数
#round 四舍五入,3表示小数点后位数
SELECT ROUND(4.65);  #5
SELECT ROUND(-4.6533) #-5
SELECT ROUND(-4.6533,3); # -4.653

#ceil 向上取整，返回值大于等于该参数的最小整数
#floor 向下取整，返回值小于等于该参数的最大整数
SELECT CEIL(4.65);  #5
SELECT FLOOR(4.65);  #4

#truncate 小数点后截断,参数是小数点后位数
SELECT TRUNCATE(1.69999,2);  #1.69
SELECT TRUNCATE(500,2);  #500

#mod 取余,符号看前面的数
#a%b=a-a/b*b
SELECT MOD(10,-3);  #1
SELECT MOD(-10,-3);  #-1
SELECT MOD(-10,3);  #-1
SELECT MOD(10,3);  #1

（3）日期函数

在这里插入图片描述

#三、日期函数
#now返回当前系统日期+时间
SELECT NOW();  #2020-08-14 21:21:17

#curdate返回当前系统日期，不包括时间
SELECT CURDATE();  #2020-08-14

#curtime返回当前时间，不包括日期
SELECT CURTIME();  #21:21:17

#DATEDIFF(date1,date2)返回date1 - date2的日期间隔
#TIMEDIFF(time1, time2)返回time1 - time2的时间间隔
select DATEDIFF('2020-10-28','1993-2-2');  #10130，这个是天数
select TIMEDIFF('21:07','6:30');   #14:37:00

#可以获取指定的部分，年，月，day日，hour小时，minute分钟，second秒
SELECT YEAR(NOW()) AS 年;  #2020
SELECT YEAR('1993-02-02') AS 年;  #1993
SELECT YEAR(hiredate) FROM employees;  

SELECT MONTH(NOW()) AS 月;  #8
# 英文显示
SELECT MONTHNAME(NOW()) AS 月;  #August
SELECT MONTH('1993-02-02') AS 月;  #2
SELECT MONTH(hiredate) FROM employees;

#str_to_date: 将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');  #1999-09-13
# 查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('3-4 1992','%d-%c %Y');
#date_format:将日期转换成字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');  #2018年06月06日
SELECT DATE_FORMAT('2018/6/6','%Y-%m-%d');  #2018-06-06
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');   #2020-08-14
# 查询有奖金的员工名和入职日期(xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

在这里插入图片描述 在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
（4）其他函数

  • version版本
  • database当前库
  • user当前连接用户
#四、其他函数
#查看mysql版本信息
SELECT VERSION();  #5.5.15
#查看当前使用的数据库
SELECT DATABASE();  #myemployees
#查看用户
SELECT USER();  #root@localhost

（5）流程控制函数

  • if 处理双分支
  • case语句 处理多分支
    • 情况1：处理等值判断
    • 情况2：处理条件判断
#五、流程控制函数
#1、if函数： if-else的效果
# if 10<5:大 else: 小
SELECT IF(10<5,'大','小');  # 小

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

在这里插入图片描述

#2、case语句处理多分支
#使用1：swith case的效果，等值判断
/*
语法：

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值1或语句2；
。。。。

else 要现实的值n或者语句n；
end

*/

# 案例
/*
查询员工的工资，要求：
	部门号30，工资为1.1倍
	部门号40，工资为1.2倍
	部门号50，工资为1.3倍
	部门号其他，工资为原工资
*/
SELECT salary AS 原工资,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;

在这里插入图片描述

#使用2：多重if，区间判断
/*
语法：

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;

在这里插入图片描述
测试：

在这里插入图片描述
在这里插入图片描述

2.5 分组函数

  • sum 求和
  • max 最大值
  • min 最小值
  • avg 平均值
  • count 计数
#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum求和；avg平均值；max最大值；min最小值；count计算非空个数
注意：
	null不参与运算
	和distinct搭配
*/

# 简单使用：不是每个人求统计，而是所有的求一个统计值
SELECT SUM(salary) FROM employees; #691400.00
SELECT AVG(salary) FROM employees; #6461.682243
SELECT MIN(salary) FROM employees; #2100.00
SELECT MAX(salary) FROM employees; #24000.00
SELECT COUNT(salary) FROM employees;  #107

# 复合使用
SELECT 
	SUM(salary) AS 和,
	AVG(salary) AS 均值,
	MAX(salary) AS 最大值,
	MIN(salary) AS 最小值,
	COUNT(salary) AS 个数
FROM employees;

#和distinct搭配(去重再统计）
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

在这里插入图片描述
在这里插入图片描述

#count函数的详细介绍
SELECT COUNT(salary) FROM employees;  # 107
# 用来统计行数，其中1可以换成其他常量值或者字符，常用*号
SELECT COUNT(*) FROM employees;   # 107
SELECT COUNT(1) FROM employees;   # 107
SELECT COUNT('嘻嘻') FROM employees;   # 107

#和分组函数一同查询的字段有限制，一般不要一块使用
# 分组函数是所有的统计一个值，所以影响
SELECT AVG(salary),employee_id FROM employees;  

# 测试
#查询员工表中最大入职时间和最小入职时间的相差天数，命名为diffrence
SELECT DATEDIFF('2017-10-1','2017-9-29') AS difference;

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference
FROM employees;

# 查询部门编号为90的员工个数
SELECT COUNT(*) 个数
FROM employees
WHERE department_id=90;

在这里插入图片描述
在这里插入图片描述 在这里插入图片描述

2.6 分组查询

在这里插入图片描述

# 进阶5：分组查询
/*
语法：
	select 分组函数，列（要求出现在哎group by的后面）
	from 表
	【where 筛选条件】
	group by
	【order by 子句】
注意：
	查询列表比较特殊，要求是分组函数和group by后出现的字段
	where必须出现在from后面
特点：
	分组查询中的筛选条件分为两类：
		分组前筛选（where） group by 前
		分组后筛选（having）group by 后
*/

# 一、简单的分组查询
#案例1：查询每个工种的最高工资
SELECT 
	MAX(salary) AS 最高工资,
	job_id AS 工种
FROM employees
GROUP BY job_id;

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

在这里插入图片描述
在这里插入图片描述

# 二、添加筛选条件的分组查询
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id,email
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;


#案例3:查询哪个部门的员工个数>2
/*
1、查询每个部门的员工个数
2、根据1中的结果进行筛选
*/
#1、第一步
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#2、第二步
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#案例4：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
/*
1、查询每个工种有奖金的员工的最高工资
2、在1的基础上判断最高工资>12000
*/
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL  # 分组前筛选
GROUP BY job_id
HAVING MAX(salary)>12000;  # 分组后筛选


#案例5：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪些，以及对应的最低工资
/*
1、查询领导编号>102的领导手下的最低工资
2、在1的基础上判断最低工资>12000
*/
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102  # 分组前筛选
GROUP BY manager_id
HAVING MIN(salary)>5000;  # 分组后筛选

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

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

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

#五、添加排序
#案例：查询每个非空部门每个工种的员工平均工资>10000的信息，并且平均工资排序
SELECT department_id,job_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

Hubert_xx
关注
专栏目录
MySQL学习记录：第一章 DQL语言
M_try的博客
08-01 1668
SELECT count(salary),count(*),count(1),count('测试') FROM employees;#案例：查询员工表中做大入职日期和最小入职日期时间的相差天数SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) 差值 FROM employees;#案例：查询部门编号为90的员工个数。
mysql数据库怎样使查询语言为小写_mysql数据库小知识----查询语言DQL
weixin_39550410的博客
02-08 1247
第一章 SQL的基本语法规范1.sql不区分大小写，但是习惯上关键字、系统保留字、函数名称大写，表名和列名小写2.sql使用空格或者回车来分隔每个单词都一样，习惯上每个子句换行WHERE子句中的每个条件占一行，子查询单独占一行3.复杂的语句最好加上注释进行说明-- 单行注释(空格必须有)/*多行注释*/4.sql语句中使用分号作为分隔符，系统读取到分号才会执行语句/*select 列名1，列名2，...
MySQL基础之查询
最新发布
woxiangsi99的博客
09-26 2544
特点： 1、外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 外连接查询结果=内连接结果+主表中有而从表没有的记录 2、左外连接，left join左边的是主表 右外连接，right join右边的是主表 3、左外和右外交换两个表的顺序，可以实现同样的效果 4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的。案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资。
MySQL笔记--MySQL分组函数和分组查询(8)
Al_tair的博客
06-17 1141
我们已经学习完排序查询，接下来我们涉足常见函数，跟上我的步伐！
数据库 - DQL - 分组查询
Javaer`s blog
03-18 413
select 查询列表 from 表名 where 筛选条件 order by 排序列表 [asc|desc]; //升序，通过asc //降序，通过desc //也可以按照表达式排序 特点： ①排序列表可以是 单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合 ②升序，通过asc ​ 降序，通过desc ...
MySQL操作汇总1——查询操作
qq_38596491的博客
05-12 2656
基础查询： select 查询列表 from 表名 #1.查询单个字段 SELECT last_name FROM employees; #2.查询多个字段 SELECT last_name,salary,email FROM employees; #3.查询表中的所有字段 如果有输出顺序要求的话： SELECT `employee_id`,`first_name`,`last_name`,`email`,`phone_number`,`job_id`,`salary`,
7、分组查询----学习笔记
qq_42847094的博客
08-31 579
文章目录7、分组查询7.1、简单的分组查询7.1.1、案例1：查询每个工种的最高工资7.1.2、案例2：查询每个位置上的部门个数7.2、添加筛选条件7.2.1、案例1：查询邮箱中包含a字符的，每个部门的平均工资7.2.2、案例2：查询有奖金的每个领导手下员工的最高工资7.3、添加复杂的筛选条件7.3.1、案例1：查询哪个部门的员工个数 > 2 (用到 HAVING）7.3.2、案例2：查询每个工种有奖金的员工的最高工资 > 12000 的工资编号和最高工资7.3.3、案例3：查询领导编号 &g
mysql学习笔记，欢迎学习
01-10
**第二章 DQL（Data Query Language）数据查询语言** **一、基本的查询语句（SELECT）** SELECT语句是DQL中最基础的部分，用于从表中检索数据。最简单的形式是SELECT * FROM table_name，这将返回表中的所有列。 *...
数据库学习笔记1
weixin_38665351的博客
07-19 1498
0407 数据库学习笔记〇、视频课链接 〇、视频课链接 书：Mysql 必知必会 【狂神说Java】MySQL最新教程通俗易懂 CSDN上的一个笔记：《数据库系统概论》第五版 +学习笔记总目录 还有Redis：【狂神说Java】Redis最新超详细版教程通俗易懂 .........
Oracle基础查询（2）
weixin_45477342的博客
08-05 294
--- Oracle基础 --- oracle的常用命令 第一章 (1) 请使用 scott 用户登录 oracle 数据库实例，然后，切换身份为 system 用户 登录后，使用： 简单使用：conn 用户名/密码 该命令经常用于切换当前用户。因为我们建议大家在登录的时候用普通用户，scott，如果确实需要 system 用户，测...
DQL（数据查询语言）语言学习
weixin_43572928的博客
11-26 1129
数据查询，单表查询，多表查询
SQL分组查询案例
Sicilly的博客
12-29 1800
总结 语法 select 查询列表 from 表 【where 筛选条件】 group by 分组的字段 having 分组后筛选 【order by 排序的字段】; 执行顺序 from子句 where子句 group by子句 having子句 select子句 order by子句 特点 和分组函数一同查询的字段必须是group by后出现的字段 筛选分为两类：分组前筛选和分组后筛选 ...
一、MySQL查询学习笔记（基础查询、条件查询、排序查询、常见函数、分组查询 详解）
zep
04-25 702
DQL语言的学习 1：基础查询 语法： SELECT 要查询的东西 【FROM 表名】; 类似于Java中 :System.out.println(要打印的东西); 特点： ①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在 ② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数 2：条件查询 条件查询：根据条件过滤原始表的数据，查询到想要的数据 语法： select...
MYSQL学习之路——数据处理练习题（一）
创作星球
03-23 932
#时间函数 SELECT now() AS 时间; #查询工资>12000的员工信息 SELECT * FROM employees WHERE salary > 12000; #查询所有职员的全名用并且用“_”隔开 SELECT CONCAT( first_name, '_', last_name ) AS 全名 FROM employees; #查...
分组查询和子查询？
悟空非空也
07-05 766
# 分组查询 /* 语法： select 列表 from 表 where 分组前筛选条件 group by 分组的字段 having 分组后的筛选条件 order by 排序的字段； 筛选 分组后： having group by后 having */ #...
MySQL案例—分组查询
señoritaw的博客
04-25 416
#分组查询 语法： select 分组函数,列(要求出现在group by的后面) from 表名 【where 筛选条件】 group by 分组的列表 【order by 子句】; 特点：1、分组查询中的筛选条件分为两类 位置 关键字 分组前的筛选 group by的前面 where 分组后的筛选 group by的后面 having 注意：分组函数做为筛选条件一定是放在having子句中能用分组前筛选的，
【数据库学习】八 分组查询
愿万事胜意
06-03 1888
学习要点 使用GROUP BY子句可以像切蛋糕那样对表进行分割。通过使用聚合函数和GROUP BY子句，可以根据“商品种类”，“登记日期”等将表分割后再进行汇总。 聚合键中包含NULL时候，在结果中会以“不确定”行(空行)的形式表现出来。 使用聚合函数和GROUP BY子句的时候应该注意以下4点。 只能写在SELECT子句之中。 GROUP BY子句中不能够使用SELECT子句中的别名 ...
sql学习查询语法与分类
Chloris_的博客
01-31 415
#DAL语言 #进阶1：基础查询 #select 查询列表 from 表名； #System.out.println(打印东西); /* 一、查询的列表可以是：表中字段、常量值、表达式、函数 二、查询的结果是一个虚拟表格。 */ USE sakila;#最好在最开始指定自己要使用的库,sakila是库，address是表，phone是列名也是字段 #1.查询表中的单个字段 SELECT phone FROM address; #2.查询表中的多个字段 SELECT phone,city_id FROM.
MySQL 基础篇 二【DQL:数据库查询语言】
Kukeoo的博客
03-01 1397
一、基础查询 二、条件查询 三、排序查询 四、常见函数 五、分组函数 六、分组查询 七、连接查询 八、子查询 九、分页查询 十、union联合查询
MySQL DQL深入学习：查询与函数解析
"MySQL函数-Mysql 的DQL的学习以及掌握" 在数据库管理中，DQL（Data Query Language）是用于查询数据的关键部分，尤其在MySQL这样的关系型数据库管理系统中，掌握DQL是至关重要的。本课重点讲解了如何使用MySQL的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值