企业官网建设流程全解析

1. MySQL常用SQL语句实战指南

作为关系型数据库的典型代表，MySQL在日常开发中占据重要地位。掌握核心SQL语句的规范写法与实战技巧，是每位开发者必备的基本功。本文将系统梳理增删改查、表操作、事务控制等高频语句，并结合实际场景分析优化要点。

提示：所有示例基于MySQL 8.0版本，部分语法在低版本可能需要调整

1.1 基础查询语句精要

SELECT语句是使用频率最高的操作，需要注意以下要点：

-- 基础查询（避免使用SELECT *） SELECT id, name, create_time FROM users WHERE status = 1; -- 分页查询（大数据量推荐） SELECT id, title FROM articles ORDER BY view_count DESC LIMIT 20 OFFSET 40; -- 等价于LIMIT 40,20 -- 聚合函数使用 SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

关键技巧：

1. 始终明确指定字段名而非使用SELECT *
2. 分页查询大数据集时，优先使用OFFSET语法
3. GROUP BY子句中的字段应建立索引

1.2 数据操作语句详解

1.2.1 插入数据最佳实践

-- 多行插入（效率比单条高10倍以上） INSERT INTO products (name, price, stock) VALUES ('手机', 2999, 100), ('耳机', 399, 200), ('充电器', 129, 150); -- 带冲突处理（ON DUPLICATE KEY UPDATE） INSERT INTO user_scores (user_id, score) VALUES (1001, 90) ON DUPLICATE KEY UPDATE score = VALUES(score);

1.2.2 更新与删除注意事项

-- 更新必须带WHERE条件！ UPDATE orders SET status = 2 WHERE id = 1001 AND status = 1; -- 批量删除使用IN而非OR DELETE FROM logs WHERE create_time < '2023-01-01' AND id IN (SELECT id FROM temp_delete_list);

警告：生产环境执行DELETE前务必先SELECT确认数据

1.3 表与索引管理

1.3.1 表结构操作

-- 创建表（包含索引和约束） CREATE TABLE `articles` ( `id` bigint NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL COMMENT '文章标题', `content` text, `author_id` bigint NOT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_author` (`author_id`), KEY `idx_create` (`create_time`), CONSTRAINT `fk_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 修改表结构 ALTER TABLE articles ADD COLUMN view_count int DEFAULT 0 AFTER content, MODIFY COLUMN title varchar(150);

1.3.2 索引优化策略

-- 添加组合索引 ALTER TABLE orders ADD INDEX idx_status_created (status, create_time); -- 查看索引使用情况 EXPLAIN SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC; -- 删除冗余索引 DROP INDEX idx_name ON products;

索引设计原则：

1. 优先考虑WHERE、JOIN、ORDER BY涉及的字段
2. 区分度高的字段靠前（如ID > 状态字段）
3. 单表索引不超过5个

1.4 复杂查询与性能优化

1.4.1 多表连接查询

-- 内连接（推荐显式写法） SELECT a.title, u.username, COUNT(c.id) AS comment_count FROM articles a INNER JOIN users u ON a.author_id = u.id LEFT JOIN comments c ON a.id = c.article_id WHERE a.status = 1 GROUP BY a.id HAVING comment_count > 10; -- 子查询优化（改用JOIN） -- 低效写法： SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE type = 1); -- 高效改写： SELECT p.* FROM products p JOIN categories c ON p.category_id = c.id WHERE c.type = 1;

1.4.2 窗口函数应用

-- 获取每个部门薪资排名 SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; -- 计算移动平均值 SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily_sales;

1.5 事务与锁机制

1.5.1 事务控制语句

-- 典型事务模板 START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- 业务逻辑检查 SELECT IF( (SELECT balance FROM accounts WHERE user_id = 1) >= 0, 'COMMIT', 'ROLLBACK' ) INTO @result; IF @result = 'COMMIT' THEN COMMIT; ELSE ROLLBACK; END IF;

1.5.2 锁机制应用

-- 悲观锁示例 SELECT * FROM inventory WHERE product_id = 1001 FOR UPDATE; -- 乐观锁实现 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 1001 AND version = 5;

1.6 存储过程与函数

1.6.1 存储过程开发

DELIMITER // CREATE PROCEDURE transfer_funds( IN from_account BIGINT, IN to_account BIGINT, IN amount DECIMAL(10,2), OUT result_code INT ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET result_code = -1; END; START TRANSACTION; -- 检查余额 IF (SELECT balance FROM accounts WHERE id = from_account) < amount THEN SET result_code = -2; ROLLBACK; ELSE UPDATE accounts SET balance = balance - amount WHERE id = from_account; UPDATE accounts SET balance = balance + amount WHERE id = to_account; COMMIT; SET result_code = 0; END IF; END // DELIMITER ;

1.6.2 自定义函数

CREATE FUNCTION get_user_level(credit INT) RETURNS VARCHAR(20) DETERMINISTIC BEGIN RETURN CASE WHEN credit > 1000 THEN 'PLATINUM' WHEN credit > 500 THEN 'GOLD' WHEN credit > 100 THEN 'SILVER' ELSE 'BASIC' END; END;

1.7 性能监控与优化

1.7.1 慢查询分析

-- 查看慢查询配置 SHOW VARIABLES LIKE 'slow_query%'; -- 临时开启慢查询日志（生产环境慎用） SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1; -- 超过1秒记录 -- 分析慢查询日志 mysqldumpslow -s t /var/log/mysql/mysql-slow.log

1.7.2 EXPLAIN执行计划解读

EXPLAIN FORMAT=JSON SELECT a.* FROM articles a JOIN users u ON a.author_id = u.id WHERE u.status = 1 AND a.create_time > '2023-01-01';

关键指标解读：

1. type列：从优到差 system > const > eq_ref > ref > range > index > ALL
2. key列：实际使用的索引
3. rows列：预估扫描行数
4. Extra列：Using filesort、Using temporary需要优化

1.8 备份与恢复策略

1.8.1 逻辑备份

# 全库备份 mysqldump -uroot -p --single-transaction --routines --triggers --all-databases > full_backup.sql # 单表备份 mysqldump -uroot -p db_name table_name --where="create_time>'2023-01-01'" > table_backup.sql

1.8.2 物理备份

# 使用Percona XtraBackup xtrabackup --backup --target-dir=/data/backups/ xtrabackup --prepare --target-dir=/data/backups/

1.9 安全防护要点

1.9.1 SQL注入防御

-- 不安全写法 SET @sql = CONCAT('SELECT * FROM users WHERE name = "', @input_name, '"'); PREPARE stmt FROM @sql; EXECUTE stmt; -- 安全写法（参数化查询） PREPARE stmt FROM 'SELECT * FROM users WHERE name = ?'; EXECUTE stmt USING @input_name;

1.9.2 权限管理

-- 创建最小权限用户 CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'complex_password'; GRANT SELECT, INSERT, UPDATE ON db_name.* TO 'app_user'@'192.168.1.%'; REVOKE DELETE ON db_name.* FROM 'app_user'@'192.168.1.%';

1.10 实用技巧集锦

1.10.1 数据导入导出

-- 导出CSV SELECT id, name, email INTO OUTFILE '/tmp/users.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM users; -- 导入CSV LOAD DATA INFILE '/tmp/products.csv' INTO TABLE products FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

1.10.2 日期处理技巧

-- 获取本周周一 SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS monday; -- 上个月同期对比 SELECT curr.sales AS current_sales, prev.sales AS previous_sales, (curr.sales - prev.sales) / prev.sales * 100 AS growth_rate FROM (SELECT SUM(amount) AS sales FROM orders WHERE order_date BETWEEN '2023-07-01' AND '2023-07-31') curr, (SELECT SUM(amount) AS sales FROM orders WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30') prev;

1.10.3 JSON数据处理

-- 提取JSON字段 SELECT id, JSON_EXTRACT(profile, '$.address.city') AS city, profile->>'$.hobbies[0]' AS primary_hobby FROM users WHERE JSON_CONTAINS(profile, '"reading"', '$.hobbies'); -- 更新JSON字段 UPDATE products SET specs = JSON_SET(specs, '$.weight', 500, '$.dimensions.width', 30) WHERE id = 1001;

在实际项目中，我发现合理使用临时表能显著提升复杂查询性能。例如处理多层统计报表时，可以分阶段将中间结果存入临时表：

-- 创建内存临时表 CREATE TEMPORARY TABLE temp_sales_summary ( product_id BIGINT, month DATE, total_sales DECIMAL(12,2), PRIMARY KEY (product_id, month) ) ENGINE=MEMORY; -- 分阶段计算 INSERT INTO temp_sales_summary SELECT product_id, DATE_FORMAT(order_date, '%Y-%m-01'), SUM(amount) FROM order_details WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY product_id, DATE_FORMAT(order_date, '%Y-%m-01'); -- 最终聚合 SELECT p.name, t.month, t.total_sales FROM temp_sales_summary t JOIN products p ON t.product_id = p.id ORDER BY t.month, t.total_sales DESC;