Day03 排序查询、常见函数

最新推荐文章于 2024-07-23 14:05:29 发布

爱戴假发的谢老师

最新推荐文章于 2024-07-23 14:05:29 发布

阅读量67

点赞数

分类专栏： Mysql 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/weixin_38641763/article/details/128881149

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

排序查询
常见函数

排序查询

进阶3: 排序查询
引入:
	SELECT * FROM `employees`;

语法:
	select 查询列表 from 表名 【where 筛选条件】 order by 排序列表 【asc/desc】
特点:
	1、 asc 代表 升序【低 - 高】，desc 代表 降序【高 - 低】
	2、 如果不写。默认是升序。
	3、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
	4、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`;

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

#案例4: 按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,`salary`*12*(1+IFNULL(`commission_pct`,0))AS 年薪 FROM `employees`  ORDER BY 年薪 DESC;

#案例5: 查询按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(`last_name`)AS 字节长度,`last_name`,`salary` FROM `employees` ORDER BY LENGTH(`last_name`);

#案例6: 查询员工信息，要求先按工资升序，再按员工编号降序【按多个字段排序】
SELECT * FROM `employees` ORDER BY `salary`  ASC,`employee_id`DESC

常见函数

概念:	类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

好处:	1、隐藏了功能的实现细节
	    2、提高了代码的重用性
	 
调用:	select 函数名(实参列表) 【from 表名】;

特点:	①叫什么（函数名）
	    ②干什么（函数的功能）
	 
分类:	1、单行函数
		功能:	做处理使用
		如concat、length、ifnull等
	 2、分组函数
		功能:	做统计使用，又称统计函数、聚合函数、组函数
		
常见函数:
	一、单行函数
            字符函数:
                length、concat、substr、instr、trim、upper、lower、lpad、rpad、repace
            数学函数:
                round、ceil、floor、truncate、mod
            日期函数:
                now、curdate、curdtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format
            其他函数:
                version、database、user
            控制函数:
                if、case
            
   二、分组函数
            分类:
                sum 求和
                avg 平均值
                max 最大值
                min 最小值
                count 计算个数

            特点:
            1、sum、avg一般用于处理数值型
               max、min、count可以处理任何类型
            2、以上分组函数都忽略null值
            3、可以和distinct搭配实现去重运算
            4、count函数的单独介绍
               一般使用count(*)用作统计行数
            5、和分组函数一同查询的字段要求是group by后的字段

1.字符函数

#一、字符函数

#1.length:   获取参数值的字节个数
	SELECT LENGTH("john");

#2.concat:   拼接字符串
	SELECT CONCAT(`last_name`,'_',`first_name`) AS 姓名 FROM `employees`;

#3.upper、lower:   大小写转换
	SELECT UPPER("john");
	SELECT LOWER("JOHN");
	#示例:	将姓变大写，名变小写，然后再拼接
	SELECT CONCAT(UPPER(`last_name`),"_",LOWER(`first_name`)) 姓名 FROM `employees`;
	
#4.substr、substring:	截取字符
	注意:	MySQL中索引从1开始，java从0开始
	#截取从指定索引处到结尾所有字符
	SELECT SUBSTRING("张飞大战奥特曼",5);

	#截取从指定索引处指定字符长度的字符
	SELECT SUBSTRING("张飞大战奥特曼",1,2);
	
	#案例:	姓名中首字符大写，其他字符小写，然后用_拼接，显示出来
	SELECT CONCAT(UPPER(SUBSTR(`last_name`,1,1)),"_",LOWER(SUBSTR(`last_name`,2)))AS out_put FROM `employees`
	
#5.instr   返回子串首次出现的索引位置，如果找不到就返回0
SELECT INSTR("青青草原刘海生","刘海生") AS output;

#6.trim   去除字符前后的空格，或指定的字符
	SELECT TRIM("      阿昌       ") AS output;
	SELECT TRIM("a" FROM "aaaaa阿aaaaa昌aaaa")AS output

#7.lpad   用指定的字符实现左填充指定长度
	SELECT LPAD("鲁智深",8,"*") AS output
	
#8.rpad   用指定的字符实现右填充指定长度
	SELECT RPAD("鲁智深",7,"Ab") AS output	
	
#9.replace   替换
		SELECT REPLACE("外星人外星人关公大战外星人外星人外星人外星人","外星人","曹操") AS output;

2.数学函数

#二、数学函数
#1.round   四舍五入	2,1.67
	SELECT ROUND(1.65);	
	SELECT ROUND(1.6721,2);

#2.ceil   向上取整，返回大于等于该参数的最小整数	2
	SELECT CEIL(1.523);

#3.floor   向下取整，返回小于等于该函数的最大整数	2
	SELECT FLOOR(2.486);

#4.truncate   截断，返回小数点后指定位数之前的数值	11.15
	SELECT TRUNCATE(11.152223,2);

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

3.日期函数

在这里插入图片描述

#三、日期函数

	#1.now   返回当前系统日期+时间
		SELECT NOW();
	
	#2.curdate   返回当前系统日期，不包含时间
		SELECT CURDATE();
	
	#3.curtime   返回当前系统时间，不包含日期
		SELECT CURTIME();
		
	#4.可以获取指定的部分，年、月、日、小时、分钟、秒
		SELECT YEAR(NOW()) AS 年;
		SELECT YEAR(`hiredate`) AS 年 FROM `employees`；
		
		SELECT MONTH(NOW()) AS 月;
		SELECT MONTHNAME(NOW()) AS 月;
	
	#5.str_to_date 将字符通过指定格式转换成日期
		SELECT STR_TO_DATE("1999-3-9","%Y-%c-%d");
		
		#查询入职日期为1992-4-3的员工信息
		SELECT * FROM`employees` WHERE `hiredate` = STR_TO_DATE("4-3 1992","%c-%d %Y");
		
	#6.date_format   将日期转换为字符
		SELECT DATE_FORMAT(NOW(),"%Y年%c月%d日");
		
		#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
		SELECT `last_name`,DATE_FORMAT(`hiredate`,"%c月/%d日 %Y年") FROM `employees` WHERE `commission_pct` IS NOT NULL;

4.其他函数

#四、其他函数
	
	#1.version	查看版本号
		SELECT VERSION();
		
	#2.database	查看当前数据库
		SELECT DATABASE();
	
	#3.user		查看当前用户
		SELECT USER();

5.流程控制函数

#五、流程控制函数

	#1.if函数	实现if else的效果
	SELECT IF(10>5,"大","小");
	SELECT `last_name`,`commission_pct`,IF(`commission_pct`IS NULL,"没奖金","有奖金")AS 备注 FROM `employees`;
	
	#2.case函数
		
		#使用一		类似java中switch case的效果	适合用于等值判断
		
			/*
				java中:
				
					swtich(变量或表达式){
						case 常量1: 语句1;break;
						...
						default:语句n;break;
					}
					
				mysql中:
				
					case 要判断的字段或表达式
					when 常量1 then 要显示的值1或 语句1;
					when 常量2 then 要显示的值2或 语句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`;
			
		#使用二		类似于java中多重if	适合用于区间判断
		
			/*
			java中:
			
				if(条件1){
					语句1;
				}else if(条件2){
					语句2;
				}
				...
				else{
					语句n;
				}
				
			mysql中:
			
				case
				when 条件1 then 要显示的值1或 语句1;
				when 条件2 then 要显示的值2或 语句1;
				...
				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`

6.分组函数

#二、分组函数
	/*
	功能: 用作统计使用，又称为聚合函数、统计函数、组函数
	
	分类:
		sum 求和
		avg 平均值
		max 最大值
		min 最小值
		count 计算个数
		
	特点:
	1、sum、avg一般用于处理数值型
	   max、min、count可以处理任何类型
	2、以上分组函数都忽略null值
	3、可以和distinct搭配实现去重运算
	4、count函数的单独介绍
	   一般使用count(*)用作统计行数
	5、和分组函数一同查询的字段要求是group by后的字段
		
	
	
	*/
	
	#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 SUM(`salary`) 和,ROUND(AVG(`salary`),2) 平均值,MAX(`salary`) 最大值,MIN(`salary`) 最小值,COUNT(`salary`) 数量 FROM `employees`;
	
	#2、参数支持哪些类型
	SELECT SUM(`last_name`),AVG(`last_name`) FROM `employees`;
	SELECT SUM(`hiredate`),AVG(`hiredate`) FROM `employees`;
	
	SELECT MAX(`last_name`),MIN(`last_name`) FROM `employees`;
	SELECT MAX(`hiredate`),MIN(`hiredate`) FROM `employees`;	
	
	SELECT COUNT(`last_name`) FROM `employees`;
	SELECT COUNT(`commission_pct`) FROM `employees`;
	
	#3、是否忽略null值
	SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM `employees`;
	SELECT MAX(commission_pct),MIN(commission_pct) FROM `employees`;
	SELECT COUNT(commission_pct) FROM `employees`;
	
	#4.和distinct搭配
	SELECT SUM(DISTINCT `salary`),SUM(`salary`) FROM `employees`;
	SELECT COUNT(DISTINCT `salary`),COUNT(`salary`)FROM `employees`;
	
	#5.count函数的详细介绍
	SELECT COUNT(`salary`) FROM `employees`;
	SELECT COUNT(*) FROM `employees`;
	SELECT COUNT(1) FROM `employees`;
	
	效率:
	MYISAM存储引擎下，COUNT(*)效率最高
	INNODB存储引擎下，COUNT(*)和COUNT(1)效率差不多，比COUNT(字段)要高一些
	
	#6.和分组函数一同查询的字段有限制
	SELECT AVG(`salary`),`employee_id` FROM `employees`；