分享一下工作中常见的用查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 'precision'
from 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_name
where 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_name'
order by tab.table_name,
col.ordinal_position;
注意:要查看特定数据库中的列,请取消注释上述子句之一。用查
说明:
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_default
from information_schema.columns
where table_name = 'table name' -- put table name here
-- and table_schema = 'schema name' -- put schema name here
order by ordinal_position;
说明:
说明:
4. 列出 MySQL 数据库中所有计算(生成)的列
select table_schema as database_name,
table_name,
column_name,
data_type,
generation_expression
from information_schema.columns
where length(generation_expression) > 0
and table_schema not in ('information_schema', 'sys',
'performance_schema', 'mysql')
order by table_schema,
table_name,
column_name;
说明:
select table_schema as database_name,
table_name as view_name
from information_schema.views
where table_schema not in ('sys','information_schema',
'mysql', 'performance_schema')
-- and table_schema = 'database_name' -- put your database name here
order by table_schema,
table_name;
说明:
select vw.table_schema as database_name,
vw.table_name as view_name,
vw.view_definition as definition,
tb.table_comment as description
from 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 name'
order by database_name, view_name;
注意:如果您需要特定数据库(模式)的信息,请取消注释 table_schema 行并提供您的数据库名称。
说明:
select col.table_schema as database_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_nullable
from information_schema.columns col
join information_schema.views vie on vie.table_schema = col.table_schema
and vie.table_name = col.table_name
where col.table_schema not in ('sys','information_schema',
'mysql', 'performance_schema')
-- and vie.table_schema = 'database_name' -- put your database name here
order by col.table_schema,
col.table_name,
col.ordinal_position;
说明:
查询在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_type
from information_schema.view_table_usage vtu
join information_schema.tables tab on vtu.table_schema = tab.table_schema
and vtu.table_name = tab.table_name
where view_schema not in ('sys','information_schema',
'mysql', 'performance_schema')
-- and tab.table_schema = 'database_name' -- put your database name here
order by vtu.view_schema,
vtu.view_name;
说明:
责任编辑:赵宁宁 来源: 今日头条 查询MySQL数据库(责任编辑:焦点)
11.89万元!吉利ICON主角开启预订 可享2.5万元礼包 -
smart精灵#3到店实拍图曝光 定位跨界SUV 预计年底上市 -
三层电路贴在皮肤上,延展度 800%!这款「电子纹身」还能控制机械手
长安Lumin用户突破10万!推出至高5000元限时补贴 -