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