mysql-- 查询库和表相关信息

mysql 查询库和表相关信息

1、查询库 schema

select database();

2、查询库所有表

show tables;

3、查询表创建信息

SELECT
	table_schema,
	table_name,
	table_comment,
	table_type,
	table_collation,
	create_time 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'schema' 
	AND table_type = 'BASE TABLE' 
ORDER BY
	create_time ASC;

4、查询表结构信息

show create table tableName;

5、查询表数据

select * from tableName;

6、查询表列信息

SELECT
	* 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'schema' 
	AND table_name = 'tableName' 
ORDER BY
	ordinal_position;

7、查询表主键

SELECT
	column_name 
FROM
	INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` 
WHERE
	CONSTRAINT_SCHEMA = 'schema' 
	AND table_name = 'tableName' 
	AND constraint_name = 'PRIMARY' 
ORDER BY
	ORDINAL_POSITION;   

8、查询表所有索引

 SELECT
 	* 
 FROM
 	INFORMATION_SCHEMA.STATISTICS 
 WHERE
 	TABLE_SCHEMA = 'schema';
 
 AND TABLE_NAME = 'tableName';

9、查询表索引，拼接删除索引语句

SELECT
	CONCAT( 'ALTER TABLE ', i.TABLE_NAME, ' DROP INDEX ', i.INDEX_NAME, ' ;' ) 
FROM
	INFORMATION_SCHEMA.STATISTICS i 
WHERE
	TABLE_SCHEMA = 'schema' 
 	AND TABLE_NAME = 'tableName';
	AND i.INDEX_NAME <> 'PRIMARY';

10、 添加字段

多个字段可以逗号,分开

ALTER TABLE tableName
ADD COLUMN  column1 varchar(2) NULL DEFAULT 1  COMMENT 'column1' ,
ADD COLUMN  column2 varchar(255) NULL COMMENT 'column2';

11、修改字段

多个字段可以逗号,分开

ALTER TABLE tableName
MODIFY COLUMN column1 int(10) NOT NULL  auto_increment COMMENT 'column1' ,
MODIFY COLUMN column2 varchar(255) NULL DEFAULT NULL  COMMENT 'column2' ,
MODIFY COLUMN column3 int(10) NOT NULL DEFAULT 200  COMMENT 'column3' ;

12、删除字段

多个字段可以逗号,分开

ALTER TABLE tableName 
DROP COLUMN column1,
DROP COLUMN column2,
DROP COLUMN column3;

13、添加索引

13.1、唯一索引

ALTER TABLE `tableName` ADD UNIQUE KEY `indexName` (`column`) COMMENT 'index comment' ;

13.2、普通索引

ALTER TABLE `tableName` ADD INDEX `indexName1` (`column1`) ;
ALTER TABLE `tableName` ADD INDEX `indexName2` (`column2`) ;
ALTER TABLE `tableName` ADD INDEX `indexName3` (`column3`,`column4`) ;

14、添加主键

 ALTER TABLE tableName ADD PRIMARY KEY ( 'column' );