-- 查询所有记录
SELECT * FROM users;
-- 查询指定列
SELECT username, email FROM users;
-- 别名查询
SELECT username AS '用户名', email AS '邮箱' FROM users;
-- 去重查询
SELECT DISTINCT status FROM users;
-- 条件查询
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE status = 1 AND age >= 18;
SELECT * FROM users WHERE username LIKE '张%';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
-- 排序查询
SELECT * FROM users ORDER BY created_at DESC; -- 降序
SELECT * FROM users ORDER BY age ASC; -- 升序
SELECT * FROM users ORDER BY status, created_at DESC;
-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 0;
SELECT * FROM users LIMIT 10, 20; -- 从第10条开始，取20条
SELECT * FROM users LIMIT 20; -- 前20条
聚合函数
sql
-- COUNT 统计数量
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE status = 1;
-- SUM 求和
SELECT SUM(views) FROM articles;
-- AVG 平均值
SELECT AVG(age) FROM users;
-- MAX 最大值
SELECT MAX(views) FROM articles;
-- MIN 最小值
SELECT MIN(age) FROM users;
-- 分组统计
SELECT status, COUNT(*) as total FROM users GROUP BY status;
SELECT user_id, COUNT(*) as article_count
FROM articles
GROUP BY user_id
HAVING article_count > 5;
-- 统计函数组合
SELECT
COUNT(*) as total_users,
AVG(age) as avg_age,
MAX(age) as max_age,
MIN(age) as min_age
FROM users;
多表查询
sql
-- 内连接
SELECT u.username, a.title, a.created_at
FROM users u
INNER JOIN articles a ON u.id = a.user_id;
-- 左连接
SELECT u.username, a.title
FROM users u
LEFT JOIN articles a ON u.id = a.user_id;
-- 右连接
SELECT u.username, a.title
FROM users u
RIGHT JOIN articles a ON u.id = a.user_id;
-- 多表连接
SELECT
u.username,
a.title,
c.content as comment
FROM users u
INNER JOIN articles a ON u.id = a.user_id
INNER JOIN comments c ON a.id = c.article_id
WHERE u.status = 1;
-- 子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM articles);
SELECT * FROM articles
WHERE views > (SELECT AVG(views) FROM articles);
-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM articles a WHERE a.user_id = u.id
);
高级查询技巧
sql
-- UNION合并查询
SELECT username FROM users WHERE status = 1
UNION
SELECT username FROM admin_users;
-- UNION ALL（保留重复）
SELECT username FROM users
UNION ALL
SELECT username FROM admin_users;
-- IF条件判断
SELECT
username,
IF(age >= 18, '成年', '未成年') as age_group
FROM users;
-- CASE语句
SELECT
username,
CASE
WHEN age < 18 THEN '未成年'
WHEN age BETWEEN 18 AND 30 THEN '青年'
WHEN age BETWEEN 31 AND 50 THEN '中年'
ELSE '老年'
END as age_category
FROM users;
-- COALESCE处理NULL
SELECT
username,
COALESCE(email, phone, '无联系方式') as contact
FROM users;
-- CONCAT拼接
SELECT CONCAT(username, ' - ', email) as user_info FROM users;
四、数据操作（CRUD）
插入数据
sql
-- 插入单条记录
INSERT INTO users (username, email, password, age)
VALUES ('张三', 'zhangsan@example.com', 'hashed_pass', 25);
-- 插入多条记录
INSERT INTO users (username, email, password, age) VALUES
('李四', 'lisi@example.com', 'pass1', 30),
('王五', 'wangwu@example.com', 'pass2', 28),
('赵六', 'zhaoliu@example.com', 'pass3', 35);
-- 插入查询结果
INSERT INTO articles (user_id, title, content)
SELECT id, CONCAT('欢迎 ', username), '欢迎加入我们！'
FROM users WHERE status = 1;
-- REPLACE（存在则替换）
REPLACE INTO users (id, username, email)
VALUES (1, '新张三', 'new@example.com');
-- ON DUPLICATE KEY UPDATE
INSERT INTO article_stats (article_id, views)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE views = views + 1;
更新数据
sql
-- 更新单条记录
UPDATE users SET age = 26 WHERE id = 1;
-- 更新多条记录
UPDATE users SET status = 0, updated_at = NOW() WHERE age < 18;
-- 使用表达式更新
UPDATE articles SET views = views + 1 WHERE id = 1;
-- 条件更新
UPDATE users
SET email = CONCAT(username, '@example.com')
WHERE email IS NULL;
-- 更新前备份
UPDATE articles
SET content = OLD_CONTENT
WHERE id = 1;
删除数据
sql
-- 删除单条记录
DELETE FROM users WHERE id = 1;
-- 批量删除
DELETE FROM users WHERE status = 0 AND created_at < '2025-01-01';
-- 清空表（慎用）
DELETE FROM articles; -- 可回滚，有日志
TRUNCATE TABLE articles; -- 快速清空，不可回滚
-- 级联删除（配合外键）
DELETE FROM users CASCADE; -- 外键设置后自动级联
五、索引与性能优化
索引类型
sql
-- 主键索引
ALTER TABLE users ADD PRIMARY KEY (id);
-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
-- 普通索引
ALTER TABLE users ADD INDEX idx_username (username);
-- 复合索引
ALTER TABLE articles ADD INDEX idx_user_date (user_id, created_at);
-- 全文索引（InnoDB/MyISAM）
ALTER TABLE articles ADD FULLTEXT INDEX idx_fulltext (title, content);
-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL 优化' IN NATURAL LANGUAGE MODE);
-- 查看表的所有索引
SHOW INDEX FROM users;
-- 删除索引
ALTER TABLE users DROP INDEX idx_username;
DROP INDEX idx_username ON users;
SQL性能优化
sql
-- 避免SELECT *
SELECT id, username, email FROM users WHERE id = 1;
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE username = '张三';
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
-- 优化示例
-- 优化前
SELECT * FROM articles a, users u
WHERE a.user_id = u.id AND u.status = 1;
-- 优化后（确保有索引）
SELECT a.id, a.title, u.username
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE u.status = 1;
-- 使用LIMIT限制结果
SELECT * FROM articles ORDER BY created_at DESC LIMIT 10;
-- 分页优化（大数据量）
-- 低效
SELECT * FROM articles LIMIT 1000000, 10;
-- 高效
SELECT a.*
FROM articles a
INNER JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 1000000, 10) b
ON a.id = b.id;
慢查询日志
sql
-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log
六、事务处理
事务基础
sql
-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;
-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务隔离级别
sql
-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 示例：读已提交（Read Committed）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 示例：可重复读（Repeatable Read）- MySQL默认
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Serializable（串行化）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
保存点
sql
START TRANSACTION;
INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
SAVEPOINT after_item;
-- 回滚到保存点
ROLLBACK TO after_item;
-- 提交（只提交保存点之前的内容）
COMMIT;
七、视图和存储过程
视图
sql
-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email, age
FROM users
WHERE status = 1;
-- 创建复杂视图
CREATE VIEW user_articles_stats AS
SELECT
u.id,
u.username,
COUNT(a.id) as article_count,
SUM(COALESCE(a.views, 0)) as total_views
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
WHERE u.status = 1
GROUP BY u.id, u.username;
-- 使用视图
SELECT * FROM active_users;
SELECT * FROM user_articles_stats WHERE article_count > 5;
-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 1;
-- 删除视图
DROP VIEW IF EXISTS active_users;
存储过程
sql
-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_stats(IN user_id INT, OUT total_articles INT)
BEGIN
SELECT COUNT(*) INTO total_articles
FROM articles
WHERE user_id = user_id;
END //
DELIMITER ;
-- 调用存储过程
CALL get_user_stats(1, @total);
SELECT @total;
-- 创建带事务的存储过程
DELIMITER //
CREATE PROCEDURE transfer_money(
IN from_user INT,
IN to_user INT,
IN amount DECIMAL(10, 2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Transaction failed' as message;
END;
START TRANSACTION;
UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
COMMIT;
SELECT 'Transfer successful' as message;
END //
DELIMITER ;
-- 调用
CALL transfer_money(1, 2, 100.00);
-- 删除存储过程
DROP PROCEDURE IF EXISTS get_user_stats;
八、用户权限管理
用户操作
sql
-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
-- 重命名用户
RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';
-- 删除用户
DROP USER 'newuser'@'localhost';
-- 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'newuser'@'localhost' = 'new_password';
权限管理
sql
-- 授予权限
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'newuser'@'localhost';
GRANT ALL PRIVILEGES ON myapp.* TO 'admin'@'localhost';
GRANT EXECUTE ON PROCEDURE myapp.transfer_money TO 'newuser'@'localhost';
-- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';
SHOW GRANTS;
-- 撤销权限
REVOKE INSERT, UPDATE ON myapp.* FROM 'newuser'@'localhost';
-- 刷新权限
FLUSH PRIVILEGES;
-- 解决：允许远程连接
GRANT ALL PRIVILEGES ON myapp.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
-- 检查用户主机限制
SELECT user, host FROM mysql.user WHERE user = 'root';
性能问题
sql
-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
-- 查看查询缓存
SHOW VARIABLES LIKE 'query_cache%';
-- 优化表
OPTIMIZE TABLE users;
-- 分析表统计信息
ANALYZE TABLE users;
-- 检查表
CHECK TABLE users;
字符集问题
sql
-- 查看字符集设置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
-- 批量修改字符集
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
