SQLD 관리 구문(DML, TCL, DDL, DCL)

2022년 05월 25일 02시 17분 34초에 업로드 된 글입니다.

작성자: sue24

SQLD 범위 마지막!!

관리구문

DML

Data Manipulation Language, 데이터 조작어

데이터를 테이블에 입력 / 수정 / 삭제

INSERT

단일행

한 번에 한 행만 입력된다

INSERT INTO 테이블 [(칼럼1, 칼럼2...)] VALUES (값1, 값2...)'

칼럼명과 입력 값이 1:1 매핑이 되어야 한다

칼럼 순서는 테이블에 정의된 순서와 같을 필요는 없다

INTO절에 기술하지 않은 칼럼은 기본값인 NULL로이 된다(not null 칼럼은 오류가 남)

INSERT INTO 테이블 VALUES (값1, 값2...); 이렇게 INTO절에 칼럼명이 생략되면 모든 칼럼 값을 기술해야 한다. 널 허용이어도 무조건.(칼럼명을 지정하는 게 더 안정적임)

NULL이나 ' ' 이렇게 값 입력 가능
- 현재 사용 중인 ID에 1을 더한 값을 넣으려면?
```
INSERT INTO PLAYER(ID, NAME, TEAM_ID)
VALUES (
  (
    SELECT TO_CHAR(MAX(TO_NUMBER(ID)) + 1)
    FROM PLAYER
    ),
    '홍길동', 'K06'
);
```
서브 쿼리를 이용한 다중행

서브 쿼리의 결과를 테이블에 입력하는 것으로

서브 쿼리의 결과가 다중행이면 한 번에 여러 건을 입력할 수 있다

INTO절의 칼럼 개수 = 서브 쿼리 SELECT절 칼럼 개수
```
INSERT INTO TEAM (T_ID, REGION, TEAM, ORIG_Y, S_ID)
SELECT REPLACE(T_ID, 'K', 'A') AS T_ID, REGION, 
    REGION || '올스타' AS TEAM, 2019 AS ORIG_Y, S_ID
FROM TEAM
WHERE REGION IN ('성남', '인천');
```
상기 쿼리의 서브 쿼리는 ()안에 있지 않다!

UPDATE

UPDATE 테이블 SET 수정할 칼럼 = 수정될 새 값 [, 칼럼 = 값...] [WHERE];

where절을 사용하면 해당 행만 수정되고, 쓰지 않으면 전체 데이터가 수정된다

UPDATE PLAYER SET POSITION = 'MF' WHERE POSITION IS NULL;

SET절에 서브 쿼리를 쓰면 서브 쿼리의 결과로 값을 수정한다(NULL이 나오면 NULL)

UPDATE STADIUM A
    SET (A.DDD, A.TEL) = (SELECT X.DDD, X.TEL FROM TEAM X
                           WHERE X.TEAM_ID = A.HOMETEAM_ID)
WHERE EXISTS (SELECT 1 FROM TEAM X
              WHERE X.TEAM_ID = A.HOMETEAM_ID);

SET절의 서브 쿼리는 수정될 값을 식별하고

WHERE절의 서브 쿼리는 수정될 행을 식별한다

상기 쿼리는 TEAM 테이블을 두 번 조회해야 해서 비효율적이므로 MERGE를 사용

MERGE INTO STADIUM T
USING TEAM S ON (T.TEAM_ID = S.HOMETEAM_ID)
WHEN MATCHED THEN
UPDATE SET T.DDD = S.DDD, T.TEL = S.TEL;

DELETE

DELETE [FROM] 테이블 [WHERE];

WHERE절을 생략하면 테이블 전체 데이터를 삭제한다

창단년도가 1980년 이전인 팀에 소속된 선수를 삭제하자

DELETE PLAYER A
WHERE EXISTS (SELECT 1 FROM TEAM X
              WHERE X.TEAM_ID = A.TEAM_ID
                     AND X.ORIG_YYYY < 1980);

소속 선수가 10명 이하인 팀에 소속된 선수를 삭제하자

DELETE PLAYER
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER
                      GROUP BY TEAM_ID
                             HAVING COUNT(*) <= 10);

테이블 전체 데이터 삭제는 TRUNCATE TABLE;가 시스템 부하 적다.
DELETE는 삭제된 데이터를 로그로 저장하기 때문.
그 대신 TRUNCATE는 ROLLBACK 불가능하다(SQL서버는 롤백 가능)

DROP	TRUNCATE	DELETE
DDL	DDL(DML도 조금?)	DML
롤백 불가	롤백 불가	커밋 전, 롤백
auto commit	auto commit	사용자 commit
디스크 초기화	디스크 초기화, 최초 테이블 생성시 할당된 스토리지는 남긴다	디스크 스토리지 유지
테이블 정의까지 삭제	테이블 생성된 초기상태로 테이블 스키마 정의	데이터만 삭제

MERGE

새로운 행 입력 및 기존 행 수정을 한 번에 수행할 수 있다

MERGE INTO 타겟 테이블
USING 소스 테이블 ON (조인 조건식)
    WHEN MATCHED THEN
        UPDATE ... SET ...
    WHEN NOT MATCHED THEN
        INSERT [(칼럼1, 칼럼2...)] VALUES (값1, 값2...);

USING절에 소스 테이블 대신 서브 쿼리를 사용할 수 있다

최신 버전 오라클 및 SQL서버는 matched와 not matched 둘 중 한 쪽만 사용 가능

TCL

Transaction Control Language. 트랜잭션 제어

트랜잭션

데이터베이스의 논리적 연산단위

밀접히 관련되어 분리될 수 없는 한 개 이상의 DB 조작

한 트랜잭션에는 SQL 문장이 하나 이상 존재

분할할 수 없는 최소의 단위로 전부 적용하거나 전부 취소해야 한다

DB 응용 프로그램은 트랜잭션의 집합이라고 할 수 있다

오라클의 트랜잭션은 트랜잭션 대상이 되는 SQL문 실행시 자동으로 시작된다

트랜잭션의 특성

원자성atomicity: 정의된 연산 모두 실행 OR 모두 비실행

일관성consistency: 실행 전 DB가 올바르다면 실행 후 DB도 올바르다

고립성isolation: 실행 중 다른 트랜잭션의 영향을 받지 않는다

지속성durability: 성공적으로 수행되면 갱신된 DB는 영구적으로 저장된다

가나가 다라에게 이체를 할 때, 가나에서 인출되어 다라에게 입금이 되기 전에 다른 트랜잭션이 가나의 계좌 변경을 할 수 없다(잠금LOCKING, 원자성을 위해서)

잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만 해제할 수 있다

트랜잭션에 대한 격리성이 낮을 때 발생하는 문제

Dirty Read: 다른 트랜잭션에 의해 수정되었는데 아직 커밋되지 않은 데이터를 읽음

Non-Repeatable Read: 같은 쿼리를 두 번 수행하는 하나의 트랜잭션이 있는데, 이 트랜잭션 수행 중에 다른 트랜잭션이 값을 수정/삭제해서 두 쿼리 결과가 다르게 나타나는 경우

Phantom Read: 같은 쿼리를 두 번 수행하는 하나의 트랜잭션. 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리 수행 중 나타남

COMMIT

트랜잭션의 완료

COMMIT;

INSERT/UPDATE/DELETE로 인한 변경이 완료되었음을 DB에 알림

COMMIT / ROLLBACK 이전의 데이터 상태
- 데이터 변경을 취소해서 복구 가능
- 현재 결과를 SELECT로 확인 가능(현재 사용자라면)
- 다른 사용자는 현재 사용자가 수행한 결과를 볼 수 없음
- 변경된 행은 잠금이 설정되어 다른 사용자가 변경 불가
COMMIT 이후의 데이터 상태
- 변경된 데이터의 DB 반영
- 이전 데이터는 영원히 사라짐
- 모든 사용자가 변경된 결과를 볼 수 있음
- 관련 행의 잠금이 풀리고 다른 사용자의 행 조작이 가능해짐
SQL서버의 COMMIT

오라클과 달리 AUTO COMMIT 모드가 기본값
- AUTO COMMIT
  
  SQL서버의 기본 방식
  
  DML/DDL 수행시마다 DBMS가 트랜잭션을 컨트롤함
  
  성공시 COMMIT
  
  실패시 ROLLBACK
  
  SQL서버는 auto commit false이면 DML/DDL 수행 후 자동으로 커밋되지 않음
  
  오라클은 DDL(+DCL) 수행 후 auto commit됨(auto commit false여도!)
- 암시적 트랜잭션
  
  오라클과 같은 방식
  
  트랜잭션 시작은 DBMS가 하고(트랜잭션 대상이 되는 SQL문 실행시 자동 시작)
  
  끝은 사용자가 명시적으로 COMMIT/ROLLBACK
  
  인스턴스 단위 / 세션 단위로 설정 가능
- 명시적 트랜잭션
  
  트랜잭션 시작과 끝을 모두 사용자가 함
  
  롤백과 커밋 모두 트랜잭션의 종료
  
  BEGIN TRAN[SACTION]
  
  ROLLBACK [TRANSACTION]
  
  COMMIT [TRANSACTION]

ROLLBACK

ROLLBACK

아직 COMMIT되지 않은 변경 사항을 취소한다
관련 행의 잠금이 풀린다
SQL서버에서 롤백 쓰려면 명시적 트랜잭션이어야만 한다

BEGIN TRAN

DDL/DML

ROLLBACK;
롤백을 하면 최초의 BEGIN TRANSACTION까지 모두 롤백된다
1. BEGIN TRANS
2. DDL/DML
3. BEGIN TRANS
4. DDL/DML
5. ROLLBACK
여기서 5번 ROLLBACK을 하면 3번이 아니라 1번 이전으로 되돌아간다
PK 제약이 설정된 컬럼에 여러 세션이 동시에 같은 값을 입력하려 하면

후행 트랜잭션이 블락된다.

선행 트랜잭션을 커밋하면 후행 트랜잭션은 에러

선행 트랜잭션을 롤백하면 후행 트랜잭션은 성공
롤백 후 데이터 상태
- 데이터 변경 사항 취소
- 데이터가 트랜잭션 시작 이전 상태로 되돌려짐
- 관련 행 잠금이 풀리고 다른 사용자들의 행 조작이 가능해짐
ROLLBACK/COMMIT 효과
- 데이터 무결성
- 영구적 변경 전 확인 가능
- 논리적으로 연관된 작업을 그룹핑해 처리 가능

SAVEPOINT

롤백하면 세이브포인트까지 트랜잭션의 일부만 롤백하는 것

일부 툴은 지원하지 않음

복수의 세이브포인트를 정의할 수 있다

동일한 이름으로 여러 세이브포인트를 만들면 마지막으로 정의한 것이 유효함

SAVEPOINT SVPT1;(SQL서버는 SAVE TRANSACTION SVPT1)

ROLLBACK TO SVPT1;(SQL서버는 ROLLBACK TRANSACTION SVPT1)

트랜잭션 시작 - INSERT - SVP A - UPDATE - SVP B - DELETE한 후에
SVP A로 롤백하면 SVP B는 사라지는 것

커밋/롤백 없이 자동으로 트랜잭션 종료되는 경우(오라클)
- DDL 실행시, 그 전후 시점에 자동으로 커밋 수행
- DB를 정상적으로 접속 종료함 => 커밋
- 애플리케이션의 이상 종료로 DB(인스턴스)와의 접속 단절시 자동 롤백
SQL서버의 트랜잭션은 오토 커밋이 기본

그러나, 애플리케이션의 이상 종료로 DB와 접속 단절시

오라클과 마찬가지로 자동 롤백됨

DDL

Data Definition Language 테이블 생성/조작 관련

DB가 DDL과 DML을 처리하는 방식은 다르다. DDL은 명령어 수행과 동시에 데이터 구조가 변경되지만 DML로 변경된 것을 테이블에 영구 저장하려면 COMMIT으로 트랜잭션을 종료해야 한다(SQL서버는 기본적으로 DML에 AUTO COMMIT 적용!)

CREATE TABLE

DB의 가장 기본 객체인 테이블은 행/열 구조로 데이터를 저장한다

```
CREATE TABLE 테이블명 (
    칼럼1 데이터유형 [기본값] [NOT NULL],
  칼럼2...
);
```
기본값이 생략되면 해당 칼럼 값이 입력되지 않을 때, NULL
- 테이블 생성시 주의점
  - 테이블명은 객체를 의미하는.. 가능한 단수형
  - 다른 테이블명과 중복 불가
  - 한 테이블 내 칼럼명 중복 불가
  - DB 내 일관성 있는 칼럼 사용 권고(데이터 표준화)
  - 테이블명/칼럼명은 문자로 시작(벤더별로 길이 제한 있음)
  - 벤더별 예약어는 사용 불가
  - A-Z a-z 0-9 _ & #만 사용 가능
  - 대소문자 구분x(기본적으로 테이블명, 칼럼명은 대문자로 만들어진다)
  - DATETIME 데이터 유형은 별도로 크기를 지정하지 않는다
  - 문자 데이터 유형은 반드시 최대 길이를 표시해야 한다
  - 칼럼 사이는 ,로 구분하고 마지막 칼럼 뒤에는 찍지 않는다
  - 칼럼에 대한 제약사항은 CONSTRAINT를 이용해 추가한다
```
CREATE TABLE PLAYER (
    PLAYER_ID CHAR(7) NOT NULL, /* 문자 고정 자릿수 7자리 */
  PLAYER_NAME VARCHAR(20) NOT NULL, /* 문자 가변 자릿수 20자리 */
  BACK_NO TINYINT, /* 숫자 2자리(오라클은 NUMBER(2)) */
  CONSTRAINT PLAYER_PK PRIMARY KEY(PLAYER_ID),
  /* PLAYER_PK는 제약조건명 */
  CONSTRAINT PLAYER_FK FOREIGN KEY(TEAM_ID) 
      REFERENCES TEAM (TEAM_ID)
);
```
```
CREATE TABLE T1(C1 NUMBER);
INSERT INTO T1 VALUES(1);
COMMIT;

/* 1. server 1 */
UPDATE T1 SET C1=2 WHERE C1=1;
/* 2. server 2 */
UPDATE T1 SET C1=3 WHERE C1=2;
/* 3. server 1 */
COMMIT;
/* 4. server 2 */
UPDATE T1 SET C1=4 WHERE C1=2;
/* 5. server 1 */
UPDATE T1 sET C1=5 WHERE C1=2;
/* 6. server 2 */
ROLLBACK;
/* 7. server 1 */
COMMIT;
```
SQL서버는 UPDATE 처리 방식이 오라클과 다르다.

오라클은 UPDATE문 시작 시점 기준으로 대상을 식별한다.

UPDATE문 시작 후 조건절 값이 변하면 UPDATE문을 재시작한다.

SQL서버는 레코드 도달 시점을 기준으로 대상을 식별한다.

2 시점에 C1=2가 없어서 변경되지 않는다(SQL서버는 1을 기다렸다가 2 처리)

4 시점에는 1, 3 이후라 C1을 4로 갱신

그러나 아직 COMMIT을 수행하지 않아서

C1 = 2 레코드 LOCK이 server2에 있다

5는 LOCK이 안 풀려서 대기

6이 되서 3으로 회귀

기다리던 5 수행

7 수행

=> C1은 5
제약조건(constraint)

데이터 무결성: 사용자가 원하는 조건의 데이터만 유지하기 위해서

테이블 생성시 꼭 제약조건을 넣을 필요 없음(ALTER TABLE로 추가 가능)

칼럼 정의 마지막에 기술하는 NOT NULL처럼 칼럼 레벨 정의나

테이블 마지막에 기술하는 CONSTRAINT처럼 테이블 레벨 정의 혼용 가능
- PRIMARY KEY 기본키
  
  한 데이터를 고유하게 식별할 수 있다
  
  하나의 테이블엔 하나의 기본키
  
  기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성한다
  
  => 기본키 제약 = UNIQUE 제약 + NOT NULL 제약
  
  기본키 칼럼은 NULL 입력 불가(SQL 서버는 NOT NULL 설정해야 PK 설정 가능)
- UNIQUE 고유키
  
  행 데이터를 고유하게 식별하기 위해서
  
  NULL은 고유키 제약의 대상이 아니다(SQL 서버는 NULL도 하나만..)
- NOT NULL
  
  디폴트 상태는 모든 칼럼의 널이 허가됨
  
  NOT NULL 제약을 해야 해당 칼럼의 입력이 필수가 된다
  
  CHECK의 일부분으로 이해 가능
- CHECK
  
  입력할 수 있는 값의 범위 제한
  
  TRUE/FALSE로 평가할 수 있는 논리식
- FOREIGN KEY 외래키
  
  관계형 DB에서 테이블간 관계 정의 위해 기본키를 다른 테이블의 외래키로 복사
  
  참조 무결성 제약 옵션을(delete cascade) 외래키 지정시 선택 가능
  
  NULL 가능
생성된 테이블 구조 확인
- 오라클
  
  DESCRIBE 테이블;
  
  DESC 테이블;
- SQL 서버
  
  exec sp_help 'dbo.테이블'
SELECT문으로 테이블 생성

CTAS(Create Table ~ As Select ~)

기존 테이블 이용

칼럼별 데이터 유형을 재정의하지 않아도 됨

기존 테이블 제약조건 중 NOT NULL만 적용 가능함(기본키, 외래키 등은 안 됨)

SQL서버는 SELECT ~ INTO ~(칼럼 속성에 Identity 사용했으면 Identity까지 적용됨)

CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM;

SELECT * INTO TEAM_TEMP FROM TEAM;
CREATE INDEX 인덱스명 ON 테이블명 (인덱스 설정할 컬럼명);
DELETE/MODIFY Action(Child ==reference==> Master)

CASCADE: M 삭제시 C 같이 삭제

SET NULL: M 삭제시 C 값을 NULL으로

SET DEFAULT: M 삭제시 C는 C의 기본값으로

RESTRICT: C에 PK값이 없다면 M 삭제 허용

NO ACTION: 참조무결성을 위반하는 삭제/수정 불가

ex) on delete cascade
```
CREATE TABLE exam (
    A INTEGER PRIMARY KEY, /*이렇게만 해도 NOT NULL*/
  B INTEGER REFERENCES T(C) ON DELETE CASCADE,
  C INTEGER REFERENCES T(D) ON DELETE SET NULL
);
```
B칼럼은 T테이블의 C칼럼 값이 지워지면 같이 지워진다(행 전체가!-A,B,C 다!)

C칼럼은 T의 D칼럼 값이 지워지면 C만 NULL이 된다
INSERT Action

AUTOMATIC: M에 PK 없으면, M의 PK 생성 후 C 입력

SET NULL: M에 PK 없으면, C를 NULL로

SET DEFAULT: M에 PK 없으면, C를 기본값으로

DEPENDENT: M에 PK 있을 때만 C 입력 허용

NO ACTION: 참조무결성 위반하는 입력 불가

ALTER TABLE

ADD COLUMN
```
ALTER TABLE 테이블 ADD (
  칼럼1 데이터유형 [기본값] [NOT NULL] 
  [, 칼럼2...]
);
```
SQL서버는 ADD 다음에 () 없음

새로 추가된 칼럼은 테이블의 마지막 칼럼이 된다(위치 지정 불가)
DROP COLUMN

ALTER TABLE 테이블 DROP (칼럼1 [, 칼럼2...]);

SQL 서버는 DROP COLUMN까지 쓴다
MODIFY COLUMN

칼럼의 정의 변경(데이터 유형, 기본 값, NOT NULL)
```
ALTER TABLE 테이블 MODIFY (
  칼럼1 데이터유형 [기본값] [NOT NULL] 
  [, 칼럼2...]
);
```
SQL서버
- 여러 개 동시 수정 불가
- ALTER TABLE 테이블 ALTER COLUMN 칼럼명 유형 [NOT NULL];
- 기본값 수정은 ALTER COLUMN이 아니라 ADD CONSTRAINT
- ```
ALTER TABLE TEAM MODIFY(ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);
```

 ALTER TABLE TEAM 
     ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;
 ALTER TABLE TEAM 
     ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '-' FOR ORIG_YYYY;
 ```

유의사항

칼럼 크기를 늘릴 수는 있지만,
테이블에 데이터가 있다면 기존 데이터 훼손을 막기 위해
칼럼 크기를 줄이는 데는 제약이 있다
(number 타입은 하나라도 있으면 축소 불가)
칼럼이 null 값만 갖고 있거나 아무 행도 없으면 크기를 줄일 수 있음
null값만 가지만 데이터 유형 변경 가능
칼럼 기본값을 바꾸면 변경 후 삽입되는 행에만 영향을 미친다
null값이 없을 때만 NOT NULL 추가 가능

RENAME COLUMN
불가피할 때..
ANSI/ISO 명시하지 않음. 오라클 등 일부 DBMS 지원
ALTER TABLE 테이블 RENAME COLUMN 기존칼럼 TO 새칼럼명;
제약조건도 자동 변경된다
SQL서버는 sp_rename '기존칼럼', '새칼럼', 'COLUMN';

DROP CONSTRAINT

ALTER TABLE 테이블 DROP CONSTRAINT 제약조건명;
ADD CONSTRAINT

ALTER TABLE 테이블 ADD CONSTRAINT 제약조건명 제약조건 (칼럼);
```
ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK 
    FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);
```
외래키 참조 후에는 TEAM 테이블 없애려 하면 에러가 난다(PLAYER가 참조 중)

참조 무결성 옵션에 의해.. 무결성 제약조건 위배

TEAM의 TEAM_ID 칼럼명이 바뀌는 건 괜찮다 그 위치를 참조하는 것
- ```
ALTER TABLE TB ADD CONSTRAINT FK_1 FOREIGN KEY(A_ID)
    REFERENCES TA(A_ID) ON DELETE SET NULL;
DELETE FROM TA WHERE A_ID = 1; 
```
  TA의 A_ID는 NOT NULL이라서 TB의 외래키인 A_ID도 NOT NULL이라서
  
  DELETE문은 에러가 난다
  
  TA의 A_ID가 삭제될 때 TB의 A_ID를 NULL로 만들어야 하는데
  
  A_ID는 NOT NULL이라서..

RENAME TABLE

RENAME 기존테이블명 TO 새이름;

SQL서버는 sp-rename '기존테이블', '새테이블';

DROP TABLE

DROP TABLE 테이블 [CASCADE CONSTRAINT];

DROP은 테이블의 모든 데이터 및 구조 삭제

CASCADE CONSTRAINT는 테이블 참조 제약 조건까지 삭제

SQL서버는 캐스케이드 옵션이 ㅇ벗음(삭제 전 제약조건 먼저 삭제해야 함)

TRUNCATE TABLE

TRUNCATE TABLE 테이블;

이 문장은 DML로 볼 수도 있지만 내부처리/오토커밋 등이 있어서 DDL로 분류

테이블 자체를 삭제하지는 않지만

해당 테이블 내 모든 행을 제거해서

저장 공간을 재사용 가능하게 해제한다

트렁케이트 후 DESC/sp-help하면 테이블 정의했던 것을 볼 수 있음(DROP은 없음)

TRUNCATE와 DROP은 로그를 남기지 않는다

정상적 복구 불가능

DELETE보다 시스템 부하가 적고 더 빠르다

DCL

DATA CONTROL LANGUAGE 유저 생성, 권한 제어

유저와 권한

유저별로 특정 테이블이나 오브젝트에 대해 제한적인 접근 권한 부여

오라클

유저를 통해 DB 접속

아이디-pw로 인스턴스에 접속 => 그에 해당하는 스키마에 권한 부여

기본 제공 유저
- SCOTT: 오라클 테스트용 샘플. pw = TIGER
- SYS: 최상위 관리자 계정. DB상 모든 관리 기능 수행 가능
- SYSTEM: DBA 계정(오라클 설치할 때 pw 설정). 백업/복구 등 일부 관리 기능 제외한 모든 권한 부여
SQL서버

인스턴스에 접속하기 위해 유저 생성

인스턴스 내의 다수 DB에 연결해 작업하기 위해 유저 생성 => 로그인과 유저 매핑

특정 유저는 특정 DB 내 특정 스키마에 대해 권한 부여

SQL서버의 로그인
- MS윈도우 운영체제 인증 방식(윈도우 로그인 정보로 SQL서버 접속)
  
  윈도우 인증이라 SQL서버 인증보다 훨씬 안전
  
  트러스트된 연결(윈도우가 제공한 자격 증명을 신뢰할 수 있기 때문)
- 혼합모드(윈도우 인증/SQL인증)
  
  윈도우 인증으로도 SQL서버에 접속 가능
  
  오라클 인증처럼 사용자 id-pw로 접속
  
  SQL인증 사용하려면 강력한 암호 사용해야 함

유저 생성과 시스템 권한 부여

유저를 생성하고 DB에 접속했다고 오브젝트(테이블, 뷰, 인덱스..) 생성 가능한 것은 아님

적절한 권한(시스템 권한) 필요

권한 종류만 100여개 이상이라서 일일이 사용자에 설정하기보다는 ROLE 이용
- CREATE USER: 유저 생성 권한
  - 오라클
    
    DBA 권한을 가진 유저인 SYSTEM으로 접속하면 다른 유저에게 유저 생성 권한 부여 가능
```
GRANT CREATE USER TO SCOTT:
CONN SCOTT/TIGER;
CREATE USER SQLD IDENTIFIED BY DB2019;
```
    SYSTEM 유저가 SCOTT에게 유저 생성 권한 부여
    
    SCOTT(비번 TIGER)으로 접속
    
    SCOTT이 SQLD라는 유저 생성(비번 DB2019)
  - SQL서버
    
    유저 생성 전 로그인을 생성해야 한다
    
    로그인 생성 권한을 가진 로그인은 기본적으로 sa
    
    SQL서버의 유저는 DB마다 존재한다. 유저 생성하려면 생성할 유저가 속할 dB로 이동해서 처리한다
```
CREATE LOGIN SQLD WITH PASSWORD = 'DB2019', DEFAULT_DATABASE = AdventureWorks;
USE ADVENTUREWORKS;
GO
CREATE USER SQLD FOR LOGIN SQLD WITH DEFAULT_SCHEMA=dbo;
```
    sa로 로그인 해서 SQLD라는 로그인을 생성한다. 로그인 후 최초로 접속할 DB는 AdventureWorks
    
    AdventureWorks DB 사용
    
    로그인과 새 유저를 연결하고 스키마 부여
- CREATE SESSION
  
  SQLD 유저가 생성됐지만 아무 권한이 부여되지 않음(로그인시 오류)
  
  로그인하려면 CREATE SESSION 권한 필요
  
  GRANT CREATE SESSION TO SQLD;
- CREATE TABLE
  
  테이블 생성 권한. 이거 없이 생성하려 하면 오류
  
  GRANT CREATE TABLE TO SQLD;
  
  GRANT CREATE ANY TABLE TO 유저;: any table이 아니면 기본적으로 자기 스키마에만 테이블 생성이 가능하다
  
  GRANT UNLIMITED TABLESPACE TO 유저;: 모든 테이블스페이스에 대한 무제한 권한
  
  SQL서버는 위 문장하고 스키마에 권한 부여까지 필요
  
  GRANT CONTROL ON SCHEMA::DBO TO SQLD;
- ALTER USER 유저 QUOTA 100M ON USERS;: USERS 테이블 스페이스에 대한 100MB 한도를 유저에게 부여
  
  ALTER USER 유저 QUOTA UNLIMITED ON USERS;
- GRANT ALL ON T1 TO U1;: U1 계정에 T1 테이블에 대한 모든 권한 부여
OBJECT에 대한 권한 부여

table views sequence procedure

ALTER O O

DELETE O O SQL서버만

EXECUTE O

INDEX O

INSERT O O

REFERENCES O

SELECT O O O

UPDATE O O

SQLD가 생성한 테이블을 SCOTT가 조회하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다(SQL서버도 동일하지만 유저는 스키마에 대한 권한만을 가진다. 유저가 테이블 같은 오브젝트를 소유하는 게 아니라 스키마를 소유하는 것.)

권한을 받지 못하고 테이블을 조회하면 해당 테이블/뷰가 존재하지 않는다고 나온다

SELECT * FROM SQLD.MENU; SQLD.MENU처럼 객체를 소유한 유저 이름을 붙임

SELECT * FROM DBO.MENU; SQL 서버는 스키마 이름을 붙인다

이제 SQLD로 접속해서 SCOTT에 MENU 테이블 SELECT 권한 부여

GRANT SELECT ON MENU TO SCOTT;

SELECT만 부여했기 때문에 UPDATE, INSERT 등 불가

해당 권한을 따로따로 부여해야 한다

	table	views	sequence	procedure
ALTER	O		O
DELETE	O	O	SQL서버만
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

ROLE을 이용한 권한 부여

그런데 많은 권한을 각각 부여하기 번거로우니까 ROLE 이용

DB 관리자가 ROLE 생성 - 해당 ROLE에 각종 권한 부여 - ROLE을 다른 ROLE이나 유저에게 부여

GRANT EXECUTE ON T1 TO U1;은 불가능하다. 테이블에 대해 허용 불가.

MERGE INTO T1 USING T2 ON (조인조건식);조인조건식이 반환하는 행이 여러 개면 에러가 난다

REVOKE ALL ON T1 FROM U1;: U1에게서 T1 관련 모든 권한 회수

REVOKE R1 FROM U1;: U1에게서 R1 회수

DROP ROLE R1;: R1이라는 롤 삭제

ROLE에 시스템 권한, 오브젝트 권한 모두 부여 가능
- SQLD의 CREATE SESSION, CREATE TABLE 권한 취소하기
  
  시스템 유저로 접속
  
  REVOKE CREATE SESSION, CREATE TABLE FROM SQLD;
  
  SQL서버는 sa로 접속
```
USE ADVENTUREWORKS;
GO
REVOKE CREATE TABLE FROM SQLD;
/*얘는 로그인은 가능*/
```
- LOGIN_TABLE이라는 ROLE 생성
  
  시스템 유저로 접속
```
CREATE ROLE LOGIN_TABLE;
GRANT CREATE SESSION,
            CREATE TABLE
    TO LOGIN_TABLE;
GRANT LOGIN_TABLE TO SQLD;
```
- GRANT LOGIN_TABLE TO SQLD;
오라클이 기본적으로 제공하는 ROLE 중 많이 쓰는 CONNECT, RESOURCE
- CONNECT: CREATE SESSION
- RESOURCE: CREATE CLUSTER / INDEXTYPE / OPERATOR / PROCEDURE / SEQUENCE / TABLE / TRIGGER / TYPE
유저 삭제
- CASCADE 옵션: 해당 유저가 생성한 오브젝트 삭제 후 유저 삭제
- DROP USER SQLD CASCADE; SQLD 삭제할 때 SQLD가 만든 테이블도 삭제
SQL서버
- 보통 ROLE 생성보다 제공되는 ROLE 사용
- 오라클처럼 ROLE을 자주 사용하지 않는 대신 서버 수준 및 DB 수준 역할을 이용해서 로그인/권한을 제어한다
- 서버 수준 역할: 인스턴스 수준 작업
  - public: 모든 SQL서버 로그인은 PUBLIC 서버 역할에 속한다. 서버 보안 주체가 보안 객체에 특정 사용 권한을 부여 안 했거나 거부했을 경우, 사용자는 해당 개체에 대해 PUBLIC으로 부여된 사용 권한을 상속 받는다. 모든 사용자가 개체를 사용할 수 있도록 할 때만 PUBLIC 할당
  - bulkadmin: BULK INSERT문
  - dbcreator: DB 생성/변경/삭제/복원
  - diskadmin: 디스크 파일 관리
  - processadmin: SQL 서버의 인스턴스에서 실행 중인 프로세스 종료
  - securityadmin: 로그인 및 해당 속성 관리. 서버 및 DB 수준의 사용 권한 부여/거부/취소. 로그인 암호 재설정
  - serveradmin: 서버 차원의 구성 옵션 변경. 서버 종료
  - setupadmin: 연결된 서버 추가/제거
  - sysadmin: 서버에서 모든 작업
- DB 역할:인스턴스보다 작은 수준. DB에 존재하는 유저가 참여할 수 있는 역할
  - db_accessadmin: 윈도우 로그인, 윈도우 그룹 및 SQL서버 로그인의 DB에 대한 액세스 추가/제거
  - db_backupoperator: DB 백업
  - db_datareader: 모든 사용자 테이블의 모든 데이터 읽기
  - db_datawriter: 모든 사용자 테이블 데이터 추가/삭제/변경
  - db_ddladmin: DB 내 모든 DDL
  - db_denydatareader: DB 내 사용자 테이블 데이터 읽을 수 없음
  - db_denydatawriter: DB 내 사용자 테이블 데이터 추가/삭제/변경 불가
  - db_owner: DB 내 모든 구성 및 유지 관리 작업. DB 삭제
  - db_securityadmin: 역할 멤버 자격 수정. 사용 권한 관리. 이 역할에 보안 주체 추가시 원하지 않는 권한 상승 설정 가능

PL/SQL

Block구조: 각 기능별 모듈화 가능
여러 SQL문을 block으로 묶고 한 번에 서버로 보낼 수 있어 통신량 감소
변수 / 상수 등을 선언해서 SQL 문장간 값 교환
IF, LOOP 등 절차형 언어 사용 가능
DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능
오라클에 내장되어 있다
응용 프로그램 성능을 향상시킨다
procedure, user defined function, trigger 객체 작성 가능
프로시저 내 다른 프로시저 호출시 호출 프로시저의 트랜잭션과 별도로 자율 트랜잭션 처리 가능
프로시저 내부에 작성된 절차적 코드는 PL/SQL 엔진이
일반적인 SQL문장은 SQL실행기가 처리한다
동적 SQL/DDL문을 실행할 때 EXECUTE IMMEDIATE를 쓰고 사용해야 한다
execute immediate 'TRUNCATE TABLE EMP';

절차형 SQL 모듈

저장형 프로시저는 SQL을 로직과 함께 DB에 저장한 명령문 집합

저장 모듈(stored module)

ex) PL/SQL, LP/SQL, T-SQL
SQL문을 DB에 저장해 사용자-애플리케이션 공유할 수 있게 함
독립적으로 혹은 타 프로그램에서 실행 가능한 완전한 실행 프로그램
오라클의 저장 모듈은 procedure, user defined function, trigger

저장형 함수(사용자 정의 함수)는 단독보다는 SQL문을 통해 호출되고 결과를 리턴하는 SQL보조 역할

트리거

테이블에 INSERT, UPDATE, DELETE같은 DML 실행시 DB에서 자동 동작하게 작성.
데이터 무결성, 일관성을 위해 사용자 정의 함수 사용
DB에 의해 자동 호출 및 수행됨
TCL은 사용 불가(프로시저는 TCL 사용 가능)
DB 로그인 작업도 트리거로 사용 가능
테이블/뷰/DB 작업 대상으로 정의 가능
전체 트랜잭션으로 발생되기도 하고 각 행에 대해 발생되기도 한다(정의에 따라..)

PROCEDURE	TRIGGER
CREATE Procedure	CREATE Trigger
EXECUTE로 실행	생성 후 자동 실행
COMMIT/ROLLBACK 가능	COMMIT/ROLLBACK 불가

고객명 써야하는데 GROUP BY를 고객 ID로 했다면?
SELECT에서 MAX / MIN(고객명)하면 된다

'SQL' 카테고리의 다른 글

SQL활용-3(계층형 쿼리, PIVOT, 정규표현식) (0)	2022.05.20
SQL활용 - 2(윈도우 함수, TOP N 쿼리) (0)	2022.05.12
SQL 활용 - 1(서브 쿼리, 그룹 함수) (0)	2022.04.14
SQLD 합격! (0)	2022.04.14
SQL 기본 - 2(Group by, Order by, Join) (0)	2022.04.06

다음글이 없습니다.

이전글이 없습니다.