[大数据]Day2 排序查询&常见函数查询及案例详解（未完）

前言

? ? ? ? 今天下午学了三个小时没有看完第二天的完整内容，但感觉知识点好多，放在博客上整理一下吧，明天或许会增加也可能会新开个帖子。

正文

1. 排序查询

* 语法：
? ? select? ? ? ? ? ? ? ? ? ? ? ? （3）
? ? ? ? 查询列表
? ? from? ? ? ? ? ? ? ? ? ? ? ? （1）
? ? ? ? 表
? ? where? ? ? ? ? ? ? ? ? ? ? ? ?（2）
? ? ? ? 条件
? ? order by 排序的字段|表达式|函数|别名 【asc|desc】? ? ? ? ? ? ? ?（4）
? ? ? ?

????????执行顺序：如上。

*特点：

? ? ? ? 1.asc代表的是升序，desc代表降序。

? ? ? ? 2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名

? ? ? ? 3.order by字句一般放在查询语句最后面，limit字句除外。

* 注意：
?? ?* 如果有多个排序条件，则当之前的条件值一样时，才会判断第二条件。

案例：

#案例一：查询员工信息，要求工资从高到低排序
SELECT * FROM employees  ORDER BY salary DESC;

#案例二：查询部门编号>=90的员工信息，要求按入职时间先后进行排序[添加筛选条件]
SELECT * FROM employees WHERE department_id >=90 ORDER BY hiredate;

#案例三：按年薪的高低显示员工的信息和年薪【按表达式排序】（可以按别名）

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪;

#案例四： 按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT last_name ,salary FROM employees ORDER BY LENGTH(last_name) DESC;

#案例五 ：查询员工信息，要求先按工资降序排序，再按员工编号升序排序[按多个字段排序]
SELECT * FROM employees ORDER BY salary DESC ,employee_id ASC;

2. 常见函数

功能： 类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
好处：1.隐藏了实现细节。? ?2. 提高代码重用性。
调用：SELECT 函数名 (实参列表) ? [FROM 表];
特点：
?? ??? ??? ?①叫什么（函数名） ? ② ?干什么 （函数功能）
分类：
?? ??? ??? ?一、单行函数
?? ??? ??? ?如concat、length、ifnull等
?? ??? ??? ?
?? ??? ??? ?
?? ??? ??? ??? ??? ?单行函数分类：
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?字符函数
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?数学函数
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?日期函数
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?其他函数【补充】
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?流程控制函数【补充】
?? ??? ??? ?
?? ??? ??? ?
?? ??? ??? ?二、分组函数
?? ??? ??? ?功能：做统计使用、又称为统计函数、聚合函数

1、字符函数
?? ?concat: 拼接
?? ?substr: 截取子串
?? ?upper: 转换成大写
?? ?lower: 转换成小写
?? ?trim: 去前后指定的空格和字符
?? ?ltrim: 去左边空格
?? ?rtrim: 去右边空格
?? ?replace: 替换
?? ?lpad: 左填充
?? ?rpad: 右填充
?? ?instr: 返回子串第一次出现的索引
?? ?length: 获取字节个数?? ?

案例：

#length  获取参数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

SHOW VARIABLES LIKE '%char%';


#2.CONCAT(str1,str2,...) 拼接字符串

SELECT CONCAT(last_name,'_',first_name)  姓名 FROM employees;

#3.upper、 lower;
SELECT UPPER('john');
SELECT LOWER('WQas');

#示例：将姓变大写，名变小写  然后拼接

SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;


#4.SUBSTR(str FROM pos FOR len) 、subString
#  将str字符串 从pos开始截取，直到len（字符长度）  
#注意：索引从1开始

SELECT SUBSTR('李莫愁爱上了陆展元',4) out_put;

#将  将str字符串 从pos开始截取，直到len（字符长度） 截取五个字符
SELECT SUBSTR('李莫愁爱上了陆展元',4,5) out_put;

#案例：姓名中 首字符大写，其他字符小写 然后用下划线拼接显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

#5.INSTR(str,substr)
#用于返回子串在字符串中占的索引第一位
SELECT INSTR("杨不殷六侠悔爱上了殷六侠",'殷六侠');


#6.TRIM([remstr FROM] str)

#去掉字符串首尾的字符
SELECT LTRIM('    张翠山    ');

SELECT TRIM('a' FROM 'aaaaaaaa张aaaaaaaaaaaaa翠aaaaa山aaa');


#7. lpad  用指定的字符实现   左填充指定长度  最终的长度和参数的一样
 SELECT LPAD('殷素素',10,'*');

#8. RPAD(str,len,padstr)
SELECT  RPAD('殷素素',10,'1');

#9. REPLACE 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏');

-------------------------------------------------------------------------
2、数学函数
?? ?round: 四舍五入
?? ?rand: 随机数
?? ?floor: 向下取整
?? ?ceil: 向上取整
?? ?mod: 取余
?? ?truncate: 截断

#round   
#对绝对值四舍五入然后取符号
SELECT ROUND(-1.55);

#小数点后保留两位
SELECT ROUND(1.567,2);

#ceil  向上取整    返回>=该参数的最小整数
SELECT CEIL(1.002);

SELECT CEIL(-1.002);


#FLOOR(X)  向下取整   ，返回<=该参数的最大整数
SELECT FLOOR(-9.99);


#TRUNCATE 截断后面不管是什么都不管

SELECT TRUNCATE(1.65,1);

#mod 取余

SELECT MOD(-10,-3);

SELECT 10%3;

-------------------------------------------------------------------------
3、日期函数
?? ?now: 当前系统日期+时间
?? ?curdate: 当前系统日期
?? ?curtime: 当前系统时间
?? ?str_to_date: 将字符转换成日期
?? ?date_format: 将日期转换成字符

#now   返回当前系统日期+时间

#STR_TO_DATE(str,format)：将日期格式的字符转换成指定格式的日期

#DATE_FORMAT(date,format)  将日期转化成字符串
SELECT NOW();

#curdate 返回当前系统日期，不包含时间

SELECT CURDATE();

#curtime  返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分。年、月、日、小时 、分钟、秒
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT MINUTE(NOW());

#STR_TO_DATE(str,format)  将字符通过指定的格式转化成日期
SELECT STR_TO_DATE('2000-02-6','%Y-%c-%d');

#查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate ='1992-4-3';

SELECT * FROM employees WHERE hiredate =STR_TO_DATE('3-4-1992','%d-%c-%Y');


#DATE_FORMAT(date,format)   将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y-%m-%d');

#查询有奖金的员工名和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(hiredate,'%m月%d日 %y年') FROM employees WHERE commission_pct IS NOT NULL;

-------------------------------------------------------------------------
4、流程控制函数
?? ?if 处理双分支
?? ?case语句 处理多分支
?? ??? ?情况1：处理等值判断
?? ??? ?情况2：处理条件判断

#1.if函数： if else 的效果
SELECT IF(10<5,'大','小');


SELECT  last_name ,commission_pct ,IF(commission_pct is NULL,'没奖金，呵呵','有奖金。嘻嘻')   FROM employees;


#case函数的使用一：switch case  的效果

/*

mysql中  
case 要判断的字段或表达式
WHEN 常量一  THEN 要显示的值1 或语句1;
WHEN 常量二  THEN 要显示的值2  或语句2;
....
ELSE 要现实的值n或语句n;
end;
*/


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


SELECT salary '原工资'  ,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 新工资  FROM employees;


/*3.case 函数的使用二： 类似于 多重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  工资级别 FROM employees;

-------------------------------------------------------------------------
5、其他函数
?? ?version: 版本
?? ?database: 当前库
?? ?user: 当前连接用户

#版本号
SELECT VERSION();   

#查看当前使用的数据库
SELECT DATABASE();

#查看当前使用者
SELECT USER();