mysql常用查询

1、Mysql查询某个表的字段名

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'business_info';

 

2、生成数据字典

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='dida'

 

 

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_NAME='business'

 

 

3、多表更新

update settlement s
inner join business_bank b on
b.business_id = s.business_id
set s.status = 0    
where s.status = 1 and s.oid_paybill is null and b.pay_online = 1

 

update table1 t1,table2 t2 
set t1.name = t2.name where t1.id = t2.id

 

 

4、多表删除

delete ir from 
business_interest_rate ir
left join business_base b on ir.business_id = b.id
left join business_head bh ON b.business_parent_id = bh.id
left join business_industry i ON i.id = bh.industry
where b.id is null

 

5、mysql中的先排序后分组

 

gp比较集中时使用（一个gp下有好多好多记录）：

SELECT a.id,a.sort,a.gp,a.name FROM (  
    SELECT * FROM sort_group ORDER BY sort DESC  limit 100000
) a  
GROUP BY a.gp 

 

gp比较分散时使用（就是一个gp下只有几条记录）：

SELECT * FROM `sort_group` 
WHERE id IN 
(SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY sort DESC),',',1) FROM sort_group GROUP BY gp);
 

 

 

/*查询表的字段名*/

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'orders';

 

 

/*生成数据字典*/

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,case when IS_NULLABLE = 'NO' then '否' else '是' end,COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA='dida'
 

 

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT from information_schema.columns where TABLE_NAME='business'
 

 

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,case when IS_NULLABLE = 'NO' then '是' else '否' end,COLUMN_DEFAULT,case when COLUMN_KEY = 'PRI' then '主键' else '' end,COLUMN_COMMENT  from information_schema.columns where TABLE_SCHEMA='test_police_zhxjg'

 

包含表描述

select c.TABLE_SCHEMA,c.TABLE_NAME,t.TABLE_COMMENT, c.COLUMN_NAME,c.COLUMN_COMMENT,c.COLUMN_TYPE,case when c.IS_NULLABLE = 'NO' then '否' else '是' end,
c.COLUMN_DEFAULT,'备注'
from information_schema.columns c
left join INFORMATION_SCHEMA.TABLES t on c.TABLE_NAME = t.TABLE_NAME
where c.TABLE_SCHEMA='aps_rdkj_dev'

 

 

/*取万*/

select round(sum(money)/10000+0.00000001,2) as total_money  from orders

 

 

/*查询没有主键的表*/

select table_schema,table_name from information_schema.tables 
where (table_schema,table_name) not in(
    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'    
)
and table_schema not in (
    'sys','mysql','information_schema','performance_schema'
);