[Mysql] Sql 문법익히기 (DDL, DML, DCL)

1 . SQL이란?

Structured Query Language 구조적 질의 언어로  RDBMS (Relational DataBase Magagement System) 관계형 데이터베이스에서 수많은 데이터를 처리 및 관리하기 위해 설계된 관리목적의 프로그래밍 언어이다.

SQL 문법은 데이터베이스를 접근하고 조작하는 데 필요한 표준 언어를 활용할 수 있게 해주는 규칙이다. 

특수 목적성을 띄는 언어로 DB에서 수없이 많은 데이터를 일괄적으로 관리하기 위해 만들어진 것이다. 

 

** 관계형 데이터베이스란?

관계형 데이터베이스란 행과 열로 구성된 테이블 형태로 구성된 데이터를 일컫는다. 데이터의 종속성을 관계로 표현하는 것이 특징이며 대표적인 예로 MS-SQL, MySQL, MariaDB, Oracle 등이 존재한다.

 

* 관계형 데이터베이스에서 관계란 무엇일까?

관계 데이터 모델의 구성요소 가운데 관계(Relation)란 행(Row)과 열(Column)으로 구성된 테이블을 일컫는다.

테이블을 분리한 후 관계를 형성해 데이터를 효율적으로 관리한다. (테이블을 중심으로 데이터 관리가 이루어짐을 알 수 있다.)

ex. 학생이라는 테이블내에 학번, 이름, 학년, 학과 등의 속성(열 = Column)에 맞춰 튜플(행 = Row)의 데이터가 형성되어있다.

 

 

 

2) SQL 문법의 종류

1. 데이터정의어(DDL) _ Data Definition Language 

- 데이터 정의어는 데이터를 정의하는 언어로서 `데이터를 담는 그릇을 정의하는 언어`이다.

- 테이블이나 관계의 구조를 생성하는 데 사용한다.

- 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 데이터 구조와 관련된 명령어  들을 데이터 정의어라고 부른다.

-  CREATE(생성), ALTER(변경), DROP, TRUNCATE(삭제) 문이 있다.

 

 

 

1) CREATE(생성) _ 데이터베이스 오브젝트를 생성

---

  # CREATE TABLE 속성 

 

- PRIMARY KEY _ 테이블의 기본키를 정의, 유일하게 테이블의 각 행을 식별

- FOREIGN KEY _ 외래키를 정의, 참조대상을 테이블(컬럼명)로 명시, 열과 참조된 테이블의 열 사이의 외래키 관계를 적용하고 설정

- UNIQUE _ 해당 컬럼에 동일한 값이 들어가지 않도록 하는 제약조건

- NOT NULL _ 해당 컬럼은 NULL 값을 포함하지 않도록 하는 설정 

- CHECK _ 개발자가 정의하는 제약조건, 참(TRUE)이어야하는 조건을 지정 

- DEFAULT _ 해당 필드의 기본값을 설정 

---

 

2) ALTER(변경) _ 데이터베이스 오브젝트를 변경

---

  # ALTER TABLE 문법 

 

- 컬럼추가 ) ALTER TABLE 테이블명 ADD 컬럼명 데이터타입; _ 데이터에 필요한 컬럼을 추가하는 명령어

- 컬럼수정 ) ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [DEFAULT 값] [NOT NULL]; _ 기존 테이블에 존재하는 컬럼의 데이터 유형, 기본값, NOT NULL등의 제약조건에 대한 변경

- 컬럼삭제 ) ALTER TABLE 테이블명 DROP 컬럼명; _ 테이블에 존재하는 컬럼을 삭제하는 명령어

- 컬럼명 수정 ) ALTER TABLE 테이블명 RENAME COLUMN 변경 전_ 컬럼명 TO 변경 후_컬럼명; _ 테이블의 컬럼명을 변경하는 명령어 

---

 

3) DROP _ 데이터베이스 오브젝트를 삭제

  # DROP TABLE 명령어 옵션

- CASCADE 참조하는 테이블까지 연쇄적으로 제거하는 옵션

- RESTRICT 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션

 

ex. DROP TABLE 테이블명;

       DROP TABLE 테이블명 [ CASCADE | RESTRICT ];

 

 

4) TRUCATE _ 데이터베이스 오브젝트 내용 삭제

ex. TRUNCATE TABLE 테이블명;

 

 

** 삭제 정의어 주의사항 

DROP의 경우, 데이터베이스 오브젝트를 삭제한다 -> 테이블 자체를 삭제한다

TRUNCATE의 경우, 데이터베이스 오브젝트 내용을 삭제한다 -> 테이블은 그대로 유지한다.

 

 

 

 

 

2.데이터조작어(DML)_ Data Manipulation Language

- 데이터 조작어는 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어이다.

- 데이터 조작어 유형에는 SELECT(조회), INSERT(생성), UPDATE(변경), DELETE(삭제)가 있다.

 

1) SELECT _ 데이터의 내용을 조회

ex. SELECT [ ALL | DISTINCT ] 속성명1 (Column), 속성명2 (Column) FROM 테이블명1 

---

  # SELECT 명령어

 

- SELECT절 _ 검색하고자 하는 속성명, 계산식 

                        2개 이상의 테이블을 대상으로 검색할 때는 '테이블명 , 속성명' 으로 표현 

                        술어 부분은 ALL이 기본값

ALL	- 모든 튜플을 검색할 때 사용  - SELECT 뒤에 명시하지 않을 경우 ALL로 인식
DISTINCT	- 중복된 속성이 조회될 경우 그 중 한개만 검색(SELECT 뒤에 명시된 속성이 중복될 경우 한개만 검색)

 

 

- FROM절 _  질의에 의해 검색될 데이터들을 포함하는 테이블명을 기술 

- WHERE절 _ 검색할 조건을 기술

- GROUP BY절_ 속성값을 그룹으로 분류하고자 할 때 사용

- HAVING절_ GROUP BY에 의해 분류한 후 그룹에 대한 조건을 기술 

- ORDER BY절_ 속성값을 정렬하고자 할 때 사용 

                           ASC와 DESC 키워드 생략 시 오름차순 정렬 

 

ASC	오름차순 정렬
DESC	내림차순 정렬

 

 

** AND 조건문

ex. SELECT * FROM `student` WHERE sex = ‘남자’ AND address = ‘서울’; >> 성별이 남자이고 주소가 서울인 사람을 조회해라(행으로 조회됨)

 

** OR 조건문

ex. SELECT * FROM `student` WHERE sex = ‘여자’ OR address = ‘서울’;  >> 성별이 여자이거나 주소가 서울인 사람들을 조회해라(행으로 조회됨)

---

 

2) INSERT _ 데이터의 내용을 삽입

---

  # SELECT 명령어

 

- 속성과 데이터 개수, 데이터 타입이 일치해야 한다.

- 속성명은 생략이 가능하다.

 

ex. INSERT INTO 테이블명 (속성명1, ...) VALUES (데이터1, ...);  

      INSERT INTO 테이블명 VALUES (value1, value2, value3);

---

 

3) UPDATE_ 데이터의 내용을 변경 

---

  # UPDATE 명령어

 

- UPDATE 명령문은 WHERE 절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 자주 사용된다.

 

ex. UPDATE 테이블명SET 속성명 = 데이터, WHERE 조건;

      UPDATE 테이블명 SET 컬럼1=컬럼의값(변경시킬값) WHERE 대상이 될 컬럼명=컬럼의 값 (_변경대상);

      UPDATE student SET name = 이진경 WHERE id = 1 ( 해석 ; 아이디 1번을 가진 행의 name 을 이진경으로 변경해라 = 행을 사용        할 수도 있음)

     UPDATE 테이블명 SET 컬럼1=컬럼의값  >> 모든 컬럼1의 값을 변경시킴

---

 

4) DELETE_ 데이터의 내용을 삭제

 

  # DELETE 명령어

 

- 모든 레코드를 삭제할 때는 WHERE 없이 DELETE만 사용

- 레코드를 삭제해도 테이블 구조는 남아 있어서 디스크에서 테이블을 완전히 삭제하는 DROP 명령과는 다르다.

- 행단위로 데이터가 삭제된다.

 

ex. DELETE FROM 테이블명 WHERE 조건;

      DELETE FROM student WHERE id = 2 (해석;  id 가 2번인 행을 삭제해라 )

 

 

 

 

3.데이터제어어(DCL)_ Data Control Language

 

- 데이터 제어어는 DB관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 언어이다.

(* 병행제어란? 여러 트랜잭션을 수행할 때 트랜잭션들이 데이터베이스의 일관성을 파괴하지 않도록 트랙잭션 간의 상호작용을 제어하는 기능) 

- 데이터 제어어의 유형에는 GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT(=CHECKPOINT)가 있다.

- COMMIT, ROLLBACK, SAVEPOINT는 DCL이기도 하지만, TCL(Transaction Control Language)이라고도 불린다.

(* TCL이란? 트랜잭션을 제어하는 언어이다. )

 

---

# 데이터 제어어 유형 (DCL / TCL)

 

* DCL

GRANT	사용 권한 부여	관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
REVOKE	사용 권한 취소	관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어

 

 

* DCL / TCL

COMMIT	트랜잭션 확정	데이터베이스 트랜잭션의 내용 업데이트를 영구적으로 확정하는 명령어
ROLLBACK	트랜잭션 취소	데이터베이스에서 업데이트 오류가 발생할 때, 이전 상태로 되돌리는 명령어
SAVEPOINT ( = CHECKPOINT)	저장 시기 설정	트랜잭션의 특정 지점에 이름을 지정하고, 그 지점 이전에 수행한 작업에 영향을 주지 않고 그 지점 이후에 수행한 작업을 롤백할 수 있는 명령어

 

---

 

1) GRANT_ 데이터베이스 관리자(DBA)가 사용자에게 데이터베이스에  대한 권한을 부여 

- 데이터 베이스 관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어이다.

- GRANT 구문 마지막에 WITH GRANT OPTION 키워드를 붙이면 권한이 필요할 경우 다른 사용자에게 부여할 수 있는 권한을 부여할 수 있다. 

---

# GRANT 명령어

 

시스템 권한	GRANT 권한 TO 사용자;	관리자가 사용자에게 테이블/뷰/프로시저 등을 생성하고 삭제할 수 있는 권한을 부여
객체 권한	GRANT 권한 ON 테이블 TO 사용자;	관리자가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행을 할 수 있는 권한을 부여

 

 

ex. GRANT 권한 ON 테이블 TO 사용자;

      GRANT UPDATE ON 학생 TO 장길산; -> 사용자인 '장길산'에게 '학생' 테이블에 대해 수정(UPDATE)할 수 있는 권한을 부여

      GRANT SELECT ON 학생 TO 장길산 WITH GRANT OPTION; -> 사용자인 '장길산'에게 '학생' 테이블에 대해 조회(SELECT)할 수 있는 권한과 그 권한을 다른 사용자에게 부여할 수 있는 권한을 부여

---

 

 

2) REVOKE_ 데이터베이스 관리자(DBA)가 사용자에게 부여했던 데이터베이스에  대한 권한을 회수 

- 데이터 베이스 관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어이다.

- REVOKE 구문에 CASCADE CONSTRAINTS 키워드를 붙이면 WITH GRANT OPTION으로 부여된 사용자들의 권한까지 회수할 수 있다. 

---

# REVOKE 명령어

시스템 권한	REVOKE 권한 FROM 사용자;	관리자가 사용자에게 테이블/ 뷰 / 프로시저 등을 생성하고 삭제할 수 있는 권한을 회수
객체 권한	REVOKE 권한 ON 테이블 FROM 사용자;	관리자가 사용자에게 테이블을 수정, 삽입, 삭제, 조회와 프로시저 실행할 수 있는 권한을 회수

 

 

ex. REVOKE UPDATE ON 학생 FROM 장길산; -> 사용자인 '장길산'에게 '학생' 테이블에 대해 수정(UPDATE)할 수 있는 권한을 회수

      REVOKE SELECT ON 학생 FROM 장길산 CASCADE CONSTRAINT; -> 사용자인 '장길산'에게 '학생' 테이블에 대해 조회 (SELECT)할 수 있는 권한과 WITH GRANT OPTION 으로 부여된 사용자들의 권한까지 회수

---