mysql 常用函数

这里讲解mysql常用函数,适合用统计以及平时sql工作中

1、多列合并一列展示

SELECT concat(uid0, uid1, uid2, uid3, uid4) FROM account_info

image.png

2、UNION ALL 关键字

SELECT created_by, creation_date, remarks FROM A
UNION ALL
SELECT created_by, creation_date, remarks FROM B

3、 a表某字段关联b表某几个字段 关联查询

SELECT * from user_info a LEFT JOIN account_info b on (a.uid = b.uid0 or a.uid = b.uid1 or a.uid = b.uid2)

4、某个条件下的条数

count(if(status=1,true,0))中count函数返回一个布尔值类型的数值。如果status=1,返回true,会计数;如果status不等于1返回0,不会计数。

select sum(IF(type = 'INVENTORY', true, 0)) from data_bi.econ_section_config;

5、sum和count函数中使用if判断条件

sum函数中使用if判断条件格式为:sum(if(条件,列值,0))
count函数中使用if判断条件格式为:
1.统计总数,count(if(条件字段名=值,true,null))
2.统计总数去重复值,count(DISTINCT 需要计算count的字段名,if(条件字段名=值,true,null))

6、并集、交集、差集

select count(*) INTO oneStay from
(
		SELECT account_id FROM data_tslog.guess_user_settle_flow A
		WHERE A.bankruptcy_flag = 1 and A.event_time BETWEEN eventTime and endTime
    UNION
    SELECT account_id FROM data_tslog.guess_user_room_flow B
		WHERE B.event_time BETWEEN eventTime and endTime
		UNION
		SELECT account_id FROM data_tslog.guess_user_bet_flow C
		WHERE C.event_time BETWEEN eventTime and endTime
) as a cross join (
		SELECT account_id FROM data_tslog.guess_user_settle_flow A
		WHERE A.bankruptcy_flag = 1 and A.event_time BETWEEN oneStayStartTime and oneStayEndTime
    UNION
    SELECT account_id FROM data_tslog.guess_user_room_flow B
		WHERE B.event_time BETWEEN oneStayStartTime and oneStayEndTime
		UNION
		SELECT account_id FROM data_tslog.guess_user_bet_flow C
		WHERE C.event_time BETWEEN oneStayStartTime and oneStayEndTime
) as b on a.account_id = b.account_id;

从上面可以看得出:
1、并集就是 UNION,还有UNION ALL。没有ALL是将其去重了
2、交集可以用cross join 其实内连接inner join也可以
3、差集.......
具体例子:
交集(下面等价)

select DISTINCT account_id from data_tslog.guess_user_room_flow INNER JOIN data_tslog.guess_user_bet_flow USING(account_id);
select DISTINCT a.account_id from data_tslog.guess_user_room_flow a INNER JOIN data_tslog.guess_user_bet_flow b on a.account_id = b.account_id

并集

UNION ALL 或 UNION

差集: (只演示t1和t2的差集,t1有t2没有的)

select * from t1 where not EXISTS (
select * from t2 where t1.name = t2.name and t1.age = t2.age
)
 
select * from t1 where (name,age) not in ( select * from t2)
 
select t1.* from t1 LEFT JOIN t2 on t1.name = t2.name 
and t1.age = t2.age where t2.name is null 

7、mysql 大于小于号

<			小于号
>			大于号
<![CDATA[ >= ]]>	大于等于
<![CDATA[ <= ]]>	小于等于

8、判断是否存在某张表

-- 指定库
SELECT table_name FROM information_schema.TABLES WHERE table_name = #{tableName} and TABLE_SCHEMA='data_bi_pay_value'
-- 没有指定库
SELECT table_name FROM information_schema.TABLES WHERE table_name = #{tableName};

9、mysql查看表信息相关语句

DESC bi_advert_put_statistics
show full columns from bi_advert_put_statistics

10、显示创表语句

show create table

11、MySQL数字的取整、四舍五入、保留n位小数

函数说明
FLOOR(X)返回不大于X的最大整数。
CEIL(X)、CEILING(X)返回不小于X的最小整数。
TRUNCATE(X,D)返回数值X保留到小数点后D位的值,截断时不进行四舍五入。
ROUND(X)返回离X最近的整数,截断时要进行四舍五入。
ROUND(X,D)保留X小数点后D位的值,截断时要进行四舍五入。
FORMAT(X,D)将数字X格式化,将X保留到小数点后D位,截断时要进行四舍五入。