MySQL数据库学习整理（1）CRUD、JOIN、索引、事务

2025-10-11

MySQL基础简略整理，便于快速阅览。

CRUD

增：

数据库新增：**create **database 数据库名;
表新增：**create **table 表名;
字段新增：**alter **table 表名 **add **字段名类型;
行新增：**insert **表名 (字段名1，字段名2……) values(值1,值2,……);

删：

数据库删除：**drop **database 数据库名;
表删除：**drop **table 表名;
字段删除：**alter **table 表名 **drop **字段名;
行删除：**delete **from 表名 where 条件;

改：

表名修改：**alter **table 旧表名 rename to 新表名;
字段修改：**alter **table 表名 [**change **旧字段名新字段名类型/**modify **字段名类型];
行修改：**update **表名 **set **字段名1=值1, 字段名2=值2,...;

查：

当前数据库查询：**select **database();
全部数据库查询：**show **databases;
全部表查询：**show **tables;
表结构查询：**desc **表名;
行查询：
基本查询：**select **[distinc]* from 表名;
条件查询：select * from 表名 where 条件;
聚合查询：select 聚合函数() from 表名;
分组查询：select * from 表名 group by 字段名;
排序查询：select * from 表名 order by 字段名排序方式(asc desc);
分页查询：select * from 表名 limit 起始索引,查询记录数;

JOIN

内连接：AB交集-**select **字段列表 from 表1 **join **表2 **where **条件;
外连接：
左外：AB交集+左表所有数据-**select **字段列表 from 表1 **left join **表2 on 条件;
右外：AB交集+右表所有数据-**select **字段列表 from 表1 **right join **表2 on 条件;
自连接：单表和自己查询，需要表别名-**select **字段列表 from 表1 别名A **join **表1 别名2 **on **条件;
联合查询：多次查询结果合并-select 字段列表 from 表1 … union select 字段列表 from 表2…;
子查询/嵌套查询：子查询外部语句可以是insert/uodate/delete/select的任何一个。

索引

聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据，only one。
二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，many。
增：create index 索引名称 on 表名(字段名);
删：drop index 索引名称 on 表名;
查：show index from 表名;

事务

基础概念：一系列操作的集合，要么一起成功、要么一起失败。
四大特性：
原子性（同成功同失败）；
一致性（数据状态一致）；
隔离性（隔离机制保证并发操作不影响事务）；
持久性（提交或回滚，数据的改变就是永久的）；
并发问题：
脏读：指事务执行时读取到另一个事务内尚未提交的数据；
不可重复读：指事务执行时多次读取同一数据得到不同的结果；
幻读：指事务执行时未读取到数据但是插入操作提示已经存在；
操作：
SELECT @@autocommit；设置为手动提交：SET @@autocommit=0；开启事务：START TRANSACTION/BEGIN；提交事务：COMMIT; 回滚事务：ROLLBACK;
隔离级别：
read uncommitted;
read committed;
repeatable read;
seralizable;

练习：

假设表结构如下：

# 20251011 练手

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    created_at DATETIME
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10,2),
    order_date DATE
);

-- 产品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT
);

-- 1. 查询所有年龄大于25岁的用户
select * from users where age>25;
-- 2. 插入一条新用户记录（姓名：张三，年龄：30，城市：北京）
insert users (name,age,city) values ("张三",30,"北京");
-- 3. 更新所有北京用户的年龄加1
update users set age=age+1 where city="北京";
-- 4. 删除所有年龄小于18岁的用户
delete from users where age &lt; 18;
-- 5. 查询订单表中金额最高的5条记录
select * from orders order by amount desc limit 5;

-- 6. 查询每个用户的订单数量
select u.name,count(o.id) 
from users u join orders o on u.id=o.user_id
group by u.name;
-- 7. 查询购买过产品的用户姓名和购买的产品名称
select u.name,o.product
from users u 
join orders o on u.id=o.user_id;
-- 8. 查询从未下过订单的用户(***)
select u.id, u.name
from users u 
left join orders o on u.id=o.user_id
where o.id is null;
-- 9. 查询每个城市的用户数量
select u.city,count(u.id) 
from users u
group by u.city;
-- 10. 查询每个产品类别的平均价格
select p.category,avg(p.price) 
from products p
group by p.category;

-- 11. 查询2024年10月的所有订单总金额(***)
select sum(o.amount)
from orders o
where order_date >= '2024-10-01' AND order_date &lt; '2024-11-01';
-- 12. 查询购买金额超过1000元的用户(***)
select u.id,u.name,sum(o.amount)
from users u
join orders o on u.id =o.user_id
group by u.id,u.name
having sum(o.amount)>1000;

-- 13. 查询库存少于10的产品，并按价格降序排列
select p.id,p.name,p.stock,p.price
from products p
where p.stock&lt;10
order by p.price desc;
-- 14. 查询每个用户的最近一次订单日期(***)
select u.id,u.name,max(o.order_date)
from users u
join orders o on u.id = o.user_id
group by u.id,u.name;
-- 15. 查询订单数量排名前3的产品
select o.product,count(o.id)
from orders o
group by o.product
order by count(o.id) desc
limit 3;

-- 16. 为users表的name字段创建索引
create index user_name on users(name);
-- 17. 查询并解释以下SQL的执行计划（***）
explain select * from users where age>25 and city = '北京';
-- 结果里simple_type是查询类型，
-- SIMPLE就是简单表不用表连接或者子查询，
-- type就是连接类型，all表示性能最差。
-- possible_key为null，表名这个表可用索引为null。
-- key也为null，证明了没用到索引。
-- filtered表示返回结果行数占需要读取数的百分比。
-- 18. 优化这条慢查询（假设orders表有100万条数据）（***）
explain SELECT u.name, COUNT(o.id) 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.name;
/*
首先，我们来分析一下为什么在 orders 表有100万条数据时，原始查询会非常慢。
执行流程: 数据库的执行器通常会遍历 users 表（我们称之为驱动表）。对于 users 表中的每一行记录，它都需要去 orders 表中查找所有匹配 o.user_id = u.id 的记录。
性能瓶颈: 关键在于“查找”这一步。由于 orders 表的 user_id 字段上没有索引，数据库为了给一个 user 找到他所有的 order，不得不对100万条记录的 orders 表进行一次全表扫描 (Full Table Scan)。
灾难性的结果: 如果 users 表有1000个用户，那么数据库就需要对 orders 表进行 1000 次全表扫描。这会导致 1000 * 1,000,000 级别的比较操作，执行时间会非常长，甚至可能导致数据库超时。
EXPLAIN 结果预测 (优化前): 在对 orders 表的扫描计划中，type 字段会显示为 ALL，这正是全表扫描的标志，也是性能问题的根源。*/
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 19. 写一个转账事务（从账户A转100元到账户B）（***）

start transaction；

-- 检查账户A的余额是否足够
-- (这里的 @balance 是一个会话变量，用于存储查询结果)
SELECT money INTO @balance FROM users WHERE name = 'A' FOR UPDATE;

-- 如果余额充足
IF @balance >= 100 THEN
    UPDATE users SET money = money - 100 WHERE name = 'A';
    UPDATE users SET money = money + 100 WHERE name = 'B';
    -- 提交事务，让所有更改永久生效
    COMMIT;
    SELECT '转账成功' AS Result;
ELSE
    -- 如果余额不足，则回滚事务，撤销所有操作
    ROLLBACK;
    SELECT '余额不足，转账失败' AS Result;
END IF;
-- **关键点**: `SELECT ... FOR UPDATE` 会锁定账户A的行，防止在本次事务完成前有其他操作来修改它的余额，这是防止并发问题的重要手段。

-- 20. 解释以下场景应该使用什么隔离级别(***)
-- 场景：电商库存扣减（防止超卖）
/*
(1.使用SERIALIZABLE(串行化)隔离级别:
优点: 这是最严格的隔离级别。它会强制所有事务排队执行，完全杜绝了并发问题。
缺点: 性能极差，因为牺牲了所有的并发性。在电商这种高并发场景下，基本不适用。

2.使用悲观锁(`SELECT ... FOR UPDATE`):
这是最常用、最推荐的方案。
在 `REPEATABLE READ` 或 `READ COMMITTED` 隔离级别下，通过在读取库存时加一个排他锁，强制其他想修改该库存的事务等待。
示例:
START TRANSACTION;
-- 查询并锁定库存行，其他事务将被阻塞在此
SELECT stock FROM products WHERE product_id = 123 FOR UPDATE;
-- 在代码中检查库存是否 > 0
-- 如果是，则执行更新
UPDATE products SET stock = stock - 1 WHERE product_id = 123;
COMMIT;

3.使用乐观锁:
不通过数据库锁来控制，而是通过一个版本号 (`version`) 字段。更新时检查版本号是否匹配，这是一种CAS (Compare-And-Swap) 思想。
示例: 
UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = 123 AND version = (之前读到的版本号);
如果更新影响的行数为0，说明在你更新前已经被别人修改了，此时需要应用重试或提示用户失败。)

← 返回