3-3 DML

- 좌측 정렬 : 문자 및 날짜 데이터

- 우측 정렬 : 숫자 데이터

앞 절에서 테이블을 생성하고 생성된 테이블의 구조를 변경하는 명령어에 대해서 알아보았다. 지금부터는 만들어진 테이블의 관리할 자료들을 입력/수정/삭제/조회하는 DML(DATA MANIPULATION LANGUAGE) 사용 방법을 알아보자.

DB는 DDL 명령어와 DML 명령어를 다르게 처리한다. DDL(CREATE, ALTER, RENAME, DROP) 명령어는 직접 DB 테이블에 영향을 미치기 때문에 DDL 명령어를 입력하는 순간 명령어에 해당하는 작업이 즉시 완료(AUTO COMMIT)된다. 하지만 DML(INSERT, UPDATE, DELETE, SELECT) 명령어는, 조작하려는 테이블을 메모리 버퍼에 올리고 작업하기 때문에 테이블에 실시간으로 영향을 미치지는 않는다. 따라서 버퍼에서 처리한 DML 명령어가 실제 테이블에 반영되기 위해서는 COMMIT 명령어를 입력하여 TRANSACTION을 종료해야 한다. 하지만 SQL Server의 경우 DML 명령어도 DDL 명령어처럼 AUTO COMMIT 처리되므로 COMMIT 명령어가 필요없다. 

INSERT

테이블에 데이터를 입력할 때는 해당 칼럼명과 입력값을 서로 1:1로 매핑한다. 칼럼의 데이터 유형이 CHAR나 VARCHAR2 등 문자 유형일 경우 싱글 쿼터(') 안에 값을 입력한다. 숫자이면 필요 없다.

1. INSERT INTO ~ VALUES ~

a 테이블의 b 칼럼에 c라는 데이터를 넣고 싶다면 a, b, c 순으로 입력해서 데이터를 넣는다. 이 때 칼럼의 순서는 테이블의 칼럼 순서와 같지 않아도 되고 정의하지 않은 칼럼은 기본값으로 NULL이 입력된다. 단 PK나 Not NULL로 정의된 칼럼은 허용되지 않는다.

INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST);

만약 특정 테이블의 모든 칼럼에 데이터를 입력할 때는 칼럼명을 생략한다. 이 때는 칼럼 순서대로 빠짐없이 데이터가 입력되어야 한다.

INSERT INTO 테이블명 VALUES(전체 COLUMN에 넣을 VALUE_LIST);

INSERT 문을 실행했다고 데이터 파일에 저장되는 것은 아니고 최종적으로 TCL문인 Commit을 실행해야 한다. 

2. INSERT INTO ~ SELECT ~ FROM ~;

B 테이블의 모든 데이터를 A 테이블에 삽입하고자 한다면 SELECT 문을 사용해 B 테이블을 조회해서 바로 삽입할 수 있다. 물론 B 테이블을 사전에 생성되어 있어야 한다.

INERT INTO A
	SELECT * FROM B;

* Nologging 옵션

DB에 데이터를 입력하면 로그파일에 그 정보를 기록한다. Check point라는 이벤트가 발생하면 로그파일 데이터를 데이터 팡리에 저장한다. Nologging 옵션은 로그파일의 기록을 최소화시켜 입력 시 성능을 향상시키는 방법으로, Buffer Cache라는 메모리 영역을 생략하고 기록한다.

ALTER TABLE 테이블명 NOLOGGING;

---

[예제] 선수 테이블에 박지성 선수의 데이터를 일부 칼럼만 입력한다. 테이블명 : PLAYER

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);

 

아래 표는 DB 내의 PLAYER 테이블에 박지성 선수 정보가 입력된 것을 나타낸 것이다. INSERT 문장에서 BACK_NO가 마지막에 정의가 되었더라도 테이블에는 칼럼 순서대로 데이터가 입력되었다. 칼럼명이 정의되지 않은 경우 NULL 값이 입력되었다.

---

[예제] 해당 테이블에 이청용 선수의 데이터를 입력한다.

*테이블을 생성할 때 NATION 칼럼을 빼먹었다.. 테이블 변경이 필요하다ㅠ

1) 먼저 빼먹은 NATION 칼럼을 추가한다: ALTER TABLE ~ ADD ~;

2) tmp 테이블을 select 문을 이용해 칼럼 순서를 정해서 생성한다: CREATE TABLE ~ AS SELECT ~ FROM ~;

3. PLAYER 테이블을 삭제하고 tmp 테이블의 이름을 PLAYER로 바꾼다: DROP TABLE ~; RENAME ~ TO ~;

이제 다시 예제를 보자. 아래 SQL문을 실행해 이청용 선수의 데이터를 넣는다.

INSERT INTO PLAYER VALUES ('2002010','이청용','K07','','BlueDragon','2002','MF','17',NULL, NULL,'1',180,69); 

 

데이터를 입력하는 경우 정의되지 않은 값인 E_PLAYER_NAME은 두 개의 『 '' 』 싱글 쿼터를 붙여서 표현하거나, NATION이나 BIRTH_DATE의 경우처럼 NULL이라고 명시적으로 표현할 수 있다.

UPDATE

입력한 정보 중에 잘못 입력되거나 정보를 수정해야 하는 경우엔 UPDATE문을 사용한다. UPDATE 문장의 기본 형태는 "UPDATE 테이블명 SET 수정할 칼럼명 = 새로운 값;"이다. 하지만 이는 특정 칼럼의 모든 값을 (새로운 값)으로 수정하는 것이기 때문에 조건을 추가해주어야 한다. 

UPDATE 테이블명 SET 수정할 칼럼명 = 수정을 원하는 새로운 값 WHERE 조건;

데이터를 수정할 때 조건절이 나오는 행 수만큼 수정되는 것을 유의해야 한다. 조건에 검색되는 로우가 2개라도 조건절이 1개면 그 2개 데이터 모두 같은 값으로 수정된다. 

---

[예제] 선수 테이블의 백넘버를 일괄적으로 99로 수정한다.

UPDATE PLAYER SET BACK_NO = 99;

 

---

[예제] 선수 테이블의 포지션을 일괄적으로 ‘MF’로 수정한다.

UPDATE PLAYER SET POSITION = 'MF';

 

DELETE

테이블의 정보가 필요없을 경우 데이터를 삭제한다. 이 때 FROM 문구는 생략이 가능하며, WHERE 절을 사용하지 않으면 테이블의 전체 데이터가 삭제된다. 하지만 용량이 초기화되지는 않는다.

DELETE [FROM] 삭제할 테이블명;

---

[예제] 선수 테이블의 데이터를 전부 삭제한다.

DELETE PLAYER;

 

*용량이 초기화되지 않는다는 의미

Oracle DB는 Extent 단위로 저장공간을 할당하고 데이터가 입력되면 Extent에 저장된다. 이 Extent는 최대로 저장할 수 있는 크기인 MAX_EXTENTS를 가지고 이를 넘어서게 되면 용량 초과 오류가 발생한다. DELETE문은 데이터만 삭제할 뿐 용량은 초기화되지 않는다. 

DELETE FROM 테이블명;	TRUNCATE TABLE 테이블명;
테이블의 모든 데이터를 삭제하지만 테이블의 용량은 감소하지 않는다.	테이블의 모든 데이터를 삭제하고 용량을 초기화한다.

테이블의 전체 데이터를 삭제하는 경우 시스템 활용 측면에서는 삭제된 데이터를 로그로 저장하는 DELETE TABLE보다, 시스템 부하가 적은 TRUNCATE TABLE을 권고한다. 단 TRUNCATE TABLE의 경우 삭제된 데이터의 로그가 없으므로 ROLLBACK이 불가능하다. 하지만 SQL Server의 경우 사용자가 임의적으로 트랜잭션을 시작한 후 TRUNCATE TABLE을 이용해 데이터를 삭제한 이후 오류가 발견돼 다시 복구를 원하면, ROLLBACK 문을 통해 테이블 데이터를 원상태로 복구할 수 있다. 

SELECT

1. SELECT 문 사용하기

앞에서 입력한 자료들을 조회해보는 SQL 문은 다음과 같다. 

SELECT 조회할 데이터 FROM 테이블명 WHERE 조회조건;

2. ALL, WILDCARD(*)와 DISTINCT

해당 테이블의 모든 칼럼 정보를 보고 싶을 경우에는 ALL 옵션을 사용한다. Default 옵션이므로 별도로 표시하지 않아도 된다. 중복된 데이터가 있어도 모두 출력한다.

SELECT ALL POSITION FROM PLAYER;

ALL은 생략 가능한 키워드이므로 아래 SQL 문장도 같은 결과를 출력한다.

SELECT POSITION FROM PLAYER;

와일드카드로 *(애스터리스크)를 사용하여 조회할 수도 있다.

SELECT * FROM 테이블명;

---

[예제] 입력한 선수들의 정보를 모두 조회한다.

SELECT * FROM PLAYER;

 

중복된 데이터를 한 번만 출력할 때는 DISTINCT 옵션을 사용한다.

SELECT DISTINCT 칼럼명 FROM 테이블명;

 

실행 결과 화면을 보면 칼럼 레이블LABLE이 맨 위에 보이고, 레이블 밑에 점선이 보인다. 실질적인 자료는 다음 줄부터 시작된다. 레이블은 기본적으로 대문자로 보이고, 첫 라인에 보이는 레이블의 정렬은 다음과 같다.

3. Ordery by를 사용한 정렬

Order by절은 아래 포스팅을 참고하자.

3-8 ORDER BY 절

4. ALIAS

조회된 결과에 일종의 별명(ALIAS, ALIASES)을 부여해서 칼럼 레이블을 변경할 수 있다. 칼럼 별명ALIAS에 대한 사항을 정리하면 다음과 같다.

SELECT 칼럼명 [AS] 별칭 FROM 테이블명;

• 칼럼명 바로 뒤에 온다.
• 칼럼명과 ALIAS 사이에 AS, as 키워드를 사용할 수도 있다.
• (option) ""(Double quotation)는 ALIAS가 공백, 특수문자를 포함하거나 대소문자 구분이 필요할 경우 사용된다.

---

[예제] TEST_EMP의 데이터가 다음과 같을 때 EMPNO는 사원번호, ENAME은 사원이름으로, 또 사원번호를 내림차순으로 정렬하여 출력한다.

SELECT EMPNO 사원번호, ENAME 사원이름, SAL FROM TEST_EMP ORDER BY EMPNO DESC;

---

[예제] 칼럼 별명을 적용할 때 별명 중간에 공백이 들어가는 경우 『" "』를 사용해야 한다.

산술 연산자와 합성CONCATENATION  연산자

1. 산술 연산자

산술 연산자는 NUMBER, DATE 자료형에 대해 적용되며 일반적으로 수학에서의 사칙 연산과 동일하다. 그리고 우선순위를 위한 괄호 적용이 가능하다. 일반적으로 산술 연산을 사용하거나 특정 함수를 적용하면 칼럼의 LABEL이 길어지고, 기존 칼럼에 대해 새로운 의미를 부여한 것이므로 적절한 ALIAS를 새롭게 부여하는 것이 좋다. 그리고 산술 연산자는 수학에서와 같이 (), *, /, +, - 순의 우선순위를 가진다.

---

[예제] 선수들의 키에서 몸무게를 뺀 값을 출력한다.

SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게" FROM PLAYER;

 

---

[예제] 선수들의 키와 몸무게를 이용해서 BMI 지수를 측정한다. (예제에서 사용된 ROUND() 함수는 반올림을 위한 내장 함수이다), BMI=몸무게(kg)/키(m)/키(m))

SELECT PLAYER_NAME 이름, ROUND(WEIGHT/((HEIGHT/100)*(HEIGHT/100)),2) "BMI 지수" FROM PLAYER;

 

2. 합성 연산자

문자와 문자를 연결하는 합성 연산자를 사용하면 별도의 프로그램 도움 없이 SQL 문장만으로도 유용한 리포트를 출력할 수 있다. 합성 연산자의 특징은 다음과 같다.

• Oracle - 문자와 문자를 연결하는 경우 ||(2개의 수직 바)에 의해 이루어진다.
• SQL Server - 문자와 문자를 연결하는 경우 '+'에 의해 이루어진다.
• 두 벤더 모두 공통적으로 CONCAT (string1, string2) 함수를 사용할 수 있다.
• 칼럼과 문자 또는 다른 칼럼과 연결시킨다.
• 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.

---

[예제] 다음과 같은 선수들의 출력 형태를 만들어 본다.

출력 형태) 선수명 선수, 키 cm, 몸무게 kg 예) 박지성 선수, 176cm, 70kg

SELECT PLAYER_NAME || ' 선수, ' || HEIGHT || 'cm, ' || WEIGHT || 'kg' 체격정보 FROM PLAYER;

 

---

[예제] 출력 칼럼 형태_ 선수 이름(PLAYER_NAME 선수) 키(HEIGHTcm)