企业官网建设流程全解析

龙华网站建设公司,点赞分享打赏 wordpress,网站平台建设技术基础,建筑工程培训有哪些目录 前言 一、基础铺垫#xff1a;测试环境搭建 二、核心分类#xff1a;SQL 四大类语句全解析 #xff08;一#xff09;DDL#xff1a;数据定义语言#xff08;表 / 库结构操作#xff09; 避坑提示#xff1a; #xff08;二#xff09;DML#xff1a;数据…目录前言一、基础铺垫测试环境搭建二、核心分类SQL 四大类语句全解析一DDL数据定义语言表 / 库结构操作避坑提示二DML数据操作语言数据增删改1. 插入数据INSERT2. 更新数据UPDATE3. 删除数据DELETE避坑提示三DQL数据查询语言核心数据查询1. 基础查询单表2. 多表关联查询JOIN3. 子查询4. 进阶查询MySQL 8.0避坑提示四DCL数据控制语言权限 / 事务1. 权限管理2. 事务控制3. 锁机制手动加锁避坑提示三、高级扩展存储过程 / 函数 / 触发器1. 存储过程批量执行 SQL类似函数2. 自定义函数总结核心要点回顾SQL 按功能分为 DDL结构操作、DML数据操作、DQL数据查询、DCL权限 / 事务四大类覆盖了数据库操作的全场景前言MySQL 作为全球使用最广泛的开源关系型数据库其 SQL 语句是操作数据库的核心 —— 从表结构的创建、数据的增删改查到复杂的多表关联、事务控制、存储过程SQL 语句的掌握程度直接决定了数据库使用的效率和深度。本文耗时 30 小时整理全覆盖 MySQL 常用 SQL 操作从基础的 DDL/DML/DQL/DCL 四大类语句到进阶的窗口函数、CTE、锁机制每个知识点都搭配「语法解析 实战案例 避坑提示」既是新手入门的零基础教程也是开发 / 运维人员的日常速查手册。适用人群零基础入门者、后端开发工程师、数据分析师前置要求已安装 MySQL5.7/8.0了解数据库 / 表的基本概念配套练习文末附测试表结构可直接复制执行所有案例。一、基础铺垫测试环境搭建为了让所有案例可落地先创建 2 张测试表用户表 订单表后续所有 SQL 操作均基于这两张表展开-- 1. 创建用户表user_info CREATE TABLE IF NOT EXISTS user_info ( user_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 用户ID主键, user_name VARCHAR(50) NOT NULL COMMENT 用户名, age TINYINT UNSIGNED COMMENT 年龄, gender ENUM(男,女,未知) DEFAULT 未知 COMMENT 性别, phone VARCHAR(20) UNIQUE COMMENT 手机号唯一索引, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户信息表; -- 2. 创建订单表order_info CREATE TABLE IF NOT EXISTS order_info ( order_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 订单ID主键, user_id INT NOT NULL COMMENT 用户ID外键关联user_info, order_amount DECIMAL(10,2) NOT NULL COMMENT 订单金额, order_status TINYINT COMMENT 订单状态1-待支付 2-已支付 3-已取消 4-已完成, pay_time DATETIME COMMENT 支付时间, INDEX idx_user_id (user_id) COMMENT 用户ID索引, INDEX idx_order_status (order_status) COMMENT 订单状态索引, FOREIGN KEY (user_id) REFERENCES user_info(user_id) ON DELETE CASCADE COMMENT 外键关联 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单信息表; -- 插入测试数据 INSERT INTO user_info (user_name, age, gender, phone) VALUES (张三, 25, 男, 13800138000), (李四, 30, 女, 13800138001), (王五, 28, 男, 13800138002), (赵六, 35, 女, 13800138003); INSERT INTO order_info (user_id, order_amount, order_status, pay_time) VALUES (1, 99.90, 2, 2024-01-01 10:00:00), (1, 199.00, 2, 2024-01-05 14:30:00), (2, 299.99, 1, NULL), (3, 599.00, 4, 2024-01-10 09:15:00), (4, 89.90, 3, 2024-01-12 16:20:00);二、核心分类SQL 四大类语句全解析SQL 语句按功能可分为 4 大类DDL数据定义语言、DML数据操作语言、DQL数据查询语言、DCL数据控制语言以下逐一详解。一DDL数据定义语言表 / 库结构操作DDL 用于创建、修改、删除数据库和表结构核心关键词CREATE/ALTER/DROP/TRUNCATE。操作类型核心语法实战案例创建数据库CREATE DATABASE [IF NOT EXISTS] 库名 [CHARSET字符集];CREATE DATABASE IF NOT EXISTS test_db CHARSETutf8mb4;删除数据库DROP DATABASE [IF EXISTS] 库名;DROP DATABASE IF EXISTS test_db;切换数据库USE 库名;USE test_db;创建表CREATE TABLE [IF NOT EXISTS] 表名 (字段 类型 约束,...) [引擎/字符集];见上文「测试环境搭建」的CREATE TABLE语句修改表名ALTER TABLE 旧表名 RENAME TO 新表名;ALTER TABLE user_info RENAME TO t_user;添加字段ALTER TABLE 表名 ADD COLUMN 字段名 类型 [约束];ALTER TABLE user_info ADD COLUMN email VARCHAR(100) UNIQUE;修改字段类型ALTER TABLE 表名 MODIFY COLUMN 字段名 新类型 [约束];ALTER TABLE user_info MODIFY COLUMN age INT UNSIGNED;修改字段名ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 类型 [约束];ALTER TABLE user_info CHANGE COLUMN phone mobile VARCHAR(20) UNIQUE;删除字段ALTER TABLE 表名 DROP COLUMN 字段名;ALTER TABLE user_info DROP COLUMN email;添加主键ALTER TABLE 表名 ADD PRIMARY KEY (字段名);ALTER TABLE order_info ADD PRIMARY KEY (order_id);添加索引ALTER TABLE 表名 ADD INDEX 索引名 (字段名);ALTER TABLE user_info ADD INDEX idx_age (age);添加唯一索引ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (字段名);ALTER TABLE user_info ADD UNIQUE INDEX idx_mobile (mobile);删除索引ALTER TABLE 表名 DROP INDEX 索引名;ALTER TABLE user_info DROP INDEX idx_age;删除表DROP TABLE [IF EXISTS] 表名;DROP TABLE IF EXISTS t_user;清空表数据TRUNCATE TABLE 表名;不可回滚比 DELETE 快TRUNCATE TABLE order_info;避坑提示TRUNCATE会重置自增主键且无法通过事务回滚生产环境慎用修改表结构ALTER会锁表大表操作建议在低峰期执行外键约束会影响删除 / 更新效率非必要场景可取消外键仅业务层控制关联。二DML数据操作语言数据增删改DML 用于操作表中的数据核心关键词INSERT/UPDATE/DELETE/REPLACE。1. 插入数据INSERT-- 基础插入指定字段 INSERT INTO user_info (user_name, age, gender, mobile) VALUES (孙七, 22, 男, 13800138004); -- 批量插入推荐减少IO INSERT INTO user_info (user_name, age, gender, mobile) VALUES (周八, 33, 女, 13800138005), (吴九, 27, 男, 13800138006); -- 从另一张表插入数据数据迁移 INSERT INTO user_info_bak (user_id, user_name, mobile) SELECT user_id, user_name, mobile FROM user_info WHERE age 25; -- 替换插入主键/唯一索引冲突时删除旧数据再插入 REPLACE INTO user_info (user_id, user_name, mobile) VALUES (1, 张三_修改, 13800138000);2. 更新数据UPDATE-- 基础更新 UPDATE user_info SET age 26 WHERE user_id 1; -- 批量更新CASE WHEN UPDATE user_info SET age CASE WHEN user_id 2 THEN 31 WHEN user_id 3 THEN 29 END WHERE user_id IN (2,3); -- 关联更新根据另一张表更新 UPDATE order_info o JOIN user_info u ON o.user_id u.user_id SET o.order_status 4 WHERE u.age 30 AND o.order_status 2;3. 删除数据DELETE-- 基础删除 DELETE FROM user_info WHERE user_id 6; -- 批量删除 DELETE FROM user_info WHERE age 25; -- 关联删除删除关联数据 DELETE o FROM order_info o JOIN user_info u ON o.user_id u.user_id WHERE u.gender 女 AND o.order_status 3; -- 删除所有数据可回滚比TRUNCATE慢 DELETE FROM order_info;避坑提示UPDATE/DELETE必须加WHERE条件否则会修改 / 删除全表数据生产环境可开启sql_safe_updates限制批量操作建议分批次执行如每次 1000 条避免锁表REPLACE本质是「删除 插入」会触发触发器慎用。三DQL数据查询语言核心数据查询DQL 是 SQL 中最核心、最复杂的部分核心关键词SELECT涵盖单表查询、多表关联、聚合分析、窗口函数等。1. 基础查询单表-- 查询所有字段不推荐生产环境指定字段 SELECT * FROM user_info; -- 查询指定字段 SELECT user_id, user_name, age FROM user_info; -- 去重查询 SELECT DISTINCT gender FROM user_info; -- 条件查询WHERE SELECT * FROM user_info WHERE age 25 AND gender 男; -- 模糊查询LIKE SELECT * FROM user_info WHERE user_name LIKE 张%; -- 左匹配走索引 SELECT * FROM user_info WHERE user_name LIKE %三; -- 右匹配不走索引 SELECT * FROM user_info WHERE user_name LIKE %五%; -- 全匹配不走索引 -- 范围查询IN/BETWEEN SELECT * FROM user_info WHERE age BETWEEN 25 AND 30; SELECT * FROM user_info WHERE user_id IN (1,2,3); -- 空值判断IS NULL/IS NOT NULL SELECT * FROM order_info WHERE pay_time IS NULL; -- 排序ORDER BYASC升序默认DESC降序 SELECT * FROM user_info ORDER BY age DESC, user_id ASC; -- 分页查询LIMITLIMIT 偏移量, 条数 SELECT * FROM user_info ORDER BY user_id LIMIT 0, 2; -- 第1页每页2条 SELECT * FROM user_info ORDER BY user_id LIMIT 2, 2; -- 第2页每页2条 -- 聚合查询COUNT/SUM/AVG/MAX/MIN SELECT COUNT(*) AS total_user, -- 总用户数 SUM(age) AS sum_age, -- 年龄总和 AVG(age) AS avg_age, -- 平均年龄 MAX(age) AS max_age, -- 最大年龄 MIN(age) AS min_age -- 最小年龄 FROM user_info; -- 分组查询GROUP BY SELECT gender, COUNT(*) AS user_count FROM user_info GROUP BY gender; -- 分组过滤HAVING对聚合结果过滤 SELECT gender, AVG(age) AS avg_age FROM user_info GROUP BY gender HAVING avg_age 28;2. 多表关联查询JOIN-- 内连接INNER JOIN只返回匹配的记录 SELECT u.user_name, o.order_id, o.order_amount FROM user_info u INNER JOIN order_info o ON u.user_id o.user_id; -- 左连接LEFT JOIN返回左表所有记录右表匹配不到则为NULL SELECT u.user_name, o.order_id, o.order_amount FROM user_info u LEFT JOIN order_info o ON u.user_id o.user_id; -- 右连接RIGHT JOIN返回右表所有记录左表匹配不到则为NULL SELECT u.user_name, o.order_id, o.order_amount FROM user_info u RIGHT JOIN order_info o ON u.user_id o.user_id; -- 全连接FULL JOINMySQL无原生支持用UNION模拟 SELECT u.user_name, o.order_id FROM user_info u LEFT JOIN order_info o ON u.user_id o.user_id UNION SELECT u.user_name, o.order_id FROM user_info u RIGHT JOIN order_info o ON u.user_id o.user_id; -- 交叉连接CROSS JOIN笛卡尔积慎用 SELECT u.user_name, o.order_id FROM user_info u CROSS JOIN order_info o;3. 子查询-- 标量子查询返回单个值 SELECT * FROM order_info WHERE user_id (SELECT user_id FROM user_info WHERE user_name 张三); -- 列子查询返回一列 SELECT * FROM order_info WHERE user_id IN (SELECT user_id FROM user_info WHERE age 25); -- 行子查询返回一行 SELECT * FROM user_info WHERE (user_id, age) (1, 25); -- 表子查询返回多行多列 SELECT * FROM (SELECT user_id, user_name FROM user_info WHERE gender 男) AS t; -- 关联子查询依赖外部查询 SELECT u.user_name, (SELECT COUNT(*) FROM order_info o WHERE o.user_id u.user_id) AS order_count FROM user_info u;4. 进阶查询MySQL 8.0-- 1. 窗口函数排名/分组统计 -- 按订单金额排名ROW_NUMBER不重复RANK跳级DENSE_RANK不跳级 SELECT user_id, order_id, order_amount, ROW_NUMBER() OVER (ORDER BY order_amount DESC) AS rn, RANK() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS user_rank FROM order_info; -- 2. 公用表表达式CTEWITH子句 WITH user_order AS ( SELECT u.user_id, u.user_name, COUNT(o.order_id) AS order_count FROM user_info u LEFT JOIN order_info o ON u.user_id o.user_id GROUP BY u.user_id, u.user_name ) SELECT * FROM user_order WHERE order_count 1; -- 3. 递归查询查询层级数据如部门树 -- 先创建部门表 CREATE TABLE dept ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50), parent_id INT COMMENT 上级部门ID ); INSERT INTO dept VALUES (1, 总部门, 0), (2, 技术部, 1), (3, 产品部, 1), (4, 前端组, 2), (5, 后端组, 2); -- 递归查询所有部门的层级 WITH RECURSIVE dept_cte AS ( SELECT dept_id, dept_name, parent_id, 1 AS level FROM dept WHERE parent_id 0 UNION ALL SELECT d.dept_id, d.dept_name, d.parent_id, dc.level 1 FROM dept d JOIN dept_cte dc ON d.parent_id dc.dept_id ) SELECT * FROM dept_cte;避坑提示SELECT *会增加网络传输和内存消耗生产环境务必指定字段子查询尽量用EXISTS替代IN结果集大时性能更优窗口函数需 MySQL 8.0 版本低版本可通过变量模拟分页查询LIMIT 偏移量, 条数在偏移量大时性能差建议用主键分页WHERE id 偏移量 LIMIT 条数。四DCL数据控制语言权限 / 事务DCL 用于管理数据库权限和事务核心关键词GRANT/REVOKE/COMMIT/ROLLBACK。1. 权限管理-- 创建用户 CREATE USER test_user% IDENTIFIED BY Test123456; -- %表示允许所有IP访问 -- 授权给用户授予test_db库的所有权限 GRANT ALL PRIVILEGES ON test_db.* TO test_user%; -- 授权指定权限查询/插入/更新 GRANT SELECT, INSERT, UPDATE ON test_db.user_info TO test_user%; -- 刷新权限 FLUSH PRIVILEGES; -- 查看用户权限 SHOW GRANTS FOR test_user%; -- 撤销权限 REVOKE DELETE ON test_db.* FROM test_user%; -- 删除用户 DROP USER test_user%; -- 修改用户密码 ALTER USER test_user% IDENTIFIED BY NewTest123456;2. 事务控制-- 开启事务 START TRANSACTION; -- 或 BEGIN; -- 执行操作 UPDATE user_info SET age age 1 WHERE user_id 1; UPDATE order_info SET order_status 4 WHERE user_id 1 AND order_status 2; -- 提交事务确认修改 COMMIT; -- 回滚事务撤销修改 -- ROLLBACK; -- 保存点部分回滚 START TRANSACTION; UPDATE user_info SET age 27 WHERE user_id 1; SAVEPOINT sp1; -- 创建保存点 UPDATE order_info SET order_amount 200 WHERE order_id 1; ROLLBACK TO sp1; -- 回滚到保存点仅撤销第二个更新 COMMIT;3. 锁机制手动加锁-- 行锁FOR UPDATE仅在事务中生效 START TRANSACTION; SELECT * FROM user_info WHERE user_id 1 FOR UPDATE; -- 加排他锁 UPDATE user_info SET age 28 WHERE user_id 1; COMMIT; -- 读锁LOCK IN SHARE MODE START TRANSACTION; SELECT * FROM user_info WHERE user_id 1 LOCK IN SHARE MODE; -- 加共享锁 COMMIT;避坑提示授权时遵循「最小权限原则」避免给用户授予ALL PRIVILEGES事务默认是自动提交autocommit1开启事务后需手动COMMIT/ROLLBACK行锁仅在使用索引查询时生效否则会升级为表锁导致性能问题。三、高级扩展存储过程 / 函数 / 触发器1. 存储过程批量执行 SQL类似函数-- 创建存储过程根据用户ID查询订单总数 DELIMITER // -- 修改分隔符避免;终止存储过程 CREATE PROCEDURE get_order_count(IN p_user_id INT, OUT p_count INT) BEGIN SELECT COUNT(*) INTO p_count FROM order_info WHERE user_id p_user_id; END // DELIMITER ; -- 恢复分隔符 -- 调用存储过程 SET count 0; CALL get_order_count(1, count); SELECT count; -- 删除存储过程 DROP PROCEDURE IF EXISTS get_order_count;2. 自定义函数-- 创建函数计算订单金额总和按用户ID DELIMITER // CREATE FUNCTION calc_total_amount(p_user_id INT) RETURNS DECIMAL(10,2) DETERMINISTIC -- 确定性函数相同输入返回相同输出 BEGIN DECLARE total DECIMAL(10,2); SELECT SUM(order_amount) INTO total FROM order_info WHERE user_id p_user_id; RETURN IFNULL(total, 0.00); END // DELIMITER ; -- 调用函数 SELECT user_name, calc_total_amount(user_id) AS total_amount FROM user_info; -- 删除函数 DROP FUNCTION IF EXISTS calc_total_amount;3. 触发器自动执行 SQL如数据变更时-- 创建触发器插入订单时自动更新用户表的最后订单时间 -- 先给用户表添加字段 ALTER TABLE user_info ADD COLUMN last_order_time DATETIME; DELIMITER // CREATE TRIGGER trg_after_order_insert AFTER INSERT ON order_info FOR EACH ROW -- 每行触发 BEGIN UPDATE user_info SET last_order_time NOW() WHERE user_id NEW.user_id; END // DELIMITER ; -- 测试触发器插入订单后查看last_order_time INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 89.90, 1); SELECT user_name, last_order_time FROM user_info WHERE user_id 1; -- 删除触发器 DROP TRIGGER IF EXISTS trg_after_order_insert;总结核心要点回顾SQL 按功能分为 DDL结构操作、DML数据操作、DQL数据查询、DCL权限 / 事务四大类覆盖了数据库操作的全场景DQL 是核心难点需重点掌握 JOIN 关联、聚合查询、窗口函数等进阶用法同时规避索引失效、笛卡尔积等常见问题生产环境使用 SQL 需遵循「性能优先 安全第一」原则比如加 WHERE 条件、最小权限授权、避免长事务等。基于本文的测试表手动执行所有 SQL 案例加深理解结合EXPLAIN分析每条 SQL 的执行计划理解索引的作用学习 MySQL 慢查询日志、性能_schema 等工具提升问题定位能力。