[大数据]各种数据库元数据管理的方式

前言

在开发过程中操作各类数据库需要获取其元数据，比如获取表结构，表间关系等，下面分别记录各类数据库的获取方式。包括：Myql、Oracle、Postgresql、Sqlite、Clickhouse等

一、MySQL

1、获取某个表的列信息

SELECT
	*
FROM
	information_schema.COLUMNS
WHERE
	TABLE_SCHEMA = #{dbName} AND TABLE_NAME = #{tableName} ORDER BY ordinal_position

其中#{dbName}是库名，#{tableName}是表名

2、获取库的列表

SHOW DATABASES

3、通过子表获取表关系

SELECT
	*
FROM
	information_schema.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = #{dbName} AND TABLE_NAME = #{tableName} AND 
	REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
	CONSTRAINT_NAME,
	ORDINAL_POSITION

其中#{dbName}是库名，#{tableName}是表名

4、通过主表获取表关系

SELECT
	*
FROM
	information_schema.KEY_COLUMN_USAGE
WHERE
	TABLE_SCHEMA = #{dbName} AND REFERENCED_TABLE_NAME = #{tableName} AND 
	TABLE_NAME IS NOT NULL
ORDER BY
	CONSTRAINT_NAME,
	ORDINAL_POSITION

其中#{dbName}是库名，#{tableName}是表名

5、查询某个库中的所有表

SELECT
	*
FROM
	information_schema.TABLES
WHERE
	TABLE_SCHEMA = #{dbName}

其中#{dbName}是库名

6、获取统计信息（主键、索引等）

SELECT
	*
FROM
	information_schema.STATISTICS
WHERE
	TABLE_SCHEMA = #{dbName} and TABLE_NAME = #{tableName} order by SEQ_IN_INDEX

其中#{dbName}是库名，#{tableName}是表名

7、获取键信息(主键、唯一键等)

SELECT
	*
FROM
	information_schema.TABLE_CONSTRAINTS
WHERE
	TABLE_SCHEMA = #{dbName} AND TABLE_NAME = #{tableName}

其中#{dbName}是库名，#{tableName}是表名

8、获取某个表的表结构信息

SELECT
	*
FROM
	information_schema.TABLES
WHERE
	TABLE_SCHEMA = #{dbName} AND TABLE_NAME = #{tableName}

其中#{dbName}是库名，#{tableName}是表名

9、获取某个表的建表语句

SHOW CREATE TABLE `${dbName}`.`${tableName}`

其中${dbName}是库名，${tableName}是表名

10、表重命名DDL

ALTER TABLE `${dbName}`.`${oriTableName}` RENAME TO `${dbName}`.`${tableName}`

其中${dbName}是库名，${oriTableName}是原来的名，${tableName}是修改后的名

11、修改表的元信息DDL

ALTER TABLE `${dbName}`.`${tableName}` DEFAULT CHARACTER
SET ${charset} DEFAULT COLLATE ${collate} COMMENT '${comment}'

其中:

• ${dbName}是库名，
• ${tableName}是表名，
• ${charset}是编码方式，
• ${collate}是排序方式，
• ${comment}是列注释。

12、删除表的某一列DDL

ALTER TABLE `${dbName}`.`${tableName}` DROP COLUMN ${columnName}

其中${dbName}是库名，${tableName}是表名，${columnName}是列名

13、获取某个表占用空间和数据条数

SELECT
	table_schema AS tableSchema,
	table_name AS tableName,
	table_rows AS rows,
	(
		TRUNCATE(data_length / 1024 / 1024, 2) + TRUNCATE(index_length / 1024 / 1024, 2)
	) AS storage
FROM
	information_schema.TABLES t
WHERE
	t.TABLE_SCHEMA = #{dbName} AND t.TABLE_NAME = #{tableName}

其中${dbName}是库名，${tableName}是表名

14、修改某一列的属性DDL

ALTER TABLE `${tableSchema}`.`${tableName}` MODIFY `${name}` ${mysqlDataTypeAndLength} CHARACTER
SET ${charset} COLLATE ${collation} NOT NULL DEFAULT ${defaultVal} COMMENT '${comment}'

其中:

• ${tableSchema}是库名，
• ${tableName}是表名，
• ${name}是列名，
• ${mysqlDataTypeAndLength}是类型，如果有长度带着长度，
• ${charset}是编码方式，
• ${collation}是排序方式，
• ${defaultVal}是默认值，
• ${comment}是列注释。

以上属性中不需要改或不需要设置的可以不带。

15、在表中添加一列DDL

ALTER TABLE `${tableSchema}`.`${tableName}` ADD COLUMN `${name}` ${mysqlDataTypeAndLength} 
CHARACTER SET ${charset} COLLATE ${collation} NOT NULL DEFAULT ${defaultVal} PRIMARY KEY 
COMMENT '${comment}' AFTER ${beforeCol}

其中：

• ${tableSchema}是库名，
• ${tableName}是表名，
• ${name}是列名，
• mysqlDataTypeAndLength是类型，如果有长度带着长度，
• ${charset}是编码方式，
• ${collation}是排序方式，
• ${defaultVal}是默认值，
• ${comment}是列注释，
• ${beforeCol}是给列排序用的，表示当前加入的列在哪个列后面。

以上属性中不需要改或不需要设置的可以不带。

16、建表DDL

CREATE TABLE `${schema}`.`${name}` (
   <#list columns as col>
   	`${col.name}` ${col.mysqlDataTypeAndLength}CHARACTER SET ${col.charset} COLLATE ${col.collation} NOT NULL DEFAULT ${col.defaultVal} PRIMARY KEY COMMENT '${col.comment}',
   </#list>
    
) ENGINE=${engine} DEFAULT CHARSET=${charset} DEFAULT COLLATE=${collation} ROW_FORMAT=DYNAMIC COMMENT '${comment}'

其中：

• ${schema}是库名，
• ${name}是表名，
• <#list columns as col> 表示是个循环，可以写多个列，
• ${col.name}是列名，
• ${col.mysqlDataTypeAndLength}是类型，如果有长度带着长度，
• ${col.charset}是编码方式，
• ${col.collation}是排序方式，
• ${col.defaultVal}是默认值，
• ${col.comment}是列注释，
• ${engine}是表引擎，如InnoDB，
• ${charset}是表的默认编码方式，
• ${collation}是表的默认排序方式，
• ${comment}是表的注释。

以上属性中不需要改或不需要设置的可以不带。

二、Clickhouse(单机)

1、获取所有数据库

SHOW DATABASES

2、获取某个库的建库语句

SHOW CREATE DATABASE ${dbName}

其中${dbName}是数据库名

3、获取某个表占用的空间和数据行数

SELECT
	DATABASE AS `tableSchema`,
	TABLE AS `tableName`,
	round(size / 1024, 2) AS `storage`,
	round(
		data_uncompressed_bytes / 1024,
		2
	) AS `oriStorage`,
	round(
		data_compressed_bytes / 1024,
		2
	) AS `compressedStorage`,
	round(compress_rate, 4) AS `compressRate`,
	rows AS `rows`,
	days AS `days`,
	formatReadableSize (avgDaySize) AS `avgDaySize`
FROM
	(
		SELECT
			DATABASE,
			TABLE,
			sum(bytes) AS size,
			sum(rows) AS rows,
			min(min_date) AS min_date,
			max(max_date) AS max_date,
			sum(data_uncompressed_bytes) AS data_uncompressed_bytes,
			sum(data_compressed_bytes) AS data_compressed_bytes,
			(
				data_compressed_bytes / data_uncompressed_bytes
			) AS compress_rate,
			max_date - min_date AS days,
			size / (max_date - min_date) AS avgDaySize
		FROM
			system.parts
		WHERE
			active
		AND DATABASE = #{dbName}
		AND TABLE = #{tableName}
		GROUP BY
			DATABASE,
			TABLE
	)

其中#{dbName}是库名，#{tableName}是表名。

4、获取某个库中所有表

SELECT
	database AS schema,
	name AS name,
	engine AS engine,
	partition_key AS partitionKey,
	sorting_key AS orderBy,
	comment AS comment,
	sampling_key AS samplingKey
FROM
	system.tables
WHERE
	database = #{dbName}

其中#{dbName}是库名

5、获取某个表的属性

SELECT
	database AS schema,
	name AS name,
	engine AS engine,
	partition_key AS partitionKey,
	sorting_key AS orderBy,
	comment AS comment,
	sampling_key AS samplingKey
FROM
	system.tables
WHERE
	database = #{dbName} AND name = #{tableName}

其中#{dbName}是库名，#{tableName}是表名。

6、获取某个表的列信息

SELECT
	table_schema AS tableSchema,
	table_name AS tableName,
	column_name AS name,
	column_default AS defaultVal,
	is_nullable AS nullable,
	data_type AS dataType,
	ordinal_position AS ordinalPosition,
	column_comment AS comment,
	character_maximum_length AS charMaxLen,
	numeric_precision AS precision,
	numeric_scale AS scale,
	datetime_precision AS datetimePrecision
FROM
	information_schema.columns
WHERE
	table_schema = #{dbName} AND table_name = #{tableName}

其中#{dbName}是库名，#{tableName}是表名。

7、修改或为某一列添加注释DDL

ALTER TABLE `${dbName}`.`${tableName}` COMMENT COLUMN `${colName}` '${comment}'

其中${dbName}是库名，${tableName}是表名，${colName}是列名，${comment}是注释。

8、修改或为某一个表添加注释DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY COMMENT '${comment}'

其中${dbName}是库名，${tableName}是表名，${comment}是注释。

9、修改或为某一个表添加order by属性DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY ORDER BY (${orderBy})

其中${dbName}是库名，${tableName}是表名，${orderBy}是order by信息。

10、修改或为某一个表添加sample by属性DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY SAMPLE BY (${sampleBy})

其中${dbName}是库名，${tableName}是表名，${sampleBy}是sample by信息。

11、删除列DDL

ALTER TABLE `${dbName}`.`${tableName}` DROP COLUMN `${colName}`

其中${dbName}是库名，${tableName}是表名，${colName}是列名。

12、修改列类型DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY COLUMN `${colName}` ${colType}"

其中${dbName}是库名，${tableName}是表名，${colName}是列名， ${colType}是列类型。

13、删除列的默认值DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY COLUMN `${colName}` REMOVE DEFAULT

其中${dbName}是库名，${tableName}是表名，${colName}是列名。

14、修改或设置列的默认值DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY COLUMN `${colName}` DEFAULT ${defaultVal}

其中${dbName}是库名，${tableName}是表名，${colName}是列名， ${defaultVal}是默认值。

15、删除列的注释DDL

ALTER TABLE `${dbName}`.`${tableName}` MODIFY COLUMN `${colName}` REMOVE COMMENT

其中${dbName}是库名，${tableName}是表名，${colName}是列名。

16、重命名表DDL

RENAME TABLE `${dbName}`.`${oriName}` TO `${dbName}`.`${newName}`

其中${dbName}是库名，${oriName}是原始表名，${newName}是修改后的表名。

17、添加列DLL

ALTER TABLE `${tableSchema}`.`${tableName}` ADD COLUMN `${name}`
 Nullable(${clickhouseDataTypeAndLength}) DEFAULT ${defaultVal} AFTER ${beforeCol}

其中：

• ${tableSchema}是库名，
• ${tableName}是表名，
• ${name}是列名，
• ${clickhouseDataTypeAndLength}是列类型，
• ${defaultVal} 是默认值，
• ${beforeCol}是为列排序用的，表示当前添加的列在beforeCol这个列之后。

以上属性中不需要的可以不设置

18、建表DLL

CREATE TABLE `${schema}`.`${name}` (
   <#list columns as col>
   	`${col.name}`${col.clickhouseDataTypeAndLength} NOT NULL DEFAULT ${col.defaultVal},
   </#list>
) ENGINE=${engine}
PRIMARY KEY (${primaryKeys})
PARTITION BY ${partitionKey}
ORDER BY (${orderBy})
SAMPLE BY (${samplingKey})
COMMENT '${comment}'

其中：

• ${schema}是库名，
• ${name}是表名，
• ${col.name}是列名，
• ${col.clickhouseDataTypeAndLength}是列类型，
• ${col.defaultVal} 是默认值，
• ${engine}是表引擎，
• ${primaryKeys}是主键，
• ${partitionKey}是分区，
• ${orderBy}是order by,
• ${samplingKey}是采样,
• ${comment}是表注释。

以上属性中不需要的可以不设置

三、Oracle

1、获取当前用户的表信息

SELECT
	t1.TABLE_NAME AS "name",
	t1.TABLESPACE_NAME AS tablespaceName,
	t2.COMMENTS AS "comment",
	'${dbName}' AS "schema"
FROM
	USER_TABLES t1
LEFT JOIN USER_TAB_COMMENTS t2 ON t1.table_name = t2.table_name

2、获取某个表的信息

SELECT
	t1.TABLE_NAME AS "name",
	t1.TABLESPACE_NAME AS tablespaceName,
	t2.COMMENTS AS "comment",
	'${dbName}' AS "schema"
FROM
	USER_TABLES t1
LEFT JOIN USER_TAB_COMMENTS t2 ON t1.table_name = t2.table_name
WHERE
	t1.table_name = #{tableName}"

其中#{tableName}是表名

3、获取某个表的列信息

SELECT
	A.table_name AS tableName,
	A.column_name AS "name",
	A.data_default AS defaultVal,
	A.nullable AS "nullable",
	A.data_type AS dataType,
	DECODE(
		A.char_length,
		0,
		A.data_length,
		A.char_length
	) AS charMaxLen,
	A.COLUMN_ID AS ordinalPosition,
	B.comments AS "comments",
	A.data_precision AS "precision",
	A.data_scale AS "scale"
FROM
	user_tab_columns A
LEFT JOIN user_col_comments B ON a.COLUMN_NAME = b.column_name
AND A.Table_Name = B.Table_Name
WHERE
	A.Table_Name = #{tableName} 

其中#{tableName}是表名

4、获取表的主键

SELECT
	cu.COLUMN_NAME
FROM
	user_cons_columns cu,
	user_constraints au
WHERE
	cu.constraint_name = au.constraint_name
AND au.constraint_type = 'P'
AND au.table_name = #{tableName}

其中#{tableName}是表名

5、删除表的某一列DDL

ALTER TABLE "${tableName}" DROP COLUMN "${columnName}"

其中${tableName}是表名，${columnName}是列名

6、重命名表DDL

ALTER TABLE "${oriName}" rename to "${newName}"

其中${oriName}是原始表名，${newName}是修改后的表名

7、获取表的存储空间和行数

SELECT
	t2.*, t3.storage
FROM
	(
		SELECT
			t1.TABLE_NAME AS tableName,
			t1.NUM_ROWS AS "rows"
		FROM
			USER_TABLES t1
		WHERE
			t1.TABLE_NAME = #{tableName} ) t2
		LEFT JOIN (
			SELECT
				segment_name AS tableName,
				ROUND(BYTES / 1024 / 1024, 2) AS STORAGE
			FROM
				user_segments
			WHERE
				segment_name = upper(
					#{tableName} )) t3 ON t2.tableName = t3.tableName

其中${tableName}是表名

8、获取表的键

SELECT
	a.TABLE_NAME,
	a.CONSTRAINT_NAME,
	a.CONSTRAINT_TYPE,
	b.COLUMN_NAME,
	b.POSITION
FROM
	user_constraints a
LEFT JOIN user_cons_columns b ON a.constraint_name = b.constraint_name
WHERE
	a.TABLE_NAME = #{tableName} ORDER BY a.CONSTRAINT_NAME

其中#{tableName}是表名

9、获取表的外键

SELECT
	a.CONSTRAINT_NAME,
	a.TABLE_NAME AS childTableName,
	a.R_CONSTRAINT_NAME,
	b.COLUMN_NAME,
	b.POSITION
FROM
	user_constraints a
LEFT JOIN user_cons_columns b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE
	a.CONSTRAINT_TYPE = 'R'
AND a.TABLE_NAME = #{tableName}

其中#{tableName}是表名

10、通过主表获取子表的外键健名

SELECT
	b.*
FROM
	user_constraints b
WHERE
	b.CONSTRAINT_TYPE = 'R'
AND b.R_CONSTRAINT_NAME IN (
	SELECT
		a.CONSTRAINT_NAME
	FROM
		user_constraints a
	WHERE
		a.TABLE_NAME = #{tableName})

其中#{tableName}是表名

11、为表添加列DDL

ALTER TABLE "${tableName}" ADD "${name}" ${oracleDataTypeAndLength} 
DEFAULT ${defaultVal} NOT NULL PRIMARY KEY

其中：

• ${tableName是表名，
• ${name是列名，
• ${oracleDataTypeAndLength是列类型，
• ${defaultVal}是默认值。

12、为列添加注释DDL

COMMENT ON COLUMN "${tableName}"."${name}" IS '${comment}'

其中${tableName}是表名，.${name}是列名， ${comment}是注释信息

13、为表添加注释DDL

COMMENT ON TABLE "${name}" IS '${comment}'

其中${name}是表名， ${comment}是注释信息

14、创建表DDL

CREATE TABLE "${name}" (
   <#list columns as col>
   	"${col.name}" ${col.oracleDataTypeAndLength} DEFAULT ${col.defaultVal}> NOT NULL PRIMARY KEY,
   </#list>
)

其中：

• ${name}是表名，
• list columns as col表示一个循环，多个列
• ${col.name}是列名，
• ${col.oracleDataTypeAndLength}是列类型,
• ${col.defaultVal}是默认值。

以上属性不需要的属性可以不写

15、修改列属性DDL

ALTER TABLE "${tableName}" MODIFY "${name}" ${oracleDataTypeAndLength} DEFAULT ${defaultVal} NOT NULL

其中：

• ${tableName}是表名，
• ${name}是列名，
• ${col.oracleDataTypeAndLength}是列类型,
• ${col.defaultVal}是默认值。

以上属性不需要的属性可以不写

四、Postgresql

1、获取所有数据库

SELECT datname AS "name" FROM pg_database WHERE datistemplate = false

2、获取某个库中的表信息

SELECT
        t1.*,
        t2.COMMENT,
        '${dbName}' AS "schema"
        FROM
        ( SELECT tablename AS "name", tableowner AS "owner", "tablespace" AS tablespaceName FROM pg_tables WHERE
        tablename NOT LIKE'pg%' AND tablename NOT LIKE'sql_%' ) t1
        LEFT JOIN ( SELECT relname AS tablename, CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS
        "comment" FROM pg_class C ) t2 ON t1.NAME = t2.tablename

其中${dbName}是库名

3、获取某个表的列信息

SELECT
        '${dbName}' AS tableSchema,
        t1.TABLE_NAME AS tableName,
        t1.COLUMN_NAME AS "name",
        t1.column_default AS defaultVal,
        t1.is_nullable AS "nullable",
        t1.data_type AS "dataType",
        t1.ordinal_position AS ordinalPosition,
        t2.COMMENT AS "comment",
        t1.character_maximum_length AS charMaxLen,
        t1.numeric_scale AS "scale",
        t1.datetime_precision AS datetimePrecision,
        t1.numeric_precision AS "precision",
        t2.TYPE AS type2,
        b.pk_name
        FROM
        information_schema.COLUMNS t1
        LEFT JOIN (
        SELECT
        col_description ( A.attrelid, A.attnum ) AS "comment",
        format_type ( A.atttypid, A.atttypmod ) AS TYPE,
        A.attname AS "name",
        A.attnotnull AS NOTNULL,
        C.relname AS tableName,
        A.attlen AS charMaxLen
        FROM
        pg_class AS C,
        pg_attribute AS A
        WHERE
        C.relname = #{ tableName }
        AND A.attrelid = C.oid
        AND A.attnum > 0
        ) t2 ON t1.COLUMN_NAME = t2.
        NAME LEFT JOIN (
        SELECT
        pg_attr.attname AS colname,
        pg_constraint.conname AS pk_name
        FROM
        pg_constraint
        INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
        INNER JOIN pg_attribute pg_attr ON pg_attr.attrelid = pg_class.oid
        AND pg_attr.attnum = pg_constraint.conkey [ 1 ]
        INNER JOIN pg_type ON pg_type.oid = pg_attr.atttypid
        WHERE
        pg_class.relname = #{ tableName }
        AND pg_constraint.contype = 'p'
        ) b ON b.colname = t1.COLUMN_NAME
        WHERE
        t1.TABLE_NAME = #{ tableName } AND t1.COLUMN_NAME != 'dmo_id'
        ORDER BY
        t1.ordinal_position

其中${dbName}是库名，#{tableName}是表名

4、获取某个表的信息

        SELECT
        t1.*,
        t2.COMMENT,
        '${dbName}' AS "schema"
        FROM
        ( SELECT tablename AS "name", tableowner AS "owner", "tablespace" AS tablespaceName FROM pg_tables WHERE tablename NOT LIKE'pg%' AND tablename NOT LIKE'sql_%' ) t1
        LEFT JOIN ( SELECT relname AS tablename, CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "comment" FROM pg_class C ) t2 ON t1.NAME = t2.tablename
        WHERE
        t2.tablename = #{ tableName}

其中${dbName}是库名，#{tableName}是表名

5、删除表的列DDL

ALTER TABLE ${tableName} DROP COLUMN ${columnName} CASCADE

其中${tableName}是表名，${columnName}是列名

6、修改列的类型DDL

ALTER TABLE ${tableName} ALTER COLUMN ${columnName} type ${type}

其中${tableName}是表名，${columnName}是列名，${type}是类型。

7、修改某一列不为空DDL

ALTER TABLE ${tableName} ALTER COLUMN ${columnName} SET NOT NULL

其中${tableName}是表名，${columnName}是列名。

8、修改某一列可以为空DDL

ALTER TABLE ${tableName} ALTER COLUMN ${columnName} DROP NOT NULL

其中${tableName}是表名，${columnName}是列名。

9、为列修改或添加默认值DDL

ALTER TABLE ${tableName} ALTER COLUMN ${columnName} SET DEFAULT ${defaultVal}

其中${tableName}是表名，${columnName}是列名，${defaultVal}是默认值。

10、删除列的默认值DDL

ALTER TABLE ${tableName} ALTER COLUMN ${columnName} DROP DEFAULT

其中${tableName}是表名，${columnName}是列名。

11、查询注释

        WITH tmp_tab AS (
        SELECT
        pc.oid AS ooid,
        pn.nspname,
        pc.*
        FROM
        pg_class pc
        LEFT OUTER JOIN pg_namespace pn ON pc.relnamespace = pn.oid
        WHERE
        1 = 1
        AND pc.relkind IN ( 'r', 'v', 'm', 'f', 'p' )
        AND pn.nspname NOT IN ( 'pg_catalog', 'information_schema' )
        AND pn.nspname NOT LIKE'pg_toast%'
        ORDER BY
        pc.relname
        ),
        tmp_desc AS ( SELECT pd.* FROM pg_description pd WHERE 1 = 1 AND pd.objsubid = 0 ) SELECT
        t0.*,
        CASE
        WHEN t0.relkind IN ( 'r', 'p' ) THEN
        'comment on table ' || t0.nspname || '.' || t0.relname || ' is ''' || COALESCE ( t0.description, '' ) || ''';'
        WHEN t0.relkind = 'v' THEN
        'comment on view ' || t0.nspname || '.' || t0.relname || ' is ''' || COALESCE ( t0.description, '' ) || ''';'
        END AS table_description
        FROM
        (
        SELECT
        tab.nspname,
        tab.relname,
        tab.relkind,
        de.description
        FROM
        tmp_tab tab
        LEFT OUTER JOIN tmp_desc de ON tab.ooid = de.objoid
        WHERE
        1 = 1
        ) t0
        WHERE
        1 = 1
        ORDER BY
        t0.nspname,
        t0.relname

12、表重命名DDL

ALTER TABLE ${oriName} RENAME TO ${newName}

其中${oriName}是原始表名， ${newName}是修改后的表名

13、查询当前库的占用空间

        SELECT SUM
        ( T.SIZE )
        FROM
        (
        SELECT
        table_schema || '.' || TABLE_NAME AS table_full_name,
        pg_total_relation_size ( '"' || table_schema || '"."' || TABLE_NAME || '"' ) AS SIZE
        FROM
        information_schema.tables
        ORDER BY
        pg_total_relation_size ( '"' || table_schema || '"."' || TABLE_NAME || '"' ) DESC
        ) T

14、查询当前库各个表的占用空间

        SELECT
        table_name, table_schema || '.' || TABLE_NAME AS table_full_name,
        pg_total_relation_size ( '"' || table_schema || '"."' || TABLE_NAME || '"' ) AS SIZE
        FROM
        information_schema.tables
        ORDER BY
        pg_total_relation_size ( '"' || table_schema || '"."' || TABLE_NAME || '"' ) DESC

15、查询当前库各个表的条数

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC

16、为表添加列DDL

ALTER TABLE "${tableName}" ADD "${name}" ${postgresqlDataTypeAndLength} PRIMARY KEY NOT NULL

其中：

• ${tableName}是表名
• ${name}是列名
• ${postgresqlDataTypeAndLength}是列类型

17、为列添加注释DDL

COMMENT ON COLUMN "${tableName}"."${name}" IS ${comment}

其中：

• ${tableName}是表名
• ${name}是列名
• ${comment}是列注释

18、为表添加注释DDL

COMMENT ON TABLE "${name}" IS ${comment}

其中：

• ${name}是表名
• ${comment}是列注释

19、创建表DDL

CREATE TABLE "${name}" (
   <#list columns as col>
   	"${col.name}" ${col.postgresqlDataTypeAndLength} PRIMARY KEY NOT NULL,
   </#list>
)

其中：

• ${name}是表名
• list columns as col表示一个循环，有多个列
• ${col.name}是列名
• ${col.postgresqlDataTypeAndLength}是列的类型

20、为列添加默认值DDL

ALTER TABLE "${tableName}" ALTER COLUMN "${name}" SET DEFAULT ${defaultVal}

其中${tableName}是表名，${name}是列名，${defaultVal}是默认值

五、Sqlite

1、查询表的元数据

SELECT * FROM sqlite_master WHERE type='table' ORDER BY name

2、获取列信息

pragma table_info(${tableName})

其中${tableName}是表名

3、删除表的某一列DDL

ALTER TABLE "${tableName}" DROP COLUMN "${columnName}"

其中${tableName}是表名，${columnName}是列名

4、表重命名DDL

ALTER TABLE "${oriTableName}" RENAME TO "${tableName}"

其中${oriTableName}是原始表名，${tableName}是修改后的表名

5、添加列DDL

ALTER TABLE "${tableName}" ADD COLUMN "${name}" ${sqliteColumnType} NOT NULL 
DEFAULT ${defaultVal} COLLATE ${collation} PRIMARY KEY

其中：

• ${tableName}是表名
• ${name}是列名
• ${sqliteColumnType}是列类型
• ${defaultVal}是默认值
• ${collation}是排序方式

6、创建表DDL

CREATE TABLE "${name}" (
   <#list columns as col>
   	"${col.name}" ${col.sqliteColumnType} NOT NULL DEFAULT ${col.defaultVal} COLLATE ${col.collation} PRIMARY KEY,
   </#list>
)

其中：

• ${name}是表名
• list columns as col表示循环，有个多个列
• ${col.name}是列名
• ${col.sqliteColumnType}是列类型
• ${col.defaultVal}是默认值
• ${col.collation}是排序方式

总结

其他数据库的也会持续更新

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖