[SQLD][책없이 공부]2과목 01. SQL 기본_03 DML

4. DML (Data Manipulation Language)

- 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업 -> 실시간으로 DB가 영향 X => commit으로 트랜잭션 종료해야 함

- sql server는 DML도 기본 auto commit

1) INSERT 문

(1) 기본 INSERT 문

- 테이블에 데이터를 입력하는 DML문

INSERT INTO 테이블명 (속성명, 속성명...) VALUES (속성값, 속성값 ...);

- 문자열 입력 시 작은 따옴표 사용 ' '

- 특정 테이블의 모든 칼럼(속성)에 삽입하는 경우 속성명 생략 가능

INSERT INTO EMP VALUES(모든 속성에 넣을 값);

- Auto Commit(Set auto commit on)으로 설정된 경우 COMMIT 실행하지 않아도 바로 저장됨

(2) SELECT 문을 이용한 INSERT문

- SELECT문으로 데이터를 조회해서 해당 테이블에 삽입하는 방법

INSERT INTO DEPT_TEST  SELECT * FROM DEPT;

미리 정의된 DEPT 테이블의 튜플(모든 속성을 포함하는)을 가져와서 DEPT_TEST테이블에 INSERT

(3) Nologging 사용

- Nologging 옵현은 로그파일의 기록을 최소화시켜서 입력 시 성능을 향상시키는 방법

- 데이터베이스에 데이터를 입력하면 로그파일에 그 정보를 기록함

  Check point 라는 이벤트가 발생하면 로그파일의 데이터를 데이터 파일에 저장

- Buffer Cache 라는 메모리 영역을 생략하고 기록함

ALTER TABLE DEPT NOLOGGING;

2) UPDATE 문

- 입력된 데이터 값 수정 / 원하는 조건을 검색해서 해당 데이터를 수정

- 조건문을 입력하지 않으면 테이블의 모든 데이터가 수정됨

- 데이터를 수정할 떄 조건절이 나오는 행 수만큼 수정됨

UPDATE 테이블명 SET 속성명 = 바꿀 속성값, 속성명2 = 바꿀 속성값2  WHERE 조건;

3) DELETE 문

- 조건을 검색해서 해당되는 행을 삭제

- 조건문을 입력하지 않으면 테이블의 모든 데이터가 삭제됨

- DELETE문으로 데이터를 삭제한다고 해서 테이블의 용량이 초기화 되는 것은 아님!!!

DELETE FROM 테이블명 WHERE 조건문;

* 테이블 용량에 관하여

- Extent : 최대로 저장할 수 있는 공간 =>  Extent의 크기가 max_extents를 넘어서게 되면 용량 초과 오류가 발생

- Oracle 데이터베이스는 저장공간을 할당할 떄 Extent 단위로 할당하고, 테이블에 데이터가 입력되면 이 곳에 저장하게 됨

- DELETE 문은 삭제 여부만 표시하고 용량은 초기화 하지 않으므로 오류가 발생할 수 있음

* 테이블의 모든 데이터 삭제!!

TRUNCATE TABLE 테이블명;	DELETE TABLE 테이블명;
- 테이블의 모든 데이터를 삭제함  - 데이터가 삭제되면 테이블의 용량은 초기화됨 - 삭제된 데이터 로그가 없어 oracle에서는 rollback 불가능   (sql server는 롤백 가능) - DDL 취급 함	- 테이블의 모든 데이터를 삭제함  - 데이터가 삭제되어도 테이블의 용량은 감소하지 않음

4) SELECT 문

(1) SELECT문 사용

- 테이블에 입력된 데이터를 조회하기 위해 사용

- 특정 칼럼이나 특정 행만을 조회할 수도 있고 모두 조회할 수도 있음

SELECT 조회할 속성명 FROM 조회할 테이블명  WHERE 조건절 ;

- 조회할 속성명에 * 를 넣으면 모든 속성을 출력  - WHERE절 생략시 모든 행 출력 / 조건문이 있을 시 조건에 만족하는 행 출력

* SELECT 칼럼 지정

SELECT ENAME || '님' FROM EMP;

- EMP 테이블에서 ENAME을 조회함  - ENAME 칼럼 뒤에 '님' 이라는 문자를 결합

(2) Order by를 사용한 정렬

- Order by : 오름차순 ASC (DEFAULT) / 내림차순 DESC

- Order by가 정렬을 하는 시점은 모든 실행이 끝난 뒤 데이터를 출력해 주기 바로 전임

- Oracle DB는 정렬을 위해 메모리 내부에 할당된 SORT_AREA_SIZE를 사용함 (SORT_AREA_SIZE가 너무 작으면 성능 저하 발생)

  즉, Order by는 메모리를 많이 사용함

SELECT * FROM 테이블명 ORDER BY 속성명 DESC, 속성명2 ;

- 기본 속성(생략했을 시)은 오름차순 ASC

(3) INDEX를 사용한 정렬 회피

- ORDER BY는 DB에 부하를 주므로 INDEX를 사용해 ORDER BY 회피 가능

- 기본키 속성을 이용하면 기본키를 따라 자동으로 오름차순 인덱스가 생성됨

- 인덱스 힌트를 주면 ORDER BY 사용하지 않고도 정렬 가능

SELECT /*+ INDEX_DESC(A) */ FROM EMP A;

/*+ INDEX_DESC(A) */  <- 요 부분이 인덱스 힌트.   EMP테이블에 생성도니 인덱스를 내림차순으로 읽게 지정한 것

(4) Distinct 와 Alias

① Distinct

- 칼럼명 앞에 지정하여 중복된 데이터를 한 번만 조회하게 함

SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO;

② Alias

- 별칭! 테이블명이나 칼럼명이 너무 길어서 간략하게 할 때 사용 -> 레이블 명 설정

- 칼럼 명 바로 뒤에 옴

- 칼럼명과  Alias 사이에 as 키워드 사용하기도 함

- 이중 인용 부호 : 공백 특수 문자 포함하거나 대소문자 구별해야 하는 Alias 일 때 사용

SELECT ENAME AS "이름" FROM EMP A WHERE A.EMPNO=1000;

- ENAME AS "이름" -> ENAME 속성을 '이름'으로 간략화  - EMP A -> EMP롤 A로 간략화 => A.EMPNO 는 EMP.EMPNO와 같음

(5) 연산자

- 연산자를 사용하여 Select 문의 칼럼을 조회 가능

① 산술 연산자

- ( ) , * , / , + , - 순으로 연산자 우선순위 적용

- 기존의 칼럼에 사용하여 칼럼 자체를 연산하여 표시 -> 칼럼명이 길어지므로 보통 Alias 사용 

SELECT PNAME, HEIGHT-WEIGHT AS 비중 FROM PLAYERLIST;

② 합성 연산자 (CONCATENATION)

- mysql : || / oracle : + / sql server concat(string1, strin2);

- 문자열에 맞는 새 칼럼을 형성함

5. WHERE 문 사용

① 두 개 이상의 테이블에 대한 조인 조건 기술

② 결과 제한을 위한 조건 기술

1) where문이 사용하는 연산자

- 연산자 우선 순위 ( ) -> NOT -> 비교 -> AND -> OR

* 비교 연산자 : =, <, <=, >, >=

* 부정 비교 연산자 : !=, ^=, <>, NOT 칼럼명 = , NOT 칼럼명 > 등 연산자 같지 않다!

* 논리 연산자

- AND : 조건으 모두 만족해야 참. / OR : 조건 중 하나만 만족해도 참, / NOT : 참이면 거짓, 거짓이면 참으로 바꿈

* sql 연산자 / 부정 sql 연산자

sql 연산자	부정 sql 연산자
Between A And : a와 b 사이의 값 조회	NOT Between A And B : a와 b 사이의 값 아닌 값 조회
IN (list) : or 의미. list 값 중 하나만 일치해도 조회 됨	NOT IN (list) : list와 불일치 하는 것 조회
IS NULL : null값 조회	IS NOT NULL : null값 아닌거 조회
Like '비교문자열' : Like문에 와일드카드 사용해서 조회

2) LIKE문 사용

* 와일드카드

%	- 어떤 문자를 포함한 모든 것을 조회
_(언더바)	- 한 개의 단일 문자 조회

3) NULL 값 조회

(1) NULL

- 아직 정의되지 않은 값

- 모르는 값, 값의 부재 => 0 이나 공백과는 다름 (0은 숫자, 공백은 문자)

- 모든 비교 -> 알 수 없음 반환

- 숫자, 날짜 연산 시 -> NULL 반환

- 비교 연산자로 비교 시 -> 거짓 (FALSE)반환 (ISNULL()  ,ISNOTNULL() 로 연산해야 함)

*NULL 관련 함수

- 산술 데이터에서 값이 NULL일 경우 유용하게 사용

- 칼럼 간 계산 수행 시 NULL값 존재하면 해당 연산 결과가 NULL값이 되므로 함수 사용해 원하는 데이터를 얻어야 함

NVL (표현식1, 표현식2) ISNULL (표현식1, 표현식2)	- NULL이면 다른 값으로 바꿈  - 표현식1 결과가 NULL이면 표현식 2값 출력 (표현식간 데이터타입 일치)
NVL2 (표현식1, 표현식2, 표현식2)	- NVL 함수와 DECODE를 합침  - 표현식1이 NULL이 아니면 표현식2, NULL이면 표현식3 리턴
NULLIF (표현식1, 표현식2)	- 두 개의 값이 값으면 NULL, 다르면 첫번째 값 반환  -표현식1 = 표현식2 -> NULL리턴, 표현식1 != 표현식2 -> 표현식1 리턴
COALESCE (표현식1, 표현식2 ...)	- 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄   - COALESCE(mrg, 1) -> mgr이 NULL이 아니면 1 반환   - 모든 표현식이 NULL이라면 NULL 리턴

- ORACLE -> NVL 함수 사용 / SQT SERVER -> ISNULL 사용

- 공집합 : NVL ISNULL 사용해도 공집합이 출력 됨 -> 이 함수들은 공집합을 대상으로 하는 함수가 아님

그룹함수(적절한 집계함수)와 NVL함수를 함께 사용하여야 함

6. GROUP 연산

1) GROUP BY 절

- 테이블에서 소규모 행을 그룹화하여 합계, 평균, 최대값, 최소값 등 계산

- FROM 절과 WHERE절 뒤에 옴

- ALIAS 사용 불가

- 집계함수를 사용할 수 있는 GROUP BY 절보다 WHERE절이 먼저 수행됨

- WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킴

2) HAVING 절

- GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시함

- GROUP BY절 뒤에 위치

- GROUP BY에 의한 소그룹별로 만들어진 집계 데이터 중 HAVING 절에 제한 조건을 두어 만족하는 내용만 출력

* 사용 예시

SELECT 속성명1, SUM(속성명2) FROM 테이블명 GROUP BY 속성명1 HAVING SUM(속성명2) > 1000 ORDER BY 속성명1;

- HAVING 구에 조건문 사용. (그룹 조건) WHERE절에 조건문을 사용하면 GROUP BY 대상에서 제외됨(전체 조건)  - ORDER BY를 사용해 정렬 가능

- GROUP BY 소그룹 데이터 중 일부만 필요한 경우

 ① GROUP BY 연산 전 WHERE 절에서 조건 적용해 데이터 추출 후 GROUP BY연산 진행

 ② GROUP BY 연산 후 HAVING 절에서 필요한 데이터만 필터링

- ORDER BY : SELECT절에서 단 한번 맨 마지막에 올 수 있음

* 집계 함수

- HAVING절, SELECET절, ORDER BY절에서 사용 가능 (WHERE 절에 사용 불가능)

- 집계함수의 통계 정보는 NULL값을 가진 행 제외하고 수행

- 입력값이 전부 NULL인 경우만 함수 결과가 NULL이 나옴

COUNT( )	행 수 조회	COUNT(*) : NULL값 포함 모든 행수 계산 / COUNT(컬럼명) : NULL제외 그 컬럼의 행수 계산
SUM( )	합계 계산	AVG( )	평균 계산	MAX( )	최대값
STDDEV( )	표준편차	VARIAN( )	분산	MIN( )	최소값

* 예시

사원번호 1000~1003번의 부서별 급여 합계

SELECT DEPTNO, SUM(SALARY) FROM EMP WHERE EMPNO BETWEEN 1000 AND 1003 GROUP BY DEPTNO;

- 사원번호는 전 사원을 대상으로 조회하는 것이므로 GROUP 안에서의 조건이 아님. 전 사원중 1000~1003번을 그룹으로 나누는 것! 그래서 HAVING 이 아닌 WHERE임

7. SELECT문 실행 순서

SELECT ENAME FROM EMP WHERE EMPNO=10 GROUP BY ENAME HAVING COUNT(*)>=1 ORDER BY ENAME;	- SQL의 SYNTAX, SEMANTIC 에러 점검하는 순서와 일치함  FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY 순으로 실행됨
- FROM : 발췌 대상 테이블 참조 - WHERE : 발췌 대상 데이터가 아닌 것 제거 - GROUP BY : 행 소그룹화	- HAVING : 그룹핑된 값의 조건에 맞는 것만 출력 - SELECT : 데이터 값 출력 / 계산 - ORDER BY : 데이터 정렬

8. 명시적 형변환과 암시적 형변환

- 형변환 : 두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것

- 명시적 형변환 : 형변환 함수를 사용하여 데이터 타입을 일치시키는 것. 개발자가 SQL사용할 때 형변환 함수 사용

* 형변환 함수

TO_NUMBER(문자열)	문자열을 숫자로 변환
TO_CHAR(숫자 혹은 날짜, [FORMAT])	숫자 혹은 날짜를 지정된 FORMAT형태의 문자로 변환
TO_DATE(문자열, FORMAT)	문자열을 지정된 FORMAT의 날짜형으로 변환

- 암시적 형변환 : 개발자가 형변환을 하지 않은 경우 데이터베이스 관리 시스템이 자동으로 형변환 하는 것