[SQLD] Chapter 3. SQL 기본

- 데이터베이스란?

: 특정 기업이나 조직 또는 개인이 필요에 의해(예: 부가가치가 발생하는) 데이터를 일정한 형태로 저장해놓은 것.

 

 

- DBMS?

: 데이터베이스 관리 소프트웨어

 

- SQL?

: RDBMS 데이터를 관리하기 위해 설계된 프로그래밍 언어

 

- 관계형 데이터베이스

: 데이터를 테이블 형태로 저장해놓은 것.

 

• 장점
  • 정규화를 통해 이상현상을 제거하고 데이터 중복을 피할 수 있다.
  • 동시성 관리, 병행 제어를 통해 데이터를 공유한다.
  • 데이터의 표현 방법 등을 체계화할 수 있고, 데이터 표준화할 수 있다.
  • 보안 기능
  • 데이터 무결성을 보장한다.
  • 데이터를 회복/복구하는 기능

 

- SQL

• 데이터 조작어(DML)
  • SELECT, INSERT, UPDATE, DELETE
• 데이터 정의어(DDL)
  • CREATE, ALTER, DROP, RENAME
• 데이터 제어어(DCL)
  • GRANT, REVOLKE
• 트랜잭션 제어어(TCL)
  • COMMIT, ROLLBACK

 

 

- 테이블(Table)

: 데이터를 저장하는 객체(Object)로서 관계형 데이터베이스의 기본 단위

 

 

- 테이블의 구조

• 칼럼/열 : 2차원  구조를 가진 테이블에서 세로 방향으로 이루어진 하나하나의 특정 속성
• 행 : 2차원 구조를 가진 테이블에서 가로 방향으로 이루어진 연결된 데이터

 

- 테이블 관계 용어

• 정규화(Normalization) : 테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스
• 기본키(Primary Key) : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 칼럼
• 외부키(Foreign Key) : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼

 - ERD(Entity Relationship Diagram)

: ERD의 구성요소는 엔터티(Entity), 관계(Relationship), 속성(Attiribute) 3가지

 

 

---

 

 

- 데이터 유형

• 숫자 타입
  • NUMERIC, DECIMAL, DEC, SMALLINT, INTEGER, INT, BIGINT, FLOAT, REAL, DOUBLE...
• 문자 타입
  • CHAR : 가변 길이
  • VARCHAR : 고정 길이
  • CHAR 에서는 문자열을 비교할 때 공백을 채워서 비교하고 VARCHAR 유형에서는 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급한다. 예) CHAR 유형 'AA' = 'AA '  vs VARCHAR 유형 'AA' ≠ 'AA '

 

- 제약조건(CONSTRAINT)

: 데이터 무결성을 유지하기 위한 데이터베이스의 보편적인 방법

도메인 제약조건, 키 제약조건, 무결성 제약조건

 

• 종류 
  • PRIMARY KEY(기본키)
  • UNIQUE KEY(고유키) : 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. NULL 값 허용
  • NOT NULL
  • CHECK : 입력할 수 있는 값의 범위 등을 제한한다. 
  • FOREIGN KEY(외래키)

- NULL 의 의미 : 조건에 맞는 데이터가 없을 때의 공집합과도 다르다. 아직 정의되지 않은 미지의 값, 또는 현재 데이터를 입력하지 못하는 경우를 의미한다.

- DEFAULT 의 의미 : 데이터 입력 시에 칼럼의 값이 지정되어 있지 않을 경우 기본값

 

 

- DDL

: 생성, 변경, 수정

 

1. CREATE TABLE : CREATE TABLE copy_student AS SELECT * FROM student;
2. ALTER TABLE
   1. ADD COLUMN : ALTER TABLE copy_student ADD (address VARCHAR2(80));
   2. DROP COLUMN : ALTER TABLE copy_student DROP COLUMN address;
   3. MODIFY COLUMN : ALTER TABLE copy_student MODIFY (univ_name VARCHAR2(20) DEFAULT '숙명여자대학교' NOT NULL);
   4. RENAME COLUMN : ALTER TABLE copy_student RENAME COLUMN univ_name TO university);
   5. DROP CONSTRAINT : ALTER TABLE copy_student DROP CONSTRAINT sub_fk;
   6. ADD CONSTRAINT : ALTER TABLE copy_student ADD CONSTRAINT sub_fk FOREIGN KEY (sub_no) REFERENCES subject(sub_no);
3. RENAME TABLE : RENAME copy_student TO cp_student;
4. DROP TABLE : DROP TABLE copy_student CASCADE CONSTRAINT; (* cascade 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.)
5. TRUNCATE TABLE : TRUNCATE TABLE copy_student; (* 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조까지 완전히 삭제하기 위해서는 DROP TABLE 을 실행)

 

테이블 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE 을 사용하는 것이 더 낫다. 단, TRUNCATE TABLE 의 경우 정상적인 복구가 불가능하므로 주의해야한다. 

 

 

 

---

 

 

- DML

: 입력, 수정, 삭제, 조회 명령어

 

1. INSERT : INSERT INTO student VALUES (1510599, '김땡땡', '컴퓨터과학', 1); 
2. UPDATE : UPDATE student SET stu_grade = 4 WHERE stu_no = 1510599; 
3. DELETE : DELETE FROM student; (*student 테이블 삭제)
4. SELECT : SELECT * FROM student;

 

• DROP vs TRUNCATE vs DELETE 

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML 성격 가짐)	DML
Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
Auto Commit	Auto Commit	사용자 Commit
테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage 중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음
테이블의 정의 자체를 완전히 삭제함	테이블을 최초 생성된 초기상태로 만듬	테이터만 삭제

* 시퀀스가 있을 때 truncate는 시퀀스번호가 초기화되지만 delete는 그렇지 않다.

 

 

---

 

- 트랜잭션이란?

• 데이터베이스의 논리적 연산단위 
• 트랜잭션은 분할할 수 없는 최소의 단위이다. (전부 적용하거나 전부 취소/ ALL OR NOTHING)
• 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미한다.
• 하나의 트랜잭션에는 하나 이상의 SQL문장이 포함된다.

- 트랜잭션의 특징

• 원자성(Atomicity) : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야한다.
• 일관성(Consistency) : 트랜잭션이 실행되기 전 데이터베이스에 이상이 없다면 실행 후에도 이상이 없어야한다.
• 고립성(Isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
• 지속성(Durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. 

- 트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

• Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않는 데이터를 읽는 것을 말한다.
• Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상을 말한다.
• Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다.

- TCL

: 트랜잭션을 컨트롤하는 TCL

1. COMMIT : 입력, 수정, 삭제한 자료에 대해서 문제가 없을 경우 트랜잭션을 완료한다.
2. ROLLBACK : 입력, 수정, 삭제한 데이터에 대해서 커밋 이전에 변경 사항을 취소할 수 있는 기능이다.
3. SAVEPOINT : 저장점. 저장점을 정의함으로써 롤백할 때 전체 롤백이 아닌 일부만 롤백할 수 있다. 

COMMIT 과 ROLLBACK 을 사용함으로써 데이터 무결성을 보장하고, 영구적인 변경을 하기 전에 데이터의 변경 사항을 확인할 수 있으며, 논리적으로 연관된 작업을 그룹화하여 처리 가능하다.

 

 

---

 

 

- WHERE 절

 

- ROWNUM , TOP

: where 절에서 행의 개수를 제한하는 목적으로 사용한다.

예) SELECT student_name FROM student where ROWNUM = 1; SELECT TOP(1) FROM student;

 

두 건 이상의 N개의 행을 가져오고 싶은 경우 SELECT student_name FROM student WHERE ROWNUM <= 2; (X) 이렇게 사용할 수 없다.

 

SELECT TOP(N) WITH TIES ~ : WITH TIES 키워드를 사용하면 동일한 값도 포함하여 출력한다.

 

 

---

 

 

- 내장함수

 

 

- 문자형 함수

 

dual 테이블은 데이터 딕셔너리와 함께 오라클에서 자동으로 생성되는 테이블입니다. 사용자 sys가 소유하며 모든 사용자가 액세스 가능한 테이블입니다. 

 

- 날짜형 함수

 

 

- 변환형 함수

 

 

 

 

- CASE 표현

: IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할을 한다. 

 

IF SAL > 2000

    THEN revised_salary = sal

    ELSE revised_salary = 2000

END-IF.

 

SELECT ename, 

             CASE 

             WHEN sal > 2000

             THEN sal

             ELSE 2000

             END resvised_salary

 

 

 

- NVL/ ISNULL 함수

 

SELECT NVL(mgr, -1) mgr

FROM employees

 

SELECT * FROM employees WHERE mgr IS NULL;

 

- NULLIF 

: NULLIF 함수는 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다. 특정 값을 NULL로 대체하는 경우에 유용하게 사용할 수 있다.

NULLIF(표현식1, 표현식2)

 

- COALESCE

: 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다. 

COALESCE (표현식1, 표현식2, ... )

 

일반형 함수	함수 설명
NVL(표현식1, 표현식2)-Oracle함수/  ISNULL(표현식1, 표현식2)-SQL Server함수	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야한다. NULL관련 가장 많이 사용되는 함수이므로 상당히 중요하다.
NULLIF(표현식1, 표현식2)	표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴한다.
COALESCE(표현식1, 표현식2, ....)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다.

 

 

---

 

 

- 집계함수

: select 절, having 절, order by 절에 사용가능하다. 

 

⭐️⭐️⭐️NULL값을 포함하는 지 안하는 지

 

- GROUP BY 절

: FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 사용한다. 

• GROUP BY 절과 HAVING 절의 특성
  • 집계 함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행한다.
  • GROUP BY 절에서는 SELECT 절과는 다르게 ALIAS 명을 사용할 수 없다. 
  • WHERE 절은 전체 데이터를 GROUP 으로 나누기 전에 행들을 미리 제거한다.

- HAVING 절

: 집계함수를 조건으로 하여 데이터를 조회하고 싶을 때 사용한다. 

 

 

- ORDER BY 정렬

: 칼럼명 대신 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능. 기본적으로 오름차순 정렬(ASC)

 

- JOIN