2절. DDL
1. 데이터 유형
CHARACTER - 고정길이 문자열 정보
VARCHAR - 가변길이 문자열 정보
NUMERIC - 정수, 실수 등 숫자 정보
DATETIME - 날짜와 시각 정보
-> CHAR와 VARCHAR : 저장 영역과 문자열의 비교 방법 차이
CHAR : 실제 데이터 크기만 필요함 -> 길이가 다양한 칼럼, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼
또한 공백을 채워서 비교해 앞에서부터 한 문자씩 비교 -> 끝의 공백만 다른 문자열은 같다고 판단
VARCHAR : 맨 처음부터 한 문자씩 비교 -> 공백도 하나의 문자로 취급, 끝의 공백이 다르면 다른 문자로 판단
즉, VARCHAR, NUMERIC : 정의한 길이와 자릿수는 최대한의 한계값을 정의
2. CREATE TABLE
가. 테이블과 칼럼 정의
기본키와 외래키
테이블의 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정한다
선수 테이블에 선수의 소속팀 정보가 같이 존재한다고 가정하면, 특정 팀 의 이름이 변경되었을 경우 그 팀에 소속된 선수 데이터를 일일이 찾아서 수정을 하거나, 또한 팀이 해체되었을 경우 선수 관련 정보까지 삭제되는 수정/삭제 이상(Anomaly) 현상이 발생할 수 있다.
이런 이상 현상을 방지하기 위해 팀 정보를 관리하는 팀 테이블을 별도로 분리해서 팀ID와 팀 이름을 저장하고, 선수 테이블에서는 팀ID를 외부키로 참조하게 한다.
나. CREATE TABLE
(1) 다음 조건 형태로 선수 테이블 생성
| 테이블명 | PLAYER |
| 테이블 설명 | K-리그 선수들의 정보를 가지고 있는 테이블 |
| 칼럼명 | PLAYER_ID (선수ID) 문자 고정 자릿수 7자리, PLAYER_NAME (선수명) 문자 가변 자릿수 20자리, TEAM_ID (팀ID) 문자 고정 자릿수 3자리, E_PLAYER_NAME (영문선수명) 문자 가변 자릿수 40자리, NICKNAME (선수별명) 문자 가변 자릿수 30자리, JOIN_YYYY (입단년도) 문자 고정 자릿수 4자리, POSITION (포지션) 문자 가변 자릿수 10자리, BACK_NO (등번호) 숫자 2자리, NATION (국적) 문자 가변 자릿수 20자리, BIRTH_DATE (생년월일) 날짜, SOLAR (양/음) 문자 고정 자릿수 1자리, HEIGHT (신장) 숫자 3자리, WEIGHT (몸무게) 숫자 3자리, |
| 제약조건 | 기본키(PRIMARY KEY) → PLAYER_ID (제약조건명은 PLAYER_ID_PK) 값이 반드시 존재 (NOT NULL) → PLAYER_NAME, TEAM_ID |
CREATE TABLE PLAYER (
PLAYER_ID CHAR(7) NOT NULL,
PLAYER_NAME VARCHAR2(20) NOT NULL,
TEAM_ID CHAR(3) NOT NULL,
E_PLAYER_NAME VARCHAR2(40),
NICKNAME VARCHAR2(30),
JOIN_YYYY CHAR(4),
POSITION VARCHAR2(10),
BACK_NO NUMBER(2),
NATION VARCHAR2(20),
BIRTH_DATE DATE, SOLAR CHAR(1),
HEIGHT NUMBER(3),
WEIGHT NUMBER(3),
CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID) );
다. 제약조건
데이터의 무결성을 유지하기 위한 DB의 보편적인 방법, 특정 칼럼에 설정하는 제약
| 기본키 | 테이블에 저장된 행 데이터를 고유하게 식별 하나의 테이블에 하나의 기본키 제약 정의가능 -> 고유키 제약 & NOT NULL 제약 |
| 고유키 | 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키 저의 NULL은 고유키 제약 대상이 아니다. |
| NOT NULL | NULL 값의 입력을 금지. 입력 필수. NOT NULL을 CHECK의 일부분으로 이해 가능 |
| CHECK | 입력할 수 있는 값의 범위 제한 [TRUE or FALSE] |
| 외래키 | 테이블 간의 관계를 정의. 기본 키를 다른 테이블의 외래키로 복사 [참조 무결성 제약 옵션 선택] |
NULL : 아직 정의되지 않은 미지의 값, 현재 데이터를 입력하지 못하는 경우
DEFAULT : 칼럼의 값이 지정되어 있지 않을 경우, 데이터 입력시 명시된 값을 지정하지 않을 경우 NULL입력되지 않고 자동 입력
(2) 다음 조건 형태로 팀 테이블 생성
| 테이블명 | TEAM |
| 테이블 설명 | K-리그 선수들의 소속팀에 대한 정보를 가지고 있는 테이블 |
| 칼럼명 | TEAM_ID (팀 고유 ID) 문자 고정 자릿수 3자리, REGION_NAME (연고지 명) 문자 가변 자릿수 8자리, TEAM_NAME (한글 팀 명) 문자 가변 자릿수 40자리, E-TEAM_NAME (영문 팀 명) 문자 가변 자릿수 50자리 , ORIG_YYYY (창단년도) 문자 고정 자릿수 4자리, STADIUM_ID (구장 고유 ID) 문자 고정 자릿수 3자리, ZIP_CODE1 (우편번호 앞 3자리) 문자 고정 자릿수 3자리, ZIP_CODE2 (우편번호 뒷 3자리) 문자 고정 자릿수 3자리, ADDRESS (주소) 문자 가변 자릿수 80자리, DDD (지역번호) 문자 가변 자릿수 3자리, TEL (전화번호) 문자 가변 자릿수 10자리, FAX (팩스번호) 문자 가변 자릿수 10자리, HOMEPAGE (홈페이지) 문자 가변 자릿수 50자리, OWNER (구단주) 문자 가변 자릿수 10자리, |
| 제약조건 | 기본 키(PRIMARY KEY) → TEAM_ID (제약조건명은 TEAM_ID_PK) NOT NULL → REGION_NAME, TEAM_NAME, STADIUM_ID (제약조건명은 미적용) |
CREATE TABLE TEAM (
TEAM_ID CHAR(3) NOT NULL,
REGION_NAME VARCHAR2(8) NOT NULL,
TEAM_NAME VARCHAR2(40) NOT NULL,
E_TEAM_NAME VARCHAR2(50),
ORIG_YYYY CHAR(4),
STADIUM_ID CHAR(3) NOT NULL,
ZIP_CODE1 CHAR(3),
ZIP_CODE2 CHAR(3),
ADDRESS VARCHAR2(80),
DDD VARCHAR2(3),
TEL VARCHAR2(10),
FAX VARCHAR2(10),
HOMEPAGE VARCHAR2(50),
OWNER VARCHAR2(10),
CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID),
CONSTRAINT TEAM_FK FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID) );
라. 생성된 테이블 구조 확인
“DESCRIBE 테이블명;”
“DESC 테이블명;”
(3) 선수 테이블 구조 확인
DESCRIE PLYAER;
마. SELECT 문장을 통한 테이블 생성 사례
CTAS
Create Table As Select : 칼럼별로 데이터 유형 다시 재정의 안해도 된다.
[제약조건중 NOT NULL만 적용 다른 제약조건은 사라짐] -> ALTER TABLE 사용
(4) 선수 테이블과 같은 내용으로 TEAM_TEMP라는 복사 테이블 생성
CREATE TABLE TEAM_TEMP
AS SELECT * FROM TEAM;
3. ALTER TABLE
칼럼을 추가/삭제, 제약조건을 추가,삭제
가. ADD COLUMN
(5) PLAYER 테이블에 ADDRESS (데이터 유형은 가변 문자로 자릿수 80자리로 설정) 칼럼을 추가
ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80));
나. DROP COLUMN
한 번에 하나의 칼럼만 삭제 가능, 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 한다.
(6) PLYAER 테이블의 ADDRESS 칼럼을 삭제
ALTER TABLE PLAYER
DROP COLUMN ADDRESS;
다. MODIFY COLUMN
ALTER TABLE 명령을 이용해 데이터 유형, 디폴트 값, NOT NULL 제약조건 변경 가능
(7) TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)→VARCHAR2(8)으로 변경하고,
향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고,
모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL → NOT NULL로 변경
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
RENAME COLUMN [PLYAER_ID -> TEMP_ID로, TEMP_ID -> PLAYER_ID로]
ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;
테이블이 변경되었다.
ALTER TABLE PLAYER RENAME COLUMN TEMP_ID TO PLAYER_ID;
테이블이 변경되었다.
라. DROP CONSTRAINT
(8) PLAYER 테이블의 외래키 제약조건 삭제
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;
마. ADD CONSTRAINT
(9) PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다.
제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);
(10) PLAYER 테이블이 참조하는 TEAM 테이블 제거
DROP TABLE TEAM;
ERROR: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있다.
테이블 삭제 수행안함
(11) PLAYER 테이블이 참조하는 TEAM 테이블의 데이터 삭제
DELETE TEAM
WHERE TEAM_ID = 'K10';
ERROR: 무결성 제약조건(SCOTT.PLAYER_FK)이 위배되었다.
자식 레코드가 발견되었다.
데이터 삭제 수행안함
참조 무결성 옵션에 따라 삭제 불가능하게 제약
-외부키 설정함으로써 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지
4. RENAME TABLE
(12) RENAME 문장 이용해 TEAM 테이블을 다른 이름으로 변경하고 다시 TEAM 테이블로 변경
RENAME TEAM TO TEAM_BACKUP;
테이블 이름이 변경되었다.
RENAME TEAM_BACKUP TO TEAM;
테이블 이름이 변경되었다.
5. DROP TABLE
CONSTRAINT
-관계가 있었던 참조되는 제약조건에 대해서도 삭제
(13) PLAYER 테이블 제거
DROP TABLE PLAYER;
테이블이 삭제되었다.
DESC PLAYER;
ERROR: 설명할 객체를 찾을 수 없다.
6. TRUNCATE TABLE
테이블 자체가 삭제되지 않고, 모든 행들이 제거되고, 저장 공간을 재사용 가능하도록 해제
(14) TRUNCATE TABLE을 사용해 해당 테이블의 모든 행을 삭제하고 테이블 구조를 확인
TRUNCATE TABLE TEAM;
테이블이 트렁케이트되었다.
테이블의 삭제
DROP TABLE : 테이블 자체가 사라짐
TRUNCATE TABLE : 테이블 구조는 그대로 유지한 채 데이터만 전부 삭제
테이블의 데이터 삭제
DELETE TABLE : 단건의 데이터 삭제시 유용
TRUNCATE TABLE : 테이블의 전체 데이터 삭제시 부하가 적다. 정상적인 복구가 불가능
3절. DML
원하는 자료들을 입력, 수정, 삭제, 조회
1. INSERT
1) 선택 칼럼 데이터 입력 -> 빠진 경우 DEFAULT로 NULL 입력
2) 모든 칼럼 데이터 입력 -> 순서대로 빠짐없이 데이터 입력
(1) 선수 테이블에 박지성 선수의 데이터를 일부 칼럼만 입력
INSERT INTO PLAYER
(PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);
(2) 해당 테이블에 이청용 선수의 데이터 입력
INSERT INTO PLAYER
VALUES ('2002010','이청용','K07','','BlueDragon','2002','MF','17',NULL, NULL,'1',180,69);
정의되지 않은 미지의 값 입력 방법
- "이나 NULL로 표현
2. UPDATE
(3) 선수 테이블의 백넘버를 일괄적으로 99로 수정
UPDATE PLAYER
SET BACK_NO = 99;
(4) 선수 테이블의 포지션을 일괄적으로 'MF'로 수정
UPDATE PLAYER
SET POSITION = 'MF';
3. DELETE
FROM : 생략 가능 / WHERE : 생략시 테이블 전체 데이터 삭제
(1) 선수 테이블 데이터 전부 삭제
DELETE FROM PLAYER;
DDL : AUTO COMMIT
DML : 테이블을 메모리 버퍼에 올려놓고 작업 -> COMMIT -> 트랜잭션 종료
전체 데이터를 삭제하는 경우엔 TRUNCATE TABLE -> ROLLBACK 불가
4. SELECT
ALL
-Default 옵션으로, 중복된 데이터 있어도 모두 출력
DISTINCT
-중복된 데이터 있는 경우 1건으로 처리해서 출력
(1) 조회하기를 원하는 칼럼명을 SELECT 다음에 콤마 구분자(,)로 구분하여 나열하고, FROM 다음에 해당 칼럼이 존재하는 테이블명을 입력하여 실행시킨다. 입력한 선수 들의 데이터를 조회한다.
SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO
FROM PLAYER;
(2) 선수 테이블의 포지션 정보를 ALL과 DISTINCT 옵션으로 확인해본다.
SELECT ALL POSITION
FROM PLAYER;
SELECT POSITION
FROM PLAYER;
SELECT POSITION
FROM PLAYER;
와일드카드
(3) 입력한 선수들의 정보를 모두 조회
SELECT *
FROM PLAYER;
ALIAS
(4) 입력한 선수들의 정보를 칼럼 별명을 이용해 출력
SELECT PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게
FROM PLAYER;
칼럼 별명에서 AS를 꼭 사용하지 않아도 되므로, 아래 SQL은 위 SQL과 같은 결과를 출력한다.
SELECT PLAYER_NAME 선수명, POSITION 위치, HEIGHT 키, WEIGHT 몸무게
FROM PLAYER;
(5) 칼럼 별명을 적용할 때 별명 중간에 공백이 들어가는 경우 『" " 』를 사용해야 한다.
SQL Server의 경우『" "』, 『' 』', 『[ ]』와 같이 3가지의 방식으로 별명을 부여할 수 있다.
SELECT PLAYER_NAME "선수 이름", POSITION "그라운드 포지션", HEIGHT "키", WEIGHT "몸무게"
FROM PLAYER;
5. 산술 연산자와 합성 연산자
산술 연산자
- (), *, /, +, -
(1) 선수들의 키에서 몸무게를 뺀 값
SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게"
FROM PLAYER;
(2) 선수들의 키와 몸무게를 이용해 반올림을 이용해 BMI 비만지수를 측정
SELECT PLAYER_NAME 이름, ROUND(WEIGHT/((HEIGHT/100)*(HEIGHT/100)),2)
"BMI 비만지수"
FROM PLAYER;
합성 연산자 [ ||, CONCAT() ]
(3) 출력 형태) 선수명 선수, 키 cm, 몸무게 kg 예) 박지성 선수, 176 cm, 70 kg
SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보
FROM PLAYER;
4절. TCL
1. 트랜잭션 개요
데이터베이스의 논리적 연산단위 -> 분리될 수 없는 한 개 이상의 데이터베이스 조작
즉, 분할할 수 없는 최소의 단위 [ALL OR NOTHING, 원자성]
-> 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합
대상
: DML 문, 배타적 LOCK을 요구하는 SELECT
특성
: atomicity, consistency, isolation, durability
: 원자성, 일관성, 고립성, 지속성
잠금 [LOCKING]
:트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다.
잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고 다른 트랜잭 션으로부터 간섭이나 방해를 받지 않는 것을 보장하며, 잠금이 걸린 데이터는 잠금 을 수행한 트랜잭션만이 해제할 수 있다
2. COMMIT
트랜잭션의 완료
(1) PLAYER 테이블에 데이터를 입력하고 COMMIT을 진행
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
1개의 행이 만들어졌다.
COMMIT;
커밋이 완료되었다.
(2) PLAYER 테이블에 데이터를 수정하고 COMMIT을 수행
UPDATE PLAYER
SET HEIGHT = 100;
480개의 행이 수정되었다.
COMMIT;
커밋이 완료되었다.
(3) PLAYER 테이블에 있는 데이터를 삭제하고 COMMIT을 수행
DELETE FROM PLAYER;
480개의 행이 삭제되었다.
COMMIT;
커밋이 완료되었다.
3. ROLLBACK
COMMIT 이전에는 변경 사항을 취소 가능하게 해주는 기법
변경 사항이 취소되어 이전 상태로 복구, 잠금이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 해준다.
(4) PLAYER 테이블에 데이터를 입력하고 ROLLBACK을 실행
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
1개의 행이 만들어졌다.
ROLLBACK;
롤백이 완료되었다.
(5) PLAYER 테이블에 데이터를 수정하고 ROLLBACK을 실행
UPDATE PLAYER
SET HEIGHT = 100;
480개의 행이 수정되었다.
ROLLBACK;
롤백이 완료되었다.
(6) PLAYER 테이블에 데이터를 삭제하고 ROLLBACK을 실행
DELETE FROM PLAYER;
480개의 행이 삭제되었다.
ROLLBACK;
롤백이 완료되었다.
효과
: 데이터 무결성 보장
: 데이터 변경 사항 확인 가능
: 연관된 작업을 그룹핑해서 처리
4. SAVEPOINT
롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 SAVEPOINT까지 트랜잭션의 일부만 롤백
-> 실패한 부분에 대해서만 다시 실행 가능
복수의 저장점 정의 가능, 동일이름 저장시 나중에 정의한 저장점만 유효
(1) SAVEPOINT를 지정하고, PLAYER 테이블에 데이터를 입력한 다음 롤백 (ROLLBACK)을 이전에 설정한 저장점까지 실행한다
SAVEPOINT SVPT1;
저장점이 생성되었다.
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
1개의 행이 만들어졌다.
ROLLBACK TO SVPT1;
롤백이 완료되었다
(2) 먼저 SAVEPOINT를 지정하고 PLAYER 테이블에 있는 데이터를 수정한 다음 롤백 (ROLLBACK)을 이전에 설정한 저장점까지 실행한다.
SAVEPOINT SVPT2;
저장점이 생성되었다.
UPDATE PLAYER SET WEIGHT = 100;
480개의 행이 수정되었다.
ROLLBACK TO SVPT2;
롤백이 완료되었다.
(3) SAVEPOINT를 지정하고, PLAYER 테이블에 있는 데이터를 삭제한 다음 롤백 (ROLLBACK)을 이전에 설정한 저장점까지 실행한다.
SAVEPOINT SVPT3;
저장점이 생성되었다.
DELETE FROM PLAYER;
480개의 행이 삭제되었다.
ROLLBACK TO SVPT3;
롤백이 완료되었다.
저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 되 돌릴 수는 없다.
일단 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 되기 때문이다.
즉, ‘ROLLBACK TO A’를 실행한 시점에서 저장점 A 이후에 정의한 저장 점 B는 존재하지 않는다.
저장점 지정 없이 “ROLLBACK”을 실행했을 경우 반영안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.
(4) 새로운 트랜잭션을 시작하기 전에 PLAYER 테이블의 데이터 건수와 몸무게가 100 인 선수의 데이터 건수를 확인한다
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
------- 480
1개의 행이 선택되었다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
------- 0
1개의 행이 선택되었다.
(5) 새로운 트랜잭션을 시작하고 SAVEPOINT A와 SAVEPOINT B를 지정한다. (툴에 AUTO COMMIT 옵션이 적용되어 있는 경우 해제함)
새로운 트랜잭션 시작
INSERT INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
1개의 행이 만들어졌다.
SAVEPOINT SVPT_A;
저장점이 생성되었다.
UPDATE PLAYER SET WEIGHT = 100;
481개의 행이 수정되었다.
SAVEPOINT SVPT_B;
저장점이 생성되었다.
DELETE FROM PLAYER;
481개의 행이 삭제되었다.
현재 위치에서 [예제] CASE 1,2,3을 순서대로 수행해본다.
CASE1. SAVEPOINT B 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*) FROM PLAYER; COUNT(*)
-------- 0
1개의 행이 선택되었다.
ROLLBACK TO SVPT_B;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
------- 481
1개의 행이 선택되었다.
CASE2. SAVEPOINT A 저장점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
------- 481
1개의 행이 선택되었다.
ROLLBACK TO SVPT_A;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER
WHERE WEIGHT = 100;
COUNT(*)
------- 0
1개의 행이 선택되었다
CASE3. 트랜잭션 최초 시점까지 롤백(ROLLBACK)을 수행하고 롤백 전후 데이터를 확인해본다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
------- 481
1개의 행이 선택되었다.
ROLLBACK;
롤백이 완료되었다.
SELECT COUNT(*)
FROM PLAYER;
COUNT(*)
------- 480
1개의 행이 선택되었다
데이터의 무결성을 보장
:트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료된다.
단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
- CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
- 부연하면, DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
- 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
- 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동 으로 롤백된다.
5절. WHERE절
[DISTINCT/ALL], 칼럼명 [ALIAS명]
1. WHERE 조건절 개요
:자신이 원하는 자료만 검색
두 개 이상의 테이블에 대한 조인 조건 기술, 결과를 제한하기 위한 조건 [WHERE절의 JOIN 조건, FROM 절의 JOIN]
-> FTS
Full Table Scan
WHERE 절에 조건이 없는 문장으로, SQL 튜닝의 1차적인 검토 대상이 된다.
필요 없는 많은 자료들을 데이터베이스로부터 요청해 시스템 자원을 과다하게 사용한다.
2. 연산자의 종류
비교연산자
WHERE 절에 조건식을 사용할 때 사용
WHERE절에 사용하는 연산자의 3가지 종류
비교 연산자 (부정 비교 연산자 포함)
SQL 연산자 (부정 SQL 연산자 포함)
논리 연산자
우선순위 :
괄호 > 부정 연산자 > 비교, SQL 비교 연산자> AND, OR
3. 비교 연산자
(1) K-리그 일부 선수들의 이름과 포지션, 백넘버를 알고 싶다.
조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에서
포지션이 미드필더 이면서, 키는 170 센터미터 이상, 180 이하여야 한다.
1) 소속팀코드 = 삼성블루윙즈팀 코드(K02)
2) 소속팀코드 = 전남드래곤즈팀 코드(K07)
3) 포지션 = 미드필더 코드(MF)
4) 키 >= 170 센티미터
5) 키 <= 180 센티미터
(2) 첫 번째 요구 사항인 소속팀이 삼성블루윙즈라는 조건을 WHERE 조건절로 옮겨서 SQL 문장을 완성하여 실행한다.
ELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' ;
(3) 세 번째 요구 사항인 포지션이 미드필더(MF)인 조건을 WHERE 조건절로 옮겨서 SQL 문장을 완성하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE POSITION = 'MF';
문자 유형간의 비교 조건
비교 연산자의 양쪽이 모두 CHAR 유형 타입인 경우
비교 연산자의 어느 한 쪽이 VARCHAR 유형 타입인 경우
상수값과 비교할 경우
(4) 네 번째 요구 사항인 "키가 170 센티미터 이상"인 조건도 WHERE 절로 옮겨서 SQL 문장을 완성하여 실행한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE HEIGHT >= 170;
4. SQL 연산자
: 예약되어 있는 연산자로 4가지 종류
BETWEEN a AND b
IN (list)
LIKE '비교문자열'
IS NULL
(5) 앞서 요구사항을 비교연산자와 SQL 비교연산자로 표현
| K-리그 일부 선수들의 이름과 포지션, 백넘버를 알고 싶다. 조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에서 포지션이 미드필더 이면서, 키는 170 센터미터 이상, 180 이하여야 한다. |
1) 소속팀코드 IN (삼성블루윙즈 코드(K02), 전남드래곤즈 코드(K07))
2) 포지션 LIKE 미드필더(MF)
3) 키 BETWEEN 170 센티미터 AND 180 센티미터
IN (list) 연산자
(6) 소속팀 코드와 관련된 IN (list) 형태의 SQL 비교 연산자를 사용하여 WHERE 절에 사용한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02', 'K07');
(7) 사원 테이블에서 JOB이 MANAGER이면서 20번 부서에 속하거나, JOB이 CLERK이면서 30번 부서에 속하는 사원의 정보를 IN 연산자의 다중 리스트를 이용해 출력하라.
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB, DEPTNO) IN (('MANAGER',20),('CLERK',30));
주의
| SELECT ENAME, JOB, DEPTNO FROM EMP WHERE JOB IN ('MANAGER','CLERK') AND DEPTNO IN (20,30); |
LIKE 연산자
(8) 요구 사항의 두 번째 조건에 대해서 LIKE 연산자를 WHERE 절에 적용해서 실행한다.
| K-리그 일부 선수들의 이름과 포지션, 백넘버를 알고 싶다. 조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에서 포지션이 미드필더 이면서, 키는 170 센터미터 이상, 180 이하여야 한다. |
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE POSITION LIKE 'MF'
와일드카드
한 개 혹은 0개 문자를 대신해서 사용하기 위한 특수문자 -> 조합해서 스트링 값으로 용이하게 사용
% : 0개 이상의 어떤 문자를 의미
_ : 1개인 단일 문자를 의미
(9) “장”씨 성을 가진 선수들의 정보를 조회하는 WHERE 절을 작성한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE PLAYER_NAME LIKE '장%';
BETWEEN a AND b 연산자
(10) 세 번째로 키가 170 센티미터 이상 180센티미터 이하인 선수들의 정보를 BETWEEN a AND b 연산자를 사용하여 WHERE 절을 완성한다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE HEIGHT BETWEEN 170 AND 180;
BETWEEN a AND b는 범위에서 'a'와 'b'의 값을 포함하는 범위를 말하는 것이다.
IS NULL 연산자
: 비교 자체가 불가능한 값
(11) POSITION 칼럼(Column) 값이 NULL 값인지를 판단하기 위해서는 IS NULL을 사 용하여 다음과 같이 SQL 문장을 수정하여 실행한다
SELECT PLAYER_NAME 선수이름, POSITION 포지션, TEAM_ID
FROM PLAYER
WHERE POSITION IS NULL;
5. 논리 연산자 [AND, OR, NOT]
비교 연산자나 SQL 비교 연산자로 이루어진 여러 개의 조건들을 논리적으로 연결
(12) “소속이 삼성블루윙즈”인 조건과 “키가 170 센티미터 이상”인 조건을 연 결해 보면 “소속이 삼성블루윙즈이고 키가 170 센티미터 이상인 조건을 가진 선수 들의 자료를 조회”
SELECT PLAYER_NAME 선수이름, POSITION 포지션, TEAM_ID
FROM PLAYER
WHERE TEAM_ID = 'K02'
AND HEIGHT >= 170;
(13) “소속이 삼성블루윙즈이거나 전남드래곤즈”인 조건을 SQL 비교 연산자로, “포지션 이 미드필더(MF)”인 조건을 비교 연산자로 비교한 결과를 논리 연산자로 묶어서 처리한다
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02','K07') AND POSITION = 'MF';
(14) 요구 사항을 하나씩 하나씩 AND, OR 같은 논리 연산자를 사용하여 DBMS가 이해 할 수 있는 SQL 형식으로 질문을 변경한다. 요구 사항을 순서대로 논리적인 조건을 적용한다
| 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들이어야 하고, 포지션이 미드필더(MF:Midfielder)이어야 한다. 키는 170 센티미터 이상이고 180 이하여야 한다 |
1) 소속팀이 삼성블루윙즈 OR 소속팀이 전남드래곤즈
2) AND 포지션이 미드필더
3) AND 키는 170 센티미터 이상
4) AND 키는 180 센티미터 이하
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE (TEAM_ID = 'K02' OR TEAM_ID = 'K07')
AND POSITION = 'MF'
AND HEIGHT >= 170
AND HEIGHT <= 180;
주의
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'
AND POSITION = 'MF'
AND HEIGHT >= 170
AND HEIGHT <= 180;
삼성블루윙즈이거나 전남드래곤즈 중 포지션이 미드필더(MF: Midfielder)인 선수들에 대한 자료만 요청했는데 포지션이 DF나 FW인 선수가 같이 출력된 것이다.
[예제]에서 “소속팀 코드가 삼성블루윙즈(K02) 이거나 전남드래곤즈(K07)”라는 조건을 만족하고
“포지션이 미드필더(MF)”인 조건을 동시에 만족해야 하는데,
위의 SQL 문장에서 는 괄호가 누락됨으로서
OR 논리 연산자보다 AND 논리 연산자를 먼저 실행하기 때문에 잘못된 결과를 나타낸 것이다.
->논리 연산자들이 여러 개가 같이 사용되었을 때의 처리 우선순위는
( ), NOT, AND, OR의 순서대로 처리
(15) IN (list)와 BETWEEN a AND b 연산자를 활용하여 같은 결과를 출력하는 SQL 문장을 작성한다.
두개의 SQL 문장은 DBMS 내부적으로 같은 프로세스를 거쳐 수행 되므로 당연히 실행 결과도 같다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02','K07')
AND POSITION = 'MF'
AND HEIGHT BETWEEN 170 AND 180;
6. 부정연산자
(1) 삼성블루윙즈 소속인 선수들 중에서 포지션이 미드필더(MF:Midfielder)가 아니고,
키가 175 센티미터 이상 185 센티미터 이하가 아닌 선수 출력
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02'
AND NOT POSITION = 'MF'
AND NOT HEIGHT BETWEEN 175 AND 185;
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02'
AND POSITION <>'MF'
AND HEIGHT NOT BETWEEN 175 AND 185;
(2) 국적(NATION) 칼럼의 경우 내국인들은 별도 데이터를 입력하지 않았다. 국적 칼럼 이 NULL이 아닌 선수와 국적을 표시
SELECT PLAYER_NAME 선수이름, NATION 국적
FROM PLAYER
WHERE NATION IS NOT NULL;
7. ROWNUM, TOP 사용
ROWNUM
Pseudo Column으로써 SQL 처리 결과 집 합의 각 행에 대해 임시로 부여되는 일련번호이며,
테이블이나 집합에서 원하는 만큼의 행 만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용
한 건의 행 가져오기
WHERE ROWNUM = 1;
WHERE ROWNUM <= 1;
WHERE ROWNUM < 2;
두 건 이상의 행 가져오기
WHERE ROWNUM <= N;
WHERE ROWNUM <= N+1;
# 테이블 내의 고유한 키나 인덱스 값 생성
UPDATE MY_TABLE SET COLUMN1 = ROWNUM;
TOP 절 [SQL SERVER]
: 출력되는 행의 수를 계산
6절. 함수
-> 내장함수 / 사용자 정의 함수 [항상 M:1 관계]
1. 내장 함수
종류
단일행 함수
-입력 값이 단일행 값이 입력
-하나의 값 또는 여러 값이 입력 인수로 표현 가능
다중행 함수
-여러 행의 값이 입력
-여러 레코드의 값들을 입력 인수로 사용
-집계 함수 / 그룹 함수 / 윈도우 함수
단일행 함수의 종류
문자형 함수
-문자 입력시 문자나 숫자 값 반환
숫자형 함수
-숫자 입력시 숫자 값 반환
날짜형 함수
-DATE 타입의 값 연산
변환형 함수
-문자, 숫자, 날짜형 값의 데이터타입 변환
NULL 관련 함수
-NULL 처리 함수
2. 문자형 함수
: 문자 데이터를 매개 변수로 받아 문자나 숫자 값의 결과로 리턴하는 함수
종류
LOWER
UPPER
ASCII
CHR / CHAR
CONCAT
SUBSTR / SUBSTRING
LENGTH / LEN
LTRIM
RTRIM
TRIM
(1) ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수
SELECT LENGTH('SQL Expert')
FROM DUAL;
LENGTH('SQL Expert')
Oracle : 사용자 테이블이 필요 없는 SQL 문장의 경우에도 필수적으로 DUAL 이라는 테이블을 FROM 절에 지정
(2) ‘SQL Expert’라는 문자형 데이터의 길이를 구하는 문자형 함수를 사용한다.
SELECT LEN('SQL Expert') AS ColumnLength;
ColumnLength;
(3) 선수 테이블에서 CONCAT 문자형 함수를 이용해 축구선수란 문구를 추가한다.
SELECT CONCAT(PLAYER_NAME, ' 축구선수') 선수명
FROM PLAYER;
CONCAT 함수는 Oracle의 '||' 합성 연산자와 같은 기능이다.
SELECT PLAYER_NAME || ' 축구선수' AS 선수명
FROM PLAYER;
실제적으로 함수가 모든 행에 대해 적용되어 ‘~ 축구선수’라는 각각의 결과로 출력
특별한 제약 조건이 없다면 함수는 여러 개 중첩하여 사용이 가능
함수 내부에 다른 함수를 사용하며 안쪽에 위치해 있는 함수부터 실행되어 그 결과 값이 바 깥쪽의 함수에 인자(Argument)로 사용
(4) 경기장의 지역번호와 전화번호를 합친 번호의 길이를 구하시오.
연결연산자의 결과가 LENGTH(SQL Server는 LEN 사용) 함수의 인수
SELECT STADIUM_ID, DDD||TEL as TEL, LENGTH(DDD||TEL) as T_LEN
FROM STADIUM;
3. 숫자형 함수
(5) 소수점 이하 한 자리까지 반올림 및 내림하여 출력
SELECT ENAME, ROUND(SAL/12,1), TRUNC(SAL/12,1)
FROM EMP;
(6) 정수 기준으로 반올림 및 올림하여 출력
SELECT ENAME, ROUND(SAL/12), CEILING(SAL/12)
FROM EMP;
4. 날짜형 함수
DATE 타입의 값을 연산하는 함수
단일행 날짜형 함수 연산
(6) Oracle의 SYSDATE 함수와 SQL Server의 GETDATE( ) 함수를 사용하여 데이터베이스에서 사용하는 현재의 날짜 데이터를 확인한다.
-> 날짜 데이터는 시스템 구성에 따라 다양하게 표현될 수 있으므로 사용자마다 다른 결과가 나올 수 있다.
SELECT SYSDATE
FROM DUAL;
(7) 사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력
SELECT ENAME, HIREDATE,
EXTRACT(YEAR FROM HIREDATE) 입사년도,
EXTRACT(MONTH FROM HIREDATE) 입사월,
EXTRACT(DAY FROM HIREDATE) 입사일
FROM EMP;
SELECT ENAME, HIREDATE,
TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) 입사년도,
TO_NUMBER(TO_CHAR(HIREDATE,'MM')) 입사월,
TO_NUMBER(TO_CHAR(HIREDATE,'DD')) 입사일
FROM EMP;
TO_NUMBER 함수 제외시 문자형으로 출력됨 (ex: 01,02,03,...)
5. 변환형 함수
특정 데이터 타입을 다양한 형식으로 출력하고 싶을 때 사용하는 함수
명시적 데이터 유형 변환
-데이터 변환형 함수로 데이터 유형을 변환하도록 명시 -> 사용 권장
암시적 데이터 유형 변환
-데이터 베이스가 자동으로 데이터 유형을 변환해 계산 -> 성능 저하 발생 가능
명시적 데이터 유형 변환형 함수
(8) 날짜를 정해진 문자 형태로 변형
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') 날짜,
TO_CHAR(SYSDATE, 'YYYY. MON, DAY') 문자형
FROM DUAL;
(9) 금액을 달러와 원화로 표시
SELECT TO_CHAR(123456789/1200,'$999,999,999.99') 환율반영달러,
TO_CHAR(123456789,'L999,999,999') 원화
FROM DUAL;
(10) 팀(TEAM) 테이블의 ZIP 코드1과 ZIP 코드2를 숫자로 변환한 후 두 항목을 더한 숫자를 출력
SELECT TEAM_ID,
TO_NUMBER(ZIP_CODE1,'999') + TO_NUMBER(ZIP_CODE2,'999') 우편번호합
FROM TEAM;
6. CASE 표현
(=함수와 같은 성격, Decode 함수 같은 기능)
IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완하는 역할
(11) IF-THEN-ELSE-END
| IF SAL >2000 THEN REVISED_SALARY = SAL ELSE REVISED_SALARY = 2000 END-IF. |
| SELECT ENAME, CASE WHEN SAL >2000 THEN SAL ELSE 2000 END REVISED_SALARY FROM EMP; |
단일행 CASE 표현의 종류
Simple Case Expression과 Searched Case Expression
| CASE SIMPLE_CASE_EXPRESSION 조건 or SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END |
Simple Case Expression (=DECODE함수와 동일한 기능) / ('='를 이용해 표현)
| CASE EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR ELSE 표현절 END |
(12) 부서 정보에서 부서 위치를 미국의 동부, 중부, 서부로 구분
SELECT LOC,
CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC'
END as AREA
FROM DEPT;
SEARCHED_CASE_EXPRESSION (다양한 조건)
| CASE WHEN CONDITION THEN RETURN_EXPR ELSE 표현절 END |
(13) 사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류
SELECT ENAME,
CASE WHEN SAL >= 3000 THEN 'HIGH'
WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW'
END AS SALARY_GRADE
FROM EMP;
(14) 사원 정보에서 급여가 2000 이상이면 보너스를 1000으로, 1000 이상이면 5000으로, 1000 미만이면 0으로 계산
SELECT ENAME, SAL,
CASE WHEN SAL >= 2000
THEN 1000
ELSE (CASE WHEN SAL >= 1000
THEN 500
ELSE 0
END)
END as BONUS
FROM EMP;
7. NULL 관련 함수
가. NVL/ISNULL 함수
(15) NVL (NULL 판단 대상,‘NULL일 때 대체값’)을 이용해 테스트
SELECT NVL(NULL, 'NVL-OK') NVL_TEST
FROM DUAL;
(16) 선수 테이블에서 성남 일화천마(K08) 소속 선수의 이름과 포지션을 출력하는데, 포 지션이 없는 경우는 '없음'으로 표시
SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음') 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'
(17) NVL 함수와 ISNULL 함수를 사용한 SQL 문장은 벤더 공통적으로 CASE 문장으로 표현
SQL SERVER
SELECT PLAYER_NAME 선수명, POSITION,
CASE WHEN POSITION IS NULL
THEN '없음'
ELSE POSITION
END AS 포지션
FROM PLAYER
WHERE TEAM_ID = 'K08'
(18) 급여와 커미션을 포함한 연봉을 계산하면서 NVL 함수의 필요성
SELECT ENAME 사원명, SAL 월급, COMM 커미션,
(SAL * 12) + COMM 연봉A, (SAL * 12) + NVL(COMM,0) 연봉B
FROM EMP;
NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생 할 수 있으므로 굳이 NVL 함수를 사용할 필요가 없다.
다중행 함수는 입력 값으로 전체 건 수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 다중행 함수의 대상에서 제외한다.
예를 들면 100명 중 10명의 성적이 NULL 값일 때 평균을 구하는 다중행 함수 AVG를 사용하면 NULL 값이 아닌 90명의 성적에 대해서 평균값을 구하게 된다.
나. NULL과 공집합
1)일반적인 NVL/ISNULL함수
STEP1. 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력
SELECT MGR FROM EMP WHERE ENAME='SCOTT';
STEP2. 매니저에 NULL이 들어있는 KING의 매니저를 출력
SELECT MGR FROM EMP WHERE ENAME='KING';
STEP3. 매니저가 NULL인 경우 빈칸이 아닌 9999로 출력하기 위해 NVL/ISNULL 함수를 사용
SELECT NVL(MGR,9999) MGR FROM EMP WHERE ENAME='KING';
2) 공집합의 NVL/ISNULL 함수
STEP1. 공집합을 발생시키기 위해 사원 테이블에 존재하지 않는 'JSC'라는 이름으로 데이터를 검색
SELECT MGR
FROM EMP
WHERE ENAME='JSC';
STEP2. NVL/ISNULL 함수를 이용해 공집합을 9999로 바꾸고자 시도
SELECT NVL(MGR, 9999) MGR
FROM EMP
WHERE ENAME='JSC';
STEP3. 적절한 집계 함수를 찾아서 NVL 함수 대신 적용
SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='JSC';
STEP4. 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 빈칸이 아닌 9999로 출력
SELECT NVL(MAX(MGR), 9999) MGR FROM EMP WHERE ENAME='JSC';
경우의 수
(1) NVL/ISNULL 함수를 사용해야 하는 경우
(2) 집계 함수를 포함한 NVL/ISNULL 함수를 사용해야 하는 경우
(3) NVL/ISNULL 함수를 포함한 집계 함수를 사용하지 않아야 될 경우
다. NULLIF
NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL을, 같지 않으면 EXPR1을 리턴한다.
-> 특정 값을 NULL로 대체하는 경우 사용
(19) 사원 테이블에서 MGR와 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR를 표시
SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NUIF
FROM EMP;
SELECT ENAME, EMPNO, MGR,
CASE WHEN MGR = 7698
THEN NULL
ELSE MGR
END NUIF
FROM EMP;
라. 기타 NULL 관련 함수 (COALESCE)
인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL 이 아닌 최초의 EXPR을 나타낸다. 만일 모든 EXPR이 NULL이라면 NULL을 리턴
(20) 사원 테이블에서 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되 두 칼 럼 모두 NULL인 경우는 NULL로 표시
SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL
FROM EMP;
SELECT ENAME, COMM, SAL,
CASE WHEN COMM IS NOT NULL
THEN COMM
ELSE (CASE WHEN SAL IS NOT NULL
THEN SAL
ELSE NULL
END)
END COAL
FROM EMP;
7절 GROUP BY, HAVING절
1. 집계 함수
특성
-여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수이다.
- GROUP BY 절은 행들을 소그룹화 한다.
- SELECT 절, HAVING 절, ORDER BY 절에 사용할 수 있다.
옵션
ALL
-Default 옵션으로 생략가능
DISTINCT
-중복 제거
집계 함수의 종류
(1) 테이블 전체가 하나의 그룹이 되는 경우에는 GROUP BY 절 없이 단독으로도 사용 가능
SELECT COUNT(*) "전체 행수", COUNT(HEIGHT) "키 건수",
MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키,ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER;
-> COUNT(HEIGHT)는 NULL값이 아닌 키(HEIGHT) 칼럼의 건수만 출력
-> COUNT(*) : 전체 행의 개수를 출력
-> COUNT(HEIGHT) : NULL인 33건은 제외된 건수의 합
2. GROUP BY절
: FROM 절과 WHERE절 뒤에 오면서, 데이터를 작은 그룹으로 분류해 소그룹에 대한 항목별 통계정보 제공
| SELECT [DISTINCT] 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] ; |
(2) K-리그 선수들의 포지션별 평균키는 어떻게 되는가란 요구 사항을 접수하였다. GROUP BY 절을 사용하지 않고 집계 함수를 사용했을 때 어떤 결과를 보이는지 포지션별 평균키 출력
주의
SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER;
SELECT POSITION 포지션, AVG(HEIGHT) 평균키 *
1행에 오류: ERROR: 단일 그룹의 집계 함수가 아니다
-> GROUP BY 절에서 그룹 단위를 표시해 주어야 SELECT 절에서 그룹 단위의 칼럼과 집계 함수를 사용
(3) SELECT 절에서 사용된 포지션이라는 한글 ALIAS를 GROUP BY 절의 기준으로 사용
SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER
GROUP BY POSITION 포지션; GROUP BY POSITION 포지션 *
3행에 오류: ERROR: SQL 명령어가 올바르게 종료되지 않았다.
-> GROUP BY 절에서는 ALIAS 명을 사용할 수 없다
(4) 포지션별 최대키, 최소키, 평균키를 출력
SELECT POSITION 포지션, COUNT(*) 인원수, COUNT(HEIGHT) 키대상,
MAX(HEIGHT) 최대키, MIN(HEIGHT) 최소키, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER GROUP BY POSITION;
-> 최대키, 최소키, 평균키를 구할 때 키 값이 NULL인 경우는 계산 대상에서 제외
3. HAVING 절
(5) K-리그 선수들의 포지션별 평균키를 구하는데, 평균키가 180 센티미터 이상인 정보만 표시
SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER
WHERE AVG(HEIGHT) >= 180 GROUP BY POSITION;
WHERE AVG(HEIGHT) >= 180 *
3행에 오류: ERROR: 집계 함수는 허가되지 않는다.
-> WHERE 절에는 AVG()라는 집계 함수는 사용할 수 없다
-> HAVING 절은 WHERE 절과 비슷하지만 그룹을 나타내 는 결과 집합의 행에 조건이 적용
(6) HAVING 절을 이용해 평균키가 180 센티미터 이상인 정보만 표시
SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER
GROUP BY POSITION HAVING AVG(HEIGHT) >= 180;
(7) SQL 문장은 GROUP BY 절과 HAVING 절의 순서를 바꾸어서 수행
SELECT POSITION 포지션, AVG(HEIGHT) 평균키
FROM PLAYER HAVING AVG(HEIGHT) >= 180
GROUP BY POSITION;
(8) K-리그의 선수들 중 삼성블루윙즈(K02)와 FC서울(K09)의 인원수 출력
SELECT TEAM_ID 팀ID, COUNT(*) 인원수
FROM PLAYER
WHERE TEAM_ID IN ('K09', 'K02')
GROUP BY TEAM_ID;
(9) 포지션별 평균키만 출력하는데, 최대키가 190cm 이상인 선수를 가지고 있는 포지션의 정보만 출력
SELECT POSITION 포지션, ROUND(AVG(HEIGHT),2) 평균키
FROM PLAYER GROUP BY POSITION
HAVING MAX(HEIGHT) >= 190;
SELECT 절에서 사용하지 않는 MAX 집계 함수를 HAVING 절에서 조건절로 사용
-> HAVING 절은 SELECT 절에 사용되지 않은 칼럼이나 집계 함수가 아니더라도 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시가능
WHERE 절의 조건 변경 : 대상 데이터의 개수가 변경되므로 결과 데이터 값이 변경 가능
HAVING절의 조건 변경 : 결과 데이터 변경은 없고 출력 되는 레코드의 개수만 변경 가능
4. CASE 표현을 활용한 월별 데이터 통계
집계 함수(CASE( ))~GROUP BY
: 모델링의 제1정규화로 인해 반복되는 칼럼의 경우 구분 칼럼을 두고 여러 개의 레코드로 만들어진 집합을, 정해진 칼럼 수만큼 확장해서 집계하는 기법
(10) 부서별 월별 입사자의 평균급여 출력
: 입사 후 1년마다 급여 인상이나 보너스 지급과 같은 일정이 정기적이라면 업무적으로 중요한 정보가 될 수 있다.
STEP 1.
개별 데이터 확인
: 개별 입사정보에서 월별 데이터를 추출하는 작업 진행 [월별 정보가 있다면 생략]
SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) 입사월, SAL
FROM EMP;
STEP 2.
월별 데이터 구분
: 추출된 MONTH 데이터를 Simple Case Expression을 이용해서 12개의 월별 칼럼 으로 구분한다. 실행 결과에서 보여 주는 ENAME 칼럼은 최종 리포트에서 요구되는 데이 터는 아니지만, 정보의 흐름을 이해하기 위해 부가적으로 보여 주는 임시 정보
SELECT ENAME, DEPTNO,
CASE MONTH WHEN 1 THEN SAL END M01,
CASE MONTH WHEN 2 THEN SAL END M02,
CASE MONTH WHEN 3 THEN SAL END M03,
CASE MONTH WHEN 4 THEN SAL END M04,
CASE MONTH WHEN 5 THEN SAL END M05,
CASE MONTH WHEN 6 THEN SAL END M06,
CASE MONTH WHEN 7 THEN SAL END M07,
CASE MONTH WHEN 8 THEN SAL END M08,
CASE MONTH WHEN 9 THEN SAL END M09,
CASE MONTH WHEN 10 THEN SAL END M10,
CASE MONTH WHEN 11 THEN SAL END M11,
CASE MONTH WHEN 12 THEN SAL END M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
FROM EMP);
STEP 3.
부서별 데이터 집계
: 최종적으로 보여주는 리포트는 부서별로 월별 입사자의 평균 급여를 알고 싶다는 요구사항이므로 부서별 평균값을 구하기 위해 GROUP BY 절과 AVG 집계 함수를 사용한다.
[직원 개인에 대한 정보는 더 이상 필요 없으므로 제외]
SELECT DEPTNO, AVG(CASE MONTH WHEN 1 THEN SAL END) M01,
AVG(CASE MONTH WHEN 2 THEN SAL END) M02,
AVG(CASE MONTH WHEN 3 THEN SAL END) M03,
AVG(CASE MONTH WHEN 4 THEN SAL END) M04,
AVG(CASE MONTH WHEN 5 THEN SAL END) M05,
AVG(CASE MONTH WHEN 6 THEN SAL END) M06,
AVG(CASE MONTH WHEN 7 THEN SAL END) M07,
AVG(CASE MONTH WHEN 8 THEN SAL END) M08,
AVG(CASE MONTH WHEN 9 THEN SAL END) M09,
AVG(CASE MONTH WHEN 10 THEN SAL END) M10,
AVG(CASE MONTH WHEN 11 THEN SAL END) M11,
AVG(CASE MONTH WHEN 12 THEN SAL END) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
FROM EMP)
GROUP BY DEPTNO ;
하나의 데이터에 여러 번 CASE 표현을 사용하고 집계 함수가 적용되지만 -> 하나의 SQL문장으로 처리 가능하므로 효율적
(11) Simple Case Expression으로 표현된 위의 SQL과 같은 내용으로 Oracle의 DECODE 함수를 사용한 SQL 문장을 작성
SELECT DEPTNO,
AVG(DECODE(MONTH, 1,SAL)) M01, AVG(DECODE(MONTH, 2,SAL)) M02,
AVG(DECODE(MONTH, 3,SAL)) M03, AVG(DECODE(MONTH, 4,SAL)) M04,
AVG(DECODE(MONTH, 5,SAL)) M05, AVG(DECODE(MONTH, 6,SAL)) M06,
AVG(DECODE(MONTH, 7,SAL)) M07, AVG(DECODE(MONTH, 8,SAL)) M08,
AVG(DECODE(MONTH, 9,SAL)) M09, AVG(DECODE(MONTH,10,SAL)) M10,
AVG(DECODE(MONTH, 11,SAL)) M11, AVG(DECODE(MONTH,12,SAL)) M12
FROM (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) MONTH, SAL
FROM EMP)
GROUP BY DEPTNO ;
5. 집계 함수와 NULL
: 다중 행 함수를 사용하는 경우는 오히려 불필요한 부하가 발생하므로 굳이 NVL 함수를 다중 행 함수 안에 사용할 필요가 없다.
-> 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외
[CASE 표현 사용시 ELSE 절을 생략하게 되면 Default 값이 NULL]
주의
Oracle의 SUM(NVL(SAL,0)), SQL Server의 SUM(ISNULL (SAL,0)) 연산을 삼간다.
개별 데이터의 급여(SAL)가 NULL인 경우는 NULL의 특성으로 자동적으로 SUM 연산에서 빠지는 데,
불필요하게 NVL/ISNULL 함수를 사용해 0(Zero)으로 변환시켜 데이터 건수만큼의 연산이 일어나게 하는 것은 시스템의 자원을 낭비하는 일
->리포트 출력 때 NULL이 아닌 0을 표시하고 싶은 경우에는 NVL(SUM(SAL),0)이나, ISNULL(SUM(SAL),0)처럼 전체 SUM의 결과가 NULL인 경우(대상 건수가 모두 NULL인 경우)에만 한 번 NVL/ISNULL 함수를 사용
(12) 팀별 포지션별 FW, MF, DF, GK 포지션의 인원수와 팀별 전체 인원수를 구하는 SQL 문장을 작성한다. 데이터가 없는 경우는 0으로 표시
SIMPLE_CASE_EXPRESSION 조건 - Oracle
SELECT TEAM_ID, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 ELSE 0 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 ELSE 0 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 ELSE 0 END),0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
SIMPLE_CASE_EXPRESSION 조건 - Oracle
CASE 표현의 ELSE 0, ELSE NULL 문구는 생략 가능하므로 조금 더 짧게 SQL 문장을 작성할 수 있다.
Default 값인 NULL이 적용됨.
SELECT TEAM_ID, NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 END),0) FW,
NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF,
NVL(SUM(CASE POSITION WHEN 'DF' THEN 1 END),0) DF,
NVL(SUM(CASE POSITION WHEN 'GK' THEN 1 END),0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
SEARCHED_CASE_EXPRESSION 조건 - Oracle
SELECT TEAM_ID,
NVL(SUM(CASE WHEN POSITION = 'FW' THEN 1 END), 0) FW,
NVL(SUM(CASE WHEN POSITION = 'MF' THEN 1 END), 0) MF,
NVL(SUM(CASE WHEN POSITION = 'DF' THEN 1 END), 0) DF,
NVL(SUM(CASE WHEN POSITION = 'GK' THEN 1 END), 0) GK,
COUNT(*) SUM
FROM PLAYER
GROUP BY TEAM_ID;
(13) GROUP BY 절 없이 전체 선수들의 포지션별 평균 키 및 전체 평균 키를 출력
SELECT ROUND(AVG(CASE WHEN POSITION = 'MF' THEN HEIGHT END),2) 미드필더,
ROUND(AVG(CASE WHEN POSITION = 'FW' THEN HEIGHT END),2) 포워드, ROUND(AVG(CASE WHEN POSITION = 'DF' THEN HEIGHT END),2) 디펜더, ROUND(AVG(CASE WHEN POSITION = 'GK' THEN HEIGHT END),2) 골키퍼,
ROUND(AVG(HEIGHT),2) 전체평균키
FROM PLAYER;
8절. ORDER BY 절
1. ORDER BY절
:SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용
| SELECT 칼럼명 [ALIAS명] FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼(Column)이나 표현식] [HAVING 그룹조건식] [ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ; ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다. (기본 값이므로 생략 가능) DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다. |
특징
(1) 칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능
(2) 별도로 정렬 방식을 지정하지 않으면 기본 적으로 오름차순이 적용
(3) SQL 문장의 제일 마지막에 위치
(4) 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.
(5) 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력 된다.
예를 들어 ‘01-JAN-2012’는 ‘01-SEP-2012’보다 먼저 출력
(6) Oracle은 NULL 값을 가장 큰 값으로 취급, SQL SERVER는 NULL 값을 가장 작은 값으로 취급
옵션
ASC : 오름차순 [생략가능]
DESC : 내림차순
(1) 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데 사람 이름을 내림차순으로 정렬하여 출력
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
ORDER BY PLAYER_NAME DESC;
(2) 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데 선수들의 포지션 내림차순으로 출력한다.
[칼럼명이 아닌 ALIAS를 이용]
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
ORDER BY 포지션 DESC;
->Oracle은 NULL 값을 가장 큰 값으로 취급
(3) 한 개의 칼럼이 아닌 여러 가지 칼럼(Column)을 기준으로 정렬해본다. 먼저 키가큰 순서대로, 키가 같은 경우 백넘버 순으로 ORDER BY 절을 적용하여 SQL 문장을 작성 하는데, 키가 NULL인 데이터는 제외
SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE HEIGHT IS NOT NULL
ORDER BY HEIGHT DESC, BACK_NO;
-> 칼럼명이나 ALIAS 명을 대신해서 SELECT 절의 칼럼 순서를 정수로 매핑하여 사용가능
(4) 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데 선수들의 백넘버 내림차순, 백넘버가 같은 경우 포지션, 포지션까지 같은 경우 선수명 순서로 출력한다. BACK_NO가 NULL인 경우는 제외하고, 칼럼명이나 ALIAS가 아닌 칼럼 순서를 매핑하여 사용
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE BACK_NO IS NOT NULL ORDER BY 3 DESC, 2, 1;
(5) DEPT 테이블 정보를 부서명, 지역, 부서번호 내림차순으로 정렬해서 출력한다. 아래의 SQL 문장은 출력되는 칼럼 레이블은 다를 수 있지만 결과는 모두 같다.
Case1. 칼럼명 사용 ORDER BY 절 사용
SELECT DNAME, LOC, DEPTNO
FROM DEPT
ORDER BY DNAME, LOC, DEPTNO DESC;
Case2. 칼럼명 + ALIAS 명 사용 ORDER BY 절 사용
SELECT DNAME DEPT, LOC AREA, DEPTNO
FROM DEPT
ORDER BY DNAME, AREA, DEPTNO DESC;
Case3. 칼럼 순서번호 + ALIAS 명 사용 ORDER BY 절 사용
SELECT DNAME, LOC AREA, DEPTNO
FROM DEPT
ORDER BY 1, AREA, 3 DESC;
2. SELECT 문장 실행 순서
: GROUP BY 절과 ORDER BY가 같이 사용될 때 SELECT 문장은 6개의 절로 구성
5. SELECT 칼럼명 [ALIAS명]
1. FROM 테이블명
2. WHERE 조건식
3. GROUP BY 칼럼(Column)이나 표현식
4. HAVING 그룹조건식
6. ORDER BY 칼럼(Column)이나 표현식;
1. 발췌 대상 테이블을 참조한다. (FROM)
2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)
3. 행들을 소그룹화 한다. (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)
5. 데이터 값을 출력/계산한다. (SELECT)
6. 데이터를 정렬한다. (ORDER BY)
-> 관계형 데이터베이스가 데이터를 메모리에 올릴 때 행 단위로 모든 칼럼을 가져오게 되므로,
SELECT 절에서 일부 칼럼만 선택하더라도 ORDER BY 절에서 메모리에 올라와 있는 다른 칼럼의 데이터를 사용 가능
(1) SELECT 절에 없는 EMP 칼럼을 ORDER BY 절에 사용
SELECT EMPNO, ENAME
FROM EMP
ORDER BY MGR;
(2) 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용
SELECT EMPNO
FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);
(3) 인라인 뷰에 미정의된 칼럼을 메인쿼리에서 사용
SELECT MGR FROM
(SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);
SELECT MGR FROM ;
* ERROR: "MGR": 부적합한 식별자
-> 서브쿼리의 SELECT 절에서 선택되지 않은 칼럼들은 계속 유지되는 것이 아니라 서브쿼리 범위를 벗어나면 더 이상 사용할 수 없게 된다. (인라인 뷰도 동일함)
그룹핑 기준을 정의시
-> 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블의 구조를 그대로 가지고 가는 것이 아니라, GROUP BY 절의 그룹핑 기준에 사용된 칼럼과 집계 함수에 사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만든다.
GROUP BY 절 사용시
-> 그룹핑 기준에 사용된 칼럼과 집계 함수에 사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만드는데, 개별 데이터는 필요 없으므로 저장하지 않는다. GROUP BY 이후 수행 절인 SELECT 절이나 ORDER BY 절에서 개별 데이터를 사용하는 경우 에러가 발생한다.
즉, SELECT 절에서는 그룹핑 기준과 숫자 형식 칼럼의 집계 함수를 사용할 수있지만, 그룹핑 기준 외의 문자 형식 칼럼은 정할 수 없다.
(4) GROUP BY 절 사용시 SELECT 절에 일반 칼럼을 사용
SELECT JOB, SAL
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >0
ORDER BY SAL;
SELECT JOB, SAL ; *
ERROR: GROUP BY 표현식이 아니다.
(5) GROUP BY 절 사용시 ORDER BY 절에 일반 칼럼을 사용
SELECT JOB
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >0
ORDER BY SAL;
ORDER BY SAL; *
ERROR: GROUP BY 표현식이 아니다.
(6) GROUP BY 절 사용시 ORDER BY 절에 집계 칼럼을 사용
SELECT JOB
FROM EMP
GROUP BY JOB
HAVING COUNT(*) >0
ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);
-> SELECT SQL에서 GROUP BY 절이 사용되었기 때문에
: SELECT 절에 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY 절에서 사용 가능
3. TOP N 쿼리 [ROWNUM / TOP ()]
주의
Oracle의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라,
데이터의 일부가 먼저 추출된 후(ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음) 데이터에 대한 정렬 작업이 일어나므로 주의
(1) 사원 테이블에서 급여가 높은 3명만 내림차순으로 출력하고자 하는데, 잘못 사용된 SQL의 사례
SELECT ENAME, SAL
FROM EMP
WHERE ROWNUM <4
ORDER BY SAL DESC;
-> 급여 순서에 상관없이 무작 위로 추출된 3명에 한해서 급여를 내림차순으로 정렬
(2) RDER BY 절이 없으면 ORACLE의 ROWNUM 조건과 SQL SERVER의 TOP 절은 같은 결과를 보인다.
ORDER BY 절이 사용되는 경우 ORACLE은 ROWNUM 조건을 ORDER BY 절보다 먼저 처리되는 WHERE 절에서 처리,
-> 정렬 후 원하는 데이터를 얻기 위해서는 2장 4절에서 배울 인라인 뷰에서 먼저 데이터 정렬을 수행한 후 메인쿼리에서 ROWNUM 조건을 사용
SELECT ENAME, SAL
FROM (SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC)
WHERE ROWNUM <4 ;
-> 인라인 뷰를 사용하여 추출하고자 하는 접합을 정렬한 후 ROWNUM을 적용시킴으로써 결과에 참여하는 순서와 추출되는 로우 순서를 일치
9절. 조인
1. JOIN 개요
:두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것
-> PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립도 가능
| SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1, 테이블2 WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; → WHERE 절에 JOIN 조건을 넣는다. |
| SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼명1 = 테이블2.칼럼명2; → ON 절에 JOIN 조건을 넣는다. |
특징
FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 단 두 개의 집합 간에만 조인이 일어난다
FROM 절에 A, B, C 테이 블이 나열되었더라도 특정 2개의 테이블만 먼저 조인 처리되고, 2개의 테이블이 조인되어서 처리된 새로운 데이터 집합과 남은 한 개의 테이블이 다음 차례로 조인
-> 이 순서는 4개 이상의 테이블이 사용되더라도 같은 프로세스를 반복한다.
: A, B, C, D 4개의 테이블을 조인하고자 할 경우 옵티마이저는 ( ( (A JOIN D) JOIN C) JOIN B) 순차적으로 조인을 처리
먼저 A와 D 테이블을 조인 처리하고, 그 결과 집합과 C 테이블을 다음 순서에 조인 처리하고, 마지막으로 3개의 테이블을 조인 처리한 집합과 B 테이블을 조인 수행. 이때 테이블의 조인 순서는 옵티마이저에 의해서 결정
2. EQUI JOIN
: 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로 대부분 PK ↔ FK의 관계를 기반
-> JOIN의 조건은 WHERE 절에 기술하게 되는데 “=” 연산자를 사용해서 표현
(1) 선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER, TEAM WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
-> “테이블명.칼럼명”처럼 테이블명과 칼럼명이 같이 나타낸다. [유일성, 가독성, 유지보수성]
가. 선수-팀 EQUI JOIN
선수(PLAYER) 테이블과 팀(TEAM) 테이블에서 K-리그 소속 선수들의 이름, 백넘버와 그 선수가 소속되어 있는 팀명 및 연고지 출력
(2) 데이터를 출력하기 위한 SELECT SQL 문장을 작성
SELECT PLAYER.PLAYER_NAME, PLAYER.BACK_NO, PLAYER.TEAM_ID, TEAM.TEAM_NAME, TEAM.REGION_NAME
FROM PLAYER, TEAM
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT PLAYER.PLAYER_NAME, PLAYER.BACK_NO, PLAYER.TEAM_ID, TEAM.TEAM_NAME, TEAM.REGION_NAME
FROM PLAYER INNER JOIN TEAM
ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
-> SELECT 절에서 칼럼에 대한 ALIAS를 사용하는 것처럼 FROM 절의 테이블에 대해서도 ALIAS를 사용가능
: 가독성을 위해 테이블명 대신 ALIAS를 주로 사용
(3) 칼럼과 테이블에 ALIAS를 적용하여 위 SQL을 수정한다. 실행 결과는 ALIAS 적용 전과 같음을 확인가능
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, P.TEAM_ID 팀코드,
T.TEAM_NAME 팀명, T.REGION_NAME 연고지
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, P.TEAM_ID 팀코드,
T.TEAM_NAME 팀명, T.REGION_NAME 연고지
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID;
나. 선수-팀 WHERE 절 검색 조건
:WHERE 절에서 JOIN 조건 이외의 검색 조건에 대한 제한 조건을 덧붙여 사용가능
-> EQUI JOIN의 최소한의 연관 관계를 위해서 테이블 개수 - 1개의 JOIN 조건을 WHERE 절에 명시하고, 부수적인 제한 조건을 논리 연산자를 통하여 추가로 입력
(4) WHERE 절에 포지션이 골키퍼인(골키퍼에 대한 포지션 코드는 ‘GK’임) 선수들에 대한 데이터만을 백넘버 순으로 출력
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, T.REGION_NAME 연고지, T.TEAM_NAME 팀명
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID AND P.POSITION = 'GK'
ORDER BY P.BACK_NO;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT P.PLAYER_NAME 선수명, P.BACK_NO 백넘버, T.REGION_NAME 연고지, T.TEAM_NAME 팀명
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID WHERE P.POSITION = 'GK'
ORDER BY P.BACK_NO;
주의사항
ALIAS를 적용해서 SQL 문장을 작성했을 경우,
WHERE 절과 SELECT 절에는 테이블명이 아닌 테이블에 대한 ALIAS를 사용
(5) FROM 절에서 테이블에 대한 ALIAS를 정의했는데, SELECT 절이나 WHERE 절에서 테이블명을 사용한다면 DBMS의 옵티마이저가 칼럼명이 부적합하다는 에러를 파싱 단계에서 발생
SELECT PLAYER.PLAYER_NAME 선수명, P.BACK_NO 백넘버, T.REGION_NAME 연고지, T.TEAM_NAME 팀명
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID AND P.POSITION = 'GK'
ORDER BY P.BACK_NO;
SELECT PLAYER.PLAYER_NAME 선수명, P.BACK_NO 백넘버, *
1행에 오류:ERROR: 열명이 부적합하다.
->(SQL 문장의 파싱 순서는 FROM 절, WHERE 절, SELECT 절, ORDER BY 절 순서이다.)
다. 팀-구장 EQUI JOIN
(6) 팀(TEAM) 테이블과 구장(STADIUM) 테이블의 관계를 이용해서 소속팀이 가지고 있는 전용구장의 정보를 팀의 정보와 함께 출력하는 SQL문을 작성
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID,
STADIUM.STADIUM_NAME, STADIUM.SEAT_COUNT
FROM TEAM, STADIUM WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID,
STADIUM.STADIUM_NAME, STADIUM.SEAT_COUNT
FROM TEAM INNER JOIN STADIUM
ON TEAM.STADIUM_ID = STADIUM.STADIUM_ID;
위 SQL문과 ALIAS를 사용한 아래 SQL문은 같은 결과를 얻을 수 있다.
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME, S.SEAT_COUNT FROM TEAM T, STADIUM S WHERE T.STADIUM_ID = S.STADIUM_ID;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME,
S.SEAT_COUNT
FROM TEAM T INNER JOIN STADIUM S
ON T.STADIUM_ID = S.STADIUM_ID;
중복이 되지 않는 칼럼의 경우 ALIAS를 사용하지 않아도 되므로, 아래 SQL 문은 위 SQL문과 같은 결과를 얻을 수 있다. 그러나 같은 이름을 가진 중복 칼럼의 경우는 테이블명이나 ALIAS가 필수 조건이다.
SELECT REGION_NAME, TEAM_NAME, T.STADIUM_ID, STADIUM_NAME,
SEAT_COUNT FROM TEAM T, STADIUM S
WHERE T.STADIUM_ID = S.STADIUM_ID;
3. Non EQUI JOIN
: 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용된다. Non EQUI JOIN의 경우에는 “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행
(7) 어떤 사원이 받고 있는 급여가 어느 등급에 속하는 등급인지 출력
SELECT E.ENAME, E.JOB, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
(8) 사원 14명 모두에 대해 아래 SQL로 급여와 급여등급 출력
SELECT E.ENAME 사원명, E.SAL 급여, S.GRADE 급여등급
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
4. 3개 이상 TABLE JOIN
(9) 선수 테이블, 팀 테이블, 운동장 테이블을 예로 들었다. 선수들 별로 홈그라운드 경기장이 어디인지를 출력
중간에 팀 테이블이라는 서로 연관관계가 있는 테이블을 추가해서 세 개의 테이블을 JOIN 해야만 원하는 데이터를 얻을 수 있다.
세 개의 테이블에 대한 JOIN이므로 WHERE 절에 2개 이상의 JOIN 조건이 필요
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지,
T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID ORDER BY 선수명;
또는 INNER JOIN을 명시하여 사용할 수도 있다.
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지,
T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P INNER JOIN TEAM T
ON P.TEAM_ID = T.TEAM_ID
INNER JOIN STADIUM S
ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;
-> JOIN이 필요한 기본적인 이유는 과목1에서 배운 정규화에서부터 출발한다.
정규화
불필요한 데이터의 정합성을 확보 하고 이상현상(Anomaly) 발생을 피하기 위해, 테이블을 분할하여 생성
성능 측면에서도 간단한 데이터를 조회하는 경우에도 규모가 큰 테이블에서 필요한 데이터를 찾아야 하기 때문에 오히려 검색 속도가 떨어질 수도 있다.
테이블을 정규화하여 데이터를 분할하게 되면 위와 같은 문제는 자연스럽게 해결 된다.
그렇지만 특정 요구조건을 만족하는 데이터들을 분할된 테이블로부터 조회하기 위해서는 테이블 간에 논리적인 연관관계가 필요하고 그런 관계성을 통해서 다양한 데이터들을 출력할수 있는 것이다.
JOIN 조건 : 논리적인 관계를 구체적으로 표현하는 것
| 1. 관계형 데이터베이스 개요 2. DDL (1) 데이터 유형 (2) CREATE TABLE 가. 테이블과 칼럼 정의 나. CREATE TABLE 다. 제약조건 라. 생성된 테이블 구조 확인 마. SELECT 문장을 통한 테이블 생성 사례 (3) ALTER TABLE 가. ADD COLUMN 나. DROP COLUMN 다. MODIFY COLUMN 라. DROP CONSTRAINT 마. ADD CONSTRAINT (4) RENAME TABLE (5) DROP TABLE (6) TRUNCATE TABLE 3. DML (1) INSERT (2) UPDATE (3) DELETE (4) SELECT (5) 산술 연산자와 합성 연산자 4. TCL (1) 트랜잭션 개요 (2) COMMIT (3) ROLLBACK (4) SAVEPOINT 5. WHERE 절 (1) WHERE 조건절 개요 (2) 연산자의 종류 (3) 비교 연산자 (4) SQL 연산자 (5) 논리 연산자 (6) 부정 연산자 (7) ROWNUM, TOP 사용 6. 함수 (1) 내장 함수 개요 (2) 문자열 함수 (3) 숫자형 함수 (4) 날짜형 함수 (5) 변환형 함수 (6) CASE 표현 (7) NULL 관련 함수 가. NVL/ISNULL 함수 나. NULL과 공집합 다. NULLIF 라. 기타 NULL 관련 함수(COALESCE) 7. GROUP BY, HAVING절 (1) 집계 함수 (2) GROUP BY절 (3) HAVING절 (4) CASE 표현을 활용한 월별 데이터 집계 (5) 집계 함수와 NULL 8. ORDER BY절 (1) ORDER BY 정렬 (2) SELECT 문장 실행 순서 (3) TOP N 쿼리 [ROWNUM, TOP()] 9. 조인 (1) JOIN 개요 (2) EQUI JOIN 가. 선수-팀 EQUI JOIN 사례 나. 선수-팀 WHERE 절 검색 조건 사례 다. 팀-구장 EQUI JOIN 사례 3. Non EQUI JOIN 4. 3개 이상 TABLE JOIN |
'Data Science > SQLD' 카테고리의 다른 글
| [SQLD] SQL 활용 2-2. 집합 연산자 (0) | 2021.10.05 |
|---|---|
| [SQLD] SQL 활용 2-1. 표준 조인 (0) | 2021.10.05 |
| [SQLD] 데이터 모델링의 이해 1-4. 관계 (0) | 2021.09.23 |
| [SQLD] 데이터 모델링의 이해 1-3. 속성 (0) | 2021.09.23 |
| [SQLD] 데이터 모델링의 이해 1-2. 엔터티 (0) | 2021.09.23 |
