MySQL入门到精通—高级功能篇

时间：2024-06-10

索引（Index）、视图（Views）的创建与管理、存储过程（Stored Procedure）和函数（Function）、触发器（Triggers）、事务管理（Transactions）、锁机制（Locks）、外键与数据完整性约束、全文搜索（Full-Text Search）。

1. 索引创建与使用

定义

索引是用于加速数据检索的数据结构，通常以B树或哈希表的形式实现,帮助MySQL高效获取数据的数据结构。

特点

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接,实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间
全文检索字段进行搜索优化

原则

索引不是越多越好，小数据量的表不需要加索引
不要对经常变动的数据增加索引
索引一般加在经常要查询的列上

优化

选择合适的列：选择查询频繁且选择性高的列。
避免过多索引：过多的索引会影响写性能，应该权衡查询和写入性能。
使用覆盖索引：尽量使查询可以仅通过索引获取所需数据，而无需访问表。
监控和调整：定期监控查询性能，根据需要添加或删除索引。
通过合理使用索引，可以显著提高数据库的查询性能，但也需要注意索引的维护成本和潜在的负面影响。

查询优化

SELECT * FROM users WHERE name ='Alice';

如果 name 列上有索引，查询速度会更快。

排序优化

SELECT * FROM users ORDERBY name;

如果 name 列上有索引，排序速度会更快。

多列查询优化

SELECT * FROM users WHERE name ='Alice'AND email ='[email protected]';

如果 name 和 email 列上有复合索引，查询速度会更快。

使用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE name ='Alice';

输出会显示查询是否使用了索引以及使用了哪个索引。

类型
单列索引：基于单列创建的索引。
多列索引：基于多列创建的复合索引。
空间索引：用于处理地理数据的索引。

单列索引CREATE INDEX index_name ON table_name (column_name);示例：CREATE INDEX idx_user_name ON users (name);多列索引CREATE INDEX index_name ON table_name (column1, column2);示例：CREATE INDEX idx_user_name_email ON users (name, email);空间索引CREATE SPATIAL INDEX index_name ON table_name (spatial_column);示例：CREATE SPATIAL INDEX idx_spatial_location ON locations (coordinates);查看索引SHOW INDEX FROM table_name;示例：SHOW INDEX FROM users;删除索引DROP INDEX index_name ON table_name;示例：DROP INDEX idx_user_name ON users;重新构建索引ALTER TABLE table_name DROP INDEX index_name, ADD INDEX index_name (column_name);示例：ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (name);

主键索引（Primary Key Index）

每个表只能有一个主键索引
主键索引是唯一的，并且不允许为空值
自动创建在声明主键时

唯一索引（Unique Index）

确保索引列的值是唯一的。
可以有多个唯一索引。
允许一个 NULL 值。

普通索引（Normal Index）

仅用于加速数据访问，没有唯一性约束。
可以在表的任何列上创建。

复合索引（Composite Index）

基于多个列创建的索引。
加速多列组合查询。

全文索引（Full-text Index）

用于加速对文本数据的搜索。
常用于搜索引擎或需要全文检索的应用。

哈希索引（Hash Index）

基于哈希表实现的索引。
适用于等值查询，但不适合范围查询。

添加索引的优点

加速查询：索引可以显著提高 SELECT 语句的查询速度。
保证唯一性：唯一索引可以确保数据的唯一性。
加速排序和分组：ORDER BY 和 GROUP BY 操作可以通过索引加速。
提高联接性能：在联接操作中，可以快速定位关联表中的数据。

添加索引的缺点

占用空间：索引需要额外的存储空间，尤其是对于大型表。
降低写性能：INSERT、UPDATE和DELETE操作需要维护索引。
复杂性增加：管理和维护索引需要额外的工作。

索引的使用场景

查询频繁的列：对经常出现在 WHERE 子句中的列创建索引。
联接列：对经常用于表联接的列创建索引。
排序和分组列：对经常用于 ORDER BY 和 GROUP BY 的列创建索引。
唯一性约束：对需要唯一性的列创建唯一索引。

创建与删除索引

-- 创建某个索引

-- 删除某个索引

-- 测试某个索引

2. 视图（Views）的创建与管理

定义

视图是基于 SQL 查询的虚拟表，可以简化复杂查询、增强数据安全性和提高数据管理的灵活性。

视图不存储数据，只存储查询逻辑。

优点

简化复杂查询：通过视图，可以将复杂的 SQL 查询封装成一个简单的查询。
增强安全性：通过视图，可以限制用户只能访问特定的数据。
数据抽象：视图提供了对底层表的抽象，使得数据库结构可以随时间变化而不会影响用户的查询。

创建语法

CREATE VIEW view_name ASSELECT column1, column2, ...FROM table_nameWHERE condition;

示例：

假设有一个 users 表，包含以下字段：id、name、email、status。

创建一个视图，选择所有活跃用户的信息：

CREATE VIEW active_users ASSELECT id, name, emailFROM usersWHERE status = 'active';

通过视图查询活跃用户：

SELECT * FROM active_users;

视图可以使用 CREATE OR REPLACE VIEW 语句进行更新。

CREATE OR REPLACE VIEW view_name ASSELECT new_column1, new_column2, ...FROM new_table_nameWHERE new_condition;

示例：

更新 active_users 视图，包含 status 字段：

CREATE OR REPLACE VIEW active_users ASSELECT id, name, email, statusFROM usersWHERE status = 'active';

删除视图语法：

DROP VIEW view_name;

示例：

删除 active_users 视图：

DROP VIEW active_users;

复杂视图示例

假设有两个表 orders 和 order_details，创建一个视图显示每个订单的详细信息。

创建视图：

CREATE VIEW order_summary ASSELECT o.order_id, o.user_id, o.order_date, d.product_id, d.quantity, d.price, (d.quantity * d.price) AS total_priceFROM orders oJOIN order_details d ON o.order_id = d.order_id;

查询视图，获取订单摘要信息：

SELECT * FROM order_summary;

视图的权限管理，控制用户对视图的访问权限，可以限制用户只能访问视图而不能直接访问底层表。

授予权限：

GRANT SELECT ON view_name TO 'username'@'hostname';

示例：

GRANT SELECT ON active_users TO 'user'@'localhost';

视图更新的限制，视图的可更新性取决于视图的定义。

以下视图通常不可更新：

包含聚合函数（如 SUM()、AVG()）
包含 DISTINCT
包含 GROUP BY 或 HAVING
包含联接（JOIN）
包含子查询
包含只读字段

要更新视图中的数据，视图必须直接映射到底层表的列，并且视图中不能包含复杂的 SQL 语法。

示例：

假设有一个可更新的视图：

CREATE VIEW user_emails AS
SELECT id, email
FROM users;

可以通过视图更新数据：

UPDATE user_emails SET email = '[email protected]' WHERE id = 1;

视图是用于简化复杂查询、提高安全性和灵活性。

通过视图，用户可以定义并重用复杂查询逻辑，同时保护底层数据表的安全性。

3. 存储过程和函数

定义

存储过程和函数是封装业务逻辑的重要机制，支持参数和返回值。

存储过程和函数是用来封装一组 SQL 语句的程序单元，它们可以在数据库中保存和执行，提供了复用 SQL 逻辑的能力，减少代码重复，提高维护性和性能。

存储过程（Stored Procedure）是一组预编译的 SQL 语句，可以通过调用来执行。存储过程可以接受输入参数，并且可以返回输出参数。

优点

代码复用：存储过程可以在多个应用程序中重复使用。
性能提升：存储过程是预编译的，执行速度更快。
安全性：通过存储过程可以限制直接访问表，增强安全性。
减少网络流量：客户端与服务器之间只需传输存储过程的调用，减少网络流量。

创建存储过程：

CREATE PROCEDURE procedure_name (param_list)BEGIN
    -- 业务逻辑END;

参数类型：

IN：输入参数

OUT：输出参数

INOUT：既是输入参数也是输出参数

示例：

创建一个简单的存储过程，用于获取用户的详细信息：

CREATE PROCEDURE GetUserDetails(IN userId INT, OUT userName VARCHAR(100))BEGIN
    SELECT name INTO userName FROM users WHERE id = userId;END;

调用存储过程：

CALL GetUserDetails(1, @userName);

获取输出参数的值：

SELECT @userName;

创建一个存储过程，插入新用户：

CREATE PROCEDURE AddUser(IN userName VARCHAR(100), IN userEmail VARCHAR(100))BEGIN
    INSERT INTO users (name, email) VALUES (userName, userEmail);END;

调用存储过程：

CALL AddUser('John Doe', '[email protected]');

函数（Function）类似于存储过程，但它有一个返回值，并且只能返回一个值。

函数通常用于计算和返回结果。

优点：

用于计算和返回单一结果。
可以在 SQL 语句中直接调用，比如在 SELECT 语句。

创建函数：

CREATE FUNCTION function_name (param_list) RETURNS datatypeBEGIN
    -- 业务逻辑    RETURN value;END;

示例：

创建一个简单的函数，返回用户的姓名：

CREATE FUNCTION GetUserName(userId INT) RETURNS VARCHAR(100)BEGIN
    DECLARE userName VARCHAR(100);
    SELECT name INTO userName FROM users WHERE id = userId;
    RETURN userName;END;

调用函数：

SELECT GetUserName(1);

更多示例：

创建一个函数，计算两个数的和：

CREATE FUNCTION AddNumbers(a INT, b INT) RETURNS INTBEGIN
    RETURN a + b;END;调用函数：SELECT AddNumbers(10, 20);

案例：使用存储过程进行批量更新

假设有一个 employees 表，我们需要一个存储过程来批量更新员工的薪水。

CREATE PROCEDURE UpdateSalaries(IN percentage DECIMAL(5,2))BEGIN
    UPDATE employees
    SET salary = salary * (1 + percentage / 100);END;

调用存储过程：

CALL UpdateSalaries(5);  -- 将所有员工的薪水增加 5%

案例：使用函数计算订单总价

假设有一个 order_items 表，我们需要一个函数来计算订单的总价。

CREATE FUNCTION GetOrderTotal(orderId INT) RETURNS DECIMAL(10,2)BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_id = orderId;
    RETURN total;END;

调用函数：

SELECT GetOrderTotal(1);  -- 获取订单 ID 为 1 的订单总价

存储过程和函数是 MySQL 中强大的工具，可以帮助开发者简化复杂的业务逻辑、提高性能和安全性。

了解如何创建和使用存储过程和函数是数据库开发和管理的重要技能。

4. 触发器（Triggers）

定义

触发器是在特定事件（如插入、更新、删除）发生时自动执行的存储过程。

触发器（Trigger）是在对表进行插入、更新或删除操作时自动执行的一种特殊存储过程。

它可以用于维护数据的完整性、自动生成日志、实现复杂的业务规则等。

特点

自动执行：触发器是自动触发的，无需显式调用。
事件驱动：基于数据库事件（INSERT、UPDATE、DELETE）触发。

作用范围：作用于表的行级操作。

触发器的类型

BEFORE 触发器：在插入、更新或删除操作之前执行。
AFTER 触发器：在插入、更新或删除操作之后执行。

创建基本语法：

CREATE TRIGGER trigger_nameBEFORE/AFTER INSERT/UPDATE/DELETEON table_name FOR EACH ROWBEGIN
    -- 触发器逻辑END;

示例：

创建一个 BEFORE INSERT 触发器，在插入新用户记录之前设置 created_at 字段的值为当前时间：

CREATE TRIGGER before_insert_usersBEFORE INSERT ON usersFOR EACH ROWBEGIN
    SET NEW.created_at = NOW();END;

创建一个 AFTER UPDATE 触发器，在用户信息更新后记录日志：

CREATE TRIGGER after_update_usersAFTER UPDATE ON usersFOR EACH ROWBEGIN
    INSERT INTO user_logs (user_id, action, action_time)
    VALUES (OLD.id, 'UPDATE', NOW());END;

创建一个 BEFORE DELETE 触发器，在删除用户记录之前记录日志：

CREATE TRIGGER before_delete_usersBEFORE DELETE ON usersFOR EACH ROWBEGIN
    INSERT INTO user_logs (user_id, action, action_time)
    VALUES (OLD.id, 'DELETE', NOW());END;

假设有两个表 products 和 product_logs

我们希望在 products 表中进行任何修改时，都会在 product_logs 表中记录这些修改。

创建表：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT);CREATE TABLE product_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    action VARCHAR(50),
    action_time DATETIME,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    old_stock INT,
    new_stock INT);

创建触发器：

在插入新产品之前记录日志：

CREATE TRIGGER before_insert_productsBEFORE INSERT ON productsFOR EACH ROWBEGIN
    INSERT INTO product_logs (product_id, action, action_time, new_price, new_stock)
    VALUES (NEW.id, 'INSERT', NOW(), NEW.price, NEW.stock);END;

在更新产品之前记录旧值：

CREATE TRIGGER before_update_productsBEFORE UPDATE ON productsFOR EACH ROWBEGIN
    INSERT INTO product_logs (product_id, action, action_time, old_price, new_price, old_stock, new_stock)
    VALUES (OLD.id, 'UPDATE', NOW(), OLD.price, NEW.price, OLD.stock, NEW.stock);END;

在删除产品之前记录日志：

CREATE TRIGGER before_delete_productsBEFORE DELETE ON productsFOR EACH ROWBEGIN
    INSERT INTO product_logs (product_id, action, action_time, old_price, old_stock)
    VALUES (OLD.id, 'DELETE', NOW(), OLD.price, OLD.stock);END;

查看触发器：

SHOW TRIGGERS;

或

SHOW TRIGGERS LIKE 'table_name';

删除触发器：

DROP TRIGGER trigger_name;

示例：

DROP TRIGGER before_insert_users;

触发器是实现数据库自动化操作的强大工具。

它们可以帮助维护数据的一致性和完整性，自动记录数据更改日志，实施复杂的业务规则。

了解和使用触发器可以极大地提高数据库管理和应用程序开发的效率。

5. 事务管理（Transactions）

定义

事务管理是数据库系统中确保数据一致性和完整性的重要机制。

事务是一组要么全部执行成功、要么全部回滚的SQL语句。

通过事务，数据库可以保证在执行过程中即使发生错误，数据仍然保持一致。

特性（ACID）

原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不执行，不会出现部分完成的情况。
一致性（Consistency）：事务执行前后，数据库必须处于一致的状态，保证数据库的完整性。
隔离性（Isolation）：多个事务同时执行时，一个事务的操作不会影响其他事务，事务之间是相互隔离的。
持久性（Durability）：事务一旦提交，其结果将永久保存在数据库中，即使发生系统崩溃。

基本操作

开始事务：START TRANSACTION
提交事务：COMMIT
回滚事务：ROLLBACK

使用示例：

假设有一个 accounts 表，包含以下字段：account_id、balance。

START TRANSACTION;-- 转账操作UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;COMMIT;

在这个示例中，如果两个更新操作都成功，则提交事务。

如果其中一个操作失败，则可以回滚事务，保证数据的一致性。

回滚示例：

START TRANSACTION;-- 转账操作UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- 假设此处出现错误-- UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;-- 发生错误，回滚事务ROLLBACK;

事务的隔离级别定义了一个事务与其他事务隔离的程度。

MySQL 支持四种隔离级别：

读未提交（READ UNCOMMITTED）：最低的隔离级别，事务中的修改即使未提交，其他事务也能看到。
读已提交（READ COMMITTED）：事务中的修改只有在提交后，其他事务才能看到。
可重复读（REPEATABLE READ）：保证在一个事务中多次读取同一数据结果是一致的，即使其他事务进行了更新（MySQL 默认隔离级别）。
可串行化（SERIALIZABLE）：最高的隔离级别，确保事务完全串行化执行，避免并发事务造成的冲突。

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

示例：

设置当前会话的隔离级别为 READ COMMITTED：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在 MySQL 中，InnoDB 存储引擎支持事务，而 MyISAM 存储引擎不支持事务。

为了使用事务管理，确保使用支持事务的存储引擎。

查看表的存储引擎：

SHOW TABLE STATUS LIKE 'table_name';

示例：

SHOW TABLE STATUS LIKE 'accounts';

修改表的存储引擎为 InnoDB：

ALTER TABLE table_name ENGINE = InnoDB;

示例：

ALTER TABLE accounts ENGINE = InnoDB;

假设有两个表 accounts 和 transactions，我们希望在进行转账操作时，记录转账日志。

如果任何一步失败，回滚整个事务。

表结构：

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)) ENGINE=InnoDB;CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    from_account INT,
    to_account INT,
    amount DECIMAL(10, 2),
    transaction_date DATETIME) ENGINE=InnoDB;

存储过程实现转账操作：

CREATE PROCEDURE TransferAmount(
    IN fromAccount INT,
    IN toAccount INT,
    IN amount DECIMAL(10, 2))BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 出现异常时回滚事务        ROLLBACK;
    END;
    START TRANSACTION;
    -- 扣款    UPDATE accounts SET balance = balance - amount WHERE account_id = fromAccount;
    -- 加款    UPDATE accounts SET balance = balance + amount WHERE account_id = toAccount;
    -- 记录交易    INSERT INTO transactions (from_account, to_account, amount, transaction_date)
    VALUES (fromAccount, toAccount, amount, NOW());
    COMMIT;END;

调用存储过程：

CALL TransferAmount(1, 2, 100.00);

事务管理是确保数据库操作安全性和一致性的关键技术。

通过使用事务，可以保证一组操作要么全部成功，要么全部失败，防止部分成功导致的数据不一致问题。

掌握事务的使用和管理，是数据库开发和管理的重要技能。

6. 锁机制（Locks）

锁机制是数据库系统中用于管理并发访问的技术，确保多个事务在并发执行时数据的完整性和一致性。锁的类型、粒度和使用方式对数据库性能和并发控制有重要影响。

锁的类型

共享锁（Shared Lock，S 锁）：允许多个事务读取同一资源，但不能修改该资源。即，一个资源可以被多个事务共享读取，但在共享锁存在期间，其他事务不能修改该资源。
排他锁（Exclusive Lock，X 锁）：只允许一个事务访问该资源，无论是读取还是写入。在排他锁存在期间，其他事务不能读取或修改该资源。

锁的粒度，锁的粒度指锁定资源的大小，包括行级锁、表级锁和页级锁等。

行级锁（Row-Level Lock）：锁定单个行，粒度最小，支持高并发，但开销较大。
表级锁（Table-Level Lock）：锁定整张表，粒度较大，支持并发较低，但开销较小。
页级锁（Page-Level Lock）：锁定数据页，一般为几个连续的数据行，介于行级锁和表级锁之间。

MySQL支持多种存储引擎，不同存储引擎的锁机制有所不同。

常用的存储引擎包括InnoDB和MyISAM。

InnoDB是MySQL中最常用的存储引擎，支持行级锁和表级锁，默认使用行级锁。

行级锁：通过索引条件锁定行。未使用索引的查询会使用表级锁。

锁模式

共享锁（S 锁）：SELECT ... LOCK IN SHARE MODE，用于读取操作。
排他锁（X 锁）：SELECT ... FOR UPDATE，用于写入操作。

示例：

共享锁示例：

START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;
-- 事务提交或回滚
COMMIT;

排他锁示例：

START TRANSACTION;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;
-- 事务提交或回滚
COMMIT;

MyISAM存储引擎仅支持表级锁，不支持事务。

表级锁：

读锁（READ LOCK）：允许多个事务读取表，但不允许写入。
写锁（WRITE LOCK）：只允许一个事务写入表，其他事务不能读写。

读锁示例：

LOCK TABLES accounts READ;-- 执行读取操作UNLOCK TABLES;

写锁示例：

LOCK TABLES accounts WRITE;-- 执行写入操作UNLOCK TABLES;

死锁，死锁是指两个或多个事务相互等待对方释放锁，从而导致无限期等待的现象。

解决死锁的方法：

死锁检测：InnoDB存储引擎支持死锁检测，当检测到死锁时，会回滚其中一个事务以解开死锁。
合理设计事务顺序：避免循环依赖，尽量在同一事务中按相同顺序访问资源。
设置锁等待超时：通过innodb_lock_wait_timeout参数设置锁等待超时时间，避免长时间等待。

示例：

设置锁等待超时时间：

SET innodb_lock_wait_timeout = 50;

锁等待与超时，锁等待是指一个事务在尝试获取锁时，如果锁被其他事务持有，需要等待锁释放。

锁等待时间超过一定阈值后，会发生锁超时。

设置锁等待时间：

SET innodb_lock_wait_timeout = 10; -- 设置锁等待时间为10秒

检测锁等待，使用以下查询查看正在等待锁的事务：

SHOW ENGINE INNODB STATUS;

事务的隔离级别会影响锁的行为。

锁机制是数据库并发控制的重要手段，通过锁机制可以确保数据的一致性和完整性。

理解并合理使用锁的类型、粒度和事务隔离级别，可以显著提高数据库系统的性能和可靠性。

在使用锁机制时，应注意避免死锁，并设置合理的锁等待时间和事务隔离级别。

7. 外键与数据完整性约束

定义

在关系数据库中，外键（Foreign Key）和数据完整性约束是确保数据一致性和完整性的重要机制。

外键用于在表之间建立和维护关系，数据完整性约束用于确保数据库中的数据遵循特定的规则。

外键（Foreign Key）

外键是一种约束，用于在一个表（子表或从表）中建立到另一个表（父表或主表）的链接。

外键约束确保子表中的值必须在父表中存在，从而保持数据的一致性和完整性。

创建外键约束：

CREATE TABLE child_table (
    column_name datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name));

示例：

假设有两个表 customers 和 orders，orders 表中的 customer_id 是 customers 表中的外键。

创建 customers 表：

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100));

创建 orders 表并设置外键：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id));

外键约束可以在以下几种操作上生效：

ON DELETE：当父表中的记录被删除时，子表中的相关记录的行为。
ON UPDATE：当父表中的记录被更新时，子表中的相关记录的行为。

操作类型：

CASCADE：级联操作。对父表记录的删除或更新会在子表中进行相应的删除或更新。
SET NULL：设置为空。对父表记录的删除或更新会在子表中将外键字段设置为 NULL。
RESTRICT：限制操作。对父表记录的删除或更新会被阻止。
NO ACTION：默认行为，与 RESTRICT 类似。

创建 orders 表并设置外键的操作行为：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

数据完整性约束用于确保数据库中的数据符合业务规则。

常见的数据完整性约束包括：

主键约束（PRIMARY KEY）：确保每条记录唯一标识。
唯一约束（UNIQUE）：确保列中的所有值唯一。
非空约束（NOT NULL）：确保列中不能有空值。
默认约束（DEFAULT）：为列设置默认值。
检查约束（CHECK）：确保列中的值符合指定的条件。

主键约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100));

唯一约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) UNIQUE);

非空约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL);

默认约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    hire_date DATE DEFAULT CURRENT_DATE);检查约束：CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT CHECK (age >= 18));

创建 departments 和 employees 表，并设置约束：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) UNIQUE NOT NULL);CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    hire_date DATE DEFAULT CURRENT_DATE,
    age INT CHECK (age >= 18),
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE);

查看外键约束：

SHOW CREATE TABLE table_name;

删除外键约束：

ALTER TABLE child_table DROP FOREIGN KEY foreign_key_name;

添加外键约束：

ALTER TABLE child_table ADD CONSTRAINT foreign_key_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name);

删除 orders 表中的外键：

ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;

添加 orders 表中的外键：

ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

外键和数据完整性约束是确保数据库中数据一致性和完整性的关键机制。

通过设置外键，可以在表之间建立关系并强制参照完整性。

数据完整性约束则用于确保数据符合业务规则。

理解并正确使用这些约束，可以有效维护数据库的可靠性和稳定性。

8. 全文搜索（Full-Text Search）

定义

全文搜索是一种搜索技术，用于在数据库中对文本字段进行复杂的搜索和匹配操作。

MySQL 提供了内置的全文搜索功能，特别是在 InnoDB 和 MyISAM 存储引擎，支持全文索引和全文搜索查询。

特点

快速检索：通过建立全文索引，可以快速地在大文本字段中检索关键词。
灵活查询：支持布尔模式搜索、自然语言模式搜索等多种查询方式。
高级匹配：支持关键词的相关性评分，返回匹配度高的结果。

创建

在 MySQL 中，可以使用 FULLTEXT 关键字来创建全文索引。

基本语法：

CREATE FULLTEXT INDEX index_name ON table_name(column_name1, column_name2, ...);

示例：

假设有一个 articles 表，包含以下字段：id、title、content。

创建 articles 表：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT);

为 title 和 content 字段创建全文索引：

CREATE FULLTEXT INDEX idx_fulltext ON articles(title, content);

MySQL 提供了 MATCH 和 AGAINST 子句用于全文搜索。

基本语法：

SELECT * FROM table_name WHERE MATCH(column_name1, column_name2, ...) AGAINST('search_query' IN NATURAL LANGUAGE MODE);

插入一些示例数据：

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'Full-text search is a technique for searching text-based content.'),
('Introduction to MySQL', 'MySQL is an open-source relational database management system.'),
('Advanced SQL Techniques', 'This article covers advanced SQL techniques including joins, subqueries, and indexes.');

进行全文搜索：

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('Full-Text Search' IN NATURAL LANGUAGE MODE);

搜索模式

自然语言模式：默认模式，按照自然语言处理方式对文本进行搜索，忽略常见词（如 "the", "and" 等）。

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

布尔模式：支持布尔操作符（如 +, -, *, >），可以实现更复杂的查询逻辑。

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('+MySQL -Introduction' IN BOOLEAN MODE);

查询扩展模式：基于初始查询扩展搜索范围，提高查询结果的相关性。

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('Full-Text Search' WITH QUERY EXPANSION);

添加全文索引：

ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name1, column_name2);

删除全文索引：

ALTER TABLE table_name DROP INDEX index_name;

为 content 字段添加全文索引：

ALTER TABLE articles ADD FULLTEXT INDEX idx_content(content);

删除全文索引：

ALTER TABLE articles DROP INDEX idx_content;

欢迎咨询/Welcome to inquire

tel/vx:18842938855
qq:1685522781
email:[email protected]