SQL 基础

-- 创建/删除数据库
CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARACTER SET utf8mb4;
DROP DATABASE IF EXISTS demo;

-- 选择数据库
USE shop;

-- 创建表（示例）
CREATE TABLE categories (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 修改结构
ALTER TABLE categories ADD COLUMN parent_id BIGINT NULL;
ALTER TABLE categories DROP COLUMN description;

CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  stock INT NOT NULL DEFAULT 0,
  status ENUM('ON','OFF') NOT NULL DEFAULT 'ON',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ux_products_name (name)
);

-- 插入
INSERT INTO products (name, price, stock) VALUES
('iPhone 15', 5999.00, 5),
('Kindle', 899.00, 12);

-- 插入或更新（避免重复键）
INSERT INTO products (name, price, stock)
VALUES ('Kindle', 799.00, 20)
ON DUPLICATE KEY UPDATE price = VALUES(price), stock = stock + VALUES(stock);

-- 基础查询
SELECT id, name, price FROM products;

-- 过滤、排序、分页
SELECT p.id, p.name, p.price AS retail_price
FROM products AS p
WHERE p.price >= 1000 AND p.name LIKE '%i%'
ORDER BY p.price DESC, p.id ASC
LIMIT 10 OFFSET 0;

SELECT name,
       stock,
       CASE WHEN stock = 0 THEN '缺货' WHEN stock < 5 THEN '紧张' ELSE '充足' END AS stock_status,
       ROUND(price * 0.9, 2) AS discount_price,
       DATE_FORMAT(created_at, '%Y-%m-%d') AS created_date
FROM products;

SELECT JSON_EXTRACT(metadata, '$.color') AS color
FROM product_attributes;

-- 更新库存
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 按条件批量更新，可借助子查询
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'CANCELLED'
WHERE u.email NOT LIKE '%@example.com';

-- 删除指定行
DELETE FROM products WHERE id = 2;

-- 带限制的清理（避免一次性删除过多数据）
DELETE FROM orders WHERE status = 'CANCELLED' ORDER BY created_at LIMIT 1000;

SELECT COUNT(*) AS cnt, MIN(price) AS minp, MAX(price) AS maxp
FROM products;

SELECT name, COUNT(*) AS n
FROM products
GROUP BY name
HAVING COUNT(*) > 1
ORDER BY n DESC;

-- 聚合与条件
SELECT DATE(created_at) AS d, SUM(total) AS revenue
FROM orders
WHERE created_at >= CURDATE() - INTERVAL 7 DAY
GROUP BY d WITH ROLLUP;

LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, price, stock);

-- 用户与订单数
SELECT u.id, u.name, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY orders DESC;