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