1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
-- ==========================================
-- 1. DDL（数据定义）
-- ==========================================
CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4;
USE shop;
CREATE TABLE products (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0,
stock INT UNSIGNED NOT NULL DEFAULT 0,
category_id INT UNSIGNED,
tags JSON, -- MySQL 5.7+ JSON类型
description TEXT,
is_active TINYINT(1) DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_price (price)
) ENGINE=InnoDB;
-- 修改表
ALTER TABLE products ADD COLUMN view_count INT DEFAULT 0;
ALTER TABLE products MODIFY COLUMN name VARCHAR(300);
ALTER TABLE products DROP COLUMN view_count;
ALTER TABLE products RENAME TO goods;
RENAME TABLE goods TO products;
-- ==========================================
-- 2. DML（数据操作）
-- ==========================================
INSERT INTO products(name, price, stock, category_id, tags) VALUES
('iPhone 15', 5999.00, 100, 1, JSON_ARRAY('手机','苹果','5G')),
('小米14', 3999.00, 200, 1, JSON_ARRAY('手机','小米','5G')),
('华为Mate60', 5499.00, 150, 1, JSON_ARRAY('手机','华为','5G')),
('MacBook Pro', 14999.00, 50, 2, JSON_ARRAY('电脑','苹果','笔记本'));
-- 查询
SELECT * FROM products WHERE price BETWEEN 3000 AND 6000;
SELECT * FROM products WHERE tags->'$[0]' = '手机';
SELECT category_id, COUNT(*) cnt, AVG(price) avg_price
FROM products GROUP BY category_id HAVING COUNT(*) > 0;
SELECT * FROM products ORDER BY price DESC LIMIT 5 OFFSET 0;
-- 子查询
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
-- 关联
SELECT p.name, p.price, c.category_name
FROM products p LEFT JOIN categories c ON p.category_id = c.id;
-- UPDATE
UPDATE products SET stock = stock - 1, price = price * 0.9 WHERE id = 1;
-- DELETE
DELETE FROM products WHERE stock = 0;
TRUNCATE TABLE products;
-- ==========================================
-- 3. 事务
-- ==========================================
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders(product_id, qty) VALUES (1, 1);
COMMIT;
-- ROLLBACK; -- 出错回滚
-- ==========================================
-- 4. 视图 / 函数 / 存储过程
-- ==========================================
CREATE VIEW v_active_products AS
SELECT id, name, price FROM products WHERE is_active = 1;
CREATE FUNCTION fn_get_price_level(p DECIMAL(10,2))
RETURNS VARCHAR(20) DETERMINISTIC
RETURN CASE
WHEN p < 1000 THEN '便宜'
WHEN p < 5000 THEN '中等'
WHEN p < 10000 THEN '贵'
ELSE '土豪款'
END;
SELECT name, price, fn_get_price_level(price) level FROM products;
-- ==========================================
-- 5. 常用聚合 / 窗口函数（8.0+）
-- ==========================================
SELECT
name, price,
ROW_NUMBER() OVER (ORDER BY price DESC) rn,
RANK() OVER (ORDER BY price DESC) rk,
DENSE_RANK() OVER (ORDER BY price DESC) drk,
SUM(price) OVER () total,
AVG(price) OVER (PARTITION BY category_id) avg_cat
FROM products;
|