MySQL语法练习 - 基础DDL/DML/DQL/DCL练习

MySQL语法练习 - 基础DDL/DML/DQL/DCL练习

练习目标

通过本次练习掌握MySQL的四大核心语法：

• DDL (Data Definition Language) - 数据定义语言
• DML (Data Manipulation Language) - 数据操作语言
• DQL (Data Query Language) - 数据查询语言
• DCL (Data Control Language) - 数据控制语言

---

练习场景

我们将创建一个简单的在线书店管理系统，包含以下实体：

• 用户 (users)
• 书籍 (books)
• 分类 (categories)
• 订单 (orders)
• 订单详情 (order_items)

---

第一部分：DDL练习 (数据定义语言)

练习1.1：创建数据库

-- 任务：创建一个名为 bookstore 的数据库，字符集为utf8mb4
-- 请在下方写出SQL语句：

练习1.2：创建表结构

-- 任务1：创建分类表 categories
-- 字段要求：
-- id: 主键，自增，INT类型
-- name: 分类名称，VARCHAR(50)，不能为空，唯一
-- description: 分类描述，TEXT类型，可以为空
-- created_at: 创建时间，TIMESTAMP，默认当前时间
-- 请在下方写出SQL语句：-- 任务2：创建用户表 users
-- 字段要求：
-- id: 主键，自增，INT类型
-- username: 用户名，VARCHAR(50)，不能为空，唯一
-- email: 邮箱，VARCHAR(100)，不能为空，唯一
-- password: 密码，VARCHAR(255)，不能为空
-- phone: 手机号，VARCHAR(20)，可以为空
-- age: 年龄，INT，默认值为0
-- status: 状态，ENUM('active','inactive','banned')，默认'active'
-- created_at: 创建时间，TIMESTAMP，默认当前时间
-- updated_at: 更新时间，TIMESTAMP，默认当前时间且更新时自动更新
-- 请在下方写出SQL语句：-- 任务3：创建书籍表 books
-- 字段要求：
-- id: 主键，自增，INT类型
-- title: 书名，VARCHAR(200)，不能为空
-- author: 作者，VARCHAR(100)，不能为空
-- isbn: ISBN号，VARCHAR(20)，不能为空，唯一
-- price: 价格，DECIMAL(10,2)，不能为空
-- stock: 库存，INT，默认值为0
-- category_id: 分类ID，INT，外键关联categories表的id
-- description: 书籍描述，TEXT，可以为空
-- created_at: 创建时间，TIMESTAMP，默认当前时间
-- 请在下方写出SQL语句：-- 任务4：创建订单表 orders
-- 字段要求：
-- id: 主键，自增，INT类型
-- user_id: 用户ID，INT，外键关联users表的id
-- total_amount: 订单总金额，DECIMAL(10,2)，不能为空
-- status: 订单状态，ENUM('pending','paid','shipped','delivered','cancelled')，默认'pending'
-- order_date: 订单日期，TIMESTAMP，默认当前时间
-- 请在下方写出SQL语句：-- 任务5：创建订单详情表 order_items
-- 字段要求：
-- id: 主键，自增，INT类型
-- order_id: 订单ID，INT，外键关联orders表的id
-- book_id: 书籍ID，INT，外键关联books表的id
-- quantity: 数量，INT，不能为空，默认值为1
-- price: 单价，DECIMAL(10,2)，不能为空
-- 主键为(order_id, book_id)的复合主键
-- 请在下方写出SQL语句：

练习1.3：修改表结构

-- 任务1：为users表添加一个新字段 address VARCHAR(200)
-- 请在下方写出SQL语句：-- 任务2：将books表的author字段长度改为150
-- 请在下方写出SQL语句：-- 任务3：为books表添加索引
-- 在title字段上创建普通索引，索引名为idx_title
-- 在isbn字段上创建唯一索引，索引名为uk_isbn
-- 请在下方写出SQL语句：-- 任务4：为books表的price字段添加检查约束，确保价格大于0
-- 请在下方写出SQL语句：

---

第二部分：DML练习 (数据操作语言)

练习2.1：插入数据

-- 任务1：向categories表插入以下数据：
-- (1, '计算机科学', '计算机相关技术书籍')
-- (2, '文学小说', '各类文学作品和小说')
-- (3, '历史传记', '历史类和人物传记')
-- (4, '科学技术', '自然科学和工程技术')
-- 请在下方写出SQL语句：-- 任务2：向users表插入以下数据：
-- username='zhangsan', email='zhangsan@qq.com', password='123456', phone='13812345678', age=25
-- username='lisi', email='lisi@163.com', password='abcdef', phone='13987654321', age=30
-- username='wangwu', email='wangwu@gmail.com', password='qwerty', age=28
-- 请在下方写出SQL语句：-- 任务3：向books表插入以下数据：
-- title='MySQL必知必会', author='Ben Forta', isbn='9787115308122', price=45.50, stock=100, category_id=1, description='MySQL入门经典教程'
-- title='红楼梦', author='曹雪芹', isbn='9787020008735', price=68.00, stock=50, category_id=2, description='中国古典四大名著之一'
-- title='史记', author='司马迁', isbn='9787101003048', price=89.00, stock=30, category_id=3, description='中国第一部纪传体通史'
-- title='算法导论', author='Thomas H.Cormen', isbn='9787111407010', price=128.00, stock=25, category_id=1, description='算法领域的经典教材'
-- 请在下方写出SQL语句：-- 任务4：批量插入订单数据
-- 用户1购买了MySQL必知必会和算法导论，总金额173.50
-- 用户2购买了红楼梦，总金额68.00
-- 请先插入orders数据，再插入order_items数据：

练习2.2：更新数据

-- 任务1：将用户zhangsan的年龄更新为26
-- 请在下方写出SQL语句：-- 任务2：将所有计算机科学类别的书籍价格上涨10%
-- 请在下方写出SQL语句：-- 任务3：将库存少于30本的书籍状态标记为需要补货(可以通过添加一个remarks字段实现)
-- 请先添加字段，再更新数据：-- 任务4：更新订单状态，将用户1的订单状态改为'paid'
-- 请在下方写出SQL语句：

练习2.3：删除数据

-- 任务1：删除状态为'inactive'的用户
-- 请在下方写出SQL语句：-- 任务2：删除库存为0的书籍
-- 请在下方写出SQL语句：-- 任务3：删除已取消的订单及其详情
-- 请在下方写出SQL语句：

---

第三部分：DQL练习 (数据查询语言)

练习3.1：基础查询

-- 任务1：查询所有用户的用户名和邮箱
-- 请在下方写出SQL语句：-- 任务2：查询价格在50-100元之间的书籍信息
-- 请在下方写出SQL语句：-- 任务3：查询用户名包含'zhang'的用户信息
-- 请在下方写出SQL语句：-- 任务4：查询库存最多的前3本书
-- 请在下方写出SQL语句：-- 任务5：查询年龄在25-30岁之间的活跃用户
-- 请在下方写出SQL语句：

练习3.2：聚合查询

-- 任务1：统计每个分类下有多少本书
-- 请在下方写出SQL语句：-- 任务2：计算所有书籍的平均价格、最高价格、最低价格
-- 请在下方写出SQL语句：-- 任务3：统计每个用户的订单数量和总消费金额
-- 请在下方写出SQL语句：-- 任务4：查询购买书籍数量超过1本的订单
-- 请在下方写出SQL语句：-- 任务5：统计每月的订单数量和销售额
-- 请在下方写出SQL语句：

练习3.3：连接查询

-- 任务1：查询所有书籍及其分类名称
-- 请在下方写出SQL语句：-- 任务2：查询每个用户的订单信息（包括用户名、订单号、订单日期、总金额）
-- 请在下方写出SQL语句：-- 任务3：查询订单详情，显示订单号、用户名、书名、数量、单价
-- 请在下方写出SQL语句：-- 任务4：查询没有下过订单的用户
-- 请在下方写出SQL语句：-- 任务5：查询每个分类下销量最好的书籍
-- 请在下方写出SQL语句：

练习3.4：子查询

-- 任务1：查询价格高于平均价格的书籍
-- 请在下方写出SQL语句：-- 任务2：查询购买过'MySQL必知必会'这本书的用户信息
-- 请在下方写出SQL语句：-- 任务3：查询每个分类中价格最高的书籍
-- 请在下方写出SQL语句：-- 任务4：查询订单金额超过100元的用户名单
-- 请在下方写出SQL语句：-- 任务5：查询同时购买了计算机类和文学类书籍的用户
-- 请在下方写出SQL语句：

---

第四部分：DCL练习 (数据控制语言)

练习4.1：用户管理

-- 任务1：创建一个名为'bookstore_admin'的用户，密码为'admin123'，只能从本地登录
-- 请在下方写出SQL语句：-- 任务2：创建一个名为'bookstore_user'的用户，密码为'user123'，可以从任何地方登录
-- 请在下方写出SQL语句：-- 任务3：修改bookstore_user用户的密码为'newpass456'
-- 请在下方写出SQL语句：

练习4.2：权限管理

-- 任务1：给bookstore_admin用户授予bookstore数据库的所有权限
-- 请在下方写出SQL语句：-- 任务2：给bookstore_user用户授予bookstore数据库中books和categories表的SELECT权限
-- 请在下方写出SQL语句：-- 任务3：给bookstore_user用户授予users表的SELECT和UPDATE权限
-- 请在下方写出SQL语句：-- 任务4：撤销bookstore_user用户对users表的UPDATE权限
-- 请在下方写出SQL语句：-- 任务5：查看bookstore_admin用户的所有权限
-- 请在下方写出SQL语句：

---

第五部分：综合练习

练习5.1：视图创建

-- 任务1：创建一个视图，显示所有书籍的详细信息（包括分类名称）
-- 视图名：v_book_details
-- 请在下方写出SQL语句：-- 任务2：创建一个视图，显示用户的订单统计信息
-- 视图名：v_user_order_stats，包含：用户名、订单数量、总消费金额、最后一次购买时间
-- 请在下方写出SQL语句：

练习5.2：存储过程练习

-- 任务1：创建一个存储过程，根据书籍ID查询库存数量
-- 存储过程名：GetBookStock
-- 参数：IN book_id INT, OUT stock_count INT
-- 请在下方写出SQL语句：-- 任务2：创建一个存储过程，更新书籍库存
-- 存储过程名：UpdateBookStock
-- 参数：IN book_id INT, IN quantity INT (正数表示入库，负数表示出库)
-- 请在下方写出SQL语句：

练习5.3：触发器练习

-- 任务1：创建一个触发器，当插入订单详情时自动减少书籍库存
-- 触发器名：trigger_reduce_stock
-- 请在下方写出SQL语句：-- 任务2：创建一个触发器，当用户注册时自动记录到用户日志表
-- 首先创建用户日志表user_logs，然后创建触发器
-- 请在下方写出SQL语句：

练习5.4：事务练习

-- 任务1：创建一个完整的下单事务
-- 包括：插入订单、插入订单详情、更新书籍库存、更新订单总金额
-- 如果任何步骤失败，则回滚整个事务
-- 请在下方写出SQL语句：-- 任务2：使用保存点实现部分回滚
-- 先插入一个订单，设置保存点，再插入订单详情，如果详情插入失败，只回滚到保存点
-- 请在下方写出SQL语句：

---

练习答案

点击查看答案

DDL答案

练习1.1答案：

CREATE DATABASE bookstore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bookstore;

练习1.2答案：

-- 分类表
CREATE TABLE categories (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(50) NOT NULL UNIQUE,description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 用户表
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,phone VARCHAR(20),age INT DEFAULT 0,status ENUM('active','inactive','banned') DEFAULT 'active',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);-- 书籍表
CREATE TABLE books (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(200) NOT NULL,author VARCHAR(100) NOT NULL,isbn VARCHAR(20) NOT NULL UNIQUE,price DECIMAL(10,2) NOT NULL,stock INT DEFAULT 0,category_id INT,description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (category_id) REFERENCES categories(id)
);-- 订单表
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,total_amount DECIMAL(10,2) NOT NULL,status ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);-- 订单详情表
CREATE TABLE order_items (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT,book_id INT,quantity INT NOT NULL DEFAULT 1,price DECIMAL(10,2) NOT NULL,FOREIGN KEY (order_id) REFERENCES orders(id),FOREIGN KEY (book_id) REFERENCES books(id),UNIQUE KEY uk_order_book (order_id, book_id)
);

练习1.3答案：

-- 添加地址字段
ALTER TABLE users ADD COLUMN address VARCHAR(200);-- 修改作者字段长度
ALTER TABLE books MODIFY COLUMN author VARCHAR(150);-- 添加索引
CREATE INDEX idx_title ON books(title);
CREATE UNIQUE INDEX uk_isbn ON books(isbn);-- 添加检查约束
ALTER TABLE books ADD CONSTRAINT chk_price CHECK (price > 0);

DML答案

练习2.1答案：

-- 插入分类数据
INSERT INTO categories (name, description) VALUES 
('计算机科学', '计算机相关技术书籍'),
('文学小说', '各类文学作品和小说'),
('历史传记', '历史类和人物传记'),
('科学技术', '自然科学和工程技术');-- 插入用户数据
INSERT INTO users (username, email, password, phone, age) VALUES 
('zhangsan', 'zhangsan@qq.com', '123456', '13812345678', 25),
('lisi', 'lisi@163.com', 'abcdef', '13987654321', 30),
('wangwu', 'wangwu@gmail.com', 'qwerty', NULL, 28);-- 插入书籍数据
INSERT INTO books (title, author, isbn, price, stock, category_id, description) VALUES 
('MySQL必知必会', 'Ben Forta', '9787115308122', 45.50, 100, 1, 'MySQL入门经典教程'),
('红楼梦', '曹雪芹', '9787020008735', 68.00, 50, 2, '中国古典四大名著之一'),
('史记', '司马迁', '9787101003048', 89.00, 30, 3, '中国第一部纪传体通史'),
('算法导论', 'Thomas H.Cormen', '9787111407010', 128.00, 25, 1, '算法领域的经典教材');-- 插入订单数据
INSERT INTO orders (user_id, total_amount) VALUES (1, 173.50), (2, 68.00);-- 插入订单详情数据
INSERT INTO order_items (order_id, book_id, quantity, price) VALUES 
(1, 1, 1, 45.50),
(1, 4, 1, 128.00),
(2, 2, 1, 68.00);

练习2.2答案：

-- 更新用户年龄
UPDATE users SET age = 26 WHERE username = 'zhangsan';-- 更新计算机科学类书籍价格
UPDATE books SET price = price * 1.1 WHERE category_id = 1;-- 添加备注字段并更新
ALTER TABLE books ADD COLUMN remarks VARCHAR(100);
UPDATE books SET remarks = '需要补货' WHERE stock < 30;-- 更新订单状态
UPDATE orders SET status = 'paid' WHERE user_id = 1;

练习2.3答案：

-- 删除非活跃用户
DELETE FROM users WHERE status = 'inactive';-- 删除零库存书籍
DELETE FROM books WHERE stock = 0;-- 删除已取消订单
DELETE oi FROM order_items oi 
JOIN orders o ON oi.order_id = o.id 
WHERE o.status = 'cancelled';
DELETE FROM orders WHERE status = 'cancelled';

DQL答案

练习3.1答案：

-- 查询用户名和邮箱
SELECT username, email FROM users;-- 查询指定价格范围的书籍
SELECT * FROM books WHERE price BETWEEN 50 AND 100;-- 查询用户名包含'zhang'的用户
SELECT * FROM users WHERE username LIKE '%zhang%';-- 查询库存最多的前3本书
SELECT * FROM books ORDER BY stock DESC LIMIT 3;-- 查询指定年龄范围的活跃用户
SELECT * FROM users WHERE age BETWEEN 25 AND 30 AND status = 'active';

练习3.2答案：

-- 统计每个分类的书籍数量
SELECT c.name, COUNT(b.id) as book_count 
FROM categories c LEFT JOIN books b ON c.id = b.category_id 
GROUP BY c.id, c.name;-- 计算书籍价格统计
SELECT AVG(price) as avg_price, MAX(price) as max_price, MIN(price) as min_price FROM books;-- 统计用户订单和消费
SELECT u.username, COUNT(o.id) as order_count, COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;-- 查询多本书的订单
SELECT order_id, SUM(quantity) as total_books
FROM order_items
GROUP BY order_id
HAVING total_books > 1;-- 统计每月订单数和销售额
SELECT YEAR(order_date) as year, MONTH(order_date) as month, COUNT(*) as order_count, SUM(total_amount) as total_sales
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

练习3.3答案：

-- 查询书籍及分类
SELECT b.*, c.name as category_name 
FROM books b JOIN categories c ON b.category_id = c.id;-- 查询用户订单信息
SELECT u.username, o.id as order_id, o.order_date, o.total_amount
FROM users u JOIN orders o ON u.id = o.user_id;-- 查询订单详情
SELECT o.id as order_id, u.username, b.title, oi.quantity, oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;-- 查询没有订单的用户
SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;-- 查询每个分类销量最好的书籍
SELECT c.name, b.title, SUM(oi.quantity) as total_sold
FROM categories c
JOIN books b ON c.id = b.category_id
JOIN order_items oi ON b.id = oi.book_id
GROUP BY c.id, b.id
ORDER BY c.id, total_sold DESC;

DCL答案

练习4.1答案：

-- 创建管理员用户
CREATE USER 'bookstore_admin'@'localhost' IDENTIFIED BY 'admin123';-- 创建普通用户
CREATE USER 'bookstore_user'@'%' IDENTIFIED BY 'user123';-- 修改用户密码
ALTER USER 'bookstore_user'@'%' IDENTIFIED BY 'newpass456';

练习4.2答案：

-- 授予管理员所有权限
GRANT ALL PRIVILEGES ON bookstore.* TO 'bookstore_admin'@'localhost';-- 授予普通用户查询权限
GRANT SELECT ON bookstore.books TO 'bookstore_user'@'%';
GRANT SELECT ON bookstore.categories TO 'bookstore_user'@'%';-- 授予users表的查询和更新权限
GRANT SELECT, UPDATE ON bookstore.users TO 'bookstore_user'@'%';-- 撤销更新权限
REVOKE UPDATE ON bookstore.users FROM 'bookstore_user'@'%';-- 查看用户权限
SHOW GRANTS FOR 'bookstore_admin'@'localhost';