[MySQL] SQL (DDL, DML, DCL)

1. SQL (Structured Query Language)

1) SQL

구조화된 질의 언어로 DBMS를 관리하고 제어하기 위해 사용하는 인터페이스 또는 언어이다. 데이터베이스로부터 정보를 얻거나 갱신하기 위한 표준 대화식 프로그래밍 언어이다. DBMS에 따라 사용되는 SQL 문법이 다르다.

2) 기본 문법

항목	MSSQL	MySQL	ORACLE
한줄 주석	--	#, --	--
범위 주석	/* */	/* */	/* */
문자열 지정	'	', "	'
산술 연산자	+, -, *, /, %, ...	+, -, *, /, div	+, -, *, /
비교 연산자	=, >, <, >=, <=, <>, !=, !>, !<	=, >, <, >=, <=, <>, <==>, !=	=, >, <, >=, <=, <>
문자열 비교	like, not like	like, not like	like, not like
논리 연산자	and, or, not	and(&&), or(||), not(!)	and, or, not
범위 연산자	between A and B not between A and B	between A and B not between A and B	between A and B not between A and B
값 출력 연산자	in, not in	in, not in	in not in
NULL 검색	is null, is not null	is null, is not null	is null, is not null

3) 참고 명령어

하나의 명령어 끝에는 항상 세미콜론으로 끝나야한다.

// 에러나 경고 메시지
mysql> SHOW ERRORS;
mysql> SHOW WARNINGS;

※ 기본적으로 제공하는 information_schema에는 DBMS에 있는 DB와 테이블 정보를, mysql은 계정 및 권한 관련 정보를, test는 테스트용으로 아무것도 들어가 있지 않은 데이터베이스가 있다.

 

 

 

2. DDL (Database Definition Language)

1) DDL

데이터 정의어로 데이터베이스의 구조를 정의하거나 변경, 삭제하기 위해 사용하는 언어이다. (CREATE, ALTER, DROP, TRUNCATE)

2) 테이블 생성 및 조회

테이블 생성시 컬럼명 - 데이터타입 - 옵션 - 제약 조건 순으로 생성해야한다.

// 테이블 생성
mysql> USE [DB명];
mysql> CREATE TABLE [TABLE명] (
  no INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  name CHAR(20),
  id VARCHAR(20) UNIQUE,
  password VARCHAR(50) DEFAULT '1234',
  reg_date DATETIME NOT NULL);
  
// 테이블 목록 조회
mysql> SHOW TABLES;

// 테이블 구조 조회
mysql> DESC [TABLE명];

타입	크기	데이터
bool, boolean	1 Byte	참 / 거짓
tinyint	1 Byte	정수
int,, integer	4 Byte	정수
bigint	8 Byte	정수
float	4 Byte	부동소수점(실수)
double	8 Byte	부동소수점(실수)
bit(n)	(n) Bit	정수
char(n)	(n) Byte 고정형, 최대 2^8 Byte	문자
varchar(n)	(n) Byte 가변형, 최대 2^8 Byte	문자
text	2^16 Byte	문자
longtext	2^32 Byte	문자
date		YYYY-MM-DD
time		HH:MM:SS
datetime		YYYY-MM-DD HH:MM:SS
timestamp		YYYY-MM-DD HH:MM:SS
year		YYYY

제약 조건	설명
NOT NULL	NULL 값 사용 제한한다.
UNIQUE	테이블 내에서 해당 COLUMN의 값이 유일하도록 설정한다. 중복이 될 수 없다.
PRIMARY KEY	테이블의 각 행을 고유하게 식별하는 값으로, UNIQUE와 NOT NULL의 제약 조건의 특징을 모두 보유하고 있다.
FOREIGN KEY	두 테이블 데이터 간 연결을 설정하고 강제 적용하여 외래 키 테이블에 저장될 수 있는 데이터를 제어한다.
CHECK	해당 COLUMN에 저장 가능한 데이터 값의 범위나 조건을 지정한다.

테이블 생성 및 조회

3) 테이블 구조 변경

mysql> ALTER TABLE [TABLE명] [ACTION] [옵션]...;

옵션	기능
ADD	COLUMN 추가
DROP	COLUMN 삭제
MODIFY	COLUMN 수정
CHANGE	COLUMN 변경
RENAME	테이블명 변경

// COLUMN 추가 예시
mysql> ALTER TABLE [TABLE명] ADD [COLUMN명] [데이터타입] [옵션] [제약조건] [위치옵션];
mysql> ALTER TABLE [TABLE명] ADD nickname VARCHAR(30) UNIQUE;
mysql> ALTER TABLE [TABLE명] ADD age INT FIRST;
mysql> ALTER TABLE [TABLE명] ADD memo TEXT AFTER [COLUMN명];

// COLUMN 삭제
mysql> ALTER TABLE [TABLE명] DROP [COLUMN명];

// COLUMN 변경
mysql> ALTER TABLE [TABLE명] MODIFY [COLUMN명] [데이터타입] [옵션] [제약조건];
mysql> ALTER TABLE [TABLE명] MODIFY [COLUMN명] VARCHAR(50) DEFAULT 'qwer1234';

mysql> ALTER TABLE [TABLE명] CHANGE [기존COLUMN명] [새COLUMN명] [데이터타입] [옵션] [제약조건];

// COLUMN명 변경
mysql> ALTER TABLE [기존TABLE명] RENAME [새COLUMN명];
mysql> RENAME [기존TABLE명] TO [COLUMN명];

// 테이블 초기화
mysql> TRUNCATE [TABLE명];

// 테이블 삭제
mysql> DROP TABLE [TABLE명];

 

 

 

3. DML (Data Manipulation Language)

1) DML

데이터 조작을 위해 사용하는 언어이다. 데이터의 삽입, 수정, 삭제, 조회 등의 동적을 제어한다. (INSERT, UPDATE, DELETE, SELECT)

2) 데이터 생성

// 테이블의 모든 컬럼에 데이터 입력할 경우
mysql> INSERT INTO [TABLE명] VALUES([값1], [값2] ...);

// 특정 컬럼을 지정해서 데이터 입력할 경우
mysql> INSERT INTO [TABLE명]([COLUMN1], [COLUMN2], ...) VALUES([값1], [값2] ...);
mysql> INSERT INTO [TABLE명] SET [COLUMN명]=[값], ...;

3) 데이터 조회

// 테이블 내 모든 데이터 조회
mysql> SELECT * FROM [TABLE명];

// 특정 컬럼 지정 조회
mysql> SELECT [COLUMN명] ... FROM [TABLE명];

// 조건 조회
mysql> SELECT [COLUMN명] ... FROM [TABLE명] WHERE [조건];

// 조건과 비슷한 조회
mysql> SELECT [COLUMN명] ... FROM [TABLE명] WHERE [조건1] LIKE '%';

// 데이터 일부 조회
mysql> SELECT * FROM [TABLE명] LIMIT [개수];
mysql> SELECT * FROM [TABLE명] LIMIT [개수] OFFSET [위치];
mysql> SELECT * FROM [TABLE명] LIMIT [위치],[개수];

// 데이터 정렬 조회
mysql> SELECT * FROM [TABLE명] ORDER BY [정렬 기준 COLUMN] [DESC/ASC];

// 데이터 중복 제거 조회
mysql> SELECT DISTINCT * FROM [TABLE명];

// 데이터 그룹핑 조회
mysql> SELECT * FROM [TABLE명] GROUP BY [COLUMN명] ;
mysql> SELECT * FROM [TABLE명] GROUP BY [COLUMN명] HAVING [조건] ;

※ 데이터 조회(SELECT)의 경우 사용할 수 있는 함수 및 지시어가 존재한다. (count(), sum(), avg(), min(), max())

※ HAVING절의 경우, GROUP BY절에서 집계함수를 조건으로 사용할 때 사용한다.

4) 데이터 수정

mysql> UPDATE [TABLE명] SET [COLUMN명] = [값] ... WHERE [조건]

5) 데이터 삭제

mysql> DELETE FROM [TABLE명] WHERE [조건];

6) 쿼리 일괄 처리

파일의 형태로 SQL 명령을 작성하여 저장한 뒤 DBMS에 바로 적용 시키는 방법이다. 텍스트 파일에 처리할 SQL 명령을 작성한 후 저장한 뒤 저장할 때 확장자를 .sql로 지정하고, 인코딩형식을 UTF-8로 지정한다.

mysql < [파일명]

---

웹에서 회원가입을 하는 쿼리의 약식 코드(PHP)

<?php
   $_POST['id'];
   $_POST['pw'];
   $sql_query = "INSERT INTO member SET id='{$id}', pw='{$pw}';
?>

 

 

 

4. DCL (Data Control Language)

1) DCL

DB에 대한 보안, 무결성, 복구 등 DBMS를 제어하기 위한 언어이다. (GRANT, REVOKE, COMMIT, ROLLBACK) 보안 관점에서 바라볼때, 데이터베이스 사용자(앱/웹)의 접근 가능한 위치를 지정하거나 사용자가 사용할 수 있는 언어에 제한을 걸 수 있다.

2) 계정 확인

MySQL DB의 user 테이블에서 DBMS 계정에 대한 정보를 관리한다.

mysql> DESC mysql.user;
mysql> SELECT user, host, password FROM mysql.user;

항목	설명
user	계정명
host	접속 경로
password	비밀번호
localhost	DBMS가 설치도니 로컬 시스템에서의 접속
%	모든 네트워크에서의 접속
IP + %	특정 네트워크 영역에서의 접속 (192.168.94.%)
IP	특정 IP에서만 접속

3) 계정 생성

// 계정 생성 (1)
mysql> CREATE user '[계정명]'@'[접속경로]' IDENTIFIED BY '[비밀번호]';
mysql> FLUSH PRIVILEGES;

// 계정 생성 (2)
mysql> INSERT INTO mysql.user( user, host, password ) VALUES ( '[계정명]' , '[접속경로]' , password('[비
밀번호]')); 
mysql> FLUSH PRIVILEGES;

4) 계정 비밀번호 설정

mysql> UPDATE mysql.user SET password = password('[비밀번호]') WHERE user='root';
mysql> FLUSH PRIVILEGES;

5) 계정 삭제

mysql> DROP user '[계정명]'@'[접속경로]';
mysql> FLUSH PRIVILEGES;

6) 권한 구성

// 권한 확인
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR '[계정명]'@'[접속경로];

// 권한 설정
mysql> GRANT PRIVILEGES ON [DB명].[TABLE명] TO '[계정명]'@[접속경로] IDENTIFIED BY PASSWORD '[비밀번호]' WITH GRANT OPTION;

// 모든 권한 허용
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.94.%' WITH GRANT OPTION;

// 특정 테이블의 특정 컬럼 조회 권한 허용
mysql> GRANT SELECT(id, name), INSERT ON [DB명].[TABLE명] TO 'root'@'localhost';

// 권한 삭제
mysql> REVOKE PRIVILEGES ON [DB명].[TABLE명] FROM '[계정명]@'[접속경로]';

※ WITH GRANT OPTION은 권한을 다른 사용자한테 부여 가능 여부를 결정하는 옵션이다.

 

 

 

5. MySQL에서 자주 사용하는 함수

함수	설명
version()	MySQL 버전
now()	현재 시간 (년월일 시분초)
curdate()	현재 날짜 (년월일)
year(datetime)	연도만 추출
concat(str1, str2)	문자열 합치기
group_concat()	그룹 문자열 합치기 (SQL Injection에 많이 사용됨)
substr(str, offset, count)	문자열에서 오프셋 위치부터 개수만큼 출력 (SQL Injection에 많이 사용됨)
lpad(str, count, padding_char), rpad()	문자열의 자릿수를 개수만큼 패딩 문자열로 패딩

---

[참고] Database - Practice - DDL, DML