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

 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.COLUMNS
WHERE
    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
) t

WHERE 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_NAME
FROM (
    
    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.COLUMNS
WHERE
    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_NAME
WHERE
    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';