sql中的ddl,dql,dml,dcl

朝阳(菜鸡变强中)

于 2024-09-12 14:47:36 发布

阅读量606

点赞数 17

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_62303337/article/details/142175821

版权

mysql中sql

DDL

在 SQL 中，DDL（Data Definition Language，数据定义语言）用于定义和管理数据库结构，例如表、视图、索引等。DDL 主要负责创建、修改和删除数据库对象。

常见的 DDL 语句包括：

1. CREATE —— 创建数据库对象

用于创建数据库、表、视图、索引等。
示例：创建数据库
```
CREATE DATABASE my_database;
```

示例：创建表

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

示例：创建索引

CREATE INDEX idx_username ON users(username);

2. ALTER —— 修改数据库对象

用于修改已有的表或其他对象的结构。
示例：为表添加新列
```
ALTER TABLE users ADD age INT;
```

示例：修改列的数据类型

ALTER TABLE users MODIFY email VARCHAR(150);

示例：删除表中的某一列
```
ALTER TABLE users DROP COLUMN age;
```

3. DROP —— 删除数据库对象

用于删除表、数据库、视图或索引。
示例：删除表
```
DROP TABLE users;
```
示例：删除数据库
```
DROP DATABASE my_database;
```

4. TRUNCATE —— 清空表数据

用于删除表中的所有数据，但保留表结构。
示例：清空表
```
TRUNCATE TABLE users;
```

5. RENAME —— 重命名数据库对象

用于更改数据库、表或其他对象的名称。
示例：重命名表
```
RENAME TABLE users TO customers;
```

DDL 的特点：

自动提交：DDL 语句会在执行后自动提交更改，因此无法回滚。
影响数据库结构：DDL 主要用于定义或更改数据库的结构，而不是操作表中的具体数据。

这些 DDL 语句在 SQL 中用于设置和管理数据库的架构和结构。

DQL

DQL（Data Query Language，数据查询语言）主要用于查询数据库中的数据。其核心语句是 SELECT，用于从一个或多个表中检索数据。DQL 既可以用于简单查询，也可以进行复杂的数据分析操作。

DQL 的核心语句：`SELECT`

1. 基本语法：

SELECT column1, column2, ... FROM table_name;

column1, column2, ...：表示要查询的列。
table_name：表示查询的表。

2. 查询所有列：

使用星号 * 可以查询表中的所有列：

SELECT * FROM users;

3. 别名（AS）：

可以为列或表设置别名，以便于理解或格式化输出。

列别名：

SELECT username AS 'User Name', email AS 'Email Address' FROM users;

表别名：

SELECT u.username, u.email FROM users AS u;

4. WHERE 子句 —— 条件查询

用于根据条件过滤数据，只返回符合条件的记录。

语法：

SELECT column1, column2 FROM table_name WHERE condition;

示例：
```
SELECT username, email FROM users WHERE age > 18;
```
- age > 18 是条件，表示查询年龄大于 18 的用户。

5. 运算符 —— 用于条件查询

比较运算符：
- =：等于
- != 或 <>：不等于
- >：大于
- <：小于
- >=：大于等于
- <=：小于等于

逻辑运算符：

AND：用于组合多个条件，所有条件都满足时返回结果。
```
SELECT * FROM users WHERE age > 18 AND gender = 'M';
```

OR：用于组合多个条件，任一条件满足时返回结果。

SELECT * FROM users WHERE age > 18 OR city = 'New York';

NOT：用于否定一个条件。

SELECT * FROM users WHERE NOT city = 'New York';

6. ORDER BY 子句 —— 排序查询结果

用于对查询结果进行排序，默认按升序排列。可以通过 ASC 或 DESC 明确指定升序或降序。

语法：

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

示例：按用户名升序排列：

SELECT username, email FROM users ORDER BY username ASC;

示例：按年龄降序排列：

SELECT username, age FROM users ORDER BY age DESC;

7. LIMIT 子句 —— 限制返回的记录数

用于限制查询返回的记录数，常用于分页。

语法：

SELECT column1, column2 FROM table_name LIMIT number_of_records;

示例：

SELECT username, email FROM users LIMIT 5;

仅返回前 5 条记录。

8. LIKE 操作符 —— 模糊查询

用于根据部分匹配查询数据，通常结合通配符使用：

%：表示任意数量的字符。
_：表示一个单个字符。

示例：查找以 ‘A’ 开头的用户名：

SELECT username FROM users WHERE username LIKE 'A%';

示例：查找包含 ‘a’ 的用户名：

SELECT username FROM users WHERE username LIKE '%a%';

9. IN 操作符 —— 指定范围内的查询

IN 用于匹配多个值中的任意一个。

语法：

SELECT column1, column2 FROM table_name WHERE column1 IN (value1, value2, ...);

示例：

SELECT * FROM users WHERE city IN ('New York', 'Los Angeles', 'Chicago');

10. BETWEEN 操作符 —— 范围查询

BETWEEN 用于查询在某个范围内的值，包括范围的边界。

语法：

SELECT column1, column2 FROM table_name WHERE column1 BETWEEN value1 AND value2;

示例：查询年龄在 18 到 30 之间的用户：

SELECT username, age FROM users WHERE age BETWEEN 18 AND 30;

11. DISTINCT 关键字 —— 去重查询

用于去除查询结果中的重复记录。

语法：

SELECT DISTINCT column1 FROM table_name;

示例：查询所有不同的城市：
```
SELECT DISTINCT city FROM users;
```

12. GROUP BY 子句 —— 分组查询

用于将具有相同值的记录分组，通常与聚合函数（如 COUNT, SUM, AVG, MAX, MIN）一起使用。

语法：

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

示例：按城市分组统计用户数量：

SELECT city, COUNT(*) FROM users GROUP BY city;

13. HAVING 子句 —— 分组过滤

HAVING 用于过滤分组后的数据，常与 GROUP BY 搭配使用。它与 WHERE 类似，但 WHERE 只能在分组前过滤数据，而 HAVING 用于分组后的结果。

语法：

SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > value;

示例：查询用户数超过 5 的城市：

SELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;

14. 聚合函数 —— 数据聚合处理

COUNT()：计算列中不为空的记录数量。
```
SELECT COUNT(*) FROM users;
```
SUM()：计算数值列的总和。
```
SELECT SUM(salary) FROM employees;
```
AVG()：计算数值列的平均值。
```
SELECT AVG(age) FROM users;
```
MAX()：返回列中的最大值。
```
SELECT MAX(salary) FROM employees;
```
MIN()：返回列中的最小值。
```
SELECT MIN(age) FROM users;
```

15. 连接（JOIN） —— 多表查询

用于从多个表中查询数据，JOIN 是非常重要的查询操作，用于关联表之间的关系。

INNER JOIN：返回两个表中匹配的记录。

SELECT users.username, orders.order_id 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN：返回左表中的所有记录，即使右表中没有匹配的记录。

SELECT users.username, orders.order_id 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN：返回右表中的所有记录，即使左表中没有匹配的记录。

SELECT users.username, orders.order_id 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;

在求职面试中，关于 SQL 查询（DQL）的执行顺序和写法是面试官常常问的问题。以下是一些重点和常见问题的补充，以及它们的执行顺序和面试中可能被问到的问题。

SQL 查询的执行顺序（实际执行的顺序）

在 SQL 查询中，尽管我们书写的顺序是从 SELECT 开始，但 SQL 引擎的实际执行顺序是不同的。面试中，面试官可能会问：

SQL 执行顺序：

FROM：首先从哪个表或视图中读取数据。
JOIN：如果有多表查询，会先进行表之间的连接操作。
WHERE：接着应用 WHERE 子句，筛选满足条件的记录。
GROUP BY：然后对数据进行分组。
HAVING：过滤分组后的数据（如果存在）。
SELECT：选择要返回的列。
DISTINCT：去除重复行（如果有）。
ORDER BY：对结果集排序。
LIMIT：最后根据 LIMIT 限制返回的行数。

示例：

SELECT city, COUNT(*) 
FROM users 
WHERE age > 18 
GROUP BY city 
HAVING COUNT(*) > 5 
ORDER BY city 
LIMIT 10;

执行顺序为：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。

面试常问问题(应该掌握的)：

SQL 查询的执行顺序是怎样的？
- 回答时注意执行顺序与书写顺序的区别。
GROUP BY 与 HAVING 的区别？
- GROUP BY 用于分组，HAVING 用于对分组结果进行过滤，通常与聚合函数一起使用。注意 HAVING 是在分组之后应用的，而 WHERE 是在分组之前。
WHERE 和 HAVING 的区别？
- WHERE 用于筛选原始数据，HAVING 用于筛选分组后的数据。
DISTINCT 和 GROUP BY 的区别？
- DISTINCT 用于去重，GROUP BY 是对数据进行分组。面试官可能会问你在什么情况下更适合使用 DISTINCT 而不是 GROUP BY。
内连接（INNER JOIN）与外连接（LEFT JOIN、RIGHT JOIN）的区别？
- INNER JOIN 返回的是两个表中匹配的数据；LEFT JOIN 返回左表的所有数据以及右表的匹配数据；RIGHT JOIN 返回右表的所有数据以及左表的匹配数据。
UNION 和 UNION ALL 的区别？
- UNION 会去除结果中的重复行，而 UNION ALL 会返回所有行，包括重复行。
SQL 优化常用的技术？
- 索引的使用。
- 避免 SELECT *，明确选择需要的列。
- 使用 JOIN 时注意数据量大的表连接顺序，尽量减少连接后的数据量。
- 使用 EXPLAIN 查看查询计划，分析查询效率。
EXISTS 和 IN 的区别？
- IN 适用于小范围值的查询，EXISTS 适合大数据量的子查询，EXISTS 通常性能更好，尤其在子查询返回大量记录时。
如何进行分页查询？
- 使用 LIMIT 和 OFFSET 关键字来分页：
```
SELECT * FROM users LIMIT 10 OFFSET 20;
```
- 或者使用 ROW_NUMBER() 和子查询进行复杂的分页需求。
聚合函数与分组的应用：
- 聚合函数如 COUNT()、SUM()、AVG()、MAX()、MIN()，通常与 GROUP BY 一起使用进行统计分析。面试中，面试官可能会问如何统计每个城市的用户数量，以及过滤某些条件。

其他可能的面试问题：

如何查询第 N 高的工资？

可以使用子查询：

SELECT salary FROM employees e1 
WHERE N-1 = (SELECT COUNT(DISTINCT salary) 
             FROM employees e2 
             WHERE e2.salary > e1.salary);

优化复杂查询的思路？
- 考虑索引、避免全表扫描、拆分大查询、减少冗余操作、合理使用子查询或连接。

DML

DML（Data Manipulation Language，数据操作语言）主要用于对数据库表中的数据进行插入、更新、删除等操作。常见的 DML 语句包括 INSERT、UPDATE 和 DELETE。DML 语句是最常见的操作之一，因为它直接影响数据库中的数据。

1. `INSERT` 语句 —— 插入数据

INSERT 用于向数据库表中插入一条或多条记录。

基本语法：

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

table_name：表示要插入数据的表。
(column1, column2, ...)：指定要插入值的列。
(value1, value2, ...)：每列对应的值。

示例：

INSERT INTO employees (name, age, position) 
VALUES ('John Doe', 30, 'Developer');

这条语句向 employees 表中插入一条新记录，其中 name 为 John Doe，age 为 30，position 为 Developer。

插入多条记录：

INSERT INTO employees (name, age, position) 
VALUES ('Alice', 25, 'Designer'), 
       ('Bob', 28, 'Manager');

该语句一次性向 employees 表中插入了两条记录。

省略列名：

如果插入的值与表的列顺序相匹配，可以省略列名，直接插入：

INSERT INTO employees 
VALUES ('Charlie', 35, 'Engineer');

2. `UPDATE` 语句 —— 更新数据

UPDATE 用于修改表中现有的记录。

基本语法：

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

table_name：表示要更新数据的表。
SET：指定要修改的列及其新值。
WHERE：用于指定哪些行要更新。没有 WHERE 条件时，所有行都会被更新（应避免未加 WHERE 的 UPDATE 操作）。

示例：

UPDATE employees 
SET age = 31, position = 'Senior Developer' 
WHERE name = 'John Doe';

这条语句将 employees 表中 John Doe 的年龄更新为 31，职位更新为 Senior Developer。

更新多条记录：

UPDATE employees 
SET position = 'Team Lead' 
WHERE age > 30;

这条语句将 age 大于 30 的所有员工的职位更新为 Team Lead。

注意：如果没有 `WHERE` 条件，所有记录都会被更新：

UPDATE employees 
SET age = 29;

这条语句会将所有员工的年龄更新为 29，因此 UPDATE 语句使用时需谨慎，尤其是 WHERE 子句。

3. `DELETE` 语句 —— 删除数据

DELETE 用于删除表中的记录。

基本语法：

DELETE FROM table_name WHERE condition;

table_name：表示要删除数据的表。
WHERE：指定删除哪些记录。没有 WHERE 条件时，所有记录都会被删除。

示例：

DELETE FROM employees 
WHERE name = 'John Doe';

这条语句会删除 employees 表中 name 为 John Doe 的记录。

删除多条记录：

DELETE FROM employees 
WHERE age < 25;

该语句删除所有年龄小于 25 岁的员工记录。

删除所有记录（不推荐）：

DELETE FROM employees;

这条语句会删除 employees 表中的所有记录，但表结构仍然保留。如果要清空表，DELETE 也可以加上 TRUNCATE 来提高效率。

4. `TRUNCATE` 语句 —— 清空表

虽然不完全属于 DML（更接近 DDL），但 TRUNCATE 常用于快速清空表的所有数据，保留表结构。

基本语法：

TRUNCATE TABLE table_name;

DELETE、TRUNCATE 和 DROP 都用于从数据库中删除数据或表结构，但它们的功能、作用范围以及行为方式存在明显区别。以下是对这三者的详细对比：

1. `DELETE` 语句

功能：删除表中的数据，但保留表结构。

语法：

DELETE FROM table_name WHERE condition;

使用场景：
- 删除部分或全部数据行，但表和其结构仍然存在。
- 可以使用 WHERE 子句来指定删除某些行。
- 支持事务，因此可以回滚。适用于需要保留操作日志且可能需要回滚的情况。
特点：
- 逐行删除：DELETE 是逐行删除的操作，会根据条件逐条删除匹配的数据。
- 事务支持：支持事务处理，可以在删除数据时加锁，且可以回滚操作。
- 触发器：会触发与 DELETE 操作相关的触发器。
示例：
```
DELETE FROM employees WHERE age > 40;
```
这条语句会删除 employees 表中年龄大于 40 的所有记录。

2. `TRUNCATE` 语句

功能：快速清空表中的数据，但保留表结构。
语法：
```
TRUNCATE TABLE table_name;
```
使用场景：
- 当需要快速删除表中的所有数据，但保留表结构和索引时使用。
- 不记录逐行删除日志，因此比 DELETE 快得多。
- 适用于不需要保留删除日志且不需要回滚的情况。
特点：
- 删除所有数据：TRUNCATE 会删除表中的所有数据，但不会删除表结构。
- 不支持事务：不记录单条删除操作，无法回滚。
- 效率高：TRUNCATE 通常比 DELETE 更高效，因为它不逐行操作，直接释放页面空间。
- 重置自动递增：TRUNCATE 操作会将表中的自动递增列重置（通常是 AUTO_INCREMENT 字段）。
示例：
```
TRUNCATE TABLE employees;
```
这条语句会清空 employees 表的所有数据，但保留表结构。

3. `DROP` 语句

功能：彻底删除表或数据库，包括数据和表结构。

语法：

DROP TABLE table_name;
DROP DATABASE database_name;

使用场景：
- 完全删除表或数据库及其所有内容，包括数据、表结构、索引、约束等。
- 使用场景是当表或数据库已经不再需要时。
特点：
- 删除表和数据：DROP 会删除表及其所有数据、索引、触发器、约束等，不可恢复。
- 不支持事务：一旦执行 DROP 操作，无法回滚。
- 无法保留结构：删除后表结构、索引和触发器等都会消失。
示例：
```
DROP TABLE employees;
DROP DATABASE my_database;
```
这条语句会彻底删除 employees 表以及它包含的数据，或者删除 my_database 数据库。

总结：`DELETE`、`TRUNCATE`、`DROP` 的区别

特性	`DELETE`	`TRUNCATE`	`DROP`
操作对象	表中的部分或全部数据行	表中的所有数据行	整个表或数据库
是否保留表结构	保留表结构	保留表结构	删除表结构
是否支持事务	支持，操作可回滚	不支持，无法回滚	不支持，无法回滚
执行速度	慢，逐行删除	快，直接清空表数据	快，删除整个表或数据库
是否触发触发器	会触发与 `DELETE` 相关的触发器	不会触发触发器	不会触发触发器
自动递增重置	不会重置自动递增列	会重置自动递增列	不适用（表已删除）
使用场景	需要删除部分数据或需要保留日志时	快速清空数据，但保留表结构时	完全删除不再使用的表或数据库时

需要知道的问题：

DELETE 和 TRUNCATE 的区别是什么？什么时候用哪个？
- 区别在于 DELETE 逐行删除数据并支持事务，而 TRUNCATE 直接清空表，速度更快但不支持事务。一般当需要保留操作日志或条件删除时使用 DELETE，而 TRUNCATE 适用于快速清空整个表。
DROP 和 TRUNCATE 的区别？
- DROP 删除表结构及其所有数据，而 TRUNCATE 仅删除数据但保留表结构

DCL

DCL（Data Control Language，数据控制语言）主要用于控制数据库的访问权限和安全性。DCL 涉及两个主要的 SQL 语句：GRANT 和 REVOKE，用于授予和撤销用户或角色对数据库对象的权限。

DCL 语句

1. `GRANT` 语句

功能：授予用户或角色对数据库对象（如表、视图、数据库等）的特定权限。
语法：
```
GRANT privilege_list ON object TO user [WITH GRANT OPTION];
```
- privilege_list：要授予的权限列表，如 SELECT、INSERT、UPDATE、DELETE 等。
- object：数据库中的对象，如表、视图等。
- user：接收权限的用户或角色。
- WITH GRANT OPTION：允许该用户将该权限授予其他用户。
示例：
```
GRANT SELECT, INSERT ON employees TO 'john';
```
这条语句授予用户 john 对 employees 表的 SELECT 和 INSERT 权限。

2. `REVOKE` 语句

功能：撤销用户或角色对数据库对象的特定权限。
语法：
```
REVOKE privilege_list ON object FROM user;
```
- privilege_list：要撤销的权限列表，如 SELECT、INSERT 等。
- object：数据库中的对象。
- user：权限被撤销的用户或角色。
示例：
```
REVOKE SELECT ON employees FROM 'john';
```
这条语句撤销用户 john 对 employees 表的 SELECT 权限。

常见 DCL 权限类型

SELECT：查询表中的数据。
INSERT：向表中插入数据。
UPDATE：更新表中的数据。
DELETE：删除表中的数据。
EXECUTE：执行存储过程或函数。
USAGE：允许访问特定的数据库对象（如序列、数据库等）。
ALL PRIVILEGES：授予用户对特定数据库对象的所有权限。

常见 DCL 面试问题

1. `GRANT` 和 `REVOKE` 的作用是什么？

回答：GRANT 用于授予用户或角色对数据库对象的权限，而 REVOKE 用于撤销用户或角色的权限。

2. 如何授予用户在表上执行特定操作的权限？

回答：
```
GRANT SELECT, UPDATE ON employees TO 'john';
```
这条语句授予用户 john 对 employees 表的 SELECT 和 UPDATE 权限。

3. 如何撤销用户对特定表的权限？

回答：
```
REVOKE INSERT ON employees FROM 'john';
```
这条语句撤销用户 john 对 employees 表的 INSERT 权限。

4. `WITH GRANT OPTION` 是什么？

回答：WITH GRANT OPTION 允许用户将其获得的权限授予其他用户。例如，如果一个用户被授予了 SELECT 权限并使用了 WITH GRANT OPTION，那么他可以将 SELECT 权限授予其他用户。

示例：

GRANT SELECT ON employees TO 'john' WITH GRANT OPTION;

5. 如何查看某个用户的权限？

回答：可以使用 SHOW GRANTS FOR 语句来查看某个用户的权限。
```
SHOW GRANTS FOR 'john';
```

DCL 的应用场景

权限管理：DCL 通常用于管理数据库中用户的访问权限。管理员可以根据用户的角色和职责，授予或撤销他们对数据库对象的权限。
数据安全：通过控制用户的权限，可以保证数据库的安全，防止未经授权的访问、修改或删除操作。

朝阳(菜鸡变强中)

关注

17
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
sql中的ddl,dql,dml,dcl

在 SQL 中，DDL（Data Definition Language，数据定义语言）用于定义和管理数据库结构，例如表、视图、索引等。DDL 主要负责创建、修改和删除数据库对象。
复制链接

扫一扫