MySQL創(chuàng)建索引、視圖、存儲過程、函數(shù)、觸發(fā)器

?

目錄

1 構造表結構和數(shù)據(jù)

1.1 連接到MySQL服務器

1.2 表結構

1.3 示例數(shù)據(jù)

2 創(chuàng)建索引

2.1 創(chuàng)建索引

2.2 查看索引是否被使用

3 創(chuàng)建視圖

4 創(chuàng)建存儲過程

5 創(chuàng)建函數(shù)

6 創(chuàng)建觸發(fā)器

1 構造表結構和數(shù)據(jù)

1.1 連接到MySQL服務器

首先，使用以下命令連接到MySQL服務器：

mysql -u your_username -p -h 127.0.0.1

連接成功后，選擇要操作的數(shù)據(jù)庫：

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 示例數(shù)據(jù)

1.customers 表示例數(shù)據(jù)：

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 表示例數(shù)據(jù)：

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

3.orders 表示例數(shù)據(jù)：

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');

以上是用于演示的表結構和示例數(shù)據(jù)。你可以根據(jù)這些示例進行索引、視圖、存儲過程、函數(shù)和觸發(fā)器的創(chuàng)建操作。

2 創(chuàng)建索引

索引是一種提高數(shù)據(jù)庫查詢性能的數(shù)據(jù)結構。下面是創(chuàng)建索引的步驟：

2.1 創(chuàng)建索引

現(xiàn)在，我們可以使用CREATE INDEX語句創(chuàng)建索引。以下是一個示例，創(chuàng)建名為idx_customers_email的索引：

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

這將在customers表的email列上創(chuàng)建一個索引。

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';

執(zhí)行結果中的key列將顯示查詢使用的索引。如果在key列中看到了索引的名稱，表示該索引被查詢使用了。

3 創(chuàng)建視圖

現(xiàn)在，我們可以使用CREATE VIEW語句創(chuàng)建視圖。以下是一個示例，創(chuàng)建名為active_customers的視圖，該視圖顯示所有狀態(tài)為“active”的客戶：

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

這將創(chuàng)建一個名為active_customers的視圖，其結果集是從customers表中選擇狀態(tài)為“active”的所有行。

可以把一個視圖當作表使用：

SELECT * FROM  active_customers;

4 創(chuàng)建存儲過程

存儲過程是一組預編譯的SQL語句，可以在數(shù)據(jù)庫中進行重復性操作。下面是創(chuàng)建存儲過程的步驟：

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;
 ? ?
 ? ?-- 獲取客戶訂單總數(shù)
 ? ?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，并計算該客戶的訂單總數(shù)。然后，通過查詢結果返回一個包含結果消息的語句。

你可以使用如下命令執(zhí)行該存儲過程：

CALL get_customer_orders('John Doe');

執(zhí)行上述命令后，存儲過程將輸出類似以下結果的消息：

John Doe has 2 order(s).

5 創(chuàng)建函數(shù)

函數(shù)是一段可重復使用的SQL代碼，接受輸入?yún)?shù)并返回值。下面是創(chuàng)建函數(shù)的步驟：

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 ;

這將創(chuàng)建一個名為calculate_discount的函數(shù)，當調(diào)用該函數(shù)時，它將接受產(chǎn)品價格作為輸入，并返回折扣后的價格。

要調(diào)用一個函數(shù)，可以使用SELECT語句將函數(shù)的返回值作為查詢結果進行檢索:

SELECT calculate_discount(1000);

?

6 創(chuàng)建觸發(fā)器

觸發(fā)器是在數(shù)據(jù)庫中某個特定事件發(fā)生時自動執(zhí)行的代碼。下面是創(chuàng)建觸發(fā)器的步驟：

我們可以使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器。以下是一個示例，創(chuàng)建名為update_stock的觸發(fā)器，當orders表插入新記錄時，自動更新products表的庫存數(shù)量：

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 ;

這將創(chuàng)建一個名為update_stock的觸發(fā)器，當orders表插入新記錄時，觸發(fā)器將自動執(zhí)行一條更新語句，減少products表中對應產(chǎn)品的庫存數(shù)量。

你無需手動調(diào)用觸發(fā)器，它將在滿足觸發(fā)條件時自動執(zhí)行。在這個例子中，每當在orders表中插入新記錄時，觸發(fā)器會自動減少相應產(chǎn)品的庫存數(shù)量。

例如，執(zhí)行以下插入命令：

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

這將插入一條新的訂單記錄，并觸發(fā)觸發(fā)器。觸發(fā)器將自動執(zhí)行更新語句，減少products表中對應產(chǎn)品的庫存數(shù)量。

你可以通過查詢products表來驗證觸發(fā)器是否成功更新了庫存數(shù)量：

SELECT * FROM products;

觸發(fā)器將自動更新products表中的庫存數(shù)量，反映了新的訂單操作。