SQLD[요약정리(3)05/05~05/28]

3장. 관계형 데이터베이스 

  -데이터베이스 : "특정기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장한 것"

  

★ SQL: SQL은 관계형데이터베이스에서 데이터 정의, 데이터 조작, 데이터제어를 하기 위해 사용하는 언어

- 데이터조작어(DML) - SELECT, INSERT, UPDATE, DELETE(NOT AUTO COMMIT)

- 데이터정의어(DDL) - CREATE, ALTER, DROP, RENAME(AUTO COMMIT)

- 데이터제어어(DCL) - GRANT, REVOKE

 - 트랜잭션제어어(TCL) - COMMIT, ROLLBACK

★트랜잭션 

  -트랜잭션은 데이터베이스의 작업을 처리하는 단위이다.

  -하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.

  -트랜잭션은 분할할 수 없는 최소의 단위이다. 그렇기 때문에 전부 적용하거나 전부 취소한다.  

  *트랙잭션의 특성 4가지  

   ‒    원자성(Atomicity): 전부 실행되거나 전혀 실행되지 않음 (All or Nothing)

   ‒    일관성(Consistency): 트랜잭션으로 인한 DB 상태의 모순이 없음

   ‒    고립성(Isolation): 부분적인 실행 결과에 다른 트랜잭션이 접근할 수 없음, LOCKING으로 고립성 보장

   ‒    영속성(Durability): 트랜잭션의 결과는 영구적으로 저장됨

 

★DDL

  1.데이터 타입

   -CHAR

   -VARCHAR2

   -NUMBER(L,D)

   -DATE, DATETIME :날짜형, 데이터 크기 지정이 필요하지않음

  2. CREATE TABLE

  -테이블 및 컬럼 명명 규칙

  -1)알파벳 2)숫자 3)'-'(언더바) 4)$(달러)

*제약 조건

 -primary key :테이블 당 하나의 기본키만 정의 가능 (unique&NOT NULL)

 -foreign key(외래키):다른 테이블의 기본키를 외래키로 지정,참조 무결성 제약조건

 -unique key :행 데이터를 식별하기 위해 생성함, NULL 가능

 -default : 'default 값'으로 기본값 설정 

 

1.ALTER TABLE : 테이블 컬럼 관련 변경 명령어

-컬럼 추가 : ALTER TABLE 테이블명 ADD (컬럼명 데이터 타입)
-컬럼 삭제 : ALTER TABLE 테이블명 DROP COLUMN 컬럼명(삭제후 복구 불가)

-컬럼 설정 변경 : ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입 제약조건)

-컬럼명 변경 : ALTER TABLE 테이블명 RENAME COLUMN 컬럼명

-제약조건 추가 : ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건

-제약조건 제거 : ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건

 

2.DROP TABLE 

  -테이블의 데이터와 구조 삭제 ,복구불가

  -CASCADE CONSTRAINT 옵션으로 관련 테이블의 참조 제약조건도 삭제하여 참조 무결성을 준수할수 있음

3.TRUNCATE TABLE 

  -테이블 전체 데이터 삭제(<->DROP TABLE은 테이블 자체를 제거함)

  -로그를 기록하지 않기 때문에 ROLLBACK 불가

★DML

  -INSERT: 데이터 입력                                       SQL>> INSERT INTO 테이블명 (칼럼명, …) VALUES (필드값, …);

  - INSERT INTO 테이블명 VALUES (필드값, …);

   -UPDATE: 데이터 수정                                     SQL>> UPDATE 테이블명 SET 칼럼명=필드값;

   -DELETE: 데이터 삭제                                   SQL>> DELETE FROM 테이블명 WHERE 조건절;

                                                                    SQL>> DELETE FROM 테이블명;

4. 문자열의 합성 연산자: +(플러스),CONCAT 함수로도 2개 문자열 합성가능,ORACLE 에서는 ||도 가능

5. DUAL : Oracle의 기본 더미 테이블, 연산 수행을 위해 사용됨

*DISTINCT:중복시 1회만 출력 (성능 안좋아짐)

★DCL

 -DCL: 유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요함

 -GRANT: 권한 부여        SQL>> GRANT 권한 ON 오브젝트 TO 유저명;

 -REVOKE: 권한 제거       SQL>> REVOKE 권한 ON 오브젝트 TO 유저명;

2. 권한(Privileges)

  -SELECT, INSERT, UPDATE, DELETE, ALTER, ALL : DML 관련 권한

   -EFERENCES : 지정된 테이블을 참조하는 제약조건을 생성하는 권한

   -INDEX : 지정된 테이블에서 인덱스를 생성하는 권한

3.  Oracle의 유저

 -SCOTT: 테스트용 샘플 유저

 -SYS: DBA 권한이 부여된 최상위 유저

 - SYSTEM: DB의 모든 시스템 권한이 부여된 DBA

4. ROLE: 권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여할 수 있음

★TCL

1.  TCL: 데이터 무결성 보장을 목적으로 함, 1) 영구 변경 전 확인과 2) 연관 작업 동시처리 가능

   -Oracle은 1) SQL 문장을 실행하면 트랜잭션이 시작되고 2) TCL을 실행하면 트랜잭션이 종료됨

   -DDL을 실행하면 자동 커밋 (DML 이후 커밋 없이 DDL을 실행해도 자동 커밋)

   -DB를 정상적으로 종료하면 자동 커밋, 애플리케이션 등의 이상으로 DB 접속이 단절되면 자동 롤백

2. COMMIT: 데이터를 DB에 영구적으로 반영하는 명령어, 커밋 시 트랜잭션이 완료되어 LOCKING이 해제됨, SQL Server은 기본적으로 자동 커밋

 

 1. COMMIT 전

 

‒ 데이터 변경이 메모리 버퍼에만 영향을 받았기 때문에 복구 가능 (NOLOGGING 옵션 사용 시 버퍼 캐시의 기록을 생략하여 입력 성능이 향상됨)

‒ 사용자는 SELECT절로 결과를 확인할 수 있으나 다른 사용자는 현재 결과를 볼 수 없음

‒  변경된 행에 LOCKING이 설정되어 다른 사용자가 변경할 수 없음 (LOCKING이 안 걸린 상태일 때 여러 사용자가 데이터를 변경하면 상관없음)

 

 2. COMMIT 후

 

‒ 변경 사항이 DB에 반영되고 이전 데이터는 복구 불가

‒ 모든 사용자가 결과를 볼 수 있음

‒ LOCKING이 해제되어 다른 사용자가 행을 조작할 수 있음

3. ROLLBACK: 트랜잭션 시작 이전의 상태로 되돌리는 명령어, COMMIT 이전 상태로 돌려줌, ROLLBACK 시 LOCKING이 해제됨

  -SAVEPOINT: 트랜잭션 일부만 롤백 할 수 있도록 중간상태를 저장하는 명령어, ‘ROLLBACK TO 저장점명’ 시 해당 저장점 상태로 돌려줌, 동일한 저장점명이 있으면 나중 저장점이 유효함

  -SQL Server에서는 ’BEGIN TRAN’으로 명시해야 가능함