orm: object relation mappint 对象关系映射

一、常见函数

1. 字符函数

1. CONCAT 拼接字符

-- 1. CONCAT 拼接字符
SELECT CONCAT('hello', first_name, last_name) AS 备注
FROM employees;

2. LENGTH 获取字节长度

-- 2. LENGTH 获取字节长度
SELECT LENGTH('获取长度');
## 12
## UTF-8 一个中文三个字节

3. CHAR_LENGTH 获取字符长度(个数)

-- 3. CHAR_LENGTH 获取字符长度
SELECT CHAR_LENGTH('获取长度');
# 4

4. SUBSTRING 截取子串

-- 4. SUBSTRING 截取子串
/**
  substr(str, 起始索引， 截取的字符长度）
  substr(str, 起始索引)

  其实索引从1开始
  */
SELECT SUBSTRING('张三做了件不好的事', 1, 3);

# substr substring 效果一致

5. INSTR 获取字符串第一次出现的位置

-- 5. INSTR 获取字符串第一次出现的索引
SELECT INSTR('数字出现在第几个索引', '索引');
# 9

6. TRIM 去前后指定字符，默认空格

-- 6. TRIM 去前后指定字符，默认空格
SELECT TRIM('     小     买    部     ') AS col;
SELECT TRIM('x' FROM 'xxxxx小xxxxx买xxxx部xxxxx') AS col;

7. LPAD/RPAD 左填充/右填充

-- 7. LPAD/RPAD 左填充/右填充
SELECT LPAD('西游记', 10, '看');  # 看看看看看看看西游记
SELECT RPAD('西游记', 10, '好看'); # 西游记好看好看好看好

# 填充结果是整体字符长度到10

8. UPPER/LOWER 大写/小写

案例

查询员工表的姓名，要求格式：姓首字母大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后其别名 OUTPUT

SELECT CONCAT(
            UPPER(first_name),
            '_',
            UPPER(SUBSTRING(last_name, 1, 1)),
            LOWER(SUBSTRING(last_name, 2))
           ) AS 'OUTPUT'
FROM employees;

9. STRCMP 比较字符大小

-- 9. STRCMP
SELECT STRCMP('aba', 'aaaaaa'); # 1
SELECT STRCMP('aaa', 'aaaaaa'); # -1
SELECT STRCMP('aaa', 'aaa');    # 0

10. LEFT/RIGHT 截取子串

-- 10 LEFT/RIGHT
SELECT LEFT('123', 1); # 1
SELECT RIGHT('123', 1);# 3

2. 数学函数

1. ABS 绝对值

-- 1 abs
SELECT ABS(-1433223);

2. CEIL 向上取整

返回 >= 该参数的最小整数

-- 2. ceil
SELECT CEIL(0.9); # 1
SELECT CEIL(-0.9); # 0
SELECT CEIL(1.9); # 2

3. FLOOR 向下取整

-- 3. floor
SELECT FLOOR(0.9); # 0
SELECT FLOOR(-0.9); # -1
SELECT FLOOR(1.9); # 1

4. ROUND 四舍五入

-- 4. round
SELECT ROUND(0.9); # 1
SELECT ROUND(0.4); # 0
SELECT ROUND(-0.9); # -1
SELECT ROUND(1.9); # 2

SELECT ROUND(1.888889898098908098094); # 2
SELECT ROUND(1.888889898098908098094, 2); # 1.89

5. TRUNCATE 截断

-- 5. truncate
SELECT TRUNCATE(1.1433223, 0); # 1
SELECT TRUNCATE(1.1433223, 1); # 1.1
SELECT TRUNCATE(11433223, 0); # 11433223

## 第二个参数相当于保留小数位数，但是不会四舍五入，直接截取返回

6. 取余

-- 6. MOD
SELECT MOD(-10, 3); # -1
SELECT -10 % 3;     # -1
SELECT MOD(-10, 3); # -1

# -10 % 3 = -10 - (-10)/3*3 = -1
SELECT -10 % 3;     # -1
SELECT -10 % -3;     # -1
SELECT 10 % 3;     # 1
SELECT 10 % -3;     # 1

3. 日期函数

1. NOW 获取当前时间

-- 1. NOW
SELECT NOW();

2. CURDATE 获取当前日期

-- 2. CURDATE
SELECT CURDATE();

3. CURTIME 获取当前时间

-- 3. CURTIME
SELECT CURTIME();

4. DATEDIFF 时间差

-- 4. DATEDIFF
SELECT DATEDIFF('2020-11-30', '2020-12-30'); # -30
SELECT DATEDIFF('2020-12-30', '2020-11-30'); # 30

# 返回天数，前面减后面

5. DATE_FORMAT 格式化日期输出

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

-- 5. date_format
SELECT DATE_FORMAT('2020-10-01', '%Y年%m月%d日 %H小时%i分钟%s秒') AS 国庆;

结果：

国庆
2020年10月01日 00小时00分钟00秒

查询入职日期:

SELECT DATE_FORMAT(hiredate, '%Y年%m月%d日 %H小时%i分钟%s秒') AS 'hiredate'
FROM employees;

6. STR_TO_DATE 按指定格式解析字符串为日期类型

-- 6 STR_TO_DATE
SELECT STR_TO_DATE('3/15 1998', '%m/%d %Y'); # 1998-03-15
SELECT STR_TO_DATE('3/15', '%m/%d'); # 0000-03-15
SELECT STR_TO_DATE('3/15 98', '%m/%d %y'); # 1998-03-15
SELECT STR_TO_DATE('3/15 11', '%m/%d %y'); # 2011-03-15

# 使用
SELECT *
FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998', '%m/%d %Y');

3. 流程控制函数

1. IF 函数

SELECT IF(100>9, 'good', 'bad'); 

# 需求： 如果有奖金，则显示最终奖金，如果没有则显示0
SELECT
    CONCAT(first_name, ' ', last_name) AS '姓名',
    IF(commission_pct IS NULL, 0,
       salary * 12 * commission_pct) AS '奖金'
FROM employees
ORDER BY 奖金 DESC;

2. CASE

情况1: 类似于switch语句，可以实现等值判断

CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END
# 类似switch

# 案例
/**
  部门编号是30， 工资显示为2倍
  部门编号是50， 工资显示为3倍
  部门编号是60， 工资显示为4倍
  否则不变

  显示 部门编号，新工资，旧工资
  */
SELECT department_id,
       CASE department_id
           WHEN 30 THEN salary*2
           WHEN 50 THEN salary*3
           WHEN 60 THEN salary*4
           ELSE salary
           END AS new_salary,
       salary
FROM employees;

情况2:类似于多重IF语句，实现区间判断

# 案例
/**
  如果工资>20000，显示级别A
  工资>15000，显示级别B
  工资>10000，显示级别C
  否则，显示D
 */
SELECT salary,
       CASE WHEN salary > 20000 THEN 'A'
            WHEN salary > 15000 THEN 'B'
            WHEN salary > 10000 THEN 'C'
            ELSE 'D'
           END AS grade
FROM employees;

案例

# 案例
# 显示系统时间(注:日期+时间)
SELECT NOW();
# 查询员工号,姓名,工资,以及工资提高百分之 20%后的结果(new salary)
SELECT employee_id,
       CONCAT(first_name, ' ', last_name) AS 'name',
       salary * (1 + 0.2) AS 'new salary'
FROM employees;
# 将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT CONCAT(first_name, ' ', last_name) AS employees_name,
       LENGTH(CONCAT(first_name, ' ', last_name)) AS name_length
FROM `employees`
ORDER BY substr(employees_name, 1, 1);
# 做一个查询,产生下面的结果
# <last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,
    ' earns ',
    salary,
    ' monthly but wants ',
    salary * 3) AS 'Dream Salary'
FROM employees;
# 使用 case-when,按照下面的条件:
# job  grade
# AD_PRES  A
# ST_MAN  B
# IT_PROG  C
# SA_REP  D
# ST_CLERK  E
SELECT last_name as Last_name,
       job_id AS Job_id,
       CASE job_id
           WHEN 'AD_PRES' THEN 'A'
           WHEN 'ST_MAN' THEN 'B'
           WHEN 'IT_PROG' THEN 'C'
           WHEN 'SA_REP' THEN 'D'
           WHEN 'ST_CLERK' THEN 'E'
           ELSE 'NONE'
           END AS 'Grade'
FROM employees;

二、分组函数

分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数。

1. 函数

1. SUM 求和

2. AVG 求平均数

3. MAX 求最大值

4. MIN 求最小值

5. COUNT 计算非空字段的个数

1. 统计行数

SELECT COUNT(*)
FROM employees;

## count 加 * 号 统计行数

SELECT COUNT(1)
FROM employees;

SELECT COUNT(1)
FROM employees
WHERE department_id = 30;
## 与 * 效果一致
## 会在where结果中添加一行1再统计
## 效率上 * 更高

2. 搭配 DISTINCT 实现去重的统计

# 需求： 查询有员工的部门的个数
SELECT COUNT(DISTINCT  department_id)
FROM employees;

案例：

# 案例1 查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT
    SUM(salary),
    AVG(salary),
    MIN(salary),
    MAX(salary),
    COUNT(salary)
FROM employees;

# 案例2 添加查询条件
# 1. 查询表中的记录数
SELECT COUNT(employee_id)
FROM employees;
# 2. 查询表中有奖金的人数
SELECT COUNT(commission_pct)
FROM employees;
# 3. 查询表中月薪大于2500的人数
SELECT COUNT(employee_id)
FROM employees
WHERE salary > 2500;
# 4. 查询有领导的人数
SELECT COUNT(manager_id)
FROM employees;

2. 分组查询 GROUP BY 引入

# 查询每个部门的总工资、平均工资
SELECT department_id,
    SUM(salary) AS '总工资',
    AVG(salary) AS '平均工资'
FROM employees
GROUP BY department_id;

三、分组查询

1. 语法

SELECT 查询名称
FROM 表名
WHERE 筛选条件
GROUP BY 分组列表;

执行顺序

from
where
group by
having
select
order by

2. 特点

查询列表往往是分组函数和被分组的字段
分组查询中的筛选分为两类
1. 分组前筛选
2. 分组后筛选
筛选的基表使用的关键词位置

分组前筛选 原始表 where group by 的前面

分组后筛选 分组后的结果集 having group by 的后面

筛选的基表	使用的关键词	位置
分组前筛选	原始表	where	group by 的前面
分组后筛选	分组后的结果集	having	group by 的后面

3. 执行

1. 简单的分组

# 案例1 查询每个工种的员工平均工资
SELECT job_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY job_id;

# 案例2 每个领导的手下人数
SELECT manager_id,
       COUNT(*)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

2. 实现分组前的筛选

# 案例1 查询邮箱中包含a字符的 每个部门的最高工资
# 案例1 查询邮箱中包含a字符的 每个部门的最高工资
SELECT department_id,
       MAX(salary) AS max_salary
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

# 案例2 查询每个领导手下员工的平均工资
SELECT manager_id,
       AVG(salary) AS ems_avg_salary
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

# 案例2 查询每个领导手下有奖金员工的平均工资
SELECT manager_id,
       AVG(salary) AS ems_avg_salary
FROM employees
WHERE manager_id IS NOT NULL
AND commission_pct IS NOT NULL
GROUP BY manager_id;

3. 实现分组后的筛选

HAVING 函数，实现分组后的筛选。

# 案例1 筛选出部门员工数>5的部门的员工个数
SELECT department_id,
       COUNT(*) AS 'ems'
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

# 案例2 每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,
       MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

# 案例3： 领导编号>102的 每个领导手下的最低工资大于5000的最低工资
SELECT manager_id,
       MIN(salary) AS min_salary
FROM employees
WHERE manager_id IS NOT NULL
    AND manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000;

4. 实现排序

# 案例 每个工种有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
SELECT job_id,
       MAX(salary) AS max_salary
FROM employees
GROUP BY job_id
HAVING MAX(salary) > 6000
ORDER BY max_salary;

5. 按多个字段分组

# 案例 查询每个工种每个部门的最低工资，并按最低工资降序
SELECT job_id, department_id,
       MIN(salary) AS min_salary
FROM employees
GROUP BY job_id, department_id
ORDER BY min_salary DESC;

[mysql] 一些函数

一、常见函数

1. 字符函数

1. CONCAT 拼接字符

2. LENGTH 获取字节长度

3. CHAR_LENGTH 获取字符长度(个数)

4. SUBSTRING 截取子串

5. INSTR 获取字符串第一次出现的位置

6. TRIM 去前后指定字符，默认空格

7. LPAD/RPAD 左填充/右填充

8. UPPER/LOWER 大写/小写

案例

9. STRCMP 比较字符大小

10. LEFT/RIGHT 截取子串

2. 数学函数

1. ABS 绝对值

2. CEIL 向上取整

3. FLOOR 向下取整

4. ROUND 四舍五入

5. TRUNCATE 截断

6. 取余

3. 日期函数

1. NOW 获取当前时间

2. CURDATE 获取当前日期

3. CURTIME 获取当前时间

4. DATEDIFF 时间差

5. DATE_FORMAT 格式化日期输出

6. STR_TO_DATE 按指定格式解析字符串为日期类型

3. 流程控制函数

1. IF 函数

2. CASE

情况1: 类似于switch语句，可以实现等值判断

情况2:类似于多重IF语句，实现区间判断

案例

二、分组函数

1. 函数

1. SUM 求和

2. AVG 求平均数

3. MAX 求最大值

4. MIN 求最小值

5. COUNT 计算非空字段的个数

1. 统计行数

2. 搭配 DISTINCT 实现去重的统计

案例：

2. 分组查询 GROUP BY 引入

三、分组查询

1. 语法

执行顺序

2. 特点

3. 执行

1. 简单的分组

2. 实现分组前的筛选

3. 实现分组后的筛选

4. 实现排序

5. 按多个字段分组