[SQLD] 2과목 - 3장. 관리 구문

제 1절 DML - 조회 및 검색, 데이터 변형

1. DML

• 호스트 프로그램 속에 삽입되어 사용, 데이터 부속어라고도 함
• Procedural DML(절차적 데이터 조작어): 초급언어, 사용자가 무슨 데이터를 원하고 어떻게 접근해 처리할 것인지 명세해야 함
• Nonprocedural DML(비절차적 데이터 조작어): 고급언어, 사용자가 무슨 데이터를 원하는지만 명세하고 어떻게 접근할 것인지는 하지 않음. 선언적 언어라고도 함

2. INSERT - 데이터 입력 방법

• INSERT INTO 고객 (이름, 주소) VALUES ('이순신', '한양');
• INSERT INTO 테이블명 (컬럼리스트 / 생략 = 전체컬럼)
  VALUES (컬럼명 순서에 맞춰 입력할 값 매핑해 작성);
• 데이터가 문자형일 경우 “”로 묶어서 입력

3. UPDATE - 정보 수정

• 데이터가 문자형일 경우 “”로 묶어서 입력
• SQL>> “Employees라는 테이블에서 EmployeeID가 4인 직원의 Salary를 50000으로 변경”
  UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 4;
• UPDATE 테이블명
  SET 칼럼명 = 변경할 값 WHERE 조건;

4. DELETE - 삭제

• DELETE FROM 테이블명 WHERE 조건;
• FROM 문구는 생략 가능
• WHERE 절을 사용하지 않는다면 테이블의 전체 데이터 삭제

5. SELECT - 데이터 조회

• SELECT [ALL(*)   | DISTINCT] 칼럼1, 칼럼2, … FROM 테이블명;
• ALL(*): 디폴트 옵션 (중복 데이터 모두 출력)
• DISTINCT: 중복제거 하여 1건으로 출력
• 조회된 결과에 별명(에리아스/ALIAS)을 부여해서 칼럼 레이블 변경 가능
• 칼렁명 바로 뒤에 오고, 칼럼명과 ALIAS 사이에 AS 키워드 사용 가능 (선택임)
• 공백/특수문자, 대소문자 구분이 필요할 경우 “”로 묶어서 사용

6. 산술 연산자

• NUMBER와 DATE 자료형에 적용 (수학 사칙연산과 동일)

7. 합성 연성자 - 문자와 문자를 연결하는 합성(CONCATENATION) 연산자

• [Oracle] → ||
• [SQL Server] → +
• SQL>> SELECT PLAYER_NAM+’선수,’+HEIGHT+’cm,’+WEIGHT+’kg’ 체력정보 FROM PLAYER;
  정경량선수,173cm,65kg 정은익선수, 176cm, 63kg

 

제 2절 TCL - 트랜잭션 제어

1. TCL

• 논리적 작업단위를 묶어 DML에 의해 조작된 결과를 작업단위 별로 제어
• 일부에서는 DCL로 분류하기도 함

2. 트랜잭션

• 데이터베이스의 논리적인 연산 단위
• 트랜잭션에는 하나 이상의 SQL 문장이 포함됨
• 트랜잭션은 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB조작을 가리킴
• 분할할 수 없는 회소단위, 전부 적용하거나 전부 취소해야 함

3. 트랜잭션의 특징

1. 원자성(atomicity): 모두 성공 아님 전혀 실행되지 않은 상태로 남기(Rollback)
2. 일관성(consistency): 트랜잭션이 실된 이후에도 데이터 베이스 내용 잘못 있으면 안됨
3. 독립성.고립성(isolation): 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향 받으면 안됨
4. 지속성(durability): 트랜잭션이 성공적으로 수행되면 갱신된 데베 내용은 영구적으로 저장

4. 트랜잭션을 컨트롤하는 TCL

• 커밋(COMMIT): 올바르게 반영된 데이터를 데베에 반영시키는 것
• 롤백(ROLLBACK): 트랜잭션 시작 이전의 상태로 되돌리는 것
• 저장점(SAVEPOINT): 저장점 기능
• 잠금(LOCKING): 다른 트랜잭션이 동시에 접근하지 못하고록 제한
• 트랜잭션 대상이 되는 SQL
  • UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문
  • SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문

 

5. COMMIT - 입력/수정/삭제한 자료가 문제가 없을 경우 변경 사항 적용

• COMMIT 이전 상태
  • 단지 메모리 버퍼에만 영향 주고, 이전 상태로 복구 사능
  • 현재 사용자는 SELECT 문으로 변경 결과 확인 가능
  • 다른 사용자는 현재 사용자가 수행한 결과 확인 불가능
  • 변경된 행은 아직 잠금(LOCKING) 설정되어 다른 사용자가 변경 불가능
• COMMIT 이후 상태
  • 데이터에 대한 변경사항을 데베에 영구 반영
  • 이전 데이터는 영원히 잃어버림
  • 모든 사용자가 결과 조회 가능
  • 변경된 행은 잠금 해제되어 다른 사용자가 변경 가능
• Auto COMMIT
  • [Oracle] 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료
  • [SQL Server] 기본적으로 Auto Commit 모드, DML 구문이 성공이면 자동으로 COMMIT되고 오류가 발생할 경우 자동으로 ROLLBACK 처리

6. ROLLBACK - COMMIT 이전으로 되돌림

• 테이블에 입력/수정/삭제한 데이터에 대해 COMMIT 이전에 변경사항을 취소
• 이전 데이터가 다시 재저당됨
• 관련 행에 대한 잠금이 풀리고, 다른 사용자들이 데이터 변경 가능

7. COMMIT과 ROLLBACK을 사용함으로써 얻을 수 있는 효과

• 데이터 무결성 보당
• 영구적인 변경을 하기 전에 데이터의 변경사항을 확인 가능
• 논리적으로 연관된 작업을 그룹핑하여 처리 가능

8. SAVEPOINT - 저장점, 데이터 변경을 사전에 지정한 저장점까지만 롤백

• 저장덤을 정의하면 롤백을 할 경우 전체 롤백이 아닌 저장점까지의 일부만 롤백
• SAVEPOINT는 여러 개 지정할 수 있음
• 동일 이름으로 저장점 지정 시 가장 나중에 정의한 저장점이 유효
• Point 1로 되돌리고 나면 그보다 미래인 Point2로는 되돌릴 수 없음
• 저장점 없이 롤백하면 모든 변경사항 취소
• [Oracle] SAVEPOINT 포인트 이름; → ROLLBACK TO 포인트 이름;
  [SQL Server] SAVE TRANSACTION 포인트 이름; → ROLLBACK TRANSACTION 포인트 이름;

 

제 3절 DDL - 데이터 구조 정의

1. 데이터의 유형

• 숫자 타입
  • ANSI/ISO 기준: Numeric, Decimal, Dec, Small Int, Integer, Int, Big, int, Float, Real, Double Precision
  • SQL Server / Sybase: 작음 정수, 정수, 큰 정수, 실수 등 + Money, Small Money
  • Oracle: 숫자형 타입에 대해서 Number 한 가지 타입만 지원
  • 벤더에서 ANSI/ISO 표준을 사용할 땐 기능을 중심으로 구현, 표준과 다른 용어 사용 허용
• 문자열 유형
  • CHAR(고정길이) 과 VARCHAT(가변길이)의 차이
  • CHAR에서 문자열 비교 = 공백을 채워서 비교
  • VARCHAR에서 비교 = 맨 처음부터 한 문자씩 비교 (공백도 하나의 문자로 취급)
• 테이블의 칼럼이 가지고 있는 대표적인 4가지 유형
  • CHARACTER(s), VARCHAR(s), NUMERIC, DATETIME

2. CREATE TABLE - 테이블 생성

• CREATE TABLE 테이블 이름( 칼럼명1 DATATYPE [DEFAULT 형식], …);
  CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL, BIRTH CHAR(1) INT);
  
  
• 명명 규칙: 알파벳 → 숫자 → _(언더바) → $(달러) → #(샵)
• 테이블 생성시 대/소문자 구분은 하지 않음. 기본적으로 테이블이나 칼럼명 = 대문자로 
• DATETIME 데이터 유형에는 별도로 크기를 지정하지 않음
• 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시
• 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마x
• 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가

3. 제약조건(CONSTRAINT) - 데이터 무결성 유지 방법, 사용자가 원하는 조건의 데이터만 유지

• PK (Primary Key), 기본키: 한 테이블에 하나만 지정 가능(unique), NULL 입력 불가(기본키 제약 = 고유키 & not null)
• FK (Foreign Key), 외래키: 참조 무결성 옵션 선택 가능, 여러 개 가능
• UNIQUE Key, 고유키: NULL 가능, 행을 고유하게 식별하기 위한 고유키
• NULL: 아직 정의되지 않은 값, 데이터 입력하지 못하는 경우 (알 수 없는 값, 0과 공백(“ “)은 NULL이 아님)
• NOT NULL: NULL 값 입력 금지
• CHECK: 데이터 무결성을 유지하기 위한 테이블의 특정 칼럼에 설정하는 제약, 입력할 수 있는 값의 범위 등을 제한, TRUE or FALSE 논리식을 지정

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

• [Oracle] DESCRIBE 테이블명; (간략히 DESC 테이블명;)
• [SQL Server] sp_help ‘dbo.테이블명’

5. SELECT 문장을 통한 테이블 생성 사례

• CREATE TABLE AS (CTAS)
  • CREATE TABLE AS 문장을 사용하는 경우 테이블의 구조를 복사하므로 따로 작성X
  • 기존 테이블 제약조건 중 NOT NULL 제약 조건만 새로운 테이블에 복제됨
  • 다른 제약조건(기본키, 고유키, 외래키, CHECK 등)은 복사 되지 않아서 다시 적용해야 함
  • 계약 조건 추가하기 위해 ALTER TABLE 기능 사용
  • [Oracle] CREATE TABLE 테이블명B AS SELECT * FROM 테이블명 A;

    ex) 선수(PLAYER) 테이블과 같은 내용으로 TEAM_TEMP라는 복사 테이블을 만들기

    CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;
• SQL Server에서는 Select ~ Into ~ 를 활용하여 같은 결과
  • [SQL Server] SELECT * INTO 테이블명B FROM 테이블명A;
    
SELECT * INTO TEAM_TEMP FROM TEAM; (1개 행이 영향 받음)

6. ALTER TABLE - 칼럼 추가/삭제 or 제약조건 추가/삭제

• 칼럼 추가/삭제
  • ALTER TABLE [테이블명] ADD [추가할 컬럼명][데이터타입(길이)]: 칼럼 추가
    
ex) user 테이블에 데이터타입이 varchar(30)인 adress 컬럼 추가
    
ALTER TABLE USER ADD ADRESS VARCHAR(30);
  • ALTER TABLE [테이블명] DROP [삭제할 컬럼명]: 칼럼 삭제

    ex) ALTER TABLE USER DROP ADRESS;
  • ALTER TABLE [테이블명] MODIFY [컬럼명][수정 데이터타입(수정 길이)]: 칼럼 속성 변경
    
[Oracle] ALTER TABLE 테이블명 MODIFY (칼럼명 데이터 유형 DEFAULT NOT NULL);

    [SQL Server] ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터 유형 DEFAULT NOT NULL;

    ex) ALTER TABLE USER MODIFY USER_EMIAL CHAR(30);
  • ALTER TABLE [테이블명] RENAME [기존 컬럼명] TO [수정 컬럼명]: 칼럼명 변경

    ex) 유저 테이블의 age 컬럼명을 use_age로 변경

    ALTER TABLE USER RENAME AGE TO USER_AGE;
  • RENAME 변경 전 테이블명 TO 변경후 테이블명: 테이블 명 변경

• 제약 조건 추가/삭제
  • ALTER TABLE + ADD CONSTRAINT: 제약조건 추가
  • ALTER TABLE + DROP CONSTRAINT: 제약조건 삭제

• ALTER TABLE 칼럼 변경 주의사항
  • 칼럼의 크기를 늘릴 수는 있지만 줄이기X
  • null만 있거나 행이 없는 경우에만 칼럼 폭 줄이기 가능
  • null이 있을 때는 데이터 유형(숫자, 문자) 변경 가능
  • null이 없으면 not null 제약조건 추가 가능
  • 기본값(default) 변경 작업 이후 발생하는 행 삽입에 대해서만 기본값 변경

• PK 제약조건 생성하는 DDL

• 외래키(FK) 참고사항
  • 테이블 생성 시 설정할 수 잇음
  • 외래키는 NULL값 가질 수 있음
  • 한 테이블에 여러 개 존재 가능함
  • 참조 무결성 제약을 받음

7. DROP TABLE - 테이블 제거

• ALTER TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;
  ① DROP TABLE PLAYER; → 테이블 전부 삭제, 회복 불가
  ② ALTER TABLE PLAYER DROP COLUMN ADDRESS; → 테이블 일부 칼럼 삭제, 회복 불가
• DROP TABLE 테이블명 [CASCADE CONSTRAINT]; 제약조건명;
• DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제
• CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있던 참조되는 제약도 다 삭제
• SQL Server에서는 CASCADE 옵션 존재X, 테이블 삭제 전에 참조 FK 제약조건, 참조 테이블 먼저 삭제

8. TRUNCATE TABLE - 테이블 비우기

• TRUNCATE TABLE 테이블명 DROP COLUMN 삭제할 컬럼명;
• 테이블 삭제가 아닌 해당 테이블의 모든 행 제거 후 저장공간 재사용
• 구조 자체 삭제는 DROP COLUMN 삭제할 칼럼명

9. DDL과 DML의 삭제

• DDL은 반드시 AUTO COMMIT이 일어남 → DROP, TRUNCATE 원상복구 불가
• DML은 사용자가 COMMIT 해야함 → DELETE 테이블 삭제해도 ROLLBACK으로 복구 가능

10. 참조 동작

• Automatic: 자식 삽입 시 부모 테이블에 pk가 없으면 부모 pk 생성 후 자식에 삽입
• Dependent: 자식 삽입 시 부모 테이블에 pk가 존재할 때만 자식 삽입 허용
• Cascade: 부모 삭제 시 자식 같이 삭제
• Restrict: 부모 삭제 시 자식 테이블에 pk가 없는 경우에만 부모 삭제 허용

 

제 4절 DCL - 권한 부여 및 회수

1. DCL

• 유저를 생성하거나 권한을 제어하는 명령어, 보안을 위해 필요
• GRANT: 권한 부여 (SQL → GRANT 권한 ON 오브젝트 TO 유저명;)
• REVOKE: 권한 제거 (SQL → REVOKE 권한 ON 오브젝트 TO 유저명;)

2. 권한

• SELECT, INSERT, UPDATE, DELETE, ALTER, ALL: DML 관련 권한
• REFERENCES: 지정된 테이블을 참조하는 제약조건을 생성하는 권한
• INDEX: 지정된 테이블에서 인덱스를 생성하는 권한

3. Oracle 기본 유저 종류

• SCOTT: Oracle 테스트용 샘플 유저 (디폴트 패스워드: TIGER)
• SYS: DBA ROLE을 부여받은 유저 
• SYSTEM: 데베의 모든 시스템 권한을 부여받은 DBA 유저, Oracle 설치 완료 시에 패스워드 설정

4. ROLE을 이용한 권한 부여

• 많은 데베에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE wprhd
• 권한의 집합, 권한을 일일이 부여하지 않고 ROLE로 편리하게 여러 권한을 부여
• 시스템 권한, 오브젝트 권한 모두 부여 가능
• ROLE은 유저에게 직접 부여하거나 다른 ROLE에 포함되어 유저에게 부여될 수 있음

5. Oracle에서 제공하는 ROLE 종류

• CONNECT: CREATE SESSION과 같은 로그인 권한
• RESOURCE: CREATE TABLE과 같은 오브젝트(리소스) 생성 권한

6. 유저 삭제 명령어와 권한

• DROP USER 유저명 CASCADE;
• CASCADE 옵션은 해당 유저가 생성한 오브젝트를 먼저 삭제 후 유저를 삭제