欧美97色伦欧美一区二区日韩,国产福利片在线观看,freexxx性欧美vide0高清,西西亚洲,日本欧美国产精品第一页久久,成人18免费软件

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息

作者：VEPHP   時(shí)間 2017-09-27

《Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息》要點(diǎn)：
本文介紹了Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息，希望對您有用。如果有疑問，可以聯(lián)系我們。

接著上篇文章《解析SQL 表結(jié)構(gòu)信息查詢 含主外鍵、自增長》里面提到了INFORMATION_SCHEMA視圖,其實(shí)到了SQL 2005微軟都主推大家使用INFORMATION_SCHEMA系統(tǒng)視圖,而不是在使用sys東東了,當(dāng)然目前還是有許多信息只能通過sys視圖來查詢.這里我們還是以查詢表結(jié)果信息為例來說明一些主要的INFORMATION_SCHEMA視圖的使用.
首先我們需要查詢列的信息,這需要用到[INFORMATION_SCHEMA].[COLUMNS]系統(tǒng)視圖來查詢數(shù)據(jù)列的信息,SQL 如下：
代碼如下:

SELECT? c.TABLE_SCHEMA ,
??????? c.TABLE_NAME ,
??????? c.COLUMN_NAME ,
??????? c.DATA_TYPE ,
??????? c.CHARACTER_MAXIMUM_LENGTH ,
??????? c.COLUMN_DEFAULT ,
??????? c.IS_NULLABLE ,
??????? c.NUMERIC_PRECISION ,
??????? c.NUMERIC_SCALE
FROM??? [INFORMATION_SCHEMA].[COLUMNS] c
WHERE?? TABLE_NAME = 'Address'

運(yùn)行結(jié)果如下：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
我們都知道我們在定義列的時(shí)候一般的使用都是varchar(50)之類的信息,這里我們需要整合DATA_TYPE和CHARACTER_MAXIMUM_LENGTH信息,當(dāng)CHARACTER_MAXIMUM_LENGTH為-1時(shí)即使說沒有指定具體最大長度,數(shù)據(jù)的指定長度信息是max,而numeric需要整合NUMERIC_PRECISION、NUMERIC_SCALE信息.修改后的SQL如下：
代碼如下:

SELECT? c.TABLE_SCHEMA ,
??????? c.TABLE_NAME ,
??????? c.COLUMN_NAME ,
??????? CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH <> -1
????????????????? )
???????????? THEN c.DATA_TYPE + '('
????????????????? + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
???????????? WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH = -1
????????????????? ) THEN c.DATA_TYPE + '(max)'
???????????? WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )
???????????? THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
????????????????? + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'
???????????? ELSE c.DATA_TYPE
??????? END AS DATA_TYPE ,
??????? c.COLUMN_DEFAULT ,
??????? c.IS_NULLABLE ,
??????? c.COLUMN_DEFAULT
FROM??? [INFORMATION_SCHEMA].[COLUMNS] c
WHERE?? TABLE_NAME = 'Address'
ORDER BY c.ORDINAL_POSITION

運(yùn)行結(jié)果如圖：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
現(xiàn)在我們需要標(biāo)記這張表的那些列是主鍵,那些列是外鍵,要查詢表的主、外鍵信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系統(tǒng)視圖
運(yùn)行結(jié)果如圖：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
修改我們先前的SQL語句：
代碼如下:

SELECT? c.TABLE_SCHEMA ,
??????? c.TABLE_NAME ,
??????? c.COLUMN_NAME ,
??????? CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH <> -1
????????????????? )
???????????? THEN c.DATA_TYPE + '('
????????????????? + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
???????????? WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH = -1
????????????????? ) THEN c.DATA_TYPE + '(max)'
???????????? WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )
???????????? THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
????????????????? + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'
???????????? ELSE c.DATA_TYPE
??????? END AS DATA_TYPE ,
??????? c.COLUMN_DEFAULT ,
??????? c.IS_NULLABLE ,
??????? c.COLUMN_DEFAULT ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'YES'
???????????? ELSE 'NO'
??????? END AS IS_PRIMARY_KEY ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'YES'
???????????? ELSE 'NO'
??????? END AS IS_FOREIGN_KEY
FROM??? [INFORMATION_SCHEMA].[COLUMNS] c
??????? LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
????????????????????????????????????????????????????????????? AND kcu.TABLE_NAME = c.TABLE_NAME
????????????????????????????????????????????????????????????? AND kcu.COLUMN_NAME = c.COLUMN_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE?? c.TABLE_NAME = 'Address'
ORDER BY c.ORDINAL_POSITION

運(yùn)行結(jié)果如圖：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
現(xiàn)在我們已經(jīng)知道那些列是主鍵那些是外鍵,接下來的就是外鍵列所關(guān)聯(lián)的外檢表信息,這里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系統(tǒng)視圖,運(yùn)行該視圖如下：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
所以修改我們的SQL如下：
代碼如下:

SELECT? c.TABLE_SCHEMA ,
??????? c.TABLE_NAME ,
??????? c.COLUMN_NAME ,
??????? CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH <> -1
????????????????? )
???????????? THEN c.DATA_TYPE + '('
????????????????? + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
???????????? WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH = -1
????????????????? ) THEN c.DATA_TYPE + '(max)'
???????????? WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )
???????????? THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
????????????????? + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'
???????????? ELSE c.DATA_TYPE
??????? END AS DATA_TYPE ,
??????? c.COLUMN_DEFAULT ,
??????? c.IS_NULLABLE ,
??????? c.COLUMN_DEFAULT ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN 'YES'
???????????? ELSE 'NO'
??????? END AS IS_PRIMARY_KEY ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN 'YES'
???????????? ELSE 'NO'
??????? END AS IS_FOREIGN_KEY,
??????? fkcu.COLUMN_NAME AS FOREIGN_KEY,
??????? fkcu.TABLE_NAME AS FOREIGN_TABLE
FROM??? [INFORMATION_SCHEMA].[COLUMNS] c
??????? LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
????????????????????????????????????????????????????????????? AND kcu.TABLE_NAME = c.TABLE_NAME
????????????????????????????????????????????????????????????? AND kcu.COLUMN_NAME = c.COLUMN_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE?? c.TABLE_NAME = 'Address'
ORDER BY c.ORDINAL_POSITION

運(yùn)行結(jié)果如圖：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
我們的查詢結(jié)果中顯示了太多的NULL,看著不怎么舒服,還有我們的表名應(yīng)該顯示一次就可以,修改SQL如下：
代碼如下:

SELECT? CASE WHEN c.ORDINAL_POSITION = 1
???????????? THEN c.TABLE_SCHEMA + '.' + c.TABLE_NAME
???????????? ELSE ''
??????? END AS TABLE_NAME ,
??????? c.COLUMN_NAME ,
??????? CASE WHEN ( ( CHARINDEX('char', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH <> -1
????????????????? )
???????????? THEN c.DATA_TYPE + '('
????????????????? + CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
???????????? WHEN ( ( CHARINDEX('CHAR', c.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', c.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND c.CHARACTER_MAXIMUM_LENGTH = -1
????????????????? ) THEN c.DATA_TYPE + '(max)'
???????????? WHEN ( CHARINDEX('numeric', c.DATA_TYPE) > 0 )
???????????? THEN c.DATA_TYPE + '(' + CAST(c.NUMERIC_PRECISION AS VARCHAR(4))
????????????????? + ',' + CAST(c.NUMERIC_SCALE AS VARCHAR(4)) + ')'
???????????? ELSE c.DATA_TYPE
??????? END AS DATA_TYPE ,
??????? ISNULL(c.COLUMN_DEFAULT, '') AS COLUMN_DEFAULT ,
??????? CASE WHEN c.IS_NULLABLE = 'YES' THEN '√'
???????????? ELSE ''
??????? END IS_NULLABLE ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'PRIMARY KEY' THEN '√'
???????????? ELSE ''
??????? END AS IS_PRIMARY_KEY ,
??????? CASE WHEN tc.CONSTRAINT_TYPE = 'FOREIGN KEY' THEN '√'
???????????? ELSE ''
??????? END AS IS_FOREIGN_KEY ,
??????? ISNULL(fkcu.COLUMN_NAME, '') AS FOREIGN_KEY ,
??????? ISNULL(fkcu.TABLE_NAME, '') AS FOREIGN_TABLE
FROM??? [INFORMATION_SCHEMA].[COLUMNS] c
??????? LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] kcu ON kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
????????????????????????????????????????????????????????????? AND kcu.TABLE_NAME = c.TABLE_NAME
????????????????????????????????????????????????????????????? AND kcu.COLUMN_NAME = c.COLUMN_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] tc ON tc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] fc ON kcu.CONSTRAINT_SCHEMA = fc.CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND kcu.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
??????? LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] fkcu ON fkcu.CONSTRAINT_SCHEMA = fc.UNIQUE_CONSTRAINT_SCHEMA
????????????????????????????????????????????????????????????? AND fkcu.CONSTRAINT_NAME = fc.UNIQUE_CONSTRAINT_NAME
WHERE?? c.TABLE_NAME = 'Address'
ORDER BY c.ORDINAL_POSITION


運(yùn)行結(jié)果如圖：
Mysql必讀探討SQL利用INFORMATION_SCHEMA系統(tǒng)視圖如何獲取表的主外鍵信息
有不對的地方還請大家拍磚!

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/4900.html


    標(biāo)簽：