SQLD 2과목 1장 정리

** SQL 자격검정 실전문제 내용입니다:)
** 예시 SQL문의 결과는 따로 적어두지 않았습니다.

---

2. SQL 기본 및 활용

1) SQL 기본

(1) 관계형 데이터베이스 개요

• 데이터 조작어(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

(2) DDL

CREATE - 생성 (2-6) (2-12)

CREATE TABLE PRODUCT
( PROD_ID VARCHAR2(10) NOT NULL -- _, $, # 문자 사용 가능
 ,PROD_NM VARCHAR2(100) NOT NULL
 ,REG_DT DATE NOT NULL
 ,REGR_NO NUMBER(10)
 ,CONSTRAINT PRODUCT_PK PRIMARY KEY (PROD_ID)); -- PK 제약조건

CREATE TABLE EMP
(EMP_NO VARCHAR2(10) PRIMARY KEY,
 EMP_NM VARCHAR2(30) NOT NULL,
 DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL, -- 기본값 '0000'
 JOIN_DATA DATE NOT NULL,
 REGIST_DATE DATE NULL);
CREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE); -- 입사일자 기준 인덱스 생성

ALTER - 변경 (2-7) (2-12) (2-16)

-- 한번에 한 칼럼만 수정 가능
ALTER TABLE 기관분류 ALTER COLUMN 분류명 VARCHAR(30) NOT NULL;

ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_NO); -- PK지정

ALTER TABLE EMP DROP COLUMN COMM; -- 컬럼 삭제

RENAME - 이름변경 (2-18)

RENAME STADIUM TO SADIUM_JSC -- STADIUM 테이블명을 STADIUM_JSC로 변경

제약조건(2-10)

PRIMARY KEY -- 기본키, 중복값x, NULL 불가
UNIQUE KEY -- 고유키, 중복값X, NULL 가능
FOREIGN KEY -- 외래키
NOT NULL
CHECK (범위) -- 입력 범위 지정

(3) DML

SELECT - 조회 (2-24)

SELECT DISTINCT 거주지, 근무지 -- DISTINCT: 중복제거
FROM 고객지역;

INSERT - 삽입 (2-17) (2-20)

INSERT INTO 부서 VALUES('10', '영업과');

INSERT INTO TBL(ID, AMT) VALUES(3, 300);

UPDATE - 수정 (2-21)

UPDATE BOARD SET USE_YN = 'N' WHERE BOARD_ID = '1';
UPDATE BOARD SET BOARD_ID = 200 WHERE BOARD_ID = '100';

DELETE - 삭제 (2-31)

DELETE 품목 WHERE 품목ID='002'

DELETE, TRUNCATE, DROP 차이점 (2-23)

DELETE FROM STADIUM; -- 데이터 삭제, 공간은 남겨둠
TRUNCATE TABLE STADIUM; -- 공간을 남기지 않고 삭제. (처음 테이블이 만들어진 상태)
DROP TABLE STADIUM; -- 테이블을 완전히 제거

(4) TCL

트랜잭션의 특성(ACID)

• 원자성(Atomicity): 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 전혀 실행되지 않은 상태로 남아 있어야 한다.

• 일관성(Consistency): 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.

• 고립성(Isolation): 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된 다.

• 지속성(durability): 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

COMMIT & ROLLBACK (2-32)

BEFIN TRANSACTION;
SAVE TRANSACTION SP1; -- 저장점(SAVEPINT)
UPDATE 상품 SET 상품명 = 'LCD-TV' WHERE 상품ID = '001';
SAVE TRANSACTION SP2;
UPDATE 상품 SET 상품명 = '평면-TV' WHERE 상품ID = '001';
ROLLBACK TRANSACTION SP2; -- SP2가 ROLLBACK되었으므로
COMMIT; -- SP1만 유효한 상태로 적용

(5) WHERE 절

SQL 연산자(2-39)

WHERE BETWEEN a AND b -- a와 b사이일 경우
WHERE IN (list) -- 리스트에 있는 값 중 하나가 일치할 경우
LIKE '비교문자열' -- 비교문자열과 형태가 일치할 경우 (%, _)
IS NULL  -- null값인 경우

부정 비교 연산자(2-36)

WHERE COLUMN1 != NULL
WHERE COLUMN1 ^= NULL
WHERE COLUMN1 <> NULL
WHERE NOT COLUMN1 = ~ -- 같지 않다
WHERE NOT COLUMN1 > ~ -- 크지 않다

부정SQL 연산자(2-36)

WHERE COLUMN1 NOT BETWEEN a AND b -- a와 b사이에 있지 않다
WHERE COLUMN1 NOT IN (list) -- list 값과 일치하지 않는다
WHERE COLUMN1 NOT NULL -- null 값을 갖지 않는다.

(6) 함수

단일행 함수의 종류(2-42)

• 문자형 함수
  • LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
• 숫자형 함수
  • ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, GLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
• 날짜형 함수
  • SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, ‘YYYY’ㅣ’MM’ㅣ’DD’))/YEARㅣMONTHㅣDAY
• 변환형 함수
  • TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
• NULL 관련 함수
  • NVL/ISNULL, NULLIF, COALESCE

단일행 문자형 함수 (2-41)

LOWER(문자열) -- 소문자로 변환
UPPER(문자열) -- 대문자로 변환
ASCII(문자) -- 아스키코드로 변환
CHR/CHAR(ASCII번호) -- 문자로 변환
CONCAT(문자열1, 문자열2) -- 문자열을 연결
SUBSTR/SUBSTRING(문자열, m[, n ]) -- 문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려줌
LENGTH/LEN(문자열) -- 문자열의 개수를 숫자값으로
LTRIM(문자열 [, 지정문자]) -- 첫문자부터 확인하여 지정문자를 제거
RTRIM(문자열 [, 지정문자]) -- 마지막문자부터 확인하여 지정문자를 제거
TRIM([leading|trailing|both] 지정문자 FROM 문자열) -- 머리말, 꼬리말, 양쪽에 있는 지정문자 제거

단일행 NULL 관련 함수 (2-46)

NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2) -- 1의 결과가 NULL이면 2
NULLIF(표현식1, 표현식2) -- 1이 2와 같으면 NULL, 아니면 1
COALESCE(표현식1, 표현식2, ...) -- 임의의 개수 표현식에 NULL이 아닌 최초의 표현식

집계 함수 (2-50)

COUNT(*) -- NULL 값을 포함한 행의 수를 출력
COUNT(표현식) -- 표현식의 값이 NULL값인 것을 제외한 행의 수를 출력
SUM([DISTINCT|ALL] 표현식) -- 표현식의 NULL 값을 제외한 합계를 출력
AVG([DISTINCT|ALL] 표현식) -- 표현식의 NULL 값을 제외한 평균을 출력
MAX([DISTINCT|ALL] 표현식) -- 표현식의 최대값을 출력 (문자, 날짜 타입 가능)
MIN([DISTINCT|ALL] 표현식) -- 표현식의 최소값을 출력 (문자, 날짜 타입 가능)
STDDEV([DISTINCT|ALL] 표현식) -- 표현식의 표준편차를 출력
VARIAN([DISTINCT|ALL] 표현식) -- 표현식의 분산을 출력

(7) Grup By, Having 절

GROUP BY & HAVING (2-51)

SELECT 메뉴ID, 사용유형코드, COUNT(*), AS CNT
FROM 시스템사용이력
WHERE 사용일시 BETWEEN SYSDATE-1 AND SYSDATE
GROUP BY 메뉴ID, 사용유형코드
HAVING 메뉴ID = 3 AND 사용유형코드 = 100;

(8) Order By 절

ORDER BY (2-58)

SELECT ID, AMT
FROM TBL
ORDER BY (CASE WHEN ID = 'A' THEN 1 ELSE 2 END), AMT DESC

SELECT 문장의 실행 순서 (2-59)

FROM - WHERE - CROUP BY - HAVING -SELECT - ORDER BY

(9) 조인(JOIN)

EQUI JOIN (2-62)

SELECT 영화, 영화명, 배우, 배우명, 출연로
FROM 배우, 영화, 출연
WHERE 출연료 >= 8888
  AND 출연.영화번호 = 영화.영화번호
  AND 출연.배우번호 = 배우.배우번호;