2과목:SQL기본 및 활용(1장 : SQL기본)

DDL : CREATE, DROP, ALTER, RENAME

DML : SELECT, INSERT, UPDATE, DELETE

DCL : GRANT, REVOKE

TCL : COMMIT, ROLLBACK

- Transaction을 제어하는 명령어 TCL(Transaction Control Language)

 

테이블 칼럼에 대한 정의 변경

1) Orable

ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터유형 [DEFAULT 식][NOT NULL], 칼럼명2 데이터...);

2) SQL Server

ALTER TABLE 테이블명 ALTER(칼럼명1 데이터유형 [DEFAULT식][NOT NULL], 칼럼명2 데이터...);

 

NULL값

- NULL은 공백이나 숫자0과는 다른 값, 조건의 맞는 데이터가 없을 떄의 공집합과도 다름.

- NULL은 아직 정의되지 않은 미지의 값이거나 현재 데이터를 입력하지 못하는 경우를 의미

- ORACLE에서는 제일 작은 값으로 정렬하여 보여주고 SQL Server에서는 제일 큰 값으로 정렬하여 보여줌

- 조건절에서 NULL값을 사용할 경우 IS NULL, IS NOT NULL 이라는 키워드를 사용

 

UNIQUE의 조건

- UNIQUE KEY(고유키) : 테이블 내 중복되는 값이 없지만, NULL값 허용

 

CREATE TABLE EMP
(EMP_NO VARCHAR2(10) NOT NULL PRIMARY KEY,
EMP_NM VARCHAR NOT NULL,
DEPT_CODE VARCHAR2(4) DEFAULT '0000' NOT NULL,
JOIN_DATE DATE NULL);
ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMP_NO);
VREATE INDEX IDX_EMP_01 ON EMP (JOIN_DATE);

=> PRIMARY KEY설정을 이미 했으므로 아래의 ALTER문에서 에러가 남

 

데이터베이스 테이블의 제약조건(Constraint)

- Check제약조건은 데이터베이스에서 데이터 무결성을 유지하기위해 테이블 특정 컬럼에 설정하는 제약

- 기본키는 반드시 테이블 당 하나의 제약만을 정의

- 고유키로 지정된 컬럼들은 NULL값을 가질 수 있다

- 외래키는 테이블간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키가 참조하도록 생성

 

테이블의 컬럼삭제

ALTER TABLE 대상테이블명 DROP COLUMN 삭제할컬럼명;

ALTER TABLE, DROP COLUMN을 테이블명/컬럼명을 쓰는 것과 상관없이 다 써줘야 함.

 

DELETE/MODIFY ACTION 

- CASCADE : MASTER 삭제 시, CHILD 같이 삭제

- SET NULL : MASTER 삭제 시, CHILE 해당 필드 NULL

- SET DEFAULT : MASTER 삭제 시, CHILD 해당 필드 DEFAULT 값으로 설정

- RESTRICT : CHILD 테이블에 PK값이 없는 경우만 MASTER 삭제 허용

- NO ACTION : 참조 무결성을 위반하는 삭제/수정 액션을 취하지 않음

 

INSERT ACTION

- AUTOMATIC : MASTER 테이블에 PK가 없으면, MASTER PK를 생성 후 CHILD 입력

- SET NULL : MASTER 테이블에 PK가 없으면 CHILD 외부키를 NULL값으로 처리

- SET DEFAULT : MASTER 테이블에 PK가 없으면 CHILD 외부키를 지정된 기본값으로 입력

- DEPENDENT : MASTER 테이블에 PK가 존재할 때만 CHILD 입력을 허용

- NO ACTION : 참조무결성을 위반하는 입력 액션을 취하지않음

 

STADIUM 테이블의 이름을 STADIUNM_JSC로 변경하는 SQL문을 작성(ANSI표준 기준)
=> RENAME STADIUM TO STADIUM_JSC;

ANSI표준 기준은 ORACLE과 동일

 

테이블에 데이터 입력하는 2가지 방법

1) INSERT INTO 테이블명 (컬럼목록, , , ) VALUES (넣을 값 , , , , )

* 컬럼 목록에서 몇 개 빼도 상관 없음

2) INSERT INTO 테이블명 (전체 컬럼목록에 넣을 값)

* 컬럼목록에서 빠짐 없이 다 쓸 것

 

 

SQL 쿼리문의 순서에서 유의할 점

- 외래키를 가지고 있는 테이블 외래키값의 컬럼 값을 메인키로쓰고있는 컬럼의 값이 먼저 들어가기 전에 입력할 수 없음

- 컬럼 값을 삭제할 때, 컬럼 값의 제약조건이 NOT NULL일 경우에는 삭제가 불가능 함

 

 

삭제

- DELETE FROM 테이블명 => 로그를 남기고 삭제

- TRUNCATE TABLE 테이블명, DROP TABLE 테이블명 => 로그를 남기지 않고 삭제

TRUNCATE TABLE 테이블명 : 테이블에 들어있는 모든 행만 제거되고 저장공간을 재사용 가능
DROP TABLE 테이블명 : 테이블 구조까지 모두 완전 삭제

* TRUNCATE 명령어는 UNDO를 위한 데이터를 생성하지 않기때문에 동일 데이터량일 경우 DELETE보다 빠름

 

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML의 성격)	DML
ROLLBACK 불가능	ROLLBACK 불가능	COMMIT이전 ROLLBACK 가능
AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
테이블이 사용한 STORAGE를 모두 RELEASE	테이블이 사용한 STORAGE 중 최초 테이블 생성 시 할당된 STORAGE만 남기고 RELEASE	데이터를 모두 DELETE해도 사용했던 STORAGE는 RELEASE 안됨
테이블 정의 자체를 완전히 삭제	테이블을 최초 생성된 초기상태로 만듬	데이터만 삭제

 

데이터베이스 트랜잭션의 격리성이 낮을 경우 발생할 수 있는 문제점

- Non Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이 일어남

- Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상이 일어남

 

 

용어 설명

- Dirty Read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것

- isolation : 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다는 데이터베이스 트랜잭션 4가지의 특성(목표) 중 하나.

 

- Transaction(트랜젝션)은 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이트 조작을 가리킨다.
- Commit(커밋)은 종료를 위한 대표적 명령어로서 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영
- Rollback(롤백)은 커밋되지않은 상위의 모든 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌리는 것

 

트랜잭션 시작

- BEGIN TRANSACTION

- BEGIN TRAN

 

트랜잭션 종료

- COMMIT TRANSACTION

- COMMIT

*ROLLBACK구분을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK

저장점을 정의하면 롤백할 때, 트랙잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있음.
1) ORACLE 
SAVEPOINT SVPT1; 
...
ROLLBACK TO SVPT1;

2)SQL Server
SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR1; 

 

연산자의 우선순위

1) 괄호로 묶은 연산 ( )

2) 부정 연산자 NOT (not in~, is not null)

3) 비교 연산자 =>   >   =   <   <= 와 SQL 비교연산자 BETWEEN a AND b,    IN (list),    LIKE,    IS NULL

4) 논리 연산자 중 AND, OR의 순으로 처리

 BETWEEN a AND b     => a와 b값도 포함임

NULL값과 모든 연산은 NULL값을 리턴,
NULL값의 비교 연산은 FALSE를 리턴, 특정값보다 크다/작다 표현이 불가능

 

부정비교 연산자

1) 같지 않다           !=    ^=    <>(ISO펴준, 모든 운영체제에서 사용가능)

2) ~와 같지 않다     NOT 컬럼명 = ~~

3) ~보다 크지 않다  NOT 컬럼명 > ~~

 

 

내장함수

- 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분

- 단일행 함수는 select, where, order by, update의 set 절에  사용 가능

- 1:M 조인이라 하더라도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용할 수 있음

- 다중행 함수도 단일행 함수와 동일하게 단일 값만 반환

- 벤더에서 제공하는 함수

1)벤더에서 제공하는 함수는 내장함수
 a: 단일행 함수
 b: 다중행 함수
   b1 : 집계 함수
   b2 : 그룹함수 
   b3 : 윈도우 함수
2) 사용자가 정의하는함수 

 

단일행 문자형 함수의 종류

LOWER()
UPPER()
ASCII(문자)
CHR / CHAR(ASCII번호)	ASCII번호를 문자나 숫자로 바꿈
CONCAT( , )
SUBSTR / SUBSTRING ( , )/( )
LENGTH / LEN()	문자열의 갯수를 숫자 값으로 돌려줌
LTRIM (문자열 , 지정문자)	문자열의 첫문자부터 확인해서 지정문자가 나타나면 해당문자를 제거. 지정문자가 생력되면 공백값이 디폴트값이 됨. (SQL Server에서는 지정문자사용x 문자열만 넣어서 공백만 제거가능)
RTRIM (문자열, 지정문자)	문자열 마지막 문자부터 확인해서 지정문자 나타나는 동안 해당문자를 제거 (지정문자 생략되면 공백값이 디폴트값), (SQL Server에서는 지정문자 사용x, 문자열만 넣어서 공백만 제거가능)
TRIM([위치]지정문자 FROM 문자열)	위치(머리,꼬리,양쪽끝쪽)에 있는 지정문자를 제거, 위치 생략은 양쪽 끝이 기본값. (SQL Server에서는 공백만 제거가능)

 

날짜 연산

- 오라클에서 날짜의 연산은 숫자의 연산과 같음

- 1/24/60  = 1분 ,   1/24/(60/10) = 10분

SELECT TO_CHAR(TO_DATE('2015.01.10. 10', 'YYYY.MM.DD.HH24') + 1/24/(60/10), 'YYYY, MM, DD HH24:MI:SS') FROM DUAL;
=> 2015.01.10 10:10:00

 

단일행 함수의 종류

1) 문자형함수

2) 숫자형 함수

3) 날짜형함수

4) 변환형함수

5) NULL관련 함수 : NVL/ISNULL , NULLIF, COALESEC

- NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2) : 표현식1의 결과값이 NULL이면 표현식 2의 값을 출력.

(단, 표현식1,2의 결과 데이터 타입이 같아야 함.)

- NULLIF(표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL, 같지 않으면 표현식1을 리턴

- COALESCE(표현식1, 표현식2, ...) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄. 모든 표현식이 NULL이라면 NULL을 리턴

 

SEARCHED_CASE_EXPRESSION  SQL문장
SELECT LOC, 
  CASE WHENLOC = 'NEW YORK' THEN 'EAST'
    ELSE 'ETC'
  END as AREA
FROM DEPT;

SIMPLE_CASE_EXPRESSION  SQL문장
SELECT LOC,
  CASE LOCWHEN'NEW YORK' THEN 'EAST'
    ELSE 'ETC'
  END as AREA
FROM DEPT;

 

- SUM, AVG, COUNT는 NULL값인 것을 제외하여 계산한 합계/평균/행의수를 출력

- STDDEV 표준편차, VARIAN 표현식의 분산을 출력

 

SELECT COUNT(GRADE) FROM EMP;
=> NULL값을 제외하고 COUNT

SELECT GRADE FROM EMP WHERRE GRADE IN ('차장', '부장', '널');
=> NULL값으로 갯수가 나와있더라도 데이터는 없다고 보는 것으로 차장+부장 만

SELECT GRADE, COUNT(*) FROM EMP GROUP BY GRADE;
=> 모든직급카운트 수 + NULL로 직급처리안된 것(1) => +1만하면 됨 

 

GROUP BY절과 HAVING 절의 특성

- GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용

- 집계 함수의 통계 정보는 NULL값을 가진 행을 제외하고 수행

- GROUP BY절에서는 SELECT 절과 달리 ALIAS명을 사용할 수 없음

- 집계 함수는 WHERE절에는 올 수 없음(WHERE절이 GROUP BY절보다 먼저 수행됨)

- WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거

- HAVING절은 보통 GROUP BY절 뒤에 옴

- GROUP BY절에 의해 소그룹별로 만들어진 집계 데이터 중에서 HAVING 절의 제한 조건을 만족하는 내용만 출력 

- HAVING절은 SELECT절보다 선행처리되므로 GROUP BY/HAVING절의 내용이 SELECT절에 기술되지 않는다

 

SELECT MAX(가) AS가, 나, SUM(다) AS 다
FROM a
GROUP BY 나
HAVING COUNT(*) >1
ORDER BY 다 DESC;

=> GROUP BY수행으로 HAVING COUNT건수가 1건 이상인 결과를 SELECT 조회해서 다의 SUM값 ORDER BY 내림차순으로 정렬하고 가는 MAX값을  보여줌 

 

IN-LINE VIEW

 

 

ORDER BY 절

- ORDER BY절에 컬럼명 대신 Alias명이나 컬럼 순서를 나타내는 정수를 혼용해서 사용 가능

- 기본 정렬은 오름차순 ASC

- 날짜형 데이터 타입은 오름차순으로 정렬할 경우, 날짜가 가장 빠른 값이 먼저 출력

- DBMS마다 NULL값에 대한 정렬 순서가 다를 수 있음(ORACLE은 가장 작은값으로 간주, SQL Server는 가장 큰값으로 간주)

- GROUP BY절을 사용하는 경우, ORDER BY절에 집계함수를 사용할 수도 있음

 

SELECT 문장의 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

 

팀별 성적 테이블에서 승리건수가 높은 순으로 3위까지 출력하되 3위의 승리건수가 동일한 팀이 있으면 함께 출력
=> 
SELECT TOP(3) WITH TIES 팀명, 승리건수
  FROM 팀별성적
  ORDER BY 승리건수 DESC;

WITH TIES 옵션을 ORDER BY절과 함께 사용

 

JOIN 할 테이블 갯수 계산

여러테이블로부터 원하는 데이터를 조회하기 위해서 전체 테이블 개수에서 최소 N-1개 만큼의 JOIN조건이 필요

 

JOIN에 대한 설명

- 일반적으로 PK와 FK값의 연관성에 의해 성립

(어떠한 경우는 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN성립)

- DBMS 옵티마이져는 FROM 절에 나열된 테이블들을 임의로 3개정도씩 묶여서 JOIN 처리

- EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법

- EQUI JOIN은 = 연산자에 의해서만 수행되며, 그 외의 비교 연산자를 사용하는 경우 모두 Non EQUI JOIN

- 대부분 Non EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있음

 

SELECT COUNT(*) CNT
  FROM EMP_TBL A, RULE_TBL B
  WHERE A.ENAME LIKE B.RULE

*RULE_TBL의 RULE이 아래와 같을 때,
1. S%
2. %T%  

EMP_TBL에 ENAME이 SMITH, SCOTT이 룰을 충족한다.
COUNT => 4 (* 둘다 RULE 1, 2를 각각 만족하기때문에 갯수를 따로 세어야 함)