MySQL常用查询Columns和Views
分享一下工作中常见的用查mysql脚本，此次分享的用查内容如下：
ColumnsViews
一、Columns
1. 列出 MySQL 数据库中的用查表列 复制select tab.table_schema as database_schema,
tab.table_name as table_name,
col.ordinal_position as column_id,
col.column_name as column_name,
col.data_type as data_type,
case when col.numeric_precision is not null then col.numeric_precision else col.character_maximum_length end as max_length,
case when col.datetime_precision is not null then col.datetime_precision when col.numeric_scale is not null then col.numeric_scale else 0 end as precisionfrom information_schema.tables as tab inner join information_schema.columns as col on col.table_schema = tab.table_schema and col.table_name = tab.table_namewhere tab.table_type = BASE TABLE and tab.table_schema not in (information_schema,mysql,
performance_schema,sys)
-- uncomment line below for current database only -- and tab.table_schema = database()
-- uncomment line below and provide specific database name -- and tab.table_schema = your_database_nameorder by tab.table_name,
col.ordinal_position;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.注意：要查看特定数据库中的列，请取消注释上述子句之一。用查
说明：
schema_name - 数据库名称table_name - 表名column_id - 表列 id，用查每个表从 1 开始column_name - 列的用查名称data_type - 列数据类型max_length - 数据类型最大长度precision- 数据类型精度2. 列出 MySQL 数据库中特定表中的所有列 复制select ordinal_position as column_id,
column_name as column_name,
data_type as data_type,
case when numeric_precision is not null then numeric_precision else character_maximum_length end as max_length,
case when datetime_precision is not null then datetime_precision when numeric_scale is not null then numeric_scale else 0 end as data_precision,
is_nullable,
column_defaultfrom information_schema.columnswhere table_name = table name -- put table name here-- and table_schema = schema name -- put schema name hereorder by ordinal_position;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.说明：
column_id - 表中的列位置，从 1 开始column_name - 表中列的用查名称data_type - 列数据类型max_length - 数据类型最大长度data_precision - 数据类型精度is_nullable - 如果列可以为空，则为 YES，用查否则为 NOcolumn_default - 列的用查默认表达式
说明：
database_name - 数据库（模式）名称table_name - 表名column_name - 列名data_type - 数据列的用查类型包含精度- 数字类型的精度或日期时间类型的小数位数numeric_scale - 数字数据类型的比例char_length - 最大字符长度column_default - 列的默认值PK - 指示列是否为主键FK - 指示列是否为外键UQ - 指示列是否必须在表中具有唯一值is_nullable - 指示列是否可以为空 
4. 列出 MySQL 数据库中所有计算（生成）的列
复制select table_schema as database_name,
table_name,
column_name,
data_type,
generation_expressionfrom information_schema.columnswhere length(generation_expression) > 0 and table_schema not in (information_schema, sys,
performance_schema, mysql)
order by table_schema,
table_name,
column_name;1.2.3.4.5.6.7.8.9.10.11.12.说明：
database_name - 包含表的数据库（模式）名称table_name - 表名column_name - 列的名称data_type - 列的数据类型generation_expression - 计算公式
二、Views
1. 列出 MySQL 数据库中的用查视图 复制select table_schema as database_name,
table_name as view_namefrom information_schema.viewswhere table_schema not in (sys,information_schema,
mysql, performance_schema)
-- and table_schema = database_name -- put your database name hereorder by table_schema,
table_name;1.2.3.4.5.6.7.8.说明：
database_name - 包含视图的数据库（模式）的名称view_name - 视图名称
,
vw.table_name as view_name,
vw.view_definition as definition,
tb.table_comment as descriptionfrom information_schema.views as vw inner join information_schema.tables as tb on tb.table_name = vw.table_name-- where vw.table_schema = your database nameorder by database_name, view_name;1.2.3.4.5.6.7.8.9.注意：如果您需要特定数据库（模式）的IT技术网信息，请取消注释 table_schema 行并提供您的用查数据库名称。
说明：
schema_name - 视图的数据库（模式）名称view_name - 视图的名称定义- 视图的定义脚本描述- 视图的描述
,
col.table_name as view_name,
col.ordinal_position,
col.column_name,
col.data_type,
case when col.character_maximum_length is not null then col.character_maximum_length else col.numeric_precision end as max_length,
col.is_nullablefrom information_schema.columns coljoin information_schema.views vie on vie.table_schema = col.table_schema and vie.table_name = col.table_namewhere col.table_schema not in (sys,information_schema,
mysql, performance_schema)
-- and vie.table_schema = database_name -- put your database name hereorder by col.table_schema,
col.table_name,
col.ordinal_position;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.说明：
database_name - 数据库（模式）名称view_name - 视图名称column_name - 列名data_type - 列数据类型max_length- 列长度：对于字符串列，以字符为单位的最大长度；对于数值列，数值精度。is_nullable - 指示列是否允许空值的标志4. 列出 MySQL 数据库中视图使用的表查询在8.0.13 MySQL 版本下执行。
复制select vtu.view_schema as database_name,
vtu.view_name as view_name,
vtu.table_schema as referenced_database_name,
vtu.table_name as referenced_object_name,
tab.table_type as object_typefrom information_schema.view_table_usage vtujoin information_schema.tables tab on vtu.table_schema = tab.table_schema and vtu.table_name = tab.table_namewhere view_schema not in (sys,information_schema,
mysql, performance_schema)
-- and tab.table_schema = database_name -- put your database name hereorder by vtu.view_schema,
vtu.view_name;1.2.3.4.5.6.7.8.9.10.11.12.13.说明：
database_name - 查看数据库（模式）名称view_name - 视图名称referenced_database_name - 视图引用的表数据库名称referenced_object_name - 视图引用的表名object_type- 引用对象的源码下载类型：BASE TABLE；VIEW 
本文地址：http://www.bhae.cn/html/295d7999625.html
版权声明
本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。