✿∘˚˳°∘°
22일차 : ALTER, DROP / TCL / OBJECT 본문
2022/12/27
1. ALTER / DROP
: CREATE로 정의된 내용을 수정할 때 사용, 컬럼/제약조건 추가삭제, 컬럼자료형변경 등등
-- 실습을 위한 테이블 생성 및 INSERT
CREATE TABLE USER_TBL(
USER_ID VARCHAR2(20) PRIMARY KEY,
USER_PW VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(20)
);
INSERT INTO USER_TBL VALUES('user01', '1234', '유저1');
-- 테이블 컬럼 추가하기
-- 테이블에 컬럼을 추가(USER_ADDR 컬럼추가)
ALTER TABLE USER_TBL
ADD (USER_ADDR VARCHAR2(100));
-- 컬럼을 추가하면 이미존재하는 회원은 값이 없기 때문에 기본적으로 NULL로 세팅
ALTER TABLE USER_TBL
ADD (USER_AGE NUMBER DEFAULT 0);
-- DEFAULT값을 지정해주어 이미존재하는 회원의 AGE값에 DEFAULT값으로 세팅
-- 테이블 컬럼 수정하기
-- 테이블의 컬럼을 수정(USER_ADDR VARCHAR2(100) -> VARCHAR2(200))
ALTER TABLE USER_TBL MODIFY USER_ADDR VARCHAR2(200);
DESC USER_TBL; -- 컬럼에 대한 간략한 정보 조회
-- 테이블 컬럼 삭제하기
-- 테이블의 컬럼 삭제(USER_AGE 컬럼 삭제)
ALTER TABLE USER_TBL DROP COLUMN USER_AGE;
-- 제약조건 확인을 위한 쿼리문
SELECT UC.CONSTRAINT_NAME,
UC.CONSTRAINT_TYPE,
UCC.COLUMN_NAME,
UC.SEARCH_CONDITION
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.TABLE_NAME = 'USER_TBL';
-- 컬럼에 제약조건 추가
-- 컬럼에 제약조건을 추가(USER_ADDR에 NOT NULL)
ALTER TABLE USER_TBL
MODIFY USER_ADDR CONSTRAINT USER_ADDR_NN NOT NULL;
-- 이미 USER_ADDR에 NULL이 들어가있기 때문에 에러발생
-- 제약조건에 맞게 컬럼상태를 맞춰줘야함
-- (UNIQUE : 중복데이터X / NOT NULL : NULL이없어야함 등)
-- NOT NULL은 MODIFY로 추가
UPDATE USER_TBL SET USER_ADDR = '서울시 영등포구';
-- USER_TBL USER_PHONE 컬럼추가 자료형CHAR(13)
ALTER TABLE USER_TBL ADD (USER_PHONE CHAR(13));
-- 컬럼에 제약조건을 추가(USER_PHONE UNIQUE)
-- NOT NULL을 제외한 나머지 제약조건은 ADD로 추가
ALTER TABLE USER_TBL
ADD CONSTRAINT USER_PHONE_UNQ UNIQUE(USER_PHONE);
-- 제약조건 삭제
-- 제약조건 삭제(NOT NULL : MODIFY / 나머지 : DROP)
-- NOT NULL 제약조건 삭제
ALTER TABLE USER_TBL MODIFY USER_ADDR NULL;
-- UNIQUE, PK, FK 제약조건삭제
ALTER TABLE USER_TBL DROP CONSTRAINT USER_PHONE_UNQ;
-- 제약조건 설정 시 이름을 알고 있어야함.
-- 제약조건 수정 : 지우고 다시 설정(업데이트X)
-- 컬럼 / 제약조건 / 테이블 이름변경
DESC USER_TBL;
-- 컬럼이름 변경(USER_PHONE -> PHONE_NUMBER)
ALTER TABLE USER_TBL
RENAME COLUMN USER_PHONE TO PHONE_NUMBER;
-- 제약조건이름 변경
ALTER TABLE USER_TBL
RENAME CONSTRAINT SYS_C007123 TO USER_ID_PK;
DESC ALTER_TEST;
-- 테이블이름 변경
ALTER TABLE USER_TBL
RENAME TO ALTER_TEST;
-- COLUMN도 CONSTRAINT도 안붙이면 TABLE
-- 테이블 삭제
-- 테이블 삭제
DROP TABLE ALTER_TEST;
-- 어제 실습한 MEMBER_TBL 삭제하기
DROP TABLE MEMBER_TBL CASCADE CONSTRAINT;
-- MEMBER_TBL이 삭제가 안되는 이유 : 외래키옵션과 같은상황(제약조건이 이상해지니까)
-- MEMBER_TBL을 지우려면 얘를 참조하고있는 자식테이블을 먼저 삭제해야함
-- 귀찮으면 CASCADE CONSTRAINT 추가(MEMBER_TAB을 참조하고있던 제약조건도 모두삭제)
-- MEMBER_TBL을 참조하고 있는 테이블이 있는 경우 삭제 불가능
-- CASCADE CONSTRAINT 옵션으로 삭제하면 연결되어있는 제약조건을 삭제하고 테이블삭제
2. TCL
TRANSACTION : 한꺼번에 수행되어야 할 최소의 작업단위
TCL : 트랜잭션 제어 언어 / 트랜잭션 작업을 적용, 취소할 때 사용하는 SQL
COMMIT / ROLLBACK / SAVEPOINT / ROLLBACK TO
-- 트랜잭션은 데이터의 변화에 따라서면 적용/취소
-- DDL은 트랙잭션과 상관없음
-- DML(INSERT(ROW추가) / UPDATE(FILED변경) / DELETE(ROW삭제))만 트랜잭션에 영향
-- SELECT는 DML에 속하긴 하지만 테이블의 데이터변화가 없음 - 트랜잭션에서 예외
CREATE TABLE USER_TBL(
USER_ID VARCHAR2(20) PRIMARY KEY,
USER_PW VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(20) NOT NULL
);
-- 트랜잭션은 데이터의 변화에 따라서면 적용/취소
-- DDL은 트랙잭션과 상관없음
-- DML(INSERT(ROW추가) / UPDATE(FILED변경) / DELETE(ROW삭제))만 트랜잭션에 영향
-- SELECT는 DML에 속하긴 하지만 테이블의 데이터변화가 없음 - 트랜잭션에서 예외
INSERT INTO USER_TBL VALUES('user01', '1234', '유저1');
INSERT INTO USER_TBL VALUES('user02', '1358', '유저2');
INSERT INTO USER_TBL VALUES('user03', '2468', '유저3');
-- 지금 수행한 INSERT 3번을 하나의 트랜잭션으로 가정
-- (트랜잭션은 INSERT-UPDATE일수도잇고 내마음대로)
-- 아직 반영을 하지않음
-- 지금까지 발생한 변경사항을 저장 : COMMIT(트랜잭션을 적용하겠다.)
COMMIT;
INSERT INTO USER_TBL VALUES('user04', '4444', '유저4');
INSERT INTO USER_TBL VALUES('user05', '5555', '유저5');
-- 6,7번을 하다가 실수를 할 것같으면 굳이 5로 돌아갈 필요가없음(6부터다시작업)
-- SAVEPOINT 임시저장지점 생성
SAVEPOINT SP1;
INSERT INTO USER_TBL VALUES('user06', '6666', '유저6');
INSERT INTO USER_TBL VALUES('user07', '7777', '유저7');
ROLLBACK TO SP1; -- SAVEPOINT로 이동
COMMIT; -- 현재시점으로 적용하면서 저장된 세이브포인트 모두삭제
SELECT * FROM USER_TBL;
-- 유저1,2,3은 커밋완료 / 유저 4,5는 커밋미완료
ROLLBACK; --가장 최근에 COMMIT한 시점으로 되돌림 - 123만 있을때로 되돌아감
[ 짝궁(다른사람)의 DB에 접속해서 실습해보기 ]

다른 사람의 DB에 접속하여 테이블조회 시 INSERT를 하더라도 COMMIT이 되기 전까지의 테이블데이만 조회되는 것을 확인할 수 있음. INSERT한 데이터를 확인하려면 반드시 COMMIT을 해야함
또, 다른사람이 INSERT를 하고 COMMIT을 하지않았을 경우 내가 INSERT를 하면 에러가 발생하는 모습 확인가능.
3. OBJECT
3 - 1 ). VIEW
SELECT 쿼리의 실행 결과를 화면에 저장한 논리적인 가상테이블 복사된 테이블과 비슷하지만, 사용면에서 차이점존재.
GRANT CREATE VIEW TO kh;
-- view생성권한은 resource에 없으므로 생성을 위해서는 추가로 권한 부여
-- 아래 쿼리문을 사용하여 테이블(복사)과 VIEW를 생성
-- 이 조회결과를 이용해서 VIEW생성, 테이블생성
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;
-- 테이블 생성
-- 테이블만들기
CREATE TABLE EMP_COPY
AS
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;
-- VIEW 생성
-- VIEW만들기
CREATE VIEW EMP_VIEW
AS
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;
-- 권한을 설정해 주지 않을 경우 에러 발생
-- insufficient privileges : 권한X에러
-- 복사된 TABLE과 VIEW의 가장 큰 차이점 : 데이터의 상호작용
-- TABLE에서 멤버의 데이터 변경(UPDATE)
SELECT * FROM EMP_COPY;
UPDATE EMP_COPY SET SALARY = 2000000 WHERE EMP_NAME = '방명수';
COMMIT;
-- VIEW에서 멤버의 데이터 변경(UPDATE)
UPDATE EMP_VIEW SET SALARY = 2500000 WHERE EMP_NAME = '방명수';
-- 테이블생성시 독립적으로 사용이 가능하지만, VIEW경우 원본테이블과 연관이있다
-- EMPLOYEE테이블(원본테이블)에서는 UPDATE를 하지않았음에도 값이 변경됨
-- VIEW는 원본데이터에서 제한적인 데이터를 제공하기 위해서 사용(서로데이터를 반영할때)
-- 단순한 구조에서는 VIEW와 원본테이블과의 처리가 원할하게 이루어짐
-- 단, DML명령어 조작이 불가능한 경우가 있음
3 - 2 ) SEQUENCE
순차적으로 정수 값을 자동으로 생성하는 객체로, 자동번호 발생기의 역할
(시작번호와 그다음값과의 간격도 지정해줄 수 있다.)
-- 시퀀스의 생성과 사용법
CREATE SEQUENCE TEST_SEQ
START WITH 100 -- 시퀀스 시작값은 100
INCREMENT BY 10 -- 증가폭은 10
MAXVALUE 200 -- 시퀀스 최대값은 200
NOCYCLE; -- 반복없음(최대값이 도달하면 끝)
/*
시퀀스 사용법
시퀀스이름.CURRVAL : 현재 시퀀스값을 반환
시퀀스이름.NEXTVAL : 다음 시퀀스값을 반환 - INCREMENT만큼 MAX까지 계속 증가
MAX에 도달하고 NOCYCLE옵션이면 NEXTVAL을 했을 때 에러발생 : 최대값에 도달했어~
*/
CREATE SEQUENCE MEMBER_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE;
CREATE TABLE MEMBER_TBL_SEQ(
MEMBER_NO NUMBER PRIMARY KEY,
MEMBER_NAME VARCHAR2(20) NOT NULL,
MEMBER_AGE NUMBER
);
-- 원래방법 : 이전회원의 번호를 확인하고 그 다음번호로 입력
-- 계속 확인을 해줘야해서 굉장히 불편하다.
INSERT INTO MEMBER_TBL_SEQ VALUES(1, '회원1', 20);
INSERT INTO MEMBER_TBL_SEQ VALUES(2, '회원2', 22);
-- 시퀀스를 이용한 방법 : 이전번호를 확인하지않아도 알아서 INCREMENT만큼 증가
-- 시퀀스명.NEXTVAL
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원1', 20);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원2', 22);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원3', 30);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL,
'회원'||MEMBER_SEQ.CURRVAL, 20+MEMBER_SEQ.CURRVAL);
-- '회원'||MEMBER_SEQ.CURRVAL : 현재시퀀스값을 연결연산자를 통해 붙여 회원N이됨
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원16', 36);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL,NULL, 37); -- 실패
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원17', 37);
-- INSERT가 실패했다고 시퀀스가 돌아가지 않음
-- NEXTVAL이 돌면 일단 1이 올라가는것, 그렇기 때문에 실패 후 다시 INSERT하면
-- 17이 아닌 18이 출력되는것
ALTER SEQUENCE MEMBER_SEQ INCREMENT BY 10; -- 시퀀스 수정
-- 다시 .NEXTVAL을 하면 10이 증가되어있음
-- 다른건 모두 변경가능하지만 START는 변경불가
-- START를 변경하고 싶으면 DROP후 재생성 해야한다.
3 - 3 ) INDEX
SQL명령문의 처리속도 향상을 위한 명령문
CREATE INDEX EMP_IDX
ON
EMPLOYEE(EMP_NAME, EMP_NO, HIRE_DATE);
-- EMPLOYEE 테이블에서 EMP_NAME, EMP_NO, HIRE_DATE를 자주 사용할 경우(변경도적음)
-- 인덱스로 묶어놓음
-- 인덱스에 지정을 해놓으면 조회 시 인덱스에 지정된 애들은 인덱스에서 조회
-- 인덱스에 지정되지않은 컬럼은 테이블에서 조회
-- 현재로서는 확인할 수 없지만 데이터가 방대해질경우 처리속도에 차이가 생긴다.
DROP INDEX EMP_IDX; -- INDEX삭제
3 - 4 ) SYNONYM : 동의어
-- 비공개동의어 : 생성한 사용자계정에서만 사용가능
-- 관리자 계정에서 권한부여
GRANT CREATE SYNONYM TO kh; -- SYNONYM(동의어) 생성을 위한 권한 부여
-- kh계정에서 동의어 생성
CREATE SYNONYM EMP FOR EMPLOYEE;
-- EMPLOYEE를 EMP라고 부를게 라는 의미
SELECT * FROM EMPLOYEE;
SELECT * FROM EMP; -- 동의어로도 조회가 가능하다
-- 공개 동의어 : 모든 사용자가 사용가능(관리자계정에서 생성)
-- 비공개동의어 생성 / 비공개동의어는 관리자계정에서 생성
CREATE PUBLIC SYNONYM DEPT FOR KH.DEPARTMENT;
-- kh계정에서 동의어로 쿼리문 작성시 조회가능
SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT;
또다른 계정에서 공개동의어를 사용해 주고 싶으면 조회권한을 주면된다.
-- 관리자계정에서 권한부여
-- test02계정이 KH.DEPARTMENT 테이블을 조회할 수 있게 해주는 권한
GRANT SELECT ON KH.DEPARTMENT TO test02;
-- test02계정에서 다른계정.테이블 / 공개동의어로 조회가능
SELECT * FROM KH.DEPARTMENT;
SELECT * FROM DEPT;
-- 관리자 계정에서 공개동의어 생성되었으므로 동의어로도 조회가능
단, emp는 kh가 만든 동의어이기때문에 tset02에서는 조회불가
(EMPLOYEE테이블 또한 관리자 계정에서 조회권한을 줘야한다)
-- test02계정에서 EMPLOYEE 조회
SELECT * FROM KH.EMPLOYEE;
SELECT * FROM EMP; --단, emp는 kh가 만든 동의어이기때문에 tset02에서는 조회불가
-- 동의어 삭제
-- 동의어삭제는 해당동의어를 생성한 계정에서 이루어져야한다.
-- kh 계정에서 삭제
DROP SYNONYM EMP;
-- 관리자계정에서 삭제
DROP PUBLIC SYNONYM DEPT;
-- 오늘의 실습문제 --
< 관리자계정 >
CREATE USER mytest IDENTIFIED BY 1234;
GRANT CONNECT, RESOURCE TO mytest;
GRANT CREATE VIEW TO mytest;
GRANT CREATE SYNONYM TO mytest;
< 사용자계정 >
CREATE TABLE MEMBER(
MEMBER_NO NUMBER PRIMARY KEY,
MEMBER_USERID VARCHAR2(20) NOT NULL UNIQUE,
MEMBER_USERPWD CHAR(20) NOT NULL,
MEMBER_NICNAME VARCHAR2(20) NOT NULL ,
MEMBER_GENDER VARCHAR2(3) NOT NULL CHECK(MEMBER_GENDER IN('남', '여')),
MEMBER_EMAIL VARCHAR2(30) NOT NULL
);
CREATE SEQUENCE MEMBER_SEQ
START WITH 1000
INCREMENT BY 1
MAXVALUE 1000000
NOCYCLE;
CREATE TABLE BOARD(
BOARD_NO NUMBER PRIMARY KEY,
BOARD_TITLE VARCHAR2(20) NOT NULL,
BOARD_CONTENT VARCHAR2(200) NOT NULL,
BOARD_WRITEDATE DATE NOT NULL,
BOARD_NICNAME VARCHAR2(20) NOT NULL
REFERENCES MEMBER(MEMBER_NICNAME)ON DELETE CASCADE
);
CREATE SEQUENCE BOARD_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 500000
NOCYCLE;
SELECT * FROM MEMBER;
INSERT INTO MEMBER
VALUES(MEMBER_SEQ.NEXTVAL, 'user01', '1111', '일호', '남', 'aaa@naver.com');
INSERT INTO MEMBER
VALUES(MEMBER_SEQ.NEXTVAL, 'user02', '2222', '이호', '여', 'bbb@naver.com');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '1번제목', '내용이없어요', SYSDATE, '이호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '2번제목', '내용이있어요', SYSDATE, '일호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '2번제목', '내용이있어요', SYSDATE, '이호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '이건타이틀', '안뇽안뇽', SYSDATE, '이호');
SELECT * FROM BOARD;
-- VIEW 만들기
CREATE VIEW MEMBER_VIEW
AS
SELECT MEMBER_NO, MEMBER_NICNAME, MEMBER_EMAIL, BOARD_TITLE
FROM MEMBER
JOIN BOARD ON(MEMBER_NICNAME = BOARD_NICNAME);
SELECT * FROM MEMBER_VIEW;
-- 동의어 만들기
CREATE SYNONYM M FOR MEMBER;
CREATE SYNONYM B FOR BOARD;
SELECT * FROM M;
SELECT * FROM B;
'국비수업 > DB' 카테고리의 다른 글
24일차 : PL/SQL / DB모델링 (0) | 2022.12.29 |
---|---|
23일차 : PL/SQL (0) | 2022.12.28 |
21일차 : 서브쿼리 / DML / DDL (0) | 2022.12.26 |
20일차 : GROUP BY / JOIN (0) | 2022.12.23 |
19일차 : 함수 (0) | 2022.12.22 |