mysql入门--06数据控制语言

原创 于 2025-02-11 09:50:46 发布 · 1k 阅读 · 20 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

DCL:数据控制语言

COMMIT与ROLLBACK

  • COMMIT 命令用于将事务中的所有操作永久保存到数据库中。一旦执行 COMMIT，事务就会被提交，所有的更改将被应用到数据库中，且不可逆转。
  • ROLLBACK 命令用于取消事务中的所有操作，将数据库恢复到事务开始之前的状态。执行 ROLLBACK 会撤销事务中的所有更改，并且这些更改不会被保存到数据库中。
  • 在使用事务时，通常会先开始一个事务（BEGIN 或 START TRANSACTION），然后执行一系列的数据库操作，最后根据操作的结果决定是提交事务（COMMIT）还是回滚事务（ROLLBACK）。这种操作方式可以确保数据库的一致性和完整性。

约束

数据完整性

  • 实体完整性：实体完整性是指确保每一行数据都有一个唯一的标识符，通常是通过主键来实现。在MySQL中，可以使用主键约束来实现实体完整性，确保表中每一行都有一个唯一的标识符。

  • 域完整性：域完整性是指确保数据的准确性和有效性，数据应该符合定义的域（数据类型、长度等）。在MySQL中，可以使用数据类型、长度限制、枚举等约束来实现域完整性。

  • 参照完整性：参照完整性是指确保表之间的关联关系的有效性，通常通过外键约束来实现。外键约束可以确保在一个表中的外键值必须在另一个表的主键中存在，从而保持数据的一致性。

  • 用户定义的完整性：用户定义的完整性是指依据特定业务规则定义的完整性约束，这些约束不属于上述三种完整性的范畴。在MySQL中，可以使用触发器或存储过程来实现用户定义的完整性约束。

非空约束

  • 在MySQL中，非空约束（NOT NULL constraint）用于确保表中的列不接受NULL值。
  • 这意味着当向该列插入数据时，该列的值不能为NULL，必须有一个有效的值。
  • 要在MySQL中添加非空约束，可以在创建表时或者修改表结构时使用。
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
  • 修改表结构添加非空约束
    • 如果要将现有列设置为非空，可以使用ALTER TABLE语句：
ALTER TABLE users
MODIFY COLUMN username VARCHAR(50) NOT NULL;

唯一性约束

  • 在MySQL中，可以通过唯一性约束（Unique Constraint）来确保表中的列中的值是唯一的。
  • 唯一性约束可以应用于一列或多列，用于防止表中出现重复的数据。
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);
  • 如果要将唯一性约束应用于多列，可以在 UNIQUE 关键字后列出这些列名。
ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

主键约束

  • 主键约束用于唯一标识表中的每一行数据。
  • 主键约束确保表中的每一行具有唯一的标识符，且不允许为空值。
  • 主键约束可以由一个或多个列组成，这些列的值必须唯一。
  • 通常情况下，主键约束会自动创建一个唯一的索引，以加快数据检索速度。
  • 表中只能有一个主键约束。
  • 主键列的值必须唯一，且不允许为空。
  • 主键列的数据类型通常选择整数类型，以确保唯一性和效率。
  • 主键约束可以与自增长属性一起使用，以确保每行数据都有一个唯一的标识符。
  • 使用整数类型的主键时可以使用AUTO_INCREMENT来修饰使得主键自增长
-- 在创建表时定义主键约束
CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

-- 在已有表中添加主键约束：
ALTER TABLE table_name
ADD PRIMARY KEY (column1);

外键约束

  • 在 MySQL 中，外键约束是用来确保数据完整性的重要机制之一。
  • 通过外键约束，可以建立表与表之间的关联关系，确保引用表中的数据符合主表中的数据。
  • 满足以下条件
    • 两个表之间必须有关联字段，通常是一个表中的字段与另一个表中的主键字段相关联。
    • 外键字段的数据类型和长度必须与引用字段的数据类型和长度相匹配。
    • 外键字段的值必须存在于引用表的主键字段中，如果引用表中没有对应的值，则无法插入或更新外键字段的值。
    • 外键约束可以指定级联操作，例如级联更新或级联删除，这样当主表中的数据发生变化时，相关的外键表中的数据也会相应地进行更新或删除。
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

检查约束与默认值约束

  • NOT NULL 约束：
    • 确保列中的数据不为空。
CREATE TABLE table_name (
    column1 INT NOT NULL,
    column2 VARCHAR(50) NOT NULL
);
  • UNIQUE 约束：
    • 确保列中的所有数值是唯一的。
CREATE TABLE table_name (
    column1 INT UNIQUE,
    column2 VARCHAR(50) UNIQUE
);
  • PRIMARY KEY 约束：
    • 唯一标识表中的每一行，并确保主键列中的值不为空。
CREATE TABLE table_name (
    column1 INT PRIMARY KEY,
    column2 VARCHAR(50)
);
  • FOREIGN KEY 约束：
    • 用于关联两个表中的数据，确保一个表中的外键值存在于另一个表的主键中。
CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE table2 (
    id INT PRIMARY KEY,
    table1_id INT,
    FOREIGN KEY (table1_id) REFERENCES table1(id)
);
  • CHECK 约束：
    • 定义列中的值必须满足的条件。
CREATE TABLE table_name (
    column1 INT,
    column2 VARCHAR(50),
    CHECK (column1 > 0)
);
  • DEFAULT 约束：
    • 为列指定默认值，当插入新行时，如果未提供该列的值，则使用默认值。
CREATE TABLE table_name (
    column1 INT DEFAULT 0,
    column2 VARCHAR(50) DEFAULT 'default_value'
);

视图

  • MySQL视图是一个虚拟的表，它是基于一个查询的结果集而创建的。
  • 视图中SELECT语句中涉及到的表，称为基表
  • 针对视图做DML操作，会影响到相应基表的数据
  • 视图本身的删除，不会导致基表中数据的删除
  • 视图本身不包含数据，它只是一个根据特定查询定义的结果集的可视化表示。
  • 通过使用视图，用户可以以一种更简洁、更易于理解的方式访问或操作数据。
  • 视图可以看作是存储起来的查询语句 
-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW IF EXISTS view_name;

-- 查看当前视图定义
SHOW CREATE VIEW view_name;

-- 重新定义视图

CREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2
FROM new_table_name
WHERE new_condition;

存储过程与存储函数

存储过程（Stored Procedure）：

  • 功能：
    • 存储过程是一组SQL语句的集合，可以执行一系列操作，如检索、更新、删除数据等。
    • 存储过程可以包含流程控制语句（如条件语句、循环语句等）。
    • 存储过程可以接受输入参数和返回多个结果集。
  • 调用：
    • 存储过程通过CALL语句来调用。
    • 可以在SQL语句中直接调用存储过程。
  • 返回值：
    • 存储过程可以返回零个或多个结果集，但不能返回单个值。
  • 使用场景：
    • 适合执行一系列复杂的操作，如数据处理、业务逻辑等。
    • 可以提高性能，减少网络通信开销。
-- 创建存储过程：
DELIMITER //
CREATE PROCEDURE GetEmployee(IN id INT)
BEGIN
    SELECT * FROM employees WHERE employee_id = id;
END //
DELIMITER ;

-- 调用存储过程：
CALL GetEmployee(1001);

-- 带参数的存储过程：
DELIMITER //
CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
    UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END //
DELIMITER ;

存储函数（Stored Function）：

  • 功能：
    • 存储函数是一个独立的代码块，可接受输入参数并返回单个值。
    • 存储函数可以被其他SQL语句调用，类似于内置函数。
  • 调用：
    • 存储函数可以直接在SELECT语句中调用，也可以在其他SQL语句中使用。
  • 返回值：
    • 存储函数必须返回一个单一的值。
  • 使用场景：
    • 适合用于计算、数据转换等需要返回单个值的场景。
    • 可以在SELECT语句中方便地调用。
-- 创建存储函数：
DELIMITER //
CREATE FUNCTION CalcBonus(salary DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE bonus DECIMAL(10,2);
    IF salary > 50000 THEN
        SET bonus = salary * 0.1;
    ELSE
        SET bonus = salary * 0.05;
    END IF;
    RETURN bonus;
END //
DELIMITER ;

-- 调用存储函数：
SELECT employee_id, salary, CalcBonus(salary) AS bonus FROM employees;

变量、流量控制与游标

变量

  1. 用户变量
    用户变量是在 MySQL 会话中定义的变量，通常以@符号开头。
    可以使用SET语句来赋值给用户变量，如SET @var_name = 10;。
    用户变量可以用于存储中间结果、控制流程等。
    示例：
-- 定义并使用用户变量
SET @total_sales = 0;
SELECT @total_sales := @total_sales + sales_amount AS cumulative_sales
FROM sales_data;
  1. 系统变量
    系统变量是 MySQL 服务器的配置选项，可以通过SET GLOBAL和SET SESSION来动态设置。
    系统变量通常用于配置服务器的行为，如max_connections、innodb_buffer_pool_size等。
    示例：
-- 设置全局和会话级别的系统变量
SET GLOBAL max_connections = 1000;
SET SESSION innodb_buffer_pool_size = 2G;

流量控制

  1. 流量控制基础
    流量控制是指管理数据库系统中的数据访问和处理速率，以避免资源耗尽或性能下降。
    可以通过限制并发连接数、优化查询、使用索引等方式进行流量控制。
  2. 流量控制工具
    MySQL 提供了一些工具和技术来进行流量控制，如连接池、查询缓存、查询优化器等。
    通过合理配置这些工具，可以有效控制数据库系统的负载，提高性能。

游标

  1. 游标概述
    游标是用于在 SQL 查询中处理结果集的机制，允许逐行处理查询结果。
    游标通常用于存储过程或函数中，允许逐行处理数据并执行相关操作。
  2. 游标类型
    MySQL 支持显式游标，允许程序员在存储过程或函数中使用游标来处理结果集。
    游标可以根据需求进行打开、关闭、滚动等操作，提供了更灵活的数据处理方式。
    示例：
-- 创建游标并逐行处理数据
DECLARE cur CURSOR FOR
SELECT product_name, price FROM products;
OPEN cur;
FETCH cur INTO @product_name, @price;
WHILE @@FETCH_STATUS = 0 DO
    -- 处理数据
    ...
    FETCH cur INTO @product_name, @price;
END WHILE;
CLOSE cur;

触发器

  • MySQL触发器是与表相关联的数据库对象，当在表上执行特定的操作时，触发器会自动触发。
  • 触发器可以在INSERT、UPDATE 或 DELETE 语句执行前或执行后执行相应的动作。

MySQL触发器类型：

  • BEFORE INSERT：在插入行之前触发。
  • AFTER INSERT：在插入行之后触发。
  • BEFORE UPDATE：在更新行之前触发。
  • AFTER UPDATE：在更新行之后触发。
  • BEFORE DELETE：在删除行之前触发。
  • AFTER DELETE：在删除行之后触发。

创建MySQL触发器的语法：

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

示例：
假设我们有一个名为 orders 的表，包含 order_id 和 total_amount 字段。我们想要创建一个触发器，在每次插入新订单时，自动更新订单总金额的字段 total_amount。

创建一个BEFORE INSERT触发器：

DELIMITER //
CREATE TRIGGER update_total_amount
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.total_amount = NEW.total_amount + 10; -- 假设每个订单都增加10
END;
//
DELIMITER ;

在这个示例中，每次插入新的订单时，触发器会将订单的 total_amount 字段增加10。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值