MySQL语句必知必会（二）
发布于2021-08-08
使用数据处理函数
select Upper([column_name]) as [upcase_alias] from [table];
select [column_name] from [table] where Upper([column_name])=[value];
常用的文本处理函数
| 函数 | 说明 |
|---|---|
| Left(str, len) | 返回字符串左边指定长度的字符 |
| Right(str, len) | 返回字符串右边指定长度的字符 |
| SubString(str, position, len) | 从特定位置开始的字符串返回一个给定长度的子字符串 |
| Length(str) | 返回字符串的长度 |
| Lower(str) | 将字符串转换为小写 |
| Upper(str) | 将字符串转换为大写 |
| LTrim(str) | 去掉字符串左边的空格 |
| RTrim(str) | 去掉字符串右边的空格 |
| Locate(substr, str) | 返回子串在字符串中第一次出现的位置，如果子串不存在返回0 |
常用的日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate(date, value) | 添加一个日期（天、周等） |
| AddTime(date, value) | 增加一个时间（时、分等） |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期之差 |
| Date_Add() | 向日期添加指定的时间间隔 |
| Date_Format() | 返回一个格式化的日期或字符串 |
| Day() | 返回一个日期的天数部分 |
| Month() | 返回一个日期的月份部分 |
| Year() | 返回一个日期的年份部分 |
| DayOfWeek() | 对应一个日期返回星期几 |
| Time() | 返回一个日期时间的时间部分 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Second() | 返回一个时间的秒部分 |
| Now() | 返回当前日期和时间 |
常用的数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回一个数的绝对值 |
| Cos() | 返回一个角度的余弦 |
| Exp() | 返回一个数的指数值 |
| Mod() | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sqrt() | 返回一个数的平方根 |
使用聚集函数汇总数据
select avg([column_name]) as [avg_alias] from [table];
// count() 函数参数为 “*” 时不忽略 null 值
select count(*) as [count_alias] from [table];
// 返回不同值的聚集结果
select avg(distinct [column_name]) as [distinct_avg_alias] from [table];
常用的聚集函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
分组数据
select [column_name], count(*) as [count_alias] from [table] group by [column_name];
// 对分组数据统计汇总
select [column_name], count(*) as [count_alias] from [table] group by [column_name] with rollup;
// 过滤分组
select [column_name], count(*) as [count_alias] from [table] group by [column_name] having count(*) > [value];
select 子句顺序
| 字句 | 说明 | 是否必须使用 |
|---|---|---|
| select | 要返回的列或表达式 | 是 |
| from | 从中检索数据的表 | 仅在从表选择数据时使用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在按组计算聚集时使用 |
| having | 组级过滤 | 否 |
| order by | 输出排序顺序 | 否 |
| limit | 要检索的行数 | 否 |
使用子查询
select [column_name1] form [table1] where [column_name2] in (select [column_name2] from [table2] where [column_name3]=[value]);
select [column_name1], (select count(*) from [table1] where [table1].[column_name2]=[table2].[column_name2]) as [count_alias] from [table2];
创建等值联结（内联结）
select [column_name_table1], [column_name_table2] from [table1], [table2] where [table1].[column_name]=[table2].[column_name];
select [column_name_table1], [column_name_table2] from [table1] inner join [table2] on [table1].[column_name]=[table2].[column_name];
// 联结多个表
select [column_name_table1], [column_name_table2], [column_name_table3] from [table1], [table2], [table3] where [table1].[column_name]=[table2].[column_name] and [table2].[column_name1]=[table3].[column_name1] and [column_name2]=[value];
创建自联结
// 查询语句字段前必须给出表前缀，否则会报错
select [table1_alias1].[column_name1], [table1_alias1].[column_name2] from [table1] as [table1_alias1], [table1] as [table1_alias2] where [table1_alias1].[column_name3]=[table1_alias2].[column_name3]
创建外部联结
// 与内部联结不同的是，外部联结包括那些还没有关联的行
select [table1].[column_name], [table2].[column_name1] form [table1] left outer join [table2] on[table1].[column_name2]=[table2].[column_name2];
select [table1].[column_name], [table2].[column_name1] form [table1] right outer join [table2] on[table1].[column_name2]=[table2].[column_name2];
组合查询
// 也称为并（union）或者复合查询，与具有多个where子句条件完成的功能相同
select [column_name1], [column_name2] form [table1] where [column_name3]>[value] union select [column_name1], [column_name2] form [table2] where [column_name4]=[value]
// 包含重复行则使用union all进行组合
全文本搜索
// MyISAM数据库引擎支持全文本搜索，不具有词分隔符的语言（中文）不能恰当的返回全文本搜索结果
select [column_name] from [table] where match([column_name]) against([value]);
// 使用查询扩展
select [column_name] from [table] where match([column_name]) against([value] with query expansion);
// 使用布尔操作符（上面的查找过程中必需对表字段定义fulltext索引，而布尔操作不需要对表字段定义fulltext索引）
select [column_name] from [table] where match([column_name]) against([value] in boolean mode);
全文本布尔操作符
| 布尔操作符 | 说明 |
|---|---|
| + | 包含，词必须存在 |
| - | 排除，词必须不出现 |
| > | 包含，而且增加等级值 |
| < | 包含，而且减少等级值 |
| () | 把词组成子表达式，允许子表达式作为一个组被包含，排除，排列等 |
| ~ | 取消一个词的排序值 |
| * | 词尾的通配符 |
| "" | 定义一个短语 |
参考书籍或文章：
1.《MySQL必知必会》