[SQL] DBA가 수행하는 SQL

📌 SQL의 종류 5가지

1. Query : select 문의 6가지절, 조인, 서브쿼리
2. DML문(Data Manipulation Language): insert, update, delete, merge, select
3. TCL문(Transaction Control Language) : commit, rollback, savepoint
4. DDL문(Data Definition Language): create, alter, drop, truncate, rename
5. DCL문(Data Contorl Language) : grant, revoke

---

✔️ 예제1. savepoint 이해하기

: insert나 update나 delete를 하는 중간중간 rollback 할 지점을 지정하는 것

select * from emp;

update emp
 set comm = 0;

-- savepoint a 지정
savepoint a;

update emp
 set sal = 0;
 
-- savepoint b 지정
savepoint b;

delete from emp;

select * from emp;

-- emp 테이블을 delete 하기 전 상태로 돌아감
rollback to b;

select * from emp;

---

✔️ 예제2. rename 명령어 이해하기

: 오라클 테이블의 이름을 변경하는 명령어

emp 테이블의 이름을 emp1000으로 변경

rename emp to emp1000; 

select * from emp1000;

문제1. 다시 emp1000 을 emp 로 이름을 변경하시오

rename emp1000 to emp;

select * from emp;

---

✔️ 예제3. alter 명령어 이해하기

1. emp 테이블에 email 이라는 컬럼을 추가하시오

alter table emp
  add email varchar2(50); 
  
select * from emp; 

2. emp 테이블의 sal 컬럼을 삭제하시오

alter table emp
 drop column sal;
 
select * from emp;

💡 column이 삭제되면
--> rollback도 안되고 flashback도 안됨

3. emp 테이블의 직업의 컬럼 길이를 varchar2(50) 으로 늘리시오

alter table emp
modify job varchar2(50);

desc emp;

문제1. 사원 테이블에 address 컬럼을 varchar2(50)으로 해서 추가하시오

alter table emp
 add address varchar2(50);

문제2. 다시 address 컬럼을 삭제하시오

alter table emp
 drop column address;

---

✔️ 예제4. grant 명령어로 권한 부여하기

c##smith라는 유져를 생성하시오. 패스워드는 tiger로 하시오

-- c##smith 유저 생성
create user c##smith
 identified by tiger; 
 
grant connect to c##smith;

c##scott 유저

-- c##smith에게 emp 테이블에 대한 권한 부여
grant select on emp to c##smith;

-- synonym 만듦
create public synonym emp
for c##scott.emp;

-- emp 테이블 조회권한 회수
revoke select on emp from c##smith;

c##smith 유저

-- user 조회
show user;

-- scott 유저의 emp 테이블이 조회됨
-- synonym 만들기 전이라 c##scott.emp라고 꼭 써줘야됨
select * from c##scott.emp;

-- scott 유저에서 synonym 만든 후
-- emp만 써도 조회됨
select * from emp;

---

🔸 권한 부여 (GRANT)

grant select on 테이블명 to 유저명;

🔸 권한 회수 (REVOKE)

revoke select on 테이블명 from 유저명;

---

문제. c##scott 유져가 c##smith 유져에게 c##scott 유져가 가지고 있는 테이블들을 모두 조회할 수 있는 권한을 부여하고 synonym 도 만들어주세요.

-- 가지고 있는 모든 table 조회
select table_name from user_tables;

select 'grant select on ' || table_name || ' to c##smith;'
 from user_tables;

-- c##smith에게 권한 부여
grant select on EMP21 to c##smith;
grant select on TELECOM_TABLE to c##smith;
grant select on ORDER2 to c##smith;
grant select on CRIME_TIME to c##smith;
grant select on EMP2 to c##smith;
grant select on SALGRADE to c##smith;
...

select 'create public synonym ' || table_name || ' for c##scott.emp;'
 from user_tables;

-- synonym 만듦
create public synonym EMP21 for c##scott.emp;
create public synonym TELECOM_TABLE for c##scott.emp;
create public synonym ORDER2 for c##scott.emp;
create public synonym CRIME_TIME for c##scott.emp;
create public synonym EMP2 for c##scott.emp;
create public synonym SALGRADE for c##scott.emp;
...