MySQL 基础入门

一、MySQL 简介

1.1 什么是 MySQL

MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 使用 SQL（Structured Query Language）作为数据库查询语言，是目前最流行的关系型数据库之一。

1.2 MySQL 的特点

特点	描述
开源免费	社区版完全免费，源代码开放
高性能	优化的查询算法，高效的存储引擎
跨平台	支持 Windows、Linux、macOS 等多种操作系统
可扩展	支持主从复制、读写分离、分库分表
安全性	提供完善的权限管理和加密机制
社区活跃	拥有庞大的用户群体和丰富的文档资源

1.3 MySQL 版本演进

1.4 MySQL 与其他数据库对比

特性	MySQL	PostgreSQL	Oracle	SQL Server
开源	是	是	否	否
性能	高	高	极高	高
复杂查询	良好	优秀	优秀	优秀
学习曲线	低	中	高	中
社区支持	极强	强	商业支持	商业支持
适用场景	Web 应用	复杂业务	大型企业	Windows 生态

二、MySQL 安装与配置

2.1 Linux 环境安装

使用 yum 安装（CentOS/RHEL）

# 下载 MySQL 官方 yum 源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 安装 yum 源
rpm -ivh mysql80-community-release-el7-3.noarch.rpm

# 安装 MySQL Server
yum install mysql-community-server -y

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

# 查看初始密码
grep 'temporary password' /var/log/mysqld.log

使用 apt 安装（Ubuntu/Debian）

# 更新包索引
apt update

# 安装 MySQL Server
apt install mysql-server -y

# 运行安全配置脚本
mysql_secure_installation

2.2 Docker 安装（推荐开发环境）

# 拉取 MySQL 8.0 镜像
docker pull mysql:8.0

# 创建并启动容器
docker run -d \
  --name mysql8 \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -v /data/mysql/data:/var/lib/mysql \
  -v /data/mysql/conf:/etc/mysql/conf.d \
  mysql:8.0

# 进入 MySQL 容器
docker exec -it mysql8 mysql -uroot -p

2.3 Windows 安装

1. 下载 MySQL Installer：https://dev.mysql.com/downloads/installer/
2. 运行安装程序，选择 "Custom" 自定义安装
3. 选择需要的组件：MySQL Server、MySQL Workbench 等
4. 配置 MySQL Server：设置 root 密码、端口等
5. 完成安装并启动服务

2.4 配置文件详解

MySQL 配置文件位置：

• Linux: /etc/my.cnf 或 /etc/mysql/my.cnf
• Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

[mysqld]
# 基本配置
port = 3306
basedir = /usr/local/mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# 字符集配置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接配置
max_connections = 500
max_connect_errors = 100
wait_timeout = 28800
interactive_timeout = 28800

# 缓冲区配置
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# 日志配置
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 二进制日志（用于复制和恢复）
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
port = 3306
socket = /var/lib/mysql/mysql.sock

2.5 首次登录与密码修改

-- 使用初始密码登录后修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

-- MySQL 8.0 创建用户并授权
CREATE USER 'app_user'@'%' IDENTIFIED BY 'UserPassword123!';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- 查看当前用户
SELECT user, host FROM mysql.user;

三、SQL 语法基础

3.1 数据库操作

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

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE mydb;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

3.2 表操作

-- 创建表
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    email VARCHAR(100) NOT NULL COMMENT '邮箱',
    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
    status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

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

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20) COMMENT '手机号';
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME TO user_info;

-- 删除表
DROP TABLE IF EXISTS users;

-- 清空表数据
TRUNCATE TABLE users;

3.3 CRUD 操作

INSERT - 插入数据

-- 单条插入
INSERT INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'hash_value');

-- 批量插入
INSERT INTO users (username, email, password_hash) VALUES
('alice', 'alice@example.com', 'hash1'),
('bob', 'bob@example.com', 'hash2'),
('charlie', 'charlie@example.com', 'hash3');

-- 插入或更新（存在则更新）
INSERT INTO users (id, username, email, password_hash)
VALUES (1, 'john', 'john_new@example.com', 'new_hash')
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    password_hash = VALUES(password_hash);

-- 忽略重复插入
INSERT IGNORE INTO users (username, email, password_hash)
VALUES ('john', 'john@example.com', 'hash_value');

SELECT - 查询数据

-- 基本查询
SELECT * FROM users;
SELECT id, username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE status = 1;
SELECT * FROM users WHERE username LIKE 'j%';
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 排序与分页
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY status ASC, created_at DESC;
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20; -- 第3页，每页10条

-- 聚合查询
SELECT COUNT(*) as total FROM users;
SELECT status, COUNT(*) as count FROM users GROUP BY status;
SELECT status, COUNT(*) as count FROM users GROUP BY status HAVING count > 10;

-- 去重
SELECT DISTINCT status FROM users;

UPDATE - 更新数据

-- 单条更新
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

-- 多字段更新
UPDATE users SET
    email = 'new_email@example.com',
    status = 0,
    updated_at = NOW()
WHERE id = 1;

-- 批量更新
UPDATE users SET status = 0 WHERE id IN (1, 2, 3);

-- 使用 CASE WHEN 批量更新不同值
UPDATE users SET status = CASE id
    WHEN 1 THEN 0
    WHEN 2 THEN 1
    WHEN 3 THEN 0
END
WHERE id IN (1, 2, 3);

DELETE - 删除数据

-- 条件删除
DELETE FROM users WHERE id = 1;

-- 批量删除
DELETE FROM users WHERE status = 0;

-- 删除所有数据（保留表结构）
DELETE FROM users;

-- 清空表（更快，重置自增ID）
TRUNCATE TABLE users;

3.4 连接查询

-- 创建订单表用于演示
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- INNER JOIN（内连接）
SELECT u.username, o.amount, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左连接）
SELECT u.username, o.amount, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN（右连接）
SELECT u.username, o.amount, o.status
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 多表连接
SELECT u.username, o.amount, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

3.5 子查询

-- WHERE 子句中的子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- FROM 子句中的子查询（派生表）
SELECT avg_amount
FROM (
    SELECT user_id, AVG(amount) as avg_amount
    FROM orders
    GROUP BY user_id
) AS user_avg
WHERE avg_amount > 100;

-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 相关子查询
SELECT username,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

四、MySQL 数据类型详解

4.1 数值类型

类型	字节	范围（有符号）	范围（无符号）	说明
TINYINT	1	-128 ~ 127	0 ~ 255	小整数
SMALLINT	2	-32768 ~ 32767	0 ~ 65535	小整数
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215	中等整数
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295	整数
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 2^64-1	大整数
FLOAT	4	-	-	单精度浮点
DOUBLE	8	-	-	双精度浮点
DECIMAL(M,D)	变长	-	-	精确小数

-- 整数类型使用建议
id BIGINT UNSIGNED AUTO_INCREMENT  -- 主键推荐
age TINYINT UNSIGNED               -- 年龄
status TINYINT                     -- 状态枚举

-- 小数类型使用建议
price DECIMAL(10, 2)              -- 金额必须用 DECIMAL
rate DECIMAL(5, 4)                -- 比率，如 0.1234
score FLOAT                       -- 不要求精确的小数

4.2 字符串类型

类型	最大长度	特点	适用场景
CHAR(N)	255 字符	定长，末尾补空格	固定长度：MD5、UUID
VARCHAR(N)	65535 字节	变长，存储长度前缀	可变长度：姓名、标题
TEXT	65535 字节	大文本，不能有默认值	文章内容、描述
MEDIUMTEXT	16MB	更大文本	大型文档
LONGTEXT	4GB	超大文本	极少使用

-- 字符串类型使用建议
username VARCHAR(50)              -- 用户名
description VARCHAR(500)          -- 短描述
content TEXT                      -- 文章内容
uuid CHAR(36)                     -- UUID 固定36位
md5_hash CHAR(32)                 -- MD5 固定32位

4.3 日期时间类型

类型	格式	范围	存储	说明
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	3 字节	日期
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 字节	时间
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31	8 字节	日期时间
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 ~ 2038-01-19	4 字节	时间戳
YEAR	YYYY	1901 ~ 2155	1 字节	年份

-- 日期时间类型使用建议
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
birth_date DATE
start_time TIME
expire_time TIMESTAMP

-- DATETIME vs TIMESTAMP
-- DATETIME: 不受时区影响，存储原始值，范围更大
-- TIMESTAMP: 存储 UTC 时间戳，自动转换时区，2038年问题

4.4 JSON 类型（MySQL 5.7+）

-- 创建包含 JSON 字段的表
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,
    tags JSON
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes, tags) VALUES
('iPhone', '{"color": "black", "storage": "128GB"}', '["electronics", "phone"]');

-- 查询 JSON 字段
SELECT name, attributes->>'$.color' AS color FROM products;
SELECT * FROM products WHERE attributes->'$.storage' = '128GB';
SELECT * FROM products WHERE JSON_CONTAINS(tags, '"electronics"');

-- 更新 JSON 字段
UPDATE products SET attributes = JSON_SET(attributes, '$.price', 999) WHERE id = 1;
UPDATE products SET tags = JSON_ARRAY_APPEND(tags, '$', 'new') WHERE id = 1;

五、约束与索引基础

5.1 约束类型

CREATE TABLE example (
    -- 主键约束
    id BIGINT PRIMARY KEY AUTO_INCREMENT,

    -- 唯一约束
    email VARCHAR(100) UNIQUE,

    -- 非空约束
    username VARCHAR(50) NOT NULL,

    -- 默认值约束
    status TINYINT DEFAULT 1,

    -- 外键约束（实际生产环境慎用）
    department_id BIGINT,
    FOREIGN KEY (department_id) REFERENCES departments(id),

    -- CHECK 约束（MySQL 8.0+）
    age INT CHECK (age >= 0 AND age <= 150),

    -- 复合主键
    -- PRIMARY KEY (col1, col2)
);

5.2 索引基础

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX uk_email ON users(email);
CREATE INDEX idx_name_status ON users(username, status);  -- 复合索引

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

-- 使用 ALTER TABLE 添加索引
ALTER TABLE users ADD INDEX idx_status(status);
ALTER TABLE users ADD UNIQUE INDEX uk_phone(phone);

六、常用函数

6.1 字符串函数

SELECT CONCAT('Hello', ' ', 'World');           -- 字符串连接
SELECT CONCAT_WS(',', 'a', 'b', 'c');           -- 带分隔符连接
SELECT LENGTH('Hello');                          -- 字节长度
SELECT CHAR_LENGTH('你好');                      -- 字符长度
SELECT UPPER('hello'), LOWER('HELLO');          -- 大小写转换
SELECT TRIM('  hello  ');                        -- 去除两端空格
SELECT SUBSTRING('Hello World', 1, 5);          -- 子串
SELECT REPLACE('Hello', 'l', 'L');              -- 替换
SELECT REVERSE('Hello');                         -- 反转
SELECT LEFT('Hello', 2), RIGHT('Hello', 2);     -- 左右截取

6.2 数值函数

SELECT ABS(-10);                -- 绝对值
SELECT CEIL(4.1), FLOOR(4.9);   -- 向上/向下取整
SELECT ROUND(4.567, 2);         -- 四舍五入
SELECT TRUNCATE(4.567, 2);      -- 截断
SELECT MOD(10, 3);              -- 取模
SELECT RAND();                  -- 随机数 0-1
SELECT POWER(2, 10);            -- 幂运算
SELECT SQRT(16);                -- 平方根

6.3 日期时间函数

SELECT NOW();                                    -- 当前日期时间
SELECT CURDATE(), CURTIME();                     -- 当前日期/时间
SELECT DATE('2024-01-15 10:30:00');             -- 提取日期
SELECT TIME('2024-01-15 10:30:00');             -- 提取时间
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());   -- 提取年月日
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()); -- 提取时分秒
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 格式化
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);         -- 日期加减
SELECT DATEDIFF('2024-12-31', '2024-01-01');    -- 日期差
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01', NOW()); -- 时间差
SELECT UNIX_TIMESTAMP();                         -- Unix 时间戳
SELECT FROM_UNIXTIME(1704067200);               -- 时间戳转日期

6.4 聚合函数

SELECT COUNT(*) FROM users;                      -- 总数
SELECT COUNT(DISTINCT status) FROM users;        -- 去重计数
SELECT SUM(amount) FROM orders;                  -- 求和
SELECT AVG(amount) FROM orders;                  -- 平均值
SELECT MAX(amount), MIN(amount) FROM orders;     -- 最大/最小值
SELECT GROUP_CONCAT(username) FROM users;        -- 分组连接
SELECT GROUP_CONCAT(username ORDER BY id SEPARATOR ', ') FROM users;

6.5 条件函数

-- IF 函数
SELECT IF(status = 1, '启用', '禁用') as status_text FROM users;

-- IFNULL / COALESCE
SELECT IFNULL(phone, 'N/A') FROM users;
SELECT COALESCE(phone, email, 'N/A') FROM users;

-- CASE WHEN
SELECT
    username,
    CASE status
        WHEN 0 THEN '禁用'
        WHEN 1 THEN '启用'
        WHEN 2 THEN '待审核'
        ELSE '未知'
    END as status_text
FROM users;

SELECT
    username,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END as age_group
FROM users;

-- NULLIF
SELECT NULLIF(status, 0) FROM users;  -- status=0 时返回 NULL

七、实战练习

7.1 创建完整的用户订单系统

-- 用户表
CREATE TABLE t_user (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    email VARCHAR(100) COMMENT '邮箱',
    phone VARCHAR(20) COMMENT '手机号',
    avatar VARCHAR(255) COMMENT '头像URL',
    status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_username (username),
    KEY idx_status (status),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 商品表
CREATE TABLE t_product (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    description TEXT COMMENT '商品描述',
    price DECIMAL(10, 2) NOT NULL COMMENT '价格',
    stock INT UNSIGNED DEFAULT 0 COMMENT '库存',
    category_id BIGINT UNSIGNED COMMENT '分类ID',
    status TINYINT DEFAULT 1 COMMENT '状态：0-下架，1-上架',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_category (category_id),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 订单表
CREATE TABLE t_order (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL COMMENT '订单号',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(12, 2) NOT NULL COMMENT '订单总金额',
    status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '订单状态',
    payment_time DATETIME COMMENT '支付时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id (user_id),
    KEY idx_status (status),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单明细表
CREATE TABLE t_order_item (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    product_name VARCHAR(200) NOT NULL COMMENT '商品名称快照',
    price DECIMAL(10, 2) NOT NULL COMMENT '商品单价快照',
    quantity INT UNSIGNED NOT NULL COMMENT '购买数量',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    KEY idx_order_id (order_id),
    KEY idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

7.2 常见业务查询

-- 1. 查询用户订单数及总消费
SELECT
    u.id,
    u.username,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM t_user u
LEFT JOIN t_order o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY total_spent DESC;

-- 2. 查询每月销售额
SELECT
    DATE_FORMAT(created_at, '%Y-%m') as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM t_order
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

-- 3. 查询热销商品 TOP 10
SELECT
    p.id,
    p.name,
    SUM(oi.quantity) as total_sold,
    SUM(oi.price * oi.quantity) as total_revenue
FROM t_product p
JOIN t_order_item oi ON p.id = oi.product_id
JOIN t_order o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name
ORDER BY total_sold DESC
LIMIT 10;

-- 4. 查询最近7天每天的订单统计
SELECT
    DATE(created_at) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as daily_sales
FROM t_order
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(created_at)
ORDER BY order_date;