MySQL中实用函数及查询技巧

喜欢ヅ旅行 2022-05-13 23:56 265阅读 0赞

**函数一：CONCAT**

*  使用场景：查询时把多个字段拼接成一个字符串
 *  例       子：select concat('id：',id,',name：',name) from student
 *  解       析：需要连接的字段用逗号分隔开来，自定义的拼接符号使用单引号包裹，也使用逗号分隔
 *  注       意：这个函数需要注意的是，如果有一列值为空，那么这一列拼接的结果都会为null，为了避免这样的状况发生，需要在可能为空的列上添加函数二

**函数二：IFNULL**

*  使用场景：查询时判断字段是否为空，如果为空则取第二个默认参数，不为空则取表中数据
 *  例       子：select IFNULL(name,'is null') from student
 *  解       析：两个参数，第一个参数为字段名，第二个参数为默认值
 *  注       意：是对查询结果进行的判断，如果不存在的数据是不会取默认值，如下列情况（student表中一共就十条数据）：select IFNULL(name,'is null') from student where id = 100 ，这种结果就是null，而不是默认值 'is null'

**函数三：COALESCE**

*  使用场景：查询时判断字段是否为空，如果字段为空则取从第二个参数开始不为空的值，字段不为空则取表中数据
 *  例       子：select COALESCE(name,null,null,'is default') from student
 *  解       析：多个参数，第一个参数为字段名，从第二个参数开始都为可选值，如果字段为空，取值是从第二个参数开始不为空的值，如果都为空，则返回null
 *  注       意：暂无

**函数四：REPLACE**

*  使用场景：查询时替换字段中的指定字符
 *  例       子：select REPLACE(name,'小明','小红') from student
 *  解       析：三个参数，第一个参数为字段名，第二个参数为此字段中需要替换的字符串，第三个参数为替换后的字符串
 *  注       意：暂无

**函数五：FIND\_IN\_SET**

*  使用场景：指定字符在字段中是否存在，这类字段都有一个特点，就是会有多个值，以 ','（英文逗号）分隔
 *  例       子：select id , name , score from student where find\_in\_set ('100' , score )
 *  解       析：两个参数，第一个参数为查询的条件，第二个参数为需要匹配的字段；假设学生表有个字段 score 用来存三门课的分数，是以 ','（英文逗号分隔），这条语句就是查询学生表中有至少一门分数是100分的学生 id 和 name
 *  注       意：两个参数的先后顺序，经常容易写颠倒，最坑的是写颠倒了程序不报错...

**函数六：CASE**

*  使用场景：一张表中有 state 字段，取值为 0（禁用） 或 1（启用），但是想在查询结果中分别显示对应的中文汉字 
 *  例       子：select id , name , (case state when 0 then '禁用' when 1 then '启用' else '其他' end )  state from student
 *  解       析：查询学生表的id，名称和状态，当状态为0时显示 禁用 ，为1时显示 启用 ，不为0和1时显示 其他
 *  注       意：最后有一个end作为结束，不要忘了

**函数七：GROUP\_CONCAT**

*  使用场景：假如有一张产品表，一张产品标签表以及一张产品标签关联表，想查出每个产品对应的多个产品标签 id 
 *  例       子：SELECT pi.id,GROUP\_CONCAT( ptr.product\_tag\_id ) as tags FROM product\_info pi

LEFT JOIN product\_tag\_rel ptr ON pi.id = ptr.product\_info\_id GROUP BY pi.id

*  解       析：查询每一个产品以及每一个产品对应的产品标签 id（产品标签 id 会以逗号分隔）
 *  注       意：分组

**函数八：DATE\_FORMAT**

*  使用场景：格式化时间
 *  例       子：SELECT id , DATE\_FORMAT( create\_datetime, '%Y-%m-%d %H:%i' ) FROM student
 *  解       析：查询学生表的 id 和格式化后的创建时间（年-月-日 时:分）
 *  注       意：format 字母的大小写

**函数九：TIMESTAMPDIFF**

*  使用场景：求出时间差
 *  例       子：SELECT TIMESTAMPDIFF(SECOND , create\_time , update\_time)  FROM student
 *  解       析：查询学生表的创建时间和修改时间的秒差

（SECOND：秒   /   MINUTE：分   /   HOUR：时   /   DAY：天   /   MONTH：月   /   YEAR：年）

*  注       意：create\_time < update\_time

--------------------

**操作符一：UNION 和 UNION ALL**

*  使用场景：把两条或多条select查询语句的结果合并，union不显示重复数据，union all显示重复数据
 *  例       子：( select id,name from student where id = (select max(id) from student) ) union ( select id,name from student where id = (select min(id) from student) )
 *  解       析：查询了学生表中最大id的记录和最小id的记录
 *  注       意：①两条 sql 的最外层最好使用小括号包裹   ②sql中单独写的 order by 排序会失效，这种 sql 排序要写到最后一条 sql 的括号外面    ③ 每条 sql 查询的列数量一定要一致，数量一致而列名不一致，都会以第一条 sql 为准，列的别名也会以第一条 sql 为准

--------------------

**查询技巧一：**

*  使用场景：查询一堆数据，把指定 id 的那条数据放到第一条
 *  例       子：select id , name from student order by case when id = 9 then id end desc , case when id <> 9 then create\_time end desc
 *  解       析：查询学生表的 id 和 name 字段，如果 id 为 9 就以 id 倒叙排列 ， 如果 id 不为 9 就以创建时间倒叙排列，最后id 为 9 的就会到一条的位置显示，其他的会按照创建时间的倒叙排列
 *  注       意：暂无

**查询技巧二：**

*  使用场景：把查询结果排序，另一种方式实现 Oracle 中的 rownum（这种方法可以用于指定 id 在查询的结果集中是第几条出现的）
 *  例       子：select @r := @r + 1 rownum , id from (select @r := 0 , id , name from student) alias
 *  解       析：查询学生表的数据，并有一列会从1开始排列
 *  注       意：from 后面跟的子查询是一个临时表，需要别名（这里是 'alias'，别名自定义），不然会报错

长期持续更新

希望能够帮助到你

over