文章目录
- 前言
- 一、MySQL
- 1、获取某个表的列信息
- 2、获取库的列表
- 3、通过子表获取表关系
- 4、通过主表获取表关系
- 5、查询某个库中的所有表
- 6、获取统计信息(主键、索引等)
- 7、获取键信息(主键、唯一键等)
- 8、获取某个表的表结构信息
- 9、获取某个表的建表语句
- 10、表重命名DDL
- 11、修改表的元信息DDL
- 12、删除表的某一列DDL
- 13、获取某个表占用空间和数据条数
- 14、修改某一列的属性DDL
- 15、在表中添加一列DDL
- 16、建表DDL
- 二、Clickhouse(单机)
- 1、获取所有数据库
- 2、获取某个库的建库语句
- 3、获取某个表占用的空间和数据行数
- 4、获取某个库中所有表
- 5、获取某个表的属性
- 6、获取某个表的列信息
- 7、修改或为某一列添加注释DDL
- 8、修改或为某一个表添加注释DDL
- 9、修改或为某一个表添加order by属性DDL
- 10、修改或为某一个表添加sample by属性DDL
- 11、删除列DDL
- 12、修改列类型DDL
- 13、删除列的默认值DDL
- 14、修改或设置列的默认值DDL
- 15、删除列的注释DDL
- 16、重命名表DDL
- 17、添加列DLL
- 18、建表DLL
- 三、Oracle
- 1、获取当前用户的表信息
- 2、获取某个表的信息
- 3、获取某个表的列信息
- 4、获取表的主键
- 5、删除表的某一列DDL
- 6、重命名表DDL
- 7、获取表的存储空间和行数
- 8、获取表的键
- 9、获取表的外键
- 10、通过主表获取子表的外键健名
- 11、为表添加列DDL
- 12、为列添加注释DDL
- 13、为表添加注释DDL
- 14、创建表DDL
- 15、修改列属性DDL
- 四、Postgresql
- 1、获取所有数据库
- 2、获取某个库中的表信息
- 3、获取某个表的列信息
- 4、获取某个表的信息
- 5、删除表的列DDL
- 6、修改列的类型DDL
- 7、修改某一列不为空DDL
- 8、修改某一列可以为空DDL
- 9、为列修改或添加默认值DDL
- 10、删除列的默认值DDL
- 11、查询注释
- 12、表重命名DDL
- 13、查询当前库的占用空间
- 14、查询当前库各个表的占用空间
- 15、查询当前库各个表的条数
- 16、为表添加列DDL
- 17、为列添加注释DDL
- 18、为表添加注释DDL
- 19、创建表DDL
- 20、为列添加默认值DDL
- 五、Sqlite
- 1、查询表的元数据
- 2、获取列信息
- 3、删除表的某一列DDL
- 4、表重命名DDL
- 5、添加列DDL
- 6、创建表DDL
- 总结
前言
在开发过程中操作各类数据库需要获取其元数据,比如获取表结构,表间关系等,下面分别记录各类数据库的获取方式。包括:Myql、Oracle、Postgresql、Sqlite、Clickhouse等
MySQL_15">一、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_230">二、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}
是排序方式
总结
其他数据库的也会持续更新