MySQL 基础知识
MySQL 入门必备，掌握 SQL 语句和基本操作
1. MySQL 简介
1.1 什么是 MySQL？
MySQL 是最流行的开源关系型数据库管理系统（RDBMS）。
特点：
- 开源免费
- 性能优秀
- 可靠性高
- 易于使用
- 跨平台
1.2 应用场景
- Web 应用：WordPress、Drupal
- 电商平台：淘宝、京东
- 社交网络：Facebook、Twitter
- 企业应用：OA、CRM、ERP
1.3 版本选择
- MySQL 5.7：稳定，广泛使用
- MySQL 8.0：新特性多，推荐
2. 数据类型
2.1 数值类型
整数类型
TINYINT -- 1 字节，-128 到 127
SMALLINT -- 2 字节，-32768 到 32767
MEDIUMINT -- 3 字节
INT -- 4 字节，-2147483648 到 2147483647
BIGINT -- 8 字节
-- 无符号（UNSIGNED）
INT UNSIGNED -- 0 到 4294967295
-- 示例
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
stock SMALLINT UNSIGNED DEFAULT 0,
views BIGINT UNSIGNED DEFAULT 0
);
浮点数和定点数
FLOAT -- 4 字节，单精度
DOUBLE -- 8 字节，双精度
DECIMAL(M,D) -- 定点数，精确（推荐用于金额）
-- 示例
CREATE TABLE orders (
id INT PRIMARY KEY,
amount DECIMAL(10,2), -- 10 位数字，2 位小数（如 99999999.99）
discount FLOAT -- 折扣
);
2.2 字符串类型
CHAR(M) -- 定长字符串，0-255
VARCHAR(M) -- 变长字符串，0-65535
TEXT -- 长文本，0-65535
MEDIUMTEXT -- 中等文本，0-16777215
LONGTEXT -- 超长文本，0-4294967295
-- 二进制
BINARY(M) -- 定长二进制
VARBINARY(M) -- 变长二进制
BLOB -- 二进制大对象
-- 示例
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL, -- 用户名（变长）
password CHAR(60) NOT NULL, -- 密码哈希（定长）
bio TEXT, -- 个人简介
avatar BLOB -- 头像（二进制）
);
CHAR vs VARCHAR：
- CHAR：定长，填充空格，适合长度固定（如手机号、身份证）
- VARCHAR：变长，节省空间，适合长度不定（如用户名、标题）
2.3 日期时间类型
DATE -- 日期，YYYY-MM-DD
TIME -- 时间，HH:MM:SS
DATETIME -- 日期时间，YYYY-MM-DD HH:MM:SS
TIMESTAMP -- 时间戳（自动更新）
YEAR -- 年份，YYYY
-- 示例
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(200),
published_at DATE, -- 发布日期
created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 创建时间
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
);
DATETIME vs TIMESTAMP：
- DATETIME：8 字节，范围 1000-9999 年，不受时区影响
- TIMESTAMP：4 字节，范围 1970-2038 年，受时区影响，自动更新
2.4 枚举和集合
ENUM('value1','value2',...) -- 枚举，单选
SET('value1','value2',...) -- 集合，多选
-- 示例
CREATE TABLE users (
id INT PRIMARY KEY,
gender ENUM('male', 'female', 'other'),
hobbies SET('reading', 'sports', 'music', 'travel')
);
-- 插入
INSERT INTO users VALUES (1, 'male', 'reading,sports');
3. DDL（数据定义语言）
3.1 数据库操作
-- 创建数据库
CREATE DATABASE mydb;
CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 查看数据库
SHOW DATABASES;
-- 使用数据库
USE mydb;
-- 删除数据库
DROP DATABASE mydb;
-- 修改数据库
ALTER DATABASE mydb CHARACTER SET utf8mb4;
3.2 表操作
创建表
CREATE TABLE users (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
password VARCHAR(255) NOT NULL COMMENT '密码',
age TINYINT UNSIGNED COMMENT '年龄',
gender ENUM('male', 'female', 'other') DEFAULT 'male' COMMENT '性别',
status TINYINT DEFAULT 1 COMMENT '状态：0-禁用，1-正常',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_username (username),
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
查看表结构
DESC users;
SHOW CREATE TABLE users;
SHOW COLUMNS FROM users;
修改表结构
-- 添加字段
ALTER TABLE users ADD phone VARCHAR(20);
ALTER TABLE users ADD COLUMN address VARCHAR(200) AFTER email;
-- 修改字段
ALTER TABLE users MODIFY age INT;
ALTER TABLE users CHANGE age user_age INT;
-- 删除字段
ALTER TABLE users DROP COLUMN address;
-- 添加索引
ALTER TABLE users ADD INDEX idx_phone (phone);
ALTER TABLE users ADD UNIQUE INDEX uniq_phone (phone);
-- 删除索引
ALTER TABLE users DROP INDEX idx_phone;
-- 重命名表
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;
删除表
DROP TABLE users;
DROP TABLE IF EXISTS users;
-- 清空表数据（保留结构）
TRUNCATE TABLE users;
4. DML（数据操作语言）
4.1 INSERT（插入）
-- 插入单行
INSERT INTO users (username, email, password)
VALUES ('alice', 'alice@example.com', 'hashed_password');
-- 插入多行
INSERT INTO users (username, email, password) VALUES
('bob', 'bob@example.com', 'password1'),
('charlie', 'charlie@example.com', 'password2'),
('david', 'david@example.com', 'password3');
-- 插入或更新（存在则更新）
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE email = 'newemail@example.com';
-- 插入查询结果
INSERT INTO backup_users SELECT * FROM users WHERE status = 0;
4.2 UPDATE（更新）
-- 更新单个字段
UPDATE users SET age = 26 WHERE id = 1;
-- 更新多个字段
UPDATE users
SET age = 26, status = 1, updated_at = NOW()
WHERE id = 1;
-- 批量更新
UPDATE users SET status = 0 WHERE age < 18;
-- 使用表达式
UPDATE products SET price = price * 1.1 WHERE category_id = 1;
UPDATE users SET views = views + 1 WHERE id = 1;
4.3 DELETE（删除）
-- 删除指定记录
DELETE FROM users WHERE id = 1;
-- 批量删除
DELETE FROM users WHERE status = 0;
-- 删除所有（慎用）
DELETE FROM users;
-- 使用 LIMIT
DELETE FROM users WHERE status = 0 LIMIT 100;
5. DQL（数据查询语言）
5.1 基本查询
-- 查询所有字段
SELECT * FROM users;
-- 查询指定字段
SELECT id, username, email FROM users;
-- 别名
SELECT id AS user_id, username AS name FROM users;
-- DISTINCT（去重）
SELECT DISTINCT city FROM users;
-- LIMIT（分页）
SELECT * FROM users LIMIT 10; -- 前 10 条
SELECT * FROM users LIMIT 10, 10; -- 第 11-20 条
SELECT * FROM users LIMIT 10 OFFSET 10; -- 第 11-20 条
5.2 WHERE 条件
-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18 AND age <= 30;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- IN 和 NOT IN
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE status NOT IN (0, 2);
-- LIKE 模糊查询
SELECT * FROM users WHERE username LIKE 'a%'; -- a 开头
SELECT * FROM users WHERE username LIKE '%alice%'; -- 包含 alice
SELECT * FROM users WHERE username LIKE '_lice'; -- 5 个字符，以 lice 结尾
-- IS NULL 和 IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND status = 1;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT status = 0;
5.3 ORDER BY（排序）
-- 升序（默认）
SELECT * FROM users ORDER BY age;
SELECT * FROM users ORDER BY age ASC;
-- 降序
SELECT * FROM users ORDER BY age DESC;
-- 多字段排序
SELECT * FROM users ORDER BY age DESC, created_at ASC;
-- 配合 LIMIT
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
5.4 聚合函数
-- COUNT（计数）
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;
-- SUM（求和）
SELECT SUM(amount) FROM orders;
-- AVG（平均值）
SELECT AVG(age) FROM users;
-- MAX/MIN（最大/最小值）
SELECT MAX(age), MIN(age) FROM users;
-- 组合使用
SELECT COUNT(*) AS total, AVG(age) AS avg_age, MAX(age) AS max_age
FROM users;
5.5 GROUP BY（分组）
-- 按字段分组
SELECT gender, COUNT(*) FROM users GROUP BY gender;
-- 多字段分组
SELECT city, gender, COUNT(*)
FROM users
GROUP BY city, gender;
-- HAVING（分组后过滤）
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING user_count > 10;
-- GROUP BY + ORDER BY
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
ORDER BY user_count DESC;
5.6 JOIN（联表查询）
INNER JOIN（内连接）
-- 只返回两表都有的数据
SELECT u.username, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 简写
SELECT u.username, o.order_no
FROM users u, orders o
WHERE u.id = o.user_id;
LEFT JOIN（左连接）
-- 返回左表所有数据，右表没有则为 NULL
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
RIGHT JOIN（右连接）
-- 返回右表所有数据，左表没有则为 NULL
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
CROSS JOIN（交叉连接）
-- 笛卡尔积
SELECT u.username, p.product_name
FROM users u
CROSS JOIN products p;
多表联查
SELECT
u.username,
o.order_no,
p.product_name,
oi.quantity
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;
5.7 子查询
-- WHERE 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- FROM 子查询（派生表）
SELECT city, avg_age
FROM (
SELECT city, AVG(age) AS avg_age
FROM users
GROUP BY city
) AS city_stats
WHERE avg_age > 25;
-- SELECT 子查询
SELECT
username,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;
-- EXISTS 子查询
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
5.8 UNION（联合查询）
-- UNION（去重）
SELECT username FROM users WHERE age < 18
UNION
SELECT username FROM users WHERE age > 60;
-- UNION ALL（不去重，性能更好）
SELECT username FROM users WHERE city = 'Beijing'
UNION ALL
SELECT username FROM users WHERE city = 'Shanghai';
6. 常用函数
6.1 字符串函数
-- CONCAT（连接）
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- CONCAT_WS（带分隔符连接）
SELECT CONCAT_WS('-', year, month, day) AS date FROM events;
-- SUBSTRING（截取）
SELECT SUBSTRING(username, 1, 5) FROM users;
-- LENGTH（长度）
SELECT LENGTH(username) FROM users;
-- UPPER/LOWER（大小写）
SELECT UPPER(username), LOWER(email) FROM users;
-- TRIM（去除空格）
SELECT TRIM(username) FROM users;
-- REPLACE（替换）
SELECT REPLACE(content, 'old', 'new') FROM articles;
6.2 数值函数
-- ROUND（四舍五入）
SELECT ROUND(3.1415, 2); -- 3.14
-- CEIL（向上取整）
SELECT CEIL(3.14); -- 4
-- FLOOR（向下取整）
SELECT FLOOR(3.99); -- 3
-- ABS（绝对值）
SELECT ABS(-10); -- 10
-- MOD（取模）
SELECT MOD(10, 3); -- 1
-- RAND（随机数）
SELECT RAND();
SELECT * FROM users ORDER BY RAND() LIMIT 10; -- 随机 10 条
6.3 日期时间函数
-- NOW（当前日期时间）
SELECT NOW();
-- CURDATE（当前日期）
SELECT CURDATE();
-- CURTIME（当前时间）
SELECT CURTIME();
-- DATE_FORMAT（格式化）
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- YEAR/MONTH/DAY（提取）
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at) FROM users;
-- DATE_ADD/DATE_SUB（日期计算）
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 7 天后
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1 个月前
-- DATEDIFF（日期差）
SELECT DATEDIFF(NOW(), '2024-01-01'); -- 相差天数
-- TIMESTAMPDIFF（时间差）
SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) FROM events;
6.4 条件函数
-- IF
SELECT username, IF(age >= 18, '成年', '未成年') AS age_group FROM users;
-- CASE WHEN
SELECT
username,
CASE
WHEN age < 18 THEN '未成年'
WHEN age >= 18 AND age < 60 THEN '成年'
ELSE '老年'
END AS age_group
FROM users;
-- IFNULL（NULL 处理）
SELECT username, IFNULL(email, '未设置') AS email FROM users;
-- COALESCE（返回第一个非 NULL 值）
SELECT COALESCE(mobile, email, '无联系方式') FROM users;
6.5 聚合函数
-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;
-- SUM
SELECT SUM(amount) FROM orders;
-- AVG
SELECT AVG(age) FROM users;
-- MAX/MIN
SELECT MAX(created_at), MIN(created_at) FROM users;
-- GROUP_CONCAT（分组拼接）
SELECT user_id, GROUP_CONCAT(product_name) AS products
FROM order_items
GROUP BY user_id;
7. 索引基础
7.1 什么是索引？
索引是帮助 MySQL 高效查询数据的数据结构，类似书的目录。
7.2 索引类型
-- 主键索引（PRIMARY KEY）
CREATE TABLE users (
id INT PRIMARY KEY
);
-- 唯一索引（UNIQUE）
CREATE TABLE users (
email VARCHAR(100) UNIQUE
);
-- 普通索引（INDEX/KEY）
CREATE TABLE users (
username VARCHAR(50),
INDEX idx_username (username)
);
-- 全文索引（FULLTEXT）
CREATE TABLE articles (
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (content)
);
-- 联合索引
CREATE TABLE users (
city VARCHAR(50),
age INT,
INDEX idx_city_age (city, age)
);
7.3 创建和删除索引
-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX uniq_email ON users(email);
CREATE INDEX idx_city_age ON users(city, age);
-- 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;
-- 查看索引
SHOW INDEX FROM users;
7.4 索引的优缺点
优点：
- 加快查询速度
- 唯一索引保证数据唯一性
缺点：
- 占用存储空间
- 降低写操作速度（需要维护索引）
8. 约束
8.1 PRIMARY KEY（主键）
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT
);
-- 复合主键
CREATE TABLE user_roles (
user_id INT,
role_id INT,
PRIMARY KEY (user_id, role_id)
);
8.2 UNIQUE（唯一）
CREATE TABLE users (
email VARCHAR(100) UNIQUE,
phone VARCHAR(20) UNIQUE
);
8.3 NOT NULL（非空）
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
8.4 DEFAULT（默认值）
CREATE TABLE users (
status TINYINT 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
);
-- ON DELETE CASCADE: 删除主表记录时，自动删除从表记录
-- ON DELETE SET NULL: 删除主表记录时，从表外键设为 NULL
-- ON UPDATE CASCADE: 更新主表记录时，自动更新从表
8.6 CHECK（检查约束，MySQL 8.0.16+）
CREATE TABLE users (
age INT CHECK (age >= 0 AND age <= 150)
);
9. 视图（View）
9.1 创建视图
CREATE VIEW user_orders AS
SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- 使用视图
SELECT * FROM user_orders WHERE order_count > 5;
9.2 修改和删除视图
-- 修改视图
CREATE OR REPLACE VIEW user_orders AS
SELECT ...;
ALTER VIEW user_orders AS
SELECT ...;
-- 删除视图
DROP VIEW user_orders;
10. 存储过程和函数
10.1 存储过程
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
SELECT * FROM orders WHERE user_id = userId;
END //
DELIMITER ;
-- 调用存储过程
CALL GetUserOrders(1);
-- 带输出参数
DELIMITER //
CREATE PROCEDURE GetUserCount(OUT userCount INT)
BEGIN
SELECT COUNT(*) INTO userCount FROM users;
END //
DELIMITER ;
-- 调用
CALL GetUserCount(@count);
SELECT @count;
10.2 存储函数
-- 创建函数
DELIMITER //
CREATE FUNCTION GetUserName(userId INT) RETURNS VARCHAR(50)
BEGIN
DECLARE userName VARCHAR(50);
SELECT username INTO userName FROM users WHERE id = userId;
RETURN userName;
END //
DELIMITER ;
-- 使用函数
SELECT GetUserName(1);
11. 触发器
11.1 创建触发器
-- 创建审计表
CREATE TABLE user_audit (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action VARCHAR(20),
changed_at DATETIME
);
-- 创建触发器
DELIMITER //
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action, changed_at)
VALUES (NEW.id, 'UPDATE', NOW());
END //
DELIMITER ;
-- 删除后触发
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (user_id, action, changed_at)
VALUES (OLD.id, 'DELETE', NOW());
END //
DELIMITER ;
12. 总结
MySQL 基础核心：
- ✅ 数据类型：数值、字符串、日期时间
- ✅ DDL：创建、修改、删除表
- ✅ DML：INSERT、UPDATE、DELETE
- ✅ DQL：SELECT、WHERE、JOIN、GROUP BY
- ✅ 函数：字符串、数值、日期、聚合
- ✅ 索引：类型、创建、删除
- ✅ 约束：主键、外键、唯一、非空
- ✅ 视图、存储过程、函数、触发器
下一步：学习 MySQL 进阶特性！
