mysql 常用函数

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

mysql 常用函数

1、多列合并一列展示

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

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 大于小于号

&lt;			小于号
&gt;			大于号
<![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位，截断时要进行四舍五入。

12、获得所有的表名和备注

SELECT C.`TABLE_NAME`, C.`TABLE_COMMENT`
	FROM INFORMATION_SCHEMA.`TABLES` c
	WHERE c.`TABLE_SCHEMA`='appserver_new'

13、获得所有的字段

SELECT 
  c.`COLUMN_NAME`,c.`IS_NULLABLE`,c.`DATA_TYPE`,
  c.`CHARACTER_MAXIMUM_LENGTH`,c.`NUMERIC_PRECISION`,c.`NUMERIC_SCALE`,
  c.`EXTRA`,c.`COLUMN_COMMENT`
FROM INFORMATION_SCHEMA.`COLUMNS` c
WHERE c.`TABLE_NAME`='server_event_log'
  AND c.`TABLE_SCHEMA`='appserver_new'
  ORDER BY c.`ORDINAL_POSITION`

14、获得主键

SELECT k.`COLUMN_NAME`
FROM INFORMATION_SCHEMA.`STATISTICS` k
WHERE k.`TABLE_NAME`='server_event_log' 
  AND k.`TABLE_SCHEMA`='appserver_new'
  AND k.`INDEX_NAME`='PRIMARY'

15、获得所有外键

SELECT k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`,k.`REFERENCED_COLUMN_NAME`
FROM information_schema.`KEY_COLUMN_USAGE` k 
WHERE k.`TABLE_NAME`='server_event_log'  AND k.`TABLE_SCHEMA`='appserver_new' 
AND k.`REFERENCED_TABLE_NAME` IS NOT NULL

16、IF ELSE IF多条件查询

select nlog.id,
        nlog.op_module opModule,
			(case   
				when nlog.op_event = 0 then '增加' 
				when nlog.op_event = 1 then '删除'
			end) opEvent,
        nlog.op_time opTime,        su.nick opUser
        from net_operation_log nlog
        left join sys_user su on nlog.op_user = su.username
        where nlog.is_deleted = 0

17、多条件模糊匹配

SELECT `id` FROM `groups` WHERE is_deleted = 0 AND `name` REGEXP '测试验收|自动化开发|资源'

18、特定数据TOP置顶

其实本函数有时候用的挺多，特别是下拉那种地方，解决方案我总结有两种

UNION ALL

select * from table where name='D'
UNION ALL
select * from table where name<>'D'

CASE WHEN THEN 0 ELSE 1 END FLAG order by flag asc

SELECT CASE WHEN [name]='D' THEN 0 ELSE 1 END FLAG, * 
FROM TABLE 
order by flag asc

19、删除重复数据

今天看一篇文章，讲到如何删除重复的数据，其实删除重复数据不难，重点还保留一条数据，于是乎有了如下一个总结。

查询重复数据

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(1)>1)

删除全部重复数据，一条不留

先通过分组名称，查出最小的id的数据，这些就是我们要留下的火种，所以再查询出id不面的，就是我们要删除数据的重复。

DELETE  FROM  student 
WHERE NAME  IN (
 SELECT  t.NAME 
FROM ( SELECT NAME FROM student GROUP BY NAME HAVING count( 1 ) > 1 ) t)

删除表中删除重复数据，只保留一条

DELETE FROM student
 WHERE id NOT IN (
   SELECT t.id  FROM ( SELECT MIN( id ) AS id FROM student GROUP BY `name` ) t 
 )

20、根据某个字段分组查询，得到另一个字段不一致的数据

查询，【8.0写法】

WITH
a as (
	select a.case_num aCount, a.id id from test_strategy a WHERE a.is_deleted = 0
	GROUP BY a.id
),
b as (
	SELECT count(b.id) bCount, b.test_strategy_id FROM testcase_exec_history b WHERE b.is_deleted = 0
	GROUP BY b.test_strategy_id
)
SELECT a.*, b.* FROM a,b WHERE a.id = b.test_strategy_id AND a.aCount <> b.bCount

21、关联表来进行修改某个表的某字段

UPDATE test_strategy a INNER JOIN 
	(SELECT * FROM (
		select a.case_num aCount, count(b.id) bCount, a.id aId from test_strategy a INNER JOIN  testcase_exec_history b on a.id = b.test_strategy_id 
		WHERE a.is_deleted = 0 and b.is_deleted = 0 AND a.strategy_from = 'testone-pre'
		GROUP BY a.id  
		HAVING aCount <> bCount
	) t) b on a.`id` = b.aId
SET a.case_num=b.bCount, a.gmt_modified=a.gmt_modified
	WHERE a.is_deleted = 0 AND a.strategy_from = 'testone-pre';

22、自定义排序

ORDER BY FIELD(pm.`execResult`,'FE' ,'Fail') DESC, pm.item DESC

23、多字段剔除重复并且顺序一致

GROUP_CONCAT(DISTINCT pg.NAME ORDER BY pg.id) professionalGroupName,
GROUP_CONCAT(DISTINCT pg.id ORDER BY pg.id) professionalGroupId