MySQL基础笔记(2) - 排序查询,分组查询&常见函数

排序查询,分组查询&常见函数

目录

• 排序查询,分组查询&常见函数
  • • 1. 排序查询
    • 2. 函数
      • 2.1 单行函数
      • 2.2 聚集函数
    • 3. 分组查询

1. 排序查询

语法：select [查询列表] from [表] where [筛选条件] order by [排序列表] [asc|desc]

#1.对单个列排序
#将产品名以字母顺序显示出来
SELECT prod_name
FROM products
ORDER BY prod_name;

#2.对多个列排序
#将信息按价格顺序和字母顺序显示出来
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;
#排序具有优先级，如上例子，只有当价格相同时才会使用字母排序

#3.指定排序方向
#将信息按价格顺序"降序"显示后,按字母"升序"顺序显示出来
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
#ASC升序，DESC降序，不写默认升序

#order by子句可以支持单个字段，多个字段，表达式，函数，别名

2. 函数

定义：myspl中的函数，类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

语法：select [函数名] (实参列表) from [表]

2.1 单行函数

#一.字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('john');	#4

#2.concat 拼接字符串
SELECT CONCAT(vend_name,'(',vend_country,')')
FROM vendors
ORDER BY vend_name;

#3.upper,lower 大小写变化
SELECT UPPER('John');	#JOHN

#4.substr,substring 截取字符串
#截取从指定索引处后面的所有字符
SELECT SUBSTR('this is a sentence',6) out_put; #is a sentence
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('this is a sentence',1,3) out_put; #thi
#注意:截取所使用的索引是从1开始的，而非从0开始

#5.instr 返回子串第一次出现的索引，如果找不到就返回0
SELECT INSTR ('this is a this') AS out_put;

#6.trim 去除首尾指定字符(默认去除空格)
SELECT LENGTH(TRIM('  this   ')) AS out_put; #this
SELECT TRIM('a' FROM 'aaaaataahisaaaaa') AS out_put; #taaahis

#7.lpad/rpad 用指定的字符实现左/有填充指定长度
SELECT LPAD('this',8,'*') AS out_put;	#****this

#8.replace 替换
SELECT REPLACE('thisthisthisaaathis','this','b') AS output; #bbbaaab


#二.数学函数
#1.round 四舍五入
SELECT ROUND(-1.556,2); #-1.56(保留小数点后两位)

#2.ceil向上取整/ floor向下取整
SELECT CEIL (-1.02); #-1
SELECT FLOOR (-9.99);#-10

#3.truncate 截断
SELECT TRUNCATE(1.65,1); #1.6(保留小数点后一位)

#4.mod 取余
SELECT MOD(10,3); #3


#三.日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#获取指定的部分(年月日,小时,分,秒)
SELECT YEAR(NOW()) AS 年;
SELECT DATE(NOW()) AS 日;

#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1988-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992-4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %y');

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;


#四.流程控制函数
#1.if函数
#mysql中的if函数有些类似Java中的三目运算符
SELECT IF(x<y,'x小','y小'); #若x<y为true则返回第二栏,否则返回第三栏

#2.case函数
#mysql中的case函数兼具Java中switch-case和if-else的功能
#2.1
/*	
	case 要判断的字段或表达式
	when 常量1 then 要显示的值1
	when 常量2 then 要显示的值2
	...
	else 要显示的值n或语句n;
	end
*/
SELECT prod_price,vend_id,
CASE vend_id		#注意CASE依旧为SELECT语句后的一部分
WHEN 1001 THEN prod_price*10
WHEN 1002 THEN prod_price*20
WHEN 1003 THEN prod_price*30
ELSE prod_price
END AS final_price
FROM products;

#2.2
/*	case
	when 条件1 then 要显示的值1
	when 条件2 then 要显示的值2
	...
	else 要显示的值n或语句n
	end
*/

2.2 聚集函数

定义：聚集函数是运行在组上，计算和返回单个值的函数；聚集函数主要用于统计数据

#1.简单使用
SELECT AVG(prod_price) AS avg_price
FROM products;
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
SELECT MIN(prod_price) AS min_price
FROM products;
SELECT MAX(prod_price) AS max_price
FROM products;
SELECT COUNT(cust_email) AS num_cust
FROM customers;

#2.和distinct搭配实现去重运算
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
#添加DISTINCT后平均价格提高了，因为部分价格相同的低价商品被去重，不纳入平均值计算

#3.count函数的特殊使用
SELECT COUNT(*) AS num_cust
FROM customers;
#(由于主键不可能为null)此处count函数会统计所有行的个数

#4.如果要将聚集函数结果和一般字段一起查询，必须使用group by
SELECT prod_name,AVG(prod_price) AS avg_price
FROM products; #报错
#AVG最终只会输出一行结果，而prod_name会有多行，两者无法匹配

#sum,avg函数一般用于处理数值型;max,min,count可以处理任何类型(包括字符)
#聚集函数全部都会忽略null值(不将null值及其个数加入计算)

3. 分组查询

语法：select [聚集函数],[列名] from [表名] group by [需要分组的列表]

#查询每个供应商生产的产品数量
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;

#当需要对分组后的结果进行筛选时，就要用到having
#查询具有2个以上，价格10以上的产品的供应商
SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
#WHERE不能替代HAVING，因为WHERE只能对具体的行进行筛选，如果查询中使用了聚集函数，那么就相当于对行进行了分组，WHERE并不认识分组后的表

/*	
	分组查询中的筛选条件分为两类
				数据源			位置				关键字
	分组前筛选	 原始表		 group by子句前	where
	分组后筛选	 分组后的结果集  group by子句后	  having
*/

分组查询注意事项：

• GROUP BY子句可以包含任意数目的列，这意味着可以对分组进行嵌套。如果嵌套了分组，数据将在最后规定的分组上进行汇总
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
• 如果分组列中具有NULL值，则NULL将作为一个分组返回，如果列中有多行NULL值，它们将分为一组