企业官网建设流程全解析

1. 数据库基本概念

· 数据库（Database）：存储数据的容器。

表（Table）：由行和列组成，类似 Excel 表。

行（Row）：一条记录。

列（Column）：字段，定义数据类型。

主键（Primary Key）：唯一标识一行，非空且唯一。

外键（Foreign Key）：关联另一张表的主键，保证参照完整性。

索引（Index）：加速查询的数据结构，常用 B+ 树。

SQL：结构化查询语言，用于操作数据库。

2. SQL 分类

分类 说明 常用语句

DDL 数据定义 CREATE、ALTER、DROP、TRUNCATE

DML 数据操作 INSERT、UPDATE、DELETE

DQL 数据查询 SELECT

DCL 权限控制 GRANT、REVOKE

TCL 事务控制 COMMIT、ROLLBACK、SAVEPOINT

3. 常用数据类型

整数：TINYINT、INT、BIGINT

小数：DECIMAL(m,d) 精确；FLOAT/DOUBLE 近似

字符串：CHAR 定长、VARCHAR 变长、TEXT 大文本

日期时间：DATE、TIME、DATETIME、TIMESTAMP

布尔：MySQL 用 TINYINT(1) 表示

枚举/集合：ENUM、SET

二进制：BLOB

金额建议用 DECIMAL，避免浮点误差。

4. 建表与约束

```sql

CREATE TABLE user (

id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(50) NOT NULL,

age INT DEFAULT 0,

email VARCHAR(100) UNIQUE,

dept_id INT,

CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES dept(id)

);

```

常见约束：

PRIMARY KEY 主键

FOREIGN KEY 外键

UNIQUE 唯一

NOT NULL 非空

DEFAULT 默认值

AUTO_INCREMENT 自增

CHECK 检查约束

表操作：

```sql

ALTER TABLE user ADD COLUMN sex CHAR(1);

DROP TABLE user;

TRUNCATE TABLE user; -- 清空表，不可回滚，自增重置

```

5. 增删改查

插入

```sql

INSERT INTO user(name, age) VALUES ('张三', 20);

```

更新

```sql

UPDATE user SET age = 21 WHERE id = 1;

```

删除

```sql

DELETE FROM user WHERE id = 1;

```

查询

```sql

SELECT id, name FROM user

WHERE age > 18

ORDER BY id DESC

LIMIT 10;

```

查询关键字执行顺序大致：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

6. 查询进阶

去重：SELECT DISTINCT age FROM user;

模糊查询：LIKE '%张%'

范围：BETWEEN ... AND ...

集合：IN (1,2,3)

空值：IS NULL、IS NOT NULL

逻辑：AND、OR、NOT

排序：ORDER BY age ASC/DESC

分页：LIMIT offset, size

7. 聚合函数与分组

```sql

SELECT dept_id, COUNT(*) AS num, AVG(age)

FROM user

GROUP BY dept_id

HAVING num > 5;

```

常用聚合：

COUNT() 计数

SUM() 求和

AVG() 平均

MAX() / MIN() 最大最小

注意：

WHERE 过滤行，不能直接用聚合函数。

HAVING 过滤分组后的结果。

8. 多表连接

内连接：只返回匹配行

```sql

SELECT u.name, d.name

FROM user u

INNER JOIN dept d ON u.dept_id = d.id;

```

左连接：左表全保留

右连接：右表全保留

交叉连接：笛卡尔积

自连接：表自己连接自己

9. 子查询

```sql

SELECT * FROM user

WHERE age > (SELECT AVG(age) FROM user);

```

常见形式：

IN / NOT IN

EXISTS / NOT EXISTS

标量子查询

派生表：FROM (SELECT ...) t

10. 索引

作用：加快查询，但会增加写入和存储成本。

类型：

主键索引

唯一索引

普通索引

复合索引

全文索引

底层：InnoDB 常用 B+ 树。

最左前缀原则：复合索引 (a,b,c) 通常能用于 a、a,b、a,b,c。

索引失效常见原因：

对索引列使用函数

隐式类型转换

LIKE '%xx'

OR 条件未全部命中索引

不符合最左前缀

查看执行计划：

```sql

EXPLAIN SELECT * FROM user WHERE id = 1;

```

11. 事务

事务保证一组操作要么全部成功，要么全部失败。

ACID

A 原子性：不可分割

C 一致性：数据状态合法

I 隔离性：事务间互不干扰

D 持久性：提交后永久保存

隔离级别

级别 可能问题

读未提交 脏读

读已提交 不可重复读

可重复读 幻读，MySQL 默认

串行化 性能最低

常见问题：

脏读：读到未提交数据

不可重复读：同一事务两次读结果不同

幻读：同一条件两次查询行数不同

```sql

START TRANSACTION;

UPDATE account SET money = money - 100 WHERE id = 1;

UPDATE account SET money = money + 100 WHERE id = 2;

COMMIT;

-- 或 ROLLBACK;

```

12. 视图

视图是虚拟表，保存查询逻辑。

```sql

CREATE VIEW v_user AS

SELECT id, name FROM user WHERE age > 18;

```

优点：简化查询、权限控制；缺点：复杂视图性能可能较差。

13. 存储过程、函数、触发器

存储过程：一组 SQL 集合，可带参数。

函数：有返回值，可用于 SQL 表达式。

触发器：在 INSERT/UPDATE/DELETE 前后自动执行。

基础了解即可，业务中需注意可维护性。

14. 存储引擎

引擎 特点

InnoDB 默认，支持事务、外键、行锁、MVCC

MyISAM 不支持事务，表锁，查询较快

Memory 内存表，速度快，重启丢失

15. 字符集

推荐：

```sql

CHARACTER SET utf8mb4

COLLATE utf8mb4_general_ci

```

utf8mb4 支持 Emoji 和完整 Unicode。

16. 权限管理

```sql

CREATE USER 'test'@'%' IDENTIFIED BY '123456';

GRANT SELECT, INSERT ON db.* TO 'test'@'%';

REVOKE INSERT ON db.* FROM 'test'@'%';

FLUSH PRIVILEGES;

```

17. 备份与恢复

逻辑备份：mysqldump

```bash

mysqldump -u root -p dbname > db.sql

mysql -u root -p dbname < db.sql

```

物理备份：直接备份数据文件，需配合工具。

二进制日志 binlog 可用于恢复和主从复制。

18. 常见函数

字符串：CONCAT、SUBSTRING、LENGTH、TRIM、REPLACE

数值：ROUND、CEIL、FLOOR、ABS

日期：NOW()、CURDATE()、DATE_FORMAT()、DATEDIFF()

条件：IF()、IFNULL()、CASE WHEN

聚合：COUNT、SUM、AVG、MAX、MIN

19. 优化基础

避免 SELECT *

为 WHERE、JOIN、ORDER BY 常用列建索引

使用 EXPLAIN 分析执行计划

避免索引列运算和隐式转换

分页大偏移优化：基于主键或游标

合理使用联合索引和最左前缀

控制事务大小，避免长事务

开启慢查询日志定位问题 SQL

20. 数据库设计基础

三大范式：

1NF：字段原子性

2NF：消除非主属性对主键的部分依赖

3NF：消除非主属性对主键的传递依赖

实际设计常适度反范式，提升查询性能。

一对多：外键放在多的一方。

多对多：使用中间表。