mysql查询常用方法总结

mysql查询常用方法总结

前言

仅记录个人写sql时所常用的方法。
为方便举例，现建表如下：

round()

round() 遵循四舍五入把原值转化为指定小数位数，例：round(5.05,0) = 5;round(5.55,0)=6，
如：SELECT round(salary,1) as salary from user

floor()

floor()向下舍入，返回小于等于 x 的最大整数值 例：floor(5.00)= 5;floor(5.55) = 5
如：SELECT FLOOR(salary) salary from user

ceiling()

与floor()相对，ceiling()向上舍入，返回大于等于 x 的最小整数值 例：ceiling(6.00) = 6;ceiling(5.55)=6

ifnull()

ifnull()接受两个参数，第一个参数是要判断空值的字段或值，第二个字段是当第一个参数是空值的情况下要替换返回的另一个值。两个参数都可以是文字值或表达式。例： ifnull(“ok” , “no”) //ok ； ifnull(null , “no”) //no
如：SELECT IFNULL(address,‘无’) salary from user

if()

if()接受三个参数：if(判断语句,值1，值2)；若为true，则得到的值为值1，否则为值2，
例：取两个数（a，b）的最大值if(a>b,a,b)；类似于三元表达式：a>b?a:b；
在mysql更新语句中：update salary set 字段名= if(字段名=‘xx’,’'xx,‘xx’)
如：update user set sex = if(sex=‘男’,‘女’,‘男’)
即把
即：性别相互修改了
再运行一次，返回最先的样子。

data_format()

data_format(date,format)，date 参数是合法的日期，format 规定日期/时间的输出格式，将时间以指定形式显示出来
例：date_format(now(),’%d-%m-%Y’) // 30-12-2020
输出格式有：%Y：表示年（4位）；%m：表示月（00-12）；%d：表示月的天（00-31）%e：表示月的天（0-31）；%H：表示小时（00-23）；%i：表示分钟（00-59）；%S或%s：表示秒（00-59）…
如：select DATE_FORMAT(create_date,’%Y.%m.%d’) as create_date from user

case when then else end

（1）简单case函数

case sex when 1 then ‘男’ when 2 then ‘女’ else ‘无’ end

（2）case搜索函数

case when sex = 1 then ‘男’ when sex = 2 then ‘女’ else ‘无’ end

注：（1）简单case函数只能等于，对于不等于，只能用case搜索函数；
（2）如果满足第一个条件，就会忽略第二个条件
如：SELECT role_code,
CASE
WHEN role_code = ‘boss’ THEN ‘老板’
WHEN role_code = ‘employee’ THEN ‘员工’
WHEN role_code = ‘manager’ THEN ‘管理员’
ELSE ‘其他’ END role
FROM USER

concat()

concat(str1, str2,…)：将多个字符串连接成一个字符串，返回拼接后的字符串。
注：如果有任何一个参数为null，则返回值为null。
如：select concat(account,name,address,phone) from user

concat_wt()

concat_ws(separator, str1, str2, …)：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符。separator为指定分隔符：如：’–’ ，’/’，’，’…
注：分隔符不能为null，否则返回结果为null。
如：select CONCAT_WS(’–’,account,name,address,phone) from user

group_concat()

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
向原表中插入两条数据以供举例。插入数据如下：

如：根据role_code分组，将工资打印出来：
SELECT role_code,GROUP_CONCAT(salary) from user GROUP BY role_code

字段employee的工资有重复的，可添加distinct进行去重操作
如：SELECT role_code,GROUP_CONCAT(distinct salary) from user GROUP BY role_code

字段employee的工资排序默认为asc，若想倒序，则使用order by 进行排序即可
如：SELECT role_code,GROUP_CONCAT(distinct salary ORDER BY salary DESC) from user GROUP BY role_code

字段employee的默认分隔符为’,’，可自行设置分隔符
如：SELECT role_code,GROUP_CONCAT(distinct salary ORDER BY salary DESC SEPARATOR ‘----’) from user GROUP BY role_code

注：在使用group_concat()函数时，后面group by跟的是需要进行分组的字段，若无group by，则会全部打印在一行显示出来
如：SELECT role_code,GROUP_CONCAT(distinct salary ORDER BY salary DESC SEPARATOR ‘----’) from user

left()

left(str,len)是一个字符串函数，它返回具有指定长度的字符串的左边部分。str为想要截取的字段名，len为想要截取的长度。
在user表中，时间的存储格式为yyyy-MM-dd HH:mm:ss，若只想获取其年月日，不要时分秒，那么除了用date_format()之外，还可使用left()，如：select LEFT(create_date,10) from user