一、基础查询

1. 查询所有列

SELECT * FROM TableName;

-- 查询表 TableName 中的所有列和所有行

2. 查询特定列

SELECT Column1, Column2 FROM TableName;

-- 只查询表 TableName 中的 Column1 和 Column2 列

3. 列别名

SELECT Column1 AS name1, Column2 AS name2 FROM TableName;

-- 为查询结果中的列指定别名，方便后续引用

4. 去重查询

SELECT DISTINCT Column1 FROM TableName;

-- 查询 Column1 列的唯一值，去除重复值

5. 限制返回行数

SELECT * FROM TableName LIMIT 10;

-- 限制查询结果只返回前 10 行

6. 分页查询

SELECT * FROM TableName LIMIT 10 OFFSET 20;

-- 查询第 21 到第 30 行的数据，用于分页显示

7. 排序查询

SELECT * FROM TableName ORDER BY Column1 DESC;

-- 按 Column1 列降序排列查询结果

8. 多列排序

SELECT * FROM TableName ORDER BY Column1 DESC, Column2 ASC;

-- 先按 Column1 列降序排列，若 Column1 相同则按 Column2 列升序排列

二、数据过滤

1. 基础过滤

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

-- 根据条件过滤数据，只返回满足条件的行

2. 多条件过滤

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

-- 使用 AND 运算符组合多个条件，所有条件都满足时返回行

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

-- 使用 OR 运算符组合多个条件，任一条件满足时返回行

3. 范围查询

SELECT * FROM TableName WHERE Column1 BETWEEN value1 AND value2;

-- 查询 Column1 列在指定范围内的数据

4. IN 操作符

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

-- 查询 Column1 列值在指定集合中的数据

5. 模糊查询

SELECT * FROM TableName WHERE Column1 LIKE '%value%';

-- 包含任意位置的指定字符串

SELECT * FROM TableName WHERE Column1 LIKE '%value';

-- 以指定字符串结尾

SELECT * FROM TableName WHERE Column1 LIKE 'value%';

-- 以指定字符串开头

6. NULL 值判断

SELECT * FROM TableName WHERE Column1 IS NULL;

-- 查询 Column1 列为 NULL 的数据

7. 排除特定值

SELECT * FROM TableName WHERE Column1 != value;

-- 查询 Column1 列不等于指定值的数据

三、聚合函数

1. 计算总数

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

-- 统计满足条件的行数

2. 分组求和

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

-- 按 column1 列分组，计算每组 column2 列的总和

3. 分组平均值

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

-- 按 column1 列分组，计算每组 column2 列的平均值

4. 分组最大值

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

-- 按 column1 列分组，计算每组 column2 列的最大值

5. 分组最小值

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

-- 按 column1 列分组，计算每组 column2 列的最小值

6. 分组筛选（HAVING）

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

-- 先按 column1 列分组，再筛选满足条件的分组

7. 多列分组

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

-- 按多列分组并计算聚合值

四、高级窗口函数

1. ROW_NUMBER 生成唯一序号

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

-- 为每一行生成一个唯一的序号，按 column2 列排序

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;

-- 计算排名，RANK 会跳过重复值，DENSE_RANK 不会

3. 累计百分比计算

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

-- 计算 column2 列的累计百分比

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

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

-- 计算 column2 列的平移平均值，窗口大小为当前行及前两行

5. 分组内前N名

SELECT * FROM (

SELECT column1, column2, column3, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC) AS rn

FROM TableName

) WHERE rn <=3;

-- 查询每个分组中 column2 列排名前 3 的数据

五、多表查询

1. 表连接操作

内连接

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

-- 返回两个表中匹配的行

左连接

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

-- 返回左表的所有行，右表匹配的行，右表不匹配的列值为 NULL

右连接

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

-- 返回右表的所有行，左表匹配的行，左表不匹配的列值为 NULL

全外连接

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

-- 返回两个表的所有行，不匹配的列值为 NULL

自连接

SELECT t1.column AS column1, t2.column AS column2 FROM Table t1 JOIN Table t2 ON t1.column1 = t2.column2;

-- 表与自身连接，用于比较同一表中的不同行

交叉连接

SELECT * FROM Colors CROSS JOIN Sizes;

-- 返回两个表的笛卡尔积

2. 子查询

标量子查询

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

-- 查询 TableA 中的 column1 列，并计算 TableB 中匹配的行数

IN 子查询

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

-- 查询 TableA 中 column2 列值在 Categories 表中 Name 为 'Electronics' 的记录中的 column2 列值

EXISTS 子查询

SELECT column1 FROM TableA a WHEREEXISTS(SELECT1FROM TableB WHERE column2 = a.column2);

-- 查询 TableA 中存在匹配 TableB 中 column2 列值的记录

子查询作为派生表

SELECTAVG(sum)AS avg FROM(SELECTSUM(column2)AS sum FROM TableA GROUPBY column1)AS t;

-- 查询 TableA 中每个 column1 分组的 column2 列总和的平均值

多条件子查询

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

-- 查询 TableA 中 column2 列值大于表中 column2 列平均值的记录

3. 联合查询

去重联合

SELECT column1 FROM TableA UNIONSELECT column1 FROM TableB;

-- 合并两个查询结果，并去除重复值

不去重联合

SELECT column1 FROM TableA UNIONALLSELECT column1 FROM TableB;

-- 合并两个查询结果，保留重复值

六、常用函数

1. 字符串处理

字符串长度

SELECT LENGTH(column1)FROM TableName;

-- 查询 column1 列的字符串长度

字符串截取

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

-- 截取 column_name 列从 start 位置开始的 length 长度的字符串

字符串替换

SELECTREPLACE(column1,'old_string','new_string')FROM TableName;

-- 将 column1 列中的 'old_string' 替换为 'new_string'

字符串拼接

SELECT CONCAT(column1, column2)FROM TableName;

-- 将 column1 和 column2 列的值拼接为一个字符串

字符串转大写

SELECT UPPER(column_name)FROM TableName;

-- 将 column_name 列的值转换为大写

字符串转小写

SELECT LOWER(column_name)FROM TableName;

-- 将 column_name 列的值转换为小写

2. 时间日期函数

当前时间

SELECT CURTIME();

-- 查询当前时间

当前日期

SELECT CURDATE();

-- 查询当前日期

当前日期和时间

SELECT NOW();

-- 查询当前日期和时间

日期向后加天数

SELECT DATE_ADD(NOW(),INTERVAL10DAY);

-- 将当前日期和时间向后加 10 天

日期减天数

SELECT DATE_SUB(NOW(),INTERVAL10DAY);

-- 将当前日期和时间向前减 10 天

获取两个日期差值

SELECT DATEDIFF(date1, date2);

-- 计算 date1 和 date2 之间的天数差

获取日期年

SELECTYEAR(date)FROM TableName;

-- 提取 date 列的年份

获取月

SELECTMONTH(date)FROM TableName;

-- 提取 date 列的月份

获取日

SELECTDAY(date)FROM TableName;

-- 提取 date 列的日

获取小时

SELECTHOUR(time)FROM TableName;

-- 提取 time 列的小时

获取分钟

SELECTMINUTE(time)FROM TableName;

-- 提取 time 列的分钟

获取秒

SELECTSECOND(time)FROM TableName;

-- 提取 time 列的秒

获取第几周

SELECT WEEK(time)FROM TableName;

-- 提取 time 列的周数

日期转换字符串

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

-- 将 date 列格式化为指定格式的字符串

字符串转日期

SELECT CAST(columnASDATE)FROM TableName;

-- 将 column 列的字符串值转换为日期类型

七、常用操作

1. 数据操作

插入单条数据

INSERTINTO TableName (Column1, Column2)VALUES(value1, value2);

-- 向表 TableName 中插入单条数据

插入多条数据

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

-- 向表 TableName 中插入多条数据

更新数据

UPDATE TableName SET Column1 = value1 WHERE Column2 = value2;

-- 更新表 TableName 中满足条件的记录

删除数据

DELETEFROM TableName WHERE Column2 = value2;

-- 删除表 TableName 中满足条件的记录

全表删除

DELETEFROM TableName;

-- 删除表 TableName 中的所有记录

清空表数据

TRUNCATETABLE TableName;

-- 快速清空表 TableName 中的所有记录

2. 表操作

创建表

CREATETABLE TableName (

    column1 INTPRIMARYKEY,

    column2 VARCHAR(50),

    column3 DATE

);

-- 创建一个新表 TableName

添加新列

ALTERTABLE TableName ADDCOLUMN column1 INT;

-- 向表 TableName 中添加新列 column1

修改列类型

ALTERTABLE TableName MODIFYCOLUMN column1 VARCHAR(20);

-- 修改表 TableName 中 column1 列的数据类型

删除列

ALTERTABLE TableName DROPCOLUMN column1;

-- 删除表 TableName 中的 column1 列

重命名表

ALTERTABLE TableName RENAMETO NewTableName;

-- 将表 TableName 重命名为 NewTableName

删除表

DROPTABLE TableName;

-- 删除表 TableName

3. 约束与索引

添加主键约束

ALTERTABLE TableName ADDPRIMARYKEY(column1);

-- 为表 TableName 的 column1 列添加主键约束

唯一约束

ALTERTABLE TableName ADDUNIQUE(column1);

-- 为表 TableName 的 column1 列添加唯一约束

外键约束

ALTERTABLE TableName ADDCONSTRAINT FK_column1 FOREIGNKEY(column1)REFERENCES TableB(column2);

-- 为表 TableName 的 column1 列添加外键约束，引用 TableB 的 column2 列

创建索引

CREATEINDEX idx_column1 ON TableName (column1);

-- 在表 TableName 的 column1 列上创建索引

删除索引

DROPINDEX idx_column1 ON TableName;

-- 删除表 TableName 上的索引 idx_column1

非空约束

ALTERTABLE TableName MODIFYCOLUMN column1 VARCHAR(100)NOTNULL;

-- 将表 TableName 的 column1 列设置为非空

4. 视图

创建视图

CREATEVIEW ViewName ASSELECT column1 FROM TableName WHERE condition;

-- 创建一个视图 ViewName，基于表 TableName 的查询结果

更新视图数据

UPDATE ViewName SET column1 ='value'WHERE condition;

-- 更新视图 ViewName 中的数据

删除视图

DROPVIEWIFEXISTS ViewName;

-- 删除视图 ViewName

5. 事务控制

开启事务

STARTTRANSACTION;

-- 开启一个新的事务

提交事务

COMMIT;

-- 提交当前事务，保存更改

回滚事务

ROLLBACK;

-- 回滚当前事务，撤销更改

保存点

SAVEPOINT savepoint1;

-- 设置一个保存点 savepoint1

回滚到保存点

ROLLBACKTO savepoint1;

-- 回滚到保存点 savepoint1

6. 权限管理

授予查询权限

GRANTSELECTON TableName TO user1;

-- 授予用户 user1 对表 TableName 的查询权限

授予所有权限

GRANTALLPRIVILEGESON DatabaseName.*TO'admin'@'localhost';

-- 授予用户 admin 对数据库 DatabaseName 的所有权限

撤销权限

REVOKEDELETEON TableName FROM user2;

-- 撤销用户 user2 对表 TableName 的删除权限

7. 其他操作

查询所有数据库

SHOWDATABASES;

-- 查询当前数据库服务器中的所有数据库

查询所有表

SHOWTABLES;

-- 查询当前数据库中的所有表

查询表结构

DESCRIBE TableName;

-- 查询表 TableName 的结构

查询建表语句

SHOWCREATETABLE TableName;

-- 查询创建表 TableName 的 SQL 语句

查询表的所有列

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

-- 查询指定数据库和表的所有列名

查询表的所有索引

SHOWINDEXFROM TableName;

-- 查询表 TableName 的所有索引

查询表大小

SELECT table_name AS'Table',ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2)AS'Size(MB)'FROM information_schema.TABLESWHERE table_schema ='database_name';

-- 查询指定数据库中每个表的大小（以 MB 为单位）

设置时区

SET time_zone ='Asia/Shanghai';

-- 设置时区为亚洲/上海

创建数据库

CREATEDATABASE database_name;

-- 创建一个新的数据库 database_name

删除数据库

DROPDATABASE database_name;-- 删除数据库 database_name