初级程序员必备的十大技能之数据库基础（一）

初级程序员必备的十大技能之数据库基础（一）

数据是互联网时代的“石油”

想象一下这些场景：

用户注册时，你需要在几毫秒内检查用户名是否已存在，并保存用户信息

电商大促时，数千人同时下单，你需要保证不会出现“超卖”

运营需要统计上周销售额最高的商品，你需要从几百万条订单中快速汇总数据

这些场景的背后关系数据库与sql语言，都离不开数据库。

数据库是存储、管理和检索数据的系统，是所有应用程序的“数据心脏”。无论你是前端、后端还是全栈开发者关系数据库与sql语言，掌握数据库基础知识都是必备技能。

本文将从零开始，带你系统掌握关系型数据库的核心知识：SQL 语言、表设计、索引优化、事务原理、性能调优。每一部分都有详细的原理说明和代码示例，让你不仅会写 SQL，更理解数据库背后的工作原理。

一、数据库概述：先理解“是什么”

1.1 为什么需要数据库？

1.2 数据库的分类

1.3 关系型数据库的核心概念

数据库 (Database)
    └── 表 (Table)  → 类似 Excel 工作表
         ├── 列 (Column) → 字段，如：id, name, age
         ├── 行 (Row)    → 记录，如：1, 张三, 25
         ├── 主键 (Primary Key) → 唯一标识每一行
         └── 外键 (Foreign Key) → 关联其他表

1.4 主流数据库的选择

初级程序员推荐：先学 MySQL 或关系数据库与sql语言初级程序员必备的十大技能之数据库基础（一），因为：

开源免费，学习成本低

就业市场需求最大

学会了 SQL初级程序员必备的十大技能之数据库基础（一），换其他关系型数据库成本很低

二、SQL 基础：数据操作的核心语言

SQL（ Query ）是与关系型数据库沟通的语言，分为四大类：

2.1 数据库与表的管理（DDL）

-- 1. 数据库操作
CREATE DATABASE mydb;                    -- 创建数据库
USE mydb;                                -- 切换到数据库
DROP DATABASE mydb;                      -- 删除数据库
-- 2. 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,   -- 主键，自动增长
    username VARCHAR(50) NOT NULL UNIQUE, -- 不能为空，值唯一
    email VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150), -- 检查约束
    status TINYINT DEFAULT 1,            -- 默认值
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 3. 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20);     -- 添加列
ALTER TABLE users MODIFY COLUMN age SMALLINT;       -- 修改列类型
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(20); -- 重命名列
ALTER TABLE users DROP COLUMN mobile;               -- 删除列
ALTER TABLE users RENAME TO user_accounts;          -- 重命名表
-- 4. 删除表
DROP TABLE users;
TRUNCATE TABLE users;  -- 清空表数据（保留结构）

2.2 插入、更新、删除（DML）

-- 1. 插入数据
-- 插入完整行
INSERT INTO users (username, email, age) VALUES ('zhangsan', 'zs@example.com', 25);
-- 插入多行
INSERT INTO users (username, email, age) VALUES 
    ('lisi', 'lisi@example.com', 30),
    ('wangwu', 'wangwu@example.com', 28);
-- 从其他表复制数据
INSERT INTO users_backup SELECT * FROM users;
-- 2. 更新数据
UPDATE users SET age = 26 WHERE username = 'zhangsan';
UPDATE users SET age = age + 1, updated_at = NOW() WHERE age < 18;
-- 3. 删除数据
DELETE FROM users WHERE username = 'zhangsan';
DELETE FROM users;  -- 删除所有行（逐行删除，慢，可回滚）
TRUNCATE users;     -- 清空表（直接释放空间，快，不可回滚）

2.3 数据查询（DQL）- 重中之重

查询是 SQL 中最常用、也最复杂的部分，下面从简单到复杂逐步学习。

基础查询

-- 最简单的查询
SELECT * FROM users;                    -- 所有列
SELECT username, email FROM users;      -- 指定列
SELECT 1+1 AS result;                   -- 计算表达式
-- 去重
SELECT DISTINCT age FROM users;
-- 限制结果数量
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 20 OFFSET 10;  -- 分页：第11-30条
-- 排序
SELECT * FROM users ORDER BY age ASC;   -- 升序（默认）
SELECT * FROM users ORDER BY age DESC;  -- 降序
SELECT * FROM users ORDER BY age DESC, username ASC; -- 多列排序

条件查询（WHERE）

-- 比较运算符
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE username IN ('zhangsan', 'lisi');
SELECT * FROM users WHERE email IS NULL;     -- 注意：不是 = NULL
SELECT * FROM users WHERE email IS NOT NULL;
-- 逻辑运算符
SELECT * FROM users WHERE age > 18 AND status = 1;
SELECT * FROM users WHERE age > 30 OR age < 18;
-- 模糊匹配（LIKE）
SELECT * FROM users WHERE username LIKE '张%';    -- 以"张"开头
SELECT * FROM users WHERE username LIKE '%三%';   -- 包含"三"
SELECT * FROM users WHERE email LIKE 'zhang_@%';  -- _ 匹配单个字符

聚合函数与分组（GROUP BY）

-- 聚合函数
SELECT COUNT(*) FROM users;               -- 总行数
SELECT COUNT(email) FROM users;           -- 非空邮件数
SELECT COUNT(DISTINCT age) FROM users;    -- 不同年龄的数量
SELECT AVG(age) FROM users;               -- 平均年龄
SELECT SUM(age) FROM users;               -- 年龄总和
SELECT MAX(age) FROM users;               -- 最大年龄
SELECT MIN(age) FROM users;               -- 最小年龄
-- 分组统计
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age;
-- 分组后筛选（HAVING）
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING COUNT(*) > 1;  -- 筛选出现次数大于1的年龄

WHERE vs 的区别：

多表查询（JOIN）

多表查询是 SQL 的核心难点，也是最常见的面试题。

-- 先创建示例表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    order_date DATE
);
-- 插入示例数据
INSERT INTO users (id, username) VALUES (1, '张三'), (2, '李四'), (3, '王五');
INSERT INTO orders (user_id, product_name, amount) VALUES 
    (1, '手机', 2999.00),
    (1, '耳机', 199.00),
    (2, '电脑', 5999.00),
    (2, '鼠标', 89.00);

不同类型的 JOIN

-- 1. INNER JOIN（内连接）：只返回两表匹配的行
SELECT u.username, o.product_name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 结果：张三(手机,耳机) 李四(电脑,鼠标) 
-- 王五没有订单，不显示
-- 2. LEFT JOIN（左连接）：返回左表所有行
SELECT u.username, o.product_name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 结果：张三(手机,耳机) 李四(电脑,鼠标) 王五(NULL,NULL)
-- 3. RIGHT JOIN（右连接）：返回右表所有行
SELECT u.username, o.product_name, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- 4. 自连接（自身连接）
-- 示例：查找年龄相同的用户
SELECT a.username AS user1, b.username AS user2, a.age
FROM users a
INNER JOIN users b ON a.age = b.age AND a.id != b.id;

JOIN 类型图解：

INNER JOIN:          LEFT JOIN:           RIGHT JOIN:
    ┌─────┐              ┌─────┐               ┌─────┐
    │  交集 │            │ 左表 │               │ 右表 │
    └─────┘              │ ┌─┐ │               │ ┌─┐ │
                         │ │交集│               │ │交集│ │
                         │ └─┘ │               │ └─┘ │
                         └─────┘               └─────┘

子查询（）

-- 1. WHERE 子句中的子查询
-- 查找年龄大于平均年龄的用户
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);
-- 2. FROM 子句中的子查询（派生表）
SELECT username, age 
FROM (SELECT * FROM users WHERE age >= 18) AS adult_users;
-- 3. SELECT 子句中的子查询（标量子查询）
SELECT username, 
       (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;
-- 4. EXISTS 子查询
-- 查询有订单的用户
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 5. IN 子查询
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

组合查询（UNION）

-- UNION：合并两个查询结果，自动去重
SELECT username FROM users WHERE age  30;
-- UNION ALL：不去重，性能更好
SELECT user_id FROM orders WHERE amount > 1000
UNION ALL
SELECT user_id FROM orders WHERE amount < 100;

执行顺序（非常重要！）

-- 一个完整 SELECT 语句的执行顺序
SELECT DISTINCT col1, agg_func(col2)   -- 5. 选择列、去重
FROM table1                            -- 1. 确定数据来源
JOIN table2 ON ...                     -- 2. 连接表
WHERE condition1                       -- 3. 行级筛选（分组前）
GROUP BY col1                          -- 4. 分组
HAVING condition2                      -- 6. 组级筛选（分组后）
ORDER BY col1                          -- 7. 排序
LIMIT offset, count;                   -- 8. 限制行数