안녕하세요. Johncom 입니다.

information Schema의 테이블에는 테이블 관련 정보들이 정의되어 기록되고 있습니다.

모든 값들은 byte 단위로 기록되고 있어 간단한 변환이 필요합니다.

아래 내용은 "MB" 기준으로 계산 되어 "GB" 계산을 진행하려면 /1024를 추가해야 합니다.

DESC information_schema.TABLES;

< information_shema.TABLES 구조 >

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+

TABLE 정보

Query

1. 간단한 DB 사이즈 TOTAL SIZE 조회 ( 데이터 + 인덱스 )

SELECT table_schema "DATABASE",
ROUND(SUM(data_length+index_length)/1024/1024,1) "DATA(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

2. DB 사이즈 중 DATA와 INDEX 나눠서 보기

SELECT
table_schema "DATABASE",
ROUND(SUM(data_length)/(1024/1024),2) as 'DATA(MB)',
ROUND(SUM(index_length)/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY 2 DESC;

3.특정 DB내 테이블 정보 가져오기

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
WHERE table_schema = 'test'
ORDER BY 3 DESC;

4.DB-TABLE 별 COUNT와 DATABASE SIZE 확인하기

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES;

자주 내가 날리는 쿼리

SELECT
table_schema "DATABASE",
table_name "TABLE",
table_rows "TABLE_COUNT",
ROUND(data_length/(1024/1024),2) as 'DATA(MB)',
ROUND(index_length/(1024/1024),2) as 'INDEX(MB)'
FROM information_schema.TABLES
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema')
ORDER BY data_length DESC;

--  USER 생성

-- 서버 설정 
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 특정 플러그인 지정 (과거사용 5.7 이하)
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

-- 권한

-- 모든 권한 + 모든 DB 접근 권한 부여
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';

-- 특정 DB 전체 권한 
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';

-- 특정 테이블 전체 권한
GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';

-- 특정 테이블 SELECT 권한
GRANT SELECT ON database_name.table_name TO 'username'@'host';

-- 특정 테이블 SELECT, INSERT 권한
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

-- 특정 프로시저 실행 권한
GRANT EXECUTE ON PROCEDURE database_name.procedure_name TO 'username'@'host';


-- 특정 유저 권한 확인
SHOW GRANTS FOR 'username'@'host';

-- 특정 권한 취소
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';
REVOKE SELECT ON mydatabase.mytable FROM 'testuser'@'localhost';


-- 마지막에는 까먹지 말고 꼭!
FLUSH PRIVILEGES;

-- 유저 조회

-- USER와 HOST를 조회
SELECT host, user FROM mysql.user;

1. 데이터 조작 언어 ( Data manipulation language)

데이터 조작언어에 대해서 알아보겠습니다.

데이터 조작언어에 종류

1. SELECT (조회)

2. INSERT (삽입)

3. DELETE (삭제)

4. UPDATE(수정)

{글} <- 여기서 중괄호{}는 생략 해주세요.

*** DBMS 이용시 칼럼에 ''작은따옴표를 묶어 주셔야 합니다. DBMS마다 상이함. ***

*** 문자데이터는 ''작은따옴표로 묶어 줘야 합니다. ***

1. SELECT 원하는 데이터를 조회할 때 사용합니다.

1-1.모든 칼럼 모든 튜플 조회 : SELECT * FROM {테이블명}

1-2. 원하는 칼럼 모든 튜플 조회 : SELECT {칼럼명}, {칼럼명2}, {칼럼명3} FROM {테이블명} 

1-3. 원하는 칼럼 원하는 튜플 조회 : SELECT {칼럼명}, {칼럼명2}, {칼럼명3} FROM {테이블명} WHERE {조건칼럼명} = {조건값}

2. INSERT 원하는 데이터를 테이블에 넣을때 사용합니다.

INSERT INTO {테이블명} ( {칼럼명1}, {칼럼명2}, {칼럼명3} ) VALUES ( '값1', '값2', '값3') 

** 칼럼에 DEFAULT 값이 설정 되어 있거나 NULL 허용이 되어 있다면 빈칸으로 두어도 됩니다.   ex ) '밥',3000, ,3명 **

** 칼럼명 부분을 생략하면 모든 데이터 입력을 하게 됩니다. ex) INSERT INTO {테이블명} VALUES ( '값1', '값2', '값3') 

3. DELETE 데이터를 삭제할때 사용합니다.

DELETE FROM {테이블명} WHERE {조건칼럼명} = {조건값}

** 앞과 마찬가지로 찾는값이 문자이면 '' 작은따옴표로 묶어주세요.

4. UPDATE 데이터를 수정할 때 사용합니다.

UPDATE {테이블명} SET WHERE {조건칼럼명} = {조건값}