[SQL] 기본 정리

관계형 데이터베이스

• 정규화를 통해 이상 현상을 제거하고 데이터 중복을 피함으로써 데이터 불일치성 문제를 해결한다.
• 동시성 관리와 병행 제어를 통해 여러 사용자가 동시에 데이터를 공유하고 조작할 수 있다.
• 메타 데이터를 통해 데이터의 성격, 속성, 표현 방법을 체계화하고 데이터 표준화를 지원한다.
• 보안 기능을 제공하여 인증된 사용자만이 참조할 수 있도록 한다.
• 다양한 제약조건을 이용하여 데이터 무결성을 보장하고 오류를 방지합니다.
• 1970년에 E.F.Codd 박사가 논문에 처음 소개했다.
• 기존의 파일시스템, 망형(네트워크형 데이터베이스), 계층형 데이터베이스를 대체하는 장점이있다.(데이터 중복제거, 동시성 관리와 병행 제어, 합리적인 테이블 모델링, 보안 기능 제공 등등)

 

 

---

DML, DDL, DCL, TCL 과 테이블 내용 정리

DML, DDL, DCL, TCL 각 명령어와 설명

• DML예시

update client     --고객 테이블내에 있는 데이터 수정
set money = money+3000;

 

• DDL예시

create table client     --테이블 생성
(
	ssn varchar2(14),
	name varchar2(10)	not null,
	address varchar2(10),
	constraint pk_client primary key(ssn)
)

ALTER TABLE client ADD COLUMN phone VARCHAR(20);   --client테이블에 phone 필드추가

DROP TABLE products;    --테이블 삭제

 

• DCL예시

GRANT SELECT ON client TO kimjisu;     --고객 테이블에 대한 SELECT 권한 "kimjisu"에게 부여
REVOKE SELECT ON client FROM kimjisu;     --고객 테이블에 대한 SELECT 권한 "kimjisu"에게서 회수

 

• TCL예시

UPDATE accounts SET balance = balance - 500 WHERE account_number = '477'; --계좌에서 금액 500 차감

//1번 선택지
COMMIT;  --트랜잭션을 확정하고 변경사항을 데이터베이스에 반영
//2번 선택지
ROLLBACK; --트랜잭션을 취소하고 변경사항을 취소하여 이전상태로 복구

 

 

• 테이블 컬럼에 대한 정의 변형
  • [Oracle]
    ALTER TABLE 테이블명 MODIFY(칼럼명1 데이터 유형[DEFAULT 식][NOT NULL], 칼럼명2 ....);
  • [SQL Server]
    ALTER TABLE 테이블명 ALTER(칼럼명1 데이터 유형[DEFAULT 식][NOT NULL], 칼럼명2 ....);
    
    
• 제약 조건의 종류
  
  • PRIMARY KEY(기본키):  중복된 값 허용x, 테이블의 여러 필드로 구성될 수 있다.  null값 허용안한다.
  • UNIQUE KEY(고유키): 중복된 값 허용x, 테이블당 하나만 지정, null값 허용한다.
  • NOT NULL: NULL값을 허용하지 않는다. 해당 컬럼은 항상 유효한 값이 필요하다.
  • CHECK: 조건을 건다. ex. 나이 컬럼에 음수나 120이상의 수는 안받게한다. 
  • FOREIGN KEY(외래키): 다른 테이블의 기본키를 참조하는 제약 조건이다.
    
    
• 테이블 생성시 주의사항 
  • 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 단수형을 권고한다.
  • 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
  • 한 테이블 내에서는 컬럼(필드)명이 중복되게 지정될 수 없다.
  • 테이블 이름을 지정하고 각 컬럼들은 "()"로 묶어 지정한다.
  • 각 컬럼들은 콤마 "," 로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ";"으로 끝낸다.
  • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.(데이터 표준화 관점)
    
    • 다른 테이블의 gender 컬럼에는 'M', 'F'같은 약어를 사용하고 또 다른 테이블의 gender 컬럼에는 '남성', '여성' 과 같이 한글로 저장하면 안됨 
  • 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
  • 테이블명과 칼럼명은 반드시 문자로 시작해야하고, 벤더별로 길이에 대한 한계가 있다.
  • 벤더에서 사전에 정의한 예약어는 쓸 수 없다.
    • 벤더(데이터베이스 관리 시스템 제조사)에서 정의한 "SELECT", "DROP" 과 같은 예약어 사용불가
  • A-Z, a-z, 0-9, _, $, #문자만 허용

create table client     --테이블 생성, clients 같은 복수형보다는 단수형이 적절
(
	ssn varchar2(14),       --컬럼(필드)뒤에 데이터 유형 필요
	name varchar2(10)	not null,
	address varchar2(10),
	constraint pk_client primary key(ssn)
)

--필드 만들때 벤더에서 사전에 정의한 예약어로 만들지 말고 허용된 문자만 사용하기
--적절한 예시(employee_id, #hashtag)

--추가로 테이블의 불필요한 칼럼 삭제
ALTER TABLE <테이블명> DROP COLUMN <삭제할 컬렴명>;

 

• DUAL 테이블의 특성
  • 사용자 SYS가 소유하며 모든 사용자가 액세스 가능한 테이블이다.
  • SELECT  ~FROM ~의 형식을 갖추기 위한 일종의 DUMMY 테이블이다.
  • DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.
• DLETE(/Modify) Action 
  • Cascade: Master 삭제 시 Child 같이 삭제
  • Set Null: Master 삭제 시 Child 해당 필드 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: 참조 무결성을 위반하는 입력 액션을 취하지 않음
    
    
• 테이블에 데이터를 입력하는 두가지 유형

--INSERT INTO <테이블명>(COLUMN_LIST)VALUES(COLUMN_LIST에 넣을 VALUE_LIST);
insert into client(client_id, name, region, phone) --리스트에 들어간 칼럼에 대해서만 값, 나머지는 Null
values('000305', '빵빵이', '서울', '010-1234-5678')   
--INSERT INTO <테이블명>(전체 COLUMN에 넣을 VALUE_LIST);
insert into client 
values('000305', '빵빵이', '서울', '010-1234-5678')

 

• 입력된 데이터의 수정

--UPDATE <테이블명>SET <수정되어야할 컬럼명> = 수정되기를 원하는 새로운 값;
update client     
set money = money+3000;

 

• 테이블에 입력된 데이터 조회
  • SELECT[ALL/DISTINCT]

SELECT * FROM client;   --모든 칼럼과 모든 행 조회
SELECT client_id, name, phone;   --특정 컬럼과 해당 행 조회

          ※TRUNCATE TABLE: 테이블의 모든 데이터를 삭제한다. 근데 이제 테이블 자체가 삭제되는 것이 아니라
          안에 데이터만 사라지는거라 테이블 재사용이 가능하다. 진짜 테이블과 구조까지 삭제하는거면
           DROP TABLE실행하면된다

 

 

• 트랜잭션의 특성
  • 원자성: 트랜잭션에 정의된 연산들을 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다.(commit 되거나 rollback 되거나)
  • 일관성: 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
  • 고립성: 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
  • 지속성: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

          ※추가 내용: 테이블 내 입력한 데이터, 수정한 데이터, 삭제한 데이터에 대해서 COMMIT 이전에는 변경사항을 취소           하는 기능이 있는데 ROLLBACK을 사용하면 된다. 그러면 이전 COMMIT까지로 데이터의 상태가 복구된다.
         결국 COMMIT전에는 삭제된게 아니라 사용자가 접근 못하게 LOCK하는거같고 COMMIT하면 LOCK 
         한 부분을 실제로 삭제, ROLLBACK시에는 그냥 LOCK만 풀어서 다시 접근하게 하는거같다.

 

• DROP, TRUNCATE, DELETE의 차이

 

 

• 트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점
  • Dirty Read: 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것을 말한다.
  • Non-Repeatable Read: 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상을 말한다. 
  • Phantom Read: 한 트랜잭션 내에서 같은 쿼리를 두번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상을 말한다.
    ※해결법: 데이터베이스 시스템에서 제공하는 여러 격리 수준중 높은 수준의 격리를 사용한다.
    
    
• TRANSACTION 사용

BEGIN TRANSACTION COMMIT  --BEGIN TRAN 구문도 가능
TRANSACTION COMMIT  --또는 ROLLBACK
ROLLBACK    --전에 COMMIT한곳까지 ROLLBACK

--저장 포인트(SAVEPOINT) 정의 시 롤백하면 savepoint까지만 롤백이 된다
--Oracle 기준
SAVEPOINT SVPT1;

ROLLBACK TO SVPT1;

--SQL Server기준
SAVE TRANSACTION SVPT1;

ROLLBACK TRANSACTION SVPT1;

 

 

---

WHERE 절

WHERE절은 FROM 절 다음에 위치하며, 조건식은 아래 내용으로 구성된다.

• 칼럼명(보통 조건식의 좌측에 위치)
• 비교 연산자
• 문자, 숫자, 표현식(보통 조건식의 우측에 위치)
• 비교 칼럼명(JOIN사용시)

SELECT p.* FROM products p    --product테이블에서 모든 필드 선택
JOIN categories c ON p.category_id = c.category_id   --외래키 category_id를 통해 카테고리 테이블과 조인
WHERE c.category_name = 'Electronics';   --카테고리 테이블의 category_name 필드의 데이터가 Electronics면 해당되는 조건
-- 조건을 만족하는 product 테이블의 데이터들 출력

 

 

---

연산자와 NULL

연산자

• 연산자의 우선순위
  1. 괄호로 묶은 연산
  2. 부정 연산자(NOT)
  3. 비교 연산자(=,>,>=,<,<=)와 SQL 비교 연산자(BETWEEN a AND b, IN(list), LIKE, IS NULL)
  4. 논리 연산자 중 AND, OR의 순으로 처리
     
     
• 부정 비교 연산자
  • !=: 같지 않다.
  • ^=: 같지 않다.
  • <>: 같지않다
  • NOT 칼럼명 = :~와 같지않다.
  • NOT칼럼명 >: ~보다 크지않다. 
    
    
• 연산자의 종류

 

 

 

 

 

• BETWEEN a AND b
  • a와  b의 값 사이에 있으면 된다.(a와 b값이 포함임 ex. a <=  해당값 <= b)
    
    

• IN(list)
  • 리스트에 있는 값 중에서 어느 하나라도 일치하면 된다. 
    
    
    

NULL

• NULL 
  • NULL의 의미
    • NULL은 공백이나 숫자와는 전혀 다른 값이며, 조건에 맞는 데이터가 없을때의 공집합과도 다르다.
    • NULL은 '아직 정의되지 않은 미지의 값' 이거나 '현재 데이터를 입력하지 못하는 경우'를 의미한다.
  • NULL의 연산 
    • NULL 값과의 연산(+,-,*,/등)은 NULL 값을 리턴
    • NULL 결과의 비교연산(=,>,>=,<,<=)은 FALSE를 리턴
    • 특정 값보다 크다, 적다라고 표현할 수 없음
  • NULL 포함 연산의 결과
    • NULL +2, 2+NULL
    • NULL -2, 2-NULL
    • NULL*2, 2*NULL 
      의 결과는 모두 NULL이다. 

 

함수

함수의 포함관계 이미지로 표현

 

단일행 함수

종류	내용	함수의 예
문자형함수	문자를 입력하면 문자나 숫자 값을 변환	LOWER, UPPER, SUBSTR,SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값을 변환	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수	DATE 타입의 값을 연산	SYSDATE/GETDATE, EXTRACT/DATEPART
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입 변환	TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE
주: Oracle 함수/ SQL Server함수 표시, '/'없는 것은 공통 함수

 

 

• 단일행 문자형 함수의 종류
  
  • LOWER(문자열): 소문자로 바꾼다 
  • UPPER(문자열): 대문자로 바꾼다
  • ASCII(문자): 문자나 숫자를 ASCII 코드 번호로 바꾼다
  • CHR/CHAR(ASCII번호): ASCII 코드 번호를 문자나 숫자로 바꾸어 준다
  • CONCAT(문자열1, 문자열2): 문자열1+문자열2 
  • SUBSTR/SUBSTRING(문자열, m, n): 문자열 중 m 위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다, 이때 n이 생략 가능한데 그러면 마지막 문자까지다. - 50회 sqld에 출제됨
  • LENGTH/LEN(문자열): 문자열의 개수를 숫자값으로 돌려준다
  • LTRIM(문자열, 지정문자): 문자열의 첫문자부터 확인해서 지정문자가 나타나면 해당 문자 제거
  • RTRIM(문자열, 지정문자): 문자열의 마지막 문자부터 확인해서 지정문자가 나타나면 해당 문자 제거
  • TRIM(문자열, 지정문자): 문자열 양쪽에 있는 지정문자를 제거한다

 

• 단일행 숫자형 함수
  • ABS(숫자): 절댓값
  • SIGN(숫자): 양수, 음수, 0인지 구분(1, -1, 0반환)
  • MOD(숫자1, 숫자2): 숫자1을 숫자2로 나눈 나머지값
  • CEIL/CEILING(숫자): 숫자보다 크거나 같은 최소 정수를 리턴
  • FLOOR(숫자): 숫자보다 작거나 같은 최대 정수를 리턴
  • ROUND(숫자 [,m]): 숫자를 소수점 m자리에서 반올림하여 리턴, m이생략되면 디폴트값은 0이다
  • TRUNC(숫자, [,m]): 숫자를 소수 m자리에서 잘라서 버린다.(m 생략시 디폴트 값 0이다)
  • EXP, POEWER, SQRT, LOG, LN: 지수, 거듭 제곱, 제곱근, 자연로그 값 리턴  - 잘 안나오는거 같음

 

 

• NULL 관련된 함수(중요함)
  • NVL(표현식1, 표현식2)/ISNULL(표현식1, 표현식2): 표현식1의 결과값이  NULL이면 표현식 2의 값을 출력
  • NULLIF(표현식1, 표현식2): 표현식1, 2가 같으면 NULL, 아니면 표현식1리턴
  • COALESCE(표현식1, 표현식2, ....): NULL이 아닌 최초의 표현식 나타냄(모든 표현식이 NULL이면 NULL리턴)

 

 

---

GROUP BY와 HAVING

• GROUP BY와 HAVING 문장

--SELECT 칼럼명 FROM 테이블명 [WHERE 조건식] [GROUP BY 칼럼이나 표현식] [HAVING 그룹 조건식];
SELECT department_id, AVG(salary)   //AVG는 그룹조건식을 통해 표현
FROM employees
GROUP BY department_id    //부서별로 그룹화
HAVING AVG(salary) >= 50000;    //부서별로 평균급여가 5만이상인 부서만 선택하게 조건 생성
//employe테이블의 데이터를 부서별로 그룹화하고 이후 조건을 통해 해당되는 부서와 AVG를 가져온다

 

•  GROUP BY 절과 HAVING 절의 특성
  • GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
  • 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
  • GROUP BY절에서는 SELECT절과는 달리 ALIAS 명을 사용할 수 없다. 
    
    • ex. from client c 로 별칭 설정후 SELECT에서는 c 사용가능하나 GROUP BY에서는 사용불가능
  • 집계 함수는 WHERE절에는 올 수 없다.(집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE절이 먼저 수행된다.)
  • WHERE 절은 전체 데이터를  GROUP으로 나누기 전에 행들을 미리 제거시킨다.
  • HAVING쩔은 GROUP BY 절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.
  • GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다. 
  • HAVING절은 일반적으로 GROUP BY  절 뒤에 위치한다.
    
    
    

---

ORDER BY 

• ORDER BY 절문장

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 50000
ORDER BY average_salary DESC;   //내림차순으로 설정(평균 급여가 높은 부서부터 뜨게됨)

ASC는 오름차순(기본 값이므로 생략가능), DESC는 내림차순

 

• ORDER BY 절 특징
  
  • 기본적인 정렬 순서는 오름차순(ASC)이다.
  • 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.
  • 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력된다.
  • Oracle에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막, 내림차순일때는 가장 먼저 위치한다.
  • 반면, SQL Server에서는 NULL값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저, 내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

 

 

 

SELECT 문장 실행순서

1. 발췌 대상 테이블을 참조한다. (FROM)
2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)
3. 행들을 소그룹화 한다.(GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력한다.(HAVING)
5. 데이터 값을 출력/ 계산한다.
6. 데이터를 정렬한다.(ORDER BY)

 

 

---

조인(JOIN)

두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 하며, 일반적인

경우 행들을 PRIMARY KEY나 FOREIGN KEY값의 연관에 의해 JOIN이 성립된다. 하지만 어떤 경우에는

이런한 PK,FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.

 

• EQUI JOIN문장

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;  --WHERE절에 JOIN조건을 넣는다

 

 

• ANSI/ISO SQL 표준 EQUI JOIN 문장

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
--ON에 조인조건을 넣는다 따로 INNER JOIN으로 표시