MySQL基础02

一. 函数

在 MySQL 中，函数是用于对数据进行特定处理或计算的工具，根据作用范围和返回结果的不同，主要分为单行函数和聚合函数（又称分组函数）。以下是详细介绍：

1.单行函数

单行函数对每一行数据单独处理，每行返回一个结果，常见类型包括字符串函数、数值函数、日期函数、流程控制函数等。

1.） 字符串函数

用于处理字符串类型数据，常见函数如下：

2.） 数值函数

用于数值计算，常见函数如下：

3. ）日期函数

用于处理日期和时间类型数据，常见函数如下：

4. ）流程控制函数

用于实现条件判断逻辑，类似编程语言中的if-else：

2.聚合函数（分组函数）

聚合函数用于对一组数据进行汇总计算，返回单一结果，通常与GROUP BY配合使用（若不分组，则默认对全表数据聚合）。常见聚合函数如下：

注意事项：

1. 聚合函数会自动忽略NULL值（COUNT(*)除外）。

2. 若使用聚合函数时未加GROUP BY，则整个表视为一组。

3. SELECT子句中若有聚合函数，其他非聚合字段需出现在GROUP BY中（避免歧义）。

3.函数使用示例

1. 字符串拼接与日期格式化：

   SELECT 
     CONCAT('姓名：', name) AS 姓名,
     DATE_FORMAT(birthday, '%Y年%m月%d日') AS 生日
   FROM students;

2. 聚合函数统计：

   SELECT 
     COUNT(*) AS 总人数,
     AVG(score) AS 平均分,
     MAX(score) AS 最高分
   FROM students;

3. 条件判断：

   SELECT 
     name AS 姓名,
     score AS 分数,
     CASE 
       WHEN score >= 90 THEN '优秀'
       WHEN score >= 80 THEN '良好'
       ELSE '及格'
     END AS 等级
   FROM students;

二. 约束

在 MySQL 中，约束（Constraints）是用于限制表中数据的规则，确保数据的完整性、一致性和准确性。常见的 MySQL 约束包括以下几种：

主键约束（PRIMARY KEY）

• 唯一标识表中的每条记录

• 不能有重复值，且不能为 NULL

• 一个表只能有一个主键

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

外键约束（FOREIGN KEY）

• 用于关联两个表，确保参照完整性

• 外键列的值必须匹配另一个表的主键列的值或为 NULL

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

唯一约束（UNIQUE）

• 确保列中的所有值都是唯一的

• 允许 NULL 值，但 NULL 只能出现一次

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

非空约束（NOT NULL）

• 确保列不能包含 NULL 值

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT
);

检查约束（CHECK）

• 确保列中的值满足指定的条件

• MySQL 8.0.16 及以上版本支持

CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0)
);

默认约束（DEFAULT）

• 为列指定默认值，如果插入数据时未指定该列的值，则使用默认值

CREATE TABLE customers (
    id INT PRIMARY KEY,
    country VARCHAR(50) DEFAULT 'China'
);

三. 多表查询

多表查询是数据库操作中常用的技术，用于从两个或多个相关联的表中获取数据。在 MySQL 中，实现多表查询主要有以下几种方式：

多表关系

1. 一对一关系（One-to-One）

• 特点：两个表中的记录一一对应，一个表的一条记录只能与另一个表的一条记录关联。

• 适用场景：常用于拆分表结构（如将用户基本信息和详细信息分开存储），或关联具有强归属关系的数据。

• 实现方式：在任意一个表中添加外键，关联另一个表的主键，并设置外键为唯一（UNIQUE）。

-- 示例：用户表（基本信息）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);
​
-- 用户详情表（一对一关联 users 表）
CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL, -- 唯一外键，确保一对一
    real_name VARCHAR(50),
    phone VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2. 一对多关系（One-to-Many）

• 特点：一个表中的一条记录可以对应另一个表中的多条记录，但反向只能对应一条。

• 适用场景：最常见的关系（如 “部门 - 员工”“订单 - 订单项”）。

• 实现方式：在 “多” 的一方添加外键，关联 “一” 的一方的主键。

-- 示例：部门表（一的一方）
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL
);
​
-- 员工表（多的一方，关联部门表）
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    dept_id INT, -- 外键关联部门表
    FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE SET NULL
);

3. 多对多关系（Many-to-Many）

• 特点：两个表中的记录可以互相对应多条记录（如 “学生 - 课程”：一个学生可选多门课，一门课可被多个学生选）。

• 适用场景：需要双向一对多的业务关系。

• 实现方式：通过中间表关联两个表，中间表至少包含两个外键，分别指向两个表的主键。

-- 示例：学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
​
-- 课程表
CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);
​
-- 中间表（关联学生和课程，实现多对多）
CREATE TABLE student_courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    -- 联合唯一约束，避免重复关联
    UNIQUE KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

关系设计的注意事项

1. 外键约束

   强制关联关系，可选参数：

   • ON DELETE CASCADE：主表记录删除时，从表关联记录也删除

   • ON DELETE SET NULL：主表记录删除时，从表外键设为 NULL（需外键允许 NULL）

2. 避免循环依赖：多表关联时避免形成闭环（如 A 依赖 B，B 依赖 C，C 依赖 A），可能导致数据操作异常。

3. 中间表设计：多对多的中间表可额外添加字段（如 “学生选课表” 可加 score 字段记录成绩）。

1. 交叉连接（CROSS JOIN）

返回两个表的笛卡尔积（所有可能的组合），通常需要配合 WHERE 条件过滤无意义的记录。

-- 语法
SELECT 列名 FROM 表1 CROSS JOIN 表2 [WHERE 条件];
​
-- 示例：查询学生和他们的课程（未过滤前是所有学生与所有课程的组合）
SELECT students.name, courses.course_name
FROM students CROSS JOIN courses
WHERE students.id = courses.student_id; -- 过滤出有效的关联记录

2. 内连接（INNER JOIN）

只返回两个表中满足连接条件的记录（即匹配的记录），是最常用的连接方式。

-- 语法
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 连接条件;

-- 示例：查询学生及其选修的课程（只显示有选课记录的学生）
SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

3. 外连接（OUTER JOIN）

返回一个表的所有记录，以及另一个表中满足条件的匹配记录。外连接分为：

• 左外连接（LEFT JOIN / LEFT OUTER JOIN）：返回左表所有记录 + 右表匹配记录

• 右外连接（RIGHT JOIN / RIGHT OUTER JOIN）：返回右表所有记录 + 左表匹配记录

• 全外连接（FULL JOIN）：返回两个表的所有记录（MySQL 不直接支持，需用 UNION 模拟）

-- 左外连接示例：查询所有学生及其选修的课程（包括未选课的学生）
SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;
​
-- 右外连接示例：查询所有课程及选修该课程的学生（包括无学生选修的课程）
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

4. 自连接（SELF JOIN）

将表与自身进行连接，通常用于查询表中具有层级或关联关系的数据（如员工与上司）。

-- 示例：查询员工及其直属上司的姓名（假设 employees 表中有 manager_id 关联上司ID）
SELECT e.name AS 员工, m.name AS 上司
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

5. 联合查询（UNION / UNION ALL）

用于合并多个 SELECT 语句的结果集，要求列数和数据类型一致。

• UNION：去除重复记录

• UNION ALL：保留所有记录（包括重复）

-- 示例：合并两个查询结果（查询10岁以下和20岁以上的学生）
SELECT name, age FROM students WHERE age < 10
UNION
SELECT name, age FROM students WHERE age > 20;

子查询（Subquery）是嵌套在另一个 SQL 语句中的查询，也称为内部查询，而包含子查询的语句称为外部查询。子查询通常用于为主查询提供数据或条件，可使复杂查询逻辑更清晰。

子查询的基本特点

• 子查询必须用括号 () 包裹

• 子查询通常放在 WHERE、FROM 或 SELECT 子句中

• 子查询的结果可以是单个值、一行、一列或多行

常见子查询类型及示例

1. 返回单个值的子查询（标量子查询）

常用于 WHERE 子句中，配合比较运算符（=, >, <, >=, <= 等）使用。

-- 示例：查询与"张三"同龄的学生
SELECT name, age 
FROM students 
WHERE age = (SELECT age FROM students WHERE name = '张三');

2. 返回一列值的子查询（列子查询）

通常与 IN、NOT IN、ANY、ALL 等关键字配合使用

-- 示例1：查询选修了"数学"课程的学生（使用IN）
SELECT name 
FROM students 
WHERE id IN (
    SELECT student_id 
    FROM courses 
    WHERE course_name = '数学'
);
​
-- 示例2：查询年龄大于所有女生的男生（使用ALL）
SELECT name, age 
FROM students 
WHERE gender = '男' 
  AND age > ALL (SELECT age FROM students WHERE gender = '女');

3. 返回多行多列的子查询（表子查询）

可作为一个临时表，用于 FROM 子句中（通常需要起别名）。

-- 示例：查询每个班级的平均年龄及班级信息
SELECT classes.class_name, avg_ages.avg_age
FROM classes
JOIN (
    SELECT class_id, AVG(age) AS avg_age 
    FROM students 
    GROUP BY class_id
) AS avg_ages ON classes.id = avg_ages.class_id;

4. EXISTS 子查询

用于判断子查询是否返回结果，返回 TRUE 或 FALSE，不关心具体数据。

-- 示例：查询有选修课程的学生（存在选课记录）
SELECT name 
FROM students s 
WHERE EXISTS (
    SELECT 1 
    FROM courses c 
    WHERE c.student_id = s.id
);

5. 嵌套子查询

子查询中可以再包含子查询，形成多层嵌套（但建议层数不宜过多，以免影响性能）。

-- 示例：查询选修了"张三"所选全部课程的学生
SELECT DISTINCT s.name
FROM students s
JOIN courses c ON s.id = c.student_id
WHERE c.course_name IN (
    SELECT course_name 
    FROM courses 
    WHERE student_id = (SELECT id FROM students WHERE name = '张三')
)
GROUP BY s.name
HAVING COUNT(c.course_name) = (
    SELECT COUNT(*) 
    FROM courses 
    WHERE student_id = (SELECT id FROM students WHERE name = '张三')
);

子查询的注意事项

1. 性能问题：复杂子查询可能影响效率，可尝试用 JOIN 改写（部分场景下 JOIN 性能更优）。

2. 关联子查询与非关联子查询

   ：

   • 非关联子查询：独立于外部查询，只执行一次

   • 关联子查询：依赖外部查询的字段，可能执行多次（每行一次）

3. SELECT 子句中的子查询：只能返回单个值，用于为结果集增加计算列。

多表查询的注意事项

1. 表别名：使用 AS 为表起别名（可省略 AS），简化 SQL 语句。

2. 列名冲突：当多表中有同名列时，需用 表名.列名 或 别名.列名 区分（如 students.id）。

3. 性能优化：对连接条件中的列建立索引，避免不必要的 SELECT *，减少数据扫描量。

四. 事务

在 MySQL 中，事务（Transaction）是一组数据库操作的集合，这些操作要么全部成功执行，要么全部失败回滚，以此保证数据的一致性和完整性。事务是数据库管理系统（DBMS）处理并发操作和故障恢复的核心机制。

MySQL 事务的 ACID 特性

事务必须满足以下四个基本特性，即 ACID 特性：

1. 原子性（Atomicity） 事务中的所有操作要么全部执行成功，要么全部失败回滚（Rollback），不会出现部分执行的情况。 例如：转账操作中，“A 账户扣款” 和 “B 账户到账” 必须同时成功或同时失败。

2. 一致性（Consistency） 事务执行前后，数据库从一个一致的状态转变为另一个一致的状态，数据规则（如约束、逻辑关系）不会被破坏。 例如：转账前后，A 和 B 的账户总金额保持不变。

3. 隔离性（Isolation） 多个事务并发执行时，一个事务的操作不会被其他事务干扰，各事务之间相互隔离。 MySQL 通过隔离级别控制并发事务的交互程度（见下文）。

4. 持久性（Durability） 事务一旦提交（Commit），其对数据的修改会永久保存到数据库中，即使系统崩溃也不会丢失。

MySQL 事务的操作语法

MySQL 中，事务的基本操作通过以下 SQL 命令实现：

1. 开启事务 关闭自动提交模式，后续操作将纳入事务管理：

   START TRANSACTION;  -- 或 BEGIN;

2. 执行操作 执行一系列 SQL 语句（如 INSERT、UPDATE、DELETE 等）：

   UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- A账户扣款
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- B账户到账

3. 提交事务 确认所有操作成功，将修改永久保存到数据库：

   COMMIT;

4. 回滚事务 若操作失败，撤销所有已执行的修改，恢复到事务开始前的状态：

   ROLLBACK;

5. 设置保存点（可选） 在事务中设置中间点，可回滚到指定保存点（而非整个事务）：

   SAVEPOINT sp1;  -- 创建保存点 sp1
   ROLLBACK TO sp1;  -- 回滚到 sp1

MySQL 事务的隔离级别

多个事务并发执行时，可能出现 脏读、不可重复读、幻读 等问题。MySQL 定义了四种隔离级别（从低到高），用于平衡隔离性和并发性能：

1. 读未提交（READ UNCOMMITTED） 最低隔离级别，一个事务可读取另一个未提交的修改。可能导致 脏读（读取到未提交的无效数据）。

2. 读已提交（READ COMMITTED） 一个事务只能读取另一个已提交的修改，避免 脏读，但可能出现 不可重复读（同一事务中多次读取同一数据，结果不一致）。 这是大多数数据库的默认级别（如 Oracle）。

3. 可重复读（REPEATABLE READ） MySQL 的默认隔离级别。保证同一事务中多次读取同一数据的结果一致，避免 脏读 和 不可重复读，但可能出现 幻读（事务执行中，其他事务新增的数据被读取到）。

4. 串行化（SERIALIZABLE） 最高隔离级别，强制事务串行执行（类似单线程），避免所有并发问题，但性能最差。

查看和设置隔离级别

• 查看当前隔离级别：

  -- 查看全局隔离级别
  SELECT @@GLOBAL.transaction_isolation;
  -- 查看当前会话隔离级别
  SELECT @@SESSION.transaction_isolation;

• 设置隔离级别（需有足够权限）：

  -- 设置全局隔离级别（对新会话生效）
  SET GLOBAL transaction_isolation = 'READ COMMITTED';
  -- 设置当前会话隔离级别
  SET SESSION transaction_isolation = 'REPEATABLE READ';

注意事项

1. 存储引擎支持：MySQL 中，只有 InnoDB 存储引擎支持事务，MyISAM 等引擎不支持。

2. 自动提交：MySQL 默认开启 AUTOCOMMIT=1（每条 SQL 自动作为一个事务提交），开启事务后需手动提交或回滚。

3. 锁机制：事务的隔离性通过 InnoDB 的锁机制（如行锁、表锁）和 MVCC（多版本并发控制）实现。