SQL函数查询

#SQL

#按条件表达式筛选
#案例一：查询员工工资大于12000的员工信息
SELECT 
  CONCAT(`first_name`,'_',`last_name`),
   `salary` 
FROM
  `employees` AS 工资 
WHERE salary > 12000 ;


#案例二：查询部门编号不等于90的员工名和部门编号,在SQL语句中<>就是表示不等于
SELECT 
  CONCAT(`first_name`, '_', `last_name`),
  `department_id` 
FROM
  `employees` 
WHERE 
   `department_id` <> 90 ;

#按逻辑表达式筛选

#排序查询
#order by后面增加排序列表，【asc | desc】，表示升序还是降序
#案例：查询员工信息，要求工资从高到底
SELECT 
  * 
FROM
  `employees` 
ORDER BY `salary` DESC ;

#查询部门编号大于等于90的员工信息，按入职的时间先后顺序排序
SELECT 
  * 
FROM
  `employees` 
WHERE `department_id` >= 90 
ORDER BY `hiredate` ASC ;

#按姓名的长度显示员工的姓名和工资
SELECT 
  LENGTH(`last_name`) AS 字节长度,
  `salary` 
FROM
  `employees` 
ORDER BY LENGTH(`last_name`) DESC ;

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

#字符函数,一个汉字3个字节，不同字符集不同
#length获得参数集的字节长度
SELECT LENGTH('haixiquan');
SELECT LENGTH('海溪泉');

#查看字符集
SHOW VARIABLES LIKE '%char%';

#concat表示拼接字符串
SELECT 
  CONCAT(`first_name`, '_', `last_name`) AS 名字 
FROM
  `employees` ;

# upper lower 的使用，改变大小写
SELECT UPPER('tom');
SELECT LOWER('TOM');

#将姓变大写，名变小写，然后拼接
SELECT 
  CONCAT(
    UPPER(`first_name`),
    '_',
    LOWER(`last_name`)
  ) 
FROM
  `employees` ;
 
#substr,sbustring
SELECT SUBSTR('李莫愁爱上陆展元',6); #陆展元,SQL语句从1开始

SELECT SUBSTR('李莫愁爱上陆展元',1,3); #李莫愁,3表示长度
#姓名中首字符大写，其他字符小写，并用_连接
SELECT 
  CONCAT(
    UPPER(SUBSTR(`last_name`, 1, 2)),
    '__',
    LOWER(SUBSTR(`last_name`, 3))
  ) 
FROM
  `employees` ;

#instr 返回字串第一次出现的索引
SELECT INSTR('李莫愁爱上陆展元','陆展元'); #返回值为6，返回起始索引

#trim 去前后空格
SELECT TRIM('    张翠山');

SELECT TRIM('    张  翠山'); #中间空格没法去掉

SELECT TRIM('a' FROM 'aaa张aaa翠山aaa'); #张aaa翠山
#lpad 为左填充  *******温老板
SELECT 
  LPAD('温老板', 10, '*') AS out_put ;
#rpad 为右填充

#repalce为替换
SELECT REPLACE('温老板爱吃粉','温老板','海溪泉');

#round 四舍五入
SELECT ROUND(1.65);  # 2
SELECT ROUND(1.657,2); #保留两位小数 1.66

#ceil向上取整
SELECT CEIL(1.65);    #2

#floor 为向下取整
SELECT FLOOR(1.66);   #1

#truncate 为截断
SELECT TRUNCATE(1.666,2);  #1.66

#mod为取余 
SELECT MOD(10,3);  #1

#日期函数

#now 返回当前日期+时间
SELECT NOW();
#curdate 返回当前日期
SELECT CURDATE();

#curtime 返回当前时间
SELECT CURTIME();

#获得年月日 小时 分钟 秒
SELECT YEAR(NOW());  #2021
SELECT YEAR('2021-2-22'); #2021
SELECT YEAR(`hiredate`) AS 年 FROM `employees`;
SELECT DAY(NOW());   #23
SELECT MONTH(NOW());  #3
SELECT MONTHNAME(NOW());  #March

#str_to_date 将字符转换为日期
SELECT STR_TO_DATE('9-19-1950','%c-%d-%Y');

SELECT 
  * 
FROM
  `employees` 
WHERE `hiredate` = STR_TO_DATE('4-3-1992', '%c-%d-%Y') ;

#date_format 将日期转换为字符
SELECT DATE_FORMAT('1996/6/16','%Y年%c月%d日');

#其他函数
SELECT VERSION()；#查看版本
SELECT DATABASE(); #查看当前数据库 `myemployees`
SELECT USER();  #查找当前用户


#流程控制函数
SELECT IF(10>5,'是','不是'); # 是

SELECT 
  `last_name`,
  `commission_pct`,
  IF(
    `commission_pct` IS NULL,
    '没奖金',
    '有奖金'
  ) AS 备注 
FROM
  `employees` ;
  
#case函数的使用 ：switch case 的效果

#案例：查询员工的工资
# 部门号为30 员工工资显示为1.1倍
# 部门编号为40 员工工资显示为1.2 倍
# 部门编号为50 员工工资显示为1.3倍
# 其他部门 员工工资显示为原工资
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`;

#case函数的使用2，类似于多重if
SELECT `salary`,
CASE
WHEN `salary`>20000 THEN 'A'
WHEN `salary`>15000 THEN 'B'
WHEN `salary`>10000 THEN 'c'
ELSE 'D' 
END AS 工资等级
FROM `employees` ;