Mysql基础（三）

进阶三：

排序查询：

语法：
select 查询列表
from 表
【where 筛选条件】

order by 排序的字段|表达式|函数|别名 【asc|desc】

特点：

? 1、asc代表的是升序，desc代表的是降序，如果不写，默认是升序

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

? 3、order by 子句一般是放在查询语句的最后面，limit子句除外

语句执行顺序是：

? 1、from 表 2、where 筛选条件、3、select 查询、4、order by 排序

#案例一：查询员工信息，要求工从高到低排序SELECT * FROM employees ORDER BY salary DESC;#如果不写 ASC 或DESC,默认是升序SELECT * FROM employees ORDER BY salary;#案例二【添加筛选条件】：查询部门编号>=90的员工信息，按入职时间的先后排序SELECT * FROM employeesWHERE department_id>=90ORDER BY hiredate ASC;#案例三【按表达式排序】：按年薪的高低显示员工的信息和年薪SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;#案例四【按别名排序】：按年薪的高低显示员工的信息和年薪SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪FROM employeesORDER BY 年薪 DESC;#案例五【按函数排序】：按姓名的长度显示员工的姓名和工资SELECT LENGTH(last_name) 字节长度,last_name,salaryFROM employees ORDER BY LENGTH(last_name) DESC;#案例六【按多个字段排序】：查询员工信息，要求先按工资升序排序，再按员工编号降序排序SELECT * FROM employeesORDER BY salary ASC,employee_id DESC;

进阶四

常见函数

函数分类：

? 字符函数
? 数学函数
? 日期函数
? 其他函数【补充】
? 流程控制函数【补充】

字符函数

# 进阶四# 常见函数/* 功能类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处：1、隐藏了实现细节 2、提高的代码的重用性 调用：selec 函数名(实参列表) 【from 表】; 特点： 1、方法名 2、功能 分类： 1、单行函数 如：concat 、length、ifnull等 2、分组函数 功能：做统计使用，有称为统计函数，聚合函数，组函数 */#一、字符函数#1、length 获取参数值的字节（返回字节数，汉字占三个字节，字母占1个字节）SELECT LENGTH('中国');#2、cancat 拼接字符串SELECT CONCAT(first_name,'_',last_name) 姓名 FROM employees;#3、upper(字符变大写)、lower(字符变小写)SELECT UPPER('json');SELECT LOWER('JSON');#案例：将姓变大写，名变小写，然后拼接SELECT CONCAT(UPPER(first_name),LOWER(last_name)) 姓名 FROM employees;#substr,substring#注意在sql语言中，索引都是从1开始#截取从索引开始到最后，指定索引SELECT SUBSTR('好好学习，天天向上',6) 截取索引后所有;#截取一段字符，指定开始索引和字符个数SELECT SUBSTR('好好学习，天天向上',1,4) 截取一段结果;#案例：姓名中首字符大写，其他字符小写 然后用'_' 拼接，显示出来SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) 结果FROM employees;#5、instr ： 返回子串第一次出现的索引，如果你找不到返回0SELECT INSTR('好好学习，天天向上','天天' ) AS 插入结果 ;#6、trim:去除前后指定字符，不指定字符时，去除前后空格SELECT TRIM(' 天天向上 ') 出去空格的结果;SELECT TRIM('a' FROM 'aaaaa天天aaaaa向上aaaa') 去掉a;#7、Lpad :用指定的字符实现左填充指定长度SELECT LPAD('好好学习',10,'*') AS 结果;#9、replace 替换函数SELECT REPLACE('好好学习，天天向上','向上','努力') AS 替换结果;

数学函数

#数学函数#round 四舍无入SELECT ROUND(-1.54);SELECT ROUND(1.456,2); #小数点后保留两位#ceil 向上取整,返回大于等于参数的最小整数SELECT CEIL(-1.02);#floor 向下取整，返回 <= 该参数的最大整数SELECT FLOOR(-0.99);#truncate 截断,保留小数端后几位SELECT TRUNCATE(1.68989,1);#mod 取余SELECT MOD(100,-98);SELECT 100%98;

日期函数

#日期函数#nowSELECT NOW();#curdate:返回当前系统日期，不包含时间SELECT CURDATE();#curtimeSELECT CURTIME();#可以获取指定的部分,年、月、日、小时、分钟、秒SELECT YEAR(NOW())AS 年;SELECT YEAR('1998-02-04') 年;SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月 ;SELECT MONTHNAME(NOW()) 英文月名;#str_to_date: 将日期格式的字符转换成指定格式的日期SELECT STR_TO_DATE('1999-05-30','%Y-%c-%d');#案例入职日期为1992-4-3的员工信息SELECT * FROM employees WHERE hiredate ='1992-4-3';#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;

日期函数

#日期函数#nowSELECT NOW();#curdate:返回当前系统日期，不包含时间SELECT CURDATE();#curtimeSELECT CURTIME();#可以获取指定的部分,年、月、日、小时、分钟、秒SELECT YEAR(NOW())AS 年;SELECT YEAR('1998-02-04') 年;SELECT YEAR(hiredate) 年 FROM employees; SELECT MONTH(NOW()) 月 ;SELECT MONTHNAME(NOW()) 英文月名;#str_to_date: 将日期格式的字符转换成指定格式的日期SELECT STR_TO_DATE('1999-05-30','%Y-%c-%d');#案例入职日期为1992-4-3的员工信息SELECT * FROM employees WHERE hiredate ='1992-4-3';#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;

其他函数

#其他函数SELECT VERSION();SELECT DATABASE();SELECT USER();

流程控制函数

#流程控制函数#1、if函数： if else 的效果SELECT IF(10>5,'10大于五','10小于5');SELECT last_name,commission_pct ,IF(commission_pct is NULL,'没奖金','有奖金') FROM employees;#2、case函数的使用一：switch case 的效果 /* case 要判断的字段会表达式 when 常量1 then 要显示的值1或语句1; when 常量1 then 要显示的值1或语句1; when 常量1 then 要显示的值1或语句1; when 常量1 then 要显示的值1或语句1; ...... else 要显示的值n 或语句n end */ /* #案例：查询员工的工资，要求的 部门号=30，显示工资为1.1倍 部门号=40，显示工资为1.2倍 其他部门，显示的工资为原工资*/SELECT salary 原始工资,department_id,CASE department_id WHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2ELSE salaryEND AS 新工资FROM employees;#case 函数的使用二：类似于 多重if /** case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示的值2或语句2 。。。。 else 要显示的值n 或语句n end *//*#案例：查询员工的工资的情况如果工资》200000，显示A级别如果工资> 15000,显示B级别如果工资>100000,显示C级别否则，显示D级别*/SELECT salary,CASEWHEN salary > 20000 THEN 'A'WHEN salary > 15000 THEN 'B'WHEN salary > 10000 THEN 'C'ELSE 'D'END AS 工资级别 FROM employees; 

总结函数：

? 一、单行函数
? 1、字符函数
? concat拼接
? substr截取子串
? upper转换成大写
? lower转换成小写
? trim去前后指定的空格和字符
? ltrim去左边空格
? rtrim去右边空格
? replace替换
? lpad左填充
? rpad右填充
? instr返回子串第一次出现的索引
? length 获取字节个数
?
? 2、数学函数
? round 四舍五入
? rand 随机数
? floor向下取整
? ceil向上取整
? mod取余
? truncate截断

? 3、日期函数
? now当前系统日期+时间
? curdate当前系统日期
? curtime当前系统时间
? str_to_date 将字符转换成日期
? date_format将日期转换成字符
? 4、流程控制函数
? if 处理双分支
? case语句 处理多分支
? 情况1：处理等值判断
? 情况2：处理条件判断
?

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

分组函数

#分组函数#分类：sum ：求和,avg: 平均值、max 最大值、min 最小值、count 计算个数#1、简单的使用SELECT SUM(salary) FROM employees;SELECT AVG(salary) FROM employees;SELECT MIN(salary) FROM employees;SELECT MAX(salary) FROM employees;SELECT COUNT(salary) FROM employees;SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数 FROM employees;#2、参数类型支持那些类型/* 特点： 1、sum 、avg 一般用于处理数值类型 max,min，count可以处理任何类型 2、以上分组函数都忽略null值 3、可以和Distinct搭配实现去重的运算 4、count函数的单独介绍 一般使用count(*)统计函数 5、和分组函数一同查询的字段要求是group by 后的字段 */SELECT SUM(last_name) ,AVG(last_name) FROM employees;#一般sum和avg支持数字类型的字段SELECT MAX(last_name),MIN(last_name) FROM employees;SELECT MAX(hiredate),MIN(hiredate) FROM employees;SELECT COUNT(last_name) FROM employees;SELECT COUNT(commission_pct) FROM employees;#3、是否忽略nullSELECT SUM(commission_pct),AVG(commission_pct) FROM employees;#4、和distinct搭配SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;#5、count函数的详细介绍SELECT COUNT(*) FROM employees;SELECT COUNT(salary) FROM employees;#效率MYISAM 存储引擎下，count(*)的效率最高;INNODB 存储引擎下，count(*)和 count(1)的效率差不多，比count(字段)要高一些;#6、很分组函数一同查询的字段有限制SELECT AVG(salary),employee_id FROM employees;