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