建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL常用查詢Columns和Views
分享一下工作中常見的mysql腳本，此次分享的內(nèi)容如下：
- Columns
- Views
一、Columns
1. 列出 MySQL 數(shù)據(jù)庫中的表列
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;
注意：要查看特定數(shù)據(jù)庫中的列，請取消注釋上述子句之一。
說明：
- schema_name - 數(shù)據(jù)庫名稱
- table_name - 表名
- column_id - 表列 id，每個表從 1 開始
- column_name - 列的名稱
- data_type - 列數(shù)據(jù)類型
- max_length - 數(shù)據(jù)類型最大長度
- precision- 數(shù)據(jù)類型精度
2. 列出 MySQL 數(shù)據(jù)庫中特定表中的所有列
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;
說明：
- column_id - 表中的列位置，從 1 開始
- column_name - 表中列的名稱
- data_type - 列數(shù)據(jù)類型
- max_length - 數(shù)據(jù)類型最大長度
- data_precision - 數(shù)據(jù)類型精度
- is_nullable - 如果列可以為空，則為 YES，否則為 NO
- column_default - 列的默認(rèn)表達(dá)式
3. 列出 MySQL 數(shù)據(jù)庫中所有包含詳細(xì)信息的表列（PKs、UKs、FKs、Default、Computed 等）
說明：
- database_name - 數(shù)據(jù)庫（模式）名稱
- table_name - 表名
- column_name - 列名
- data_type - 數(shù)據(jù)列的類型包含
- 精度- 數(shù)字類型的精度或日期時間類型的小數(shù)位數(shù)
- numeric_scale - 數(shù)字?jǐn)?shù)據(jù)類型的比例
- char_length - 最大字符長度
- column_default - 列的默認(rèn)值
- PK - 指示列是否為主鍵
- FK - 指示列是否為外鍵
- UQ - 指示列是否必須在表中具有唯一值
- is_nullable - 指示列是否可以為空
? ?
4. 列出 MySQL 數(shù)據(jù)庫中所有計算（生成）的列
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;
說明：
- database_name - 包含表的數(shù)據(jù)庫（模式）名稱
- table_name - 表名
- column_name - 列的名稱
- data_type - 列的數(shù)據(jù)類型
- generation_expression - 計算公式
二、Views
1. 列出 MySQL 數(shù)據(jù)庫中的視圖
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;
說明：
- database_name - 包含視圖的數(shù)據(jù)庫（模式）的名稱
- view_name - 視圖名稱
2. 列出 MySQL 中的視圖及其定義
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;
注意：如果您需要特定數(shù)據(jù)庫（模式）的信息，請取消注釋 table_schema 行并提供您的數(shù)據(jù)庫名稱。
說明：
- schema_name - 視圖的數(shù)據(jù)庫（模式）名稱
- view_name - 視圖的名稱
- 定義- 視圖的定義腳本
- 描述- 視圖的描述
3. 列出 MySQL 數(shù)據(jù)庫中的視圖中的所有列
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;
說明：
- database_name - 數(shù)據(jù)庫（模式）名稱
- view_name - 視圖名稱
- column_name - 列名
- data_type - 列數(shù)據(jù)類型
- max_length- 列長度：對于字符串列，以字符為單位的最大長度；對于數(shù)值列，數(shù)值精度。
- is_nullable - 指示列是否允許空值的標(biāo)志
4. 列出 MySQL 數(shù)據(jù)庫中視圖使用的表
查詢在8.0.13 MySQL 版本下執(zhí)行。
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;
說明：
- database_name - 查看數(shù)據(jù)庫（模式）名稱
- view_name - 視圖名稱
- referenced_database_name - 視圖引用的表數(shù)據(jù)庫名稱
- referenced_object_name - 視圖引用的表名
- object_type- 引用對象的類型：BASE TABLE；VIEW
? ?
網(wǎng)頁名稱：MySQL常用查詢Columns和Views
URL鏈接：http://www.dlmjj.cn/article/ccegiop.html
