SQL

总结

介绍了 SQL 的概念、作用以及 SQL 的四大语言分类，并对常见的 SQL 命令进行举例说明。

详情

概念

SQL（Structured Query Language，结构化查询语言）是用于管理和操作关系型数据库的国际标准语言。它使得用户高效地与数据库进行交互，实现数据的存储、检索、管理和更新。

SQL 的核心作用

数据库和表结构管理：创建新的数据库、数据表以及定义表中的字段和数据类型。
数据操作：在数据库表中进行数据的增加（INSERT）、删除（DELETE）、修改（UPDATE）和查询（SELECT）。
简化复杂操作：创建和管理视图（Views）、函数（Functions）和存储过程（Stored Procedures），以简化复杂操作和提高代码复用性。
数据分析：对数据库中的数据执行聚合、排序、分组等操作，进行初步的数据分析。
大数据技术栈集成：与 Hive、Spark SQL 等大数据查询引擎结合使用，处理和分析海量数据集。
机器学习：配合 SQLFlow 等工具，直接在数据库中构建和部署机器学习模型。

SQL 的四大语言分类

SQL 的命令根据其操作性质被划分为四个主要的子语言，表格对比如下：

类别	全称 (英文)	中文名称	主要作用	核心命令
DCL	Data Control Language	数据控制语言	管理数据库的访问权限和安全策略，控制事务。	`GRANT`, `REVOKE`, `COMMIT`, `ROLLBACK`
DDL	Data Definition Language	数据定义语言	定义和管理数据库的骨架，如库、表、索引等对象。	`CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `RENAME`
DML	Data Manipulation Language	数据操作语言	对表中的具体数据行进行增加、修改或删除。	`INSERT`, `UPDATE`, `DELETE`
DQL	Data Query Language	数据查询语言	从数据库中检索和读取数据，是使用最频繁的部分。	`SELECT`

SQL 命令举例说明

举例说明各类 SQL 命令的用法。

DCL (数据控制语言) — 管理权限与事务

GRANT：授予用户访问权限。
- 示例：授予用户 data_analyst 对 students 表的只读（查询）权限。
```
GRANT SELECT ON students TO 'data_analyst'@'localhost';
```
REVOKE：撤销已授予的权限。
- 示例：撤销用户 data_analyst 对 students 表的查询权限。
```
REVOKE SELECT ON students FROM 'data_analyst'@'localhost';
```

COMMIT：提交事务，将一系列操作（如多次 INSERT 或 UPDATE）永久保存到数据库。

示例：

-- (开始一个事务)
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- 提交事务

ROLLBACK：回滚事务，撤销事务中所有未提交的更改，通常用于发生错误时。

示例：

-- (开始一个事务)
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
-- 假设此时发生错误
ROLLBACK; -- 撤销转账操作，恢复到事务开始前的状态

DDL (数据定义语言) — 构建和管理数据结构

CREATE：用于创建新的数据库对象。

示例：创建一个名为 students 的表，包含学号、姓名和年龄字段。

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

示例： students 表中创建一个联合索引

	CREATE INDEX province_city_area ON students (province, city, area);

ALTER：用于修改已存在对象的结构。

示例：在 students 表中增加一个 email 字段。

ALTER TABLE students ADD COLUMN email VARCHAR(255);

其他示例

-- 修改表名 :
ALTER TABLE 旧表名 RENAME AS 新表名
-- 添加字段 :
ALTER TABLE 表名 ADD 字段名 列属性[属性]
-- 修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
-- 删除字段 :
ALTER TABLE 表名 DROP 字段名
 
-- 修改字段长度示例
ALTER TABLE students MODIFY name VARCHAR(1000);

-- 添加外键：将 `students` 表中的 `grade_id` 列与 `grade` 表中的 `id` 列关联起来
ALTER TABLE students ADD CONSTRAINT `FK_grade` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`id`);
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_grade;
-- 添加索引
ALTER TABLE stu ADD INDEX code_name (code,name);
-- 删除索引
ALTER TABLE stu DROP INDEX code_name;

DROP：用于彻底删除一个数据库对象。
- 示例：删除整个 students 表。
```
DROP TABLE students;
```
TRUNCATE：用于快速清空表内所有数据，但保留表结构本身。
- 示例：清空 students 表的所有记录。
```
TRUNCATE TABLE students;
```

DML (数据操作语言) — 操作具体数据

DML 负责填充、更新和移除表中的数据行。

INSERT：向表中插入一条或多条新记录。
- 示例：向 students 表中添加一位新学生的信息。
```
INSERT INTO students (id, name, age, email)
VALUES (101, '王五', 22, 'wangwu@example.com');
```
UPDATE：修改表中的现有记录。
- 示例：将学号为 101 的学生的年龄更新为 23。
```
UPDATE students SET age = 23 WHERE id = 101;
```
DELETE：从表中删除一条或多条记录。
- 示例：删除学号为 101 的学生记录。
```
DELETE FROM students WHERE id = 101;
```

TRUNCATE-DELETE- 区别

特性	`TRUNCATE TABLE` (DDL)	`DELETE FROM table` (DML)
事务与回滚	隐式提交，通常不能回滚。注：PostgreSQL 是个例外，它允许在事务中回滚 `TRUNCATE`）。	可以回滚。
执行效率	非常快。它通过释放整个数据页来清空表，不记录单行日志，资源消耗极少，尤其在处理大表时优势明显。	相对较慢。它会逐行扫描并删除，每删除一行都会在事务日志中记录，当删除大量数据时，会产生大量日志并消耗更多时间和 I/O 资源。
触发器 (Triggers)	不会触发 `ON DELETE` 触发器。因为它不针对任何单一行进行操作。	会触发 `ON DELETE` 触发器。每删除一行，都会触发为该表定义的删除触发器。
自增列 (Identity)	重置自增列。	不重置自增列。
锁定机制	通常需要对表加表级锁 (Table Lock)，会暂时阻止其他会话对该表进行任何操作。	通常使用行级锁 (Row Lock)，只锁定被删除的行，并发性能更好。
空间回收	立即释放磁盘空间（高水位线 HWM 会重置）。	不立即释放磁盘空间。删除操作只是将空间标记为可用，高水位线不变，需要后续的数据库整理操作才能回收空间。

DQL (数据查询语言) — 检索信息

DQL 是 SQL 的核心，几乎所有数据分析工作都离不开它。

SELECT：从数据库表中检索数据。

示例：从 students 表中查询所有年龄大于 20 岁的学生的姓名和邮箱。
```
SELECT name, email
FROM students
WHERE age > 20
ORDER BY name;
```

常用操作符：

- AND 
- OR 
- NOT
- IS NULL
- IS NOT NULL
- BETWEEN ~ AND 
- LIKE "%A_"
- IN
- INNER JOIN|LEFT JOIN|RIGHT JOIN|OUTER JOIN

Huan's Garden

最近文章

SQL

总结

详情

概念