1. SQL简介

SQL是一种用于与关系型数据库通信的标准语言。通过SQL，我们可以执行查询、更新、删除、插入等数据操作，也可以创建和管理数据库及其表结构。

小注：本文是由我原写于Notion的Markdown笔记转化到HTML上的，内容格式可能故障请理解

2. 数据库操作

2.1 创建数据库

CREATE DATABASE database_name;

示例：

CREATE DATABASE my_database;

2.2 删除数据库

DROP DATABASE database_name;

示例：

DROP DATABASE my_database;

2.3 使用数据库

USE database_name;

示例：

USE my_database;

3. 表操作

3.1 创建表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

示例：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50)
);

3.2 修改表结构

添加列：

ALTER TABLE table_name ADD column_name datatype;

示例：

ALTER TABLE employees ADD salary DECIMAL(10, 2);

删除列：

ALTER TABLE table_name DROP COLUMN column_name;

示例：

ALTER TABLE employees DROP COLUMN salary;

3.3 删除表

DROP TABLE table_name;

示例：

DROP TABLE employees;

4. 数据操作

4.1 插入数据

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

示例：

INSERT INTO employees (id, name, age, department) VALUES (1, 'Alice', 30, 'HR');

4.2 更新数据

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

示例：

UPDATE employees SET age = 31 WHERE id = 1;

4.3 删除数据

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM employees WHERE id = 1;

5. 查询操作

5.1 基本查询

SELECT column1, column2, ... FROM table_name;

示例：

SELECT name, age FROM employees;

5.2 带条件查询

SELECT column1, column2, ... FROM table_name WHERE condition;

示例：

SELECT name, department FROM employees WHERE age > 25;

5.3 排序查询

SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];

示例：

SELECT name, age FROM employees ORDER BY age DESC;

5.4 聚合函数查询

• COUNT：统计行数

SELECT COUNT(column_name) FROM table_name;

示例：

SELECT COUNT(id) FROM employees;

• SUM：计算总和

SELECT SUM(column_name) FROM table_name;

示例：

SELECT SUM(salary) FROM employees;

• AVG：计算平均值

SELECT AVG(column_name) FROM table_name;

示例：

SELECT AVG(age) FROM employees;

• MAX 和 MIN：计算最大、最小值

SELECT MAX(column_name), MIN(column_name) FROM table_name;

示例：

SELECT MAX(age), MIN(age) FROM employees;

5.5 分组查询

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

示例：

SELECT department, COUNT(*) FROM employees GROUP BY department;

5.6 联表查询（JOIN）

内连接（INNER JOIN）：

SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department = departments.id;

左连接（LEFT JOIN）：

SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

示例：

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department = departments.id;

6. 索引与视图操作

6.1 创建索引

CREATE INDEX index_name ON table_name (column_name);

示例：

CREATE INDEX idx_age ON employees (age);

6.2 删除索引

DROP INDEX index_name ON table_name;

示例：

DROP INDEX idx_age ON employees;

6.3 创建视图

CREATE VIEW view_name AS
SELECT column1, column2, ... FROM table_name WHERE condition;

示例：

CREATE VIEW employee_view AS
SELECT name, department FROM employees WHERE age > 25;

6.4 删除视图

DROP VIEW view_name;

示例：

DROP VIEW employee_view;

7. 权限管理

7.1 授予权限

GRANT privilege ON object TO user;

示例：

GRANT SELECT ON employees TO 'john';

7.2 撤销权限

REVOKE privilege ON object FROM user;

示例：

REVOKE SELECT ON employees FROM 'john';

8. 事务控制

事务用于管理多个SQL操作的执行，以确保数据库的完整性。

8.1 开始事务

START TRANSACTION;

8.2 提交事务

COMMIT;

8.3 回滚事务

ROLLBACK;

示例：

START TRANSACTION;

UPDATE employees SET age = 32 WHERE id = 1;
DELETE FROM employees WHERE id = 2;

COMMIT;