数据库基础与 MySQL 常用 SQL

Honkers · 2026-6-23 22:57:17

第一章.数据库基础

1.1 数据库的介绍

1.数据库:
用来按照一定结构存储、管理和查询数据的软件系统。
2.常见关系型数据库:
MySQL
Oracle
SQL Server
PostgreSQL
3.关系型数据库:
使用表存储数据,表和表之间可以建立关系。

复制代码

1.2 数据库表

1.表 table:
数据库中存放数据的基本单位。
2.表由以下部分构成:
a.表名
b.列名,也叫字段名
c.每列的数据类型
d.行,也叫记录

复制代码

id	username	password
1	tom	111
2	jack	222

1.3 表和 Java 类的对应关系

数据库	Java
表名	类名
列名	属性名
列类型	属性类型
一行数据	一个 JavaBean 对象
单元格数据	对象的属性值

public class User {
private Integer id;
private String username;
private String password;
}

复制代码

// 表中的两行数据可以理解成两个对象:
User user1 = new User(1, "tom", "111");
User user2 = new User(2, "jack", "222");

复制代码

1.4 查询结果如何返回给 Java

1.数据库执行 SELECT 查询。
2.查询结果是一张临时结果表。
3.Java 程序逐行读取结果。
4.每一行封装成一个 JavaBean 对象。
5.多个对象放入 List 集合。
6.最终返回给页面或接口使用。

复制代码

List<User> users = new ArrayList<>();
users.add(new User(1, "tom", "111"));
users.add(new User(2, "jack", "222"));

复制代码

第二章.SQL 语言

2.1 SQL 的介绍

1.SQL:
Structured Query Language,结构化查询语言。
2.作用:
用来操作关系型数据库。
3.注意:
不同数据库都遵守 SQL 标准,
但也会有自己的语法差异,这些差异叫 SQL 方言。

复制代码

2.2 SQL 分类

分类	全称	作用	常见关键字
DDL	Data Definition Language	定义数据库对象	CREATE、ALTER、DROP
DML	Data Manipulation Language	操作表中数据	INSERT、UPDATE、DELETE
DQL	Data Query Language	查询表中数据	SELECT、FROM、WHERE
DCL	Data Control Language	权限控制	GRANT、REVOKE
TCL	Transaction Control Language	事务控制	COMMIT、ROLLBACK

2.3 SQL 通用语法

-- 1.SQL 可以单行或多行书写,通常以分号结尾。
SELECT * FROM user;
-- 2.MySQL 关键字不区分大小写,但建议关键字大写。
SELECT username, password FROM user;
-- 3.单行注释
# 这是 MySQL 注释
-- 这也是 SQL 注释
-- 4.多行注释
/*
这是多行注释
*/

复制代码

1.库名、表名、字段名可以使用反引号包裹。
2.字符串和日期值通常使用单引号。
3.SQL 中判断相等使用 =,不是 Java 中的 ==。

复制代码

2.4 MySQL 常用数据类型

类型	说明	示例
INT	整数	age INT
BIGINT	大整数	id BIGINT
DOUBLE	双精度小数	score DOUBLE
DECIMAL(m, d)	精确小数，适合金额	price DECIMAL(10,2)
CHAR(n)	固定长度字符串	gender CHAR(1)
VARCHAR(n)	可变长度字符串	name VARCHAR(20)
TEXT	长文本	content TEXT
DATE	年月日	2026-06-22
DATETIME	年月日时分秒	2026-06-22 10:30:00
TIMESTAMP	时间戳	常用于创建时间、修改时间

金额不要用 DOUBLE

金额、余额、价格等精确小数，优先使用 DECIMAL，不要使用 DOUBLE，避免精度误差。

第三章.DDL 数据定义语言

3.1 数据库操作

-- 查看所有数据库
SHOW DATABASES;
-- 创建数据库
CREATE DATABASE java_study;
-- 如果不存在才创建
CREATE DATABASE IF NOT EXISTS java_study;
-- 使用数据库
USE java_study;
-- 查看当前正在使用的数据库
SELECT DATABASE();
-- 删除数据库
DROP DATABASE java_study;
-- 如果存在才删除
DROP DATABASE IF EXISTS java_study;

复制代码

3.2 创建表

CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
age INT,
create_time DATETIME
);

复制代码

1.CREATE TABLE:
创建表。
2.字段格式:
字段名数据类型约束。
3.AUTO_INCREMENT:
自增长,通常配合整数主键使用。

复制代码

3.3 查看表结构

-- 查看当前库中的所有表
SHOW TABLES;
-- 查看建表语句
SHOW CREATE TABLE user;
-- 查看表字段结构
DESC user;

复制代码

3.4 修改表结构

-- 添加字段
ALTER TABLE user ADD email VARCHAR(50);
-- 修改字段类型
ALTER TABLE user MODIFY email VARCHAR(100);
-- 修改字段名和类型
ALTER TABLE user CHANGE email user_email VARCHAR(100);
-- 删除字段
ALTER TABLE user DROP user_email;
-- 修改表名
ALTER TABLE user RENAME TO sys_user;

复制代码

3.5 删除表

-- 删除表
DROP TABLE sys_user;
-- 如果表存在才删除
DROP TABLE IF EXISTS sys_user;

复制代码

第四章.DML 数据操作语言

4.1 插入数据

-- 指定字段插入
INSERT INTO product (pid, pname, price)
VALUES (1, '苹果', 6.50);
-- 不指定字段时,值必须覆盖所有列并保持顺序
INSERT INTO product
VALUES (2, '梨', 5.00, '水果');
-- 一次插入多条数据
INSERT INTO product (pid, pname, price)
VALUES
(3, '香蕉', 4.50),
(4, '西瓜', 20.00),
(5, '草莓', 18.80);

复制代码

字符串使用单引号

SQL 中字符串建议使用单引号。Java 拼接 SQL 时，双引号会和 Java 字符串本身冲突。

4.2 修改数据

-- 修改指定商品价格
UPDATE product
SET price = 7.00
WHERE pid = 1;
-- 同时修改多个字段
UPDATE product
SET price = 8.00, category_name = '新鲜水果'
WHERE pname = '苹果';

复制代码

UPDATE 一定注意 WHERE

如果不写 WHERE，会修改整张表的所有记录。

4.3 删除数据

-- 删除指定数据
DELETE FROM product
WHERE pid = 1;
-- 删除价格大于 100 的商品
DELETE FROM product
WHERE price > 100;
-- 删除整张表数据
DELETE FROM product;

复制代码

4.4 DELETE、TRUNCATE、DROP 区别

语句	作用	是否删除表结构	是否可加 WHERE
DELETE	删除表中数据	否	可以
TRUNCATE TABLE	清空整张表数据	否	不可以
DROP TABLE	删除整张表	是	不可以

TRUNCATE TABLE product;
DROP TABLE product;

复制代码

第五章.约束

本章目标

约束用于限制字段数据，保证数据合法、唯一、非空以及多表之间的引用关系正确。

5.1 主键约束

1.关键字:
PRIMARY KEY
2.特点:
a.一张表通常应该有一个主键
b.主键用于唯一标识一行数据
c.主键不能重复
d.主键不能为 NULL

复制代码

创建表时指定主键

CREATE TABLE category (
cid INT PRIMARY KEY,
cname VARCHAR(20)
);

复制代码

约束区域指定主键

CREATE TABLE category (
cid INT,
cname VARCHAR(20),
PRIMARY KEY (cid)
);

复制代码

修改表添加主键

CREATE TABLE category (
cid INT,
cname VARCHAR(20)
);
ALTER TABLE category ADD PRIMARY KEY (cid);

复制代码

5.2 联合主键

1.联合主键:
多个字段合起来作为一个主键。
2.特点:
多个字段的组合不能重复,并且主键字段不能为 NULL。

复制代码

CREATE TABLE student_course (
student_id INT,
course_id INT,
score INT,
PRIMARY KEY (student_id, course_id)
);

复制代码

5.3 删除主键

ALTER TABLE student_course DROP PRIMARY KEY;

复制代码

5.4 自增长约束

1.关键字:
AUTO_INCREMENT
2.特点:
a.通常配合整数主键使用
b.插入数据时主键可以不写
c.MySQL 自动生成下一个编号

复制代码

CREATE TABLE student (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);
INSERT INTO student (sname) VALUES ('tom');
INSERT INTO student (sname) VALUES ('jack');

复制代码

5.5 非空约束

1.关键字:
NOT NULL
2.特点:
被约束字段不能插入 NULL。
3.注意:
NULL、空字符串 ''、字符串 'null' 不是一回事。

复制代码

CREATE TABLE student (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
score INT
);
INSERT INTO student (sname, score) VALUES ('tom', 100);
INSERT INTO student (sname, score) VALUES ('', 99);
INSERT INTO student (sname, score) VALUES ('null', 97);

复制代码

5.6 唯一约束

1.关键字:
UNIQUE
2.特点:
被唯一约束修饰的字段不能重复。
3.和主键区别:
a.一张表只能有一个主键,但可以有多个唯一约束
b.主键不能为 NULL
c.唯一约束在 MySQL 中可以有多个 NULL

复制代码

CREATE TABLE role (
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(20) UNIQUE
);
INSERT INTO role (rname) VALUES ('护士');
INSERT INTO role (rname) VALUES ('教师');

复制代码

5.7 默认值约束

1.关键字:
DEFAULT
2.作用:
插入数据时如果不指定该字段,自动使用默认值。

复制代码

CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
status INT DEFAULT 1
);
INSERT INTO account (username) VALUES ('tom');

复制代码

5.8 外键约束

1.关键字:
FOREIGN KEY
2.作用:
让从表字段引用主表主键,保证多表之间的数据关系正确。
3.常见关系:
分类表是主表。
商品表是从表。
商品表中的 category_id 引用分类表的 cid。

复制代码

CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL
);
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
price DECIMAL(10, 2),
category_id INT,
CONSTRAINT fk_product_category
FOREIGN KEY (category_id)
REFERENCES category (cid)
);

复制代码

第六章.DQL 基础查询

6.1 准备案例表

CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
price DECIMAL(10, 2),
category_name VARCHAR(20),
stock INT,
create_time DATETIME
);
INSERT INTO product (pname, price, category_name, stock, create_time)
VALUES
('苹果', 6.50, '水果', 100, '2026-06-01 10:00:00'),
('香蕉', 4.50, '水果', 80, '2026-06-02 10:00:00'),
('牙刷', 9.90, '日用品', 200, '2026-06-03 10:00:00'),
('洗面奶', 59.00, '化妆品', 50, '2026-06-04 10:00:00'),
('电视', 2999.00, '家电', 10, '2026-06-05 10:00:00');

复制代码

6.2 中间遗漏补齐:从建库到查询的完整练习

练习目标

这一段把原笔记中间容易断开的内容串起来：先建库建表，再插入、修改、删除，最后做基础查询。

-- 1.创建并使用数据库
CREATE DATABASE IF NOT EXISTS db_java_basic;
USE db_java_basic;
-- 2.创建商品分类表
CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL UNIQUE
);
-- 3.创建商品表
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
price DECIMAL(10, 2),
stock INT DEFAULT 0,
category_id INT,
create_time DATETIME,
CONSTRAINT fk_product_category
FOREIGN KEY (category_id)
REFERENCES category (cid)
);
-- 4.插入分类数据
INSERT INTO category (cname)
VALUES ('水果'), ('化妆品'), ('家电');
-- 5.插入商品数据
INSERT INTO product (pname, price, stock, category_id, create_time)
VALUES
('苹果', 6.50, 100, 1, '2026-06-01 10:00:00'),
('香蕉', 4.50, 80, 1, '2026-06-02 10:00:00'),
('洗面奶', 59.00, 50, 2, '2026-06-03 10:00:00'),
('电视', 2999.00, 10, 3, '2026-06-04 10:00:00');
-- 6.修改商品库存
UPDATE product
SET stock = stock - 1
WHERE pname = '苹果';
-- 7.删除库存为 0 的商品
DELETE FROM product
WHERE stock = 0;
-- 8.查询商品和分类
SELECT p.pid, p.pname, p.price, p.stock, c.cname
FROM product p
JOIN category c ON p.category_id = c.cid
ORDER BY p.price DESC;

复制代码

这段练习覆盖:
1.创建数据库。
2.创建主表和从表。
3.主键、自增长、非空、唯一、默认值、外键。
4.INSERT、UPDATE、DELETE。
5.JOIN 多表查询。

复制代码

6.3 简单查询

-- 查询所有列
SELECT * FROM product;
-- 查询指定列
SELECT pid, pname, price FROM product;
-- 去重查询
SELECT DISTINCT category_name FROM product;
-- 计算查询
SELECT pname, price + 100 AS new_price FROM product;
-- 给表取别名
SELECT p.pname, p.price FROM product AS p;

复制代码

1.SELECT 查询出来的结果是一张临时结果表。
2.* 表示查询所有列,实际开发中更推荐明确写出需要的列。
3.AS 可以给列或表取别名,AS 可以省略。

复制代码

6.4 条件查询

-- 查询价格大于 10 的商品
SELECT * FROM product WHERE price > 10;
-- 查询价格在 5 到 60 之间的商品,包含边界
SELECT * FROM product WHERE price BETWEEN 5 AND 60;
-- 查询分类是水果或家电的商品
SELECT * FROM product WHERE category_name IN ('水果', '家电');
-- 查询商品名中包含面的商品
SELECT * FROM product WHERE pname LIKE '%面%';
-- 查询库存不为空的商品
SELECT * FROM product WHERE stock IS NOT NULL;

复制代码

运算符	说明
=	等于
!= 或 <>	不等于
>、>=、<、<=	比较大小
BETWEEN ... AND ...	区间范围，含头含尾
IN (...)	在指定集合中
LIKE	模糊查询
IS NULL	判断为空
IS NOT NULL	判断不为空
AND	并且
OR	或者
NOT	非

6.5 LIKE 通配符

通配符	说明	示例
%	任意 0 个或多个字符	LIKE '张%'
_	任意 1 个字符	LIKE '_三'

-- 查询姓张的人
SELECT * FROM user WHERE username LIKE '张%';
-- 查询名字中包含香的商品
SELECT * FROM product WHERE pname LIKE '%香%';
-- 查询第二个字是想的数据
SELECT * FROM product WHERE pname LIKE '_想%';
-- 查询商品名正好四个字的数据
SELECT * FROM product WHERE pname LIKE '____';

复制代码

6.6 排序查询

1.关键字:
ORDER BY
2.排序规则:
ASC: 升序,默认。
DESC: 降序。
3.执行顺序:
先查询,最后排序。

复制代码

-- 按价格升序
SELECT * FROM product ORDER BY price ASC;
-- 按库存降序
SELECT * FROM product ORDER BY stock DESC;
-- 先按分类升序,同分类内按价格降序
SELECT * FROM product
ORDER BY category_name ASC, price DESC;

复制代码

6.7 聚合查询

函数	说明
COUNT(*)	统计行数
SUM(列名)	求和
AVG(列名)	求平均值
MAX(列名)	求最大值
MIN(列名)	求最小值

-- 统计商品总数
SELECT COUNT(*) FROM product;
-- 统计库存总数
SELECT SUM(stock) FROM product;
-- 查询平均价格、最高价格、最低价格
SELECT AVG(price), MAX(price), MIN(price) FROM product;

复制代码

COUNT 的常见写法

COUNT(*) 统计行数；COUNT(列名) 只统计该列不为 NULL 的行。

6.8 分组查询

1.关键字:
GROUP BY
2.作用:
把相同字段值的数据合并为一组。
3.WHERE 和 HAVING 区别:
WHERE 在分组前过滤。
HAVING 在分组后过滤。

复制代码

-- 按分类统计商品数量
SELECT category_name, COUNT(*) AS total
FROM product
GROUP BY category_name;
-- 按分类统计平均价格,只看平均价格大于 20 的分类
SELECT category_name, AVG(price) AS avg_price
FROM product
GROUP BY category_name
HAVING AVG(price) > 20;
-- 先过滤库存大于 0 的商品,再按分类统计
SELECT category_name, COUNT(*) AS total
FROM product
WHERE stock > 0
GROUP BY category_name;

复制代码

6.9 分页查询

1.关键字:
LIMIT
2.语法:
SELECT * FROM 表名 LIMIT 起始索引, 每页条数;
3.起始索引:
(当前页 - 1) * 每页条数

复制代码

-- 第 1 页,每页 3 条
SELECT * FROM product LIMIT 0, 3;
-- 第 2 页,每页 3 条
SELECT * FROM product LIMIT 3, 3;
-- 第 3 页,每页 3 条
SELECT * FROM product LIMIT 6, 3;

复制代码

int currentPage = 2;
int pageSize = 5;
int startRow = (currentPage - 1) * pageSize;
// 总页数 = 向上取整(总记录数 / 每页条数)
int totalPage = (int) Math.ceil(totalSize * 1.0 / pageSize);

复制代码

6.10 SQL 编写顺序和执行顺序

SELECT category_name, COUNT(*) AS total
FROM product
WHERE price > 5
GROUP BY category_name
HAVING COUNT(*) >= 1
ORDER BY total DESC
LIMIT 0, 5;

复制代码

阶段	顺序
书写顺序	SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
大致执行顺序	FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

第七章.MySQL 常用函数

7.1 字符串函数

函数	说明
CONCAT(str1, str2)	拼接字符串
LENGTH(str)	获取字节长度
CHAR_LENGTH(str)	获取字符长度
LOWER(str)	转小写
UPPER(str)	转大写
SUBSTRING(str, start, len)	截取字符串
TRIM(str)	去除首尾空格

SELECT CONCAT(pname, '-', category_name) AS info FROM product;
SELECT CHAR_LENGTH('数据库') AS len;
SELECT SUBSTRING('abcdef', 2, 3) AS result;

复制代码

7.2 数值函数

函数	说明
ROUND(num, d)	四舍五入
CEIL(num)	向上取整
FLOOR(num)	向下取整
ABS(num)	绝对值

SELECT ROUND(3.14159, 2);
SELECT CEIL(10.1);
SELECT FLOOR(10.9);

复制代码

7.3 日期函数

函数	说明
NOW()	当前日期时间
CURDATE()	当前日期
CURTIME()	当前时间
YEAR(date)	获取年份
MONTH(date)	获取月份
DAY(date)	获取日期中的天
DATE_FORMAT(date, format)	格式化日期

SELECT NOW();
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT * FROM product WHERE YEAR(create_time) = 2026;

复制代码

7.4 流程函数

-- IF(条件, 成立结果, 不成立结果)
SELECT pname, IF(stock > 0, '有货', '无货') AS stock_status
FROM product;
-- CASE WHEN
SELECT
pname,
CASE
WHEN price >= 1000 THEN '高价'
WHEN price >= 100 THEN '中价'
ELSE '低价'
END AS price_level
FROM product;

复制代码

第八章.数据库三范式

本章目标

范式用于指导表结构设计，核心目标是减少数据冗余、避免更新异常、让表之间职责更清晰。

8.1 第一范式:字段保持原子性

1.第一范式:
表中的每个字段都应该是不可再拆分的原子值。
2.不符合示例:
address_phone = '北京市昌平区xxx小区 1501087xxxx'
3.符合示例:
province
city
detail_address
phone

复制代码

8.2 第二范式:每行能被唯一区分

1.第二范式:
在满足第一范式基础上,每行数据应该能被唯一标识。
2.常见做法:
给每张表设计主键。
3.目的:
避免无法准确定位某一行数据。

复制代码

CREATE TABLE student (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL
);

复制代码

8.3 第三范式:非主键字段不能相互依赖

1.第三范式:
在满足第二范式基础上,非主键字段不能依赖其他非主键字段。
2.不符合示例:
员工表中同时保存 department_name 和 department_manager。
department_manager 依赖 department_name,不直接依赖员工主键。
3.解决:
拆分部门表,员工表只保存 department_id。

复制代码

CREATE TABLE department (
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20) NOT NULL,
manager VARCHAR(20)
);
CREATE TABLE employee (
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) NOT NULL,
department_id INT
);

复制代码

8.4 三范式总结

1.第一范式:
一列数据不要混多个含义。
2.第二范式:
每张表要能唯一定位一行数据。
3.第三范式:
一张表不要记录多张表的信息。

复制代码

范式不是越高越好

业务开发中通常先保证设计清晰、减少冗余；在性能要求很高的场景，也可能为了查询效率适当冗余字段。

第九章.多表关系与外键

9.1 一对一关系

1.一对一:
一条主表数据对应一条从表数据。
2.示例:
用户表 user
用户详情表 user_profile
3.建表方式:
在其中一张表保存另一张表的主键,并加唯一约束。

复制代码

CREATE TABLE user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL
);
CREATE TABLE user_profile (
id INT PRIMARY KEY AUTO_INCREMENT,
uid INT UNIQUE,
real_name VARCHAR(20),
id_card VARCHAR(18),
FOREIGN KEY (uid) REFERENCES user (uid)
);

复制代码

9.2 一对多关系

1.一对多:
一个分类包含多个商品。
一个商品只属于一个分类。
2.主表:
一的一方,例如分类表。
3.从表:
多的一方,例如商品表。
4.外键位置:
外键建在从表中。

复制代码

CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL
);
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
category_id INT,
FOREIGN KEY (category_id) REFERENCES category (cid)
);

复制代码

9.3 多对多关系

1.多对多:
一个学生可以选多门课程。
一门课程可以被多个学生选择。
2.建表方式:
创建中间表。
3.中间表:
保存两张主表的主键作为外键。

复制代码

CREATE TABLE student (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL
);
CREATE TABLE course (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL
);
CREATE TABLE student_course (
sid INT,
cid INT,
score INT,
PRIMARY KEY (sid, cid),
FOREIGN KEY (sid) REFERENCES student (sid),
FOREIGN KEY (cid) REFERENCES course (cid)
);

复制代码

9.4 外键删除和更新策略

1.RESTRICT:
如果从表有引用,禁止删除或更新主表数据。
2.CASCADE:
主表删除或更新时,从表跟着删除或更新。
3.SET NULL:
主表删除或更新时,从表外键设置为 NULL。

复制代码

CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
category_id INT,
CONSTRAINT fk_product_category
FOREIGN KEY (category_id)
REFERENCES category (cid)
ON DELETE SET NULL
ON UPDATE CASCADE
);

复制代码

谨慎使用级联删除

ON DELETE CASCADE 会在删除主表数据时自动删除从表数据，真实业务中要先确认数据是否允许被连带删除。

第十章.多表查询

10.1 准备多表数据

CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20) NOT NULL
);
CREATE TABLE product (
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(30) NOT NULL,
price DECIMAL(10, 2),
category_id INT
);
INSERT INTO category (cname) VALUES ('水果'), ('化妆品'), ('家电');
INSERT INTO product (pname, price, category_id)
VALUES
('苹果', 6.50, 1),
('香蕉', 4.50, 1),
('洗面奶', 59.00, 2),
('电视', 2999.00, 3),
('未知商品', 10.00, NULL);

复制代码

10.2 交叉查询

1.语法:
SELECT 列名 FROM 表A, 表B;
2.问题:
会产生笛卡尔积。
3.解决:
添加连接条件。

复制代码

-- 会产生笛卡尔积
SELECT * FROM category, product;
-- 添加连接条件后,相当于隐式内连接
SELECT *
FROM category c, product p
WHERE c.cid = p.category_id;

复制代码

10.3 内连接查询

1.内连接:
查询两张表中满足连接条件的数据。
2.隐式内连接:
SELECT 列名 FROM 表A, 表B WHERE 连接条件;
3.显式内连接:
SELECT 列名 FROM 表A JOIN 表B ON 连接条件;

复制代码

-- 隐式内连接
SELECT p.pid, p.pname, p.price, c.cname
FROM product p, category c
WHERE p.category_id = c.cid;
-- 显式内连接
SELECT p.pid, p.pname, p.price, c.cname
FROM product p
JOIN category c ON p.category_id = c.cid;
-- 查询化妆品分类下的商品
SELECT p.pid, p.pname, p.price, c.cname
FROM product p
JOIN category c ON p.category_id = c.cid
WHERE c.cname = '化妆品';

复制代码

10.4 外连接查询

1.左外连接:
查询左表全部数据,以及右表满足条件的数据。
2.右外连接:
查询右表全部数据,以及左表满足条件的数据。
3.关键字:
LEFT JOIN
RIGHT JOIN

复制代码

-- 查询所有商品,即使商品没有分类也显示
SELECT p.pid, p.pname, c.cname
FROM product p
LEFT JOIN category c ON p.category_id = c.cid;
-- 查询所有分类,即使分类下没有商品也显示
SELECT c.cid, c.cname, p.pname
FROM product p
RIGHT JOIN category c ON p.category_id = c.cid;

复制代码

10.5 UNION 联合查询

1.UNION:
合并多条查询语句的结果,并去除重复数据。
2.UNION ALL:
合并结果,不去重。
3.注意:
多条 SELECT 的列数和列类型需要能对应。

复制代码

-- 模拟全外连接:左外连接结果 UNION 右外连接结果
SELECT c.cid, c.cname, p.pid, p.pname
FROM category c
LEFT JOIN product p ON c.cid = p.category_id
UNION
SELECT c.cid, c.cname, p.pid, p.pname
FROM category c
RIGHT JOIN product p ON c.cid = p.category_id;

复制代码

10.6 子查询

1.子查询:
一条 SELECT 语句作为另一条 SQL 的一部分。
2.常见位置:
WHERE 后面作为条件。
FROM 后面作为临时表。

复制代码

子查询作为条件

-- 查询化妆品分类下的商品
SELECT *
FROM product
WHERE category_id = (
SELECT cid FROM category WHERE cname = '化妆品'
);
-- 查询化妆品和家电分类下的商品
SELECT *
FROM product
WHERE category_id IN (
SELECT cid FROM category WHERE cname IN ('化妆品', '家电')
);

复制代码

子查询作为临时表

-- 查询每个分类的商品数量,再筛选数量大于 1 的分类
SELECT temp.category_id, temp.total
FROM (
SELECT category_id, COUNT(*) AS total
FROM product
GROUP BY category_id
) AS temp
WHERE temp.total > 1;

复制代码

10.7 多表查询选择

需求	推荐写法
只要两表匹配的数据	内连接
保留左表全部数据	左外连接
保留右表全部数据	右外连接
查询条件来自另一条 SQL	子查询
合并多个查询结果	UNION 或 UNION ALL

第十一章.事务

本章目标

事务用于保证一组 SQL 操作要么全部成功，要么全部失败，常见于转账、下单、扣库存等场景。

11.1 事务的介绍

1.事务:
一组数据库操作组成的逻辑执行单元。
2.核心目标:
要么全部成功提交,要么全部失败回滚。
3.典型场景:
张三给李四转账 100 元。
张三扣 100 和李四加 100 必须同时成功。

复制代码

11.2 事务基本操作

-- 开启事务
START TRANSACTION;
-- 张三扣款
UPDATE account SET balance = balance - 100 WHERE name = '张三';
-- 李四收款
UPDATE account SET balance = balance + 100 WHERE name = '李四';
-- 提交事务
COMMIT;

复制代码

-- 如果中途出现问题
ROLLBACK;

复制代码

11.3 事务四大特性 ACID

特性	说明
原子性 Atomicity	一组操作不可拆分，要么都成功，要么都失败
一致性 Consistency	事务前后数据状态必须合法
隔离性 Isolation	多个事务并发执行时互不干扰到规定程度
持久性 Durability	事务提交后数据永久保存

11.4 并发事务问题

问题	说明
脏读	一个事务读到另一个事务未提交的数据
不可重复读	同一事务两次读取同一行，结果不同
幻读	同一事务两次范围查询，行数不同

11.5 事务隔离级别

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	避免	可能	可能
REPEATABLE READ	避免	避免	可能
SERIALIZABLE	避免	避免	避免

-- 查看当前事务隔离级别
SELECT @@transaction_isolation;
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

复制代码

MySQL 默认隔离级别

InnoDB 默认隔离级别通常是 REPEATABLE READ。

第十二章.索引

本章目标

索引用于提高查询效率，但会占用空间，也会影响新增、修改、删除速度。

12.1 索引的介绍

1.索引:
帮助数据库快速定位数据的数据结构。
2.优点:
提高查询速度。
3.缺点:
a.占用磁盘空间
b.新增、修改、删除时需要维护索引

复制代码

12.2 常见索引类型

类型	说明
主键索引	主键自动创建
唯一索引	保证字段值唯一
普通索引	只提高查询效率
联合索引	多个字段组成一个索引

-- 创建普通索引
CREATE INDEX idx_product_name ON product (pname);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_username ON user (username);
-- 创建联合索引
CREATE INDEX idx_product_category_price ON product (category_id, price);
-- 删除索引
DROP INDEX idx_product_name ON product;

复制代码

12.3 查看 SQL 是否使用索引

EXPLAIN
SELECT *
FROM product
WHERE pname = '苹果';

复制代码

EXPLAIN 可以查看 SQL 的执行计划。
入门阶段重点关注:
1.type:
访问类型,一般越接近 const/ref/range 越好,ALL 表示全表扫描。
2.key:
实际使用的索引。
3.rows:
预计扫描行数。

复制代码

12.4 索引使用建议

适合加索引:
1.经常作为 WHERE 条件的字段。
2.经常用于 JOIN 连接的字段。
3.经常用于 ORDER BY、GROUP BY 的字段。
4.数据量较大的表。
不适合加索引:
1.数据量很小的表。
2.频繁更新但很少查询的字段。
3.区分度很低的字段,例如性别。

复制代码

12.5 最左前缀原则

1.联合索引:
INDEX(a, b, c)
2.可以较好使用索引的条件:
WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?
3.不符合最左前缀:
WHERE b = ?
WHERE c = ?

复制代码

CREATE INDEX idx_order_user_status_time
ON orders (user_id, status, create_time);
-- 符合最左前缀
SELECT * FROM orders WHERE user_id = 1 AND status = 1;
-- 不符合最左前缀
SELECT * FROM orders WHERE status = 1;

复制代码

第十三章.视图

13.1 视图的介绍

1.视图:
基于 SELECT 查询结果创建的虚拟表。
2.特点:
a.视图本身通常不直接保存数据
b.可以简化复杂查询
c.可以隐藏部分字段,控制数据暴露范围

复制代码

13.2 创建和使用视图

CREATE VIEW v_product_category AS
SELECT p.pid, p.pname, p.price, c.cname
FROM product p
JOIN category c ON p.category_id = c.cid;
SELECT * FROM v_product_category;

复制代码

13.3 修改和删除视图

-- 修改视图
CREATE OR REPLACE VIEW v_product_category AS
SELECT p.pid, p.pname, c.cname
FROM product p
JOIN category c ON p.category_id = c.cid;
-- 删除视图
DROP VIEW v_product_category;

复制代码

视图不是性能优化万能方案

视图主要用于封装查询和控制字段暴露，不等于一定能提升性能。复杂视图仍然需要关注底层 SQL 和索引。

[数据库] 数据库基础与 MySQL 常用 SQL

第一章.数据库基础

1.1 数据库的介绍

1.2 数据库表

1.3 表和 Java 类的对应关系

1.4 查询结果如何返回给 Java

第二章.SQL 语言

2.1 SQL 的介绍

2.2 SQL 分类

2.3 SQL 通用语法

2.4 MySQL 常用数据类型

第三章.DDL 数据定义语言

3.1 数据库操作

3.2 创建表

3.3 查看表结构

3.4 修改表结构

3.5 删除表

第四章.DML 数据操作语言

4.1 插入数据

4.2 修改数据

4.3 删除数据

4.4 DELETE、TRUNCATE、DROP 区别

第五章.约束

5.1 主键约束

创建表时指定主键

约束区域指定主键

修改表添加主键

5.2 联合主键

5.3 删除主键

5.4 自增长约束

5.5 非空约束

5.6 唯一约束

5.7 默认值约束

5.8 外键约束

第六章.DQL 基础查询

6.1 准备案例表

6.2 中间遗漏补齐:从建库到查询的完整练习

6.3 简单查询

6.4 条件查询

6.5 LIKE 通配符

6.6 排序查询

6.7 聚合查询

6.8 分组查询

6.9 分页查询

6.10 SQL 编写顺序和执行顺序

第七章.MySQL 常用函数

7.1 字符串函数

7.2 数值函数

7.3 日期函数

7.4 流程函数

第八章.数据库三范式

8.1 第一范式:字段保持原子性

8.2 第二范式:每行能被唯一区分

8.3 第三范式:非主键字段不能相互依赖

8.4 三范式总结

第九章.多表关系与外键

9.1 一对一关系

9.2 一对多关系

9.3 多对多关系

9.4 外键删除和更新策略

第十章.多表查询

10.1 准备多表数据

10.2 交叉查询

10.3 内连接查询

10.4 外连接查询

10.5 UNION 联合查询

10.6 子查询

子查询作为条件

子查询作为临时表

10.7 多表查询选择

第十一章.事务

11.1 事务的介绍

11.2 事务基本操作

11.3 事务四大特性 ACID

11.4 并发事务问题

11.5 事务隔离级别

第十二章.索引

12.1 索引的介绍

12.2 常见索引类型

12.3 查看 SQL 是否使用索引