MySQL学习

MySQL

目录

1.MySQL安装配置

见https://www.bilibili.com/read/cv35748119/

1.1.InnoDB

InnoDB是MySQL数据库中的一种存储引擎，自MySQL 5.5版本起成为MySQL的默认存储引擎。它提供了许多高级数据库功能和特性，使其在处理高并发、复杂查询和事务支持方面表现出色。以下是对InnoDB的详细解析：

1.1.1.InnoDB的主要特性

  1. 事务支持：
    • InnoDB支持ACID（原子性、一致性、隔离性、持久性）事务特性，确保了数据的一致性和完整性。
    • 提供了回滚（rollback）和崩溃恢复能力（crash recovery capabilities），即使在系统崩溃后也能保证数据的完整性。
  2. 行级锁定：
    • InnoDB使用行级锁定，而非表级锁定，这显著减少了锁的争用，提高了并发性能。
    • 支持Oracle风格的不加锁读取（non-locking read in SELECTs），在SELECT语句中提供了一致的非锁定读。
  3. 外键约束：
    • InnoDB支持外键约束，有助于维护数据的完整性和表之间的引用关系。
  4. 崩溃恢复：
    • InnoDB通过重做日志（redo log）和撤销日志（undo log）确保事务的持久性和一致性，在系统崩溃后能够自动恢复数据。
  5. 性能优化：
    • InnoDB使用缓冲池（buffer pool）来缓存数据和索引，减少磁盘I/O操作，提高查询性能。
    • 支持聚簇索引，数据和索引文件存储在一起，优化了数据的存储和访问。

1.1.2.InnoDB的存储结构

  1. 表空间（Tablespace）：
    • InnoDB使用表空间来组织数据和索引，表空间分为系统表空间和独立表空间。
    • 系统表空间用于存放InnoDB的系统数据和临时表数据。
    • 独立表空间则允许每个表的数据和索引存储在一个独立的.ibd文件中。
  2. 页（Page）：
    • InnoDB的最小物理存储分配单位是页，页的默认大小是16KB。
  3. 段（Segment）和区（Extent）：
    • 表空间由段组成，段由区组成，区则是由连续的页组成。

1.1.3.InnoDB的配置优化

  1. 调整缓冲池大小：
    • innodb_buffer_pool_size 是InnoDB最重要的配置参数之一，建议设置为服务器总内存的70%-80%。
  2. 调整日志文件大小：
    • innodb_log_file_size 用于设置重做日志文件的大小，应根据数据库的写入负载和恢复时间要求进行调整。
  3. 调整锁等待超时时间：
    • innodb_lock_wait_timeout 定义了事务等待获取锁的最长时间，可根据应用需求进行调整以避免死锁。

1.1.4.InnoDB的应用场景

由于InnoDB支持事务、行级锁定和外键约束等高级特性，它特别适用于需要高并发、复杂查询和事务支持的场景，如：

  • 电子商务：处理在线交易和支付。
  • 在线游戏：存储玩家数据和游戏状态。
  • 金融系统：处理交易记录和账户信息。
  • 内容管理系统：存储文章、评论等数据。

1.2.MyISAM

MyISAM是MySQL数据库的一种存储引擎，尤其在MySQL 5.5版本之前，它是默认的存储引擎。以下是关于MyISAM的详细解析：

1.2.1.MyISAM的特点

  1. 不支持事务处理：MyISAM不支持事务（Transaction）处理，即不具备ACID（原子性、一致性、隔离性、持久性）特性。这意味着，如果在执行某个操作时发生错误，MyISAM不会自动回滚之前的操作，这可能导致数据不一致。
  2. 表级锁定：MyISAM使用表级锁定（Table-Level Locking），而不是行级锁定（Row-Level Locking）。这意味着在执行写操作时，整个表将被锁定，其他查询或写操作必须等待。这虽然简化了锁的实现，但降低了并发性能。
  3. 读写相互阻塞：MyISAM的读写操作会相互阻塞，即写入操作会阻塞读取操作，读取操作也会阻塞写入操作。但读取操作之间不会相互阻塞。
  4. 缓存索引：MyISAM通过key_buffer_size来设置缓存索引，以提高访问性能并减少磁盘I/O的压力。但MyISAM只缓存索引，不缓存数据。
  5. 全文索引支持：MyISAM支持全文索引（Full-Text Indexing），这在处理文本搜索时非常有用。
  6. 数据和索引分开存储：MyISAM将表的数据和索引分别存储在.MYD（数据文件）和.MYI（索引文件）文件中，这有助于优化性能，但也可能导致磁盘碎片问题。
  7. 存储空间较小：与InnoDB相比，MyISAM在存储空间上通常较小。

1.2.2.MyISAM的应用场景

  1. 读密集型应用：由于MyISAM的表级锁定和不支持事务，它更适合读密集型的应用，如博客、图片信息数据库、用户数据库等。
  2. 不需要事务支持的场景：对于不需要事务支持的应用，MyISAM是一个很好的选择。
  3. 数据修改相对较少的业务：MyISAM适用于数据修改相对较少的业务场景，因为频繁的写入操作会导致表锁定，影响性能。
  4. 全文搜索应用：由于MyISAM支持全文索引，因此适合用于需要进行全文搜索的应用，如博客系统、新闻网站等。

1.2.3.MyISAM的存储结构

每个MyISAM数据表都由三个文件组成，每个文件都以数据表名称为文件主名，并搭配不同扩展名区分文件类型：

  1. .frm：存储数据表定义，此文件并非MyISAM引擎的一部分，而是数据库服务器的一部分。
  2. .MYD：存放真正的数据。
  3. .MYI：存储索引信息。

1.2.4.MyISAM的替代与选择

随着MySQL的发展，InnoDB逐渐成为更受欢迎的存储引擎，因为它支持事务、行级锁定等更强大的特性。在选择存储引擎时，需要根据具体的应用场景和需求来做出合适的选择。如果应用需要事务支持、高并发性能或外键约束等特性，那么InnoDB可能是更好的选择。然而，在一些特定的应用场景下，如读密集型应用或不需要事务支持的应用中，MyISAM仍然可以发挥其优势。

2.MySQL操作

2.1.数据类型

2.1.1. 数值类型

  • 整数类型：

    • TINYINT：非常小的整数。
    • SMALLINT：较小的整数。
    • MEDIUMINT：中等大小的整数。
    • INT 或 INTEGER：标准的整数。
    • BIGINT：大整数。

    每种整数类型都可以是有符号的（默认）或无符号的，无符号整数表示正值范围更大。

  • 浮点数和定点数：

    • FLOAT：单精度浮点数。
    • DOUBLE：双精度浮点数。
    • DECIMAL(M,D) 或 NUMERIC(M,D)：定点数，精确的小数。其中M是数字的最大位数（精度），D是小数点后的位数（标度）。

2.1.2. 日期和时间类型

  • DATE：仅日期值，格式为YYYY-MM-DD。
  • TIME：时间值或持续时间，格式为HH:MM:SS。
  • DATETIME：日期和时间值，格式为YYYY-MM-DD HH:MM:SS。
  • TIMESTAMP：时间戳值，自动初始化为当前的日期和时间（或者设置为特定的日期和时间）。
  • YEAR：年份值，格式为YYYY。

2.1.3. 字符串类型

  • CHAR(M)：定长字符串，最多可存储M个字符。
  • VARCHAR(M)：变长字符串，最多可存储M个字符。M的最大值是65535，但受字符集和最大行大小限制。
  • TINYTEXT：非常小的非二进制字符串。
  • TEXT：非二进制字符串。
  • MEDIUMTEXT：中等大小的非二进制字符串。
  • LONGTEXT：大型非二进制字符串。
  • BINARY(M)：定长二进制字符串。
  • VARBINARY(M)：变长二进制字符串。
  • TINYBLOB：非常小的二进制大对象。
  • BLOB：二进制大对象。
  • MEDIUMBLOB：中等大小的二进制大对象。
  • LONGBLOB：大型二进制大对象。

2.1.4. 枚举类型（ENUM）

ENUM类型是一个字符串对象，其值来自一个预定义的列表，列表中的每个值在内部都由一个整数表示。

2.1.5. 集合类型（SET）

SET类型是一个字符串对象，可以包含零个或多个值，每个值都来自一个预定义的列表，类似于ENUM，但SET可以包含列表中的多个值。

2.1.6. JSON类型

JSON数据类型用于存储JSON（JavaScript Object Notation）文档。从MySQL 5.7.8版本开始引入，提供了一种存储JSON文档的方式，并允许对这些文档执行搜索、排序等操作。

2.1.7. 空间数据类型

MySQL还支持空间数据类型，用于存储地理空间数据，如点、线和多边形。这些数据类型包括GEOMETRY、POINT、LINESTRING、POLYGON等。

2.2.基本操作

2.2.1.DDL

2.2.1.1.数据库管理
1.创建数据库
CREATE DATABASE IF NOT EXISTS mydatabase;

这个命令会创建一个名为mydatabase的数据库，如果数据库已经存在，则不会执行任何操作（因为使用了IF NOT EXISTS）。

2.删除数据库
DROP DATABASE IF EXISTS mydatabase;

这个命令会删除名为mydatabase的数据库，如果数据库存在的话。使用IF EXISTS可以防止在数据库不存在时产生错误。

2.2.1.2.表管理
1.创建表
CREATE TABLE IF NOT EXISTS mydatabase.users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    username VARCHAR(50) NOT NULL,  
    email VARCHAR(100) NOT NULL,  
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);

这个命令在mydatabase数据库中创建了一个名为users的表，表中包含四个字段：id（自增主键）、username（不允许为空）、email（不允许为空）、created_at（时间戳，默认为当前时间）。

2.删除表
DROP TABLE IF EXISTS mydatabase.users;

这个命令会删除mydatabase数据库中名为users的表，如果表存在的话。

2.2.1.3.表列管理
1.添加列
ALTER TABLE mydatabase.users ADD COLUMN age INT;

这个命令在users表中添加了一个名为age的整数列。

2.删除列
ALTER TABLE mydatabase.users DROP COLUMN age;

这个命令从users表中删除了age列（假设它之前已经被添加）。

3.修改列
ALTER TABLE mydatabase.users MODIFY COLUMN email VARCHAR(255) NOT NULL;

这个命令修改了users表中email列的数据类型，将其长度从100改为255，并保持其不允许为空的约束。

2.2.2.DML

1. 插入数据（INSERT）

--插入单行：

INSERT INTO mydatabase.users (username, email, age) VALUES ('john_doe', 'john.doe@example.com', 30);

这个命令向mydatabase数据库中的users表插入了一行新数据，包括用户名、电子邮件和年龄。

--批量插入：

批量插入可以通过单条INSERT语句插入多行数据来实现，或者使用LOAD DATA INFILE命令从文件中批量加载数据。

示例1：单条INSERT语句插入多行

INSERT INTO mydatabase.users (username, email, age) VALUES  
('Alice', 'alice@example.com', 30),  
('Bob', 'bob@example.com', 25),  
('Charlie', 'charlie@example.com', 35);

示例2：使用LOAD DATA INFILE批量加载数据

假设你有一个名为users.csv的文件，内容格式如下：

username,email,age  
Alice,alice@example.com,30  
Bob,bob@example.com,25  
Charlie,charlie@example.com,35

你可以使用以下命令将该文件中的数据批量插入到users表中：

LOAD DATA INFILE '/path/to/your/users.csv'  
INTO TABLE mydatabase.users  
FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
LINES TERMINATED BY '\n'  
IGNORE 1 ROWS  
(username, email, age);

注意：LOAD DATA INFILE命令的路径必须是MySQL服务器可以访问的，而且可能需要MySQL的配置支持（如local-infile选项）。

2. 更新数据（UPDATE）

--单表更新

UPDATE mydatabase.users SET email = 'new.email@example.com' WHERE username = 'john_doe';

这个命令更新了mydatabase数据库中users表里用户名为john_doe的用户的电子邮件地址。

--多表更新

MySQL 8.0及以上版本

从MySQL 8.0开始，可以直接在UPDATE语句中使用JOIN来更新多个表。这里有一个简单的例子：

UPDATE table1 t1  
JOIN table2 t2 ON t1.id = t2.foreign_id  
SET t1.column_name = t2.column_value  
WHERE some_condition;

这个例子中，我们根据table1和table2之间的关联（t1.id = t2.foreign_id）来更新table1中的column_name字段，使其等于table2中的column_value字段的值。WHERE子句是可选的，用于进一步限制哪些行会被更新。

MySQL 8.0以下版本

在MySQL 8.0以下版本中，由于UPDATE语句不支持直接使用JOIN，我们需要采用其他方法来实现多表更新。

方法1：使用子查询

你可以通过子查询来模拟跨表更新的效果。但是，这种方法通常适用于你能够仅根据单个表中的数据来确定要更新的值的情况。

UPDATE table1  
SET column_name = (  
    SELECT column_value  
    FROM table2  
    WHERE table1.id = table2.foreign_id  
    LIMIT 1 -- 如果存在多个匹配项，LIMIT 1来确保只取一个  
)  
WHERE EXISTS (  
    SELECT 1  
    FROM table2  
    WHERE table1.id = table2.foreign_id  
);

注意：使用LIMIT 1是因为子查询在UPDATE语句中必须返回单个值。如果子查询可能返回多个值，你需要确保通过某种方式（如LIMIT或聚合函数）来限制结果集。

方法2：使用临时表或中间表

对于更复杂的跨表更新，可以考虑将需要更新的数据先存储到一个临时表或中间表中，然后基于这个临时表来更新目标表。

  1. 创建一个临时表，包含更新所需的数据。
  2. 将需要更新的数据插入到这个临时表中。
  3. 使用JOIN（在支持JOIN的查询中，如SELECT）来查询临时表和目标表，以确定哪些行需要更新。
  4. 根据查询结果执行UPDATE操作。

虽然这种方法更复杂，但它提供了更高的灵活性和控制能力，尤其是在处理复杂的数据关系和逻辑时。

3. 删除数据（DELETE）

--单表删除

DELETE FROM mydatabase.users WHERE username = 'john_doe';

这个命令从mydatabase数据库中的users表删除了用户名为john_doe的行。

--多表删除

方法一：使用事务

如果你的数据库支持事务（大多数现代数据库系统，包括MySQL的InnoDB存储引擎，都支持事务），你可以在一个事务中执行多个DELETE语句。这样，所有的删除操作要么全部成功，要么在遇到错误时全部回滚。

START TRANSACTION;  
  
DELETE FROM table1 WHERE id = 123;  
DELETE FROM table2 WHERE id = 123;  
DELETE FROM table3 WHERE id = 123;  
  
COMMIT;

方法二：使用触发器

如果你经常需要基于某个表的更改来更新或删除其他表中的数据，可以考虑使用触发器。但是，请注意，过度使用触发器可能会导致数据库逻辑难以理解和维护，并可能影响性能。

假设你想在删除table1中的记录时自动删除table2和table3中的相关记录，你可以为table1的DELETE操作创建一个触发器。

DELIMITER $$  
  
CREATE TRIGGER after_table1_delete  
AFTER DELETE ON table1  
FOR EACH ROW  
BEGIN  
    DELETE FROM table2 WHERE id = OLD.id;  
    DELETE FROM table3 WHERE id = OLD.id;  
END$$  
  
DELIMITER ;

但是，请注意，触发器是自动的，它们在你对table1执行DELETE时自动触发，而不是允许你手动选择何时删除多个表中的数据。

方法三：编写存储过程

如果你需要更复杂的逻辑来删除多个表中的数据，或者你想将这个过程封装起来以便重复使用，你可以编写一个存储过程。

DELIMITER $$  
  
CREATE PROCEDURE DeleteFromMultipleTables(IN p_id INT)  
BEGIN  
    DELETE FROM table1 WHERE id = p_id;  
    DELETE FROM table2 WHERE id = p_id;  
    DELETE FROM table3 WHERE id = p_id;  
END$$  
  
DELIMITER ;

然后，你可以通过调用这个存储过程来删除数据：

CALL DeleteFromMultipleTables(123);

方法四：使用JOIN（对于特定场景）

虽然你不能直接在一个DELETE语句中跨多个表进行JOIN来删除数据，但在某些特定场景下，你可以通过JOIN和子查询来模拟这个行为。然而，这通常不是删除多个表中数据的通用方法，而是针对特定查询优化。

3.MySQL查询

3.1.基本语法

MySQL的查询（Query）是数据库操作中最基本也是最重要的部分之一，它允许你检索存储在数据库中的数据。以下是一个MySQL查询的基本语法框架，虽然实际查询可以根据需要变得更加复杂：

SELECT column1, column2, ...  
FROM table_name  
WHERE condition  
GROUP BY column_name(s)  
HAVING condition  
ORDER BY column_name(s) ASC|DESC  
LIMIT number;
  1. SELECT: 指定要从数据库表中检索哪些列（字段）。如果想选择所有列，可以使用星号（*）作为通配符。
  2. FROM: 指定要从中检索数据的表名。
  3. WHERE: 一个可选条件，用于指定哪些记录应该被返回。如果省略了WHERE子句，所有记录都会被检索出来。
  4. GROUP BY: 用于将结果集中的记录分组，通常与聚合函数（如COUNT(), MAX(), MIN(), SUM(), AVG()）一起使用，以对每组执行计算。
  5. HAVING: 一个过滤条件，类似于WHERE子句，但它应用于分组后的结果集。这意味着它用于过滤GROUP BY子句创建的分组。
  6. ORDER BY: 用于对结果集中的记录进行排序。默认情况下，记录会按照升序（ASC）排列，但你也可以指定降序（DESC）。
  7. LIMIT: 用于限制查询结果返回的记录数。这对于提高大型数据集的查询性能特别有用，也常用于分页显示数据。

3.2.条件查询

基本语法

SELECT column1, column2, ...  
FROM table_name  
WHERE condition;
  • SELECT后面跟要查询的列名，如果要查询所有列，可以使用*代替列名。
  • FROM后面跟表名，指定从哪个表中查询数据。
  • WHERE子句是可选的，用于指定查询条件。

示例

假设有一个名为employees的表，包含以下列：id, name, age, department。

示例1：查询特定部门的员工
SELECT *  
FROM employees  
WHERE department = 'IT';

这个查询会返回所有在IT部门的员工记录。

示例2：查询年龄大于30的员工
SELECT name, age  
FROM employees  
WHERE age > 30;

这个查询会返回所有年龄大于30岁的员工的姓名和年龄。

示例3：使用AND操作符查询满足多个条件的记录
SELECT *  
FROM employees  
WHERE age > 30 AND department = 'Sales';

这个查询会返回在Sales部门且年龄大于30岁的所有员工记录。

示例4：使用OR操作符查询满足任一条件的记录
SELECT *  
FROM employees  
WHERE age > 30 OR department = 'HR';

这个查询会返回年龄大于30岁或在HR部门的所有员工记录。

示例5：使用IN操作符查询特定范围内的值
SELECT *  
FROM employees  
WHERE department IN ('IT', 'HR');

这个查询会返回在IT部门或HR部门的所有员工记录。

示例6：使用LIKE操作符进行模糊查询
SELECT *  
FROM employees  
WHERE name LIKE 'J%';

这个查询会返回所有姓名以J开头的员工记录。%是一个通配符，表示任意数量的字符。

示例7：使用BETWEEN操作符查询范围内的值
SELECT *  
FROM employees  
WHERE age BETWEEN 25 AND 35;

这个查询会返回年龄在25到35岁之间的所有员工记录（包括25岁和35岁）。

3.3.排序与分页

3.3.1.排序（ORDER BY）

ORDER BY子句用于根据一个或多个列对结果集进行排序。你可以指定升序（ASC）或降序（DESC）排序，如果不指定，则默认为升序。

示例：

假设我们有一个名为employees的表，包含id, name, age, 和 salary列。

  • 按年龄升序排序：
SELECT * FROM employees ORDER BY age ASC;

注意：由于ASC是默认的，所以你也可以省略它，直接写ORDER BY age;。

  • 按薪水降序排序：
SELECT * FROM employees ORDER BY salary DESC;

3.3.2.分页（LIMIT）

LIMIT子句用于限制查询结果的数量，并可以用于实现分页功能。LIMIT接受一个或两个参数，第一个参数是偏移量（从哪一行开始），第二个参数是要返回的记录数。如果省略第一个参数，偏移量默认为0。

示例：
  • 获取前5条记录：
SELECT * FROM employees LIMIT 5;
  • 分页查询，每页5条记录，获取第2页的数据：

在分页查询中，如果你想要获取第N页的数据，每页M条记录，那么你应该使用(N-1)*M作为LIMIT的第一个参数（偏移量），M作为第二个参数（记录数）。

SELECT * FROM employees LIMIT 5 OFFSET 5;  
-- 或者更常见的写法（在某些数据库系统中）  
SELECT * FROM employees LIMIT 5, 5;

注意：MySQL的LIMIT子句支持上述两种写法，但并非所有数据库系统都支持第二种（带有两个数字，无OFFSET关键字的）写法。

在这个例子中，LIMIT 5 OFFSET 5;（或LIMIT 5, 5;）表示跳过前5条记录，然后获取接下来的5条记录，这相当于获取第2页的数据（假设每页5条记录）。

3.4.分组查询

基本语法

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)  
FROM table_name  
WHERE condition  
GROUP BY column_name(s)  
ORDER BY column_name(s);
  • SELECT后面跟要显示的列名或聚合函数。
  • FROM后面跟表名。
  • WHERE子句是可选的，用于指定过滤条件。
  • GROUP BY后面跟用于分组的列名，可以是一个或多个列。
  • ORDER BY子句是可选的，用于对分组后的结果进行排序。

示例

假设我们有一个名为orders的表，包含以下列：order_id, customer_id, order_date, amount。

示例1：按客户ID分组，计算每个客户的订单总数
SELECT customer_id, COUNT(order_id) AS total_orders  
FROM orders  
GROUP BY customer_id;

这个查询会返回每个customer_id对应的订单总数。

示例2：按客户ID分组，计算每个客户的订单总金额
SELECT customer_id, SUM(amount) AS total_amount  
FROM orders  
GROUP BY customer_id;

这个查询会返回每个customer_id对应的订单总金额。

示例3：结合WHERE子句进行过滤
SELECT customer_id, COUNT(order_id) AS total_orders  
FROM orders  
WHERE order_date > '2023-01-01'  
GROUP BY customer_id;

这个查询会返回在2023年1月1日之后下单的每个客户的订单总数。

示例4：使用HAVING子句对分组后的结果进行过滤
SELECT customer_id, COUNT(order_id) AS total_orders  
FROM orders  
GROUP BY customer_id  
HAVING COUNT(order_id) > 2;

这个查询会返回订单总数大于2的客户的customer_id和他们的订单总数。注意，HAVING子句是在分组后对分组结果进行过滤的，而WHERE子句是在分组前对原始记录进行过滤的。

示例5：结合ORDER BY子句进行排序
SELECT customer_id, COUNT(order_id) AS total_orders  
FROM orders  
GROUP BY customer_id  
ORDER BY total_orders DESC;

这个查询会返回每个客户的订单总数，并按订单总数降序排序。

where和having的区别：

在MySQL中，WHERE和HAVING子句都用于设置条件来过滤记录，但它们的应用场景和作用范围有所不同。主要区别在于它们的作用时机和它们所能引用的列。

WHERE子句
  • 作用时机：WHERE子句在数据分组（GROUP BY）和聚合（如SUM, AVG, COUNT等）之前对记录进行过滤。
  • 作用范围：WHERE子句可以引用表中的任何列（包括未在SELECT列表中指定的列），但不能直接引用聚合函数的结果。
  • 用途：主要用于过滤掉不满足条件的行，确保这些行不会参与后续的分组和聚合操作。
HAVING子句
  • 作用时机：HAVING子句在数据分组和聚合之后对分组结果进行过滤。
  • 作用范围：HAVING子句可以引用聚合函数的结果，也可以引用SELECT列表中指定的列（但这些列必须包含在GROUP BY子句中，除非它们是聚合函数的结果）。
  • 用途：主要用于过滤掉不满足条件的分组，确保只有满足特定条件的分组才会出现在最终的结果集中。
示例

假设我们有一个名为sales的表，包含salesperson_id（销售员ID）、product_id（产品ID）和amount（销售额）等列。

使用WHERE

如果我们想筛选出销售额大于1000的记录，但不关心是哪个销售员或哪个产品，我们可以使用WHERE子句：


SELECT * FROM sales WHERE amount > 1000;

但如果我们想基于销售员进行分组，并计算每个销售员的销售额总和，但只关心销售额总和大于1000的销售员，我们仍然需要使用WHERE（但这里实际上可能不适用，因为WHERE在分组前过滤），或者更准确地，是在分组后使用HAVING：

使用HAVING（错误示例，仅用于说明WHERE的局限性）
-- 注意：这个查询实际上不会按预期工作，因为WHERE在分组前过滤  
SELECT salesperson_id, SUM(amount) AS total_sales  
FROM sales  
WHERE SUM(amount) > 1000  -- 这是错误的，因为WHERE不能引用聚合函数  
GROUP BY salesperson_id;
正确的使用HAVING
SELECT salesperson_id, SUM(amount) AS total_sales  
FROM sales  
GROUP BY salesperson_id  
HAVING SUM(amount) > 1000;  -- 正确的，HAVING在分组后过滤

在这个例子中，HAVING子句正确地过滤了销售额总和不大于1000的销售员。

3.5.常用函数

3.5.1.字符串函数

  • CONCAT(str1, str2, ...)：连接两个或多个字符串。
  • LENGTH(str)：返回字符串的长度（字节数）。
  • CHAR_LENGTH(str) 或 CHARACTER_LENGTH(str)：返回字符串的长度（字符数）。
  • LOWER(str)：将字符串转换为小写。
  • UPPER(str)：将字符串转换为大写。
  • SUBSTRING(str, pos, len) 或 MID(str, pos, len)：从字符串中提取子串。
  • TRIM([BOTH | LEADING | TRAILING] [remstr] FROM] str)：去除字符串两端的空格或指定字符。
  • REPLACE(str, from_str, to_str)：在字符串中用to_str替换所有的from_str。

3.5.2.数学函数

  • ABS(x)：返回x的绝对值。
  • CEIL(x) 或 CEILING(x)：返回大于或等于x的最小整数。
  • FLOOR(x)：返回小于或等于x的最大整数。
  • ROUND(x, d)：将x四舍五入到d位小数。
  • RAND()：返回一个0到1之间的随机浮点数。
  • POW(x, y) 或 POWER(x, y)：返回x的y次方。
  • SQRT(x)：返回x的平方根。

3.5.3.日期和时间函数

  • NOW()：返回当前的日期和时间。
  • CURDATE()：返回当前的日期。
  • CURTIME()：返回当前的时间。
  • DATE_FORMAT(date, format)：根据指定的格式显示日期/时间值。
  • DATE_ADD(date, INTERVAL expr type)：在日期上加上一个时间间隔。
  • DATEDIFF(expr1, expr2)：返回两个日期之间的天数。
  • YEAR(date)：从日期中提取年份。
  • MONTH(date)：从日期中提取月份。
  • DAY(date)：从日期中提取日。

3.5.4.聚合函数

  • COUNT(*)：返回查询结果的行数。
  • COUNT(column_name)：返回指定列中非NULL值的数量。
  • SUM(column_name)：返回指定列值的总和。
  • AVG(column_name)：返回指定列的平均值。
  • MAX(column_name)：返回指定列的最大值。
  • MIN(column_name)：返回指定列的最小值。

3.5.5.流程控制函数

  • IF(condition, value_if_true, value_if_false)：如果条件为真，则返回value_if_true，否则返回value_if_false。
  • CASE语句：类似于其他编程语言中的switch或if-else-if语句，用于在SQL查询中实现更复杂的条件逻辑。

3.6.子查询

子查询的类型

  1. 标量子查询：返回单个值的子查询，通常用于SELECT列表、WHERE子句或HAVING子句中作为条件表达式的一部分。
  2. 列子查询：返回一列值的子查询，通常与IN、ANY、SOME或ALL操作符结合使用，在WHERE或HAVING子句中过滤行。
  3. 行子查询：返回单行多列数据的子查询，通常与EXISTS操作符或比较操作符（如=）结合使用，在WHERE子句中比较行。
  4. 表子查询（或派生表）：返回临时结果集，可以像表一样在FROM子句中引用。

示例

标量子查询
SELECT employee_name, (SELECT COUNT(*) FROM orders WHERE employee_id = e.id) AS order_count  
FROM employees e;

这个查询为每个员工返回了他们的名字和他们各自的订单数量。

列子查询
SELECT employee_name  
FROM employees  
WHERE id IN (SELECT employee_id FROM orders WHERE amount > 1000);

这个查询返回了所有参与过金额超过1000的订单的员工名字。

行子查询

假设employees表有一个复合主键(id, name)，你可能不会直接这样使用行子查询，但这里是一个概念性的例子：

SELECT *  
FROM employees e1  
WHERE (e1.id, e1.salary) = (SELECT id, MAX(salary) FROM employees);

注意：上面的查询在逻辑上可能不是最准确的，因为MAX(salary)可能返回多个员工的ID（如果多个员工有相同的最高薪水）。通常，应该使用其他方法来处理这种情况。

表子查询
SELECT o.order_id, o.amount  
FROM (SELECT * FROM orders WHERE order_date > '2023-01-01') AS o  
WHERE o.amount > 100;

这个查询首先选择2023年1月1日之后的所有订单作为一个临时表（表子查询），然后从这个临时表中选择金额超过100的订单。

注意事项

  • 子查询可能会降低查询的性能，特别是当它们返回大量数据时。优化子查询或考虑使用其他查询策略（如连接）可能是必要的。
  • 确保子查询返回的数据类型与外层查询中期望的数据类型相匹配。
  • 在使用子查询时，注意SQL注入的风险，特别是当子查询包含来自用户输入的数据时。

3.7.联表查询

笛卡尔乘积

笛卡尔乘积（Cartesian product）是数学中的一个概念，尤其在集合论、数据库理论和计算机科学中非常重要。它表示两个或多个集合中所有可能的有序对的集合。如果集合A有m个元素，集合B有n个元素，那么A和B的笛卡尔乘积将包含m*n个元素，每个元素都是一个有序对(a, b)，其中a属于A，b属于B。

定义：设A和B是两个集合，那么A和B的笛卡尔乘积记作A×B，是一个集合，该集合的元素是所有形式为(a, b)的有序对，其中a属于A，b属于B。

基本语法

SELECT columns  
FROM table1  
JOIN table2  
ON table1.common_field = table2.common_field;

这里的JOIN可以是INNER JOIN、LEFT JOIN、RIGHT JOIN等，common_field是两个表中用于连接的共同字段。

示例

假设我们有两个表：employees（员工表）和departments（部门表）。

employees表结构：

  • employee_id（员工ID）
  • name（姓名）
  • department_id（部门ID）

departments表结构：

  • department_id（部门ID）
  • department_name（部门名称）
内连接（INNER JOIN）

内连接返回两个表中满足连接条件的所有行。如果表A中的行与表B中的行通过连接条件匹配，则这些行会出现在结果集中。如果表A中的某行在表B中没有匹配的行，或者表B中的某行在表A中没有匹配的行，则这些行不会出现在结果集中。内连接是默认的连接类型，如果在JOIN语句中没有明确指定连接类型，则默认为内连接。

查询所有员工及其对应的部门名称。

SELECT employees.name, departments.department_name  
FROM employees  
INNER JOIN departments ON employees.department_id = departments.department_id;
左连接（LEFT JOIN）

左连接返回左表（LEFT JOIN语句中指定的第一个表）的所有行，即使右表中没有匹配的行。如果左表的行在右表中有匹配的行，则结果集中将包含这些行的匹配项；如果左表的行在右表中没有匹配的行，则结果集中这些行的右表部分将包含NULL值。

查询所有员工及其对应的部门名称，即使某些员工没有分配部门（即department_id为NULL）。

SELECT employees.name, departments.department_name  
FROM employees  
LEFT JOIN departments ON employees.department_id = departments.department_id;

这将返回employees表中的所有记录，以及与之匹配的departments表中的记录。如果employees表中的某条记录在departments表中没有匹配项（即该员工没有分配部门），则departments表中的列将包含NULL。

右连接（RIGHT JOIN）

右连接与左连接相反，它返回右表（RIGHT JOIN语句中指定的第二个表）的所有行，即使左表中没有匹配的行。如果右表的行在左表中有匹配的行，则结果集中将包含这些行的匹配项；如果右表的行在左表中没有匹配的行，则结果集中这些行的左表部分将包含NULL值。

查询所有部门及其对应的员工姓名，即使某些部门没有员工。

SELECT employees.name, departments.department_name  
FROM employees  
RIGHT JOIN departments ON employees.department_id = departments.department_id;

注意：在实际应用中，右连接的使用相对较少，因为它可以通过交换表的位置和使用左连接来达到相同的效果。

全连接（FULL JOIN，通过UNION模拟）

全连接返回左表和右表中所有的行。如果左表的行在右表中有匹配的行，则这些行的匹配项会出现在结果集中；如果左表的行在右表中没有匹配的行，则结果集中这些行的右表部分将包含NULL值；同样，如果右表的行在左表中没有匹配的行，则结果集中这些行的左表部分将包含NULL值。然而，需要注意的是，MySQL不直接支持FULL JOIN。但是，你可以通过结合LEFT JOIN和RIGHT JOIN，并使用UNION或UNION ALL（如果需要包含重复行）来模拟FULL JOIN的效果。

SELECT employees.name, departments.department_name  
FROM employees  
LEFT JOIN departments ON employees.department_id = departments.department_id  
UNION  
SELECT employees.name, departments.department_name  
FROM employees  
RIGHT JOIN departments ON employees.department_id = departments.department_id  
WHERE employees.name IS NULL;

但请注意，上面的RIGHT JOIN后的WHERE子句实际上是不必要的，因为UNION会自动去除重复的行，而RIGHT JOIN中employees.name IS NULL的行（即只有部门没有员工的行）在LEFT JOIN中是不会出现的，所以通常只需要LEFT JOIN和RIGHT JOIN（不带WHERE子句）的结果进行UNION即可。然而，由于RIGHT JOIN的结果集可以通过交换表的位置和使用LEFT JOIN来获得，因此通常只需要一个LEFT JOIN加上一个额外的查询（仅选择那些在LEFT JOIN结果中未出现的部门）来模拟全连接。但更常见的做法是使用UNION将LEFT JOIN和LEFT JOIN的逆（即先查询departments再左连接employees）的结果合并起来，并去除重复行。

3.8.SQL执行顺序

MySQL查询的实际执行顺序大致如下（注意，这只是一个简化的逻辑顺序，实际执行时可能因MySQL版本和查询优化器的决策而有所不同）：

  1. FROM子句：首先，MySQL会处理FROM子句，确定要从哪些表中检索数据。如果查询中包含了连接（JOINs），MySQL会先计算这些连接。
  2. WHERE子句：接下来，MySQL会应用WHERE子句中的条件来过滤FROM子句产生的结果集。这一步会排除不满足条件的行。
  3. GROUP BY子句：如果查询中包含了GROUP BY子句，MySQL会将结果集中的行分组。这一步通常与聚合函数（如SUM()、AVG()、COUNT()等）一起使用，以计算每个组的统计信息。
  4. HAVING子句：在GROUP BY子句之后（如果查询中包含了GROUP BY），MySQL会应用HAVING子句中的条件来过滤分组后的结果集。与WHERE子句不同，HAVING子句可以引用聚合函数的结果。
  5. SELECT子句：此时，MySQL会处理SELECT子句，确定要返回哪些列或表达式。如果查询中包含了聚合函数，它们将在此步骤中计算。
  6. DISTINCT关键字：如果SELECT子句中包含了DISTINCT关键字，MySQL会去除结果集中的重复行。
  7. ORDER BY子句：最后，如果查询中包含了ORDER BY子句，MySQL会根据指定的列或表达式对结果集进行排序。注意，ORDER BY是在查询的最后阶段应用的，这意味着它会影响查询的最终输出顺序，但不会影响查询的性能（除了排序本身所需的时间外）。
  8. LIMIT和OFFSET子句（如果适用）：在返回结果集之前，MySQL会应用LIMIT和OFFSET子句来限制返回的行数或跳过某些行。

4.MySQL事务

4.1.什么是事务

MySQL中的事务（Transaction）是一种数据库操作机制，它将一系列的操作作为一个整体进行执行，这些操作要么全部成功，要么全部失败，不会存在部分成功的情况。事务是数据库管理系统（DBMS）中用于确保数据完整性和一致性的重要手段。事务通常包含多个SQL语句，这些语句被看作是一个不可分割的工作单元。

事务的主要特点包括：

  • 原子性：事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败，不会留下部分执行的痕迹。
  • 一致性：事务必须保证数据库从一个一致性状态转换到另一个一致性状态，事务的执行不能破坏数据库的完整性约束。
  • 隔离性：多个事务并发执行时，系统应提供隔离机制，使每个事务都感觉自己是在单独执行，不会受到其他事务的影响。
  • 持久性：一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。

4.2.事务的四大属性

MySQL事务的四大属性通常被称为ACID特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

  1. 原子性（Atomicity）：
    • 事务是数据库中的最小工作单位，是一个不可分割的整体。
    • 事务中的所有操作要么全部提交成功，要么全部失败回滚，不会结束在中间某个环节。
  2. 一致性（Consistency）：
    • 事务的执行必须使数据库从一个一致性状态转换到另一个一致性状态。
    • 事务的执行结果必须满足所有的预定义规则，包括数据的完整性约束、触发器、级联回滚等。
  3. 隔离性（Isolation）：
    • 数据库系统提供一定的隔离机制，保证事务在不受外部干扰的情况下独立执行。
    • 隔离性有不同的级别，包括读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。
  4. 持久性（Durability）：
    • 一旦事务被提交，它对数据库的修改就是永久性的。
    • 即使发生系统崩溃或其他故障，已经提交的事务对数据库的修改也不会丢失。

4.3.数据库的并发性问题

4.3.1.事务的隔离级别

事务的隔离级别是用来定义事务可能受其他并发事务影响的程度。SQL标准定义了四种隔离级别，它们逐级增强了对并发事务的隔离效果，但同时也可能影响系统的并发性能。

  1. 读未提交（READ UNCOMMITTED）
    • 最低的隔离级别，允许事务读取未被其他事务提交的变更。这可能导致脏读、不可重复读和幻读。
  2. 读已提交（READ COMMITTED）
    • 允许事务读取已经被其他事务提交的变更，但可以避免脏读。在这个级别下，不可重复读和幻读仍可能发生。
  3. 可重复读（REPEATABLE READ）
    • MySQL的默认隔离级别。它确保了在同一个事务内多次读取同样记录的结果是一致的，避免了不可重复读，但幻读仍有可能发生。
  4. 串行化（SERIALIZABLE）
    • 最高的隔离级别，通过强制事务串行执行，避免了脏读、不可重复读和幻读。但这种方式会显著降低系统的并发性能。

4.3.2.悲观锁

悲观锁主要用于处理高冲突环境下的并发问题，它假定最坏的情况，即认为冲突会经常发生。因此，在数据被处理前，悲观锁会先对数据进行加锁，以阻止其他事务的并发访问。

  1. 实现机制
    • 悲观锁通常通过数据库的锁机制实现，如行锁、表锁等。
    • 在MySQL中，可以通过SELECT ... FOR UPDATE语句显式地对记录加锁。
  2. 优缺点
    • 优点：能有效防止数据在并发情况下被多个事务同时修改，保证了数据的一致性和完整性。
    • 缺点：在高并发环境下，频繁的加锁和解锁操作会消耗大量的系统资源，降低系统的并发性能

4.3.3.乐观锁

乐观锁主要用于处理冲突概率较低的并发环境，它假定最好的情况，即认为冲突不会经常发生。因此，乐观锁在数据提交更新时，才会正式检查是否存在冲突。

  1. 实现机制
    • 乐观锁通常通过数据版本号（Version）或时间戳（Timestamp）等机制实现。
    • 在更新数据时，会比较版本号或时间戳，如果版本号或时间戳与数据库中的记录一致，则进行更新；否则，认为数据已被其他事务修改，更新失败。
  2. 优缺点
    • 优点：不需要在事务开始时加锁，减少了锁的开销，提高了系统的并发性能。
    • 缺点：在更新数据时，如果检测到冲突，需要回滚事务并重新尝试，这可能会增加事务的失败率和重试成本。

5.MySQL索引

5.1.什么是索引

索引是MySQL中用于快速查找和检索数据的一种数据结构。它类似于书籍的目录，可以帮助数据库系统快速定位到数据的物理位置，从而加快查询速度，减少磁盘I/O次数。索引是在存储引擎中实现的，每种存储引擎（如InnoDB、MyISAM）实现索引的方式可能不同。

5.2.索引的底层原理

索引的底层逻辑通常基于B-Tree（平衡树）或其变种B+Tree。B+Tree是一种多路平衡查找树，它能够有效减少查找过程中磁盘I/O操作的次数。在B+Tree中，所有的数据都存储在叶子节点上，并且叶子节点之间通过指针相连，这使得范围查询变得非常高效。此外，B+Tree还具有良好的平衡性，能够保证查找效率的稳定。

5.3.索引的分类

MySQL中的索引可以从多个维度进行分类，以下是一些常见的分类方式：

  1. 功能上分类：
    • 普通索引：最常见的索引类型，不附加任何限制条件，只用于提高查询效率。
    • 唯一索引：确保索引列的所有值都唯一，但允许有空值。
    • 主键索引：一种特殊的唯一索引，不仅要求值唯一，而且不允许有空值。一个表只能有一个主键索引。
    • 全文索引：用于对文本内容进行搜索，支持模糊查询。
    • 空间索引：用于对地理空间数据进行索引，支持基于位置、范围和距离的搜索。
    • 哈希索引：基于哈希表的索引，适用于等值查询，但不适合范围查询。
    • 复合索引（联合索引）：基于多个列的索引，可以加快基于多个列的查询速度。
  2. 物理实现上分类：
    • 聚簇索引：数据行的物理顺序与索引的顺序相同，InnoDB的默认主键索引就是聚簇索引。
    • 非聚簇索引（二级索引、辅助索引）：索引顺序与数据行的物理

5.4.索引的优缺点

优点：

  1. 加快查询速度：通过索引，可以快速定位到数据的物理位置，减少磁盘I/O次数。
  2. 保证数据的唯一性：唯一索引和主键索引可以保证数据的唯一性。
  3. 加快表连接速度：对于依赖关系的子表和父表联合查询时，可以提高查询速度。
  4. 加快分组和排序速度：在使用GROUP BY或ORDER BY查询数据时，可以显著减少分组和排序的时间。

缺点：

  1. 占用额外空间：索引文件会占用磁盘空间。
  2. 降低更新表的速度：在插入、删除和修改数据时，索引也需要被更新，这会增加额外的开销。
  3. 增加查询优化器的负担：当数据量非常大时，查询优化器需要花费更多的时间来选择合适的索引。

5.5.使用索引的注意事项

  1. 避免在索引列上使用函数或运算：这会导致索引失效，从而进行全表扫描。
  2. 避免使用!=、IN等没有顺序的操作符：这些操作符可能会使索引失效。
  3. 使用组合索引时要遵循最左前缀原则：查询条件中必须包含组合索引的最左列，才能有效利用索引。
  4. 索引列尽量不要让字段的默认值为NULL：NULL值会使索引失效。
  5. 注意查询条件左右两侧类型不匹配的问题：这可能会导致索引失效。
  6. like 'value%'可以使用到索引，但like '%value%'会全表扫描。
  7. 合理选择索引类型：根据实际需求选择合适的索引类型，如普通索引、唯一索引、全文索引等。
  8. 定期维护索引：包括重建索引、优化索引等，以保证索引的有效性和性能。

6.MySQL视图

6.1.什么是视图

MySQL中的视图（View）是一种虚拟表，它并不实际存储数据，而是基于SQL查询的结果集。视图的内容由查询定义，这些内容来源于一个或多个表或其他视图。对于用户来说，视图就像一个真实的表一样，可以进行查询、更新、删除等操作（但并非所有视图都支持更新操作，这取决于视图的定义和底层表的结构）。视图的主要作用在于提供数据的抽象层，简化复杂的查询，以及保护数据的安全性和完整性。

6.2.视图作用

  1. 简化复杂查询：视图可以将复杂的查询逻辑封装起来，提供一个简单的接口供其他查询使用。这有助于隐藏底层数据结构和查询细节，使查询语句更加简洁和易于维护。
  2. 保护数据：通过视图，可以限制用户对数据的访问权限。只向用户公开需要的数据，同时隐藏敏感信息，从而提高数据的安全性。
  3. 提高数据独立性：如果多个应用程序需要访问相同的数据，使用视图可以确保数据的一致性。当底层表发生变化时，只需要更新视图的定义，而不需要修改所有使用该视图的应用程序。
  4. 数据重用：视图可以作为基础查询的模板，被其他查询重用。当需要相同的查询逻辑时，可以直接使用视图，避免了重复编写相同的查询语句。

6.3.视图操作

  1. 创建视图：使用CREATE VIEW语句来创建视图。语法格式通常为CREATE VIEW 视图名 AS SELECT语句。其中，SELECT语句定义了视图的内容，可以包含连接、过滤、聚合等操作。
  2. 查询视图：对视图的查询就像对数据表的查询一样，可以使用SELECT语句来检索视图中的数据。
  3. 更新视图：某些视图是可更新的，即可以通过视图来插入、删除或修改数据。然而，并非所有视图都支持更新操作，这取决于视图的定义和底层表的结构。对于可更新的视图，更新操作会转换为对底层表的更新。
  4. 删除视图：使用DROP VIEW语句来删除视图。如果需要删除多个视图，可以在DROP VIEW语句中列出所有要删除的视图名，用逗号分隔。

6.4.视图规则与限制

  1. 命名规则：视图必须唯一命名，不能与数据库中的其他表或视图重名。
  2. 权限要求：为了创建视图，用户必须具有足够的访问权限，包括对视图中引用的所有表和视图的权限。
  3. 更新限制：并非所有视图都支持更新操作。可更新的视图需要满足一定的条件，如视图中的行与底层表中的行之间必须存在一对一的关系。
  4. 性能考虑：虽然视图可以简化查询，但在某些情况下，频繁使用视图可能会导致性能下降。因为每次查询视图时，数据库都需要执行视图定义中的查询语句，这可能会增加查询的复杂性和时间。
  5. 视图定义限制：视图定义中不能包含某些特定的SQL语句和元素，如ORDER BY（在某些情况下可以被忽略）、DISTINCT、GROUP BY（在聚合视图中除外）、UNION或UNION ALL等。此外，视图也不能被索引，也不能有关联的触发器或默认值。

7.MySQL触发器

7.1.什么是触发器

MySQL触发器（Trigger）是数据库管理系统中的一个特殊对象，它是一段与表相关联的SQL代码，用于在数据库表中发生特定事件（如INSERT、UPDATE、DELETE操作）时自动执行预定义的SQL语句或逻辑。触发器的主要作用是监视数据库表的数据变化，并在满足指定条件时触发相关的响应行为，以实现数据的自动处理、业务规则的强制实施、数据完整性校验、日志记录等功能。

触发器通常与数据库表一起创建、修改和删除，它可以被视为一种数据库的约束，用于保证数据的完整性和一致性。触发器可以在特定的数据操作之前或之后触发执行，且可以根据用户定义的条件判断是否执行相应的逻辑。此外，触发器还可以调用存储过程、函数、触发其他触发器等，实现更复杂的业务逻辑。

7.2.触发器操作

触发器的操作主要包括创建触发器、查看触发器以及删除触发器。

1. 创建触发器

创建触发器需要使用CREATE TRIGGER语句，并指定触发器的名称、触发时机（BEFORE或AFTER）、触发事件（INSERT、UPDATE、DELETE）、触发的表以及触发时执行的逻辑。创建触发器的语法结构如下：

CREATE TRIGGER 触发器名称  
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}  
ON 表名  
FOR EACH ROW  
BEGIN  
    -- 触发器逻辑  
END;
  • 触发器名称：自定义，需保证唯一性。
  • 触发时机：BEFORE表示在数据操作之前触发，AFTER表示在数据操作之后触发。
  • 触发事件：指定触发器的触发条件，即INSERT、UPDATE或DELETE操作。
  • 表名：指定触发器关联的表。
  • 触发器逻辑：定义触发器触发时执行的SQL语句或逻辑块。

2. 查看触发器

查看触发器信息可以使用SHOW TRIGGERS语句，该语句会列出当前数据库中所有的触发器信息。另外，也可以通过查询INFORMATION_SCHEMA.TRIGGERS表来获取触发器的详细信息，使用方法如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='触发器名' \G

这里，\G是一个可选的语句终止符，用于将输出结果格式化，使其更易于阅读。

3. 删除触发器

删除触发器使用DROP TRIGGER语句，并指定要删除的触发器名称。如果需要指定数据库名，则可以使用[数据库名.]触发器名的格式。删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] 触发器名称;
  • IF EXISTS：可选参数，用于在触发器不存在时避免产生错误。

注意事项

  • 触发器的使用需要谨慎，过多或不当的触发器可能会对数据库性能产生负面影响。
  • 触发器通常会在事务内执行，因此如果事务回滚，触发器的操作也会回滚。
  • 调试触发器可能比较困难，因为触发器的执行是隐式的。建议在触发器中加入日志记录语句来辅助调试。
  • 触发器不能调用另一个触发器，即触发器的执行不会触发另一个触发器。

8.MySQL存储过程

8.1.什么是存储过程

MySQL中的存储过程（Stored Procedure）是一段为了完成特定功能的SQL语句集，这些语句经过编译后存储在数据库中。存储过程可以看作是一种数据库内置的程序，它允许用户通过指定的名称和参数（如果需要）来调用执行。存储过程的思想是数据库SQL语言层面的代码封装与重用，通过存储过程，可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，从而提高数据处理的效率。

MySQL从5.0版本开始支持存储过程，使得数据库操作更加灵活和强大。存储过程可以包含复杂的逻辑判断、循环控制等编程元素，实现复杂的数据处理任务。

8.2.存储过程操作

1. 创建存储过程

创建存储过程的基本语法如下：

DELIMITER //  
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])  
BEGIN  
    -- 存储过程体，包含SQL语句  
END //  
DELIMITER ;
  • DELIMITER用于更改命令结束符，以便在存储过程中使用BEGIN ... END语句。
  • CREATE PROCEDURE用于创建新的存储过程。
  • 存储过程名后跟参数列表，参数可以是输入（IN）、输出（OUT）或输入输出（INOUT）类型。
  • 存储过程体包含要执行的SQL语句。

2. 调用存储过程

调用存储过程使用CALL语句，并指定存储过程名和参数（如果需要）：

CALL 存储过程名([参数1, 参数2, ...]);

3. 查看存储过程

查看存储过程的创建语句可以使用SHOW CREATE PROCEDURE语句：

SHOW CREATE PROCEDURE 存储过程名;

此外，还可以通过查询information_schema.ROUTINES系统表来获取存储过程的详细信息。

4. 删除存储过程

删除存储过程使用DROP PROCEDURE语句：

DROP PROCEDURE IF EXISTS 存储过程名;

8.3.存储过程的优缺点

优点
  1. 提高性能：存储过程在数据库服务器上执行，减少了网络传输的数据量，从而提高了应用程序的性能。
  2. 模块化：存储过程将复杂的SQL逻辑封装在一个独立的单元中，便于维护和重用。
  3. 安全性：通过限制对存储过程的访问权限，可以保护数据库的安全。
  4. 减少网络流量：由于存储过程在数据库服务器上执行，因此减少了应用程序和数据库服务器之间的网络通讯开销。
  5. 支持复杂逻辑：存储过程可以包含复杂的逻辑判断、循环控制等编程元素，实现复杂的数据处理任务。
缺点
  1. 移植性差：不同数据库的存储过程语法可能不同，因此编写的存储过程可能无法在不同的数据库之间移植。
  2. 调试困难：存储过程的调试通常比应用程序代码更加困难，因为存储过程在数据库服务器上执行，难以直接跟踪和调试。
  3. 开发和维护成本高：编写和维护存储过程需要专业的数据库知识和技能，这可能会增加开发和维护的成本。
  4. 内存使用增加：如果使用了大量的存储过程，每个连接的内存使用量可能会显著增加。
  5. 过度使用可能导致性能问题：如果在存储过程中过度使用复杂的逻辑操作，可能会增加CPU的使用率，从而影响数据库的性能。

9.MySQL游标

9.1.游标是什么

MySQL游标（Cursor）是一种数据库查询机制，它允许用户逐行遍历查询结果集，并对每一行数据进行处理。游标充当了指针的作用，在结果集中一次只指向一行数据，通过移动游标可以访问结果集中的每一行。游标通常用于处理复杂的查询逻辑，特别是在需要对结果集中的每一行数据进行单独处理时非常有用。

9.2.游标的类型

MySQL游标主要根据其特性和行为进行分类，虽然MySQL官方文档中没有明确列出具体的游标类型名称，但根据通用数据库游标的概念，可以将其分为以下几类（这些类型可能在不同数据库系统中有所不同，但概念相通）：

  1. 静态游标（Static Cursor）：
    • 静态游标在打开时，会将结果集的快照存储在内存中或临时表中。之后，即使基础数据发生变化，游标显示的结果也不会改变。
  2. 动态游标（Dynamic Cursor）：
    • 动态游标在打开时，不存储结果集的快照。当游标移动时，它会实时反映基础数据的变化。
  3. 只进游标（Forward-Only Cursor）：
    • 只进游标只能向前遍历结果集，不能回退。一旦读取了某一行，就不能再回到该行。
  4. 键集驱动游标（Keyset-Driven Cursor）：
    • 键集驱动游标结合了静态游标和动态游标的特性。它在打开时存储结果集中每行的唯一标识符（如主键），之后通过这些标识符来检索数据。这允许游标在一定程度上反映基础数据的变化，但仅限于已检索的行。

需要注意的是，MySQL中具体的游标类型实现可能依赖于其版本和配置，以及是否使用了存储过程或函数等高级特性。

9.3.游标的工作原理

MySQL游标的工作原理主要包括以下几个步骤：

  1. 声明游标：
    • 使用DECLARE CURSOR语句声明一个游标，并指定要从哪个查询结果集中检索数据。
  2. 打开游标：
    • 使用OPEN CURSOR语句打开游标，此时游标指向结果集的第一行之前的位置（即“before first”位置）。
  3. 获取数据：
    • 使用FETCH语句从游标中获取数据。每次执行FETCH语句时，游标会向前移动到下一行，并将当前行的数据返回给指定的变量。
  4. 处理数据：
    • 在获取到游标中的数据后，可以对这些数据进行处理，如更新、删除或计算等。
  5. 关闭游标：
    • 使用CLOSE CURSOR语句关闭游标，释放与游标关联的资源。

9.4.游标的使用

在MySQL中，游标通常与存储过程或函数一起使用，以处理复杂的查询逻辑。以下是游标使用的基本步骤：

  1. 声明变量：
    • 在声明游标之前，需要声明用于存储游标数据的变量。
  2. 声明游标：
    • 使用DECLARE CURSOR语句声明游标，并指定查询语句。
  3. 声明异常处理器（可选）：
    • 为了处理游标遍历过程中可能出现的异常（如“no more rows”错误），可以声明一个异常处理器。
  4. 打开游标：
    • 使用OPEN CURSOR语句打开游标。
  5. 遍历结果集：
    • 使用循环结构（如LOOP、REPEAT或WHILE）和FETCH语句遍历结果集。
  6. 处理数据：
    • 在循环体内，对获取到的数据进行处理。
  7. 关闭游标：
    • 遍历完成后，使用CLOSE CURSOR语句关闭游标。

9.5.游标的优缺点

优点：

  1. 逐行处理：游标允许用户逐行处理查询结果集，这在处理大型数据集或需要对每行数据进行复杂处理时非常有用。
  2. 灵活性：通过游标，可以编写复杂的逻辑来处理数据，如条件判断、循环控制等。
  3. 事务支持：游标可以与事务一起使用，实现数据的回滚和提交，保证数据的一致性。

缺点：

  1. 性能开销：游标操作需要逐行处理数据，这可能会增加数据库的负载和响应时间。特别是在处理大量数据时，性能问题尤为突出。
  2. 资源占用：游标会占用数据库服务器的内存和CPU资源，特别是在打开多个游标时。
  3. 并发问题：游标在处理数据时可能会锁定相关的数据行或表，从而影响其他用户的并发访问。
  4. 维护难度：游标的使用增加了代码的复杂性和维护难度，特别是在处理复杂的逻辑时。

10.MySQL性能优化

进行MySQL调优是一个综合性的过程，涉及多个方面的优化措施。以下是一些关键的步骤和策略：

10.1.硬件层面优化

  1. 内存：确保MySQL服务器有足够的内存来缓存数据和索引。MySQL使用缓冲区（如InnoDB缓冲池）来缓存数据和索引文件，因此增加RAM可以提高读写性能。
  2. 存储设备：使用快速的存储设备，如SSD（固态硬盘），可以显著提高I/O吞吐量，减少延迟。
  3. RAID配置：使用RAID技术将数据分布在多个磁盘上，以提高磁盘读写性能和可靠性。RAID 0提供更快的读写性能，而RAID 10提供更高的可靠性和更好的读写性能。
  4. 网络：如果MySQL服务器与应用程序服务器分离，确保网络连接快速且稳定，以减少数据传输的延迟。

10.2.配置参数优化

  1. innodb_buffer_pool_size：调整InnoDB缓冲池的大小，通常建议设置为服务器总内存的70%-80%，以容纳尽可能多的索引和表数据。
  2. key_buffer_size：对于使用MyISAM表的系统，调整键缓冲区的大小，以缓存常用数据块。
  3. max_connections：根据服务器硬件和网络连接数量调整最大连接数，避免过多或过少的连接导致性能问题。
  4. query_cache_size：对于读密集型应用，启用查询缓存并调整其大小可以提高性能。但需注意，对于更新频繁的数据库，查询缓存可能会成为性能瓶颈。
  5. tmp_table_size 和 max_heap_table_size：调整内存中临时表的大小，以避免频繁创建和删除磁盘上的临时表。

10.3.查询优化

  1. 使用索引：在经常查询的列上创建索引，并确保查询时有效利用了索引。使用EXPLAIN语句分析查询计划，了解MySQL如何执行查询。
  2. 优化SELECT语句：避免使用SELECT *，只选择必要的列。减少查询的列数和行数可以显著提高性能。
  3. 使用JOIN代替子查询：在可能的情况下，使用JOIN代替子查询可以提高查询效率。
  4. 避免全表扫描：通过索引优化、WHERE子句优化等方式减少全表扫描的次数。
  5. 优化GROUP BY和DISTINCT：在GROUP BY和DISTINCT操作前使用WHERE子句进行过滤，以减少需要处理的数据量。

10.4.数据库结构优化

  1. 垂直分割：将表中的列分离到不同的表中，以减少表的宽度和提高查询效率。
  2. 水平分割（分区）：根据一定的规则（如日期、地区等）将表分割成多个较小的表或分区，以提高查询效率和管理便利性。

10.5.使用缓存

  1. 应用级缓存：使用Memcached、Redis等内存缓存系统来减少对MySQL的直接访问，特别是在读密集型应用中。
  2. 查询缓存：虽然MySQL自带的查询缓存功能在更新频繁的数据库中可能效果不佳，但在读密集型且更新不频繁的场景下仍有一定的应用价值。

10.6.定期维护

  1. 分析并优化索引：定期使用ANALYZE TABLE命令更新表的统计信息，以便MySQL优化器能够做出更好的查询计划选择。
  2. 优化表：使用OPTIMIZE TABLE命令重建表和索引的物理存储，减少碎片并提高性能。
  3. 监控慢查询：开启慢查询日志，并定期分析慢查询日志中的查询语句，找出并优化性能瓶颈。
  4. 备份与恢复：定期备份数据库，并制定合理的恢复计划，以确保数据安全性和业务连续性。

11.数据库建模

11.1.三大范式

数据库设计的三大范式（Normalization）是关系型数据库设计的一种标准化过程，旨在消除冗余数据，提高数据存储的效率和数据查询的性能。这三大范式分别是：

  1. 第一范式（1NF）：
    • 定义：确保每一列具有原子性，即每一列的数据都是不可分割的。
    • 目的：防止表中出现重复列，确保每一列都是最小单位的数据，不可再分。
    • 示例：在学生表中，姓名（name）字段应为一个整体，不应再拆分为姓（first name）和名（last name）两个字段（除非业务需求如此）。
  2. 第二范式（2NF）：
    • 定义：确保表格中的每一行数据只与该表格的主键有关系，即非主键列必须完全依赖于主键。
    • 目的：在1NF的基础上，进一步消除部分依赖，确保表中的每一行数据都能被主键唯一标识。
    • 示例：在订单表中，客户信息（如客户姓名、地址等）不应直接存储在订单表中，而应通过客户ID（主键）与客户表建立关联。
  3. 第三范式（3NF）：
    • 定义：确保非主键列之间没有传递依赖关系，即不存在一个非主键列依赖于另一个非主键列。
    • 目的：在2NF的基础上，进一步消除传递依赖，确保数据表中的每一列数据都只与主键直接相关。
    • 示例：在员工表中，不应存储关于部门的其他信息（如部门主管姓名），而应通过部门ID与部门表建立关联。

11.2.数据字典

定义：数据字典是指对数据的数据项、数据结构、数据流、数据存储、处理逻辑等进行定义和描述的集合。其目的是对数据流图中的各个元素作出详细的说明，是描述数据的信息集合。

作用：

  • 提供数据定义：为数据库中的每个数据项提供详细的定义和描述。
  • 促进数据理解：帮助开发人员、数据库管理员等更好地理解数据库的结构和数据含义。
  • 支持数据维护：在数据库设计、开发和维护过程中，提供必要的数据参考和依据。

组成：数据字典通常包括数据项、数据结构、数据流、数据存储、处理逻辑等元素的定义和描述。

11.3.PowderDesigner建模工具

简介：PowerDesigner是Sybase公司（现已被SAP收购）推出的一款强大的数据库建模工具，广泛应用于关系型数据库的设计和开发中。

主要功能：

  • 数据库建模：支持概念数据模型（CDM）、物理数据模型（PDM）等多种模型的设计，帮助用户从抽象到具体地构建数据库结构。
  • 面向对象建模：除了数据库建模外，还支持UML（统一建模语言）等面向对象建模方法，便于进行系统分析和设计。
  • 反向工程：能够从现有的数据库或SQL脚本中生成数据模型，便于用户理解和分析现有系统的数据库结构。
  • 模型导出：支持将设计好的数据模型导出为SQL脚本或其他格式的文件，便于在数据库管理系统中执行和部署。

使用场景：

  • 数据库设计：在数据库设计阶段，使用PowerDesigner可以高效地构建数据库结构，减少设计错误和冗余。
  • 系统分析：在系统分析阶段，使用PowerDesigner的UML建模功能可以帮助用户更好地理解系统需求和业务流程。
  • 数据库维护：在数据库维护阶段，使用PowerDesigner的反向工程功能可以方便地分析和修改现有数据库的结构。
posted @   tubby233  阅读(3)  评论(0)  编辑  收藏  举报
相关博文：
· mybatis---回顾jdbc
· Java常用类
· mysql经历
· 三天吃透MySQL面试八股文
· MySQL
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· 单线程的Redis速度为什么快？
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
点击右上角即可分享
微信分享提示
AI IDE Trae