网站导航
> 技术文档 > MySQL基础02

MySQL基础02

网友: 技术文档 2025-07-26 20:35:10

一. 函数

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

1.单行函数

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

1.） 字符串函数

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

函数 功能描述 示例 结果 CONCAT(str1, str2,...) 拼接多个字符串（若有一个参数为NULL，结果为NULL） CONCAT(\'MySQL\', \' \', \'函数\') MySQL 函数 CONCAT_WS(sep, str1, str2,...) 用分隔符sep拼接字符串（忽略NULL） CONCAT_WS(\'-\', \'2023\', \'10\', \'05\') 2023-10-05 LENGTH(str) 返回字符串的字节数（受字符集影响，UTF-8 中汉字占 3 字节） LENGTH(\'中国\') 6 CHAR_LENGTH(str) 返回字符串的字符数（与字符集无关） CHAR_LENGTH(\'中国\') 2 UPPER(str) 将字符串转为大写 UPPER(\'mysql\') MYSQL LOWER(str) 将字符串转为小写 LOWER(\'MYSQL\') mysql SUBSTRING(str, pos, len) 从pos位置（起始为 1）截取长度为len的子串（省略len则取到末尾） SUBSTRING(\'HelloWorld\', 7, 3) orl TRIM(str) 去除字符串两端的空格（TRIM(指定字符 FROM str)可去除指定字符） TRIM(\' MySQL \') MySQL REPLACE(str, old, new) 将str中所有old子串替换为new REPLACE(\'abc123abc\', \'abc\', \'x\') x123x INSTR(str, substr) 返回substr在str中首次出现的位置（无则返回 0） INSTR(\'abcde\', \'cd\') 3
2.） 数值函数

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

函数 功能描述 示例 结果 ABS(x) 返回x的绝对值 ABS(-10) 10 CEIL(x) 向上取整（返回大于等于x的最小整数） CEIL(3.2) 4 FLOOR(x) 向下取整（返回小于等于x的最大整数） FLOOR(3.8) 3 ROUND(x, d) 四舍五入保留d位小数（d默认 0） ROUND(3.1415, 2) 3.14 MOD(x, y) 返回x除以y的余数（同x % y） MOD(10, 3) 1 POWER(x, y) 返回x的y次方 POWER(2, 3) 8 SQRT(x) 返回x的平方根 SQRT(16) 4
3. ）日期函数

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

函数 功能描述 示例 结果（假设当前时间为 2023-10-05 15:30:00） NOW() 返回当前日期和时间（YYYY-MM-DD HH:MM:SS） NOW() 2023-10-05 15:30:00 CURDATE() 返回当前日期（YYYY-MM-DD） CURDATE() 2023-10-05 CURTIME() 返回当前时间（HH:MM:SS） CURTIME() 15:30:00 YEAR(date) 提取日期中的年份 YEAR(\'2023-10-05\') 2023 MONTH(date) 提取日期中的月份 MONTH(\'2023-10-05\') 10 DAY(date) 提取日期中的日 DAY(\'2023-10-05\') 5 HOUR(time) 提取时间中的小时 HOUR(\'15:30:00\') 15 DATEDIFF(date1, date2) 返回date1 - date2的天数差 DATEDIFF(\'2023-10-05\', \'2023-10-01\') 4 DATE_ADD(date, INTERVAL expr unit) 给日期添加指定时间间隔（unit：YEAR、MONTH、DAY 等） DATE_ADD(\'2023-10-05\', INTERVAL 3 DAY) 2023-10-08 DATE_FORMAT(date, format) 将日期按format格式转换为字符串（%Y年、%m月、%d日等） DATE_FORMAT(\'2023-10-05\', \'%Y年%m月%d日\') 2023-10-05
4. ）流程控制函数

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

函数 功能描述 示例 结果 IF(expr, v1, v2) 若expr为真（非 0/NULL），返回v1，否则返回v2 IF(10 > 5, \'是\', \'否\') 是 IFNULL(v1, v2) 若v1不为NULL，返回v1，否则返回v2（处理 NULL 值） IFNULL(NULL, \'空值\') 空值 CASE 多条件判断（类似switch-case） CASE 分数
WHEN 90 THEN \'优秀\'
WHEN 80 THEN \'良好\'
ELSE \'及格\'
END （根据分数返回对应等级）

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

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

函数 功能描述 示例（表students含字段score） 结果 COUNT(expr) 统计非NULL值的数量（COUNT(*)统计所有行，包括NULL） COUNT(score) 分数非空的行数 SUM(expr) 计算数值型字段的总和 SUM(score) 所有分数的总和 AVG(expr) 计算数值型字段的平均值 AVG(score) 分数的平均值 MAX(expr) 求字段的最大值 MAX(score) 最高分数 MIN(expr) 求字段的最小值 MIN(score) 最低分数
注意事项：

  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_nameFROM students CROSS JOIN coursesWHERE students.id = courses.student_id; -- 过滤出有效的关联记录

2. 内连接（INNER JOIN）

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

-- 语法SELECT 列名 FROM 表1 INNER JOIN 表2 ON 连接条件;-- 示例：查询学生及其选修的课程（只显示有选课记录的学生）SELECT students.name, courses.course_nameFROM studentsINNER 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_nameFROM studentsLEFT JOIN courses ON students.id = courses.student_id;​-- 右外连接示例：查询所有课程及选修该课程的学生（包括无学生选修的课程）SELECT students.name, courses.course_nameFROM studentsRIGHT JOIN courses ON students.id = courses.student_id;

4. 自连接（SELF JOIN）

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

-- 示例：查询员工及其直属上司的姓名（假设 employees 表中有 manager_id 关联上司ID）SELECT e.name AS 员工, m.name AS 上司FROM employees eLEFT 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  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_ageFROM classesJOIN (    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.nameFROM students sJOIN courses c ON s.id = c.student_idWHERE c.course_name IN (    SELECT course_name    FROM courses    WHERE student_id = (SELECT id FROM students WHERE name = \'张三\'))GROUP BY s.nameHAVING 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; -- 创建保存点 sp1ROLLBACK 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（多版本并发控制）实现。

网络标签：

相关问题