一、SQL基本数据类型
SQL 数据类型速查表
| 类别 | 数据类型 | 说明 | 范围/示例 | 适用场景 |
|---|---|---|---|---|
| 整数类型 | INT / INTEGER |
标准整数类型 | -2³¹ 到 2³¹-1 (-2,147,483,648 到 2,147,483,647) | ID、年龄、数量等 |
SMALLINT |
小范围整数 | -32,768 到 32,767 | 小范围数值 | |
BIGINT |
大范围整数 | -2⁶³ 到 2⁶³-1 | 大额订单号、时间戳 | |
TINYINT |
极小整数 (MySQL) | 0 到 255 或 -128 到 127 | 状态码、布尔值模拟 | |
| 小数类型 | DECIMAL(p,s) |
精确小数 p=总位数, s=小数位 |
DECIMAL(10,2) → 12345678.99 | 金融金额、精确计算 |
FLOAT |
单精度浮点数 | ≈6-7位精度 | 科学计算 | |
DOUBLE |
双精度浮点数 | ≈15位精度 | 高精度测量 | |
| 字符串类型 | CHAR(n) |
定长字符串 空格填充 |
CHAR(10) ‘ABC’ → 'ABC ’ | 固定长度代码（如国家代码） |
VARCHAR(n) |
变长字符串 | VARCHAR(255) | 姓名、地址等变长文本 | |
TEXT |
大文本数据 | 最多 65,535 字节 (MySQL) | 文章内容、描述 | |
| 日期时间 | DATE |
日期 | ‘2023-08-15’ | 出生日期、事件日期 |
TIME |
时间 | ‘14:30:00’ | 会议时间 | |
DATETIME |
日期+时间 | ‘2023-08-15 14:30:00’ | 订单时间、日志时间戳 | |
TIMESTAMP |
自动记录的时间戳 | 自动记录修改时间 | 创建时间/修改时间 | |
| 二进制类型 | BLOB |
二进制大对象 | 最大 65,535 字节 | 图片、PDF等文件存储 |
BINARY(n) |
定长二进制数据 | BINARY(16) | 加密数据、哈希值 | |
| 布尔类型 | BOOLEAN |
逻辑值 | TRUE/FALSE | 开关状态、是否标记 |
| 特殊类型 | ENUM('val1','val2') |
枚举值 | ENUM(‘Red’,‘Green’,‘Blue’) | 状态选项、固定分类 |
JSON |
JSON格式数据 (现代数据库) | {“name”: “John”, “age”: 30} | 灵活数据结构 | |
UUID |
全局唯一标识符 (PostgreSQL等) | ‘a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11’ | 分布式ID生成 |
二、SQL关键字解释
SQL查询知识点
基本查询结构
SELECT [DISTINCT] 列1, 列2, 聚合函数(列3) -- 选择最终显示的列
FROM 表1 -- 初始数据源
[JOIN 表2 ON 连接条件] -- 表连接
WHERE 行级过滤条件 -- 行过滤
GROUP BY 分组列 -- 数据分组
HAVING 组级过滤条件 -- 组过滤
ORDER BY 排序列 [ASC|DESC] -- 结果排序
LIMIT 结果数量; -- 行数限制
关键字详解（含执行变化案例）
1. SELECT
-
作用：指定最终结果集中显示的列
-
执行变化：从中间结果集中抽取指定列
-
示例：
/* 原始products表（执行前）： | id | product_name | price | |----|--------------|-------| | 1 | Laptop | 1200 | | 2 | Phone | 800 | | 3 | Tablet | 600 | */ SELECT product_name, price * 0.9 AS sale_price FROM products; /* 执行后结果集： | product_name | sale_price | |--------------|------------| | Laptop | 1080 | | Phone | 720 | | Tablet | 540 | */
2. FROM
- 作用：指定查询的主数据源
- 执行变化：加载初始数据集
- *示例：
FROM employees -- 加载员工表全部数据
3. JOIN（表连接）
- 作用：组合多个表的数据
- 执行变化：扩展列维度
INNER JOIN 示例：
/* employees表（左表）：
| id | name | dept |
|----|-------|--------|
| 1 | Alice | Sales |
| 2 | Bob | IT |
orders表（右表）：
| order_id | emp_id | amount |
|----------|--------|--------|
| 101 | 1 | 200 |
| 102 | 1 | 300 | */
SELECT e.name, o.amount
FROM employees e
INNER JOIN orders o ON e.id = o.emp_id;
/* 执行后结果集：
| name | amount |
|-------|--------|
| Alice | 200 |
| Alice | 300 | */
4. WHERE
-
作用：行级数据过滤
-
执行变化：减少行数
-
示例：
/* 原始orders表： | id | amount | status | |----|--------|----------| | 1 | 100 | shipped | | 2 | 200 | pending | | 3 | 150 | shipped | */ SELECT id, amount FROM orders WHERE status = 'shipped'; /* 执行后结果集： | id | amount | |----|--------| | 1 | 100 | | 3 | 150 | */
5. GROUP BY
- 作用：数据分组聚合
- 执行变化：行数减少，出现聚合值
-
执行变化：行数减少，出现聚合值
-
聚合值
示例：
/* 原始employees表：
| id | name | dept | salary |
|----|--------|--------|--------|
| 1 | Alice | Sales | 5000 |
| 2 | Bob | Sales | 6000 |
| 3 | Charlie| IT | 7000 | */
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept;
/* 执行后结果集：
| dept | avg_salary |
|-------|------------|
| Sales | 5500 |
| IT | 7000 | */
6. HAVING
- 作用：分组后结果过滤
- 执行变化：减少分组数量
- 与WHERE对比：
| 特性 | WHERE | HAVING |
|---|---|---|
| 执行时机 | 分组前 | 分组后 |
| 操作对象 | 原始行 | 分组结果 |
| 聚合函数 | 不可用 | 可用 |
示例：
/* 续上GROUP BY结果：
| dept | avg_salary |
|-------|------------|
| Sales | 5500 |
| IT | 7000 | */
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 6000;
/* 执行后结果集：
| dept | avg_salary |
|------|------------|
| IT | 7000 | */
7. ORDER BY
-
作用：结果排序
-
执行变化：行顺序改变，内容不变
-
排序方式：
ASC升序（默认）DESC降序
-
示例：
/* 原始结果： | product | price | |---------|-------| | Laptop | 1200 | | Phone | 800 | | Tablet | 600 | */ SELECT product, price FROM products ORDER BY price DESC; /* 执行后结果集： | product | price | |---------|-------| | Laptop | 1200 | | Phone | 800 | | Tablet | 600 | */
8. LIMIT
-
作用：限制返回行数
-
执行变化：截断结果集
-
示例：
/* 假设排序后结果（10条记录） */ SELECT product, price FROM products ORDER BY price DESC LIMIT 5; /* 执行后结果集：只保留前5条记录 */
9. OFFSET
OFFSET 通常与 LIMIT 配合使用，用于实现分页查询或跳过指定行数
-
跳过结果集的前 N 行
-
典型应用场景：分页查询（如第2页、第3页数据）
-
语法结构：
LIMIT 返回行数 OFFSET 跳过行数; -- 或等效写法 -- LIMIT 跳过行数, 返回行数; # MySQL专用语法
🧩 执行机制图解
📊 案例演示（分步说明）
初始数据表 products
| id | product_name | price |
|---|---|---|
| 1 | Laptop | 1200 |
| 2 | Phone | 800 |
| 3 | Tablet | 600 |
| 4 | Monitor | 300 |
| 5 | Keyboard | 50 |
| 6 | Mouse | 30 |
| 7 | Headphones | 150 |
查询1：基础分页（每页3条）
/* 获取第1页数据 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 0; -- 从第0行开始取3条
/* 结果集：
| id | product_name |
|----|--------------|
| 1 | Laptop |
| 2 | Phone |
| 3 | Tablet | */
查询2：跳转到第2页
/* 获取第2页数据 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 3; -- 跳过前3条，取接下来3条
/* 结果集：
| id | product_name |
|----|--------------|
| 4 | Monitor |
| 7 | Headphones |
| 5 | Keyboard | */
查询3：等效写法（MySQL）
/* 获取第3页数据 */
SELECT id, product_name
FROM products
ORDER BY price DESC
LIMIT 6, 3; -- 等效于 OFFSET 6 LIMIT 3
/* 结果集：
| id | product_name |
|----|--------------|
| 6 | Mouse | # 只剩1条数据 */
⚠️ 重要注意事项
- 执行顺序：
OFFSET在ORDER BY之后、LIMIT之前执行
10.字符串操作与通配符
| 通配符 | 功能 | 示例 | 匹配示例 |
|---|---|---|---|
% |
匹配任意长度字符 | 'John%' |
John, Johnson |
_ |
匹配单个字符 | '_ean' |
Dean, Jean |
[ ] |
匹配指定字符集 | '[a]%' (SQL Server) |
apple, elephant |
[^] |
排除指定字符集 | '[^0-9]%' |
Apple, #123 |
🧰 核心字符串函数
1. 基础操作函数
| 函数 | 功能 | 示例 | 结果 |
|---|---|---|---|
CONCAT(s1, s2) |
字符串拼接 | CONCAT('Hello', ' ', 'World') |
Hello World |
LENGTH(s) |
字符串长度 | LENGTH('SQL') |
3 |
UPPER(s) |
转为大写 | UPPER('hello') |
HELLO |
LOWER(s) |
转为小写 | LOWER('SQL') |
sql |
TRIM(s) |
去除两端空格 | TRIM(' text ') |
text |
2. 子字符串操作
| 函数 | 功能 | 示例 | 结果 |
|---|---|---|---|
SUBSTRING(s, start, len) |
提取子字符串 | SUBSTRING('Database', 2, 4) |
atab |
LEFT(s, n) |
提取左侧n个字符 | LEFT('2023-06-15', 4) |
2023 |
RIGHT(s, n) |
提取右侧n个字符 | RIGHT('user@email.com', 3) |
com |
REPLACE(s, old, new) |
替换字符串 | REPLACE('I like SQL', 'like', 'love') |
I love SQL |
REVERSE(s) |
反转字符串 | REVERSE('ABCD') |
DCBA |
3. 高级搜索函数
| 函数 | 功能 | 示例 |
|---|---|---|
POSITION(sub IN s) |
返回子串位置 | POSITION('@' IN 'user@domain.com') → 5 |
CHAR_LENGTH(s) |
字符数（支持多字节） | CHAR_LENGTH('中文') → 2 |
INSTR(s, sub) |
查找子串位置 | INSTR('SQL Tutorial', 'Tut') → 5 |
⚙️ 实战综合应用
场景：邮箱格式标准化
UPDATE users
SET email = LOWER(
CONCAT(
SUBSTRING(email, 1, POSITION('@' IN email) - 1), -- 用户名部分
'@company.com' -- 统一域名
)
)
WHERE email NOT LIKE '%@company.com'; -- 筛选需要修改的记录
场景：产品编号验证
/* 验证格式：AA-000 */
SELECT product_code
FROM products
WHERE product_code LIKE '__-___' -- 长度验证
AND SUBSTRING(product_code, 1, 2) REGEXP '^[A-Z]{2}$' -- 前两位大写字母
AND SUBSTRING(product_code, 4, 3) REGEXP '^[0-9]{3}$'; -- 后三位数字
场景：动态搜索
/* 根据输入关键词灵活搜索 */
SET @keyword = 'pro yoga';
SELECT * FROM products
WHERE
product_name LIKE CONCAT('%', REPLACE(@keyword, ' ', '%'), '%')
OR category LIKE CONCAT('%', @keyword, '%');
完整案例：销售分析查询
业务场景：分析2023年各部门销售业绩
初始数据
employees表：
| id | name | dept |
|---|---|---|
| 1 | Alice | Sales |
| 2 | Bob | Sales |
| 3 | Charlie | IT |
orders表：
| order_id | emp_id | amount | order_date |
|---|---|---|---|
| 101 | 1 | 200 | 2023-02-01 |
| 102 | 1 | 300 | 2023-03-15 |
| 103 | 2 | 150 | 2023-01-10 |
| 104 | 3 | 400 | 2023-04-20 |
| 105 | 1 | 500 | 2022-12-31 |
分步执行过程
SELECT
e.dept,
SUM(o.amount) AS total_sales,
COUNT(*) AS order_count
FROM employees e
INNER JOIN orders o ON e.id = o.emp_id
WHERE o.order_date >= '2023-01-01'
GROUP BY e.dept
HAVING SUM(o.amount) > 0
ORDER BY total_sales DESC;
分步结果变化：
-
FROM + JOIN（初始连接）：
| e.id | e.name | dept | o.order_id | amount | order_date | |------|--------|-------|------------|--------|-------------| | 1 | Alice | Sales | 101 | 200 | 2023-02-01 | | 1 | Alice | Sales | 102 | 300 | 2023-03-15 | | 1 | Alice | Sales | 105 | 500 | 2022-12-31 | | 2 | Bob | Sales | 103 | 150 | 2023-01-10 | | 3 | Charlie| IT | 104 | 400 | 2023-04-20 | -
WHERE（日期过滤）：
| e.id | name | dept | order_id | amount | order_date | |------|--------|-------|----------|--------|-------------| | 1 | Alice | Sales | 101 | 200 | 2023-02-01 | | 1 | Alice | Sales | 102 | 300 | 2023-03-15 | | 2 | Bob | Sales | 103 | 150 | 2023-01-10 | | 3 | Charlie| IT | 104 | 400 | 2023-04-20 | -
GROUP BY（按部门分组）：
Sales组：Alice(200+300) + Bob(150) = 650 IT组：Charlie(400) = 400 -
HAVING（组过滤）：
两个组都满足SUM(amount)>0 -
SELECT（显示结果）：
| dept | total_sales | order_count | |-------|-------------|-------------| | Sales | 650 | 3 | | IT | 400 | 1 | -
ORDER BY（最终排序）：
| dept | total_sales | order_count | |-------|-------------|-------------| | Sales | 650 | 3 | -- 销售额最高 | IT | 400 | 1 |
SQL更新
1. 插入数据（INSERT）
关键字解释
INSERT INTO：指定要插入数据的表名。VALUES：定义插入的具体值。SET（可选）：在部分数据库（如MySQL）中可用，用于指定列名和值的对应关系。
语法结构
-- 插入单条数据
INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);
-- 插入多条数据
INSERT INTO 表名 (列1, 列2, ...) VALUES
(值1, 值2, ...),
(值3, 值4, ...);
示例
假设有一个 students 表：
| id | name | age |
|---|---|---|
| 1 | Alice | 20 |
插入新数据：
INSERT INTO students (name, age) VALUES ('Bob', 22);
2. 修改数据（UPDATE）
关键字解释
UPDATE：指定要修改数据的表名。SET：定义要修改的列名及其新值。WHERE（可选）：限定修改的行范围（必须谨慎使用）。
语法结构
UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2
WHERE 条件;
示例
修改 students 表中 id=1 的学生的年龄：
UPDATE students
SET age = 21
WHERE id = 1;
3. 删除数据（DELETE）
关键字解释
DELETE FROM：指定要删除数据的表名。WHERE（可选）：限定删除的行范围（必须谨慎使用）。
语法结构
DELETE FROM 表名
WHERE 条件;
示例
删除 students 表中 id=2 的学生：
DELETE FROM students
WHERE id = 2;
4. 综合示例
场景
修改订单表中某个用户的订单状态，并删除过期订单，最后插入新订单。
SQL 操作
-- 修改订单状态
UPDATE orders
SET status = '已完成'
WHERE user_id = 1001 AND status = '处理中';
-- 删除过期订单
DELETE FROM orders
WHERE order_date < '2023-01-01';
-- 插入新订单
INSERT INTO orders (user_id, product, status)
VALUES (1002, 'Laptop', '已下单');
三、创建，修改，删除表
🏗️ 一、创建表 (CREATE TABLE)
📌 核心语法
CREATE TABLE [IF NOT EXISTS] 表名 (
列名1 数据类型 [约束],
列名2 数据类型 [约束],
...
[表级约束]
);
🛡️ 常用约束
| 约束类型 | 关键字 | 核心作用 | 示例 | 可视化说明 |
|---|---|---|---|---|
| 主键约束 | PRIMARY KEY |
唯一标识每行记录，禁止重复和 NULL | id INT PRIMARY KEY |
🔑 唯一标识符 |
| 外键约束 | FOREIGN KEY |
强制表间引用完整性 | user_id INT REFERENCES users(id) ON DELETE CASCADE |
⛓️ 表间连接关系 |
| 唯一约束 | UNIQUE |
确保列值唯一（允许多个 NULL） | email VARCHAR(255) UNIQUE |
✨ 禁止重复值 |
| 非空约束 | NOT NULL |
禁止 NULL 值 | name VARCHAR(50) NOT NULL |
🚫 强制必填项 |
| 检查约束 | CHECK |
强制自定义条件 | age INT CHECK (age >= 18) |
✅ 数据验证规则 |
| 默认值约束 | DEFAULT |
未指定值时自动填充默认值 | created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP |
⏱️ 自动填充值 |
| 自动增长约束 | AUTO_INCREMENT |
自动生成连续唯一值（MySQL） | id INT AUTO_INCREMENT PRIMARY KEY |
🔢 自增序列 |
| 枚举约束 | ENUM |
限制列值为预定义选项 | status ENUM('active','inactive') |
📋 固定选项列表 |
1. 主键约束 (PRIMARY KEY)
作用：唯一标识表的每行记录
特性：
- 值必须唯一且非 NULL
- 每表只能有一个主键（可多列组合）
案例：
CREATE TABLE students (
student_id INT PRIMARY KEY, -- 单列主键
name VARCHAR(50) NOT NULL
);
-- 多列组合主键
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id) -- 复合主键
);
最低0.47元/天 解锁文章
扫一扫
1004
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
