MYSQL学习笔记（二）-CSDN博客

本文链接：https://blog.csdn.net/weixin_46028497/article/details/115965146

样本库参见学习笔记一

#排序查询
/*
引入：
	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;