230417 마흔 번째 수업_SQL

과제 07.CREATE TABLE할 때: DEFAULT 값은 자료형 지정해준 다음에 맨 앞에 넣는 것이 오류 안 남!!

ALTER:

객체를 변경하는 구문

[표현식]

ALTER TABLE 테이블명 변경할내용

• 변경할 내용
  1) 컬럼 추가/수정/삭제
  2) 제약조건 추가/삭제는 삭제한 후 새로 추가해야 됨
  3) 컬럼명/제약조건명/테이블명 변경

1) 컬럼 추가/수정/삭제

1.1 컬럼의 추가(ADD): ADD 컬럼명 데이터타입 [DEFAULT 기본값]

LNAME컬럼 추가(VERCHAR2(20),기본값=한국)

ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '한국';

1.2 컬럼 수정(MODIFY):
데이터 타입 수정: MODIFY 컬럼명 바꿀데이터타입
DEFAULT 값 수정: MODIFY 컬럼명 DEFALULT 바꿀기본값

-- DEPT_COPY 테이블의 DEPT_ID의 CHAR(2) -> CHAR(3)로 변경
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);

--DEPT_COPY 테이블의 DEPT_ID의 CHAR(3) -> NUMBER로 변경
ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER; 
--데이터 유형을 변경할 열은 비어 있어야 합니다 오류남
--데이터를 모두 지워야 수정 가능

ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR2(10);
--10BYTE를 넘는 컬럼값이 있어 오류

--DEPT_TITLE을 VARCHAR2(40)로 LOCATION_ID를 VARCHAR2(2)로 LNAME의 기본값은 '미국'변경
--다중변경
ALTER TABLE DEPT_COPY
    MODIFY DEPT_TITLE VARCHAR2(40)
    MODIFY LOCATION_ID VARCHAR2(2)
    MODIFY LNAME DEFAULT '미국';

1.3 컬럼 삭제(DROP COLUMN): DROP COLUMN 삭제하고자하는컬럼명

-- 컬럼삭제는 다중삭제 안 됨
ALTER TABLE DEPT_COPY
        DROP COLUMN DEPT_TITLE
        DROP COLUMN LNAME; --SQL명령어가 올바르지 않습니다
        
CREATE TABLE DEPT_COPY2
AS SELECT *
    FROM DEPT_COPY;
    
ALTER TABLE DEPT_COPY2 DROP COLUMN LNAME;
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY2 DROP COLUMN LOCATION_ID;

ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_ID;
-->최소 한개의 컬럼은 존재해야 됨: 모든 열들을 삭제할 수 없다는 오류

2. 제약조건 추가/삭제

2.1 제약조건 추가
PRIMARY KEY: ADD PRIMARY KEY(컬럼명)
FOREIGN KEY: ADD FORIEGN KEY(컬럼명) REFERENCES 참조할테이블명[(컬럼명)]
UNIQUE: ADD UNIQUE(컬럼명)
CHECK: ADD CHECK(컬럼에대한조건)
NOT NULL: MODIFY 컬럼명 NULL|NOT NULL

제약조건명을 지정하고자 한다면 [CONSTRAINT 제약조건명] 제약조건

-- DEPT_COPY 테이블에 DEPT_ID에 PRIMARY KEY 추가
-- DEPT_COPY 테이블에 DEPT_ID에 UNIQUE 추가
-- DEPT_COPY 테이블에 LNAME에 NOT NULL, 제약조건명 추가

ALTER TABLE DEPT_COPY
    ADD CONSTRAINT DID_PK PRIMARY KEY(DEPT_ID)
    ADD CONSTRAINT DTITLE_UQ UNIQUE(DEPT_TITLE)
    MODIFY LNAME CONSTRAINT LNAME_NN NOT NULL;

2.2 제약조건 삭제: DROP CONSTRAINT 제약조건 / MODIFY 컬럼명 NULL(NOT NULL 제약조건일 경우)

ALTER TABLE DEPT_COPY
    DROP CONSTRAINT DID_PK;
    
ALTER TABLE DEPT_COPY
    DROP CONSTRAINT DTITLE_UQ
    MODIFY LNAME NULL; --NOT NULL값 수정

3. 컬럼명/제약조건명/테이블명 변경(RENAME)

3.1 컬럼명 변경: RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명

-- DEPT_TITLE=> DEPT_NAME 컬럼명 변경
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

3.2 제약조건명 변경: RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명

ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C008511 TO DID_NN;

3.3 테이블명 변경: RENAME [기존테이블명] TO 바꿀테이블명

ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;

DROP:

테이블 삭제 단, 참조되고 있는 부모테이블은 함부로 삭제 불가
-삭제하려면
--방법1. 자식테이블을 먼저 삭제한 후 부모테이블 삭제
--방법2. 부모테이블만 삭제하는데 제약조건까지 같이 삭제하는 방법

DROP TABLE 테이블명 CASCADE CONDTRAINT;

[표현법]

DROP TABLE 테이블명

DCL(DATA CONTROL LANGUAGE):

데이터 제어 언어

계정에게 시스템권한 또는 객체접근권한을 부여(GRANT) 하거나 회수(REVOKE)하는 구문

시스템 권한: DB에 접근하는 권한, 객체를 생성할 수 있는 권한
객체접근 권한: 특정 객체들을 조작할 수 있는 권한

• * 시스템권한 종류
  -CREATE SESSION: 접속할 수 있는 권한
  -CREATE TABLE: 테이블을 생성할 수 있는 권한
  -CREATE VIEW: 뷰를 생성할 수 있는 권한
  -CREATE SEQUENCE: 시퀀스를 생성할 수 있는 권한
  ...

--1. SAMPLE/1234 계정 생성
ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;

CREATE USER SAMPLE IDENTIFIED BY 1234;

--2. 접속할 권한 CREATE SESSION권한 부여
GRANT CREATE SESSION TO SAMPLE;

--3. 테이블 생성 
GRANT CREATE TABLE TO SAMPLE;

--4. TABLE SPACE할당
--ALTER USER SAMPLE QUOTA 2M ON SYSTEM;
ALTER USER SAMPLE DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

-- VIEW를 생성할 수 있는 권한
GRANT CREATE VIEW TO KH;

• 객체에 대한 접근 권한 종류
  특정 객체에 접근하여 조작할 수 있는 권한 권한종류
  SELECT TABLE, VIEW, SEQUENCE
  INSERT TABLE, VIEW
  UPDATE TABLE, VIEW
  DELETE TABLE, VIEW
  ...
  [표현식]

  GRANT 권한종류 ON 특정객체 TO 계정명;
  - GRANT 권한종류 ON 권한을 가지고 있는 USER명.특정객체 TO 권한줄USER;

-- 5. SAMPLE 계정에게 KH계정 EMPLOYEE테이블을 SELECT할 수 있는 권한
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;

--6. SAMPLE계정에게 KH계정 DEPARTMENT테이블에 INSERT할 수 있는 권한 
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;

-- 문제) SAMPLE 계정에게 KH계정 DEPARTMENT테이블에 SELECT할 권한
GRANT SELECT ON KH.DEPARTMENT TO SAMPLE;

--7. 권한 회수
-- REVOKE 회수할 권한 FROM 계정명;
REVOKE SELECT ON KH.EMPLOYEE FROM SAMPLE;
REVOKE INSERT ON KH.DEPARTMENT FROM SAMPLE;
REVOKE SELECT ON KH.DEPARTMENT FROM SAMPLE;

롤(ROLE):

특정 권한들을 하나의 집합으로 모아놓은 것

CONNECT: CREATE, SESSION
RESOURCE: CREATE TABLE, CREATE SEQUENCE, ...
DBA: 시스템 및 객체 관리에 대한 모든 권한을 갖고 있는 롤

GRANT CONNECT, RESOURCE TO 계정명;
GRANT DBA TO 계정명;

-데이터 값 변경
INSERT, UPDATE, DELETE

-테이블을 변경
ADD, ALTER, DROP

TCL(TRANSACTION CONTROL LANGUAGE):

트랜잭션 제어 언어

트랜잭션(TRANSACTION)

-데이터베이스의 논리적 연산단위
-데이터의 변경사항(DML)들을 하나의 트랜잭션에 묶어서 처리
DML문 하나를 수행할 때 트랜잭션이 존재하면 해당 트랜잭션에 같이 묶어 처리
존재하지 않으면 트랜잭션을 만들어 묶음 처리
COMMIT하기 전까지의 변경사항들을 하나의 트랜잭션에 담게 됨
-트랜잭션의 대상이 되는 SQL: INSERT, UPDATE, DELETE(DML)

• COMMIT(트랜잭션 종료 처리 후 확정); -> 진행: 한 트랜잭션에 담겨있는 변경사항들을 실제 DB에 반영시키겠다는 의미(후에 트랜잭션이 사라짐)
• ROLLBACK(트랜잭션 취소); -> 진행: 한 트랜잭션에 담겨있는 변경사항들을 삭제(취소)한 후 마지막 COMMIT시점으로 돌아감
• SAVEPOINT(임시저장) 포인트명; -> 진행: 현재 이 시점에 해당 포인트명으로 임시저장점을 정의해 두는 것

-- EMPLOYEE_COPY3테이블에서 사번 201번 지우기
DELETE FROM EMPLOYEE_COPY3
    WHERE EMP_ID = 201;
    
DELETE FROM EMPLOYEE_COPY3
    WHERE EMP_ID = 202;
    
ROLLBACK; --201번과 202번 되살아남

---

DELETE FROM EMPLOYEE_COPY3
    WHERE EMP_ID = 200;
    
SELECT * FROM EMPLOYEE_COPY3;

INSERT INTO EMPLOYEE_COPY3
    VALUES (300, '홍길동', 5000000, 600000000);
    
COMMIT;
ROLLBACK; --COMMIT한 후 부터 되살림, 그래서 살릴 것이 없음

---

-- 208, 217, 221 삭제
DELETE FROM EMPLOYEE_COPY3
    WHERE EMP_ID IN (208, 217, 221);
    
-- 임시저장점 만들기
SAVEPOINT SP;

INSERT INTO EMPLOYEE_COPY3
      VALUES(301, '아무개', 4000000, 48000000);
      
ROLLBACK TO SP;

COMMIT;

---

자동 COMMIT되는 경우

• 정상종료
• DCL과 DDL명령문이 수행된 경우

자동 ROLLBACK되는 경우

• 비정상 종료된 경우
• 전원이 OFF, 컴퓨터 DOWN

DELETE FROM EMPLOYEE_COPY3
    WHERE EMP_ID = 204;
    
INSERT INTO EMPLOYEE_COPY3
      VALUES(302, 'KH', 3000000, 36000000);
      
CREATE TABLE TEST(
    TID NUMBER
); --DDL구문 실행

ROLLBACK; --DDL구문이 실행되는 순간 COMMIT돼서 위의 데이터들 롤백 안 됨