close
프로필 배경
프로필 로고

CodeOneSpoon

Backend/DataBase · 2023. 11. 7. fullscreen 넓게보기

SQL 기본

관계형 데이터 베이스

데이터베이스

  • 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것.
  • 효율적인 데이터의 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고, 필요시 데이터를 복구하기 위한 강력한 기능의 소프트웨어를 필요로 하게 되었고 이러한 기본적인 요구사항을 만족시켜주는 시스템을 DBMS하고 함.

 

관계형 데이터베이스

  • 정규화를 통한 합리적인 테이블 모델링을 수행. 
  • 정규화를 통해 이상 현상을 제거하고 데이터 중복을 피할 수 있으며, 동시성 관리, 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작 가능. 
  • 메타 데이터를 총괄 관리할 수 있기 대문에 데이터의 성격, 속성 또는 표현 방법등을 체계화 가능. 
  • 데이터 표준화를 통한 데이터 품질을 확보할 수 있다는 장점을 가짐. 
  • 다양한 제약조건을 이용하여 사용자가 실수로 조건에 위배되는 데이터를 입력 한다든지, 관계를 연결하는 중요 데이터를 삭제하는 것을 방지하여 데이터 무결성을 보장함. 

 

SQL(Structured Query Language)

  • SQL은 관계형 데이터베이스에서 데이터 정의, 데이터 조작 그리고 데이터 제어를 하기 위해 사용하는 언어.
  • 데이터를 꺼내고 조회, 새로운 데이터를 입력/수정/삭제하는 행위등 소통하는 방식으로 사용됨. 
종류 명령어 설명
데이터 조작어 (DML: Data Manipulation Language) SELECT 데이터를 조회하거나 검색하기 위한 검색어
INSERT, UPDATE, DELETE 데이터의 상태를 변경하는데 사용되는 명령어
데이터 정의어 (DDL: Data Definition Language) CREATE, ALTER, DROP, RENAME 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어. 
테이블을 생성하거나 변경 그리고 삭제 또는 이름 변경을 하는데 사용되는 명령어
데이터 제어어 (DCL: Data Control Language) GRANT, REVOKE 데이터베이스에 대한 권한을 부여 및 회수하는 명령어
트랜잭션 제어어 (TCL: Transaction Control Language) COMMIT, ROLLBACK 논리적인 작업 단위를 묶어서 DML에 의해 조작된 결과의 작업 단위 별로 적용 및 취소하는 명령어 

 

테이블

  • 관계형 데이터베이스에서는 데이터는 테이블 형태로 저장이 됨.
  • 테이블은 데이터를 저장하는 객체로서 관계형 데이터베이스의 기본단위임.

 

테이블 용어

  • 테이블 : 행과 컬럼의 2차원 구조를 가진 데이터 저장소
  • 컬럼 : 테이블에서 세로방향으로 이루어진 하나하나의 속성
  • 행 : 테이블에서 가로방향으로 이루어진 데이터
  • 정규형 : 테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스.
  • 기본 키 : 테이블에 존재하는 각 행을 한가지 의미로 특정할 수 있는 한 개 이상의 컬럼.
  • 외래 키 : 다른 테이블로부터 관계를 맺기위한 컬럼. 

 

ERD(Entity Relationship Diagram)

  • 테이블 간 서로의 상관 관계를 그림으로 도식화를 한 것.
  • 엔티티, 관계, 속성 3가지로 구성이 되어져 있음. 

 

DDL(Data Definition Language)

주요 데이터 유형 정리

  • CHAR : 고정 길이 문자열, 길이가 맞지 않는 경우 남은 공간은 공백으로 채워짐. 
  • VARCHAR: 가변길이 문자열, 길이가 맞지 않아도 자동으로 길이를 조절함. 
  • NUMBER(L, D): 정수, 실수를 저장함. L값은 전체 자리 수, D값은 소수점 자리 수.
  • DATE: 날짜와 시각정보 "년월일시분초"를 표현함. 
CREATE TABLE SQLD.TB_DEPT_TEMP
(
	DEPT_CD CHAR(6) NOT NULL,
	DEPT_NM VARCHAR(150) NOT NULL,
	UPPER_DEPT_CD CHAR(6)
);
  • 테이블 명이 중복되면 안됨.
  • 테이블 명과 컬럼명은 반드시 문자로 시작해야함. 
  • A-Z, a-z, 0-9, _, $, # 만 허용

 

제약조건

  • 기본 키 (Primary Key): 하나의 테이블에 단 하나의 기본 키만 적용가능, 자동으로 UNIQUE , NOT NULL 제약조건 지정.
  • 고유키 (Unique Key): 테이블에 저장된 행 데이터를 고유하게 식별하기 위해 생성. NULL 입력가능
  • NOT NULL : NULL 값의 입력을 금지.
  • CHECK : 입력할 수 있는 값 종류 및 범위를 제한.
  • 외래 키 : 다른 테이블의 기본 키를 외래 키로 지정하는 경우 생성. (참조무결성제약조건) 

 

ALTER TABLE

CREATE TABLE SQLD.TB_DEPT_TEMP
(
	DEPT_CD CHAR(6) NOT NULL,
	DEPT_NM VARCHAR(150) NOT NULL,
	UPPER_DEPT_CD CHAR(6)
);
ALTER TABLE SQLD.TB_DEPT_TEMP ADD CONSTRAINT PK_TB_DEPT_TEMP PRIMARY KEY (DEPT_CD);

CREATE TABLE SQLD.TB_EMP_TEMP
(
	EMP_NO CHAR(10) NOT NULL
    , EMP_NM VARCHAR2(150) NOT NULL
    , BIRTH_DE CHAR(8) NOT NULL
    , LUNAR_YN CHAR(1) NOT NULL
    , SEX_CD CHAR(3) NOT NULL
    , ADDR VARCHAR2(1000) NOT NULL
    , TEL_NO VARCHAR2(150) NOT NULL
    , FINAL_EDU_SE_CD CHAR(3) NOT NULL
    , SAL_TRANS_BANK_CD CHAR(3) NOT NULL
    , SAL_TRANS_ACCNT_NO VARCHAR2(20) NOT NULL
    , DIRECT_MANAGER_EMP_NO CHAR(10)
    , DEPT_CD CHAR(6) NOT NULL
);

ALTER TABLE SQLD.TB_EMP_TEMP ADD CONSTRAINT PK_TB_EMP_TEMP PRIMARY KEY (EMP_NO);

ALTER TABLE SQLD.TB_EMP_TEMP ADD CONSTRAINT FK_TB_EMP_TEMP_01 FOREIGN KEY (DEPT_CD) REFERENCES SQLD.TB_DEPT_TEMP (DEPT_CD);

 

ADD COLUMN

ALTER TABLE SQLD.TB_EMP_TEMP ADD (MARRIED_YN CHAR(1));

 

DROP COLUMN

ALTER TABLE SQLD.TB_EMP_TEMP DROP COLUMN MARRIED_YN;

 

MODIFY COLUMN

ALTER TABLE SQLD.TB_EMP_TEMP MODIFY (MARRIED_YN CHAR(1) DEFAULT 'N' NOT NULL NOVALIDATE);

 

 

TABLE COPY

CREATE TABLE SQLD.TB_EMP_TEMP_2 AS SELECT * FROM SQLD.TB_EMP_TEMP;

 

 

RENAME

ALTER TABLE SQLD.TB_EMP_TEMP_2 RENAME COLUMN TEL_NO TO PHONE_NO;

ALTER TABLE SQLD.TB_EMP_TEMP_2 RENAME COLUMN PHONE_NO TO TEL_NO;

RENAME TB_EMP_TEMP_2 TO TB_EMP_TEMP_3;

 

 

DROP CONTRAINT

ALTER TABLE SQLD.TB_EMP_TEMP DROP CONSTRAINT FK_TB_EMP_TEMP_01;

 

 

TRUNCATE

  • TRUNCATE 수행시 ROLLBACK 불가.
TRUNCATE TABLE SQLD.TB_EMP_TEMP_3;

 

 

DROP TABLE

DROP TABLE SQLD.TB_EMP_TEMP_3;

 

 

DML(DATA MANIPULATION LANGUAGE)

INSERT

INSERT INTO SQLD.TB_CERTI T (T.CERTI_CD, T.CERTI_NM, T.ISSUE_INSTI_NM) VALUES ('100021', 'SQLD합격패스', '패스트캠퍼스');
COMMIT;

 

UPDATE

UPDATE SQLD.TB_CERTI A
SET A.ISSUE_INSTI_NM = '패스트캠퍼스온라인'
WHERE A.CERTI_CD = '100021'
;

 

DELETE

DELETE
FROM SQLD.TB_CERTI A
WHERE A.CERTI_CD = '100021'
;

 

SELECT

출력하고자 하는 컬럼명을 지정.

SELECT A.CERTI_CD
, A.CERTI_NM
, A.ISSUE_INSTI_NM
FROM SQLD.TB_CERTI A;

 

DISTINCT

중복값은 하나의 값으로만 출력.

SELECT DISTINCT A.ISSUE_INSTI_NM
FROM SQLD.TB_CERTI A;

 

*  와일드카드

*을 사용하면 모든 컬럼이 조회

SELECT *
FROM SQLD.TB_CERTI A;

 

ALIAS 지정

AS를 이용하여 컬럼의 이름을 지정 가능

SELECT A.CERTI_CD AS 자격증코드
, A.CERTI_NM AS 자격증명
, A.ISSUE_INSTI_NM AS 발급기관명
FROM SQLD.TB_CERTI A;

 

 

합성 연산자를 이용한 문자열 연결

"||" 연산자를 이용하여 문자열을 연결할 수 있다

SELECT
A.CERTI_NM || '(' || A.CERTI_CD || ')' || '-' || A.ISSUE_INSTI_NM AS CERTI_INFO
FROM SQLD.TB_CERTI A;

 

 

DUAL 테이블을 이용한 연산 수행

SELECT ( (1+1) * 3 ) / 6 AS CALC_RESULT FROM DUAL;

 

 

TCL(TRANSACTION CONTROL LANGUAGE)

트랜잭션의 특성

  • 트랜잭션은 데이터베이스의 논리적 연산단위를 말함. 
  • 하나의 트랜잭션에는 하나 이상의 SQL 문이 포함된다. 
  • 트랜잭션은 분할할 수 없는 최소의 단위임. 
특성 설명
원자성(Atomicity) 트랜잭션에서 정의된 연산들은 모두 성공적으로 끝나거나 모두 실패해야 한다.
일관성(Consistency) 트랜잭션 실행되기 전의 데이터베이스의 내용이 잘못되어 있지 않다면 실행된 이후에서도 데이터의 내용이 잘 못되면 안된다.
고립성(Isolation) 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성(Durability) 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. 

 

 

commit

입력한 자료나 수정한 자료에 대해서 문제가 없다고 판단되면 commit 명령어를 통해 트랜잭션을 완료할 수 있음. 

 

commit 전의 상태

  • 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
  • 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
  • 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  • 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.

 

commit 이후의 상태

  • 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
  • 이전 데이터는 영원히 잃어버리게 된다.
  • 모든 사용자는 결과를 볼 수 있다.
  • 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

rollback

  • 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있도록 하는 기능
  • 롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
INSERT INTO SQLD.TB_CERTI T (T.CERTI_CD, T.CERTI_NM, T.ISSUE_INSTI_NM) VALUES ('100022', 'SQL지식보유자', '패스트캠퍼스');
ROLLBACK; // insert 내용이 저장 되지 않고 사라짐

 

save point

  • 저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백 하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 할 수 있다.
  • 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행
SAVEPOINT SVPT1; // 여기가 세이브 포인트 지점
INSERT INTO SQLD.TB_CERTI T (T.CERTI_CD, T.CERTI_NM, T.ISSUE_INSTI_NM) VALUES ('100022', 'SQL지식보유자', '패스트캠퍼스');

UPDATE SQLD.TB_CERTI
SET CERTI_NM = 'SQL경험보유자'
WHERE CERTI_CD = '100022'
;
ROLLBACK TO SVPT1; // 세이브 포인트 지점으로 롤백, INSERT및 UPDATE는 모두 롤백 됨

DELETE
FROM SQLD.TB_CERTI
WHERE CERTI_CD = '100022'
;
SELECT * FROM SQLD.TB_CERTI
WHERE CERTI_CD = '100022'
;

 

 

COMMIT 및 ROLLBACK 처리와는 상관없이 트랜잭션 처리가 일어나는 상황

  • CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋 된다.
  • DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋 된다.
  • 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋 된다.
  • 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백 된다.

 

WHERE 절

개요

SQL 문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다. WHERE 절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다. 

  • 컬럼명 (보통 왼쪽에 배치)
  • 비교연산자
  • 문자, 숫자, 표현식 (보통 오른쪽에 배치)
  • 비교 컬럼명 (JOIN시)

 

연산자 종류

  • 비교 연산자
  • SQL 연산자
  • 논리 연산자

 

연산자의 우선순위

  1. () 괄호
  2. NOT 연산자 
  3. 비교 연산자, SQL 비교 연산자
  4. AND
  5. OR

 

비교 연자

  • = : ~ 와 같다 
  • > : ~ 보다 크다
  • >= : ~ 보다 크거나 같다
  • < : ~ 보다 작다
  • <= : ~ 보다 작거나 같다

 

SQL연자

  • BETWEEN A AND B : A 와 B 사이의 값
  • IN (LIST) : 리스트에 있는 값중 하나라도 일치하는 값
  • LIKE '비교 문자열' : 비교문자열의 형태와 일치하는 값 (와일드카드 사용)
  • IS NULL : 값이 NULL인 값
SELECT 
	*
FROM TB_EMP A
WHERE A.BIRTH_DE BETWEEN '19910101' AND '19991231'
	AND A.DEPT_CD IN ('100004', '100006')
	AND A.ADDR LIKE '%수원시%';
SELECT 
	*
FROM TB_EMP A WHERE A.EMP_NM LIKE '박__';
SELECT	
	A.EMP_NO ,
	A.EMP_NM ,
	A.SEX_CD ,
	A.BIRTH_DE ,
	A.DEPT_CD ,
	A.ADDR ,
	NVL(A.DIRECT_MANAGER_EMP_NO, '상사없음') DIRECT_MANAGER_EMP_NO
FROM TB_EMP A 
WHERE A.DIRECT_MANAGER_EMP_NO IS NULL;

와일드 카드

  • % : 0개 이상의 어떤 문자를 의미.
  • _ : 1개인 달일 문자를 의미. 

 

논리 연산자

  • AND : 앞 조건과 뒤 조건이 모두 참이어야 한다. 
  • OR : 앞 조건과 뒤 조건 중 하나라도 참이어야 한다. 
  • NOT : 조건이 거짓이면 된다. 
SELECT 
	*
FROM TB_EMP A
WHERE A.BIRTH_DE BETWEEN '19900101' AND '19991231'
	AND A.DEPT_CD IN ('100004', '100006')
	AND (
		A.ADDR LIKE '%수원시%'
		OR A.ADDR LIKE '%성남시%'
	)
;

 

부정 연산자

  • != : 같지 않다
  • <> : 같지 않다 
  • ^= : 같지 않다
  • NOT 컬럼명 : ~ 와 같지 않다 
  • NOT 컬럼명 > : ~ 보다 크지 않다
SELECT 
	*
FROM SQLD.TB_SAL_HIS A
WHERE A.PAY_DE >= '20200501'
AND A.PAY_DE <= '20200531'
AND NOT A.PAY_AMT >= 5500000
;
SELECT 
	*
FROM TB_EMP A
WHERE A.BIRTH_DE BETWEEN '19910101' AND '19991231'
AND A.DEPT_CD IN ('100004', '100006')
AND A.SEX_CD <> '1'
;

 

부정 SQL 연자

  • NOT BETWEEN A AND B : A 와 B 사이에 있지 않는 값
  • NOT IN (LIST) : LIST에 있는 값이 아닌 값
  • IS NOT NULL : NULL이 아닌 값 

 

문자유형 비교방법 - 양쪽이 모두 CHAR 타입인 경우

  • 길이가 서로 다르면 작이 쪽에 공백을 추가하여 길이를 맞춰야 함.
  • 달라진 첫번째 값에 따라 크기를 비교
  • 공백의 수만 다르다면 같은 값으로 결정.

 

문자유형 비교방법 - 비교연산자중 한쪽이 VARCHAR인 경우

  • 길이가 다르다면 짧은 것이 끝날 때 까지만 비교한 후 길이가 긴 것이 크다고 판단한다.
  • 길이가 같고 다른 것이 없다면 같다고 판단한다.
  • VARCHAR는 공백도 문자로 판단한다.
  • TRIM 연산자를 통해  VARCHAR 공백을 제거하고 CHAR 값을 비교하면 된다. 

 

 

함수 (Function)

단일 행 함수의 주요 특징

  • SELECT, WHERE, ORDER BY 절에 사용이 가능하다. 
  • 각 행들에 대해 개별적으로 작용하고 각각의 행에 대한 조작 결과를 리턴함.
  • 여러 인자를 입력해도 하나의 결과만 리턴함.
  • 함수의 중첩이 가능함. 

 

단일 행 함수의 종류

  • 문자형 함수 : LOWER, UPPER, SUBSTR, LENGTH, RTRIM, TRIM, ASCII
  • 숫자형 함수 : ABS, MOD, ROUND, TRUNC, SIGN, CHR, CEIL, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
  • 날짜형 함수 : SYSDATE, EXTRACT, TO_NUMBER
  • 변환형 함수 : TO_NUMBER, TO_CHAR, TO_DATA, COVERT
  • NULL관련 함수 : NVL, NULLIF, COALESCE
SELECT
LOWER('SQL Developer') AS "LOWER('SQL Developer')" --소문자로 변환
, UPPER('SQL Developer') AS "UPPER('SQL Developer')" --대문자로 변환
, ASCII('A') AS "ASCII('A')" --아스키코드값 출력
, CHR('65') AS "CHR('65')" --아스키코드값의 문자 출력
, CONCAT('SQL', 'Developer') AS "CONCAT('SQL', 'Developer')" --문자열 결합
, SUBSTR('SQL Developer', 1, 3) AS "SUBSTR('SQL Developer', 1, 3)" --문자열 잘라내기
, LENGTH('SQL') AS "LENGTH('SQL')" --문자열의 길이 출력
, LTRIM(' SQL') AS "LTRIM(' SQL')" --왼쪽 공백 제거
, RTRIM('SQL ') AS "RTRIM('SQL ')" --오른쪽 공백 제거
FROM DUAL
;
SELECT
ABS(-15) AS "ABS(-15)" --절대값을 반환
, SIGN(10) AS "SIGN(10)" --양수일경우 1, 음수일경우 -1, 0일 경우 0 반환
, MOD(8,3) AS "MOD(8,3)" --나머지를 반환
, CEIL(38.678) AS "CEIL(38.678)" --무조건 올림
, FLOOR(38.678) AS "FLOOR(38.678)" --무조건 버림
, ROUND(38.678, 2) AS "ROUND(38.678, 2)" --소수점 2번째 자리에서 반올림
, TRUNC(38.678) AS "TRUNC(38.678)" --0의 자리에서 무조건 자름
, TRUNC(38.678, 1) AS "TRUNC(38.678, 1)" --1의 자리에서 무조건 자름
, TRUNC(38.678, 2) AS "TRUNC(38.678, 2)" --2의 자리에서 무조건 자름
, TRUNC(38.678, 3) AS "TRUNC(38.678, 3)" --3의 자리에서 무조건 자름
FROM DUAL
;
SELECT SYSDATE AS "SYSDATE" --현재 년월일시분초 출력
, EXTRACT(YEAR FROM SYSDATE) AS "EXTRACT(YEAR FROM SYSDATE)" --년 출력
, EXTRACT(MONTH FROM SYSDATE) AS "EXTRACT(MONTH FROM SYSDATE)" --월 출력
, EXTRACT(DAY FROM SYSDATE) AS "EXTRACT(DAY FROM SYSDATE)" --일 출력
, TO_CHAR(SYSDATE, 'YYYY') AS "TO_CHAR(SYSDATE, 'YYYY')" --년 출력(문자열)
, TO_CHAR(SYSDATE, 'MM') AS "TO_CHAR(SYSDATE, 'MM')" --월 출력(문자열)
, TO_CHAR(SYSDATE, 'DD') AS "TO_CHAR(SYSDATE, 'DD')" --일 출력(문자열)
, TO_CHAR(SYSDATE, 'HH24') AS "TO_CHAR(SYSDATE, 'HH24')" --시 출력(문자열)
, TO_CHAR(SYSDATE, 'MI') AS "TO_CHAR(SYSDATE, 'MI')" --분 출력(문자열)
, TO_CHAR(SYSDATE, 'SS') AS "TO_CHAR(SYSDATE, 'SS')" --초 출력(문자열)
, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))" --년 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'MM'))" --월 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'DD'))" --일 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'HH24'))" --시 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'MI')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'MI'))" --분 출력
, TO_NUMBER(TO_CHAR(SYSDATE, 'SS')) AS "TO_NUMBER(TO_CHAR(SYSDATE, 'SS'))" --초 출력
FROM DUAL
;
SELECT
SYSDATE AS "SYSDATE" --현재 년월일 시분초
, SYSDATE - 1 AS "SYSDATE - 1" --1일을 뺀 년월일 시분초
, SYSDATE - (1/24) AS "SYSDATE - (1/24)" --1시간을뺀 년월일 시분초
, SYSDATE - (1/24/60) AS "SYSDATE - (1/24/60)" --1분을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) AS "SYSDATE - (1/24/60/60)" --1초을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) * 10 AS "SYSDATE - (1/24/60/60) * 10" --10초을뺀 년월일 시분초
, SYSDATE - (1/24/60/60) * 30 AS "SYSDATE - (1/24/60/60) * 30" --30초을뺀 년월일 시분초
FROM DUAL
;

 

데이터 변환의 종류

  • 명시적 형 변환 : 데이터 변환 형 함수로 데이터 유형을 변환하도록 명시.
  • 암시적 형 변환 : DBMS가 자동으로 데이터 유형을 변환하는 경우.
SELECT
TO_CHAR(SYSDATE, 'YYYY/MM/DD') "TO_CHAR(SYSDATE, 'YYYY/MM/DD')"
, TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') "TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')"
, TO_CHAR(10.25, '$999,999,999.99') AS "TO_CHAR(10.25, '$999,999,999.99')"
, TO_CHAR(12500, 'L999,999,999') AS "TO_CHAR(12500, 'L999,999,999')"
, TO_NUMBER('100') + TO_NUMBER('100') AS "TO_NUMBER('100') + TO_NUMBER('100')"
FROM DUAL
;

 

단일 행 CASE 표현의 종류

  • CASE ~ WHEN ~ THEN ~ ELSE ~ END : 조건이 맞으면 THEN 절 수행 아니면 ELSE 절을 수행. 
  • DECODE(표현식 or 컬럼명, 기준1, 결과1, 기준2, 결과2, 디폴트) 
SELECT CASE WHEN SAL_CD = '100001' THEN '기본급'
WHEN SAL_CD = '100002' THEN '상여급'
WHEN SAL_CD = '100003' THEN '특별상여급'
WHEN SAL_CD = '100004' THEN '야근수당'
WHEN SAL_CD = '100005' THEN '주말수당'
WHEN SAL_CD = '100006' THEN '점심식대'
WHEN SAL_CD = '100007' THEN '복지포인트'
ELSE '유효하지않음'
END SAL_NM
FROM TB_SAL
;
SELECT DECODE(SAL_CD, '100001', '기본급', '100002', '상여급', '기타') AS SAL_NM
FROM TB_SAL
;

 

NULL  연산 결과

  • NVL (컬럼, 결과) : 기준 컬럼이 NULL, 이면 결과 값 반환 
  • NULLIF (값, 값) : 두개가 같으면 NULL 반환 아니면 첫번째 값 반환 
  • COALESCE(값, 값, 값) : NULL이 아닌 값을 우선 반환.

 

GROUP BY, HAVING 절

집계 함수

  • 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수. 
  • GROUP BY 절은 행들을 소 그룹화 한다. 
  • SELECT 절, HAVING 절, ORDER BY 절에 사용 가능. 

 

ALL 과 DISTINCT

  • ALL : DEFAULT 옵션임 생략 가능.
  • DISTINCT : 유일한 값을 출력함.

 

집계 함수의 종류

  • COUNT(*) : NULL 값을 포함한 행의 수를 출력 
  • COUNT(표현식) : 표현식의 값이 NULL 아닌 행의 수를 출력
  • SUM(표현식) : 표현식이 NULL 값인 것을 제외한 합계를 출력
  • AVG(표현식) : 표현식이 NULL값인 것을 제외한 평균을 출력
  • MAX(표현식) : 표현식이 NULL값인 것을 제외한 최대값을 출력
  • MIN(표현식) : 표현식이 NULL값인 것을 제외한 최소값을 출력
  • STDDEV(표현식) : 표현식이 NULL값인 것을 제외한  표준편차를 출력
  • VARIAN(표현식) : 표현식이 NULL값인 것을 제외한  분산을 출력   
SELECT MAX(BIRTH_DE)
, MIN(BIRTH_DE)
, COUNT(*)
FROM TB_EMP;

 

GROUP BY 절

  • GROUP BY 절을 통해 소그룹 별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다. 
  • 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다. 
  • GROUP BY 절에서는 SELECT 절과는 달리 ALIAS를 사용할 수 없다. 
  • 집계 함수는 WHERE 절에는 올 수 없다. (집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다.)
  • WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다. 
  • HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시. 
  • HAVING 절에서 제한 조건을 두어 조건을만족하는 내용만 출력.
  • HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치. 바뀌어도 상관은 없음. 
SELECT A.DEPT_CD
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS DEPT_NM
, MAX(A.BIRTH_DE) AS "가장 늦은 생년월일"
, MIN(A.BIRTH_DE) AS "가장 빠른 생년월일"
, COUNT(*) AS "직원수"
FROM TB_EMP A
GROUP BY A.DEPT_CD
ORDER BY A.DEPT_CD ;

 

HAVING 절

  • WHERE 절에서는 집계 함수를 쓸 수 없다. 
  • 집계된 결과 집합을 기준으로 특정 조건을 주고 싶은 경우 HAVING절을 이용. 
  • 그룹을 나타내는 결과 집합의 행에 조건이 적용.
SELECT A.DEPT_CD
, (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS DEPT_NM
, MAX(A.BIRTH_DE) AS "가장 늦은 생년월일"
, MIN(A.BIRTH_DE) AS "가장 빠른 생년월일"
, COUNT(*) AS "직원수"
FROM TB_EMP A
GROUP BY A.DEPT_CD
HAVING(COUNT(*)) > 1
ORDER BY A.DEPT_CD ;

 

 

ORDER BY 절

ORDER BY 정렬

  • 특정 컬럼을 기준으로 정렬하여 출력.
  • ALIAS 사용 가능, 컬럼의 순서를 나타내는 정수도 사용 가능.
  • 기본적으로 오름차순이 적용.
  • NULL은 가장 큰 값으로 인식. 
  • SELECT 절에 없는 값으로 정렬을 해도 정렬이 가능. 

 

SELECT 문의 실행 순서

  • FROM절 : 조회 테이블을 참조.
  • WHERE절 : 대상 테이블을 조회.
  • GROUP BY절 : 대상 행을 그룹화.
  • HAVING절 : 조건에 맞는 그룹을 필터링.
  • SELECT절 : 기재한 컬럼이나 식을 계산.
  • ORDER BY절 : 결과 집합을 정렬 

 

조인 

조인이란

  • 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것을 조인이라고 함. 
  • 조인이 필요한 수는 테이블 수 - 1 개 이다.
SELECT A.EMP_NO
, A.EMP_NM
, A.DEPT_CD
, B.DEPT_NM
FROM TB_EMP A
, TB_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD
AND B.DEPT_NM = '지원팀'
;

 

저작자표시

'Backend > DataBase' 카테고리의 다른 글

SQLD 최적화  (0) 2023.11.08
SQL 활용  (0) 2023.11.07
SQL vs NoSQL  (1) 2023.11.03
데이터 모델링과 성능  (1) 2023.11.02
데이터 모델의 이해  (0) 2023.10.29
Backend/DataBase 관련 글
더 보기

티스토리툴바