SQL - DAY 11

BUMSOO·2024년 6월 24일

유저생성

CREATE USER insa -- user name = insa
IDENTIFIED BY oracle -- password = oracle
DEFAULT TABLESPACE users -- 기본값으로 users 테이블스페이스에 생성
TEMPORARY TABLESPACE temp -- 메모리 초과시 temp 디스크에 내려서 작업
QUOTA 10M ON users --용량은 10m  
ACCOUNT UNLOCK;

DBA DATA 파일 확인
SELECT * FROM dba_data_files;

DBA TEMP 파일 확인
SELECT * FROM dba_temp_files;

DBA VIEW 확인
SELECT * FROM dict;

권한부여

DCL (Data Control Language)

GRANT - 권한을 부여하는 명령어

REVOKE - 권한을 회수하는 명령어

create session(로그인) 시스템 권한 부여

GRANT create session TO insa;

DBA입장에서 보는 뷰

SELECT *
FROM dba_sys_privs
WHERE grantee = 'INSA';

유저 입장에서 보는 뷰

select * from user_sys_privs;

create session 시스템 권한 회수

REVOKE create session FROM insa;

SELECT 할수 있는 권한이 없기 때문에 나오는 오류

객체 권한 부여
GRANT select ON hr.employees TO insa;
insa 에게 SELECT 할수 있는 권한 부여

부여받은 권한 확인

SELECT *
FROM dba_tab_privs
where grantee = 'INSA';

객체 권한 회수
REVOKE select ON hr.employees FROM insa;

권한 재 확인

SELECT *
FROM dba_tab_privs
where grantee = 'INSA';

유저 수정

ALTER USER insa
IDENTIFIED BY oracle -- 옵션인 부분
DEFAULT TABLESPACE users -- 옵션인 부분
TEMPORARY TABLESPACE temp -- 옵션인 부분
QUOTA 10M ON users -- 옵션인 부분
QUOTA UNLIMITED ON users-- 옵션인 부분
ACCOUNT UNLOCK-- 옵션인 부분
ACCOUNT LOCK;-- 옵션인 부분

select * from dba_ts_quotas;

MAX_BYTES가 -1 인 경우는 무제한 이다.

ALTER user insa QUOTA Unlimited ON users;

INSA 유저의 용량제한이 무제한으로 변경되었다.

테이블 생성

테이블을 생성하려면 두가지 권한을 체크
1. CREATE TABLE 시스템권한
SELECT * FROM user_sys_privs;
2. 테이블을 저장할 수 있는 테이블스페이스에 대한 권한(quota값)
select * from user_ts_quotas;
3. 테이블 이름, 컬럼이름, 유저이름, 객체이름, 제약조건 이름
1) 문자로 시작
2) 문자의 길이는 1~ 30 까지 가능
3) 문자, 숫자, 특수문자(_,#,$)가능하다.
4) 대소문자는 구분하지 않습니다.
- 소문자 emp이름으로 테이블이 있는 경우 대문자 EMP 생성할 수 없다.
5) 동일한 유저가 소유한 객체이름은 중복되면 안된다.
ex) insa.emp(소유자.테이블명) 있는 경우 insa.emp(소유자.뷰명) 뷰를 생성할 수 없다.
6) 예약어는 사용할 수 없다.

시스템권한 확인
select * from user_sys_privs;

QUOTA값 확인
select * from user_ts_quotas;

create table 시스템 권한 부여

grant create table to insa;

권한 확인
select * from dba_sys_privs where grantee = 'INSA';

테이블 만들기(테이블스페이스 설정x)
create table test(id number, name varchar2(20), day date);

테이블 만들때 테이블 스페이스를 따로 설정 안해주면 디폴트로 insa 객체의 테이블 스페이스인 USERS로 설정됨

테이블 만들기(테이블스페이스 설정)
create table test1(id number, name varchar2(20), day date) tablespace users;

현재 사용자 관련 정보
select * from user_users;

현재 사용자 테이블 정보
SELECT * FROM user_tables;

컬럼타입

NUMBER(p,s) : 가변길이 숫자 타입,
p: 전체자리수, s: 소수점 자리수
VARCHAR2(20) : 가변길이 문자 타입(4000)
DATE : 날짜 타입

테이블 삭제

DROP TABLE test1 PURGE; -- PURGE는 영구히 삭제, PURGE 사용하지 않으면 다시 복원 가능

PURGE : 영구히 테이블 삭제, 복원할 수 없다.

DML (Data Manipulation Language)

INSERT(입력), UPDATE(수정), DELETE(삭제), MERGE(입력,수정,삭제)

INSERT

테이블에 새로운 행을 입력하는 SQL문
INSERT INTO 소유자.테이블(컬럼,컬럼,...) VALUES(데이터,데이터,...)

INSERT INTO insa.test(id,name,day) 
VALUES(1,'scott',to_date('2024-06-24','yyyy-mm-dd')); -- transaction 시작

쿼리문은 수행 하였지만 transaction은 commit 하지 않았기 때문에 다른 세션에서는 보이지 않는다.

COMMIT; --INSERT를 영구히 저장, transaction 종료 된다. 후에는

다른 세션에서도 조회되는것을 확인할 수 있다.

다시 INSERT 하기

INSERT INTO insa.test(id,name,day) 
VALUES(1,'james',sysdate); -- transaction 시작

ROLLBACK; --INSERT를 영구히 취소, transaction 종료.

CREATE TABLE insa.emp(id number(2),
                    name varchar2(30),
                    day date default sysdate) -- 기본값으로 sysdate를 설정
TABLESPACE users;

INSERT INTO insa.emp(id,name,day)
VALUES (1,'alan',to_date('2003-10-10','yyyy-mm-dd')); -- transaction 시작

-- day컬럼을 생략하면 기본값이 입력된다.
INSERT INTO insa.emp(id,name)
VALUES (2,'emma');

-- day 컬럼에 기본값을 주고 싶으면 default값을 입력하면 된다.
INSERT INTO insa.emp(id,name,day)
VALUES (3,'henry',default);

-- null 값으로 입력
INSERT INTO insa.emp(id,name,day)
VALUES (4, 'lisa',null);

COMMIT; -- 영구히 저장, transaction 시작 시점부터해서 commit 전까지 전부 영구히 저장
		-- transaction 종료
select * from insa.emp;

INSERT SUBQUERY(SELECT)

--CTAS
CREATE TABLE hr.emp
AS 
SELECT * FROM hr.employees WHERE 1=2; -- 테이블의 구조만 복제


desc hr.emp;
select * from hr.emp;

INSERT INTO hr.emp -- 데이터 이관
SELECT * FROM hr.employees; -- 데이터 추출 , transaction 시작

select * from hr.emp;

ROLLBACK --영구히 취소, transaction 중료.

[문제60]

hr.employees 테이블에서 관리자 사원의 employee_id, last_name,hire_date 정보를 hr.mgr 테이블에 입력해주세요.

<풀이>

INSERT INTO hr.mgr(id,name,day)
    SELECT
        employee_id,
        last_name,
        hire_date
    FROM
        hr.employees a
    WHERE
        EXISTS (
            SELECT
                1
            FROM
                hr.employees
            WHERE
                manager_id = a.employee_id
        ); --transaction 시작

UPDATE

특정한 필드값을 수정하는 SQL문
UPDATE 소유자.테이블 SET 컬럼=값, 컬럼=값 WHERE 조건;

UPDATE insa.emp
SET name = 'paul'; -- transaction 시작 , 조건을 주지 않아 전체 name을 update

SELECT * From insa.emp;

ROLLBACK; -- 영구히 취소, transaction 종료

SELECT * FROM insa.emp;

UPDATE insa.emp
SET name = 'paul'
WHERE id = 1; -- transaction 시작 , 조건 필드만 update

SELECT * FROM insa.emp;

COMMIT; -- 영구히 저장, transaction 종료

UPDATE insa.emp
SET name = 'lucy',  day= to_date('2023-12-24','yyyy-mm-dd') -- 여러 필드를 변경시 ','로 구분 
WHERE id = 4;

UPDATE insa.emp
SET day = default -- day 컬럼의 기본값으로 수정
WHERE id = 4;

UPDATE insa.emp
SET day = null -- day 컬럼의 값을 null값으로 수정
WHERE id = 4;

UPDATE SUBQUERY

INSERT INTO hr.emp(id,name) 
SELECT employee_id, last_name || ' ' || first_name
FROM hr.employees;

COMMIT;

SELECT * FROM hr.emp;

UPDATE hr.emp 	
SET name = null
WHERE id = 100;

COMMIT ;

SELECT * FROM hr.emp;

UPDATE hr.emp 
SET name = (SELECT last_name || ' ' ||first_name 
            FROM hr.employees 
            WHERE employee_id = 100) --UPDATE SUBQUERY 
WHERE id = 100;

COMMIT;

-- Correlated Subquery를 이용한 UPDATE
UPDATE hr.emp o
SET dept_id = (SELECT department_id
                FROM hr.employees
                WHERE employee_id = o.id);

DELETE

행을 삭제하는 SQL문
DELETE FROM 소유자.테이블; -- 테이블의 전체 행 삭제
DELETE FROM 소유자.테이블 WHERE 조건; -- 테이블의 조건절에 해당하는 행 삭제

DELETE FROM insa.emp; -- transaction 시작

SELECT * FROM insa.emp;

ROLLBACK; -- 영구히 취소, transaction 종료

DELETE FROM insa.emp WHERE id = 1; -- transaction 시작

SELECT * FROM insa.emp;

COMMIT; -- 영구히 저장, transaction 종료

DELETE FROM insa.emp WHERE id = 2; --transaction 시작
SELECT * FROM insa.emp;

CREATE TABLE insa.copy  
AS SELECT * FROM insa.emp; -- transaction 종료
-- DDL작업은 내부적으로 AUTO COMMIT를 수행한다.
-- 딕셔너리 테이블의 내부적으로 트랜잭션을 수행하고 있기때문에

ROLLBACK; -- 삭제된 id 2가 롤백되지 않음.DDL 문에서 자동 COMMIT을 해버림

SELECT * FROM insa.emp;

DELETE SUBQUERY

DELETE 에서도 조건절에 서브쿼리 사용이 가능하다

DELETE FROM hr.emp
WHERE employee_id in (SELECT employee_id 
                        FROM hr.employees 
                        WHERE last_name like 'K%');

[문제61]

emp 테이블에 있는 데이터 중에 job_history에 존재하는 사원들을 삭제해주세요.

DELETE FROM hr.emp a
WHERE EXISTS (SELECT 1 
                FROM hr.job_history
                WHERE employee_id = a.employee_id);

TCL (Transaction Control Language)

COMMIT : DML 작업을 영구히 저장

ROLLBACK : DML 작업을 영구히 취소

SAVEPOINT : ROLLBACK 기능을 도와주는 표시자

Transaction

논리적으로 DML을 하나로 묶어서 처리하는 작업 단위

자동 COMMIT

DDL(create,alter, drop, rename, truncate, comment)

DCL(grant, revoke)

DDL작업은 내부적으로 AUTO COMMIT를 수행한다.
딕셔너리 테이블의 내부적으로 트랜잭션을 수행하고 있기때문에

SQLPLUS 에서 exit를 수행해서 종료

SQLPLUS 에서 다른 유저로 접속할때, connect(conn) conn insa/oracle

자동 ROLLBACK

SQLPLUS를 비정상적으로 종료 (창닫기)

DML 작업을 수행하고 있는 컴퓨터가 비정상적인 종료

client - server 환경에서 네트워크 장애가 발생하는 경우

[11일차 후기]

오늘은 내가 느끼기에 그전에 배운 SQL은 꼭 DBA, DBE가 아니더라도 개발자들도 다 할 수 있는거 였다면, 오늘 배운 내용들은 오라클의 본질 DBA DBE들만이 할 수 있는 것들이었다. 그래서 더욱 낯선 명령어 들도 많았고 이해 하는데 어려움을 겪었지만, 간단히 생각해보면 DBA라는 시스템적인 부분과 USER 라는 객체적인 부분으로 나누어서 이해하면 쉬웠다. TABLESPACE라는 공간이 있는데 이 공간은 오라클에만 있는 부분이고 객체에서 테이블을 생성하면 전체 DBMS에 저장되는것 아니라 정해진 TABLESPACE에 저장 된다는 것이다. ~~단 객체 생성을 할때 디폴트로 TABLESPACE를 설정 안해주고 테이블 생성시 SYSTEM TABLESPACE에 저장되는점을 유의하자~~ TABLESPACE는 논리적인 공간이고 물리적으로는 DISC의 DATA_FILE에 저장된다. 그리고 DML문을 작성 할때는 항상 transaction를 유의해야 하는데, 중간에 DDL문을 작성시 AUTO COMMIT이 되어버린 다는 점은 주의 해야 할것 같다. 마지막으로 INSERT SUBQUERY 나 UPDATE SUBQUERY를 업무에 맞게 잘 하기 위해서는 이전에 배웠던 상관서브쿼리를 다시 한번 복습 해야겠다.

BUMSOO

이전 포스트

SQL - DAY 10

다음 포스트