本文会逐渐把所有常用的sql语句都一一罗列出来,适用于大部分的面试和笔试,自己在项目中实际操作也可以,同时本文会持续更新,敬请关注。
1.使所有字母大写
upper(str)
eg:upper('green')->GREEN
2.使所有字母小写
lower(str)
eg:lower('grEEn')->green
3.连接字符串(字符串拼接)
concat(str1,str2)
eg:concat('aaB','cd')->aaBcd
4.裁剪字符串
# 从第a位开始,提取b个字符,b不写的话默认截取到最后一位
SUBSTRING(str,a,b)
# 从左边开始,提取b个字符
left(str,b)
# 从右边开始,提取b个字符
right(str,b)
5.获取字符串长度
- length():单位是字节,utf8编码下,一个汉字三个字节,一个数字或字母一个字节。gbk编码下,一个汉字两个字节,一个数字或字母一个字节。
- char_length():单位为字符,不管汉字还是数字或者是字母都算是一个字符。
length('ninesun中国')=7+6=13
CHAR_LENGTH('ninesun中国')=7+2=9
6.group_concat()函数
在有group by的查询语句中,select指定的字段要么就包含在group by语句的后面,作为分组的依据,要么就包含在聚合函数中。
表中数据如下:
我们查找姓名相同的人的id,可以这样查询:
select name,id from `user` order by name;
可以看到名字有很多重复的,看上去很不直观,所以引出group_concat:
- 功能:将group by产生的同一个分组中的值连接起来,返回一个字符串结果
- 语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
- 说明:通过使用distinct可以排除重复值;如果希望对结果中的值进行排序,可以使用order by子句;separator是一个字符串值,缺省为一个逗号。
所以我们查询姓名相同的Id,这个功能可以这样写:
select name,GROUP_CONCAT(id) from `user` GROUP BY name;
当然我们也可将上面的id号从大到小排序,且用’_'作为分隔符:
select name,GROUP_CONCAT(id ORDER BY id desc SEPARATOR '_') from `user` GROUP BY name;
题目示例:https://leetcode.cn/problems/group-sold-products-by-the-date/submissions/
7.rlike
包含某字符
# ^DIAB1包含以DIAB1开头
# .*表示任意字符 \\表示空格
# .*\\sDIAB1 任意字符空格DIAB1开头
conditions rlike '^DIAB1|.*\\sDIAB1';
注意rlike里可以用正则表达式匹配
8.having
mysql中,当我们用到聚合函数,如sum,count后,又需要筛选条件时,having就派上用场了,因为WHERE是在聚合前筛选记录的,having和group by是组合着用的,当然也可以不和group by连着用,但是having后面的字段必须是返回字段。
注意 having后的判断字段必须是聚合函数返回的结果
示例:
如上面这个表格,我们按照年龄进行分组
SELECT id,name,age from user group BY age
然后我们还需统计出年龄大于等于23的
SELECT id,name,age from user group BY age HAVING age>=23
9.union
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
比如第一个查询有100条两列,第二个查询结果也为160条两列,故使用union all之后,可以将这两个结果合并成一个,变成260行两列。
语法格式:
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
参数
- expression1, expression2, … expression_n: 要检索的列。
- tables: 要检索的数据表。
- WHERE conditions: 可选, 检索条件。
- DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
- ALL: 可选,返回所有结果集,包含重复数据。
unin 的用法还有很多,像很多面试必问的就是:行转列类型的题目无非是以下两个步骤:
- 一列一列处理:把“列名”做为新列的value,把原来的value也作为新列,这是一个查询,其他列不要
- 用union all拼接每一列的结果
以下面一道比较简单的题目(leeteCode:1795. 每个产品在不同商店的价格)带大家入门:
题目描述:
请你重构 Products 表,查询每个产品在不同商店的价格,使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售,则不输出这一行。
输出结果表中的 顺序不作要求 。
查询输出格式请参考下面示例。
这道题我们就是一道比较典型的行转列的问题,我们按照刚刚说的解题步骤来:
- 一列一列处理:把“列名”做为新列的value(本题的store),把原来的value也作为新列(本题的price),这是一个查询,其他列不要
- 用union all拼接每一列的结果
注意本题如果这一产品在商店里没有出售,则不输出这一行,所以要原列 is not null的筛选条件
所以最终代码如下:
select product_id,'store1' as store,store1 as price
from Products where store1 is not null
union all
select product_id,'store2' as store,store2 as price
from Products where store2 is not null
union all
select product_id,'store3' as store,store3 as price
from Products where store3 is not null
我大致解释一下里面’store1’这种是新增一个新列,这个store1就会显示在stroe这一列中。
如果把store1改成其他名字,如store111,则会变成以下结果:
看到这个地方大家应该了解了行列转换的具体用法。