[MySQL] 데이터스키마 구조 파악하기

1. 테이블 정보 조회

-- 여러 테이블 기준
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = SCHEMA();

-- 특정 테이블 한개의 정보를 조회
DESC 테이블명;

2. 컬럼 정보 조회

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = '{테이블명(table name)}'
ORDER BY ORDINAL_POSITION;

3. 컬럼 간단 정보 조회

SELECT
 TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION AS NO, COLUMN_NAME, COLUMN_TYPE,
 COLUMN_DEFAULT AS `DEFAULT`, IS_NULLABLE,  COLUMN_KEY AS `KEY`, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = '{테이블명(table name)}'
ORDER BY ORDINAL_POSITION;

4. 현재 데이터베이스에 있는 모든 테이블 정보 조회

SELECT
 ORDINAL_POSITION AS NO, TABLE_NAME, COLUMN_COMMENT,  COLUMN_NAME, COLUMN_TYPE,  IF(IS_NULLABLE = 'YES','','V') AS NOT_NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME IN (
 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = SCHEMA()
)
ORDER BY TABLE_NAME, ORDINAL_POSITION;

5. 현재 사용중인 것

SELECT
 ORDINAL_POSITION AS NO, COLUMN_COMMENT,  COLUMN_NAME, COLUMN_TYPE,  IF(IS_NULLABLE = 'YES','','V') AS NOT_NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME = 'Recommend' /* <-- (table name) */
ORDER BY ORDINAL_POSITION;