SQL语句面试问答（四）

一、单表查询

1、基础查询

1）查询所有列

SELECT * FROM TableName;

2）查询特定列

SELECT Column1, Column2 FROM TableName;

3） 列别名

SELECT Column1 AS name1, Column2 AS name2 FROM TableName;

4） 去重查询

SELECT DISTINCT Column1 FROM TableName;

5）限制返回行数

SELECT  FROM TableName LIMIT 10;

6）分页查询

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

7） 排序查询

SELECT * FROM TableName ORDER BY Column1 DESC;

8） 多列排序

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

2、数据过滤

1） 基础过滤

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

2） 多条件过滤

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

3）范围查询

SELECT * FROM TableName WHERE Column1 BETWEEN value1 AND value2；

4） IN操作符

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

5）模糊查询

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值判断

SELECT * FROM TableName WHERE Column1 IS NULL;           

7）排除特定值

SELECT * FROM TableName WHERE Column1 != value;

3、聚合函数

1） 计算总数

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

2）分组求和

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

3）分组平均值

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

4） 分组最大值

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

5） 分组最小值

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

6） 分组筛选(HAVING)

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

7） 多列分组

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

4、高级窗口函数

1）ROW_NUMBER 生成唯一序号

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

2） RANK 与 DENSE_RANK 排名

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

3） 累计百分比计算

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

4）移动平均（最近三个窗口）

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

5）分组内前N名

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） 内连接

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

2） 左连接

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

3） 右连接

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

4） 全外连接

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

5） 自连接

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

6） 交叉连接

SELECT * FROM Colors CROSS JOIN Sizes;

2、子查询

1）标量子查询

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

2） IN子查询

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

3）EXISTS子查询

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

4）子查询作为派生表

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

5）多条件子查询

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

3、联合查询部分

1）去重联合(UNION（去重）)

SELECT column1 FROM TableA UNION SELECT column1 FROM TableB;

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

SELECT column1 FROM TableA UNION ALL SELECT column1 FROM TableB;

三、常用函数

1、字符串处理

1）字符串长度

SELECT LENGTH(column1) FROM TableName;

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

SELECT SUBSTRING (, start, length) FROM TableName;

3）字符串替换

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

4）字符串拼接

SELECT CONCAT(column1, column2) FROM TableName;

5）字符串转大写

SELECT UPPER(column_name) FROM TableName;

6）字符串转小写

SELECT LOWER(column_name) FROM TableName;

2、时间日期函数

1）当前时间

SELECT CURTIME();

2）当前日期

SELECT CURDATE();

3）当前日期和时间

SELECT NOW();

4）日期向后加天数

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

5）日期减天数

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

6）获取两个日期差值

SELECT DATEDIFF(date1, date2);

7）获取日期年份

SELECT YEAR(date) FROM TableName;

8）获取月份

SELECT MONTH(date) FROM TableName;

9）获取日

SELECT DAY(date) FROM TableName;

10）获取小时

SELECT HOUR(time_column) FROM TableName;

11）获取分钟

SELECT MINUTE(time_column) FROM TableName;

12）获取秒

SELECT SECOND(time_column) FROM TableName;

13）获取周数（一年中的第几周）

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

14）日期转字符串

SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM TableName;

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

15）字符串转日期

SELECT CAST(string_column AS DATE) FROM TableName;

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

四、常用操作

1、数据操作

1）插入单条数据

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

2）插入多条数据（批量插入）

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

3）更新数据（带条件）

UPDATE TableName SET Column1 = value1 WHERE Column2 = value2;

4）条件删除数据

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

5）全表删除（保留表结构）

DELETE FROM TempData;

6）清空表数据（高效重置）

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

2、表操作

1）创建新表

CREATE TABLE TableName (
    column1 INT PRIMARY KEY,
    column2 VARCHAR(50),
    column3 DATE
);

2）添加新列

ALTER TABLE TableName ADD COLUMN new_column INT;

3）修改列类型

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

4）删除列

ALTER TABLE TableName DROP COLUMN column1;

5）重命名表

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

6）删除表

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

3、约束与索引

1）添加主键约束

ALTER TABLE TableName ADD PRIMARY KEY (column1);

2）添加唯一约束

ALTER TABLE TableName ADD UNIQUE (column1);

3）添加外键约束（补充）

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

4）创建索引

CREATE INDEX idx_column ON TableName (column1);

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

5）删除索引

DROP INDEX idx_column1 ON TableName;

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

6）设置非空约束

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

7）移除非空约束

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

4、视图

1）创建视图

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）通过视图更新数据

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

3）删除视图

DROP VIEW IF EXISTS ViewName;

5、事务控制

1）开启事务

START TRANSACTION;

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

2）提交事务

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

3）回滚事务

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

4）设置保存点

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

5）回滚到保存点

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

6、权限管理

1）授予查询权限

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

2）授予所有权限

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

3）撤销权限

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

7、其他操作

1）列出所有数据库

SHOW DATABASES;

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

2）列出当前数据库所有表

SHOW TABLES;

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

3）查看表结构

DESCRIBE TableName;

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

4）查看建表语句

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

5）查询表的所有列

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database_name' 
  AND TABLE_NAME = 'table_name';

6）查看表索引信息

SHOW INDEX FROM TableName;

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

7）查询表存储大小

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）设置会话时区

SET time_zone = 'Asia/Shanghai';

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

9）创建数据库

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）删除数据库

DROP DATABASE IF EXISTS database_name;

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