SQL 11일차

한희수·2023년 3월 28일
sql
0

빅데이터 분석 SQL

목록 보기
11/17

20230328 SQL

<차례>
❍ 유저 삭제
❍ 유저 생성
❍ 패스워드 만료
❍ TABLE

  • 테이블을 생성하려면 두 가지를 체크해야 함
    ❍ 테이블 이름, 컬럼 이름, 유저 이름, 다른 객체이름, 제약조건 이름
    ❍ 컬럼 타입
    ❍ 테이블 생성
    ❍ 테이블 삭제(영구 삭제, 복원 불가)
    ■ DML(Data Manipulation Language)
  • INSERT
  • UPDATE
  • DELETE
  • MERGE
    ■ TCL(Transaction Control Language)
  • COMMIT : DML작업을 영구히 데이터베이스에 저장
  • ROLLBACK : DML작업을 영구히 데이터베이스에서 취소
  • SAVEPOINT : ROLLBACK 기능을 도와주는 표시자
    ● Transaction : 논리적으로 DML을 하나로 묶어서 처리하는 작업 단위
    ❍ INSERT
    ● INSERT 수행시 default 값을 입력하는 방법
    ❍ UPDATE
    ❍ DELETE문
    ■ 테이블 복제 CTAS
    ❍ INSERT SUBQUERY
    ❍ UPDATE SUBQUERY
    ★correlated subquery를 이용한 update(많이 사용함)
    ❍ DELETE SUBQUERY
    ■ 자동 COMMIT 발생할 때 (정말 주의주의주의 해야 함)
    ■ 자동 ROLLBACK 발생할 때
    [문제89] 새로운 유저를 생성해주세요. (sys계정에서 수행)
    유저이름: ora
    비밀번호: oracle
    default tablespace: users
    temporary tablespace: temp
    users tablespace quota: 1m

(정보 확인)
SELECTFROM dba_users;
SELECTFROM dba_data_files;
SELECT*FROM dba_temp_files;

SELECTFROM user_users; => 내꺼 내가 보겠다.
SELECTFROM user_tables;
SELECTFROM user_objects;
SELECTFROM user_views;
SELECTFROM user_indexes;
SELECTFROM user_ts_quotas;

❍ 유저 삭제
DROP USER ora CASCADE; => 트리거성, 이벤트성 프로그램

❍ 유저 생성
CREATE USER ora
IDENTIFIED BY oracle => 위 두 줄 필수
DEFAULT TABLESPACE users => 옵션이지만 보통 작성함
TEMPORARY TABLESPACE temp => 메모리에서 다 못하면 내려가는 디스크
QUOTA 1M ON USERS;
--PASSWORD EXPIRE;

[문제90] ORA 유저에게 create session, create table 시스템 권한을 부여해 주세요. (sys계정에서 수행)

GRANT create session, create table TO ORA;
SELECT*FROM dba_sys_privs WHERE grantee = ‘ORA’;

[문제91] ORA 유저가 사용할 수 있는 users tablespace quota 값을 unlimited로 수정해 주세요. (sys계정에서 수행)

SELECTFROM dba_ts_quotas WHERE username = ‘ORA’;
ALTER USER ora
QUOTA UNLIMITED ON users;
SELECTFROM dba_ts_quotas WHERE username = ‘ORA’;

[문제92] HR유저 employees 테이블의 select 권한을 ORA 유저에게 부여 하세요. (hr계정에서 수행)

SELECTFROM user_tab_privs;
GRANT select ON hr.employees TO ora;
SELECTFROM user_tab_privs;

❍ 패스워드 만료
ALTER USER ora PASSWORD EXPIRE;

SELECT*FROM dba_users;

● SQL> conn ora/oracle => Run SQL Command Line

SQL> conn ora/oracle
ERROR:
ORA-28001: the password has expired

Changing password for ora
New password:

● C:\Users\ITWILL>sqlplus ora/oracle => 명령 프롬프트

SQL*Plus: Release 11.2.0.2.0 Production on 화 3월 28 10:26:16 2023
Copyright (c) 1982, 2014, Oracle. All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: ora
Enter password:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

❍ TABLE

  • 테이블을 생성하려면 두 가지를 체크해야 함
  1. 테이블을 생성할 수 있는 권한
    CREATE TABLE 시스템권한

SELECT*FROM session_privs; => 권한 확인1

SELECT privilege FROM user_sys_privs => 권한 확인2(동일)
UNION
SELECT privilege FROM role_sys_privs;

  1. 테이블을 저장할 수 있는 테이블스페이스 권한
    SELECT*FROM user_ts_quotas;
    또는
    UNLIMITED TABLESPACE 시스템권한
    => db에 생성되어 있는 모든 테이블스페이스를 사용할 수 있는 권한(보안상 함부로 줄 수X)

GRANT UNLIMITED TABLESPACE TO HR;
REVOKE UNLIMITED TABLESPACE FROM HR;

❍ 테이블 이름, 컬럼 이름, 유저 이름, 다른 객체이름, 제약조건 이름

  • 문자로 시작해야 함
  • 문자의 길이는 1 ~ 30자 가능
  • 문자 뒤에 문자, 숫자, 특수문자(_, #, $)만 사용 가능
  • 대소문자는 구분하지 않음(소문자 emp 이름의 테이블 있는 경우 대문자 EMP 생성X)
  • 동일한 유저가 소유한 객체이름은 중복되면 안 됨
  • 예약어는 사용할 수 있음(예약어 예: REVOKE, GRANT, CREATE, TABLE, 그룹함수(SUM))

❍ 컬럼 타입

  • number(p,s) : 가변길이 숫자 타입, p : 전체자리수, s : 소수점 자리수
  • varchar2(4000) : 가변길이 문자 타입
    => 4000 bytes: 영문자 기준 4000문자(확인: SELECT*FROM nls_database_parameters;)
  • char(2000) : 고정길이 문자 타입(예: 결혼/비혼)
  • date : 날짜 타입
  • clob : 가변길이 문자 타입, maximum 4gbyte
  • blob : 가변길이 이진 데이터 타입, 4gbyte
  • bfile : 외부파일에 저장된 이전 데이터 타입, 4gbyte

❍ 테이블 생성
CREATE TABLE emp( => 테이블 이름은 전체 내에서 고유해야 함
id number(4), => 컬럼 이름은 테이블 내에서 고유해야 함
name varchar2(30),
day date default sysdate)
TABLESPACE users;

=> SELECT*FROM user_ts_quotas; 테이블스페이스 확인

desc emp;

SELECT*FROM user_tables;

❍ 테이블 삭제(영구 삭제, 복원 불가)
DROP TABLE emp PURGE; => 휴지통 기능X 영구히 삭제 기능

SELECT*FROM user_tables;

■ DML(Data Manipulation Language)

  • INSERT
  • UPDATE
  • DELETE
  • MERGE

■ TCL(Transaction Control Language)

  • COMMIT : DML작업을 영구히 데이터베이스에 저장

  • ROLLBACK : DML작업을 영구히 데이터베이스에서 취소

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

  • => commit, rollback 사용은 필수, rollback 도와주는 기능

● Transaction : 논리적으로 DML을 하나로 묶어서 처리하는 작업 단위
(실제 예시: 카드쓰기 inser -> update 된 것임!)

❍ INSERT

  • 테이블에 새로운 행을 입력하는 SQL문

INSERT INTO 소유자.테이블 이름(컬럼, 컬럼, 컬럼,,,,)
VALUES(데이터, 데이터, 데이터,,,,)

=> 본인 테이블 아닌 경우 소유자 표현 필수
desc emp

insert into hr.emp(id, name, day)
values(1, '홍길동',to_date('2023-3-28','yyyy-mm-dd')); => transaction 시작

SELECT*FROM hr.emp;

 ▽▽▽(결과창)

ID NAME DAY
1 홍길동 23/03/28

Run SQL Command Line에서 확인시,
============= ==============
SQL> conn hr/1234
Connected.
SQL> SELECT*FROM hr.emp;

no rows selected
============= ==============
=> 영구히 저장된 것이 아니기 때문; transaction 마무리해야 함

COMMIT; => transaction 시작 시점까지 저장, transaction 종료

Run SQL Command Line에서 재확인시,
============= ==============
SQL> /

    ID NAME                                                         DAY
     1 홍길동                                                       23/03/28

============= ==============

DESC EMP;

INSERT INTO hr.emp(id, name, day)
VALUES(2,'박찬호',to_date('2020/12/20','yyyy/mm/dd')); => transaction 시작

INSERT INTO hr.emp(id, name, day)
VALUES(3,'윤건',to_date('2010-10-10', 'yyyy-mm-dd'));

SELECT*FROM hr.emp; => 미리보기

ROLLBACK; => transaction 시작 시점까지 전부 취소
transaction 종료

SELECT*FROM hr.emp;

DESC emp;

● INSERT 수행시 default 값을 입력하는 방법

1)
INSERT INTO hr.emp(id,name)
VALUES(2,'나얼');

SELECT*FROM hr.emp;

 ▽▽▽(결과창)

1 홍길동 23/03/28
2 나얼 23/03/28 => 그래도 디폴트(sysdate)값 등록됨

2)
INSERT INTO hr.emp(id,name,day)
VALUES(3, ‘이문세’, default);

SELECT*FROM hr.emp;

3) name 컬럼에 default 값이 선언되어 있지 않은 경우 null값으로 출력됨
INSERT INTO hr.emp(id,name,day)
VALUES(4,default,default);

SELECT*FROM hr.emp;

1 홍길동 23/03/28
2 나얼 23/03/28
3 이문세 23/03/28
4 (null) 23/03/28
4) day 컬럼에 default 값이 선언되어 있더라도 null을 수행하면 null값으로 입력됨
INSERT INTO hr.emp(id,name,day)
VALUES(5,'제임스',null);

SELECT*FROM hr.emp;

1 홍길동 23/03/28
2 나얼 23/03/28
3 이문세 23/03/28
4 (null) 23/03/28
5 제임스 (null)

=> 여기서 ROLLBACK 할시 나얼(transaction 시작)부터 끝까지 취소됨

ROLLBACK;
SELECT*FROM hr.emp;

❍ UPDATE

  • 특정한 필드값을 수정하는 SQL문

UPDATE 소유자,테이블
SET 컬럼 – 새로운 값, 컬럼 – 새로운 값,..
WHERE 조건;

UPDATE hr.emp
SET id = 2; => transaction 시작 지점

SELECT*FROM hr.emp; => 미리보기

ROLLBACK; => transaction 시작 시점까지 전부 취소
transaction 종료

SELECT*FROM hr.emp; => 다시 확인

SELECT*FROM dba_data_files;

▽▽▽(결과에 이런 테이블스페이스 있음)

TABLESPACE_NAME
UNDOTBS1 => ROLLBACK시 이전 데이터 가져올 수 있도록 여기에 저장
● UNDO TABLESPACE 역할

  • DML 작업시에 이전값을 UNDO 공간에 저장을 해놓음(transaction 진행중)
  1. ROLLBACK
  2. 읽기 일관성(RUN SQL COMMAND LINE 화면에서는 나타나지 않음)

UPDATE hr.emp
SET id = 2, day = to_date('2023-03-28', 'yyyy-mm-dd')
WHERE id = 5; =>transaction 시작

SELECTFROM hr.emp; => 미리보기
COMMIT; => transaction 시작 지점까지 영구히 저장, transaction 종료
SELECTFROM hr.emp;

  • day 컬럼의 값을 default 값으로 수정
    UPDATE hr.emp
    SET day = null
    WHERE id = 2;

SELECT*FROM hr.emp;

ROLLBACK;

❍ DELETE문

  • 행을 삭제하는 SQL문

DELETE FROM 소유자, 테이블; => 테이블 전체 행 삭제
DELETE FROM 소유자, 테이블 WHERE 조건; => 테이블의 조건절에 해당하는 행만 삭제

DELETE FROM hr.emp; => transaction 시작 시점
SELECT*FROM hr.emp;
ROLLBACK; => transaction 시작 시점까지 취소, transaction 종료
DELETE FROM hr.emp;

DELETE FROM hr.emp WHERE id = 2;
COMMIT;

SQL> /

    ID NAME                                                         DAY
     1 홍길동                                                       23/03/28

■ 테이블 복제 CTAS

  • 테이블 구조, 행(데이터), 제약조건 중에서 NOT NULL 제약조건만 복제 됨
    DROP TABLE hr.emp PURGE;

(예시1)
CREATE TABLE hr.emp
AS
SELECT*FROM hr.employees;

DESC hr.emp;
▽▽▽ 결과창
이름 널? 유형

EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SELECT*FROM hr.emp;

DROP TABLE hr.emp PURGE;

(예시2)
CREATE TABLE hr.emp
AS
SELECT employee_id as id, last_name||' '||first_name name, salary sal, department_id dept_id
FROM hr.employees;

desc hr.emp;

이름 널? 유형

ID NUMBER(6)
NAME VARCHAR2(46)
SAL NUMBER(8,2)
DEPT_ID NUMBER(4)

CREATE TABLE hr.emp
AS
SELECT employee_id as id, lower(last_name||' '||first_name) as name, salary*12 as sal, department_id as dept_id
FROM hr.employees;

● 테이블 생성시에 tablespace를 지정하지 않으면 유저 생성시 DEFAULT TABLESPACE로 지정된 tablespace에 저장됨(절대 system에 저장되면 안 됨 건드리면 안 됨)
=> SELECT*FROM user_users; 여기서 확인

desc hr.emp;
SELECT*FROM hr.emp;

CREATE TABLE hr.emp
TABLESPACE users
AS
SELECT employee_id as id, lower(last_name||' '||first_name) as name, salary*12 as sal, department_id as dept_id
FROM hr.employees;

(예시3)
CREATE TABLE hr.emp
AS
SELECT *
FROM hr.employees
WHERE 1 = 2; => 아무 false 값 입력: 테이블 구조만 복제, 데이터는 가져오지 않음

desc hr.emp;
SELECT*FROM hr.emp;

❍ INSERT SUBQUERY
SELECT*FROM hr.emp; => 뼈대 있음

SELECT*FROM hr.employees; => 이걸 hr.emp로 행들만 옮기고 싶음(이행)

INSERT INTO hr.emp
SELECT*FROM hr.employees;
다른 테이블 복제하고자 할 때,
1) 테이블이 없다면 = CTAS
2) 테이블이 있다면 = insert subquery 사용 (INSERT INTO)

● sys password 잊어버린 경우
1) sql developer로 접속 불가
2) run command line 창에서 conn / as sysdba 엔터!
3) alter user sys identified by 1234; (비밀번호 수정!)

● insa 화면을 처음 켰다!
1) 시스템 권한을 확인
SELECT*FROM session_privs;
▽▽▽(결과창)
PRIVILEGE
CREATE SESSION

2) 테이블 생성 원할시, 1)과 함께 테이블스페이스 확인
SELECT*FROM user_ts_quotas;

3) 테이블 생성 해 봄(권한 없음
CREATE TABLE insa.test(id number);
▽▽▽(결과창)
오류 보고 -
ORA-01031: insufficient privileges
01031. 00000 - "insufficient privileges“

4) sys에서 insa에게 테이블 만들기 권한 부여
GRANT CREATE TABLE TO insa; => sys에서 작성

5) insa에서 테이블 만들기
CREATE TABLE insa.test(id number)TABLESPACE users; => 저장될 스페이스 작성

6) 테이블 확인하기
SELECT*FROM user_tables;

  ▽▽▽(결과창)

TEST USERS VALID 10 1 255 655 ... ...

[문제93] insa 유저는 hr.employees 테이블을 복제해 주세요.(CTAS 사용)

  1. 테이블 생성할 수 있는 권한 확인, CREATE TABLE 시스템권한 확인
    SELECT*FROM session_privs;

  2. 테이블스페이스 사용할 수 있는 권한 확인, default tablespace 확인
    SELECTFROM user_ts_quotas;
    SELECT    FROM user_users;

  3. subquery에서 사용해야 할 테이블의 권한 확인
    SELECT*FROM user_tab_privs;

[insa]
CREATE TABLE insa.employees
AS
SELECT*FROM hr.employees;

  ▽▽▽(결과창)

오류 보고 -
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"

=> insa가 hr에 접속할 권한이 없음

[dba-sys]
GRANT SELECT ON hr.employees TO insa;

SELECT*FROM dba_tab_privs WHERE grantee = 'insa';

[insa]
SELECT*FROM user_tab_privs;

CREATE TABLE insa.employees
AS
SELECT*FROM hr.employees;

SELECT*FROM insa.employees; => 테이블 보임

[문제94] mgr 테이블 생성해주세요.
컬럼 데이터타입

id number(2)
name varchar2(30)
day date

CREATE TABLE mgr(
id number(2),
name varchar2(30),
day date default sysdate)
TABLESPACE users;

desc insa.mgr;

  • 테이블의 구조를 수정
    ALTER TABLE insa.mgr MODIFY id number(3); => 사원 번호 3자리임

[문제95] hr.employees 테이블에 있는 데이터 중에 관리자 사원들의 employee_id, last_name, hire_date를 insa.mgr 테이블의 데이터를 이행(insert subquery)해 주세요.

INSERT INTO insa.mgr(id,name,day)
SELECT employee_id, last_name, hire_date
FROM hr.employees e
WHERE EXISTS (SELECT 'x'
FROM employees
WHERE manager_id = e.employee_id);

COMMIT;
SELECT*FROM insa.mgr;

((update subquery 연습용 테이블 생성))
CREATE TABLE hr.emp(
id number,
name varchar2(60),
dept_id number,
dept_name varchar2(30))
TABLESPACE users;

desc hr.emp

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

commit;

  ▽▽▽(결과창)

ID NAME DEPT_ID DEPT_NAME
100 King Steven (null) (null)
101 Kochhar Neena (null) (null)

❍ UPDATE SUBQUERY

UPDATE hr.emp
SET name = null
WHERE id = 100; => 100번 사원의 이름 null로 변경

commit;

SELECT*FROM hr.emp;

SELECT last_name||' '||first_name FROM hr.employees WHERE employee_id = 100;

UPDATE hr.emp
SET name = (SELECT last_name||' '||first_name FROM hr.employees WHERE employee_id = 100); => 전체 사원 정보 수정됨

SELECT*FROM hr.emp;

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

SELECT*FROM hr.emp;

● 부서아이디를 hr에서 데이터 가져오기
SELECT department_id FROM hr.employees WHERE employee_id = 100;

UPDATE hr.emp
SET dept_id = 90
WHERE id = 102;

★correlated subquery를 이용한 update(많이 사용함)

UPDATE hr.emp e
SET dept_id = (SELECT department_id
FROM employees
WHERE employee_id = e.id)

SELECT*FROM hr.emp;

[문제96] hr.emp 테이블의 dept_name 컬럼의 값을 hr.departments 테이블의 department_name의 값을 이용해서 수정해주세요.

UPDATE hr.emp e
SET dept_name = (SELECT department_name
FROM departments
WHERE department_id = e.dept_id);

SELECT*FROM hr.emp;
commit;

❍ DELETE SUBQUERY

SELECT *
FROM hr.emp
WHERE id IN (SELECT employee_id
FROM hr.employees
WHERE hire_date < to_date('2003/01/01','yyyy/mm/dd'));

SELECT *
FROM hr.emp e
WHERE EXISTS (SELECT 'x'
FROM hr.employees
WHERE hire_date < to_date('2003/01/01','yyyy/mm/dd')
AND employee_id = e.id);

=> 2001, 2002년 입사자 행 확인

DELETE FROM hr.emp
WHERE id IN (SELECT employee_id
FROM hr.employees
WHERE hire_date < to_date('2003/01/01','yyyy/mm/dd'));

DELETE FROM hr.emp e
WHERE EXISTS (SELECT 'x'
FROM hr.employees
WHERE hire_date < to_date('2003/01/01','yyyy/mm/dd')
AND employee_id = e.id); => 내가 한 것, 이거 맞음

DELETE FROM hr.emp e
WHERE EXISTS (SELECT employee_id => 의미 없음, 문제 되지 않음
FROM hr.employees
WHERE hire_date < to_date('2003/01/01','yyyy/mm/dd')
AND employee_id = e.id);

=> 2001, 2002년 입사자 행 삭제(8개 행 삭제)

ROLLBACK;

  • job_id를 한 번이라도 바꾼 사원들
    SELECT *
    FROM hr.emp e
    WHERE EXISTS (SELECT 'x'
    FROM job_history
    WHERE employee_id = e.id);

DELETE FROM hr.emp e
WHERE EXISTS(SELECT 'x'
FROM job_history
WHERE employee_id = e.id);

ROLLBACK;

■ 자동 COMMIT 발생할 때 (정말 주의주의주의 해야 함)

  • DDL(CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT) -- 자동 commit 가짐
  • DCL(GRANT, REVOKE) -- 자동 commit 가짐
    => DML과 DDL, DCL은 같은 창(SESSION)에서 수행하면 안 됨
  • sqlplus에서 exit(정상적인 종료)를 수행해서 종료하면 자동 commit을 가짐
  • sqlplus에서 conn를 수행할 때 자동 commit을 가짐
    => 창 종료시 커밋, 롤백하는 습관 가지기 권장

SELECT * FROM hr.emp; => 107개 행 있다~
DELETE FROM hr.emp; -- transaction 시작

CREATE TABLE hr.test(id number); -- CREATE에는 COMMIT이 내부에 숨겨져 있음
ROLLBACK; (AUTO-COMMIT)
SELECT * FROM hr.emp; => 107개 행 없다!

● 테이블 생성시 딕셔너리 테이블들에 영향, 그러므로 auto commit 실행 됨
SELECT FROM user_tables;
SELECT FROM user_objects;
SELECT * FROM user_ts_quotas;

■ 자동 ROLLBACK 발생할 때

  • sqlplus 비정상적인 종료할 때(창 닫기 ‘X’)
  • DML 작업을 수행하고 있는 컴퓨터 비정상적인 종료 발생시
  • CLIENT – SERVER 환경에서 NETWORK 장애 발생시(네트워크 오류)
profile
한희수
이전 포스트

SQL 10일차

다음 포스트

SQL 12일차

0개의 댓글