1.使用 SQL 语句 CREATE TABLE 定义约束的方法
在创建表时,可以通过 CREATE TABLE
语句来定义各种约束,以确保数据的完整性和准确性。以下是一些常见约束的定义方法:
- 主键约束(Primary Key):确保某列或列组合的值唯一,且不允许NULL值。
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
- 非空约束(Not Null):确保某列不接受NULL值。
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
);
- 唯一约束(Unique):确保某列或列组合的值在表中是唯一的。
CREATE TABLE products (
product_id INT PRIMARY KEY,
sku VARCHAR(20) UNIQUE
);
- 外键约束(Foreign Key):确保某列的值必须在另一表的主键列中存在,以保持数据的引用完整性。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
- 检查约束(Check):确保某列的值满足特定条件。
CREATE TABLE inventory (
item_id INT PRIMARY KEY,
quantity INT CHECK (quantity > 0)
);
2. 使用 SQL 语句 ALTER TABLE 增加或删除约束的方法
使用 ALTER TABLE
语句可以在现有表上添加或删除约束。
- 添加约束:
ALTER TABLE users ADD [CONSTRAINT uc_username] UNIQUE (username);
- 删除约束:
ALTER TABLE users DROP CONSTRAINT uc_username;
在SQL中,CONSTRAINT
关键字后面的字段是指你要添加约束的列名。这些列名通常用逗号分隔,表示这个约束适用于多个列。
例如,如果你想在 employees
表的 employee_id
列上添加一个主键约束,你可以这样写:
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
在这个例子中,pk_employee_id
是约束的名字,employee_id
是你要添加约束的列名。
同样地,如果你想在 employees
表的 employee_email
列上添加一个唯一性约束,你可以这样写:
ALTER TABLE employees
ADD CONSTRAINT uq_employee_email UNIQUE (employee_email);
在这个例子中,uq_employee_email
是约束的名字,employee_email
是你要添加约束的列名。
在 ALTER TABLE
语句后面跟着的代码是用来定义约束的。在这个代码块中,你可以定义各种类型的约束,如主键约束、非空约束、唯一性约束、外键约束等。
例如,如果你想在 employees
表的 department_id
列上添加一个外键约束,指向 departments
表的 department_id
列,你可以这样写:
ALTER TABLE employees
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments (department_id);
在这个例子中,fk_employee_department
是约束的名字,department_id
是你要添加约束的列名,REFERENCES departments (department_id)
则是定义外键约束的部分,表示这个列的值必须存在于 departments
表的 department_id
列中。
3. 约束的各种类型
除了上述提到的约束类型,还有其他一些约束类型,例如:
- 默认约束(Default):为列指定一个默认值,当插入记录时如果没有提供该列的值,则会自动使用默认值。
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) DEFAULT 'example@example.com'
);
- 全文索引约束(Full Text Index):用于提高文本数据的搜索效率。
ALTER TABLE articles ADD FULLTEXT(fulltext_search);
4. 使用 SQL 语句 CREATE TRIGGER 创建触发器的方法
触发器是一种特殊类型的存储过程,它会在满足特定条件时自动执行。
在SQL中,创建触发器的关键字包括 CREATE TRIGGER
,它用于定义新的触发器。触发器是一种特殊类型的存储过程,它会在满足特定条件时自动执行。例如,在MySQL中,创建触发器的基本语法如下:
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
-- 触发器逻辑
END$$
DELIMITER ;
在这个语法中:
BEFORE
或AFTER
指定触发器是在事件发生之前还是之后执行。INSERT
、UPDATE
或DELETE
指定触发器响应的数据库操作类型。table_name
是触发器关联的表名。FOR EACH ROW
表示触发器将对每一行受影响的数据执行其逻辑。BEGIN ... END
之间是触发器的逻辑,可以包含SQL语句。
在触发器的逻辑中,可以使用 OLD
和 NEW
关键字来访问触发器操作前后的数据。例如,在 UPDATE
触发器中,OLD.column_name
表示更新前的值,而 NEW.column_name
表示更新后的值。在 DELETE
触发器中,只有 **OLD
关键字**可用,因为它涉及到被删除的行。在 INSERT
触发器中,只有 NEW
关键字可用,因为它涉及到新插入的行。
一些关于不同类型的触发器的例子:
插入触发器
假设我们有一个名为 students
的表和一个名为 enrollments
的表。每当我们在 students
表中插入一个新的学生记录时,我们希望 enrollments
表中也插入一个新的记录。这可以通过创建一个 AFTER INSERT
触发器来实现:
CREATE TRIGGER enrollment_trigger
AFTER INSERT ON students
FOR EACH ROW
BEGIN
INSERT INTO enrollments (student_id, date_enrolled)
VALUES (NEW.student_id, NOW());
END;
更新触发器
假设我们有一个名为 products
的表和一个名为 prices
的表。每当我们在 products
表中更新产品价格时,我们希望 prices
表也更新相应的价格。这可以通过创建一个 AFTER UPDATE
触发器来实现:
CREATE TRIGGER price_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
IF NEW.price > OLD.price THEN
UPDATE prices SET price = NEW.price WHERE product_id = NEW.product_id;
END IF;
END;
删除触发器
假设我们有一个名为 employees
的表和一个名为 salaries
的表。每当我们在 employees
表中删除一个员工记录时,我们希望 salaries
表也删除相应的工资记录。这可以通过创建一个 AFTER DELETE
触发器来实现:
CREATE TRIGGER salary_trigger
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
DELETE FROM salaries WHERE employee_id = OLD.employee_id;
END;
5. 引发触发器的方法
触发器会在满足其定义的条件时自动触发,无需手动调用。例如,当向 orders
表插入新记录时,after_order_insert
触发器会自动执行。
6. 使用 SQL 语句 DROP TRIGGER 删除触发器的方法
使用 DROP TRIGGER
语句可以删除不再需要的触发器:
DROP TR