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를 각각 만족하기때문에 갯수를 따로 세어야 함)
'셤숌셤 > SQL개발자' 카테고리의 다른 글
2과목(3장: SQL최적화 기능) (0) | 2021.09.04 |
---|---|
2과목(2장: SQL활용) (0) | 2021.09.02 |
과목1: 데이터모델링의 이해 (0) | 2021.08.24 |