第五课 常见查询

• 概念：类似于Java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
• 好处：
  • 隐藏了实现细节
  • 提高代码的重用性
• 调用：

SELECT 函数名（实参列表） [FROM 表名]

• 分类：
  • 单行函数：CONCAT,LENGTH，IFNULL
    • 字符函数
    • 数学函数
    • 日期函数
    • 其他函数
    • 流程控制函数
  • 分组函数
    • 功能：做统计使用，又称为统计函数，聚合函数，组函数

1 单行函数

1.1 字符函数

函数名	功能
LENGTH	获取参数值的字节个数，不同字符集不同长度
CONCAT	拼接字符串
UPPER	转大写
LOWER	转小写
SUBSTR或SUBSTRING	截取字符串，SQL中索引从1开始的字符长度
INSTR	返回子串第一次出现的索引，如果找不到返回0
TRIM	去掉前后空格或指定字符
LPAD	用指定的字符实现左填充指定长度，长度不够时从左侧开始截取
RPAD	用指定的字符实现右填充指定长度，长度不够时从左侧开始截取
REPLACE	替换指定子串

# 1 LENGTH(str)
SELECT LENGTH('AAA');

# 2 CONCAT(str1,str2,...)
SELECT CONCAT('A','B','C');

# 3 UPPER(str) & LOWER(str)
SELECT UPPER('aAa'),LOWER('BbB');

# 4 SUBSTR(str,pos) & SUBSTR(str,pos,len)
SELECT SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',10),SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',1,6);

# 5 INSTR(str,substr)
SELECT INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','NO'),INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ','CF');

# 6 TRIM([remstr FROM] str)
SELECT TRIM('A' FROM 'ABCDEFGHIJKLMNOPQRSTUVWXYZAAAA'),LENGTH(TRIM('   AAAAA    '));

# 7 LPAD(str,len,padstr) & RPAD(str,len,padstr)
SELECT LPAD('AAA',10,'B'),RPAD('BBB',10,'C');
SELECT LPAD('ABC',2,'B'),RPAD('BCD',1,'C');

# 8 `REPLACE`(str,from_str,to_str)
SELECT REPLACE('AAABBBCCCDDDEEEAAABBBCCCDDDEEE','AAA','FFF');

1.2 数学函数

函数名	功能
ROUND	四舍五入
CEIL	向上取整，返回>=该参数的最小整数
FLOOR	向下取整
TRUNCATE	截断
MOD	取余（计算公式：a-a/b*b）

# 1 ROUND(X) & ROUND(X,D)（保留小数点后D位）
SELECT ROUND(1.69),ROUND(1.123456,4);

# 2 CEIL(X)
SELECT CEIL(1.6589),CEIL(-1.2562);

# 3 FLOOR(X)
SELECT FLOOR(1.5695),FLOOR(-1.5862);

# 4 `TRUNCATE`(X,D)（只针对与小数）
SELECT TRUNCATE(1.23654,2),TRUNCATE(123456798,5);

# 5 `MOD`(N,M)
SELECT MOD(10,3),MOD(-10,3),MOD(10,-3),MOD(-10,-3);

1.3 日期函数

函数名	功能
NOW	返回当前系统日期 + 时间
CURDATE	返回当前系统日期
CURTIME	返回当前系统时间
YEAR,MONTH,DAY,HOUR,MINUTE,SECOND	返回年月日时分秒
MONTHNAME	返回月份的英文名称
STR_TO_DATE	将日期格式的字符转换成指定格式的日期
DATE_FORMAT	将日期转换成字符

• 日期转换格式符

格式符	功能
%Y	四位年份
%y	两位年份
%m	月份（01,02，03…）
%c	月份（1,2,3,4…）
%d	日（01,02…）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00,01,02…）
%s	秒（00,01,02…）

# 1 NOW() & CURDATE() & CURTIME()
SELECT NOW(),CURDATE(),CURTIME();

# 2 YEAR(date),MONTH(date),DAY(date),HOUR(time),MINUTE(time),SECOND(time)
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

# 3 MONTHNAME(date)
SELECT MONTHNAME(NOW());

# 4 STR_TO_DATE(str,format)
SELECT emp.first_name,emp.hiredate FROM employees emp WHERE emp.hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');

# 5 DATE_FORMAT(date,format)
SELECT DATE_FORMAT(NOW(),'%m-%d %Y');

# 6 DATEDIFF(expr1,expr2),查询员工表中的最大入职时间和最小入职时间相差的天数
SELECT DATEDIFF(MAX(emp.hiredate),MIN(emp.hiredate)) FROM employees emp;

2 流程控制函数

• IF 函数

# 1 IF(expr1,expr2,expr3)
SELECT
	emp.first_name,
	emp.commission_pct,
IF (
	emp.commission_pct IS NULL,
	'NO COM',
	'HAVE COM'
) AS "COM"
FROM
	employees emp;

• CASE 函数
  • case函数的使用一： switch case 的效果
  • 语法：

    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1;
    when 常量2 then 要显示的值2或语句2;
    ...
    else 要显示的值n或语句n;
    end
    

  • case 函数的使用二：类似于 多重if
  • 语法：

    case 
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    ...
    else 要显示的值n或语句n
    end
    

/* 2 
查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
SELECT
	emp.employee_id,
	emp.department_id,
	emp.salary,
	CASE emp.department_id
WHEN '30' THEN
	emp.salary * 1.1
WHEN '40' THEN
	emp.salary * 1.2
WHEN '50' THEN
	emp.salary * 1.3
ELSE
	emp.salary
END
FROM
	employees emp;

/* 3 
查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/

SELECT emp.employee_id,emp.salary,
CASE
WHEN emp.salary > 20000 THEN 'A'
WHEN emp.salary > 15000 THEN 'B'
WHEN emp.salary > 10000 THEN 'C'
ELSE 'D'
END AS "LEVEL"
FROM employees emp;

3 分组函数

• 功能：用作统计使用，又称为聚合函数、统计函数或组函数
• 分类
  • SUM 求和
  • AVG 平均值
  • MAX 最大值
  • MIN 最小值
  • COUNT 计算不为NULL的个数
• 参数支持的类型
  • SUM，AVG 一般用于处理数值型
  • MAX，MIN，COUNT 可以处理任何类型
• 所有分组函数均忽略 NULL 值
• 可以和DISTINCT搭配使用，实现去重(AVG 和 COUNT)

# 1 SUM(expr),AVG([DISTINCT] expr),MAX(expr),MIN(expr),COUNT(DISTINCT expr,[expr...])
SELECT
	SUM(emp.salary),
	AVG(emp.salary),
	MIN(emp.salary),
	MAX(emp.salary),
	COUNT(emp.salary)
FROM
	employees emp;

# 2 忽略NULL值测试
SELECT
	AVG(emp.commission_pct),
	SUM(emp.commission_pct) / 107,
	SUM(emp.commission_pct) / 35
FROM
	employees emp;

SELECT
	COUNT(emp.commission_pct)
FROM
	employees emp;

# 3 DISTINCT搭配使用
SELECT
	COUNT(DISTINCT emp.commission_pct),
	AVG(DISTINCT emp.commission_pct)
FROM
	employees emp;

3.1 COUNT()函数的使用

# 4 COUNT函数的使用
SELECT COUNT(emp.first_name) FROM employees emp;
SELECT COUNT(*) FROM employees emp;
SELECT COUNT(1) FROM employees emp;

• 以上三种是常用的统计行数的方法
• 性能：
  • MYISAM存储引擎下：COUNT(*)的效率高，因为引擎自身有计数器，直接返回结果
  • INNODB存储引擎下：COUNT(*)和COUNT(1)效率差不多，但是都比COUNT(常量)高
• 和分组函数一同查询的字段要求是GROUP BY后的字段