MySQL学习第二天 排序查询 常用函数

#排序查询  
/*语法： 
select 查询列表 from 表 where 筛选条件 order by 排序列表 asc | desc
order by 支持单字段和多字段 别名 表达式 函数
一般放在语句最后面 limite语句除外
*/
#查询员工信息工资从高到低排列

SELECT * FROM `employees` ORDER BY `salary` DESC;#从高到低 降序
SELECT * FROM `employees` ORDER BY `salary` ASC; #从低到高 升序
SELECT * FROM `employees` ORDER BY `salary` #不写默认升序

#查询部门编号大于等于90，按入职时间进行排序

SELECT *FROM `employees`WHERE `department_id`>=90 ORDER BY `hiredate` ASC;

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

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

#按年薪的高低显示员工的信息和年薪（按函数排序）
SELECT *,`salary`*12*(1+IFNULL(`commission_pct`,0))AS 年薪 FROM `employees` ORDER BY LENGTH(`last_name`) DESC;

#查询员工信息先按工资排序升序在按员工编号降序排序
SELECT * FROM `employees` ORDER BY `salary`ASC,`employee_id` DESC;

#常见函数
/*
功能：类似于JAVA的方法，将一组逻辑语句封装在方法中
好吃：1，隐藏了实现细节 2，提高了代码的重用性。
调用： select 函数名（实参列表）
分类： 1 单行函数（concat length ifnull） 2 分组函数（一般统计使用）
*/
#字符函数
#length
SELECT LENGTH("niujon");
SELECT LENGTH("九章123");

#concat 拼接字符串
SELECT CONCAT(`last_name`,`first_name`) FROM `employees`;

#upper lower 大小写转换
SELECT UPPER("niuggg");
SELECT LOWER("SDDD");
#将名字中姓变大写名变小写
SELECT CONCAT(UPPER(`last_name`),LOWER(`first_name`))FROM `employees`;

#substr mySQL索引从1开始 截取字符串
SELECT SUBSTR("大声道撒大所",5); #从5开始截取
SELECT SUBSTR("大声道撒大所",3,2);#从3开始截取2个字符

#instr 返回str2在str1中的索引
SELECT INSTR("我爱学习学习爱我","学习");

#trim 去掉字符串中的指定字符
SELECT TRIM("       dsd   ");
SELECT TRIM('a' FROM "aaaaaa牛aaaaa点对点aaaa");

#lpad 用指定的字符左填充指定长度
SELECT LPAD("nn",10,' ');
#rpad 用指定的字符右填充指定长度
SELECT RPAD("nn",10,' ');
#replace 替换
SELECT REPLACE("我IDad哈得到盾华",'IDad','哈哈哈');


#数学函数
#round 四舍五入 
SELECT ROUND(1.25,1);  #1.3
#ceil 向上取整
SELECT CEIL(1.001)  #2
#floor 向下取整
SELECT FLOOR(1.001)  #1
#truncate 截断
SELECT TRUNCATE(1.699999,1)  #1.6
#mod 取余
SELECT MOD(-10,3) #-1

#日期函数
#now 返回当前系统时间
SELECT NOW();
#curdate 返回当前系统日期 不包含时间
SELECT CURDATE();
#curtime 返回当前系统时间 不包含日期
SELECT CURTIME();
#可以获取指定部分，年月日时分秒
SELECT YEAR(NOW());
SELECT YEAR("1992-2-3")
SELECT YEAR(`hiredate`) FROM `employees`
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());#英文显示
#str_to_date 将字符通过指定格式转换成日期
SELECT STR_TO_DATE("3-2-1992-","%c-%d-%Y-");
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),"%y--%c-%d")


#其他函数
VERSION()查看版本号
DATABASES()查看数据库

#流程控制函数
SELECT IF(10>5,"大","小");
#case  类似switch case 效果
/*
case 要判断的字段或者表达式
when 常量 then 要显示的值
when 常量 then 要显示的值
else 要显示的值 
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
END
FROM `employees`

/*
如果工资>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`

日期格式符

 

排序测试

SELECT CONCAT(`last_name`,`first_name`) 姓名 ,`manager_id`,`salary`*12*(1+IFNULL(`commission_pct`,0)) 年薪 FROM `employees` ORDER BY 年薪 DESC, 姓名 ASC;
SELECT CONCAT(`last_name`,`first_name`) 姓名 ,`salary` FROM `employees` WHERE `salary`<8000 OR salary>17000 ORDER BY`salary` DESC;
SELECT * FROM `employees` WHERE `email` LIKE "%e%" ORDER BY LENGTH(`email`)DESC,`department_id` ASC;

常用函数测试

SELECT NOW();
SELECT `employee_id`,`last_name`,`salary`,`salary`*1.2 AS 'new salary' FROM `employees`
SELECT `last_name`,LENGTH(`last_name`) FROM `employees`ORDER BY `last_name` 
SELECT CONCAT(`last_name`,"earns",`salary`,"monthly but wants " ,`salary`*3)FROM `employees`WHERE `salary`=24000
SELECT `employee_id`,
CASE `job_id`
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'IT_PROG' THEN 'D'
WHEN 'IT_PROG' THEN 'E'
END
FROM `employees`