深入解析MySQL约束:数据完整性的守护者

2023-12-14 02:13:39

在这篇文章中,我将带大家深入了解MySQL中的约束机制,它是维护数据库数据完整性、有效性和正确性的重要工具。

1. 约束的种类与应用

  • 非空约束 (NOT NULL): 确保列中无任何null值,维护数据的完整性。
  • 唯一约束 (UNIQUE): 保证列中每项数据的独一无二,避免重复。
  • 主键约束 (PRIMARY KEY): 每张表的核心,确保每行数据的唯一性。
  • 外键约束 (FOREIGN KEY): 两个表间的桥梁,保障数据的一致性和关系的完整性。

2. 约束的详细操作

每种约束都有其特定的添加和删除语法,以下是具体示例:

非空约束 (NOT NULL)
-- 创建表时添加非空约束
CREATE TABLE Employees (
   ID INT NOT NULL,
   Name VARCHAR(100) NOT NULL,
   Position VARCHAR(100)
); 

-- 已有表中添加非空约束
ALTER TABLE Employees MODIFY Position VARCHAR(100) NOT NULL;

-- 删除非空约束
ALTER TABLE Employees MODIFY Position VARCHAR(100);
唯一约束 (UNIQUE)
-- 创建表时添加唯一约束
CREATE TABLE Products (
   ProductID INT UNIQUE,
   ProductName VARCHAR(100)
); 

-- 已有表中添加唯一约束
ALTER TABLE Products ADD UNIQUE (ProductName);

-- 删除唯一约束
ALTER TABLE Products DROP INDEX ProductName;
主键约束 (PRIMARY KEY)
-- 创建表时添加主键约束
CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
   CustomerName VARCHAR(100)
); 

-- 已有表中添加主键约束
ALTER TABLE Customers ADD PRIMARY KEY (CustomerID);

-- 删除主键约束
ALTER TABLE Customers DROP PRIMARY KEY;
外键约束 (FOREIGN KEY)
-- 创建两个表,并设置外键约束
CREATE TABLE Departments (
   DepartmentID INT PRIMARY KEY,
   DepartmentName VARCHAR(100)
);

CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY,
   Name VARCHAR(100),
   DepartmentID INT,
   FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

3. 多表查询的艺术

掌握内连接、外连接以及子查询的技巧,能让你在数据海洋中自如航行。

内连接查询
-- 显示内连接
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
外连接查询
-- 左外连接
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- 右外连接
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

4. 事务:数据库的安全网

事务是保证数据库完整性的关键,MySQL默认的自动提交特性意味着每个SQL语句都是一个独立的事务。

实际案例:银行转账

想象一下,你要通过银行应用从你的账户向朋友的账户转账100元。这个操作包含两个步骤:从你的账户扣除100元,然后在朋友的账户中增加100元。这两个操作必须同时完成或同时不完成,以确保资金的正确移动。这就是事务的完美示例。

START TRANSACTION;

-- 从你的账户扣除100元
UPDATE Accounts SET balance = balance - 100 WHERE account_id = YOUR_ACCOUNT_ID;

-- 在朋

友的账户增加100UPDATE Accounts SET balance = balance + 100 WHERE account_id = FRIENDS_ACCOUNT_ID;

-- 如果上述两个操作都成功,则提交事务
COMMIT;

-- 如果任何一个操作失败,则回滚所有操作
ROLLBACK;

这个例子演示了事务的原子性和一致性。如果中途发生错误,比如网络问题或系统故障,事务将回滚到初始状态,保证账户金额的一致性和正确性。

MySQL事务的自动提交特性:

  • 查询默认提交模式:SELECT @@autocommit;
  • 修改提交模式:SET @@autocommit = 0;

文章来源:https://blog.csdn.net/long1_1/article/details/134908316
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。