Day49-52 - MySQL 基础

本部分涵盖 MySQL 数据库的基础知识，包括数据类型、DDL 语句、DML 语句和单表/多表查询。

1. MySQL 概述与安装

1.1 数据库基本概念

数据库（Database）
    └── 表（Table）
            └── 行（Row/Record）
            └── 列（Column/Field）

1.2 MySQL 存储引擎

引擎	特点	适用场景
InnoDB	支持事务、行锁、外键、MVCC	默认引擎，高并发 OLTP
MyISAM	表锁、非聚簇索引、查询快	读多写少的场景
Memory	表锁、数据存储内存	临时表、缓存

1.3 MySQL 安装与启动

# 启动 MySQL 服务
systemctl start mysql
systemctl enable mysql

# 连接 MySQL
mysql -u root -p

# 基本命令
SHOW DATABASES;
USE database_name;
SHOW TABLES;

2. 数据类型

2.1 数值类型

类型	大小	说明
TINYINT	1字节	-128 ~ 127
SMALLINT	2字节	-32768 ~ 32767
INT	4字节	-21亿 ~ 21亿
BIGINT	8字节	极大整数
FLOAT	4字节	单精度浮点
DOUBLE	8字节	双精度浮点
DECIMAL	变长	精确数值

2.2 字符串类型

类型	大小	说明
CHAR	0-255	固定长度
VARCHAR	0-65535	可变长度
TEXT	64KB	长文本
LONGTEXT	4GB	极大文本

2.3 日期时间类型

类型	格式	说明
DATE	YYYY-MM-DD	日期
TIME	HH:MM:SS	时间
DATETIME	YYYY-MM-DD HH:MM:SS	日期时间
TIMESTAMP	时间戳	自动更新
YEAR	YYYY	年份

3. DDL 语句（数据定义）

3.1 库操作

-- 创建数据库
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;

-- 选择数据库
USE dbname;

-- 删除数据库
DROP DATABASE dbname;

3.2 表操作

-- 创建表
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    age INT DEFAULT 18,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESC users;
DESCRIBE users;
SHOW CREATE TABLE users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN phone CHAR(11);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO user_info;

-- 删除表
DROP TABLE users;

4. DML 语句（数据操作）

4.1 插入数据

-- 单条插入
INSERT INTO users (username, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 批量插入
INSERT INTO users (username, email, age) VALUES
    ('李四', 'lisi@example.com', 30),
    ('王五', 'wangwu@example.com', 28);

-- 蠕虫复制
INSERT INTO users (username, email, age) SELECT username, email, age FROM users;

4.2 更新数据

-- 更新数据
UPDATE users SET age = 26 WHERE id = 1;

-- 批量更新
UPDATE users SET status = 0 WHERE age > 50;

4.3 删除数据

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 清空表（保留表结构）
TRUNCATE TABLE users;

-- 区别：
-- DELETE：逐行删除，可回滚，触发触发器
-- TRUNCATE：直接删除页，不可回滚，性能高

5. DQL 语句（数据查询）

5.1 基础查询

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT id, username, email FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

-- 别名
SELECT username AS name, email AS mail FROM users;
SELECT username name FROM users;  -- AS 可省略

5.2 条件查询

-- 比较运算
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE status = 1;

-- 逻辑运算
SELECT * FROM users WHERE age >= 18 AND status = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE age IN (18, 25, 30);

-- 模糊查询
SELECT * FROM users WHERE username LIKE '张%';    -- 张开头
SELECT * FROM users WHERE username LIKE '%三%';    -- 包含三
SELECT * FROM users WHERE username LIKE '张_';     -- 张开头，2个字

-- NULL 查询
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

5.3 排序

-- 单字段排序
SELECT * FROM users ORDER BY age ASC;   -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;  -- 降序

-- 多字段排序
SELECT * FROM users ORDER BY status ASC, age DESC;

5.4 分页

-- LIMIT offset, count
SELECT * FROM users LIMIT 10;              -- 前10条
SELECT * FROM users LIMIT 0, 10;            -- 第1页
SELECT * FROM users LIMIT 10, 10;          -- 第2页
SELECT * FROM users LIMIT 20, 10;          -- 第3页

-- 推导公式
-- 第 n 页：LIMIT (n-1)*page_size, page_size

5.5 聚合函数

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

-- 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 1;

-- 统计总和
SELECT SUM(age) FROM users;

-- 统计平均
SELECT AVG(age) FROM users;

-- 最大最小
SELECT MAX(age), MIN(age) FROM users;

5.6 分组查询

-- 按状态分组
SELECT status, COUNT(*) FROM users GROUP BY status;

-- 分组 + 筛选（HAVING）
SELECT status, COUNT(*) as cnt 
FROM users 
GROUP BY status
HAVING cnt > 10;

-- WHERE vs HAVING
-- WHERE: 分组前过滤
-- HAVING: 分组后过滤

5.7 查询语法顺序

SELECT DISTINCT <字段列表>
FROM <表名>
[JOIN <表名> ON <连接条件>]
[WHERE <过滤条件>]
[GROUP BY <分组字段>]
[HAVING <分组后条件>]
[ORDER BY <排序字段 ASC/DESC>]
[LIMIT <限制条数>]

6. 多表查询

6.1 连接类型

graph LR
    A["表A<br/>users"] --> AB["A.id = B.user_id"]
    B["表B<br/>orders"] --> AB
    
    AB --> I["INNER JOIN<br/>内连接"]
    AB --> L["LEFT JOIN<br/>左连接"]
    AB --> R["RIGHT JOIN<br/>右连接"]
    AB --> F["FULL JOIN<br/>全连接"]

6.2 内连接（INNER JOIN）

-- 隐式内连接
SELECT u.username, o.order_no, o.amount
FROM users u, orders o
WHERE u.id = o.user_id;

-- 显式内连接
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

6.3 外连接

-- 左连接：包含左表全部数据
SELECT u.username, o.order_no, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接：包含右表全部数据
SELECT u.username, o.order_no, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

6.4 自连接

-- 员工表查询上级
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

6.5 联合查询（UNION）

-- 合并结果集（去重）
SELECT username FROM users
UNION
SELECT admin_name FROM admins;

-- 合并结果集（不去重）
SELECT username FROM users
UNION ALL
SELECT admin_name FROM admins;

7. 子查询

7.1 标量子查询

-- 查询年龄大于平均年龄的用户
SELECT * FROM users
WHERE age > (SELECT AVG(age) FROM users);

7.2 列子查询

-- 查询有订单的用户
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

7.3 行子查询

-- 查询满足特定条件的用户
SELECT * FROM users
WHERE (age, status) = (
    SELECT MAX(age), MAX(status) FROM users
);

7.4 表子查询

-- 查询每个部门工资最高的人
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) as rn
    FROM employees
) t
WHERE rn = 1;

7.5 EXISTS 子查询

-- 查询有订单的用户
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

8. 完整性约束

8.1 主键约束（PRIMARY KEY）

-- 单字段主键
CREATE TABLE t1 (
    id INT PRIMARY KEY
);

-- 复合主键
CREATE TABLE t2 (
    user_id INT,
    order_id INT,
    PRIMARY KEY (user_id, order_id)
);

8.2 唯一约束（UNIQUE）

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

8.3 非空约束（NOT NULL）

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

8.4 默认约束（DEFAULT）

CREATE TABLE users (
    id INT PRIMARY KEY,
    status INT DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

8.5 外键约束（FOREIGN KEY）

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE  -- 级联删除
        ON UPDATE CASCADE   -- 级联更新
);

9. 实战练习

练习1：创建电商表结构

-- 商品表
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2),
    status TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单明细表
CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

练习2：复杂查询

-- 查询每个用户的订单总额（大于1000的）
SELECT 
    u.username,
    u.email,
    SUM(o.total_amount) as total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 1
GROUP BY u.id, u.username, u.email
HAVING total > 1000
ORDER BY total DESC
LIMIT 10;

Day49-52 - MySQL 基础

1. MySQL 概述与安装

1.1 数据库基本概念

1.2 MySQL 存储引擎

1.3 MySQL 安装与启动

2. 数据类型

2.1 数值类型

2.2 字符串类型

2.3 日期时间类型

3. DDL 语句（数据定义）

3.1 库操作

3.2 表操作

4. DML 语句（数据操作）

4.1 插入数据

4.2 更新数据

4.3 删除数据

5. DQL 语句（数据查询）

5.1 基础查询

5.2 条件查询

5.3 排序

5.4 分页

5.5 聚合函数

5.6 分组查询

5.7 查询语法顺序

6. 多表查询

6.1 连接类型

6.2 内连接（INNER JOIN）

6.3 外连接

6.4 自连接

6.5 联合查询（UNION）

7. 子查询

7.1 标量子查询

7.2 列子查询

7.3 行子查询

7.4 表子查询

7.5 EXISTS 子查询

8. 完整性约束

8.1 主键约束（PRIMARY KEY）

8.2 唯一约束（UNIQUE）

8.3 非空约束（NOT NULL）

8.4 默认约束（DEFAULT）

8.5 外键约束（FOREIGN KEY）

9. 实战练习

练习1：创建电商表结构

练习2：复杂查询

Day49-52-MySQL基础

分享文章

文章目录