* 수강 강의명: SQLD 자격증 대비반 - 7~10주차
[요약]
1. 관계형 데이터베이스(Relational Database, RDB)
1) 개요
- 관계형 데이터베이스란?
: 표 형태로 저장되는 데이터베이스로 생성, 조회, 수정, 삭제(CRUD / Create, Read, Update, Delete)가 가능
: 관계형 데이터베이스 관리 시스템(RDMS)을 통해 관리함
2) SQL
- 개념: Structured Query Language의 약자로 관계형 데이터베이스와 통신할 때 사용하는 구조화된 질문(query) 언어
- SQL 언어 분류 ★
· 데이터 정의 언어(DDL: Data Definition Language)
: 관계형 데이터베이스(테이블)의 구조를 정의하는 데 사용하는 언어
└ 데이터를 저장하는 테이블을 만들거나 수정할 때 사용
└ 테이블을 생성(CREATE), 변경(ALTER, RENAME), 삭제(DROP) 가능
· 데이터 조작 언어(DML: Data Manipulation Language)
: 테이블에 저장된 데이터를 조작하는 데 사용하는 언어
└ 데이터 정의 언어로 생성된 구조를 실제 데이터로 채울 때 사용
└ 데이터를 삽입(INSERT), 조회(SELECT), 수정(UPDATE), 삭제(DELETE) 할 수 있습니다.
· 데이터 제어 언어(DCL: Data Control Language)
└ 데이터베이스 접근 및 제어 권한을 관리하는 데 사용하는 언어
└ 특정한 사용자의 데이터베이스 접근 및 사용 권한을 부여하거나 박탈 가능
· 트랜잭션 제어어(TCL: Transaction Control Language)
└ 논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션)별로 제어하는 언어
└ COMMIT, ROLLBACK 등 명령어 수행
* 트랜잭션(Transaction)
└ 정의: 데이터베이스가 한 번에 처리하는 작업의 단위
└ 특징
① 원자성(Atomicity): 데이터베이스에 모두 적용되거나 아님 적용되지 않거나 둘 중 하나의 선택지만 있어야 함
② 일관성(Consistency): 작업 처리 결과는 항상 일관되어야 함
③ 독립성(Isolation): 둘 이상 트랜잭션이 동시에 진행될 경우 하나의 결과가 다른 결과에 영향 주면 안됨
④ 지속성(Durability): 특정한 작업이 마무리된 경우 결과는 데이터베이스에 영구적으로 반영되어야 함
[SQL Syntax] 자체 표준은 있으나 RDB 제공 회사마다 특징이 달라 문법은 조금씩 달라짐
- 모든 SQL 문(statement)는 SELECT, INSERT, UPDATE 등과 같은 키워드로 시작
└ SQL에 들어가는 키워드는 대소문자를 구분하지 않으나 대문자 작성을 권장
- 하나의 SQL 문은 세미콜론(;)으로 끝남 (각각의 SQL 문을 구분하는 표준 방법)
- SQL 문(Statement): 독립적으로 실행할 수 있는 완전한 SQL 코드 조각. 문은 절로 구성됨
- SQL 절(Clause): 문(Statement)의 하위 단위
SELECT col_name FROM table_name;
> 하나의 문 (Statement)과 두 개의 절 (Clause)로 구성됨
3) 테이블
- 엑셀 내 시트에 해당하는 것으로, 관계형 데이터베이스에서 데이터를 저장하는 기본 단위
- 세로 방향을 칼럼(Column), 가로 방향을 행(Row), 이들이 교차하는 공간을 필드(Field)라고함.
| 종류 | 설명 |
| 테이블(Table) | 행과 열로 구성된 2차원의 데이터 저장 구조 |
| column/열/속성 | 테이블에 저장되는 데이터의 특징 더 이상 나눌 수 없는 하나하나의 속성 |
| row/행/레코드 | 테이블에 저장되는 실제 데이터 (인스턴스) |
4) ERD(Entity-Relationship Diagram)
- 테이블 간 상관 관계를 이미지로 도식화한 것
- ERD는 Entity(개체), Relationship(관계), Attribute(속성)으로 구성
- 대표적으로 바커 표기법과 I/E 표기법으로 구분
2. DDL(Data Definition Language)
1) 정의와 유형
- 정의: 데이터베이스의 골격인 테이블을 주로 다루는 SQL의 한 종류. 테이블 구성 시 가장 중요한 것은 칼럼을 정의하는 일.
- 주요 개념
· 데이터 유형: 데이터베이스에 데이터를 저장할 때 어떠한 데이터를 받을지 결정하는 기준을 의미
· 데이터 크기: 데이터를 저장할 때는 데이터 크기(SIZE)도 지정 가능
· 숫자형 타입(NUMERIC Type): DB를 제공하는 회사별로 다른 기준 제공. 저장 숫자의 크기와 정밀도를 고려해 선택
└ NUMERIC/DECIMAL: 고정 소수점 숫자를 나타내는 데 사용. 거의 동일한 의미
└ SMALLINT: 2Byte로 표현되는 작은 범위 정수
└ BIGINT: 4Byte로 표현되는 큰 범위 정수
└ FLOAT: 부동 소수점 숫자를 나타내는 데 사용
└ REAL: 4Byte 부동 소수점 숫자를 나타내는데 사용
(cf) 왜 숫자형 타입은 여러 가지로 나뉠까? 최적의 용량(공간)에 맞춰서 활용이 필요해서
- 대표적인 데이터 유형
● CHARACTER(S) - CHAR로 표기
: 고정 길이의 문자열 정보로, 입력 시 고정 길이보다 작은 경우 남은 부부은 공백으로 채워짐
: 기본 길이는 1 Byte로 설정되며, 최대 길이는 Oracle 2,000 Bytes, SQL Server 8,000 Bytes
· VARCHAR(S) - VARCHAR, 오라클은 VARCHAR2 로 표기
: 가변 길이의 문자열 정보로, 최대 길이만큼 크기를 가질 수 있지만 가변 조정이 가능해 입력 Byte만큼만 적용됨
: 기본 길이는 1 Byte로 설정되며, 최대 길이는 Oracle 4,000 Bytes, SQL Server 8,000 Bytes
· NUMERIC
: 정수, 실수 등의 숫자 정보
: SQL Server는 다양한 숫자 타입을 사용하지만, Oracle은 NUMBER 숫자 타입이 대표적
-- Oracle
-- NUMBER(p,s) precision(정밀도)/scale(소수 자리수)
NUMBER -- 입력 변형 없이 허용가능한 범위 내에서 가변 저장
NUMBER(5) -- 5자리 숫자를 표현할 수 있음 (유효숫자)
NUMBER(5, 3) -- 5자리 숫자중 소수점을 포함해 3자리까지 소수를 나타낼 수 있음
#예제
123.56 -- NUMBER(5, 2) / 총 5자리수에 소수점 2자리
NUMBER(3) -- 124 / 소수점 없이 출력
NUMBER(3, 2) -- ERR / 유효숫자가 5자리인데 3자리까지 밖에 표현을 못하니까
NUMBER(5, 1) -- 123.6 / scale에 따라 소수점 둘째자리 반올림 되어 표시 · DATETIME
: 날짜, 시각 등과 관련된 정보
: SQL Server는 DATETIME으로 표현하고, Oracle은 DATE으로 표현
- 문자열 비교
① CHAR
- 남는 부분을 '공백(BLANK)'으로 채워서 문자열 비교
- 끝에 공백 추가 후 앞에서부터 비교하기 때문에, 끝 공백만 다르면 문자열이 같다고 판단
- 고정 길이 문자열이기 때문에 헤더에 레코드 길이 정보 포함
② VARCHAR (variable CHAR)
- 맨 처음부터 하나의 문자 단위로 문자열 비교
- 공백도 하나의 문자로 취급하기 때문에 끝의 공백이 다르면 다른 문자로 판단
- 가변 길이 문자열이기 때문에 헤더에 레코드 길이 정보 미포함
- (Oracle) VARCHAR2로 표현하며,
VARCHAR2(11 BYTE) = 11 Byte 크기 문자열 저장 / VARCHAR2(11 CHAR) = 11개 문자열 지정(크기는 제한 있음)
(cf)1개의 문자는 4Bytes 크기를 가짐
2) 제약 조건
: 테이블에 부적절한 값이 들어오지 않도록 하는 규칙. 사용자가 지정하지 않으면 DBMS가 자동 부여
· NOT NULL
: 필수로 데이터가 입력되어야 하는 컬럼에 설정
· UNIQUE
: 해당 컬럼에 들어가는 값이 전체 테이블에서 유일해야 하는 조건. 중복값 허용 불가
· PRIMARY KEY
: 테이블의 기본키. NOT NULL + UNIQUE 제약조건을 함께 짐.
: 테이블당 기본키는 1개만 허용하며, 여러 칼럼을 묶어 하나의 기본키로 사용 가능함
CONSTRAINT PK_example PRIMARY KEY(col1, col2, col3)· FOREIGN KEY
: 테이블의 외래키. 참조 관계 설정이 필요하므로 먼저 생성된 참조 테이블이 있어야 함
: 외래키가 참조하는 칼럼은 참조 테이블의 기본키(PK)여야 함
CONSTRAINT FK_example FOREIGN KEY(col4) REFERENCES table_example(col1)· DEFAULT
: 값이 입력되지 않을 경우 부여될 기본 값
...
column2 VARCHAR2(50) DEFAULT 'Default Text',
...· CHECK
: 데이터에 미리 조건을 지정할 때 사용. 주로 특정한 값이나 범위를 조건으로 지정
-- 컬럼 정의에 함께 사용할 경우
...
age NUMBER(2) CHECK(age > 14),
-- 제약 조건을 따로 명시할 경우
...
CONSTRAINT CK_age CHECK(age > 14)
3) CREATE TABLE 테이블 생성
- 기본구조
-- Create a new table in the database
CREATE TABLE table_name (
column_1 data_type constraints,
column_2 data_type constraints,
column_3 data_type constraints
);#실습01
CREATE TABLE pokemon (
pokemon_id CHAR(10) NOT NULL,
name CHAR(10) NOT NULL,
attr VARCHAR(40) NOT NULL,
age NUMBER(3) NOT NULL,
CONSTRAINT pokemon_pk PRIMARY KEY (pokemon_id)
);
#실습02
CREATE TABLE summer_olympic (
olympiad CHAR(10) NOT NULL,
host_city VARCHAR(30),
country VARCHAR(30),
competitors NUMBER(5) ,
CONSTRAINT olympiad_pk PRIMARY KEY (olympiad)
);
4) ALTER TABLE 테이블 수정
- 기본 구조
#1. 칼럼/제약사항 추가
-- ADD Column
ALTER TABLE 테이블_이름 ADD 칼럼명 데이터_유형;
-- ADD Constraint
ALTER TABLE 테이블_이름 ADD CONSTRAINT 제약_조건_이름 제약조건 (컬럼_이름);
#2. 칼럼/제약사항 삭제
-- Drop Column
ALTER TABLE 테이블_이름 DROP COLUMN 삭제_할_컬럼_이름;
-- Drop Constraint
ALTER TABLE 테이블_이름 DROP CONSTRAINT 제약_조건_이름;
3. 칼럼 수정
-- MODIFY Column
#Oracle
ALTER TABLE 테이블_이름 MODIFY (
컬럼_이름_1 데이터_유형 [DEFAULT 식] [NOT NULL],
컬럼_이름_2 데이터_유형 [DEFAULT 식] [NOT NULL]
...);
#SQL Server
ALTER TABLE 테이블_이름 ALTER COLUMN (
컬럼_이름_1 데이터_유형 [DEFAULT 식] [NOT NULL],
컬럼_이름_2 데이터_유형 [DEFAULT 식] [NOT NULL]
...);
4. 칼럼/테이블 이름 수정
-- RENAME Column
#Oracle
ALTER TABLE 테이블_이름 RENAME COLUMN 변경_할_컬럼_이름 TO 새로운_컬럼_이름;
#SQL Server
SP_RENAME 테이블_명.변경_할_컬럼_이름, 새로운_컬럼_이름, 'COLUMN';
-- RENAME Table
#Oracle
RENAME 기존_테이블_이름 TO 새로운_테이블_이름;
#SQL Server
SP_RENAME 기존_테이블_이름, 새로운_테이블_이름;##예시
--ADD Column : summer_olympic 테이블에 start_date 추가
ALTER TABLE summer_olympic ADD start_date DATE;
-- ADD Constraint : player 테이블에 외래키 제약조건 추가
ALTER TABLE player ADD CONSTRAINT player_fk FOREIGN KEY team_id
REFERENCES TEAM(team_id);
-- DROP Column
ALTER TABLE summer_olympic DROP COLUMN start_date;
-- DROP Constraint
ALTER TABLE player DROP CONSTRAINT player_fk;
-- ALTER Column
#ORACLE
ALTER TABLE TEAM_TEMP
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
#SQL Server
ALTER TABLE TEAM_TEMP
ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL;
-- RENAME Column
#ORACLE
ALTER TABLE player
RENAME COLUMN player_name TO name;
#SQL Server
SP_RENAME player.player_name, name, 'COLUMN';
-- RENAME Table
#ORACLE
RENAME player TO football_player;
#SQL Server
SP_RENAME player, football_player;
- 칼럼 수정 시 크기를 늘릴 수는 있으나 줄일 수는 없음
5) DROP TABLE, TRUNCATE TABLE 테이블 삭제
(1) DROP TABLE
: 테이블 전체를 삭제할 때 사용하며 저장 데이터도 같이 삭제
#Oracle / SQL Server 공통
DROP TABLE 테이블_이름 [CASCADE CONSTRAINT];
#예시
DROP TABLE player;
: CASCADE CONSTRAINT / 삭제 테이블을 참조하고 있는 제약조건을 같이 삭제할지 여부를 결정하는 옵션
└ SQL Server에는 해당 옵션이 없어 제약조건 및 참조테이블 먼저 삭제해야 테이블 삭제도 가능
(cf) 참조 무결성: 데이터베이스 상의 참조가 모두 유효함을 의미
#수정
-- 참조하고 있는 모든 레코드도 함께 수정
ALTER TABLE ORDERS ADD FOREIGN KEY (product_id) REFERENCES Product(id) ON UPDATE CASCADE;
-- 참조하고 있는 모든 레코드도 함께 삭제
ALTER TABLE ORDERS ADD FOREIGN KEY (product_id) REFERENCES Product (id) ON DELETE CASCADE;
-- 참조하던 레코드가 사라지면 NULL 값으로 변경
ALTER TABLE ORDERS ADD FOREIGN KEY (product_id) REFERENCES Product (id) ON DELETE SET NULL;
#테이블 생성 시 조건 설정
-- Orders 테이블 생성
CREATE TABLE Orders (
order_id NUMBER PRIMARY KEY
...
);
-- OrderItems 테이블 생성
CREATE TABLE OrderItems (
item_id NUMBER PRIMARY KEY,
order_id NUMBER,
item_name VARCHAR2(50),
CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE
);
(2) TRUNCATE TABLE
: 테이블 구조는 그대로 남기고 저장된 데이터만 지우고 싶을 때 사용
#Oracle / SQL Server 공통
TRUNCATE TABLE 테이블_이름;
#예시
TRUNCATE TABLE player;
3. DML (Data Manipulation Language)
1) 정의
: 테이블에 데이터를 삽입(INSERT), 삭제(DELETE), 수정(UPDATE), 조회(SELECT) 를 수행하는 SQL
: 삽입/삭제/수정 후, 데이터베이스에 반영하는 커밋(COMMIT)과 작업 취소를 위한 롤백(ROLLBACK)도 있음
2) INSERT
INSERT INTO 테이블_이름 (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...);
#예시
INSERT INTO player (name, height, weight) VALUES ('KIM', 184, 75);INSERT INTO 테이블_이름 VALUES (값1, 값2, ...);
#예시
INSERT INTO player VALUES ('LEE', 187, 74);3) UPDATE
: 한 번 입력된 데이터를 수정해야 할 때, UPDATE 문으로 내용 수정
: WHERE 문으로 조건 필터링 하여 수정 가능하며, 조건 안 걸면 전체 데이터가 수정됨!!
UPDATE 테이블_이름 SET 수정_할_컬럼_이름 = 수정_할_새로운_값, ...;
#예시
UPDATE player SET back_no = 99;
UPDATE player SET position = 'MF';4) DELETE
- 데이터를 삭제하기 위한 명령어 DELETE
- WHERE 문으로 조건을 만족하는 데이터만 선택하여 삭제 가
DELETE [FROM] 테이블_이름;
DELETE [FROM] 테이블_이름 WHERE 삭제할_조건;
#예시
DELETE FROM player;
DELETE FROM player WHERE back_no = 50;
[!] DELETE vs. TRUNCATE
- DELETE는 DML 명령어라 바로 삭제되지 않고, COMMIT 실행 후 최종 적용. 복원을 위해 log를 남겨 부하가 있음
└ 단, SQL Server는 DML도 AUTO COMMIT되어 바로 실행될 수도
└ 잘못 삭제했다면 ROLLBACK 명령어로 다시 되돌릴 수 있긴 함
- TRUNCATE는 DDL 명령어라 삭제 후 복원 불가. 삭제 시 시스템 부하는 적음
DELETE FROM player; -- player 테이블에서 데이터 삭제
ROLLBACK; -- 되돌리기
SELECT COUNT(*) FROM player; -- player 테이블의 전체 데이터 개수 구하기
-- 480
TRUNCATE TABLE player; -- player 테이블에서 데이터 삭제
ROLLBACK; -- 되돌리기
SELECT COUNT(*) player; -- player 테이블의 전체 데이터 개수 구하기
-- 0
5) SELECT
: 테이블에 데이터를 조회할 때 사용하는 명령어
SELECT 칼럼명1, 칼럼명2 ... FROM 테이블명; -- 칼럼명 대신 * 넣으면 전체 칼럼 출력- ALIAS: 칼럼명의 별칭을 지정하는 기능. 칼럼명과 별칭명 사이에 AS를 넣거나 생략, 테이블명에는 AS 표기 불가
- DISTINCT: 쿼리 결과에서 중복된 값을 제거하여 고유한 값만을 반환하도록 하는 역할
- 산술 연산자
| + | - | * | / | % |
| 더하기 | 빼기 | 곱하기 | 나누기 | 나머지 |
| * 연산자 우선순위: () → * → / → + → - | ||||
- 합성 연산자
: 서로 다른 두 문자를 하나의 문자열로 합칠 때 사용
-- Oracle
SELECT 문자형1 || 문자형2 FROM 테이블_이름;
-- SQL Server
SELECT 문자형1 + 문자형2 FROM 테이블_이름;
-- CONCAT 함수 사용
SELECT CONCAT(문자형1, 문자형2) FROM 테이블_이름;
4. SELECT 절 함수
1) WHERE
: 내가 원하는 데이터를 필터링하는 기능. 조회, 수정, 삭제 시 함께 사용
SELECT [ALL|DISTINCT] 칼럼명 [AS 별명] FROM 테이블명 WHERE 조건식; -- 조회
UPDATE 테이블명 SET 칼럼명=값 WHERE 조건식; -- 수정
DELETE FROM 테이블명 WHERE 조건식; -- 삭제
#예시
SELECT * FROM PLAYER WHERE BACK_NO = 10;
UPDATE PLAYER SET TEAM = '서울FC' WHERE NAME = '메시';
DELETE FROM PLAYER WHERE NAME = '발로차';
- 비교 연산자 : 숫자 혹은 문자 값의 크기 및 동일 여부 등을 확인(=, >, >=, <, <=)
- SQL 연산자
└ BETWEEN 값1 AND 값2: 값1 ≤ 비교값 ≤ 값2
└ IN (값1, 값2 ... ) : 포함
└ LIKE '비교문자열': 비교문자열과 형태 일치 조건. % = 어떤 문자든 포함한 모든 것, _ = 1개의 단일 문자(EX. _피, 커피)
└ IS NULL / IS NOT NULL: NULL값 포함 여부
- 논리 연산자
└ AND: 앞의 조건과 뒤의 조건이 둘 다 만족
└ OR: 앞의 조건이든 뒤의 조건이든 하나만 만족
└ NOT: 뒤에 오는 조건의 결과를 반대로 만족
- 부정 연산자
└ 같지 않다. (!= , ^=, <>)
└ ~와 같지 않다. (NOT 칼럼명 = )
└ ~보다 크지 않다. (NOT 칼럼명 > )
└ a와 b의 값 사이에 있지 않다. (NOT BETWEEN a AND b)
└ list 값과 일치하지 않는다. (NOT IN (list))
└ NULL 값을 갖지 않는다. (IS NOT NULL)
- 연산자의 우선순위
(1 순위) 괄호( )
(2 순위) NOT 연산자
(3 순위) 비교 연산자, SQL 비교 연산자
(4 순위) AND
(5 순위) OR
2) 가상 칼럼(PSEUDO COLUMN): ROWNUM, ROWID
- ROWNUM
: 임시로 열 번호를 부여하는 함수. 주로 WHERE 문에서 행의 개수를 제한하는 목적으로 사용
: 비교 연산자를 사용해 행의 개수를 제한할 수 있으나, = 연산자는 사용 불가
: 오라클에서 제공하는 가상 칼럼(PSEUDO COLUMN) 중 하나
(cf) PSEUDO COLUMN: 처리 결과의 각 행에 임시로 번호를 부여함. 실제 데이터베이스에는 저장되지 않아, SELECT로 조회는 가능하지만 삽입, 수정, 삭제는 불가
SELECT / UPDATE / DELETE 문
WHERE ROWNUM 비교연산자 행의갯수;
#예시
SELECT * FROM PLAYER
WHERE ROWNUM <= 3;
(cf) TOP은 SQL Server 에서 활용하는 임시 칼럼.
SELECT TOP(N) 칼럼명 FROM 테이블명;
#추가 조건 / SELECT TOP(N) [PERCENT] [WITH TIES]
> PERCENT: 쿼리 결과 집합에서 몇 %를 반환할지 정할 수 있음
> WITH TIES: 정해진 개수만큼 출력시 마지막 행과 같은 값이 존재하면 추가로 출력되도록 지정
ORDER BY 지정된 경우만 사용 가능
쿼리 결과 집합에서 몇 %를 반환할지 정할 수 있음
- ROWID
: 데이터를 구분할 수 있는 유일한 값을 지정 (오라클에서 임의로 지정하는 값)
: SELECT, WHERE절에 사용될 수 있지만 INSERT, UPDATE, DELETE로 변경 불가
: 유일한 주소값이지만 테이블의 PK처럼 사용불가
: ROWID데이터가 어떤 파일과 어떤 블록에 저장되어 있는지를 확인 가능
: 오라클에서 제공하는 가상 칼럼(PSEUDO COLUMN) 중 하나
3) 함수 - 단일행 함수
| 종류 | 내용 | 함수 |
| 문자형 함수 | 문자를 입력하면 문자나 숫자 값을 반환 | LOWER, UPPER, ASCII, CHR, CONCAT, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM, LPAD, RPAD |
| 숫자형 함수 | 숫자를 입력하면 숫자 값을 반환 | ABS, SIGN, MOD, CEIL, FLOOR, ROUND, TRUNC, POWER, SQRT, LOG, EXP, LN, SIN, COS, TAN |
| 날짜형 함수 | DATE 타입의 값을 연산 | EXTRACT, SYSDATE |
| 변환형 함수 | 문자, 숫자, 날짜형의 데이터형을 다른 데이터형으로 형변환 | TO_CHAR, TO_NUMBER, TO_DATE |
| NULL 관련 함수 | NULL 값을 처리 | NVL, NULLIF, COALESCE |
| 분기처리 함수 | 분기처리에 사용 | DECODE |
- CONCAT(문자열, 문자열) : 문자열과 문자열을 연결하여 리턴
(SQL Server. DATEPART(시간날짜단위, 시간날짜) )
- TO_NUMBER(문자열): alphanumeric 문자열을 숫자로 변환
- TO_DATE(문자열 [, FORMAT]): 문자열을 주어진 FORMAT 형태로 날짜 형식으로 변환
(SQL Server)
- CAST (expression AS data type [(length)]): expression을 목표 데이터 유형으로 변환
- CONVERT (data_type [(length)], expression [,style]): expression을 목표 데이터 유형으로 변환
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE result_else
END#기본 구조
DECODE(expression, value1, result1, value2, result2, ..., default_result)
-- 표현식(expression) 값이 기준값1(value1)과 같다면 값1(result1)을, 다르면 디폴트 값 출력
-- 여러 개의 기준값을 설정할 수 있으며, 기본값 미설정 시 NULL 값 반환
#기본 형태
SELECT DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., default])
FROM 테이블명;4) GROUP BY와 HAVING
(1) 집계함수(Aggregate Function)
- 개념: 여러 데이터들을 연산을 해주는 함수. SELECT, HAVING, ORDER BY 절에 사용 가능
└ WHERE 절에 사용 불가! GROUP BY 절 보다 먼저 수행이 되기 때문에 그룹핑 전에 집계하면 제대로된 값이 안나옴
└ NULL은 NVL이나 ISNULL을 사용해 제외 가능하나, 집계함수 대부분이 NULL을 제외하므로 별도 함수 사용 불필요
- 종류
| 항목 | 결과 |
| COUNT(*) | NULL 값을 포함한 행의 수 |
| COUNT(칼럼) | NULL 값을 제외한 행의 수 |
| SUM(칼럼) | NULL 값을 제외한 합계 |
| AVG(칼럼) | NULL 값을 제외한 평균 |
| MAX(칼럼) | NULL 값을 제외한 최대값 |
| MIN(칼럼) | NULL 값을 제외한 최소값 |
| STDDEV(칼럼) | NULL 값을 제외한 표준편차 |
| VARIAN(칼럼) | NULL 값을 제외한 분산 |
- NULL값은
(2) GROUP BY 절
- 개념: 데이터들을 작은 그룹으로 분류하여 해당 그룹에 대한 항목별로 통계 정보를 얻고자 할 때 사용하는 절
- 특징
└ SELECT 절에 집계함수 사용 시 GROUP BY 사용 해야 함
└ GROUP BY 절에 ALIAS 사용 불가
└ 그룹핑은 WHERE 절에 맞는 조건을 가져온 다음에 실행됨
└ GROUP BY 절보다 WHERE 절이 먼저 수행되기 때문에 집계 함수는 WHERE 절에 올 수 없음
└ HAVING 절을 통해 GROUP BY로 묶은 그룹에 집계 함수를 이용한 조건 및 제한 표시 가능
SELECT [DISTINCT] 칼럼명 [ALIAS명]
FROM 테이블명
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식]
[HAVING 그룹조건식] ;
(3) HAVING 절
- 개념: WHERE절과 같이 특정 조건데이터만 표시 할 수 있으나, 집계 결과에 대해 사용하는 것이 특징
- 특징
└ 집계된 결과를 기준으로 조건이 필요한 경우 HAVING 절 활용
└ GROUP BY 절과 함께 사용하며, 그룹을 나타내는 결과 행에 조건 적용
5) ORDER BY
(1) 개념 및 특징
- 특정 칼럼을 기준으로 정렬하여 출력하기 위해 사용
- 아무것도 적지 않으면 기본 오름차순(ASC)이고, 내림차순 시 DESC 추가 필요
- ORDER BY 절에 ALIAS 사용 가능
- NULL의 경우, Oracle는 가장 큰 값으로, SQL Server는 가장 작은 값으로 간주
(2) SELECT 문장 실행 순서
: FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY > LIMIT/OFFSET
(3) TOP N 쿼리
: TOP N 쿼리는 상위 N개의 데이터를 보여주기 위한 쿼리를 지칭
- ROWNUM (Oracle) : WHERE 절에 작성된 ROWNUM으로 데이터를 랜덤 추출한 다음, 정렬 작업 진행
- TOP (SQL Server) : ORDER BY로 데이터 정렬 후 원하는 데이터 일부 추출 가능
5. TCL(Transaction Control Language)
- 개념: 데이터를 삽입, 삭제, 수정하는 DML의 수행 이후 COMMIT이나 ROLLBACK을 하기 위해 사용하는 SQL
- 트랜잭션(Transaction): 데이터베이스에서 처리되는 논리적인 연산단위. 분할할 수 없는 최소 단위
└ 연산을 전부 적용하거나 혹은 적용하지 않는 양자택일의 관계로 구성
└ 기본 특성(ACID): 원자성(Atomicity), 일관성(Consistency), 고립성/격리성(Isolation), 지속성(Durability)
(1) COMMIT
: 최종 트랜잭션을 확정하는 TCL 명령어
: 데이터베이스 변경 후 COMMIT 하지 않으면, 최종 데이터베이스에 변경 내용 저장 안되며, 이전 상태 복구 가능
: SELECT 문으로 수정 내용 조회 자체는 가능하나 다른 사용자는 접근 안 됨
: COMMIT하면, 데이터베이스 변경이 반영되기까지 잠금되었다가, 풀린 이후 다른 사용자 사용 가
(cf) LOCKING(잠금)
트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법
#예시
INSERT INTO PLAYER VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
COMMIT;
UPDATE PLAYER SET HEIGHT = 100;
COMMIT;
DELETE FROM PLAYER;
COMMIT;
(2) ROLLBACK
: 트랙잭션 진행중 에러나 실수 발생 시 변경 전 가장 최신 상태로 되돌리는 명령어
SQL 쿼리문
ROLLBACK;(cf) COMMIT과 ROLLBACK을 사용하지 않아도 트랜잭션이 종료되는 경우
① DDL 문장 실행 시 자동으로 COMMIT
② DML 문장 이후에 COMMIT 없이 DDL 문장이 실행될 때
③ 정상적으로 DB 접속을 종료하면 COMMIT
④ 장애로 인한 비정상 종료로 DB 접속이 단절되었을 경우에는 AUTO ROLLBACK
(3) SAVEPOINT
: 트랜잭션 중간에 SAVEPOINT 설정 시 처음이 아닌 SAVEPOINT 지점까지만 롤백해 시스템 부하를 줄일 수 있
-- Oracle
SAVEPOINT 저장점_이름;
ROLLBACK TO 저장점_이름;
-- SQL Server
SAVE TRANSACTION 저장점_이름;
ROLLBACK TRANSACTION 저장점_이름;
(+) 트랜잭션 병렬처리 시 문제점
> 현재 수행중인 트랜잭션의 데이터에 다른 트랜잭션이 접근 가능할 때 발생할 수 있는 문제점
① Dirty Read: 수정중인데 조회를 허용하면, 잘못된 정보를 읽게 됨
② Non-Repeatable Read: 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션 값을 수정/삭제하면서 두 쿼리 결과가 다르게 나타나는 현상
③ Phantom Read: 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션 값이 추가 되면서 유령 데이터가 생기는 현상
6. DCL(Data Control Language)
- 개요: 특정 유저의 권한을 제어할 수 있는 명령어
└ 사용자: 사용자 계정과 패스워드를 통해 DBMS에 접속 가
└ 스키마(Schema): 사용자가 소유하고 있는 오브젝트의 모음
- 구문 정리
| 종류 | SQL문 |
| 사용자 계정 생성 | CREATE USER user IDENTIFIED BY “password”; |
| 사용자 비밀번호 변경 | ALTER USER user IDENTIFIED BY “password”; |
| 사용자 계정 잠금/해제 | ALTER USER user ACCOUNT LOCK/UNLOCK; |
| 사용자 계정 삭제 | DROP USER user [CASCADE]; * CASCADE: DB의 값 수정/삭제 시 참조하고 있는 레코드도 종속적으로 수정/삭제 가능 |
| 사용자에 권한 부여 | 시스템 권한 GRANT privilege TO user [WITH ADMIN OPTION]; 오브젝트 권한 GRANT privilege ON object TO [WITH GRANT OPTION]; *privilege : 부여할 권한 *object : 대상 오브젝트 *WITH ADMIN/GRANT OPTION : 부여 받은 권한을 다른 사용자에게 부여하거나 회수할 수 있음 |
| 사용자의 권한 회수 | 시스템 권한 REVOKE privilege FROM user; 오브젝트 권한 REVOKE privilege ON object FROM user; |
- 권한: 시스템 권한, 객체 권한, ROLE을 이용한 권한 크게 3가지로 분류
-- 기본 구조
GRANT [system_privilege|role] TO [user|role|PUBLIC]
[WITH ADMIN OPTION];
└ 시스템 권한: 사용자가 데이터베이스에서 특정 작업을 수행 할 수 있도록 허용
└ 객체 권한: 사용가 소유하고 있는 특정 객체를 다른 사용자들이 엑세스 하거나 조작할 수 있게 하기 위해 생성
└ 롤(ROLE): 사용자에게 허가할 수 있는 권한들의 집합
[SQL 코드카타]
1. 85. Rising Temperature
https://leetcode.com/problems/rising-temperature/
1) 어떤 문제가 있었나
전날에 비해 온도가 오른 ID 추출해야 하는데, 날짜와 온도를 같은 행에서 비교 방법이 떠오르지 않음
2) 내가 시도해본 건 무엇인가
풀이 참조 결과, 셀프 JOIN과 DATEDIFF 함수 적용이 필요했음
3) 어떻게 해결했나
셀프 JOIN 방법을 여러 가지로 조합으로 조회하여 추출되는 테이블 값을 확인
- FROM 절에 WHERE 조건 없이 2개의 테이블명을 넣으면, 각 값이 개별로 조합되어 추출(2개 데이터면 4개 조합으로)
- LEFT/RIGHT JOIN 시 (본래 날짜 = 본래 날짜 +1 ) 조건 설정하여 추출
SELECT w2.id
FROM Weather w1
LEFT JOIN Weather w2 ON w1.recordDate = (w2.recordDate - '1d')
WHERE DATEDIFF(w2.recordDate, w1.recordDate)
AND w2.temperature > w1.temperature
>> join 절 없이 넣은 쿼리
SELECT w2.id
FROM Weather w1, Weather w2
WHERE w2.recordDate = (w1.recordDate+1)
AND w2.temperature > w1.temperature
4) 무엇을 새롭게 알았나
- 소유한 테이블을 활용해 분석이 필요한 사항은 셀프 조인이 필요함
- FROM 절에 2가지 테이블을 넣으면 조인이 됨. WHERE절을 통해 JOIN ON과 같이 조건 추가가 가능한 것
└ 예를 들면, FROM 테이블1, 테이블2 WHERE 테이블1.id = 테이블2.id
[+ 그 외 새롭게 알게 된 것]
(파이썬) 타입을 변경해주는 함수
- int(): 값을 정수형(Integer)으로 변환해주는 함수
- float(): 값을 실수(real number)로 변환해주는 함수
- str(): 값을 문자열(string)로 변환해주는 함수
(SQLD) 강의
'TIL' 카테고리의 다른 글
| [240102] SQL: 코드카타 89-90 & 파이썬: 코드카타 5 (1) | 2024.01.02 |
|---|---|
| [231229] SQLD: JOIN, 집합연산자, 서브쿼리, 그룹함수, 윈도우함수 (1) | 2023.12.29 |
| [231227] SQLD: 데이터 모델링, 정규화, 데이터베이스 성능 (1) | 2023.12.27 |
| [231226] SQLD: 기본개념 및 데이터 모델링 & SQL: 코드카타 75~76 (1) | 2023.12.26 |
| [231222] SQL: 코드카타 72~74 (0) | 2023.12.22 |
