mysql常用指令

本文会逐渐把所有常用的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，则会变成以下结果：

看到这个地方大家应该了解了行列转换的具体用法。