[SQL] DBA가 DBMS와 대화하는 언어

👍 SQL이란?

사용자의 요구분석에 따라 개념적 설계, 논리적 설계, 물리적 설계를 했다면, 이제는 실제로 DBMS를 이용하여 테이블을 구현할 수 있다.

SQL은 DBA(데이터베이스 관리자)가 DBMS를 이용하여 데이터를 처리할 때 사용하는 언어다.

SQL을 세부적으로 나누어보면, 인터페이스(상호작용)와 구성요소로 달리 구분할 수 있다.

 

👊 SQL의 인터페이스에 따른 분류

 

대화식 SQL

DBA가 DBMS 안에서 SQL을 직접 사용하며 정보를 계속 주고받는 방식

내장 SQL

다른 프로그램 안에 삽입되어, 그 프로그램이 사용자의 요청에 따라 DB에 접근할 때 이미 등록된 SQL에 따라 정보를 보여주는 방식

 

👊 SQL의 구성요소에 따른 분류

 

DDL(데이터 정의어)

DCL(데이터 제어어)

DML(데이터 조작어)

---

👊 DDL(Data Definition Language) : 데이터베이스(테이블) 자체를 실제로 만들거나 구조를 변경하거나 삭제

 

DDL은 SQL 중, 논리적 설계에서 만든 관계 스키마(정규화를 거친 테이블)와 물리적 설계(자료형 및 크기 설정)를 실제 테이블로 만들기 위해 사용하는 명령어다.

 

DDL로 정의된 내용은 "메타데이터"가 되며, "시스템 카탈로그(데이터 사전)"에 저장된다.

 

여기서 시스템 카탈로그란,

[DBA가 "CREATE TABLE ~~"이라는 SQL-DDL 명령어를 입력하고 엔터키를 누름으로써 탄생하는 테이블의 모든 데이터]를 담고 있는 상위 데이터이다.(메타데이터 = 데이터의 데이터)

시스템 카탈로그를 수정하려면 직접 할 수 없고, DDL의 ALTER을 이용한다.

---

👊 DDL에는 무엇이 있나?

 

🙌 CREATE - 물리적 설계를 그대로 옮기는 과정

 

1) CREATE DOMAIN

도메인(속성이 가질 수 있는 값의 범위)을 정의하는 명령문

 

• Char(n) : n 바이트 초과되는 문자열은 받을 수 없으며, n 바이트 이하이면 모두 n 바이트의 메모리를 할당(메모리 낭비 발생, 연산에는 유리)
• varChar(n) : n 바이트 초과되는 문자열은 받을 수 없으며, n 바이트 이하일 때 그때그때 바이트 만큼의 메모리만 할당(메모리 낭비 없음, 연산에는 불리)

 

2) CREATE TABLE 테이블이름 {이하 명령어들}

테이블을 정의하는 명령문

 

• 속성명 자료형(크기) NOT NULL : 해당 속성은 Null을 가질 수 없음 (cf SELECT문에서의 IS NOT NULL은 Null이 아닌 값을 찾아달라는 주문이지만, CREATE에서의 NOT NULL은 Null이어서는 안된다고 명령하는 것이다)
• PRIMARY KEY(속성명) : 해당 속성을 기본키로 지정
• UNIQUE(속성명) : 기본키가 아닌 속성이 유일한 값을 갖도록 할 때 지정
• FOREIGN KEY(속성명) REFERENCES 테이블명(기본키) : 다른 테이블에 가서 기본키로 정보를 조회하도록 하는 속성을 외래키로 지정
• ON DELETE/UPDATE CASCADE : 1대n 테이블에서 1의 레코드 삭제/업데이트 시, n의 레코드도 같이 삭제/업데이트 됨으로써 참조무결성을 유지하는 방법
• ON DELETE/UPDATE SET NULL : [참조테이블(PK, 부모)]의 어떤 값에 변화가 생기면, [참조하는 테이블(FK, 자식)]에서는 해당 값을 Null로 바꾼다.
• ON DELETE/UPDATE SET DEFAULT : [참조테이블]의 어떤 값에 변화가 생기면, [참조하는 테이블]에서는 해당 값을 미리 정의해 둔 기본값으로 바꾼다.
• ON DELETE/UPDATE NO ACTION : [참조테이블]의 어떤 값에 변화가 생기지 않도록 미리 막는다.
• ON DELETE/UPDATE RESTRICT : NO ACTION과 같음
• CONSTRAINT 제약조건이름지정 CHECK(제약조건)

 

3) CREATE VIEW

사용자에게 보여지는 뷰(외부 스키마)를 정의하는 명령문

 

뷰는 실제 데이터가 아니라 겉으로 보여지기 위한 것뿐이기 때문에, 뷰를 다룰 때는 원본을 보호하기 위해 입출력 제약을 거는 것이 안전하다.

그 제약은 [WITH CHECK OPTION] 구문을 추가함으로써 실행되고, 뷰에 데이터를 삽입하거나 삭제 시 무결성을 검사하게 된다.

 

CREATE VIEW 뷰이름지정(뷰에 만들 속성들) AS SELECT 불러올속성들 FROM ~~

 

 

4) CREATE INDEX

데이터 정렬을 위한 인덱스를 정의하는 명령문

 

CREATE UNIQUE INDEX 인덱스명지정 ON 테이블이름(정렬대상 정렬방식)

---

🙌 ALTER - 이미 CREATE 했던 테이블의 구조를 변경

 

ALTER 기존테이블이름 ADD 추가할속성이름 자료형(크기)

ALTER 기존테이블이름 ALTER 수정할속성이름 *속성이름/기본값 등 변경가능

ALTER 기존테이블이름 DROP 삭제할속성이름

---

🙌 DROP - 스키마, 도메인, 테이블, 뷰. 인덱스 삭제

 

DROP 스키마/도메인/테이블/뷰/인덱스이름 [CASCADE/RESTRICT]

 

CASCADE : [참조테이블] 삭제 시 [참조하는 테이블]도 함께 삭제

RESTRICT : [참조하는 테이블]이 존재한다면 [참조테이블] 삭제 취소

 

---

👊 DCL(Data Control Language) : 데이터베이스(테이블)의 보안, 무결성, 회복을 담당

 

🙌 무결성 - 기존 정보를 사용자의 연산 실수로부터 보호하기 위한 장치

 

1) COMMIT : 연산 성공 시 기존 값 업데이트

 

일단 SQL 명령을 내린 후, 트랜잭션이 정상적으로 종료되었음을 확인하면 COMMIT을 입력하여 그 결과를 반영하게 함

*Auto Commit 설정이 꺼져만 있다면, COMMIT 명령을 내리기 전까지 실제 DB에는 반영되지 않는다(해당 사용자에게만 적용되는 것으로 보일 뿐, 다른 사용자는 미적용 상태로 보임).

 

2) ROLLBACK : 연산 실패 시 기존 값 복원

 

COMMIT을 하기 전까지는 연산이 실패했든 삽입/삭제로 테이블을 변경했든, 실제 DB에 반영된 것이 아니기 때문에, ROLLBACK을 입력하여 쉽게 복구 가능

---

🙌 보안(권한) - 사용자에게 해당 객체에 대한 특정 사용권한(SELECT, INSERT, DELETE, UPDATE)을 부여/취소 시 사용

 

1) GRANT : DB 사용권한 부여

 

GRANT 부여할권한 ON 테이블명 TO 유저이름 WITH GRANT OPTION

*WITH GRANT OPTION : 해당 유저에게 권한을 부여함과 동시에, 그 유저는 자신이 부여받은 권한들을 다른 유저에게 똑같이 물려줄 수 있음

 

예

GRANT SELECT, DELETE ON STUDENT TO U1 WITH GRANT OPTION

U1은 다른 U2에게 자신이 부여받은 SELECT, DELETE 권한을 물려줄 수 있다.

 

2) REVOKE : DB 사용권한 회수

 

REVOKE 줬던권한들 ON 테이블명 FROM 줬던유저명 CASCADE

*CASCADE : 권한을 부여했던 유저로부터 다시 권한을 뺏을 때, 만약 그 유저가 다른 유저들에게 권한을 줬을 경우 다 뺏어옴

---

👊 DML(Data Manipulation Language) : 데이터베이스(테이블) 내 데이터를 조작

 

🙌 SELECT - 데이터 검색

SELECT는 해당 테이블 전체 또는 사용자가 부여한 조건에 맞는 튜플을 빠르게 검색하여 임시테이블을 구성하는 명령문이다.

데이터베이스를 이용하는 이유부터가 내가 원하는 정보를 빨리 찾기 위함이므로, SQL에서는 SELECT를 가장 많이 사용하게 된다.

SELECT 테이블명.속성 FROM 테이블명;

🙌 DISTINCT - 중복 제거

속성에 중복값이 있는 경우 하나만 출력하려면 DISTINCT를 기입한다.

SELECT DISTINCT 테이블명.속성 FROM 테이블명;

🙌 WHERE - 조건 추가

추출할 튜플에 조건을 추가하려면 WHERE을 기입한다.

SELECT 테이블명.속성 FROM 테이블명 WHERE 속성1 = '값1' AND 속성2 = '값2';

🙌 IN - 같은 속성 내 여러 조건 추가

IN은 속성 조건들의 교집합이라는 점에서는 AND와 비슷하지만, 같은 속성에 속한 값들이어야 한다는 차이가 있다.

SELECT 테이블명.속성 FROM 테이블명 WHERE 속성1 IN('값1', '값2');

🙌 ORDER BY - 데이터 정렬

ORDER BY는 정렬을 담당하며, 기본값은 ASC(오름차순)이다. 정렬기준이 여러개이면, 콤마로 계속 기입한다.

SELECT * FROM 테이블명 ORDER BY 속성1 [ASC/DESC], 속성2 [ASC/DESC];

🙌 그룹화 조건 : GROUP BY & HAVING

그룹화를 하려면 WHERE 대신 GROUP BY로 그룹화할 속성을 정한 뒤, 이어서 개수세기 등 그룹화 조건을 HAVING에 써준다.

그룹화는 COUNT, AVG 같은 집단함수와 함께 잘 쓰인다.

ex <수강> 테이블에서 2명 이상 등록한 과목의 과목코드와 학생 수 검색하기

SELECT 과목코드, COUNT(*) AS 학생수 FROM 수강 GROUP BY 과목코드 HAVING COUNT(*) >= 2;

🙌 부속질의 - WHERE 안에 또다른 SELECT문

🙌 통합질의 - 두 테이블의 합집합을 구함. UNION, EXCEPT

🙌 조인질의 - 두 테이블을 외래키를 사용하여 합치고 조건에 맞는 결과를 보여줌

SELECT a.학번, a.성명, b.과목코드 FROM 학생 a JOIN 수강 b ON (a.학번 = b.학번) WHERE a.학과 = '전기';

SELECT a.학번, a.성명, b.과목코드 FROM 학생 a, 수강 b WHERE a.학번 = b.학번 AND a.학과 = '전기';

🙌 INSERT - 데이터베이스에 새로운 튜플을 삽입하는 명령문. 모든 속성에 대해 값 입력시, 속성명 명시 필요없음.

VALUES 대신 부속질의로 SELECT 결과를 넣을 수도 있다.

INSERT INTO 테이블명(속성1, 속성2, ... ) VALUES (속성1의 값, 속성2의 값, ...)

INSERT INTO 졸업예정자(학번, 성명, 학과) SELECT 학번, 성명, 학과 FROM 학생 WHERE 학년=4;

🙌 DELETE - 특정 튜플을 삭제하는 명령문. 모든 튜플 삭제 시 WHERE 이하를 생략한다(테이블 자체는 삭제 안되므로 INSERT로 다시 삽입 가능. 단, DROP TABLE을 사용하면 스키마 자체가 다 사라지므로 CREATE TABLE부터 다시 필요함).

DELETE FROM 테이블명 WHERE 조건;

🙌 UPDATE - 특정 튜플의 내용을 업데이트하는 명령어

UPDATE 테이블명 SET 변경후내용 WHERE 변경을실행할조건;