Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主
发布时间：2020-12-12 01:01:56 所属栏目：MySql教程 来源：网络整理
导读：《Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息》要点： 本文介绍了Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息，希望对您有用。如果有疑问，可以联系我们。 接着上篇文章《 解析SQL 表结构信息查询
|
《Mysql必读探讨SQL利用INFORMATION_SCHEMA系统视图如何获取表的主外键信息》要点： 首先我们需要查询列的信息,这需要用到[INFORMATION_SCHEMA].[COLUMNS]系统视图来查询数据列的信息,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' 运行结果如下：
我们都知道我们在定义列的时候一般的使用都是varchar(50)之类的信息,这里我们需要整合DATA_TYPE和CHARACTER_MAXIMUM_LENGTH信息,当CHARACTER_MAXIMUM_LENGTH为-1时即使说没有指定具体最大长度,数据的指定长度信息是max,而numeric需要整合NUMERIC_PRECISION、NUMERIC_SCALE信息.修改后的SQL如下： 代码如下: SELECT? c.TABLE_SCHEMA, ??????? 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 ??????????????????? ) ??????????????????? 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 FROM??? [INFORMATION_SCHEMA].[COLUMNS] c WHERE?? TABLE_NAME = 'Address' ORDER BY c.ORDINAL_POSITION 运行结果如图：
现在我们需要标记这张表的那些列是主键,那些列是外键,要查询表的主、外键信息需要用到[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]和[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] 系统视图 运行结果如图：
修改我们先前的SQL语句： 代码如下: SELECT? c.TABLE_SCHEMA, ??????? 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 运行结果如图：
现在我们已经知道那些列是主键那些是外键,接下来的就是外键列所关联的外检表信息,这里需要用到[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]系统视图,运行该视图如下：
所以修改我们的SQL如下： 代码如下: SELECT? c.TABLE_SCHEMA, ??????? 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 运行结果如图：
我们的查询结果中显示了太多的NULL,看着不怎么舒服,还有我们的表名应该显示一次就可以,修改SQL如下： 代码如下: SELECT? CASE WHEN c.ORDINAL_POSITION = 1 ???????????? THEN c.TABLE_SCHEMA + '.' + c.TABLE_NAME ???????????? ELSE '' ??????? END AS TABLE_NAME, ??????? 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 运行结果如图：
有不对的地方还请大家拍砖! （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
相关内容