好的，让我来逐一回答你的问题。
1. 新建数据库和表
创建数据库：
```sql
CREATE DATABASE taobao_db;
```
创建商品分类表：
```sql
USE taobao_db;
CREATE TABLE tb_goodscate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
```
创建商品信息表：
```sql
CREATE TABLE tb_goods (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
price DECIMAL(8,2) NOT NULL,
score DECIMAL(2,1) NOT NULL,
goodscate_id INT NOT NULL,
FOREIGN KEY (goodscate_id) REFERENCES tb_goodscate(id)
);
```
创建客户信息表：
```sql
CREATE TABLE tb_customer (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
address VARCHAR(100) NOT NULL
);
```
2. 添加数据
添加商品分类数据：
```sql
INSERT INTO tb_goodscate (name) VALUES
('衣服'),
('鞋子'),
('数码'),
('食品'),
('家居'),
('化妆品'),
('运动'),
('图书'),
('玩具'),
('母婴');
```
添加商品数据：
```sql
-- 生成随机商品分类id
DELIMITER //
CREATE PROCEDURE random_goodscate_id()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE goodscate_id INT DEFAULT 0;
WHILE i < 50000 DO
SET goodscate_id = FLOOR(1 + RAND() * 10);
INSERT INTO tb_goods (name, price, score, goodscate_id) VALUES
(CONCAT('商品', i), FLOOR(1 + RAND() * 1000), ROUND(1 + RAND() * 4, 1), goodscate_id);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL random_goodscate_id();
```
添加客户信息数据：
```sql
-- 生成随机客户信息
DELIMITER //
CREATE PROCEDURE random_customer()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000 DO
INSERT INTO tb_customer (name, age, address) VALUES
(CONCAT('客户', i), FLOOR(1 + RAND() * 100), CONCAT('地址', i));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL random_customer();
```
3. 使用游标修改商品表中商品价格低于500的商品价格为1000
```sql
DELIMITER //
CREATE PROCEDURE update_goods_price()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE goods_id INT;
DECLARE goods_price DECIMAL(8,2);
DECLARE goods_cursor CURSOR FOR SELECT id, price FROM tb_goods WHERE price < 500;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN goods_cursor;
goods_loop: LOOP
FETCH goods_cursor INTO goods_id, goods_price;
IF done THEN
LEAVE goods_loop;
END IF;
UPDATE tb_goods SET price = 1000 WHERE id = goods_id;
END LOOP;
CLOSE goods_cursor;
END //
DELIMITER ;
CALL update_goods_price();
```
4. 删除商品表中评分低于3分的商品
```sql
DELETE FROM tb_goods WHERE score < 3;
```
5. 使用触发器完成购物车商品的添加操作
```sql
CREATE TABLE tb_shopping_cart (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
goods_id INT NOT NULL,
quantity INT NOT NULL
);
CREATE TRIGGER add_to_shopping_cart AFTER INSERT ON tb_order
FOR EACH ROW
BEGIN
INSERT INTO tb_shopping_cart (customer_id, goods_id, quantity) VALUES (NEW.customer_id, NEW.goods_id, NEW.quantity);
END;
```
6. 使用事件完成商品分类表从此刻开始10分钟后添加建材分类
```sql
CREATE EVENT add_building_materials
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO tb_goodscate (name) VALUES ('建材');
```
7. 使用事件完成商品分类表从此刻开始5个月内每天添加10条记录到商品表中
```sql
CREATE EVENT add_goods
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
INSERT INTO tb_goods (name, price, score, goodscate_id) VALUES
(CONCAT('商品', FLOOR(1 + RAND() * 50000)), FLOOR(1 + RAND() * 1000), ROUND(1 + RAND() * 4, 1), FLOOR(1 + RAND() * 10));
SET i = i + 1;
END WHILE;
END;
```
8. 修改7题的事件从每天为每周添加
```sql
ALTER EVENT add_goods
ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
INSERT INTO tb_goods (name, price, score, goodscate_id) VALUES
(CONCAT('商品', FLOOR(1 + RAND() * 50000)), FLOOR(1 + RAND() * 1000), ROUND(1 + RAND() * 4, 1), FLOOR(1 + RAND() * 10));
SET i = i + 1;
END WHILE;
END;
```
9. 修改8题的事件从此刻开始6个月3天2小时后添加建材分类并且事件执行完毕后保留
```sql
ALTER EVENT add_building_materials
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 6 MONTH + INTERVAL 3 DAY + INTERVAL 2 HOUR
ON COMPLETION PRESERVE
DO
INSERT INTO tb_goodscate (name) VALUES ('建材');
```
扫一扫
740
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
