MySQL

  1. SQL语言四大类别：
    • DDL：用于定义数据库结构，如创建、修改、删除表。
    • DML：用于操作数据，如插入、更新、删除记录。
    • DQL：用于查询数据，如SELECT。
    • DCL：用于控制访问权限，如授予、撤销权限。
  2. 字符集选择、数据类型转换、字段操作：
    • 字符集选择影响数据存储与显示，常用的有UTF-8。
    • 数据类型转换用于在不同类型之间转换。
    • 字段操作包括修改字段类型、默认值和约束等。
  3. 数据库函数：
    • 字符串函数：处理字符串，如CONCAT、SUBSTRING。
    • 数值函数：处理数值，如ROUND、ABS。
    • 日期函数：处理日期时间，如NOW、DATE_ADD。
    • 流程函数：控制流程，如IF、CASE。
  4. 约束：
    • 非空约束：保证字段不为空。
    • 唯一约束：保证字段值唯一。
    • 主键约束：确保唯一标识每条记录。
  5. 多表查询：
    • 一对多、多对多、一对一：表之间的常见关系。
    • 连接查询：内连接、外连接、自连接。
    • 联合查询：合并多个查询结果。
    • 子查询：在查询中嵌套另一个查询。
  6. 事务：
    • 基本操作：包括START TRANSACTION、COMMIT、ROLLBACK。
    • ACID特性：保证事务的原子性、一致性、隔离性和持久性。
    • 并发事务问题和隔离级别：解决脏读、不可重复读、幻读问题，常见隔离级别有READ COMMITTED、REPEATABLE READ、SERIALIZABLE。

建议阅读

MySQL数据库的SQL语言四大类别

在MySQL数据库管理中，SQL（Structured Query Language）被划分为四大类别，每个类别包含不同的功能，旨在管理和操作数据库中的数据和结构。

1. 数据定义语言（DDL）

DDL用于定义和管理数据库对象的结构，包括表、视图、索引等。主要命令包括：

示例：

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

2. 数据操作语言（DML）

DML用于操作数据库中的数据。主要命令包括：

示例：

INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'HR');

3. 数据查询语言（DQL）

DQL主要用于查询数据，核心命令是SELECT，用于从数据库中检索数据。通过结合WHERE、GROUP BY、HAVING等子句，可以构建复杂的查询。

示例：

SELECT name, department FROM employees WHERE department = 'HR';

4. 数据控制语言（DCL）

DCL用于定义数据库的权限和安全设置，确保数据访问和使用的安全性。主要命令包括：

示例：

GRANT SELECT, INSERT ON employees TO 'user1'@'localhost';

MySQL数据库中的字符集选择、数据类型转换与字段操作

1. 字符集选择

字符集决定了数据库中存储的字符的编码方式，不同的字符集支持不同的语言和符号。常用的字符集包括utf8mb4（支持多种语言和符号）和latin1（支持西欧语言）。

示例：

CREATE DATABASE mydb CHARACTER SET utf8mb4;
ALTER TABLE employees CONVERT TO CHARACTER SET utf8mb4;

2. 数据类型转换

数据类型转换是指将一种数据类型转换为另一种数据类型。MySQL支持隐式和显式的数据类型转换。

示例：

SELECT CAST('123' AS UNSIGNED); -- 将字符串转换为无符号整数
SELECT CONVERT('2025-01-09', DATE); -- 将字符串转换为日期

3. 字段操作

字段操作涉及表中的列（字段）的增删改等操作，确保表结构的灵活性和扩展性。

示例：

ALTER TABLE employees ADD COLUMN email VARCHAR(100); -- 添加新字段
ALTER TABLE employees MODIFY COLUMN name VARCHAR(100); -- 修改字段类型
ALTER TABLE employees DROP COLUMN department; -- 删除字段

函数

MySQL提供了丰富的内置函数，帮助用户在数据库操作中进行字符串处理、数值运算、日期计算以及流程控制等多种任务。根据功能的不同，这些函数可分为四大类：字符串函数、数值函数、日期函数和流程函数。

1. 字符串函数

字符串函数用于对字符串进行处理和操作，例如提取子字符串、查找字符串长度、转换大小写等。

常用的字符串函数包括：

示例：

SELECT CONCAT('Hello', ' ', 'World');  -- 输出：Hello World
SELECT LENGTH('MySQL');  -- 输出：5
SELECT SUBSTRING('MySQL', 2, 3);  -- 输出：ySQL
SELECT UPPER('mysql');  -- 输出：MYSQL

2. 数值函数

数值函数用于执行数值计算，如四则运算、取整、随机数生成等。

常用的数值函数包括：

示例：

SELECT ROUND(123.456, 2);  -- 输出：123.46
SELECT FLOOR(123.456);  -- 输出：123
SELECT CEIL(123.456);  -- 输出：124
SELECT RAND();  -- 输出：随机值，例如：0.487156
SELECT ABS(-100);  -- 输出：100

3. 日期函数

日期函数用于处理和操作日期和时间数据，常见的功能包括获取当前时间、日期加减、格式化日期等。

常用的日期函数包括：

示例：

SELECT NOW();  -- 返回当前日期和时间，例如：2025-01-09 12:34:56
SELECT CURDATE();  -- 返回当前日期，例如：2025-01-09
SELECT DATE_ADD('2025-01-09', INTERVAL 1 DAY);  -- 输出：2025-01-10
SELECT DATE_SUB('2025-01-09', INTERVAL 2 MONTH);  -- 输出：2024-11-09
SELECT YEAR('2025-01-09');  -- 输出：2025
SELECT DATE_FORMAT('2025-01-09', '%Y-%m-%d');  -- 输出：2025-01-09

4. 流程函数

流程控制函数用于在SQL查询中实现条件判断和流程控制，如判断语句、条件表达式等。

常用的流程函数包括：

示例：

SELECT IF(1 > 0, 'True', 'False');  -- 输出：True
SELECT CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users;  -- 根据age字段判断
SELECT COALESCE(NULL, 'Default', 'Value');  -- 输出：Default
SELECT NULLIF(10, 10);  -- 输出：NULL

MySQL数据库中的约束

在MySQL中，约束（Constraints）是用于限定表中数据的规则，确保数据的完整性和一致性。约束可以在创建表时定义，也可以在表创建后使用ALTER TABLE命令添加或修改。常见的约束有非空约束（NOT NULL）、唯一约束（UNIQUE）和主键约束（PRIMARY KEY）。

1. 非空约束（NOT NULL）

非空约束确保表中的某个字段在插入数据时不能为NULL。这意味着该字段必须包含有效的数据，不能为空。

示例：

CREATE TABLE employees (
    id INT NOT NULL,  -- 非空约束，id不能为空
    name VARCHAR(50) NOT NULL,  -- 非空约束，name不能为空
    department VARCHAR(50)
);

如果尝试插入NULL值，系统会报错：

INSERT INTO employees (id, name) VALUES (1, NULL);  -- 报错，name不能为空

2. 唯一约束（UNIQUE）

唯一约束确保字段中的所有值都是唯一的，即该字段中的每个数据值不能重复。唯一约束允许字段中的值为NULL，但如果字段不为空，则每个值都必须是唯一的。

示例：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE  -- 唯一约束，email不能重复
);

在插入数据时，如果试图插入重复的email值，系统会报错：

INSERT INTO employees (id, email) VALUES (1, '[email protected]');
INSERT INTO employees (id, email) VALUES (2, '[email protected]');  -- 报错，email值重复

3. 主键约束（PRIMARY KEY）

主键约束是唯一约束的特殊形式。主键是表中用来唯一标识每一条记录的字段或字段组合。主键约束要求字段值必须唯一，并且不能为空。每个表只能有一个主键。

示例：

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(50),
    department VARCHAR(50),
    PRIMARY KEY (id)  -- id列是主键，不能重复并且不能为NULL
);

在插入数据时，如果试图插入重复的主键值或NULL值，系统会报错：

INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'HR');
INSERT INTO employees (id, name, department) VALUES (1, 'Jane Doe', 'Finance');  -- 报错，主键冲突

总结

MySQL数据库中的多表查询

在实际应用中，数据库表之间的关系非常常见，MySQL提供了丰富的多表查询功能，用于处理表与表之间的各种关系。常见的多表查询类型包括一对多关系、多对多关系、一对一关系，以及各种连接查询（内连接、外连接、自连接）和联合查询、子查询等。以下是这些查询的详细介绍：

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

一对多关系指的是一个表中的一条记录可以与另一个表中的多条记录相关联。通常在一对多关系中，"一"的一方是主表，"多"的一方是从表。

例如，假设有两个表：departments（部门表）和employees（员工表）。每个部门有多个员工，但每个员工只属于一个部门。

表结构：

查询示例：

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

这个查询返回所有员工及其所属部门的信息，其中每个部门可以有多个员工。

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

多对多关系指的是表与表之间的关系非常复杂，一条记录可以与另一表中的多条记录关联，反之亦然。通常，需要通过一个中间表来表示这种关系。

例如，假设有三个表：students（学生表）、courses（课程表）和student_courses（学生选课表）。一个学生可以选修多门课程，而每门课程可以被多个学生选修。

表结构：

查询示例：

SELECT s.student_name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

该查询返回每个学生选修的课程信息，其中每个学生可以选修多门课程，且每门课程也可以有多个学生。

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

一对一关系指的是两个表中的一条记录只能与另一个表中的一条记录关联。例如，假设有employees（员工表）和employee_details（员工详细信息表），每个员工在employee_details表中只有一条记录，反之亦然。

表结构：

查询示例：

SELECT e.employee_name, ed.address, ed.phone_number
FROM employees e
JOIN employee_details ed ON e.employee_id = ed.employee_id;

该查询返回每个员工及其详细信息，其中每个员工只有一个对应的详细信息记录。

4. 内连接查询（INNER JOIN）

内连接查询用于返回两个表中符合连接条件的记录。如果两个表中的记录没有匹配项，则该记录不会出现在查询结果中。

查询示例：

SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

这个查询只会返回那些员工与部门匹配的记录（即employee_id与department_id匹配的记录）。

5. 外连接查询（OUTER JOIN）

外连接查询返回两个表中的所有记录，即使某些记录在一个表中没有匹配项。外连接分为左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和完全外连接（FULL OUTER JOIN，在MySQL中需要用UNION模拟）。

查询示例：

-- 左外连接
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 右外连接
SELECT e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

6. 自连接查询（Self Join）

自连接查询是指同一表与其自身进行连接。这种查询通常用于查找表中记录之间的关系，比如员工表中的经理与员工之间的关系。

查询示例：

SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

这个查询返回所有员工及其经理的名称，其中e1是员工表的别名，e2是经理表的别名，manager_id表示经理的employee_id。

7. 联合查询（UNION）

联合查询用于将多个SELECT查询的结果合并为一个结果集。使用UNION时，所有的查询列数和列类型必须一致。UNION会去除重复的记录，若需要包括重复记录，可以使用UNION ALL。

查询示例：

SELECT employee_name FROM employees
UNION
SELECT department_name FROM departments;

该查询将返回员工名称和部门名称的并集。

8. 子查询（Subquery）

子查询是在一个查询中嵌套另一个查询，子查询可以出现在SELECT、FROM、WHERE等子句中。子查询可以是单行子查询、多行子查询或相关子查询。

查询示例：

-- 单行子查询
SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'HR');

-- 多行子查询
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'HR');

上面的查询会返回所有在HR部门的员工。

总结

MySQL数据库中的事务

事务（Transaction）是一个逻辑上的工作单位，由一系列的数据库操作组成，这些操作要么全部成功执行，要么全部回滚。事务的主要目的是确保数据的一致性、完整性和可靠性。MySQL支持事务处理，它可以让用户在多个操作之间实现原子性、持久性等特性，从而保证数据的准确性和一致性。

1. 基本操作

MySQL的事务操作包括START TRANSACTION、COMMIT、ROLLBACK等。通过这些操作，用户可以启动一个事务、提交事务或回滚事务。

示例：

START TRANSACTION;  -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  -- 从账户1扣款
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  -- 向账户2存款

COMMIT;  -- 提交事务

如果在执行过程中出现错误，需要回滚事务：

START TRANSACTION;  -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  -- 从账户1扣款
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  -- 向账户2存款

ROLLBACK;  -- 回滚事务，撤销所有更改

2. ACID特性

事务的四个基本特性被称为ACID特性，它们是保证事务可靠性的核心标准。ACID是以下四个英文单词的缩写：

ACID特性示例：

3. 并发事务问题和隔离级别

在数据库中，当多个事务并发执行时，可能会出现并发事务问题，例如脏读、不可重复读和幻读。为了避免这些问题，数据库提供了隔离级别（Isolation Levels）来控制并发事务的行为。

并发事务问题：

事务隔离级别： MySQL支持四种事务隔离级别，每种隔离级别提供不同程度的并发控制，从而影响事务的执行效率和数据一致性。

  1. READ UNCOMMITTED（读未提交）：
    • 在这个隔离级别，事务可以读取其他事务未提交的数据（脏读）。这是最不严格的隔离级别。
    • 可能出现脏读、不可重复读和幻读问题。

示例：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  1. READ COMMITTED（读已提交）：
    • 在这个隔离级别，事务只能读取已提交的数据（防止脏读）。但是，仍然可能出现不可重复读。
    • 这种隔离级别避免了脏读，但无法避免不可重复读和幻读问题。

示例：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. REPEATABLE READ（可重复读）：
    • 在这个隔离级别，事务中读取的数据在整个事务期间都是一致的（防止不可重复读）。但是，可能会出现幻读问题。
    • 这是MySQL的默认隔离级别，能够防止脏读和不可重复读，但幻读问题仍然存在。

示例：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  1. SERIALIZABLE（可串行化）：
    • 在这个隔离级别，事务强制按照顺序执行，完全避免了脏读、不可重复读和幻读问题。每个事务的执行会被其他事务完全隔离。
    • 这是最严格的隔离级别，能够提供完全的数据一致性，但性能较低。

示例：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

隔离级别的选择：

总结

MySQL中SQL语句的书写顺序和执行顺序：
书写顺序
SELECT：选择要返回的列。
FROM：指定查询的表。
JOIN：表连接操作。
WHERE：过滤条件。
GROUP BY：分组条件。
HAVING：分组后的过滤条件。
ORDER BY：排序条件。
LIMIT：限制返回的行数。
执行顺序
FROM：确定查询的表。
JOIN：表连接操作。
WHERE：过滤条件。
GROUP BY：分组条件。
HAVING：分组后的过滤条件。
SELECT：选择要返回的列。
ORDER BY：排序条件。
LIMIT：限制返回的行数。
为什么不同
性能优化：先处理FROM、JOIN和WHERE可以减少需要处理的数据量，提高查询效率。
逻辑依赖：GROUP BY和HAVING需要在数据已经过滤和连接之后才能生效。