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';
赞 (0)