基础-SQL语言基础
大约 10 分钟
基础-SQL语言基础
业务场景引入
假设你正在为一家在线书店构建数据库系统，需要处理以下核心业务：
- 图书管理：添加新书、更新库存、查询图书信息
- 用户管理：用户注册、信息维护、权限控制
- 订单处理：下单、支付、发货、退货
- 数据分析：销售统计、用户行为分析、库存报告
这些业务场景涵盖了SQL的四大核心操作：增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）。让我们通过实际的业务案例来掌握SQL语言基础。
DDL数据定义语言
数据库和表的创建
-- 创建书店数据库
CREATE DATABASE bookstore
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
USE bookstore;
-- 图书分类表
CREATE TABLE categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(100) NOT NULL,
parent_id INT,
description TEXT,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_parent_id (parent_id),
INDEX idx_is_active (is_active),
FOREIGN KEY (parent_id) REFERENCES categories(category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 图书信息表
CREATE TABLE books (
book_id BIGINT PRIMARY KEY AUTO_INCREMENT,
isbn VARCHAR(20) UNIQUE NOT NULL,
title VARCHAR(300) NOT NULL,
author VARCHAR(200) NOT NULL,
publisher VARCHAR(200),
publish_date DATE,
category_id INT,
price DECIMAL(8,2) NOT NULL,
stock_quantity INT UNSIGNED DEFAULT 0,
description TEXT,
cover_image_url VARCHAR(500),
page_count SMALLINT UNSIGNED,
language ENUM('Chinese', 'English', 'Bilingual') DEFAULT 'Chinese',
status ENUM('Available', 'Out_of_Stock', 'Discontinued') DEFAULT 'Available',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_isbn (isbn),
INDEX idx_title (title),
INDEX idx_author (author),
INDEX idx_category (category_id),
INDEX idx_price (price),
INDEX idx_status_stock (status, stock_quantity),
FULLTEXT INDEX ft_search (title, author, description),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 用户表
CREATE TABLE users (
user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
real_name VARCHAR(100),
phone VARCHAR(20),
birth_date DATE,
gender ENUM('Male', 'Female', 'Other'),
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMP NULL,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_phone (phone),
INDEX idx_registration_date (registration_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单表
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) UNIQUE NOT NULL,
user_id BIGINT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
shipping_fee DECIMAL(6,2) DEFAULT 0.00,
discount_amount DECIMAL(8,2) DEFAULT 0.00,
final_amount DECIMAL(10,2) NOT NULL,
order_status ENUM('Pending', 'Paid', 'Shipped', 'Delivered', 'Cancelled', 'Refunded') DEFAULT 'Pending',
payment_method ENUM('Alipay', 'WeChat', 'Credit_Card', 'Bank_Transfer'),
shipping_address TEXT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
payment_date TIMESTAMP NULL,
shipping_date TIMESTAMP NULL,
delivery_date TIMESTAMP NULL,
INDEX idx_order_no (order_no),
INDEX idx_user_id (user_id),
INDEX idx_order_status (order_status),
INDEX idx_order_date (order_date),
INDEX idx_payment_method (payment_method),
FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 订单明细表
CREATE TABLE order_items (
item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_id BIGINT NOT NULL,
book_id BIGINT NOT NULL,
quantity SMALLINT UNSIGNED NOT NULL,
unit_price DECIMAL(8,2) NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
INDEX idx_order_id (order_id),
INDEX idx_book_id (book_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (book_id) REFERENCES books(book_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;表结构修改
-- 添加新列
ALTER TABLE books
ADD COLUMN weight DECIMAL(6,3) COMMENT '图书重量(kg)',
ADD COLUMN bestseller_rank INT COMMENT '畅销榜排名';
-- 修改列定义
ALTER TABLE books
MODIFY COLUMN description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 添加索引
ALTER TABLE books
ADD INDEX idx_bestseller_rank (bestseller_rank),
ADD INDEX idx_publish_date (publish_date);
-- 添加外键约束
ALTER TABLE books
ADD CONSTRAINT fk_books_category
FOREIGN KEY (category_id) REFERENCES categories(category_id)
ON DELETE SET NULL ON UPDATE CASCADE;
-- 删除列
ALTER TABLE books DROP COLUMN weight;
-- 重命名表
RENAME TABLE order_items TO order_details;DML数据操作语言
INSERT数据插入
-- 单行插入
INSERT INTO categories (category_name, description)
VALUES ('计算机科学', '包含编程、算法、数据结构等技术类图书');
-- 多行插入
INSERT INTO categories (category_name, parent_id, description) VALUES
('编程语言', 1, 'Java、Python、C++等编程语言书籍'),
('数据库', 1, 'MySQL、Oracle、MongoDB等数据库技术'),
('算法与数据结构', 1, '算法设计、数据结构相关书籍'),
('文学', NULL, '小说、散文、诗歌等文学作品'),
('科幻小说', 5, '科幻类小说作品');
-- 批量插入图书数据
INSERT INTO books (isbn, title, author, publisher, publish_date, category_id, price, stock_quantity, description) VALUES
('978-7-121-12345-6', 'MySQL技术内幕', '张三', '电子工业出版社', '2024-01-15', 2, 89.00, 50, 'MySQL数据库深度解析，涵盖存储引擎、优化等高级主题'),
('978-7-302-23456-7', 'Java核心技术', '李四', '清华大学出版社', '2024-02-20', 1, 128.00, 30, 'Java编程语言核心技术详解'),
('978-7-111-34567-8', '算法导论', '王五', '机械工业出版社', '2023-12-10', 3, 158.00, 25, '计算机算法经典教材'),
('978-7-508-45678-9', '三体', '刘慈欣', '重庆出版社', '2006-05-01', 6, 23.00, 100, '著名科幻小说三部曲第一部');
-- 用INSERT...SELECT复制数据
INSERT INTO books (isbn, title, author, publisher, category_id, price, stock_quantity)
SELECT
CONCAT('COPY-', isbn) as new_isbn,
CONCAT('[备份] ', title) as new_title,
author,
publisher,
category_id,
price * 0.8 as discounted_price,
0 as initial_stock
FROM books
WHERE category_id = 1;
-- INSERT...ON DUPLICATE KEY UPDATE
INSERT INTO books (isbn, title, author, price, stock_quantity)
VALUES ('978-7-121-12345-6', 'MySQL技术内幕(第二版)', '张三', 99.00, 60)
ON DUPLICATE KEY UPDATE
title = VALUES(title),
price = VALUES(price),
stock_quantity = stock_quantity + VALUES(stock_quantity),
updated_at = CURRENT_TIMESTAMP;SELECT数据查询
基础查询
-- 简单查询
SELECT book_id, title, author, price FROM books;
-- 条件查询
SELECT title, author, price
FROM books
WHERE price BETWEEN 50 AND 100
AND category_id = 2;
-- 模糊查询
SELECT title, author, price
FROM books
WHERE title LIKE '%MySQL%'
OR author LIKE '%张%'
OR description LIKE '%数据库%';
-- 正则表达式查询
SELECT title, isbn
FROM books
WHERE isbn REGEXP '^978-7-121.*';
-- 空值处理
SELECT title, author, IFNULL(publisher, '未知出版社') AS publisher_name
FROM books
WHERE publish_date IS NOT NULL;排序和分页
-- 排序查询
SELECT title, author, price, stock_quantity
FROM books
ORDER BY price DESC, stock_quantity ASC;
-- 分页查询
SELECT title, author, price
FROM books
ORDER BY created_at DESC
LIMIT 10 OFFSET 20; -- 第3页，每页10条
-- 使用LIMIT简化分页
SELECT title, author, price
FROM books
ORDER BY price DESC
LIMIT 20, 10; -- 跳过20条，取10条聚合函数
-- 基础统计
SELECT
COUNT(*) as total_books,
COUNT(DISTINCT author) as unique_authors,
AVG(price) as avg_price,
MAX(price) as max_price,
MIN(price) as min_price,
SUM(stock_quantity) as total_stock
FROM books;
-- 分组统计
SELECT
c.category_name,
COUNT(b.book_id) as book_count,
AVG(b.price) as avg_price,
SUM(b.stock_quantity) as total_stock
FROM books b
INNER JOIN categories c ON b.category_id = c.category_id
GROUP BY c.category_id, c.category_name
HAVING book_count > 5
ORDER BY avg_price DESC;
-- 更复杂的统计查询
SELECT
YEAR(publish_date) as publish_year,
c.category_name,
COUNT(*) as book_count,
ROUND(AVG(price), 2) as avg_price,
CASE
WHEN AVG(price) < 50 THEN '低价位'
WHEN AVG(price) < 100 THEN '中价位'
ELSE '高价位'
END as price_level
FROM books b
INNER JOIN categories c ON b.category_id = c.category_id
WHERE publish_date >= '2020-01-01'
GROUP BY YEAR(publish_date), c.category_id, c.category_name
ORDER BY publish_year DESC, avg_price DESC;多表连接查询
-- 内连接：查询有订单的用户信息
SELECT DISTINCT
u.user_id,
u.username,
u.real_name,
u.email
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
-- 左连接：查询所有用户及其订单数量
SELECT
u.user_id,
u.username,
u.real_name,
COUNT(o.order_id) as order_count,
COALESCE(SUM(o.final_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.real_name
ORDER BY total_spent DESC;
-- 复杂多表连接：订单详情查询
SELECT
o.order_no,
u.username,
u.real_name,
b.title,
b.author,
oi.quantity,
oi.unit_price,
oi.subtotal,
o.order_status,
o.order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_details oi ON o.order_id = oi.order_id
INNER JOIN books b ON oi.book_id = b.book_id
WHERE o.order_date >= '2024-01-01'
AND o.order_status IN ('Paid', 'Shipped', 'Delivered')
ORDER BY o.order_date DESC, o.order_no;子查询
-- 标量子查询：查询价格高于平均价格的图书
SELECT title, author, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);
-- EXISTS子查询：查询有销售记录的图书
SELECT b.title, b.author, b.price
FROM books b
WHERE EXISTS (
SELECT 1
FROM order_details oi
WHERE oi.book_id = b.book_id
);
-- IN子查询：查询特定分类的图书
SELECT title, author, price
FROM books
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name IN ('编程语言', '数据库')
);
-- 相关子查询：每个分类中价格最高的图书
SELECT b1.title, b1.author, b1.price, c.category_name
FROM books b1
INNER JOIN categories c ON b1.category_id = c.category_id
WHERE b1.price = (
SELECT MAX(b2.price)
FROM books b2
WHERE b2.category_id = b1.category_id
);UPDATE数据更新
-- 基础更新
UPDATE books
SET price = 99.00, stock_quantity = 80
WHERE isbn = '978-7-121-12345-6';
-- 条件更新
UPDATE books
SET status = 'Out_of_Stock'
WHERE stock_quantity = 0;
-- 批量更新：所有编程类图书打9折
UPDATE books
SET price = price * 0.9
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name LIKE '%编程%'
);
-- 多表更新：更新订单总额
UPDATE orders o
INNER JOIN (
SELECT
order_id,
SUM(subtotal) as calculated_total
FROM order_details
GROUP BY order_id
) calc ON o.order_id = calc.order_id
SET o.total_amount = calc.calculated_total + o.shipping_fee - o.discount_amount,
o.final_amount = calc.calculated_total + o.shipping_fee - o.discount_amount;
-- 安全更新：使用LIMIT防止意外
UPDATE books
SET bestseller_rank = NULL
WHERE bestseller_rank > 1000
LIMIT 100;DELETE数据删除
-- 基础删除
DELETE FROM books
WHERE status = 'Discontinued'
AND stock_quantity = 0;
-- 条件删除：删除无订单的用户
DELETE FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id
FROM orders
)
AND registration_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- 多表删除：删除订单及其明细
DELETE o, oi
FROM orders o
INNER JOIN order_details oi ON o.order_id = oi.order_id
WHERE o.order_status = 'Cancelled'
AND o.order_date < DATE_SUB(NOW(), INTERVAL 6 MONTH);
-- 安全删除：使用LIMIT
DELETE FROM books
WHERE status = 'Discontinued'
LIMIT 10;高级SQL特性
窗口函数（MySQL 8.0+）
-- 排名函数
SELECT
title,
author,
price,
category_id,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as price_rank,
RANK() OVER (ORDER BY price DESC) as overall_rank,
DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank
FROM books;
-- 累计统计
SELECT
order_date,
final_amount,
SUM(final_amount) OVER (ORDER BY order_date) as cumulative_sales,
AVG(final_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7days
FROM orders
WHERE order_status = 'Delivered'
ORDER BY order_date;
-- 分组内比较
SELECT
title,
author,
price,
category_id,
price - LAG(price) OVER (PARTITION BY category_id ORDER BY price) as price_diff_from_prev,
FIRST_VALUE(title) OVER (PARTITION BY category_id ORDER BY price DESC) as highest_priced_in_category
FROM books;通用表表达式CTE（MySQL 8.0+）
-- 递归CTE：分类层级查询
WITH RECURSIVE category_hierarchy AS (
-- 基础查询：顶级分类
SELECT
category_id,
category_name,
parent_id,
0 as level,
CAST(category_name AS CHAR(200)) as path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 递归查询：子分类
SELECT
c.category_id,
c.category_name,
c.parent_id,
ch.level + 1,
CONCAT(ch.path, ' > ', c.category_name)
FROM categories c
INNER JOIN category_hierarchy ch ON c.parent_id = ch.category_id
)
SELECT
category_id,
CONCAT(REPEAT(' ', level), category_name) as indented_name,
level,
path
FROM category_hierarchy
ORDER BY path;
-- 复杂统计CTE
WITH monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') as sales_month,
COUNT(*) as order_count,
SUM(final_amount) as monthly_revenue
FROM orders
WHERE order_status = 'Delivered'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
sales_growth AS (
SELECT
sales_month,
order_count,
monthly_revenue,
LAG(monthly_revenue) OVER (ORDER BY sales_month) as prev_month_revenue,
ROUND(
(monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY sales_month))
/ LAG(monthly_revenue) OVER (ORDER BY sales_month) * 100, 2
) as growth_rate
FROM monthly_sales
)
SELECT
sales_month,
order_count,
monthly_revenue,
prev_month_revenue,
COALESCE(growth_rate, 0) as growth_rate_percent
FROM sales_growth
ORDER BY sales_month;存储过程示例
DELIMITER $$
-- 创建订单处理存储过程
CREATE PROCEDURE ProcessOrder(
IN p_user_id BIGINT,
IN p_book_id BIGINT,
IN p_quantity INT,
OUT p_order_id BIGINT,
OUT p_result_code INT,
OUT p_result_message VARCHAR(200)
)
BEGIN
DECLARE v_stock INT DEFAULT 0;
DECLARE v_price DECIMAL(8,2) DEFAULT 0.00;
DECLARE v_order_no VARCHAR(32);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_result_code = -1;
SET p_result_message = 'Order processing failed due to database error';
END;
START TRANSACTION;
-- 检查库存
SELECT stock_quantity, price
INTO v_stock, v_price
FROM books
WHERE book_id = p_book_id AND status = 'Available'
FOR UPDATE;
IF v_stock < p_quantity THEN
SET p_result_code = 1;
SET p_result_message = 'Insufficient stock';
ROLLBACK;
ELSE
-- 生成订单号
SET v_order_no = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(LAST_INSERT_ID(), 6, '0'));
-- 创建订单
INSERT INTO orders (order_no, user_id, total_amount, final_amount, order_status)
VALUES (v_order_no, p_user_id, v_price * p_quantity, v_price * p_quantity, 'Pending');
SET p_order_id = LAST_INSERT_ID();
-- 创建订单明细
INSERT INTO order_details (order_id, book_id, quantity, unit_price, subtotal)
VALUES (p_order_id, p_book_id, p_quantity, v_price, v_price * p_quantity);
-- 减少库存
UPDATE books
SET stock_quantity = stock_quantity - p_quantity,
updated_at = CURRENT_TIMESTAMP
WHERE book_id = p_book_id;
SET p_result_code = 0;
SET p_result_message = 'Order created successfully';
COMMIT;
END IF;
END$$
DELIMITER ;
-- 调用存储过程
CALL ProcessOrder(1, 1, 2, @order_id, @result_code, @result_message);
SELECT @order_id, @result_code, @result_message;SQL性能优化基础
查询优化技巧
-- ✅ 使用索引的查询
SELECT title, author, price
FROM books
WHERE category_id = 2 -- category_id有索引
AND status = 'Available';
-- ❌ 避免函数计算导致索引失效
SELECT title, author, price
FROM books
WHERE YEAR(publish_date) = 2024; -- 函数计算，索引失效
-- ✅ 改写为范围查询
SELECT title, author, price
FROM books
WHERE publish_date >= '2024-01-01'
AND publish_date < '2025-01-01';
-- ✅ 使用LIMIT限制结果集
SELECT title, author, price
FROM books
WHERE category_id = 1
ORDER BY created_at DESC
LIMIT 10;
-- ✅ 使用覆盖索引避免回表
SELECT book_id, title, price -- 这些字段都在索引中
FROM books
WHERE category_id = 2
AND status = 'Available';执行计划分析
-- 查看执行计划
EXPLAIN SELECT
b.title, b.author, b.price,
c.category_name,
COUNT(oi.item_id) as sales_count
FROM books b
INNER JOIN categories c ON b.category_id = c.category_id
LEFT JOIN order_details oi ON b.book_id = oi.book_id
GROUP BY b.book_id, b.title, b.author, b.price, c.category_name
HAVING sales_count > 10;
-- JSON格式的详细执行计划
EXPLAIN FORMAT=JSON
SELECT * FROM books WHERE title LIKE '%MySQL%';总结与最佳实践
SQL编写规范
命名规范
- 表名、字段名使用小写字母和下划线
- 避免使用MySQL保留字
- 使用有意义的名称
查询优化
- 避免SELECT *，明确指定需要的字段
- 合理使用WHERE条件过滤数据
- 适当使用LIMIT限制结果集大小
- 优先使用INNER JOIN而非子查询
安全考虑
- 使用参数化查询防止SQL注入
- 重要操作使用事务保证数据一致性
- 删除和更新操作谨慎使用，建议加LIMIT
SQL语言是数据库操作的基础，掌握这些核心语法和优化技巧，为后续学习高级特性和性能调优打下坚实基础。