[SQL] SQL 기본 with MySql

MySQL 로그인

cmd-> sqlplus

id/pw입력(SYSTEM/smj9819)

(최초 접속x)사용자 로그인 (scott/tiger)

사용자 확인 – show user

 

DDL

데이터 정의어

데이터베이스를 정의하거나 그 정의를 수정할 목적으로 사용하는 언어

create(테이블 생성) / alter(테이블 수정) / drop(테이블 제거)

 

DML

- 데이터 조작어

- 사용자가 데이터를 조작(삽입/삭제/갱신)을 가능하게 하는 언어

select(query language, 데이터 검색), insert, update, delete

 

DCL

데이터 제어어

공용으로 사용하는 데이터를 관리, 감독하기 위한 언어

데이터 보안유지, 무결성 유지, 권한 부여, 병행수행 제어

commit(성공), rollback(실패) -> TCL / grant(권한 부여), revoke(권한 해제)

 

scott계정 활성화

SELECT (속성명들) FROM (테이블명들) [WHERE (조건문들, and, or로 연결)] [GROUP BY (그룹할 속성명들)] [HAVING (그룹조건)] [ORDER BY (정렬할 속성명들, asc/desc)];

INSERT INTO (테이블명) VALUES(속성명1, 속성명2....);

UPDATE (테이블 명) SET (변경할 속성문장) [WHERE (조건절 들)];

DELETE FROM (테이블 명) [WHERE (조건절)];

 

연산자

-산술 연산자 : ( ), *, /, +, -

-비교 연산자 : =, < >, !=, <, >, <=, >=

-논리 연산자 : NOT, AND, OR

-SQL연산자 : IN, BETWEEN, IS NULL, LIKE, EXISTS....

-결합 연산자 : || (스트링 값을 결합)

-집합 연산자 : UNION ALL, UNION, INTERSECT, MINUS

 

순서 : From-> Where-> Group By/Having -> Order By 

---

DDL - 뷰 (View)

기본 테이블(Base table)을 기반으로 만들어진 가상 테이블(Virtual table)

- 테이블이 실제로 존재하여 데이터를 저장하고 있는 것이 아님 (논리적으로만 존재)

 

허용된 데이터를 제한적으로 보여주기 위해서 사용

뷰를 기반으로 다른 뷰를 생성하는 것도 가능

뷰 사용의 장점

- 쉬운 질의문의 작성

- 보안 유지를 강화

- 좀 더 편리한 데이터 관리

 

뷰의 생성

CREATE VIEW 혹은 CREATE OR REPLACE VIEW 명령어를 이용하여 뷰 생성

CREATE OR REPLACE VIEW구문을 이용할 경우 기존 뷰의 내용을 수정할 수 있음

FORCE 구문을 사용하여 실제 테이블의 존재 여부와 관계없이 뷰 생성이 가능

- SQL 스크립트를 작성할 때 테이블의 생성 순서에 신경 쓰고 싶지 않은 경우 사용됨

WITH READ ONLY 구문을 사용하여 읽기만 가능한 뷰 생성 가능

(생략시 뷰를 통한 INSERT, UPDATE, DELETE가 가능하게 됨)

WITH CHECK OPTION 구문을 사용하여 조건식을 만족하는 경우에만 데이터

변경이 가능하도록 변경 가능

 

CREATE [(FORCE|NOFORCE) VIEW, OR REPLACE (FORCE|NOFORCE) VIEW] 뷰이름 AS

SELECT 구문

[WITH READ ONLY]

[WITH CHECK OPTION];

 

뷰를 통한 데이터의 변경

제한적으로 뷰를 이용하여 데이터의 변경이 가능 (삽입, 수정, 삭제 가능)

일반적으로 뷰를 통하여 조회이외의 기능을 사용하는 것은 권장되지 않음

 

뷰의 삭제

DROP VIEW 명령어를 사용하여 뷰를 제거

뷰를 삭제하더라도 기존 테이블은 영향 받지 않음

DDL - 시퀀스 (Sequence)

유일(UNIQUE)한 값을 생성해주는 오라클 객체

시퀀스를 이용하여 순차적으로 증가(auto-increment)해야 하는 칼럼에 값을 부여 가능

- 오라클 DBMS 버전 12C부터는 GENERATED AS IDENTITY 구문을 지원

- Ex) 칼럼명 NUMBER GENERATED AS IDENTITY

주로 기본키 값(id)을 생성하기 위해서 사용됨

메모리에 Cache되었을 때  시퀀스 값의  액세스 효율이 증가 (Shared Pool을 이용)

단, 반드시 연속적으로 값이 부여되어야 한다면 Cache를 사용하지 않는 것

(NOCACHE)이 좋음

- Cache Size만큼 Library Cache에 저장되며 ① DB오작동이나 ② 사용빈도가 적어 Cache aged out되는 경우 Cache Size만큼 값이 SKIP되어 저장될 수 있음

시퀀스는 테이블과는 독립적으로 저장되고 생성

 

/*보통 시퀀스는 테이블에 종속적으로 만들지만 문법적으로 규정된 것은 아님

 

Cache를 사용하면 미리 어느 정도를 만들어서 메모리에 저장해 놓고 사용

-> 날아갈 수 있다.

 

Cache aged out -> 메모리가 부족한데 사용하지 않은 채 오래 된 것 지움*/

 

시퀀스 (Sequence) 생성 문법

CREATE SEQUENCE 시퀀스 이름

[START WITH n] -- 시퀀스의 시작값 설정

[INCREMENT BY n] -- 시퀀스의 증가값

(만약 시작값이 1이고 증가값이 2이면 1, 3, 5, 7, ... 순으로 늘어남)

[MAXVALUE n | NOMAXVALUE] -- 최댓값

[MINVALUE n | NOMINVALUE] -- 최솟값

[CYCLE | NOCYCLE] -- 사이클 여부 (최댓값 도달 시 순환 여부)

[CACHE n | NOCACHE] -- 캐시 여부

[ORDER | NOORDER ] – 순서성 보장 여부

(순서성을 보장하지 않아도 된다면 퍼포먼스 상승 효과)

 

//Minvalue 를 100으로 하고 start with값이 1이면 에러

 

CURRVAL, NEXTVAL

시퀀스이름.CURRVAL을 통해 현재 시퀀스 값을 가져올 수 있음

시퀀스이름.NEXTVAL을 통해 다음 시퀀스 값을 가져올 수 있음

(단, 이 경우 시퀀스 값이 증가되어 ROLLBACK을 통해서 이전 값으로 돌리기 불가능함)

- 가장 처음 호출한 NEXTVAL 값은 START WITH로 정해준 값

CURRVAL은 꼭 NEXTVAL이 한 번 이상 호출된 이후에 사용이 가능함

 

시퀀스 (Sequence) 수정 및 삭제

ALTER SEQUENCE 명령어로 수정 (단, START WITH값은 변경 불가)

- 만약 초기 값을 다시 지정하고 싶다면 시퀀스를 삭제하고 재생성 필요

DROP SEQUENCE 명령으로 시퀀스를 삭제

 

DDL- 동의어 (Synonym)

다른 유저의 객체를 참조할 때 많이 사용

객체의 긴 이름(스키마.테이블이름)을 사용하기 편한 짧은 이름으로 접근할 수 있어

SQL 구문 단순화 가능

객체를 참조하는 사용자의 오브젝트를 감출 수 있으므로 때문에 이에 대한 보안을 유지

다른 유저의 객체를  참조할 경우가 있을 때 동의어를 사용하면 참조하고 있는

오브젝트의 이름이 바뀌었을 때 객체를 사용하는 SQL문을 모두 다시 고치는 것이 아니라

동의어만 다시 정의하면 되기 때문에 편리

 

/* As를 이용한 별명과 비슷. 객체에 부여하는 별명

다른 유저의 테이블을 사용하려면 유저이름.테이블명 으로 사용 가능하나 유저명이 드러난다는 보안문제가 있다.

보안성을 위해 사용*/

 

동의어 (Synonym) 생성 문법

CREATE [PUBLIC] SYNONYM 동의어 이름 FOR 대상 오브젝트 이름

PUBLIC 키워드를 사용하여 공개 동의어 생성 가능

(단, DBA 권한을 가진 계정만 PUBLIC 키워드 사용 가능)

PUBLIC 키워드를 사용하여 생성된 공개 동의어에 모든 사용자(계정)가 바로 접근할

수는 없으며 사용자에게 GRANT 명령어를 통해 접근 권한을 부여해줘야 함

//public을 생략하면 나만 사용가능

Transaction

밀접하게 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작

데이터를 일관되게 변경하는 DML문장으로 구성

3가지 명령어를 사용하여 트랜잭션 지원

(이를 Transaction Control Language (TCL)라고 칭하기도 함)

COMMIT / ROLLBACK / SAVEPOINT

MySQL의 경우 START TRANSACTION, PostgreSQL에서는 BEGIN TRANSACTION

명령어를 사용하여 트랜잭션 진행을 알리나 오라클에서는 따로 명시적인 명령어 없이

COMMIT 이후 바로 새 트랜잭션을 진행 (표준이 존재하지 않음)

 

Transaction이 되기 위한 조건 (ACID)

Atomicity (원자성)

- 트랜잭션은 분해가 불가능한 최소의 단위로서 연산

전체가 처리되거나 전체가 처리되지 않아야 함 (All-or-Nothing)

Consistency (일관성)

- 트랜잭션이 실행을 성공적으로 완료하면

모순 없이 일관성 있는 데이터베이스 상태를 보존

Isolation (고립성)

- 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장

Durability (영속성)

- 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장

 

/*Isolation – lock – table lock

- row lock*/

 

원자성이 필요한 사례

- 십만원을 다른 계좌로 송금하는 상황

- 송금액만큼 기존의 계좌에서 금액을 차감해야 함 (내 계좌 –10만원)

- 다른 계좌의 금액에 송금액 만큼의 금액을 올려주어야 함 (다른 계좌 +10만원)

- 만약 1번만 실행되고 2번이 모종의 이유로 인해 실행되지 않는다면?

따라서 모든 명령어 전체가 실행되지 않을 것이라면 전체가 전부 실행되지 않아야 함 (All-or-Nothing)

좀 더 복잡한 상황

(현재 콘서트 좌석(R석)을 환불하고 다른 더 좋은 좌석(S석)을 예매하려고 하는 상황)

- 콘서트 좌석 예매를 취소(R석) → 여기까지만 완료되면 고객에게 최악의 상황

- 좌석의 예매 비용만큼을 환불 계좌로 환불

→ 여기까지만 완료되면 고객은 본전은 건짐

- 콘서트 좌석 다시 예매 (S석) → 여기까지만 완료되면 콘서트 업체에게 최악의 상황

- 좌석의 예매 비용만큼을 계좌에서 차감 → 다 끝나야 가장 정상적으로 끝나는 상황

이 상황 역시 모두 한꺼번에 처리되어야 함 (All-or-Nothing)

 

Commit과 Rollback

Commit

- 수행한 DML 명령어들을 최종적으로 데이터에 반영하는 명령어

Rollback

- 수행한 DML 명령어들을 취소하는 명령어

자동으로 Commit 되는 경우 (Auto Commit)

- SQL*Plus의 정상 종료

- DDL, DCL 명령문이 실행되는 경우

자동으로 Rollback 되는 경우 (Auto Rollback)

- SQL*Plus의 비정상 종료

- 정전이나 컴퓨터의 의도치 않은 다운

 

Transaction

현재 사용자는 SELECT문장으로 DML작업의 결과를 확인할 수 있음

다른 사용자는 SELECT문장으로 현재 사용자 사용한 DML문장의  결과를 확인할 수 없음

변경할 행은 LOCK이 설정되어서 다른 사용자가 변경 할 수 없음 (고립성과 연관)

//LOCK이 되어도 SELECT 가능

 

Transaction (SAVEPOINT)

큰 트랜잭션을 작은 단위로 분할하여 실행하고 되돌릴 수 있게 하는 명령어

SAVEPOINT 세이브포인트이름

- 같은 이름의 세이브포인트를 생성하면 그 전에 있던 세이브포인트는 지워짐

ROLLBACK TO 세이브포인트이름

- 해당 세이브포인트 시점으로 롤백

- 역순으로 롤백은 가능하나 그 반대로는 롤백이 불가능

 

/*Save1

Save2

Save3

Save4 가 있을 때,

4-3-2-1은 가능하지만 4-2-1-3처럼 거꾸로 가는 건 불가능*/

DML - 조인(JOIN)

/*카르테시안 곱 (+where절)

Inner Join (+On 조건절) / (+Using 컬럼명)

Self join – 자기 테이블을 가지고 조인을 함*/

 

결합 (JOIN)

JOIN은 하나 이상의 테이블의 결합을 위해서 쓰임

중복을 줄이기 위해서 여러 테이블로 나뉘어 저장된 정보들을 쓰임에 맞게,

의미에 적합하게 모으는 작업

FOREIGN KEY 제약 조건이 적극적으로 사용됨

ANSI 표준 SQL은 여러 유형의 JOIN을 규정

- CROSS JOIN

- NATURAL JOIN

- INNER JOIN

- OUTER JOIN (LEFT, RIGHT, FULL)

 

교차결합 (CROSS JOIN)

FROM 구에 테이블을 2개 이상 지정시 교차결합

(CROSS JOIN, 곱집합(Cartesian Product))한 계산한 결과를 돌려줌

- SELECT * FROM 테이블1, 테이블2, …, 테이블n;

- 결과물의 계산된 행 수는

(테이블1의 행 개수) x (테이블2의 행 개수) x … x (테이블n의 행 개수)

 

내부결합 (INNER JOIN)

일반적으로 곱집합은 거의 사용하지 않고 내부결합(INNER JOIN)을 이용하여 테이블을 연결

- SELECT * FROM 테이블1, 테이블2 WHERE 테이블1.id = 테이블2.id;

- 교차결합을 이용하여 얻어낸 결과물에 WHERE 조건을 적용한 경우와 같음

- 여기서 WHERE의 id 비교문이 결합조건

- 방법1) INNER JOIN, ON 구문 사용

- SELECT * FROM 테이블1 INNER JOIN 테이블2 ON 결합조건

(테이블1.결합용 칼럼1 = 테이블2.결합용 칼럼2)

- 방법2) INNER JOIN, USING 구문 사용

- SELECT * FROM 테이블1 INNER JOIN 테이블2 USING (결합 조건에 쓰일 칼럼명)

 

/*Using을 사용하면

Using(컬럼명);으로 사용하는데, 이걸 사용하면 두 테이블에 무조건 같은

이름의 컬럼이 있다고 가정함*/

외부결합 (OUTER JOIN)

외부결합 : 어느 한쪽 테이블에만 데이터가 존재하는 경우 연결 방법을 정해줄 수 있음

LEFT OUTER JOIN, RIGHT OUTER JOIN의 활용

- LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 RIGHT OUTER JOIN은 오른쪽 테이블을 기준으로 연결

- SELECT * FROM 기준 테이블명 LEFT OUTER JOIN 결합 테이블명 ON 결합조건

- SELECT * FROM 결합 테이블명 RIGHT OUTER JOIN 기준 테이블명 ON 결합조건

(위의 결과와 같음)

FULL OUTER JOIN의 활용

- LEFT OUTER JOIN과 RIGHT OUTER JOIN이 합쳐진 결과를 보여줌

- SELECT * FROM 기준 테이블명 FULL OUTER JOIN 결합 테이블명 ON 결합조건

ON 대신 USING도 사용 가능

 

결합 (SELF JOIN)

같은 테이블을 이용하여 결합하는 방식

따로 FOREIGN KEY를 지정하거나 JOIN 구문을 사용하지 않음