MySQL查询某个数据库某个表的字段

发布于 2021-03-13 20:25:13 分类:技术笔记 阅读(99)  

 1、查看字段详细信息

-- 查看详细信息SELECT
  COLUMN_NAME "字段名称",
  COLUMN_TYPE "字段类型长度",
  IF(EXTRA="auto_increment",CONCAT(COLUMN_KEY,"(", IF(EXTRA="auto_increment","自增长",EXTRA),")"),COLUMN_KEY) "主外键",
  IS_NULLABLE "空标识",
  COLUMN_COMMENT "字段说明"FROM
    information_schema. COLUMNS
-- 数据库名:jn_power 表名 rpt_cap_hour_ammeter_201810
WHERE TABLE_SCHEMA = 'jn_power' AND TABLE_NAME = 'rpt_cap_hour_ammeter_201810'

结果如下: 

 

 

2、查字段

 

SELECT
    CONCAT(COLUMN_NAME,"," )  FROM
    information_schema.COLUMNSWHERE
    TABLE_SCHEMA = 'jn_power'AND TABLE_NAME = 'rpt_cap_ammeter_2018';

 

3、查询字段个数

SELECT COUNT(*)  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='jn_power' AND table_name='rpt_cap_ammeter_2018'

4、查某个字段所在行数

SET @mytemp = 0;SELECT * FROM (    SELECT (@mytemp:=@mytemp+1) AS newid,t.COLUMN_NAME FROM 
        (            SELECT
                 COLUMN_NAME                
            FROM
                information_schema.COLUMNS            WHERE
                TABLE_SCHEMA = 'jn_power'
            AND TABLE_NAME = 'rpt_cap_ammeter_2018'
        )t
) tWHERE newid=(SELECT COUNT(*) newid  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='jn_power' AND table_name='rpt_cap_ammeter_2018')

 

5、处理成插入的字段

-- 一列,逗号在前SET @mytemp = 0;SELECT 
    (CASE t.newid 
            WHEN 1
            THEN CONCAT(' ',COLUMN_NAME)            ELSE CONCAT(',',COLUMN_NAME)            END 
    )COLUMN_NAME--   t.newid,t.COLUMN_NAMEFROM (    
    SELECT * FROM (        SELECT (@mytemp:=@mytemp+1) AS newid,t.COLUMN_NAME FROM 
            (                SELECT
                    COLUMN_NAME                
                FROM
                    information_schema.COLUMNS                WHERE
                    TABLE_SCHEMA = 'jn_power'
                AND TABLE_NAME = 'rpt_cap_hour_ammeter_201810'
            )t
    ) t
)t-- 用分组的方法(一行)SELECT
    COUNT(*) count_num,GROUP_CONCAT(COLUMN_NAME)        FROM
    information_schema.COLUMNSWHERE
    TABLE_SCHEMA = 'jn_power'AND TABLE_NAME = 'rpt_cap_ammeter_2018'

 

 

 6、查询某个库除了主键以外的约束

    SELECT
    TABLE_NAME '表名',
    COLUMN_NAME '字段名',
    CONSTRAINT_NAME '约束名',
    REFERENCED_TABLE_NAME '父表名',
    REFERENCED_COLUMN_NAME  '父表字段名'FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE    WHERE
    TABLE_SCHEMA = 'net_management' 
    AND CONSTRAINT_name != 'PRIMARY';

 

 

 

7、查询某个库的约束和约束类型

SELECT
    kcu.CONSTRAINT_NAME '约束名称',    LEFT(tc.CONSTRAINT_TYPE,1) '约束类型',
    kcu.TABLE_SCHEMA '子库',
  kcu.TABLE_NAME '子表',
    kcu.COLUMN_NAME '子表字段',
  kcu.REFERENCED_TABLE_NAME '父库',
    kcu.REFERENCED_TABLE_SCHEMA '父表',
    kcu.REFERENCED_COLUMN_NAME '父表字段'
    
    FROM
    information_schema.KEY_COLUMN_USAGE kcu 
LEFT JOIN     
   information_schema.`TABLE_CONSTRAINTS` tc  ON kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA 
    AND kcu.TABLE_NAME = tc.TABLE_NAME    AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAMEWHERE
    kcu.TABLE_SCHEMA = 'zx_public'  

-- AND kcu.CONSTRAINT_NAME!='PRIMARY'ORDER BY kcu.TABLE_SCHEMA,kcu.TABLE_NAME,tc.CONSTRAINT_TYPE;--     AND kcu.TABLE_NAME = 'res_site'; 


SELECT table_name,count(*)  FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='new_qixiang_manage' group by table_name order by table_name asc;