MySQL 二轮学习笔记·基础篇

基础篇

【基础】

• 导入部分
• SQL核心语法 DDL DML DQL DCL
• 函数
• 约束
• 多表查询
• 事务

导入部分

3个术语

数据库（DB）存储数据的仓库

数据库管理系统（DBMS）操作数据库的工具，如MYSQL

SQL（结构化查询语言）

数据模型

ER模型

用实体、属性、关系描述数据，画图时 实体用矩形，属性用椭圆，关系用菱形

逻辑数据模型

MySQL会把ER模型转换成表，比如 用户实体 变成 user表，userID变成字段，每条数据变成行

MySQL是关系型数据库，本质就是用表存储数据，用关系（比如外键）关联表

SQL核心语法

DDL 数据定义语言

DML 数据操纵语言

DQL 数据查询语言

DCL 数据控制语言

DDL-数据定义语言

1.数据库操作-创建、切换、删除

创建数据库：create database [if not exists] (数据库名);

切换数据库：use (数据库名);

删除数据库：drop database [if exists] (数据库名);

2.表操作-创建、修改、删除

创建表

create table [if not exists] 表名 (字段...);

create table if not exists user {
    id int;
    username varchar(50);
    age int,
    create_time datatime
};

直接使用 VARCHAR 而不指定长度是错误的、不规范的，并且会带来数据完整性和可移植性的风险。应该使用 VARCHAR(N)，并为 N 选择一个符合你业务逻辑的、合理的最大长度。

存价格用decimal(10,2);（DECIMAL(10,2) 表示这个数字总共有10位，其中小数部分占2位。）

存时间用datetime

存日期用date

修改表

加字段：alter table 表名 add 字段名 类型;

改字段：alter table 表名 modify 字段名 类型;

删除表

删除表：drop table [if exists] 表名;

小结

DDL操作的是结构（库、表），不是数据。

创建时加if not exists，删除时加if exists，避免报错。

DML-数据操纵语言

插入数据

insert into 表名 (字段...) values (值...);

如果多条数据，多个(值..)用逗号隔开。

更新数据

必须加where条件，否则会操作全表！

update 表名 字段=值 [where 条件];

批量修改，多个把 字段=值 用逗号分开。

删除数据

必须加where条件，否则会操作全表！

delete from 表名 [where 条件];

DQL-数据查询语言

1.基础查询

查询所有字段

select * from 表名

查询指定字段

select 字段1... from 表名

去重查询

select distinct 字段 from 表名;

字段别名

select 字段1 [as] 别名1, ... from 表名

（as可省略）

2.条件查询

where的各种条件

比较运算符

> < = !=，注意=不是==

逻辑运算符

and, or, not

模糊查询★

like, %(匹配任意(含0)个字符)，_(匹配1个字符)

eg. select * from user where name like '张%'; 匹配张三、张三丰等

select * from user where name like '张_';匹配张三 等

范围查询★

in(10,20,30) 指定选择

between ... and ... 在两个值之间（含边界）

select name from user where age in (50,15);

空值查询★

is null（是空值）

is not null（不是空值）

注意 不能用 值 = null 来条件查询，只能用is

3.复杂查询-聚合函数 分组 排序 分页

聚合函数

聚合函数对字段名进行包装，返回对查询到的字段对应数据经过函数处理后的结果。

count(字段)(COUNT(1)统计行数。这里的 1 是一个常量值，对于每一行，它都会判断这个常量（永远非NULL）)

sum(字段)

avg(字段)

max(字段)

min(字段)

eg.select count(id) from user;这个语句会查到所有id不为空的user id并计数，而且比count(*)效率高。

分组查询

select 分组字段, 聚合函数(字段) from 表名 [where 条件] group by 分组字段 [having 分组筛选条件]

select age, count(id) as user_count
from user
where age > 20 -- 分组前筛选
group by age
having user_count >= 2; -- 分组后筛选

where用于group by之前的对数据筛选

having用于group by之后的对于分组的筛选

排序查询

select 字段 from 字段 order by 字段1 [asc/desc]... ;

默认是升序asc。

分页查询

select 字段 from 表名 limit 起始索引, 该页条数;

-- 第1页：起始索引0,取0-4；第2页：起始索引5,取5-9
select * from user limit 5,5;

综合练习与执行顺序

以 “电商订单表（order）” 为例，需求：查询 “2024 年 1 月 1 日后创建的订单中，每个用户的订单总金额（amount），只保留总金额≥1000 的用户，按总金额降序，显示第 1 页（每页 10 条）”

1. 筛选2024年1月1日后的订单：where create_time >= '2024-01-01'
2. 按用户id分组：group by user_id
3. 计算每个用户的总金额：sum(amount) as total_amount
4. 筛选总金额>=1000的用户：having toal_amount >= 1000
5. 按总金额排序：order by total_amount desc
6. 分页：limit 0,10

需牢记 “书写顺序≠执行顺序”，正确执行顺序（从左到右）：

1. FROM：确定要查询的表；
2. WHERE：筛选表中的行（分组前）；
3. GROUP BY：按指定字段分组；（聚合函数计算）
4. HAVING：筛选分组结果（分组后）；
5. SELECT：指定要显示的字段（包括聚合函数计算）；
6. ORDER BY：对最终结果排序；
7. LIMIT：分页截取结果。

理解执行顺序能快速排查错误，比如在where中使用聚合函数会报错，因为where执行时，还未分组，聚合函数无法计算。

DCL-数据控制语言

用户与权限管理

用户管理

MySQL用过 用户名@主机 区分不同操作者，主机限制从哪台机器登录，如root@localhost只能本地登录，user1@%可以从任意机器登录。

创建用户

create user '用户名'@'主机' identified by '密码';

修改密码

alter user '用户名'@'主机' identified by '新密码';

删除用户

drop user '用户名'@'主机';

权限控制

grant..on..to..授予权限，revoke..on..from回收权限，show..for..

授予权限

grant 权限1... on 数据库.数据表 to '用户名'@'主机';

通配符：*.*所有数据库的所有表，db_shop.*表示db_shop数据库的所有表

grant select, insert on db_shop.* to 'dev'@'localhost';

回收权限

revoke 权限1... on 数据库.数据表 from '用户名'@'主机名';

revoke insert on db_shop.* from 'dev'@'localhost';

查看权限

show grants for '用户名'@'主机';

生产环境绝对禁止使用root账号，开发dev用户只给select/insert/update，不给drop/alter

标量函数

1.字符串函数

2.数值函数

3.日期函数

常用格式符：%Y（4 位年）、%m（2 位月）、%d（2 位日）、%H（24 小时）、%i（分钟）、%s（秒）。

4.流程函数★

示例场景：给用户按年龄分组，查询时显示 “年龄阶段”

SELECT 
  username,
  age,
  CASE 
    WHEN age < 18 THEN '少年'
    WHEN age < 30 THEN '青年'
    WHEN age < 50 THEN '中年'
    ELSE '老年' 
  END AS age_stage
FROM user;

约束

约束是对表中字段的规则限制，防止插入无效数据（如年龄为负数，邮箱重复等），共5类约束

5个约束

主键约束 primary key 非空且唯一

唯一约束 unique(可为null，在mysql中空值能出现多次，但是在其他数据库系统中，只能出现1次)

非空约束 not null

默认约束 default

外键约束 foreign key 关联两张表，保证从表的数据依赖主表的存在

操作约束

建表时添加约束

create table if not exists user (
    id int primary key,
    username varchar(50) not null unique,
    age int default 0,
    email varchar(100) unique
);

建表后添加约束

alter table 表名 add constraint 约束名 约束类型（字段）;

（约束名自定义，便于删除）

alter table user add constraint user_age_not_null check (age is not null);

外键约束

外键约束是多表关联的核心，用于保证从表数据依赖于主表的合法性（比如订单表中的user_id必须是用户表中存在的id，不能插入不存在的用户的订单）

主表：被依赖的表（如用户表），外键关联的是主表的主键或唯一键

从表：依赖主表的报表（如订单表），从表中添加外键字段

创建外键约束的语法

1. 建表时添加

   foreign key (从表字段) references 主表 (主表字段);

create table if not exists `order` (
    id int primary key,
    order_no varchar(20) not null unique,
    user_id int,
    amount decimal(10,2) not null,
    -- 外键约束: order.user_id关联user.id
    foreign key (user_id) references user (id)
);

2. 建表后添加

   alter table 表名 add constraint foreign key (user_id) references user(id);

alter table `order`
add constraint fk_order_user
foreign key (user_id)
references user(id);

设置外键的删除/更新行为

当主表字段数据变化时，从表外键的行为

on delete/update restrict 默认行为：禁止操作

on delete/update cascade 级联操作：主表删除/更新时，从表跟随更新

on delete/update set null 置空操作（需保证外键字段可以为空）

on delete/update set default 置默认值（需提前为外键添加字段默认值）

ALTER TABLE `order` 
DROP FOREIGN KEY fk_order_user; -- 先删除原有外键

-- 添加新外键，设置级联删除和更新
ALTER TABLE `order` 
ADD CONSTRAINT fk_order_user 
FOREIGN KEY (user_id) 
REFERENCES user(id)
ON DELETE CASCADE -- 主表用户删除时，从表关联订单也删除
ON UPDATE CASCADE; -- 主表用户id更新时，从表关联订单的user_id也更新

多表查询

多对多表设计

-- 学生表
CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL);
-- 课程表
CREATE TABLE course (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
-- 中间表（关联学生和课程）
CREATE TABLE student_course (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id), -- 复合主键，保证一个学生不会重复选同一门课
    FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE
);

1.内连接

查询两张表中满足关联条件的交集数据，即主表从表都有对应记录的数据。

1. 隐式内连接：where加关联条件

SELECT u.username, o.order_no 
FROM user u, `order` o
WHERE u.id = o.user_id;

2. 显式内连接：inner join ... on（更推荐）

select u.id, u.username, o.order_no
from user u
inner join `order` o on u.id = o.user_id;

2.外连接

查询交集数据，并保留一张表的完整数据

from 左表 left join 右表 on...，保留左表全部，添加交集右表，若无则填充null

from 左表 right join 右表 on...，保留右表全部，添加交集左表，若无则填充null

select u.username, o.order_no
from user u
left join `order` o on u.id = o.user_id;
-- 如果1个用户有多个订单，这个用户会在结果集中出现多次，每次对应一个不同的订单

3.自连接

将一张表当做两张表进行查询，适用于表中数据存在自关联关系，如员工id和上级id

select
  e.name as '员工姓名'
  m.name as '上级姓名'
from emp e
left join emp m
on e.manager_id = m.id;

4.联合查询

union：合并后自动去重（效率低）

union all：合并后保留所有数据（效率高）

-- 子查询1：年龄<20的用户
select id, username from user where age < 20
union all
-- 子查询2：有订单的用户（通过DISTINCT去重，避免一个用户多个订单导致重复）
select distinct u.id, u.username from user u join `order` o on u.id = o.user_id;

UNION 操作不是取字段的交集，而是合并行，但要求两个查询的列结构必须兼容：两个 SELECT 语句必须返回相同数量的列；对应位置的列数据类型应该兼容，但列名可以不同。

5.子查询

将一个查询的结果，作为另一个查询的条件或数据源，按返回结果的格式，分为标量子查询、列子查询、行子查询、表子查询

1.标量子查询

子查询返回单个值（1行1列），可用于where后作为条件中的量

-- 用最高金额作为条件，查对应订单
select order_no, amount
from `order`
where amount = (select max(amount) from `order`);

2.列子查询

子查询返回1列n行，可用于where后配合in等运算符

-- 查询 “有订单的用户” 的姓名和年龄
select username, age
from user
where id in (select distinct user_id from `order`);

3.行子查询

子查询返回n列1行，用于where中匹配多个字段的组合

-- 查询与 张三 年龄和城市都相同的用户
select username
from user
where (age, city) = (select age, city from user where username = '张三');

4.表子查询

子查询返回n行n列，可作为临时表起别名用于from后

-- 查询2024年5月的订单，并关联用户表获取用户名
select u.username, temp.order_no
from user u
join (
    select order_no, user_id
    from `order`
    where date_format(create_time, '%Y-%m') = '2024-05'
) temp on u.id = temp.user_id;

练习

综合运用 “多表连接 + 子查询 + 聚合函数 + 排序分页”，解决复杂业务需求。

示例需求：查询 “2024 年 5 月每个用户的订单总金额，只保留总金额≥500 的用户，按总金额降序，显示用户名、总金额、订单数，分页显示第 1 页（每页 10 条）”

SELECT 
  u.username,
  SUM(o.amount) AS total_amount, -- 总金额
  COUNT(o.id) AS order_count -- 订单数
FROM user u
LEFT JOIN `order` o 
  ON u.id = o.user_id 
  AND DATE_FORMAT(o.create_time, '%Y-%m') = '2024-05' -- 只关联5月的订单
GROUP BY u.id, u.username -- 按用户分组（需包含SELECT中所有非聚合字段）
HAVING total_amount >= 500 -- 筛选总金额≥500的用户
ORDER BY total_amount DESC -- 按总金额降序
LIMIT 0, 10; -- 第1页，每页10条

事务

事务是数据库原子性操作的核心机制

MySQL中，InnoDB存储引擎支持事务（MyISAM不支持），且默认自动提交事务（每执行一条insert/update/delete语句，都会自动提交为一个独立事务），需要手动关闭自动提交以控制事务范围

start transaction; 或 begin; 开启事务（关闭自动提交）

commit; 提交事务

rollback; 回滚事务

set autocommit = 0; 关闭当前会话的自动提交（临时生效，会话后恢复默认）

start transaction;

update account set balance = balance - 100 where id = 1;
update account set balance = balance + 100 where id = 2;

select balance from account where id in(1,2);

commit;

若执行中报错，MySQL会自动回滚。

若未执行commit或rollback就关闭会话，MySQL会自动回滚。

ACID-事务四大特性

原子性 Atomicty(事务中操作全成或全败)

一致性 Consistency(事务执行前后，数据满足业务规则约束，如转账前后总金额不变)

隔离性 Isolation(事务并发执行时，互不干扰)

持久性 Durability(事务提交后，数据持久化)

并发事务问题

脏读（Dirty Read）：A读到B未提交的数据，若B回滚，则A读到的就是脏数据

不可重复读（Non-Repeatable Read）：A多次读同一个数据，若B在期间修改了数据，导致A前后读到的数据不同

幻读（Phantom Read）：A多次读同一条件的数据，若B在期间新增了符合条件的数据，导致A前后读到的数据行数不同

数据库通过设置不同的事务隔离级别来解决这些问题，级别越高，并发性越低。

查看隔离级别

select @@transaction_isolation; (MySQL 8.0+)

select @@tx_isolation; (MySQL 5.x)

修改隔离级别

set [session/global] transaction isolation level 隔离级别;

set session transaction isolation level read committed;

InnoDB对幻读的优化

MySQL默认隔离级别可重复读下，InnoDB通过Next-Key Lock（间隙锁）机制，避免了常规幻读（即通过insert新增符合条件的数据），但无法完全禁止通过update更新成符合条件的数据，需要通过串行化隔离级别彻底解决。