MySQL学习笔记：SQL分类

SQL(Structured Query Language)是一种用于管理和操作关系型数据库的强大语言。它涵盖了多个方面，包括数据查询、定义和修改数据库结构、操纵数据以及事务处理。在本文中，我们将深入探讨 SQL 的分类及其各自的作用。

1. DDL

在SQL(结构化查询语言)中，DDL(数据定义语言)是一组用于定义或修改数据库结构的语句。DDL包括创建、修改和删除数据库对象，如表(tables)、索引(indexes)、视图(views)和约束(constraints)等。DDL是SQL中非常重要的部分，因为它允许用户管理数据库的结构，并确保数据的完整性和一致性。

以下是DDL中一些常见的语句及其描述。

1.1 CREATE

CREATE语句用于创建数据库对象，如数据库、表、索引、视图等：

• CREATE DATABASE：用于创建新的数据库CREATE DATABASE database_name;
• CREATE TABLE：用于创建新的表CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ... );
• CREATE INDEX：用于在表的列上创建索引，提升查询效率CREATE INDEX index_name ON table_name (column1, column2, ...);
• CREATE VIEW：用于创建视图，视图是基于一个或多个表的虚拟表CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

1.2 DROP

DROP语句用于删除数据库对象。需要注意的是，删除表会删除表中的所有数据。

• DROP DATABASE：用于删除数据库DROP DATABASE database_name;
• DROP TABLE：用于删除表DROP TABLE table_name;
• DROP INDEX：用于删除索引DROP INDEX index_name ON table_name;
• DROP VIEW：用于删除视图DROP VIEW view_name;

1.3 ALTER

ALTER语句用于修改数据库对象的结构或属性。可以用于添加、删除、修改列，以及修改表的约束等。

-- 添加列  
ALTER TABLE table_name ADD column_name datatype;  
  
-- 删除列  
ALTER TABLE table_name DROP COLUMN column_name;  
  
-- 修改列  
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;

1.4 TRUNCATE

TRUNCATE语句用于清空表中的数据，但保留表结构。

TRUNCATE TABLE table_name;

2. DML

DML(数据操作语言，Data Manipulation Language)是SQL中的一种语言类型，主要用于对数据库中的数据进行增加、修改、删除和查询操作。下面将介绍DML的基本概念、常用语句。

2.1 DML的基本概念

DML是SQL中用于操作数据的核心部分，它包括以下四种语句：

1. SELECT：查询数据，可以根据条件筛选、排序、分组等。
2. INSERT：向表中插入新数据。
3. UPDATE：修改表中的数据。
4. DELETE：删除表中的数据。

2.2 SELECT

SELECT语句是DML中最常用的语句，用于从数据库中查询数据。SELECT语句的基本语法如下：

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]];

其中，column1, column2, ...是要查询的列名；table_name是要查询的表名；WHERE子句用于筛选满足条件的记录；GROUP BY子句用于对结果集进行分组；HAVING子句用于筛选满足条件的分组；ORDER BY子句用于对结果集进行排序。

例如，从employees表中查询所有员工的姓名和年龄，并按照年龄升序排列：

SELECT name, age
FROM employees
ORDER BY age ASC;

2.3 INSERT

INSERT语句用于向表中插入新数据。INSERT语句的基本语法如下：

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

其中，table_name是要插入数据的表名；column1, column2, ...是要插入数据的列名；value1, value2, ...是要插入的数据值。

例如，向employees表中插入一条新记录：

INSERT INTO employees (name, age, department)
VALUES ('John Doe', 30, 'IT');

2.4 UPDATE

UPDATE语句用于修改表中的数据。UPDATE语句的基本语法如下：

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

其中，table_name是要修改数据的表名；column1, column2, ...是要修改的列名；value1, value2, ...是要修改为的新值；WHERE子句用于筛选要修改的记录。

例如，将employees表中年龄大于40的员工的名字修改为全大写：

UPDATE employees
SET name = UPPER(name5)
WHERE age > 40;

2.4 DELETE

DELETE语句用于删除表中的数据。DELETE语句的基本语法如下：

DELETE FROM table_name
[WHERE condition];

其中，table_name是要删除数据的表名；WHERE子句用于筛选要删除的记录。

例如，从employees表中删除年龄小于18的员工：

DELETE FROM employees
WHERE age < 18;

3. DQL

DQL(数据查询语言，Data Query Language)是SQL中专门用于查询数据的语言，它只包含一种语句：SELECT。SELECT语句用于从数据库中提取数据，可以根据指定的条件、排序和分组方式进行筛选。通过DQL，我们可以方便地从数据库中获取所需的信息，为分析和决策提供支持。

3.1 基本 SELECT 语句

SELECT column1, column2
FROM your_table
WHERE condition;

• column1, column2: 指定要检索的列。
• your_table: 指定要检索数据的表。
• WHERE condition: 可选，用于筛选检索的数据。

3.2 查询示例

• 基本查询，这将检索employees表中的所有列的所有行：SELECT * FROM employees;
• 选择特定列，这将只检索employees表中的first_name和last_name列：SELECT first_name, last_name FROM employees;
• 带有条件的查询，这将检索orders表中order_date大于 '2023-01-01' 的所有行：SELECT * FROM orders WHERE order_date > '2023-01-01';
• 聚合函数的使用，这将计算employees表中salary列的平均值： SELECT AVG(salary) FROM employees;

3.3 进阶查询

• 连接查询，这将联合 customers 和 orders 表，返回匹配条件的列：SELECT customers.customer_id, customers.customer_name, orders.order_id FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
• 子查询，这将使用子查询选择特定部门的员工：SELECT first_name, last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');
• 分组和聚合，这将按部门分组，计算每个部门的员工数和平均工资：SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;

3.4 事务的使用

DQL操作通常嵌套在事务中，以确保数据库的一致性和完整性。

BEGIN; -- 开始事务

-- DQL 操作

COMMIT; -- 提交事务
-- 或者
ROLLBACK; -- 回滚事务

4. DCL

DCL(数据控制语言，Data Control Language)是SQL中用于控制用户访问权限的语言，它包括两种语句：GRANT和REVOKE。GRANT语句用于为用户分配访问权限，而REVOKE语句用于撤销用户的访问权限。通过DCL，我们可以实现对数据库对象的细粒度访问控制，确保数据的安全性。

4.1 授权用户访问权限

授予用户访问权限是DCL中的一个主要任务。通过合理的授权，可以确保数据库只被授权用户访问。

GRANT SELECT, INSERT ON employees TO user1;

• SELECT, INSERT: 允许的操作。
• employees: 表的名称。
• user1: 授权的用户。

4.2 回收用户访问权限

如果不再需要某个用户执行特定操作的权限，可以使用REVOKE语句将权限回收。

REVOKE INSERT ON employees FROM user1;

• INSERT: 要回收的操作。
• employees: 表的名称。
• user1: 要回收权限的用户。

4.3 管理角色

DCL 还允许创建和管理角色，角色是一组权限的集合，可以赋予用户。

CREATE ROLE admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO admin;

• admin: 角色的名称。
• SELECT, INSERT, UPDATE, DELETE: 允许的操作。
• employees: 表的名称。

4.4 管理用户和角色之间的关系

可以将角色授予用户，这样用户就继承了角色的权限。

GRANT admin TO user1;

• admin: 角色的名称。
• user1: 用户的名称。

4.5 撤销用户的角色

如果用户不再需要某个角色的权限，可以使用 REVOKE 语句。

REVOKE admin FROM user1;

• admin: 角色的名称。
• user1: 用户的名称。

4.6 查看用户权限

可以查询用户的权限信息，以确保正确配置了数据库访问权限。

SHOW GRANTS FOR user1;

• user1: 要查询权限的用户。

5. TCL

TCL(事务控制语言，Transaction Control Language)是SQL中的一组语句，它包括三种语句：COMMIT、ROLLBACK和SAVEPOINT，用于管理事务的提交和回滚。通过使用TCL，可以确保数据库中的操作是原子性、一致性、隔离性和持久性(ACID 特性)。

5.1 开始事务

事务是数据库中一系列操作的逻辑单元，要么全部执行，要么全部回滚。通过开始事务，可以确保一系列操作被视为一个整体。

BEGIN;

这将标志着事务的开始，之后的一系列操作将被视为一个事务单元。

5.2 提交事务

提交事务表示一系列操作已成功完成，并将更改永久保存到数据库。

COMMIT;

这将提交事务，使之前的所有更改成为永久性的。

5.3 回滚事务

如果在事务执行的过程中发生了错误，可以选择回滚事务，使数据库恢复到事务开始之前的状态。

ROLLBACK;

这将回滚事务，撤销之前的所有更改。

5.4 保存点

保存点是事务中的一个标记，可以在稍后的时间回滚到该点。

-- 创建保存点
SAVEPOINT my_savepoint;
-- 回滚到保存点
ROLLBACK TO my_savepoint;

声明：本作品采用署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)进行许可，使用时请注明出处。

blog: mengbin

Github: mengbin92

cnblogs: 恋水无意

腾讯云开发者社区：孟斯特