mysql 库表整体相关查询

select table_schema,table_name from information_schema.columns where column_name = '字段名';
  • 查询某张表有几条记录
select count(*) FROM gasmis_shangxian.his_fuel_record;
  • 查询某几张表一个有多少条记录
SELECT SUM(a) from(SELECT COUNT(*) a FROM gasmis_shangxian.his_fuel_record
UNION
SELECT COUNT(*)a FROM gasmis_shangxian.his_account_journal
UNION
SELECT COUNT(*) a FROM gasmis_shangxian.his_oilsys_fuel_record) as aa
  • 查询某个字段属于哪个库
- > select table_schema,table_name from information_schema.columns where column_name = '字段名'

  • 批量更新某个主键,使其值增1,直接update会主键冲突,使用order by (神奇的order by 用法)
UPDATE normalflow_list
SET flow_seqno = flow_seqno + 1
WHERE
	flow_id = '010101'
AND flow_seqno > 2
ORDER BY
	flow_seqno DESC;
  • 获取某一列的最大值

    select max(volumu) from table_name
    
    select max(flow_seqno) from normalflow_list where flow_id = '010101';
    

posted on 2019-08-10 18:30  slowlyChange  阅读(132)  评论(0编辑  收藏  举报

导航