MySQL常用语句

#1 导出数据字典

SELECT
	t.TABLE_SCHEMA AS 库名,
	t.TABLE_NAME AS 表名,
	c.table_comment AS '表中文名',
	t.COLUMN_NAME AS 字段名,
	t.COLUMN_TYPE AS 数据类型,
CASE
	IFNULL( t.COLUMN_DEFAULT, 'Null' ) 
	WHEN '' THEN
	'空字符串' 
	WHEN 'Null' THEN
	'NULL' ELSE t.COLUMN_DEFAULT 
	END AS 默认值,
CASE
	t.IS_NULLABLE 
	WHEN 'YES' THEN
	'是' ELSE '否' 
	END AS 是否允许为空,
	t.COLUMN_COMMENT AS 字段说明 
FROM
	information_schema.COLUMNS t
	INNER JOIN INFORMATION_SCHEMA.TABLES AS c ON t.TABLE_NAME = c.TABLE_NAME 
WHERE
	t.TABLE_SCHEMA = 'populac' 
	AND t.COLUMN_NAME NOT IN ( 'created_at', 'created_by', 'updated_at', 'deleted_at' ) 
	AND t.TABLE_NAME NOT IN ( 'persons', 'children', 'marrows', 'jsways', 'jsycs', 'checks', 'holidays', 'emp_contracts', 'jsops', 'registrations' ) 
ORDER BY
	t.TABLE_NAME,
	t.COLUMN_NAME;