mysql常用指令

亦凉 2023-10-11 17:39 53阅读 0赞

本文会逐渐把所有常用的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语句的后面，作为分组的依据，要么就包含在聚合函数中。

表中数据如下：  
![在这里插入图片描述][8f14176a407c461fae6e779e23e2b223.png_pic_center]

我们查找姓名相同的人的id，可以这样查询：

select name,id from `user` order by name;

![在这里插入图片描述][03a571925fe44554b806176c152361f5.png_pic_center]

可以看到名字有很多重复的，看上去很不直观，所以引出**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;

![在这里插入图片描述][53ddaf2385f64da9adc891f26c32b3e6.png_pic_center]

当然我们也可将上面的id号从大到小排序，且用’\_'作为分隔符：

select name,GROUP_CONCAT(id ORDER BY id desc SEPARATOR '_') from `user` GROUP BY name;

![在这里插入图片描述][2fbeb17cabfe4192b20e395806072043.png_pic_center]

题目示例：[https://leetcode.cn/problems/group-sold-products-by-the-date/submissions/][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后的判断字段必须是聚合函数返回的结果**  
示例：  
![在这里插入图片描述][94a44003fcc24e0785604c1ab09c4998.png_pic_center]

如上面这个表格，我们按照年龄进行分组

SELECT id,name,age from user group BY age

![在这里插入图片描述][374a53dbc9474590a6d1aa7d63866bb1.png_pic_center]

然后我们还需统计出年龄大于等于23的

SELECT id,name,age from user group BY age HAVING age>=23

![在这里插入图片描述][44fe26444f974f0b93885566ac9b4104.png_pic_center]

## 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. 每个产品在不同商店的价格][1795.]）带大家入门：  
题目描述：  
![在这里插入图片描述][978fd2d97d76479d9783d4a1cda88ff4.png_pic_center]

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product\_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。  
![在这里插入图片描述][ed781378b97f4eddb71d206c3b617410.png_pic_center]

这道题我们就是一道比较典型的行转列的问题，我们按照刚刚说的解题步骤来：

*  一列一列处理：把“列名”做为新列的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这一列中。  
![在这里插入图片描述][78b6e79a3242493f842c97676cf22206.png_pic_center]

如果把store1改成其他名字，如store111，则会变成以下结果：  
![在这里插入图片描述][8f0278d0915d46d2a08219923f362f3f.png_pic_center]

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

[8f14176a407c461fae6e779e23e2b223.png_pic_center]: https://img-blog.csdnimg.cn/8f14176a407c461fae6e779e23e2b223.png#pic_center
[03a571925fe44554b806176c152361f5.png_pic_center]: https://img-blog.csdnimg.cn/03a571925fe44554b806176c152361f5.png#pic_center
[53ddaf2385f64da9adc891f26c32b3e6.png_pic_center]: https://img-blog.csdnimg.cn/53ddaf2385f64da9adc891f26c32b3e6.png#pic_center
[2fbeb17cabfe4192b20e395806072043.png_pic_center]: https://img-blog.csdnimg.cn/2fbeb17cabfe4192b20e395806072043.png#pic_center
[https_leetcode.cn_problems_group-sold-products-by-the-date_submissions]: https://leetcode.cn/problems/group-sold-products-by-the-date/submissions/
[94a44003fcc24e0785604c1ab09c4998.png_pic_center]: https://img-blog.csdnimg.cn/94a44003fcc24e0785604c1ab09c4998.png#pic_center
[374a53dbc9474590a6d1aa7d63866bb1.png_pic_center]: https://img-blog.csdnimg.cn/374a53dbc9474590a6d1aa7d63866bb1.png#pic_center
[44fe26444f974f0b93885566ac9b4104.png_pic_center]: https://img-blog.csdnimg.cn/44fe26444f974f0b93885566ac9b4104.png#pic_center
[1795.]: https://leetcode.cn/problems/rearrange-products-table/
[978fd2d97d76479d9783d4a1cda88ff4.png_pic_center]: https://img-blog.csdnimg.cn/978fd2d97d76479d9783d4a1cda88ff4.png#pic_center
[ed781378b97f4eddb71d206c3b617410.png_pic_center]: https://img-blog.csdnimg.cn/ed781378b97f4eddb71d206c3b617410.png#pic_center
[78b6e79a3242493f842c97676cf22206.png_pic_center]: https://img-blog.csdnimg.cn/78b6e79a3242493f842c97676cf22206.png#pic_center
[8f0278d0915d46d2a08219923f362f3f.png_pic_center]: https://img-blog.csdnimg.cn/8f0278d0915d46d2a08219923f362f3f.png#pic_center