一、基础查询
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
感谢分享 有用
发表评论