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位,截断时要进行四舍五入。

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 '删除'
				when nlog.op_event = 2 then '修改'
				when nlog.op_event = 4 then '导出'
				when nlog.op_event = 5 then '导入'
			end) opEvent,
        nlog.op_time opTime,
        su.nick opUser,
        nlog.is_deleted isDeleted
        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 '测试验收|自动化开发|资源'
打赏
支付宝 微信
上一篇 下一篇