SQL语句面试问答（四）

admin · 发表于 2026-4-29 16:42:49

一、单表查询1、基础查询1）查询所有列复制代码

1
SELECT * FROM TableName;

2）查询特定列复制代码

1
SELECT Column1, Column2 FROM TableName;

3）列别名复制代码

1
SELECT Column1 AS name1, Column2 AS name2 FROM TableName;

4）去重查询复制代码

1
SELECT DISTINCT Column1 FROM TableName;

5）限制返回行数复制代码

1
SELECT  FROM TableName LIMIT 10;

6）分页查询复制代码

1
2
3
SELECT  FROM TableName
LIMIT 10 -- 限制返回结果的数量为 10 条记录
OFFSET 20; -- 跳过查询结果的前 20 条记录

7）排序查询复制代码

1
SELECT * FROM TableName ORDER BY Column1 DESC;

8）多列排序复制代码

1
2
SELECT * FROM TableName ORDER BY Column1 DESC, Column2 ASC;
-- DESC 排序从大到小，ASC 排序从小到大

2、数据过滤1）基础过滤复制代码

1
2
SELECT * FROM TableName WHERE Column1 > value1;
-- >, <, >=, <=，!=，=

2）多条件过滤复制代码

1
2
SELECT * FROM TableName WHERE Column1 > value1 AND Column2 > value2;
SELECT * FROM TableName WHERE Column1 > value1 OR Column2 > value2;

3）范围查询复制代码

1
SELECT * FROM TableName WHERE Column1 BETWEEN value1 AND value2；

4） IN操作符复制代码

1
SELECT * FROM TableName WHERE Column1 IN (value1, value2，value3)；

5）模糊查询复制代码

1
2
3
SELECT * FROM TableName WHERE Column1 LIKE '%value%';-- 匹配值中间为value
SELECT * FROM TableName WHERE Column1 LIKE '%value';-- 匹配值以value结尾
SELECT * FROM TableName WHERE Column1 LIKE 'value%';-- 匹配值以value开头

6） NULL值判断复制代码

1
SELECT * FROM TableName WHERE Column1 IS NULL;

7）排除特定值复制代码

1
SELECT * FROM TableName WHERE Column1 != value;

3、聚合函数1）计算总数复制代码

1
SELECT COUNT(*) AS cnt FROM TableName WHERE column1 = value;

2）分组求和复制代码

1
SELECT column1 AS col1. SUM(column2) AS col2 FROM TableName GROUP BY column1;

3）分组平均值复制代码

1
SELECT column1 AS col1, AVG(column2) AS col2 FROM TableName GROUP BY column1;

4）分组最大值复制代码

1
SELECT column1 AS col1, MAX(column2) AS col2 FROM TableName GROUP BY column1;

5）分组最小值复制代码

1
SELECT column1 AS col1, MIN(column2) AS co12 FROM TableName GROUP BY column1;

6）分组筛选(HAVING)复制代码

1
2
SELECT column1 AS col1, SUM(column2) AS col2 FROM TableName
WHERE column3=value HAVING SUM(column2) > value;

7）多列分组复制代码

1
2
SELECT column1 AS col1, column1 AS col2, SUM(column3) AS col3 FROM TableName
GROUP BY column1, column2;

4、高级窗口函数1）ROW_NUMBER 生成唯一序号复制代码

1
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column2) AS row FROM TableName;

2） RANK 与 DENSE_RANK 排名复制代码

1
2
SELECT column1, column2, RANK() OVER (ORDER BY column2 DESC) AS rank,
DENSE_RANK() OVER (ORDER BY column2 DESC) AS dense_rank FROM TableName；

3）累计百分比计算复制代码

1
2
SELECT column1, column2, SUM(column2) OVER (ORDER BY column1) / SUM(column2)
OVER() AS cumulative_percent FROM TableName；

4）移动平均（最近三个窗口）复制代码

1
2
SELECT column1, column2,AVG(column2) OVER (ORDER BY column1 ROWS BETWEEN 2
PRECEDING AND CURRENT ROW) AS moving_avg FROM TableName;

5）分组内前N名复制代码

1
2
3
4
SELECT * FROM
(SELECT column1, column2, column3, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY
column2 DESC) AS rn
FROM TableName) WHERE In <= 3；

二、多表查询1、表连接操作1）内连接复制代码

1
2
SELECT t1.column1, t2.column2 FROM Table1 t1
JOIN Table2 t2 ON t1.column3 = t2.column3；

2）左连接复制代码

1
2
SELECT t1.column1, t2.column2 FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.column3 = t2.column3;

3）右连接复制代码

1
2
SELECT t1.column1, t2.column2 FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.column3 = t2.column3;

4）全外连接复制代码

1
2
SELECT t1.column1, t2.column2 FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.column3 = t2.column3;

5）自连接复制代码

1
2
SELECT t1.column as column1, t2.column as column2
FROM Table1 t1 JOIN Table1 t2 ON t1.column1=t2.column2;

6）交叉连接复制代码

1
SELECT * FROM Colors CROSS JOIN Sizes;

2、子查询1）标量子查询复制代码

1
2
SELECT column1, (SELECT COUNT(*) FROM TableB
WHERE column2= a.column2) AS cnt FROM TableA a;

2） IN子查询复制代码

1
2
3
SELECT column1 FROM TableA
WHERE column2 IN
(SELECT column2 FROM Categories WHERE Name= 'xxxxxxx');

3）EXISTS子查询复制代码

1
2
SELECT column1 FROM TableA a
WHERE EXISTS (SELECT 1 FROM TableB WHERE column2 = a.column2)；

4）子查询作为派生表复制代码

1
2
SELECT AVG(sum) AS avg
FROM (SELECT SUM(column2) AS sum FROM TABLEA GROUP BY column1) AS t;

5）多条件子查询复制代码

1
2
SELECT column1, column2 FROM TableA
WHERE column2 > (SELECT AVG(column2) FROM TableA);

3、联合查询部分1）去重联合(UNION（去重）)复制代码

1
SELECT column1 FROM TableA UNION SELECT column1 FROM TableB;

2）不去重联合(UNION ALL（保留重复）)复制代码

1
SELECT column1 FROM TableA UNION ALL SELECT column1 FROM TableB;

三、常用函数1、字符串处理1）字符串长度复制代码

1
SELECT LENGTH(column1) FROM TableName;

2）字符串截取（SUBSTRING(字符串, 起始位置, 截取长度)）复制代码

1
SELECT SUBSTRING (, start, length) FROM TableName;

3）字符串替换复制代码

1
SELECT REPLACE(column1, 'old_string', 'new_string') FROM TableName;

4）字符串拼接复制代码

1
SELECT CONCAT(column1, column2) FROM TableName;

5）字符串转大写复制代码

1
SELECT UPPER(column_name) FROM TableName;

6）字符串转小写复制代码

1
SELECT LOWER(column_name) FROM TableName;

2、时间日期函数1）当前时间复制代码

1
SELECT CURTIME();

2）当前日期复制代码

1
SELECT CURDATE();

3）当前日期和时间复制代码

1
SELECT NOW();

4）日期向后加天数复制代码

1
SELECT DATE_ADD(NOW(), INTERVAL 10 DAY);

5）日期减天数复制代码

1
SELECT DATE_SUB(NOW(), INTERVAL 10 DAY);

6）获取两个日期差值复制代码

1
SELECT DATEDIFF(date1, date2);

7）获取日期年份复制代码

1
SELECT YEAR(date) FROM TableName;

8）获取月份复制代码

1
SELECT MONTH(date) FROM TableName;

9）获取日复制代码

1
SELECT DAY(date) FROM TableName;

10）获取小时复制代码

1
SELECT HOUR(time_column) FROM TableName;

11）获取分钟复制代码

1
SELECT MINUTE(time_column) FROM TableName;

12）获取秒复制代码

1
SELECT SECOND(time_column) FROM TableName;

13）获取周数（一年中的第几周）复制代码

1
2
SELECT WEEK(date_column) FROM TableName;
-- 可添加模式参数：WEEK(date_column, 0)（0-周日开始，1-周一开始）

14）日期转字符串复制代码

1
2
3
4
5
6
7
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM TableName;

-- 常用格式：
-- %Y：4位年份
-- %m：月份（01-12）
-- %d：日（01-31）
-- %H:%i:%s：时分秒

15）字符串转日期复制代码

1
2
3
SELECT CAST(string_column AS DATE) FROM TableName;

SELECT STR_TO_DATE('2023-01-15', '%Y-%m-%d');

四、常用操作1、数据操作1）插入单条数据复制代码

1
INSERT INTO TableName (Column1, Column2) VALUES (value1, value2);

2）插入多条数据（批量插入）复制代码

1
INSERT INTO TableName (Column1, Column2) VALUES (value1, value2), (value3, value4);

3）更新数据（带条件）复制代码

1
UPDATE TableName SET Column1 = value1 WHERE Column2 = value2;

4）条件删除数据复制代码

1
DELETE FROM Orders WHERE OrderDate < '2020-01-01';

5）全表删除（保留表结构）复制代码

1
DELETE FROM TempData;

6）清空表数据（高效重置）复制代码

1
2
TRUNCATE TABLE Logs;
-- 特点：不可回滚，重置自增计数器，不触发DELETE触发器*

2、表操作1）创建新表复制代码

1
2
3
4
5
CREATE TABLE TableName (
column1 INT PRIMARY KEY,
column2 VARCHAR(50),
column3 DATE
);

2）添加新列复制代码

1
ALTER TABLE TableName ADD COLUMN new_column INT;

3）修改列类型复制代码

1
2
ALTER TABLE TableName MODIFY COLUMN column1 VARCHAR(20);
-- MySQL语法，其他数据库可能使用 ALTER COLUMN

4）删除列复制代码

1
ALTER TABLE TableName DROP COLUMN column1;

5）重命名表复制代码

1
2
ALTER TABLE TableName RENAME TO NewTableName;
-- SQL Server使用 sp_rename，Oracle使用 RENAME

6）删除表复制代码

1
2
DROP TABLE TableName;
--  将同时删除表结构和数据

3、约束与索引1）添加主键约束复制代码

1
ALTER TABLE TableName ADD PRIMARY KEY (column1);

2）添加唯一约束复制代码

1
ALTER TABLE TableName ADD UNIQUE (column1);

3）添加外键约束（补充）复制代码

1
2
3
4
ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES Customers(id);
-- 注：确保被引用列（column2）是主键或唯一键

4）创建索引复制代码

1
2
3
4
CREATE INDEX idx_column ON TableName (column1);

-- 扩展为复合索引
CREATE INDEX idx_multi ON TableName (column1, column2);

5）删除索引复制代码

1
2
3
4
5
DROP INDEX idx_column1 ON TableName;

-- 不同数据库语法差异：
-- PostgreSQL/Oracle: DROP INDEX idx_column1;
-- SQL Server: DROP INDEX TableName.idx_column1;

6）设置非空约束复制代码

1
ALTER TABLE TableName MODIFY COLUMN column1 VARCHAR(100) NOT NULL;

7）移除非空约束复制代码

1
ALTER TABLE TableName MODIFY COLUMN column1 VARCHAR(100) NULL;

4、视图1）创建视图复制代码

1
2
3
4
5
CREATE VIEW ViewName AS SELECT column1, column2 FROM TableName WHERE condition;

-- 添加计算列
CREATE VIEW SalesSummary AS
SELECT product_id, SUM(quantity) AS total_qty FROM Orders GROUP BY product_id;

2）通过视图更新数据复制代码

1
2
UPDATE ViewName SET column1 = 'value' WHERE condition;
-- 限制：视图必须满足可更新条件（不包含聚合、DISTINCT等）

3）删除视图复制代码

1
DROP VIEW IF EXISTS ViewName;

5、事务控制1）开启事务复制代码

1
2
3
4
5
START TRANSACTION;

-- 使用数据库特定语法：
-- SQL Server: BEGIN TRANSACTION
-- Oracle: SET TRANSACTION

2）提交事务复制代码

1
2
COMMIT;
-- 确认所有操作永久生效

3）回滚事务复制代码

1
2
ROLLBACK;
-- 撤销事务内所有未提交的操作

4）设置保存点复制代码

1
2
SAVEPOINT savepoint1;
-- 在事务中创建回滚标记点

5）回滚到保存点复制代码

1
2
ROLLBACK TO savepoint1;
-- 撤销保存点之后的操作，保留之前的操作

6、权限管理1）授予查询权限复制代码

1
2
-- 允许user1读取指定表数据
GRANT SELECT ON TableName TO user1;

2）授予所有权限复制代码

1
2
-- 权限范围包括：SELECT, INSERT, UPDATE, DELETE等
GRANT ALL PRIVILEGES ON DatabaseName.* TO 'admin'@'localhost';

3）撤销权限复制代码

1
2
-- 移除user2对指定表的删除权限
REVOKE DELETE ON TableName FROM user2;

7、其他操作1）列出所有数据库复制代码

1
2
3
4
5
6
SHOW DATABASES;

-- MySQL语法，其他数据库等效命令：
-- SQL Server: SELECT name FROM sys.databases
-- PostgreSQL: \l (psql命令行)
-- Oracle: SELECT * FROM v$database

2）列出当前数据库所有表复制代码

1
2
3
4
SHOW TABLES;

-- 查看指定数据库的表：
SHOW TABLES FROM database_name;

3）查看表结构复制代码

1
2
3
4
5
6
DESCRIBE TableName;

-- 等效命令：
-- MySQL: DESC TableName
-- SQL Server: sp_help 'TableName'
-- PostgreSQL: \d TableName

4）查看建表语句复制代码

1
2
SHOW CREATE TABLE TableName;
-- 输出结果包含完整DDL语句，可用于表重建

5）查询表的所有列复制代码

1
2
3
4
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name'
  AND TABLE_NAME = 'table_name';

6）查看表索引信息复制代码

1
2
3
4
5
6
7
SHOW INDEX FROM TableName;

-- 输出字段说明：
-- Non_unique: 是否唯一索引 (0=唯一, 1=非唯一)
-- Key_name: 索引名称
-- Seq_in_index: 索引中的列序号
-- Column_name: 索引列名

7）查询表存储大小复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
  table_name AS 'Table',
  ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'  -- 修正原图的LEMETH拼写错误
FROM information_schema.TABLES
WHERE table_schema = 'database_name';

-- (data_length + index_length)  -- 数据大小 + 索引大小（字节）
-- / 1024 / 1024                -- 转换为MB
-- ROUND(..., 2)                -- 保留2位小数

-- SQL Server查看表大小
EXEC sp_spaceused 'TableName';

-- PostgreSQL查看表大小
SELECT pg_size_pretty(pg_total_relation_size('TableName'));

8）设置会话时区复制代码

1
2
3
SET time_zone = 'Asia/Shanghai';

SET time_zone = '+8:00';  -- 东八区偏移量表示

9）创建数据库复制代码

1
2
3
4
5
6
7
CREATE DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE DATABASE inventory
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;  -- MySQL 8.0+推荐排序规则

10）删除数据库
复制代码

1
2
3
4
DROP DATABASE IF EXISTS database_name;

-- Oracle等效命令：
DROP USER schema_name CASCADE;  -- Oracle中数据库用户即schema

作者：玖拾肆
链接：https://www.nowcoder.com/discuss/773310033860796416
来源：牛客网

		自动登录	找回密码
密码			立即注册