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'

 

--oracle

SELECT A.OWNER AS "数据库名", A.TABLE_NAME AS "表名",C.COMMENTS AS "表描述", A.COLUMN_NAME AS "字段名",
CASE A.DATA_TYPE
WHEN 'VARCHAR2' THEN A.DATA_TYPE || '(' || A.CHAR_LENGTH || ')'
WHEN 'NUMBER' THEN A.DATA_TYPE || '(' || A.DATA_LENGTH || ',' || A.DATA_SCALE || ')'
ELSE A.DATA_TYPE
END AS "字段类型",
A.NullAble AS "是否允许为空",A.data_default AS "缺省值", B.COMMENTS AS "字段描述"
FROM ALL_TAB_COLUMNS A
LEFT JOIN ALL_COL_COMMENTS B ON A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN ALL_TAB_COMMENTS C ON C.TABLE_NAME = A.TABLE_NAME
WHERE A.TABLE_NAME in('MT_PART')
ORDER BY A.TABLE_NAME, A.COLUMN_ID;

 

 

/*取万*/

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'
);