样本库参见学习笔记一
#排序查询
/*
引入:
select * from employees;
语法:
select 查询列表
from 表
【where 筛选条件】
order by 排序列表【asc|desc】
特点
desc降序
asc升序
默认为升序
order by 子句中可以支持单个字段,多个字段,表达式,函数,别名
order by子句一般是放在查询语句最后面,limit子句除外
*/
#案例:查询员工信息,要求工资从高到低排序
SELECT* FROM employees ORDER BY salary DESC;
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)) DESC ;
#案例四:按照别名排序
SELECT
*,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC ;
#案例五:按照姓名的长度显示员工的姓名和工资【按照函数排序】
SELECT
LENGTH(last_name) AS 字节长度,
last_name,
salary
FROM
employees
ORDER BY LENGTH(last_name) DESC ;
#案例六:查询员工信息,要求先按工资排序,再按员工编号排序【按多个字段排序】
SELECT
*
FROM
employees
ORDER BY salary ASC,
employee_id DESC ;#这个意味着先按照工资进行排序,如果工资相同按照id降序排序,这就是多字段排序的意义
#1.测试查询员工的姓名,部门号和年薪,按照年薪降序,按姓名升序
SELECT
last_name,
department_id,
salary * 12 * (1+ IFNULL(commission_pct, 0)) AS 年薪
FROM
employees
ORDER BY 年薪 DESC,
last_name ASC ;
#2.选择工资不在800到17000的员工姓名和工资,按照工资降序
SELECT
CONCAT(first_name,' ', last_name) AS 姓名,
salary
FROM
employees
WHERE
!(salary BETWEEN 8000 AND 17000)
ORDER BY salary DESC;
#3.查询邮箱中包含e的员工信息,并先按照邮箱的字节数降序排列,再按照部门号升序
SELECT
*,
LENGTH(email)
FROM
employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,
department_id ASC;
二:
#常见函数
/*
功能:类似于java的方法,类似C++的函数,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1,隐藏了实现细节,2,提高代码的重用性
调用:select 函数名(实参列表) 【from 表】;
特点:
1.叫什么(函数名)
2.干什么(函数功能)
分类:
1.单行函数
如 concat,length,ifnull等
2.分组函数
功能:做统计使用,又称为统计函数,聚合函数,组函数
*/
#单行函数
/*
字符函数:
length
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数
日期函数
其它函数
流程控制函数
*/
#1.字符函数
#length
SELECT LENGTH(' ');
SELECT LENGTH('张三丰');#它占了三个字节,一个汉字三个字节
SELECT LENGTH('asd');#一个字母占一个字节
#2. concat 拼接字符串
SELECT
CONCAT(last_name, "_", first_name)
FROM
employees ;
#3.upper,lower
SELECT UPPER('john');
SELECT LOWER('JHOn');
#示例:将姓大写,将名小写
SELECT CONCAT(UPPER(last_name),' ',LOWER(first_name)) AS 姓名 FROM employees;
#4.substr substring
#索引从1开始!!!!!!
SELECT SUBSTR('阿巴1阿巴2阿巴',7) AS output;
#从指定索引处指定字符长度
SELECT SUBSTR('阿巴1阿巴2阿巴',3,3) AS output;#第二个代表长度
#案例:将姓名中的首字符大写,其它字符小写 然后用_拼接
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
LOWER(SUBSTR(last_name, 2)),
'_',
first_name
) AS 姓名
FROM
employees ;
#5.instr 返回查找的起始索引值
SELECT
INSTR(
'杨不悔殷六侠爱上了殷六侠',
'殷六侠'
) AS output ;
#返回查找的起始索引,而且只会返回第一次出现的索引
#6. trim 去除前后空格
SELECT TRIM(' dididi ') AS output;
SELECT TRIM('a' FROM'aaaaaaaaaaaaaaaaaaaaaaaaaa张aaaaaaaaaaaaaa粑aaaaaaaaaaaaaaa粑aaaaaaaaaaaa') AS output;
#它只会去除前后中间的不会去除,它输出的张aaaaaaaaaaaaaa粑aaaaaaaaaaaaaaa粑
#7. lpad 左填充,用指定的字符实现左填充指定长度
SELECT LPAD('阿西吧',10,'****') AS output;
#8.rpad 右填充,用指定字符实现右填充指定长度
SELECT RPAD('阿西吧',12,'ab') AS output;
#replace替换
SELECT REPLACE('阿西吧滴滴阿西吧阿西吧','阿西吧','扣你吉瓦') AS output;
#二,数学函数
#1.round 四舍五入
SELECT ROUND(1.65);#直接进位到2
SELECT ROUND(-1.65);
SELECT ROUND(-1.45);
SELECT ROUND(11);#只是小数位进位
SELECT ROUND(1.567,2);#小数位保留两位
#2.ceil 向上取整 ,返回>=该参数的最小整数
SELECT CEIL(1.00);#结果仍为1
SELECT CEIL(1.43);
SELECT CEIL(-1.3);#结果为-1,-1大于它
#3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);#返回-10
#4.truncate 截断
SELECT TRUNCATE(1.65,1);#这个1代表小数点几位,2为两位
#5.mod 取余
SELECT MOD(10,3);
SELECT MOD(-10,-3);
SELECT MOD(10,-3);
SELECT 10%3;
#三,日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期,不包含时间
SELECT CURDATE();
#curtime 返回当前时间,不包含日期
SELECT CURTIME();
#可以指定获取部分,年,月,日,小时,分钟,秒
SELECT YEAR(NOW());
SELECT YEAR('1999-03-03');
SELECT YEAR(hiredate)FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
#str_to_date:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y');
#date_format: 将日期转换为字符
SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
/*
%Y四位的年份
%y两位的年份
%m月份(01,02,03......12)
%c月份(1,2.......12)
%d日(01,02,.....)
%H小时(24小时制)
%h小时(12小时制)
%i分钟
%s秒
*/
#查询入职日期为1992-4-3的员工信息
SELECT
*
FROM
employees
WHERE hiredate = '1992-4-3' ;
SELECT
*
FROM
employees
WHERE hiredate = STR_TO_DATE('1992-4 3', '%Y-%m %d') ;
#查询有奖金的员工名和入职日期(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();#代表当前用户
#五,流程控制函数
#1.if 函数 if else函数
SELECT IF(10>5,'大','小');
SELECT
last_name,
commission_pct,
IF(
commission_pct IS NULL,
'没奖金',
'有奖金'
) AS 备注
FROM
employees;
#2.case函数
#使用一:类似switch case用法
/*
语法:
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或者语句1;
when 常量2 then 要显示的值2或者语句2;
...
else 要显示的值n或语句n;
end
*/
#案例:查询员工的工资,要求
/*
部门号=30,显示工资为1.1倍
部门号=40,显示工资为1.2倍
部门号=50,显示工资为1.3倍
部门号=60,显示工资为1.4倍
*/
SELECT
salary AS 原始工资,
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 ;
#使用二:类似多重if
/*
语法:
case
when 条件1 then 要显示的值1或语句;
when 条件2 then 要显示的值2或语句;
when 条件3 then 要显示的值3或语句;
when 条件4 then 要显示的值4或语句;
when 条件5 then 要显示的值5或语句;
...
else 要显示的值n或语句;
end
*/
#案例;查询员工的工资情况
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
其它显示D级别
*/
SELECT salary AS 工资,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary BETWEEN 15000 AND 20000 THEN 'B'
WHEN salary BETWEEN 10000 AND 15000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;