MySQL创建索引、视图、存储过程、函数、触发器

 

目录

1 构造表结构和数据

1.1 连接到MySQL服务器

1.2 表结构

1.3 示例数据

2 创建索引

2.1 创建索引

2.2 查看索引是否被使用

3 创建视图

4 创建存储过程

5 创建函数

6 创建触发器

1 构造表结构和数据

1.1 连接到MySQL服务器

首先，使用以下命令连接到MySQL服务器：

mysql -u your_username -p -h 127.0.0.1

连接成功后，选择要操作的数据库：

USE your_database;

1.2 表结构

1.customers 表：

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  email VARCHAR(50),
  status VARCHAR(10)
);

2.products 表：

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  price DECIMAL(10,2),
  stock INT
);

3.orders 表：

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  product_id INT,
  quantity INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

1.3 示例数据

1.customers 表示例数据：

INSERT INTO customers (name, email, status) VALUES
  ('John Doe', 'john@example.com', 'active'),
  ('Jane Smith', 'jane@example.com', 'inactive'),
  ('Bob Johnson', 'bob@example.com', 'active');

2.products 表示例数据：

INSERT INTO products (name, price, stock) VALUES
  ('iPhone', 999.99, 10),
  ('Samsung Galaxy', 799.99, 5),
  ('Google Pixel', 699.99, 8);

3.orders 表示例数据：

INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES
  (1, 1, 2, '2023-07-01'),
  (1, 2, 1, '2023-07-02'),
  (2, 3, 3, '2023-07-03');

以上是用于演示的表结构和示例数据。你可以根据这些示例进行索引、视图、存储过程、函数和触发器的创建操作。

2 创建索引

索引是一种提高数据库查询性能的数据结构。下面是创建索引的步骤：

2.1 创建索引

现在，我们可以使用CREATE INDEX语句创建索引。以下是一个示例，创建名为idx_customers_email的索引：

CREATE INDEX idx_customers_email ON customers (email);
alter table customers add index idx_customers_email(email);

这将在customers表的email列上创建一个索引。

2.2 查看索引是否被使用

explain select * from customers where id = 1;
explain select * from customers where name = 'john';
explain select * from customers where email = 'john@example.com';

执行结果中的key列将显示查询使用的索引。如果在key列中看到了索引的名称，表示该索引被查询使用了。

3 创建视图

现在，我们可以使用CREATE VIEW语句创建视图。以下是一个示例，创建名为active_customers的视图，该视图显示所有状态为“active”的客户：

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

这将创建一个名为active_customers的视图，其结果集是从customers表中选择状态为“active”的所有行。

可以把一个视图当作表使用：

SELECT * FROM  active_customers;

4 创建存储过程

存储过程是一组预编译的SQL语句，可以在数据库中进行重复性操作。下面是创建存储过程的步骤：

DELIMITER //
CREATE PROCEDURE get_customer_orders(IN customer_name VARCHAR(50))
BEGIN
    DECLARE customer_id INT;
    DECLARE total_orders INT;
    
    -- 获取客户ID
    SELECT id INTO customer_id FROM customers WHERE name = customer_name;
    
    -- 获取客户订单总数
    SELECT COUNT(*) INTO total_orders FROM orders WHERE customer_id = customer_id;
    
    -- 返回结果
    SELECT CONCAT(customer_name, ' has ', total_orders, ' order(s).') AS result;
END //
DELIMITER ;

在上面的存储过程示例中，我们通过客户姓名获取客户ID，并计算该客户的订单总数。然后，通过查询结果返回一个包含结果消息的语句。

你可以使用如下命令执行该存储过程：

CALL get_customer_orders('John Doe');

执行上述命令后，存储过程将输出类似以下结果的消息：

John Doe has 2 order(s).

5 创建函数

函数是一段可重复使用的SQL代码，接受输入参数并返回值。下面是创建函数的步骤：

DELIMITER //
CREATE FUNCTION calculate_discount(price DECIMAL(10,2)) RETURNS DECIMAL(10,2)
BEGIN
    DECLARE discount DECIMAL(10,2);
    SET discount = price * 0.1; -- 10% discount
    RETURN price - discount;
END//
DELIMITER ;

这将创建一个名为calculate_discount的函数，当调用该函数时，它将接受产品价格作为输入，并返回折扣后的价格。

要调用一个函数，可以使用SELECT语句将函数的返回值作为查询结果进行检索:

SELECT calculate_discount(1000);

 

6 创建触发器

触发器是在数据库中某个特定事件发生时自动执行的代码。下面是创建触发器的步骤：

我们可以使用CREATE TRIGGER语句创建触发器。以下是一个示例，创建名为update_stock的触发器，当orders表插入新记录时，自动更新products表的库存数量：

DELIMITER //
CREATE TRIGGER update_stock AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END//
DELIMITER ;

这将创建一个名为update_stock的触发器，当orders表插入新记录时，触发器将自动执行一条更新语句，减少products表中对应产品的库存数量。

你无需手动调用触发器，它将在满足触发条件时自动执行。在这个例子中，每当在orders表中插入新记录时，触发器会自动减少相应产品的库存数量。

例如，执行以下插入命令：

INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES (1, 1, 3, '2023-07-07');

这将插入一条新的订单记录，并触发触发器。触发器将自动执行更新语句，减少products表中对应产品的库存数量。

你可以通过查询products表来验证触发器是否成功更新了库存数量：

SELECT * FROM products;

触发器将自动更新products表中的库存数量，反映了新的订单操作。