SQLD 노랭이 2과목 요점 정리 (SQL 기본 및 활용)

DCL (Data Control Language)

데이터 제어 언어

권한 부여 및 회수 (Grant, Revoke)

 

DDL (Data Definition Language)

데이터 정의어

객체 생성·변경·제거 (Create, Alter, Drop, Rename)

스키마, 도메인, 테이블, 뷰, 인덱스를 정하거나 변경·제거할 때

 

DML (Data Manipulation Language)

데이터 조작어

입력·수정·삭제·조회 (Insert, Update, Delete, Select)

데이터베이스 사용자가 응용프로그램이나 질의어를 통해 저장된 데이터베이스를 접근할 때 사용

 

TCL (Transaction Control Language)

트랜잭션 제어 언어

작업 완료 및 취소 (Commit, Rollback, Savepoint)

(논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위(Transaction) 별로 제어하는 명령어

트랜잭션 종료 후 값이 바뀌지 않는 것은 '원자성'
트랜잭션이 실행되기 전 DB내용이 잘못되어있지 않다면 이후에도 유지되야하는 것은 '일관성'

 

 

 

---

NULL

• 모르는 값을 의미
• 값의 부재를 의미
• Null과의 모든 비교(IS NULL 제외)는 Unknown을 반환
• 공백숫자 or 숫자 0을 의미 (X)
• 공백 문자는 NULL로 출력 (NOT FOUND)
• NULL을 포함한 사칙연산 결과는 NULL (숫자로 0을 나눌 경우 에러발생)

숫자 / 0 = ERROR

숫자 / NULL = NULL

 

PK 생성 방법

/* PK로 지정할 칼럼은 속성이 NOT NULL로 설정되어 있어야 한다. */
/* PK제약조건명은 임의로 지정하면 된다.                       */

CREATE TABLE [테이블명] (필드이름 필드타입 PRIMARY KEY, ... )

ALTER TABLE [테이블명] ADD 필드이름 필드타입 PRIMARY KEY
ALTER TABLE [테이블명] ADD CONSTRAINT [PK제약조건명] PRIMARY KEY 필드이름

 

[EMP 테이블에서 COMM 칼럼을 삭제하는 문제 정답]
ALTER TABLE EMP DROP COLUMN COMM;

 

 

 

제약조건

NOT NULL

• NULL값 입력불가

PRIMARY KEY

• NOT NULL이면서 UNIQUE
• 반드시 테이블당 하나의 제약만 정의 가능

FOREIGN KEY

• 입력되어야 할 값이 다른 테이블의 칼럼값을 참조 (참조무결성 보장)

UNIQUE

• 칼럼값이 해당 테이블 전체에서 유일한 값 (동일한 Value 해당되지 않음)
• 저장된 모든 칼럼들은 NULL 입력 가능 (NULL값을 가질 수 없다는 문제 자주 출제)

CHECK

• 해당 조건을 만족하는 값만 칼럼에 입력 가능
• 데이터베이스에서 데이터의 무결성을 유지하기 위해 테이블의 특정 칼럼에 설정

 

 

 

---

ON INSERT (자식 테이블의 행이 입력될 때)	AUTOMATIC	부모가 없을 때 부모 입력 후 자식 입력
	SET NULL	부모가 없는 경우 자식의 PK를 NULL
	SET DEFAULT	부모가 없는 경우 FK는 기본값
	DEPENDENT	부모의 PK가 있는 경우만 자식 입력
ON DELETE (부모 테이블의 행이 삭제될 때)	CASCADE	부모 삭제시 자식도 삭제
	SET NULL	부모 삭제시 자식은 NULL로 설정
	SET DEFAULT	부모 삭제시 자식은 기본값 설정
	RESTRICT 제한	자식이 없는 경우만 부모 삭제

---

출처 : wikidocs

- DELETE는 테이블의 row 단위로 테이블을 삭제하는 DML 특성으로 Rollback 가능 (데이터만 삭제)
- TRUNCATE, DROP은 테이블 단위로 이뤄지는 DDL 특성으로 Rollback 불가능
       - 테이블의 구조와 스키마는 지우지 않고 남기고 싶을 때 TRUNCATE (테이블을 최초 초기상태로)
       - 테이블의 모든 정보를 지울 때만 DROP 사용

Q.25

[매출] 테이블이 너무 많은 디스크 용량을 차지하여 [매출] 테이블에서 필요한 데이터만을 추출해 별도의 테이블로
옮겨놓았다. 이후 [매출] 원본 테이블의 데이터를 모두 삭제함과 동시에 디스크 용량도 초기화하고자 한다.
(단, 테이블의 스키마는 유지)

A. TRUNCATE TABLE 매출;

 

내장함수

• 함수의 입력행 수에 다라 단일행 함수와 다중행 함수로 구분 가능
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에서 사용 가능
• 1:M 관계의 테이블을 JOIN 할 경우 M쪽에 다중행이 출력 (단일행 함수 사용 가능)
• 단일행 함수 = 하나의 결과만 반환 (다중행 함수도 여러 개의 행이 입력되어도 하나의 값만 반환)
• 그룹(집계)함수가 다중행 함수   ex) SUM, AVG, MAX, MIN, COUNT

 

뷰(VIEW)

• 데이터 구조를 단순화
• USER에게는 단순한 인터페이스 제공
• VIEW에서 제공하는 데이터는 이용 가능 (나머지는 불가)
• 테이블 자료에 대한 접근 제한 (보안)
• VIEW를 통해 복잡한 SQL 저장 가능
• 테이블의 구조 변경에 따른 영향으로부터 응용프로그램의 분리 가능
  (응용프로그램 내에서 VIEW를 사용하고 있을 때 변경되면 DB에 존재하는 VIEW를 변경해야 반영) 

DBMS 사용자를 생성하면 기본적으로 많은 권한을 부여해야 한다.
많은 DBMS에서는 DBMS 관리자가 사용자별로 권한을 관리해야 하는 부담과 복잡함을 줄이기 위해 다양한 권한을
그룹으로 묶어 관리하라 수 있도록 사용자와 권한 사이에서 중개역할을 수행하는 ROLE을 제공한다. 

 

SQL [선언적 언어]   select, update, insert 등 데이터 조작쿼리

• 관계형 DB에 저장된 데이터에 access하기 위해 사용하는 표준 언어
• 한 번에 하나의 쿼리만 실행 

 

PL/SQL [절차적 언어]

• SQL문을 사용하여 프로그램을 작성할 수 있도록 확장해놓은 오라클의 절차적 언어
  (DECLARE, EXCEPTION, LOOP 등 절차적 프로그래밍 가능)
• 한 번에  한 블록의 코드 실행을 허용하여 성능 향상
• 사용 가능한 SQL은 Query, DML, TCL
• PL/SQL SELECT문은 해당 SELECT 결과를 PL/SQL ENGINE으로 보낸다.
  (이를 캐치하기 위해 변수를 DECLEAR 해야함, INTO절을 꼭 선언하여 넣을 변수를 표현해야 행 검색 가능

- 변수와 상수 등을 사용하여 일반 SQL 문장을 실행할 때 WHERE절 조건 등으로 대입할 수 있다.
- Procedure, User defined function, Trigger 객체를 PL/SQL로 작성 가능 (트랜잭션별로 프로시저, UDF 호출 가능)
- Procedure 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리 (SQL 문장은 SQL 실행기가 처리)

Q.125 절차형 SQL 모듈에 대한 설명으로 부적절한 것은?

① 저장형 프로시저는 SQL을 Logic과 함께 데이터베이스 내에 저장해놓은 명령문의 집합을 의미한다.
     > CREATE PROCEDURE 명령을 사용해서 생성
② 사용자 정의 함수는 단독적으로 실행되기 보다는 다른 SQL문을 통해 호출되고 그 결과를 리턴하는
     SQL의 보조적인 역할을 한다.   > CREATE FUNCION 명령을 사용해서 생성
③ Trigger는 특정한 테이블에 INSERT, UPDATE, DELETE 같은 DML문이 수행됐을 때 데이터베이스에서
     자동으로 동작하도록 작성된 프로그램이다.   > CREATE TRIGGER 명령을 사용하여 생성
④ 데이터의 무결성과 일관성을 위해 사용자 정의 함수를 사용한다. 
     > Trigger을 사용하여 데이터의 무결성과 일관성을 유지 가능 (또한 TCL을 이용해 제어 불가)