MySQL知识扩展

## 拓展1：CASE语句与条件

根据工资等级分类：>15000为'高'，8000-15000为'中'，<8000为'低'，显示员工ID和等级。

```sql
SELECT 
    employee_id,
    CASE 
        WHEN salary > 15000 THEN '高'
        WHEN salary BETWEEN 8000 AND 15000 THEN '中'
        ELSE '低'
    END AS '工资等级'
FROM employees;
```

查询学生表的学生年龄，要求输入学生编号，姓名，以及年龄段： 20以上-30之间 弱冠 30-40- 而立  40 -50 不惑

```sql
select sno, sname ,
case 
	when age between 20 and 30 then 'ruoguan'
	when age between 31and 40 then 'ruoguan'
	when age between 20 and 30 then 'ruoguan'
	else '年龄不正确'
	end as ‘nianlingduan’
from student；
```

## 拓展2：CONCAT()字符串拼接

查询职位ID(job_id)不是'IT_PROG'的员工姓名和职位。

```sql
SELECT CONCAT(first_name, ' ', last_name) AS name, job_id 
FROM employees 
WHERE job_id <> 'IT_PROG';
```

## 拓展3：多条件聚合

统计工资>10000或提成率>0.2的员工数量。

```sql
SELECT COUNT(*) 
FROM employees 
WHERE salary > 10000 OR commission_pct > 0.2;
```

## 拓展4：union 和 INTERSECT

#### UNION（并集）

- **功能**：合并两个或多个 `SELECT` 语句的结果集，**去除重复行**（若需保留重复行，可使用 `UNION ALL`）。
- 语法要求
  - 所有 `SELECT` 语句的列数必须相同。
  - 对应列的数据类型必须兼容。
  - 结果集的列名取自第一个 `SELECT` 语句。

示例：此查询返回学生和教师表中的所有唯一 `(id, name)` 组合。

```sql
SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;
```

- 应用场景
  - 合并不同表中的相似数据（如合并多个地区的销售记录）。
  - 去除重复数据后生成唯一列表。

#### INTERSECT（交集）

- **功能**：返回两个或多个 `SELECT` 语句结果集中的**公共记录**（即同时存在于所有查询中的行）。
- 语法要求
  - 与 `UNION` 相同，需满足列数和数据类型兼容性。

**示例**：此查询返回既是员工又是经理的 `employee_id`。

```sql
SELECT employee_id FROM employees
INTERSECT
SELECT manager_id FROM managers;
```

#### 核心区别

| **操作**    | **功能**         | **结果集特点**         | **典型场景**                       |
| ----------- | ---------------- | ---------------------- | ---------------------------------- |
| `UNION`     | 合并结果集并去重 | 包含所有查询的唯一行   | 生成不重复的合并列表（如所有用户） |
| `INTERSECT` | 返回公共记录     | 仅包含所有查询共有的行 | 找出重叠数据（如共同关注的用户）   |

#### 注意事项

1. **性能**：`INTERSECT` 可能比 `UNION` 更消耗资源，尤其在处理大型数据集时。
2. 替代方案: INTERSECT 可用 INNER JOIN  代替实现类似功能：

```sql
   SELECT DISTINCT a.id FROM table1 a
   INNER JOIN table2 b ON a.id = b.id;
   ```

- `UNION` 可用 `OR` 条件组合查询（但可能影响可读性）。
3. **数据库支持**：部分数据库（如 MySQL 8.0 以下版本）不支持 `INTERSECT`，需通过其他方式模拟。

## 拓展5：ROUND()函数

```sql
-- 统计每个部门的平均工资，显示部门ID和平均工资（保留两位小数）
SELECT department_id, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id;

select round(avg(sal),0) avgsal
from emp
group by deptno;
```

## 拓展6： Mysql中的函数

#### 1、聚合函数

用于对一组值进行计算并返回单一值，常与 `GROUP BY` 结合使用。

1. COUNT()
   - 功能：统计行数。
   - 示例：`SELECT COUNT(*) FROM employees;`（统计员工总数）
2. SUM()
   - 功能：计算数值列的总和。
   - 示例：`SELECT SUM(salary) FROM employees;`（计算工资总和）
3. AVG()
   - 功能：计算平均值。
   - 示例：`SELECT AVG(score) FROM students;`（计算平均分）
4. MAX() / MIN()
   - 功能：返回最大值或最小值。
   - 示例：`SELECT MAX(age), MIN(age) FROM users;`（查询最大和最小年龄）

#### 2、数学函数

处理数值运算，支持四舍五入、随机数生成等。

1. ABS(x)
   - 功能：返回绝对值。
   - 示例：`SELECT ABS(-10);`（返回 `10`）
2. ROUND(x, d)
   - 功能：四舍五入到小数点后 `d` 位。
   - 示例：`SELECT ROUND(3.14159, 2);`（返回 `3.14`）
3. RAND()
   - 功能：生成 `0~1` 之间的随机数。
   - 示例：`SELECT RAND() * 100;`（生成 `0~100` 的随机数）
4. MOD(x, y)
   - 功能：返回 `x` 除以 `y` 的余数。
   - 示例：`SELECT MOD(10, 3);`（返回 `1`）

#### 3、字符串函数

处理字符串拼接、截取、大小写转换等。

1. CONCAT(str1, str2, ...)
   - 功能：连接多个字符串。
   - 示例：`SELECT CONCAT('Hello', ' ', 'World');`（返回 `Hello World`）
2. SUBSTRING(str, pos, len)
   - 功能：从字符串中提取子串。
   - 示例：`SELECT SUBSTRING('MySQL', 2, 3);`（返回 `ySQ`）
3. UPPER() / LOWER()
   - 功能：转换大小写。
   - 示例：`SELECT UPPER('mysql'), LOWER('MYSQL');`（返回 `MYSQL` 和 `mysql`）
4. TRIM(str)
   - 功能：去除字符串首尾空格。
   - 示例：`SELECT TRIM(' text ');`（返回 `text`）

#### 4、日期和时间函数

处理日期计算、格式化等。

1. NOW()

- 功能：返回当前日期和时间。
   - 示例：`SELECT NOW();`（返回 `2025-07-15 16:40:00`）
2. SYSDATE(): 返回当前日期

示例： select SYSDATE()  ; 返回2025-07-19
3. DATE_ADD(date, INTERVAL expr unit)

- 功能：日期加法（如增加天数）。
   - 示例：`SELECT DATE_ADD('2025-01-01', INTERVAL 1 MONTH);`（返回 `2025-02-01`）
4. DATEDIFF(date1, date2)

- 功能：计算两个日期的天数差。
   - 示例：`SELECT DATEDIFF('2025-12-31', '2025-01-01');`（返回 `364`）
5. YEAR() / MONTH() / DAY()

- 功能：提取日期中的年、月、日。
   - 示例：`SELECT YEAR('2025-07-15');`（返回 `2025`）

#### 5、条件判断函数

实现逻辑分支控制。

1. IF(condition, true_value, false_value)

- 功能：简单条件判断。
   - 示例：`SELECT IF(score > 60, 'Pass', 'Fail') FROM students;`
2. CASE WHEN

- 功能：多条件分支判断。
   - 示例：

```sql
     SELECT 
         CASE 
             WHEN age < 18 THEN 'Minor' 
             WHEN age BETWEEN 18 AND 60 THEN 'Adult' 
             ELSE 'Senior' 
         END AS age_group 
     FROM users;
     ```
3. IFNULL(expr1, expr2)

- 功能：若 `expr1` 为 `NULL`，则返回 `expr2`。
   - 示例：`SELECT IFNULL(commission, 0) FROM sales;`（将 `NULL` 替换为 `0`）

```sql
   select ifnull(comm, 0) from emp;

-- 年薪+comm 不能null
   select sal *12 + ifnull(comm,0) as commsal  from emp;
   ```

#### 6、高级函数

1. GROUP_CONCAT()

- 功能：将多行数据拼接为字符串（需 `GROUP BY`）。
   - 示例：

```sql
     SELECT department_id, GROUP_CONCAT(employee_name) 
     FROM employees 
     GROUP BY department_id;
     ```
2. COALESCE(expr1, expr2, ...)

- 功能：返回第一个非 `NULL` 值。
   - 示例：`SELECT COALESCE(NULL, 'default', 'backup');`（返回 `default`）

## 拓展1：CASE语句与条件

根据工资等级分类：>15000为'高'，8000-15000为'中'，<8000为'低'，显示员工ID和等级。

```sql
SELECT 
    employee_id,
    CASE 
        WHEN salary > 15000 THEN '高'
        WHEN salary BETWEEN 8000 AND 15000 THEN '中'
        ELSE '低'
    END AS '工资等级'
FROM employees;
```

查询学生表的学生年龄，要求输入学生编号，姓名，以及年龄段： 20以上-30之间 弱冠 30-40- 而立  40 -50 不惑

## 拓展2：CONCAT()字符串拼接

查询职位ID(job_id)不是'IT_PROG'的员工姓名和职位。

```sql
SELECT CONCAT(first_name, ' ', last_name) AS name, job_id 
FROM employees 
WHERE job_id <> 'IT_PROG';
```

## 拓展3：多条件聚合

统计工资>10000或提成率>0.2的员工数量。

```sql
SELECT COUNT(*) 
FROM employees 
WHERE salary > 10000 OR commission_pct > 0.2;
```

## 拓展4：union 和 INTERSECT

#### UNION（并集）

示例：此查询返回学生和教师表中的所有唯一 `(id, name)` 组合。

```sql
SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;
```

- 应用场景
  - 合并不同表中的相似数据（如合并多个地区的销售记录）。
  - 去除重复数据后生成唯一列表。

#### INTERSECT（交集）

**示例**：此查询返回既是员工又是经理的 `employee_id`。

```sql
SELECT employee_id FROM employees
INTERSECT
SELECT manager_id FROM managers;
```

#### 核心区别

#### 注意事项

1. **性能**：`INTERSECT` 可能比 `UNION` 更消耗资源，尤其在处理大型数据集时。
2. 替代方案: INTERSECT 可用 INNER JOIN  代替实现类似功能：

```sql
   SELECT DISTINCT a.id FROM table1 a
   INNER JOIN table2 b ON a.id = b.id;
   ```

- `UNION` 可用 `OR` 条件组合查询（但可能影响可读性）。
3. **数据库支持**：部分数据库（如 MySQL 8.0 以下版本）不支持 `INTERSECT`，需通过其他方式模拟。

## 拓展5：ROUND()函数

```sql
-- 统计每个部门的平均工资，显示部门ID和平均工资（保留两位小数）
SELECT department_id, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id;

select round(avg(sal),0) avgsal
from emp
group by deptno;
```

## 拓展6： Mysql中的函数

#### 1、聚合函数

用于对一组值进行计算并返回单一值，常与 `GROUP BY` 结合使用。

#### 2、数学函数

处理数值运算，支持四舍五入、随机数生成等。

#### 3、字符串函数

处理字符串拼接、截取、大小写转换等。

#### 4、日期和时间函数

处理日期计算、格式化等。

1. NOW()

- 功能：返回当前日期和时间。
   - 示例：`SELECT NOW();`（返回 `2025-07-15 16:40:00`）
2. SYSDATE(): 返回当前日期

示例： select SYSDATE()  ; 返回2025-07-19
3. DATE_ADD(date, INTERVAL expr unit)

- 功能：日期加法（如增加天数）。
   - 示例：`SELECT DATE_ADD('2025-01-01', INTERVAL 1 MONTH);`（返回 `2025-02-01`）
4. DATEDIFF(date1, date2)

- 功能：计算两个日期的天数差。
   - 示例：`SELECT DATEDIFF('2025-12-31', '2025-01-01');`（返回 `364`）
5. YEAR() / MONTH() / DAY()

- 功能：提取日期中的年、月、日。
   - 示例：`SELECT YEAR('2025-07-15');`（返回 `2025`）

#### 5、条件判断函数

实现逻辑分支控制。

1. IF(condition, true_value, false_value)

- 功能：简单条件判断。
   - 示例：`SELECT IF(score > 60, 'Pass', 'Fail') FROM students;`
2. CASE WHEN

- 功能：多条件分支判断。
   - 示例：

- 功能：若 `expr1` 为 `NULL`，则返回 `expr2`。
   - 示例：`SELECT IFNULL(commission, 0) FROM sales;`（将 `NULL` 替换为 `0`）

```sql
   select ifnull(comm, 0) from emp;

-- 年薪+comm 不能null
   select sal *12 + ifnull(comm,0) as commsal  from emp;
   ```

#### 6、高级函数

1. GROUP_CONCAT()

- 功能：将多行数据拼接为字符串（需 `GROUP BY`）。
   - 示例：

```sql
     SELECT department_id, GROUP_CONCAT(employee_name) 
     FROM employees 
     GROUP BY department_id;
     ```
2. COALESCE(expr1, expr2, ...)

- 功能：返回第一个非 `NULL` 值。
   - 示例：`SELECT COALESCE(NULL, 'default', 'backup');`（返回 `default`）

最后修改：2025 年 10 月 22 日

👆请简单粗暴地爱我。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Day28 - Redis常见问题

JavaEE基础

HTML标签及属性

Day2 - 分支、选择与while循环详解

DeepSeek R1/V3 使用本地知识库

MySQL知识扩展

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

MySQL知识扩展

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款