mysql 查询指定数据库所有表, 指定表所有列, 指定列所有表 所有外键及索引, 以及索引的创建和删除

时间：2021-09-16 13:50:09

查询指定 数据库 中所有 表 (指定数据库的,所有表)

// 可以把 TABLE_NAME 换成 * 号, 查看更丰富的信息
SELECT 
 TABLE_NAME 
FROM
 information_schema. TABLES
WHERE
 table_schema = '数据库名'

 //where 后面还有更多的条件选择,比如只查出以 oauth_表开头的表
AND TABLE_NAME LIKE 'oauth_%';

查询指定 数据库 中,指定 表 的所有 字段 (指定表的,所有列)

SELECT
 COLUMN_NAME
FROM
 information_schema.COLUMNS
WHERE
 table_schema = '数据库名'
AND table_name = '表名'

查询指定 数据库 所有表中, 含有 某个字段 的表 (指定列的,所有表)

SELECT
 table_name
FROM
 information_schema. COLUMNS
WHERE
 table_schema = '数据库名'
AND table_name in (
SELECT
 TABLE_NAME
FROM
 information_schema. TABLES
WHERE
 table_schema = '数据库名'
) AND COLUMN_NAME = '字段名';

查询指定 数据库 中所有 表 的 外键 

SELECT
 *
FROM
 information_schema.TABLE_CONSTRAINTS
WHERE
 //注意指定约束类型, 是为了过滤掉 "PRIMARY KEY, UNIQUE"这两个索引
 CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_SCHEMA = '数据库名'

 //如果只查询外键是以 fk_开头命名的 (如果你的外键命名以 fk_开头的话)
AND CONSTRAINT_NAME LIKE 'fk_%';

查询指定 数据库 中所有 表 的 索引 (主键,唯一,普通等, 除了外键) 

SELECT
    *
FROM
    INFORMATION_SCHEMA.STATISTICS
WHERE
    TABLE_SCHEMA = '数据库名';

//对常用的几个字段进行说明
//TABLE_SCHEMA, 表所在库
//TABLE_NAME, 表名

//NON_UNIQUE, 该索引能否包含重复, 1代表可以, 0代表不可以, 注意PRIMARY ,UNIQUE 为0, 
//用INDEX_NAME 字段将两者区分

//INDEX_NAME, 索引名
//COLUMN_NAME 表字段名,(表示此字段上加了索引)

如果只想查出 某个表 的 索引 

//以下两个 sql 是等价的,
//注意 表名和数据库名 顺序 以及 是否加引号 ''
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_name = '表名'
AND table_schema = '数据库名'

SHOW INDEX
FROM 表名
FROM 数据库名

创建索引

1. ALTER TABLE
//唯一索引 (文本字段不能添加索引)
ALTER TABLE `表名` ADD UNIQUE `索引名` (`字段`, `字段`);
//普通索引 (文本字段不能添加索引)
ALTER TABLE `表名` ADD INDEX `索引名` (`字段`, `字段`);
//主键索引
ALTER TABLE `表名` ADD PRIMARY KEY (`字段`);

2. CREATE INDEX
CREATE INDEX `索引名` ON `表名` (`字段`, `字段`)

CREATE UNIQUE INDEX `索引名` ON `表名` (`字段`, `字段`)

删除索引

//删除普通索引或唯一索引
1. DROP INDEX index_name ON talbe_name
2. ALTER TABLE table_name DROP INDEX index_name

注意: 按照一般情况, 主键我们一般都是设置为自增的, 所以删除主键索引前, 要先删掉自增

//删除自增
ALTER TABLE `表名` CHANGE `列名` `列名` int(10) 
//删除主键索引
3. ALTER TABLE table_name DROP PRIMARY KEY
标签：

相关文章