MySQL 排序 基础函数

#排序 ORDER BY   ASC (默认升序） DESC 降序  这个语句放在最后
SELECT * from employees ORDER BY salary DESC;
SELECT salary from employees ORDER BY salary ASC ;
#查询部门编号>=90的员工信息，按入职时间的先后顺序
SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employees.hiredate ASC;
#按年薪的高低显示员工的信息和年薪 
SELECT *,salary*12*(1+IFNULL(employees.commission_pct,0)) AS 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(employees.commission_pct,0)) DESC;
#按照别名排序
SELECT *,salary*12*(1+IFNULL(employees.commission_pct,0)) AS 年薪
FROM employees
ORDER BY 年薪 DESC;
# 按照姓名长度显示员工姓名和工资
SELECT LENGTH(last_name) AS 字节长度,last_name,salary
FROM employees
ORDER BY 字节长度 DESC;
#查询员工信息，要求先按照工资升序，再按照员工编号降序(多个字段查询）
SELECT salary,employee_id
FROM employees
ORDER BY salary,employee_id DESC;
#查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
SELECT last_name,employees.department_id,salary*12*(1+IFNULL(employees.commission_pct,0)) as 年薪
FROM employees
ORDER BY 年薪 DESC,last_name;
# 选择工资不在8000到17000的员工姓名和工资，按工资降序
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;
# 查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT *,LENGTH(email)
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
# 常见函数
#LENGTH(str)字符函数
SELECT LENGTH('john');# CHAR型一个字母占一个字节
SELECT LENGTH('我爱学习');汉字一个占3个
#CONCAT(str1,str2,...)拼接字符串
SELECT CONCAT(last_name,'_',first_name)
FROM employees;
#UPPER(str)变大写 LOWER(str) 变小写
SELECT UPPER('jkh');#JKH
SELECT LOWER('HJH');#hjh
#将姓变大写，把名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) AS 姓名
FROM employees;
#SUBSTRING(str FROM pos FOR len) SUBSTR(str FROM pos FOR len)
SELECT SUBSTR('我爱学习',1,2)  out_put;#我爱
#姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(first_name,2)))
out_put
FROM employees;
#INSTR(str,substr)返回字串第一次出现的索引，如果找不到返回0
SELECT INSTR('我爱学习','学习') AS out_put;
#TRIM([remstr FROM] str) 删除其余东西
SELECT LENGTH(TRIM(' 我爱你   ')) AS out_put;#9
#去除a
SELECT TRIM('a' FROM 'aaaa我爱你aaa') as out_put;#我爱你
#LPAD(str,len,padstr)用指定的字符实现左填充指定长度
SELECT LPAD('我爱你',5,'*') as out_put;#**我爱你
#RPAD(str,len,padstr)用指定的字符实现右边填充指定长度
SELECT RPAD('我爱你',5,'*') as out_put;
#REPLACE(str,from_str,to_str)替换
SELECT REPLACE('我爱学习学习','学习','看电视剧') AS out_put;#我爱看电视剧看电视剧


#数学函数
#ROUND(X)按照指定的小数位数进行四舍五入运算
SELECT ROUND(1.23);#1
SELECT ROUND(-1.23);#-1
SELECT ROUND(1.23,1);#1.2保留2位小数
#CEIL(X)向上取整 返回大于等于该数的最小整数
SELECT CEIL(2.344);#3
SELECT CEIL(-2.344);#-2
#FLOOR(X)返回小于等于该数的最大整数
SELECT FLOOR(2.344);#2
SELECT FLOOR(-2.344);#-3
#TRUNCATE(X,D)截断
SELECT TRUNCATE(2.333,2);#2.33
#MOD(N,M)取余  N-N/M*M  /取整
SELECT MOD(10,3);#1
SELECT 10%3;#1
SELECT MOD(-10,3);#-1


#日期函数
#NOW()返回当前日期时间
SELECT NOW();#2022-01-18 17:36:03
#CURDATE()返回日期
SELECT CURDATE();#2022-01-18
#CURTIME()返回时间
SELECT CURTIME();#17:37:28
#YEAR(date)获取指定的年，月，日，h,m,s
SELECT YEAR('1999-02-05');#1999

SELECT YEAR(employees.hiredate) AS 年 
FROM employees;

SELECT MONTH(NOW()) AS 年;#1 
SELECT DAYOFMONTH(NOW());#获取指定日期在一个月的位置1/18    #18
SELECT MONTHNAME(NOW()) 月;#January
# STR_TO_DATE(str,format)将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('4-2-1990','%c-%d-%Y');#1990-04-02
 SELECT *
 FROM employees
 WHERE hiredate=STR_TO_DATE('4-3-1992','%c-%d-%Y');
 #DATE_FORMAT(date,format)将日期转换成字符
 SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');#2022年01月18日
 #查询有奖金的员工名和入职日期（xx月/xx日 xx年）
 SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') as 入职日期
 FROM employees
 WHERE commission_pct is not NULL;
 
 
 #其他函数
 SELECT VERSION();
 SELECT DATABASE();
 SELECT USER();
 
 
 #流程控制函数
 #if
 SELECT IF(10>6,'da','xi');#da
 SELECT last_name,commission_pct,IF(commission_pct is null ,'没奖金','有钱')
 FROM employees;
 /*CASE
 case_value
	WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END CASE;
*/
/*查询员工工资
部门号=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 
	WHEN  department_id=30 THEN salary*1.1
	WHEN  department_id=40 THEN salary*1.2
	WHEN  department_id=50 THEN salary*1.3
	ELSE salary
END AS 新工资
FROM employees;



#查询系统日期时间
SELECT NOW();#2022-01-18 18:28:50
#查询员工号，姓名，工资，以及工资提高20%后的结果
SELECT employee_id,last_name,salary,salary*1.2 AS 新工资
FROM employees;
#将员工姓名按首字母排序，并写出姓名的长度
SELECT last_name,LENGTH(last_name) AS 长度,SUBSTR(last_name,1,1) AS 首字母
FROM employees
ORDER BY 首字母;