:: 작업장

[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL

1. 트랜잭션 개요.

- 트랜잭션은 데이터베이스의 논리적인 연산단위이다.

- 트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

(분할할 수 없는 최소단위)

- 전부 적용하거나 전부 취소한다. ( ALL OR NOTHING)

- 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 COMMIT, 트랜잭션 시작 이전 상태로 되돌리는 것을 ROLLBACK 이라고한다. SAVEPOINT 기능과 함께 TCL로 분류한다.

- 트랜잭션의 대상이 되는 SQL 문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML문이다.

- SELECT 문은 트랜잭션 대상은 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.

* 트랜잭션의 특성 ACID

2. COMMIT

- 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다.

* COMMIT이나 ROLLBACK 이전의 데이터 상태

- 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.

- 현재 사용자는 SELECT문으로 결과를 확인할 수 있다.

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

- 변경된 행은 잠금이 설정되어 다른사용자가 변경할 수 없다.

*COMMIT 이후의 데이터 상태

- 데이터에 대한 변경사항이 데이터베이스에 반영된다.

- 이전 데이터는 영원히 잃어버리게된다.

- 모든 사용자는 결과를 볼 수 있다.

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있다.

* SQL server의 COMMIT

- 오라클은 DML을 수행할 경우 사용자가 트랜잭션을 종료하기 위해 COMMIT 혹은 ROLLBACK을 수행해야 종료된다.

하지만 SQL server경우에는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행이후 사용자가 COMMIT이나 ROLLBACK 을 처리할 필요가 없다.

DML 구문이 성공적이면 자동으로 COMMIT되고 오류가 발생할 경우 자동으로 ROLLBACK  처리된다.

* SQL server의 트랜잭션 방식

1) AUTO COMMIT

- SQL server의 기본 방식이며 DML, DDL 수행할 때마다 DBMS 가 트랜잭션을 컨트롤 하는방식이다.

명령어가 성공적으로 수행되면 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

2) 암시적 트랜잭션

- 오라클과 같은방식으로 처리된다. 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다.

3) 명시적 트랜잭션

- 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.

BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 시작

COMMIT TRANSACTION (TRANSACTION 생략가능)

ROLLBACK TRANSACTION (TRANSACTION 생략가능) 으로 종료한다.

ROLLBACK 구문을 만나면 최초 BEGIN TRANSACTION 까지 ROLLBACK이 수행된다.

3. ROLLBACK

- 테이블에 입력한 데이터나 수정한데이터 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수있다.

- 변경사항이 취소되어 데이터의 이전상태로 복구하며 관련된 행에대해 LOCK이 풀리고 다른 사용자들이 데이터를 변경할 수 있다.

* SQL server의 ROLLBACK

- AUTOCOMMIT이 기본방식이므로 임의적으로 ROLLBACK을 수행하기 위해서는 명시적으로 트랜잭션을 선언해야한다.

*COMMIT과 ROLLBACK을 수행하며 얻는 효과

- 데이터 무결성 보장

- 영구적인 변경을 하기 전 데이터의 변경사항 확인 가능

- 논리적으로 연관된 작업을 그루핑하여 처리가능.

4. SAVEPOINT

- ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백 하는것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

- 복수의 저장점을 지원할 수 있으며 동일한 이름으로 저장할 경우 나중에 정의한 저장점이 유효하다.

- 오라클

- SQL server

- ROLLBACK 원리 (오라클)

 - A  저장점으로 돌린 후 B 저장점으로 되돌릴 수 없다. 특정 지점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효화되기 때문

(ROLLBACK TO A를 실행한 시점에서 A 이후에 저장한 B는 존재하지않는다.)

* ROLLBACK TO B 후 ROLLBACK TO A는 가능하다.

***트랜잭션 정리

- 해당테이블에 데이터의 변경을 발생시키는 입력, 수정, 삭제 수행시 그 변경되는 데이터의 무결성을 보장하는것이 COMMIT과 ROLLBACK의 목적이다.

- COMMIT은 변경된 데이터를 테이블이 영구적으로 반영해라 라는 의미를 갖음.

- ROLLBACK 변경된 데이터가 문제가있으니 변경 전 데이터로 복귀하라는 의미.

- 저장점은 데이터 변경을 저장점까지만 롤백하라는 의미.

- 오라클의 트랜잭션은 SQL문이 시작되면 자동으로 시작되고 COMMIT 또는 ROLLBACK을 수행할 때 종료된다.

- 다음의 경우에는 COMMIT과 ROLLBACK을 수행하지 않아도 자동으로 트랜잭션이 종료됨.

* CREATE, INSERT, DROP, RENAME, TRUNCATE TABLE 등 DDL이 수행되면 그 전후 시점에 자동으로 커밋된다.

* DML문장 이후 커밋없이 DDL을 수행하면 DDL 수행 전에 자동 커밋된다.

* 데이터메이스를 정상종료하면 자동으로 커밋된다.

* 애플리캐이션의 이상 종료로 데이터베이스와 접속이 단절되었을 때는 트랜잭션이 자동 롤백된다.

* SQL Server의 트랜잭션은 AUTO COMMIT이 기본방식이다.

(애플리케이션의 이상종료로 데이터베이스와 접속이 단절되었을 경우에는 오라클처럼 자동 ROLLBACK된다.)

[SQLD] 2과목 SQL 기본및 활용 - 1장 3절 DML

1. INSERT

- 테이블에 데이터를 입력하는 방법은 두 가지 유형이 있으며 한 번에 한 건만 입력된다.

* 컬럼명리스트와 입력되는 값은 서로 1:1로 매핑해서 입력하면된다. 해당 칼럼의 데이터유형이 문자형일 경우 ' '로 묶어주며 숫자일 경우 ' '를 붙이지 않는다.

1) INSERT INTO 테이블명 (COLUMN_LIST)

VALUES(COLUMN_LIST에 넣을 VALUE_LIST);

* 테이블의 칼럼을 정으할 수 있는데, 이때 칼럼의 순서는 테이블의 칼럼순서와 매치할 필요는 없으며 정의하지 않은 칼럼은 DEFAULT로 NULL값이 입력된다. NOT NULL제약조건이나 PK 지정된 컬럼은 NULL이 허락되지 않는다.

2) INSERT INTO 테이블명

VALUES (전체 COLUMN에 넣을 VALUE_LIST);

* 모든 칼럼에 데이터를 입력하는 경우 굳이 COLUM_LIST를 언급하지 않아도 되지만 칼럼의 순서대로 빠짐없이 데이터가 입력되어야한다.

ex) player 테이블에 값 추가.

1)

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO)

VALUES ('2002007','박지성','K07','MF',178,73,7);

2)

INSERT INTO PLAYER

VALUES('200207','이청용','','BLUEDRAGON',2002',MF',17,NULL,NULL,'1',180,69);

2. UPDATE

-입력한 정보 중 잘못 입력되거나 변경이 발생하여 정보를 수정할 경우 

- 구문

UPDATE 테이블명

SET 수정해야될 컬럼명 = 수정되기 원하는 새로운 값 ;

ex

1) 숫자타입.

UPDATE PLAYER

SET BACK_NO = 99;

2)문자타입

UPDATE PLAYER

SET POSITION = 'MF';

3. DELETE

- 테이블의 정보가 필요없게 될 경우 데이터 삭제를 수행한다.

- 구문

DELETE [FROM] 삭제를 원하는 테이블명;

* FROM은 생략 가능하며, WHERE절 없을 경우 테이블의 전체 데이터가 삭제된다.

* DDL명령어와 DML 명령어를 처리하는 방식에 있어서 데이터베이스는 차이가있다.

DDL 명령어의 경우 직접 데이터베이스 테이블에 영향을 미치기 때문에 AUTO COMMIT이 된다.

DML 명령어의 경우 조작하는 테이블을 메모리 버퍼에 올려놓고 작업을 하기 때문에 테이블에 영향을 실시간으로 주지않는다. 실제 반영되기 위해서는 COMMIT명령이 필요하다.

( SQL server경우에는 DML의 경우도 Auto commit으로 처리가된다.)

* TRUNCATE는 삭제된 데이터의 로그가 없으므로 ROLLBACK이 불가능하다.

SQL Server에서는 임의적으로 트랜잭션을 시작한 후 Truncate table을 이용하여 데이터를 삭제한 이후 오류가 발견되어 복구를 원할 경우 ROLLBACK문을 이용하여 데이터를 원상태로 돌릴 수 있다.

4. SELECT 

- 사용자가 입력한 데이터를 조회하는 쿼리.

SELECT [ALL/DISTINCT] 칼럼명 ....

FROM 테이블명

* ALL은 DEFAULT 옵션이므로 표시하지 않아도 된다. 중복된 데이터가 있어도 모두 출력한다.

 DISTINCT 중복된 컬럼은 제거하고 1건으로 표시한다.

ex) 

SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, WEIGHT, HEIGHT, BACK_NO

FROM PLAYER;

ex) DISTINCT

SELECT DISTINCT ALL_POSITION

FROM PLAYER;

POSITION

---------------

GK

DF

FW

MF

* WILD CARD 사용하기

SELECT * 

FROM PLAYER;

- 조회결과는 기본적으로 대문자로 보이고, 좌측정렬은 문자 및 날짜데이터 우측 정렬은 숫자데이터이다.

* ALIAS 부여하기

- 조회된 결과에 별명을 부여하여 칼럼 레이블을 변경할 수 있다.

- 컬럼바로뒤에 온다. 컬럼과 ALIAS 사이에 AS, as 키워드를 사용할 수 있다.(옵션)

- 이중부호는 ALIAS가 공백, 특수문자를 포함할 경우 대소문자 구분이 필요할 경우 사용한다.

ex)

SELECT  PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게

FROM PLAYER;

* AS는 사용하지 않아도 같은 결과가 출력된다.

SELECT  PLAYER_NAME  선수명, POSITION 위치, HEIGHT 키, WEIGHT 몸무게

FROM PLAYER;

[SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL

1. 데이터 유형

- 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정한다.

* VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다.

* CHAR에서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다.

- 공백 채우기 비교에서는 우선 짧은쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한 후 앞에서부터 비교한다.(공백만 다른 문자열은 같다고 판단함.)

* VARCHAR

- 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.

ex) 

CHAR 유형:  'AA' = 'AA    '

VARCHAR 유형 'AA' != 'AA     '

2. CREATE TABLE

가. 테이블과 칼럼 정의

- 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본칼럼으로 지정한다.

- 기본키는 단일 칼럼이 아닌 여러개의 칼럼으로 만들어질 수 있다.

- 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다.

나. CREATE TABLE

- 테이블을 생성하는 구문.

- 테이블 생성 시 주의할 규칙.

1) 테이블명은 객체를 의미할 수 있는 적절한 이름으로 사용한다. 가능한 단수형을 권고.

2) 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

3) 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

4) 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어서 지정한다.

5) 각 칼럼들은 콤마로 구분되고, 테이블 생성명의 끝은 ;로 끝난다.

6) 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.

7) 칼럼 뒤에 데이터 유형은 꼭 지정되어야한다.

8) 테이블명과 칼럼명은 반드시 문자로 시작해야 하고 벤더별로 길이에 대한 한계가 있다.

9) 벤더에서 사전에 지정한 예약어는 사용할 수 없다.

10) A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

ex) 테이블명이 잘못 정의된 사례

10_player

star-player

- 규칙에 맞게 선수 테이블 생성

1) ORACLE

2) SQL SERVER

- 테이블 생성시 대소문자 구분하지 않는다.

- 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

- DATETIME 데이터 유형에는 별도의 크기를 지정하지 않는다.

- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야한다.

- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.

* 제약조건은 PLAYER_NAME, TEAM_ID 컬럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례와 같은 칼럼레벨 정의방식과, PLAYER_PK PRIMARY KEY, PLAYER FK FOREIGN KEY 사례처럼 테이블 마지막에 모든 제약조건을 기술하는 테이블레벨 방식이 있다.

다. 제약조건 (CONSTRAINT)

- 제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터 무결성을 유지하기 위한 데이터베이스의 보편적 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.

- 제약조건의 종류

- 조건에 따른 테이블 생성

1) ORACLE

2)SQL server

라. 생성된 테이블 구조 확인.

오라클 : DESC 테이블명; 또는 DESCRIBE 테이블명;

SQL SEVER :  exec sp_help 'dbo.테이블명'

                        go

마. SELECT 문장을 통한 테이블 생성.

- Create Table ~  As select ~

해당 방법을 이용할 경우 칼럼별로 데이터 유형을 다시 정의하지 않아도된다.

- CATS기법 사용시 기존테이블의 제약 조건중에 Not null만 새로운 테이블에 적용되며 기본키, 고유키, 외래키, Check 등의 다른 제약조건은 없어진다.

- SQL Server에서는 Select ~ into ~ 를 활용하여 같은 결과를 얻을 수 있으며 칼럼속성에 Identity를 사용했다면 Identity 속성까지 같이 적용된다.

* 오라클

CREATE TABLE TEMP_PLAYER

AS SELECT * FROM PLAYER;

* SQL SERVER

SELECT * INTO TEAM_TEMP FROM TEAM;

3. ALTER TABLE

- 한번 생성된 테이블의 구조를 변경하기위해 사용한다.

- 칼럼을 추가/삭제하거나 제약조건을 추가/삭제 하는 작업을 진행하게된다.

가. ADD COLUMN.

ALTER TABLE 테이블명

ADD 추가할 칼럼명 데이터 타입;

* 추가된 칼럼은 마지막에 위치하게되며 위치를 지정할 수 없다.

- 오라클

ALTER TABLE PLAYER

ADD (ADDRESS VARCHAR2(80));

* 아래 명령어도됨.

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR2(80);

- SQL SERVER

ALTER TABLE PLAYER

ADD ADDRESS VARCHAR(80);

나. DROP COLUMN

- 테이블에서 필요없는 칼럼을 삭제할 수 있으며 데이터가 없거나 있거나 모두 삭제 가능하다.

- 한 번에 하나의 칼럼만 삭제 가능하며 칼럼 삭제 후 최소 하나 이상의 칼럼이 데이터에 존재해야한다.

- 한 번 삭제된 칼럼은 복구가 불가능하다.

ALTER TABLE 테이블명

DROP COLUMN 컬럼명;

다. MODIFY COLUMN 

- ALTER TABLE 명령어를 통해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대해 변경을 포함할 수 있다.

- 칼럼에 대한 데이터 정의를 변경하는 명령어

ALTER TABLE 테이블명

MODIFY ( 칼럼명1 데이터유형 [DEFAULT] [NOTNULL] ,

                 칼럼명2 데이터유형 .... ) ;

* 칼럼 변경 시 고려할 사항.

- 해당 칼럼의 크기는 늘릴 수 있지만 줄일 수는 없다. (기존 데이터의 훼손 방지)

- 해당 칼럼이 NULL값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.

- 해당 칼럼이 NULL값만 가지고 있으면 데이터 유형을 변경할 수 있다.

- 해당 칼럼의 DEFAULT 값을 바꾸면 변경작업 이후 발생하는 행 삽입에만 영향을 미친다.

- 해당 칼럼에 NULL값이 없을 경우에만 NOT NULL제약조건을 추가할 수 있다.

ex) TEAM 테이블에 ORIG_YYYY컬럼의 데이터 유형을 CHAR(4) -> VARCHAR2(8) 형식으로 변경하고 향후 입력되는 데이터의 DEFAULT 값으로 20090826 적용하고 모든 행의 ORIG_YYYY컬럼에 널이 없으므로 제약조건을 NOT NULL로 변경한다

- 오라클

ALTER TABLE TEAM

MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20150826' NOT NULL) ;

-SQL SERVER
ALTER TABLE TEAM

ALTER CLOUMN ORIG_YYYY VARCHAR(8) DEFAULT '20150826' NOT NULL;

ALTER TABLE TEAM

ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '201508226' FOR ORIG_YYYY;

- RENAME COLUMN

칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우.

ALTER TABLE 테이블명

RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 칼럼명;

ex) 

- 오라클

ALTER TABLE PLAYER

RENAME COLUMN PLAYER_ID TO TEMP_ID;

ALTER TABLE PLAYER 

RENAME COLUMN TEMP_ID TO PLAYER_ID;

- SQL server

sp_rename 프로시져를 이용하여 변경한다.

sp_rename 변경해야 할 컬럼명, 새로운 칼럼명, 'COLUMN';

ex) sp_rename 'dbo.TEAM_TEMP.TEAMID', 'TEAM_TEMP_ID', 'COLUMN';

라. DROP CONSTRAINT

- 테이블 생성 시 부여했던 제약조건을 삭제하는 명령

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

ex) 

- 오라클 / SQL server

ALTER TABLE PLAYER

DROP CONSTRAINT PLAYER_FK;

마. ADD CONSTRAINT

- 테이블 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.

ALTER TABLE 테이블

ADD CONSTRAINT 제약조건명 제약조건 (칼럼명) ;

ex)

ALTER TABLE PLAYER

ADD CONSTRAINT PLAYER_FK

FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);

* 참조 제약조건을 추가하면 참조 무결성 옵션에 따라 테이블의 데이터를 삭제하려 할 경우 외부에서 참조하고 있기 때문에 삭제가 불가능하게 제약할 수 있다.

=> FK를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지할 수있다.

4. RENAME TABLE

- RENAME 명령어를 사용하여 테이블 이름을 변경할 수 있다.

오라클: RENAME 변경전 테이블명 TO 변경후 테이블명;

SQL SERVER : sp_rename 변경전 테이블명, 변경후 테이블명; 

ex)

오라클

RENAME TEAM TO TEMP_TEAM;

SQL server

sp_rename 'dbo.team','temp_team';

5. DROP TABLE

- 테이블을 잘못 만들거나 더이상 필요없을 경우 삭제하는 명령어

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

* DROP 명령어를 사용하면 테이블의 데이터 및 구조를 삭제한다.

* CASCADE CONSTRAINT 옵션을 사용하면 테이블과 관계가 있었던 참조제약조건에 대해서도 삭제한다.

 (SQL server에서는 CASCADE옵션이 존재하지 않으며 테이블 삭제하기 전에는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제해야한다.)

ex) 

- 오라클 

DROP TABLE TEMP_EMP;

- SQL server

DROP TABLE TEMP_EMP;