实现“mysql for 不用存储过程”教程

整体流程

首先，让我们来看一下整个实现过程的步骤：

erDiagram
    CUSTOMER ||--o| ORDERS : places
    ORDERS ||--|{ ORDER_DETAILS : contains
    PRODUCT ||--o| ORDER_DETAILS : includes

具体步骤

  1. 创建数据库和表结构：
```sql
-- 创建数据库
CREATE DATABASE example_database;

-- 使用数据库
USE example_database;

-- 创建CUSTOMER表
CREATE TABLE CUSTOMER (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- 创建PRODUCT表
CREATE TABLE PRODUCT (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- 创建ORDERS表
CREATE TABLE ORDERS (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES CUSTOMER(id)
);

-- 创建ORDER_DETAILS表
CREATE TABLE ORDER_DETAILS (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES ORDERS(id),
    FOREIGN KEY (product_id) REFERENCES PRODUCT(id)
);


2. 插入数据：

```markdown
```sql
-- 插入CUSTOMER数据
INSERT INTO CUSTOMER (name) VALUES ('Alice');
INSERT INTO CUSTOMER (name) VALUES ('Bob');

-- 插入PRODUCT数据
INSERT INTO PRODUCT (name, price) VALUES ('Product A', 10.99);
INSERT INTO PRODUCT (name, price) VALUES ('Product B', 20.99);

-- 插入ORDERS数据
INSERT INTO ORDERS (customer_id, order_date) VALUES (1, '2022-01-01');
INSERT INTO ORDERS (customer_id, order_date) VALUES (2, '2022-01-02');

-- 插入ORDER_DETAILS数据
INSERT INTO ORDER_DETAILS (order_id, product_id, quantity) VALUES (1, 1, 2);
INSERT INTO ORDER_DETAILS (order_id, product_id, quantity) VALUES (2, 2, 1);


3. 查询数据：

```markdown
```sql
-- 查询订单信息及顾客名字
SELECT o.id, c.name, o.order_date
FROM ORDERS o
JOIN CUSTOMER c ON o.customer_id = c.id;

-- 查询订单详情及商品信息
SELECT od.id, p.name, od.quantity
FROM ORDER_DETAILS od
JOIN PRODUCT p ON od.product_id = p.id;


### 类图

```mermaid
classDiagram
    CUSTOMER <|-- ORDERS
    PRODUCT <|-- ORDER_DETAILS

通过以上步骤，你就成功地实现了“mysql for 不用存储过程”。希望这篇文章能够帮助你更好地理解和应用MySQL数据库。如果有任何问题，欢迎随时向我提问。祝你学习顺利！