实现两个表的数据关联) (一)创建商品表production 商品id、商品类型、商品名字、商品价格 创建订单信息:pro_order 订单id、商品、购买数量、下单时间{收件人、收件人的电话、地址。。。。}【一个订单对应多个商品】 使用alter table设置商品表中的价格默认值为99.99
时间: 2023-11-29 07:31:36 浏览: 29
好的,下面是实现两个表的数据关联的步骤:
1. 创建商品表production:
```sql
CREATE TABLE production (
id INT PRIMARY KEY AUTO_INCREMENT,
type VARCHAR(50) NOT NULL,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) DEFAULT 99.99
);
```
2. 创建订单信息表pro_order:
```sql
CREATE TABLE pro_order (
id INT PRIMARY KEY AUTO_INCREMENT,
order_time DATETIME NOT NULL,
receiver VARCHAR(50) NOT NULL,
receiver_phone VARCHAR(20) NOT NULL,
address VARCHAR(100) NOT NULL
);
```
3. 创建订单与商品之间的关联表order_production:
```sql
CREATE TABLE order_production (
order_id INT NOT NULL,
production_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, production_id),
FOREIGN KEY (order_id) REFERENCES pro_order(id) ON DELETE CASCADE,
FOREIGN KEY (production_id) REFERENCES production(id) ON DELETE CASCADE
);
```
4. 插入数据:
```sql
-- 插入商品数据
INSERT INTO production (type, name, price) VALUES
('电子产品', '手机', 2999.00),
('食品', '牛肉干', 29.99),
('服装', 'T恤', 99.00);
-- 插入订单数据
INSERT INTO pro_order (order_time, receiver, receiver_phone, address) VALUES
('2021-07-01 10:00:00', '张三', '13888888888', '北京市朝阳区'),
('2021-07-02 11:00:00', '李四', '13999999999', '北京市海淀区');
-- 插入订单与商品关联数据
INSERT INTO order_production (order_id, production_id, quantity) VALUES
(1, 1, 2),
(1, 2, 5),
(2, 3, 3),
(2, 1, 1);
```
5. 查询订单信息及其包含的商品信息:
```sql
SELECT
o.id AS order_id, o.order_time, o.receiver, o.receiver_phone, o.address,
p.id AS production_id, p.type, p.name, p.price, op.quantity
FROM
pro_order o
INNER JOIN order_production op ON o.id = op.order_id
INNER JOIN production p ON op.production_id = p.id
ORDER BY
o.id, p.id;
```
以上是实现两个表的数据关联的完整步骤,希望能够帮到你!