printf("ho_tari\n");
oracle sql (4) 본문
/*
4일차
데이터 조작어(DML : Data Manipulation Language)
SQL : DBMS의 데이터를 관리하기 위해 설계된 특수목적의 프로그래밍 언어
SQL 문법의 종류
1. 데이터 정의 언어(DDL : Data Defintion Language)
- 테이블과 컬럼을 정의하는 명령어
- 테이블의 생성, 수정
- CREATE, ALTER, DROP, RENAME, TRUNCATE
2. 데이터 조작 언어(DML : Data Manipulation Language)
- 데이터베이스의 내부 데이터를 관리하기 위한 언어
- 데이터를 조회, 추가, 변경, 삭제
- SELECT, INSERT, UPDATE, DELETE
3. 데이터 제어 언어(DCL : Data Control Language)
- 데이터를 관리 목적으로 보안, 무결성, 회복, 병행제어
- GRANT, REVOKE
4. 트랜잭션 제어 언어 (TCL : Transaction Control Language)
- DCL과 비슷, 데이터를 제어하는 언어가 아닌 트랜잭션을 사용
- COMMIT, ROLLBACK, SAVEPOINT
*/
-- 테이블 생성 : DDL -> create
CREATE TABLE dept_tmp
AS SELECT * FROM dept;
SELECT * FROM dept_tmp;
-- 테이블 삭제 : DDL -> drop
DROP TABLE dept_tmp; -- 삭제 후 dept_tmp 다시 생성
SELECT * FROM dept_tmp;
/*
INSERT
- 테이블에 데이터를 추가 : DML -> insert
- insert의 기본 구조
insert into 테이블명(컬럼명1, 컬럼명2,...컬럼명n)
values (컬럼명1의 데이터, 컬럼명2의 데이터,...컬럼명n의 데이터)
*/
-- dept_tmp에 데이터 추가
INSERT INTO dept_tmp(deptno, dname, loc)
VALUES (50, 'DATABASE', 'SEOUL');
SELECT * FROM dept_tmp;
INSERT INTO dept_tmp
VALUES (60, 'NETWORK', 'BUSAN');
SELECT * FROM dept_tmp;
INSERT INTO dept_tmp(loc, dname, deptno)
VALUES ('DAEJEON', 'JAVA', 70);
SELECT * FROM dept_tmp;
-- 테이블에 NULL 입력
-- 명시적
INSERT INTO dept_tmp
VALUES (70, 'WEB', NULL);
DELETE dept_tmp WHERE dname = 'JAVA';
SELECT * FROM dept_tmp;
INSERT INTO dept_tmp
VALUES (80, 'MOBILE', '');
SELECT * FROM dept_tmp;
-- 암시적
INSERT INTO dept_tmp(deptno, loc)
VALUES (90, 'INCHEON');
SELECT * FROM dept_tmp;
-- 테이블에 날짜데이터 입력
-- 테이블 생성은 하되 행 생성 X -> 테이블의 구조 복사
CREATE TABLE emp_tmp
as SELECT * FROM emp WHERE 1 <> 1;
SELECT * FROM emp_tmp;
DESC emp_tmp;
INSERT INTO emp_tmp
VALUES (9999, '홍길동', 'PRESIDENT', NULL, '2001/01/01', 5000, 1000, 10);
SELECT * FROM emp_tmp;
INSERT INTO emp_tmp
VALUES (1111, '성춘향', 'MANAGER', NULL, '2001-01-05', 4000, NULL, 20);
SELECT * FROM emp_tmp;
-- 날짜 형식 변경 : 도구 -> 데이터베이스 -> NLS -> 닐짜형식
-- YYYY-MM-DD HH24:MI:SS
-- 날짜 데이터 형식
INSERT INTO emp_tmp
VALUES (2111, '이순신', 'MANAGER', 9999, '07/01/2001', 4000, NULL, 20);
SELECT * FROM emp_tmp;
DELETE emp_tmp WHERE empno = 2111;
INSERT INTO emp_tmp
VALUES (2111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YYYY'), 4000, NULL, 20);
SELECT * FROM emp_tmp;
INSERT INTO emp_tmp
VALUES (3111, '심청이', 'MANAGER', 9999, SYSDATE, 4000, NULL, 30);
SELECT * FROM emp_tmp;
-- 서브 쿼리를 이용한 여러 데이터 입력
-- 서브쿼리 이용한 insert문의 주의점!!
-- values 사용X
-- 테이블과 서브쿼리의 컬럼의 갯수가 일치
-- 테이블과 서브쿼리의 컬럼의 자료형 일치
INSERT INTO emp_tmp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
SELECT E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal, E.comm, E.deptno
FROM emp E, salgrade S
WHERE E.sal BETWEEN S.losal AND S.hisal
AND S.grade = 1;
SELECT * FROM emp_tmp;
/*
데이터 수정
- UPDATE
- update 기본 구조
UPDATE 변경할 테이블
SET 변경할 컬럼1 = 값1, 변경할 컬럼2 = 값2, ... 변경할 컬럼n = 값n
WHERE 데이터를 변경할 대상의 조건(필수);
*/
CREATE TABLE dept_tmp2
AS SELECT * FROM dept;
SELECT * FROM dept_tmp2;
-- 데이터 전체 수정
UPDATE dept_tmp2 SET loc = 'SEOUL';
SELECT * FROM dept_tmp2;
-- 수정 내용을 되돌리기 : TCL -> ROLLBACK
ROLLBACK;
SELECT * FROM dept_tmp2;
-- 데이터 일부만 수정
UPDATE dept_tmp2 SET dname = 'DATABASE', loc = 'SEOUL'
WHERE deptno = 40;
SELECT * FROM dept_tmp2;
-- 수정 내용 적용하기(완료) : TCL -> COMMIT
COMMIT;
SELECT * FROM dept_tmp2;
ROLLBACK;
SELECT * FROM dept_tmp2;
-- 서브쿼리를 이용한 수정
UPDATE dept_tmp2 SET (dname, loc) = (SELECT dname, loc
FROM dept
WHERE deptno = 40)
WHERE deptno = 40;
SELECT * FROM dept_tmp2;
-- 서브쿼리를 이용하여 일부분 수정
UPDATE dept_tmp2 SET dname = (SELECT dname
FROM dept
WHERE deptno = 40),
loc = (SELECT loc
FROM dept
WHERE deptno = 30)
WHERE deptno = 40;
SELECT * FROM dept_tmp2;
-- where 절에 서브쿼리를 사용
UPDATE dept_tmp2 SET loc = 'SEOUL' WHERE deptno = (SELECT deptno
FROM dept_tmp2
WHERE dname = 'OPERATIONS');
SELECT * FROM dept_tmp2;
/*
데이터 삭제
- DELETE
- 기본 구조
DELETE 테이블명
WHERE 삭제 데이터의 조건;
*/
-- 데이터 일부 삭제
CREATE TABLE emp_tmp2
AS SELECT * FROM emp;
SELECT * FROM emp_tmp2;
-- where절 사용
DELETE emp_tmp2
WHERE job = 'MANAGER';
SELECT * FROM emp_tmp2;
-- where 절에 서브쿼리를 사용
DELETE FROM emp_tmp2 WHERE empno IN (SELECT E.empno
FROM emp_tmp2 E, salgrade S
WHERE E.sal BETWEEN S.losal AND S.hisal
AND S.grade = 3
AND deptno = 30);
SELECT * FROM emp_tmp2;
-- 데이터 전체를 삭제
DELETE emp_tmp2;
SELECT * FROM emp_tmp2;
/*
데이터 정의어 : DDL(Data Definition Language)
- 주의!!! : 조작어에서는 COMMIT 또는 ROLLBACK 반영시 적용, 정의어에서는 실행과 동시에 COMMIT 됨
- 객체(테이블, 뷰, 인덱스, 스키마, 도메인)를 생성(CREATE), 변경(ALTER), 삭제(DROP)
- 생성(CREATE) : 객체(테이블, 뷰, 인덱스, 스키마, 도메인)
- 변경(ALTER) : 테이블에 대한 정의를 변경하는데 사용
- 삭제(DROP) : 객체(테이블, 뷰, 인덱스, 스키마, 도메인)
*/
/*
- 테이블 생성 : create
- 기본 구조
create table 소유계정.테이블명 (
컬럼명1 자료형;
컬럼명2 자료형;
...
컬럼명n 자료형;
)
*/
CREATE TABLE emp_ddl(
empno number(4),
enmae varchar2(10),
job varchar2(9),
mgr number(4),
hairedate DATE,
sal number(7, 2),
comm number(7, 2),
deptno number(2)
);
DESC emp_ddl;
-- 기존 테이블 컬럼구조와 데이터 복사
CREATE TABLE dept_ddl
AS SELECT * FROM dept;
DESC dept;
SELECT * FROM dept;
-- 기존 테이블 컬럼구조와 데이터 일부만 복사
CREATE TABLE emp_ddl_30
AS SELECT * FROM emp WHERE deptno = 30;
SELECT * FROM emp_ddl_30;
-- 기존 테이블 컬럼구조만 복사
CREATE TABLE empdept_ddl
AS SELECT E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal, E.comm,
D.deptno, D.dname, D.loc FROM emp E, dept D WHERE 1 <> 1;
SELECT * FROM empdept_ddl;
/*
테이블 변경 : alter
- 변경 키워드
1. ADD : 데이터 컬럼 추가
2. RENAME : 컬럼명 변경
3. MODIFY : 자료형을 변경
4. DROP : 특정 컬럼 삭제
*/
CREATE TABLE emp_alter
AS SELECT * FROM emp;
-- 1. ADD
ALTER TABLE emp_alter
ADD hp varchar2(20);
SELECT * FROM emp_alter;
-- 2. RENAME
ALTER TABLE emp_alter
RENAME COLUMN hp TO TEL;
SELECT * FROM emp_alter;
-- 3. MODIFY
DESC emp_alter;
ALTER TABLE emp_alter
MODIFY empno number(5);
DESC emp_alter;
-- 4. DROP
ALTER TABLE emp_alter
DROP COLUMN tel;
SELECT * FROM emp_alter;
-- 테이블의 데이터 삭제 : truncate -> ROLLBACK 불가
CREATE TABLE emp_trunc
AS SELECT * FROM emp;
SELECT * FROM emp_trunc;
DELETE emp_trunc;
SELECT * FROM emp_trunc;
ROLLBACK;
SELECT * FROM emp_trunc;
TRUNCATE TABLE emp_trunc;
SELECT * FROM emp_trunc;
ROLLBACK;
SELECT * FROM emp_trunc;
-- 테이블 삭제 : DROP
DROP TABLE emp_trunc;
DESC emp_trunc;
/*
객체
인덱스
- select 성능을 향상시키기 위해 사용하는 물리적 저장 구조
*/
-- 오라클에서 자동으로 생성된 인덱스
-- scott 계정이 소유한 인덱스 정보
SELECT * FROM user_indexes;
-- scott 계정이 소유한 인덱스 컬럼 정보
SELECT * FROM user_ind_columns;
/*
인덱스 생성
-- 사용자가 직접 인덱스를 생성
-- 기본 구조
create index 인덱스이름
ON 테이블명(컬럼명1 ASC 또는 DESC,
컬럼명2 ASC 또는 DESC,
....
컬럼명n ASC 또는 DESC,);
*/
-- emp 테이블에 sal 컬럼의 인덱스 생성
SELECT * FROM emp;
CREATE INDEX idx_emp_sal
ON emp(sal);
-- 생성된 인덱스 확인
SELECT * FROM user_ind_columns;
SELECT * FROM emp WHERE sal > '0';
-- 인덱스 삭제
DROP INDEX idx_emp_sal;
SELECT * FROM user_ind_columns;
-- index 사용 예제
-- 테이블 생성
CREATE TABLE emp3 (
no NUMBER,
name VARCHAR2(10),
salary NUMBER);
-- 데이터 삽입
INSERT INTO emp3 VALUES (1, '강호동', 200);
INSERT INTO emp3 VALUES (2, '우재석', 300);
INSERT INTO emp3 VALUES (3, '이경규', 100);
INSERT INTO emp3 VALUES (4, '김구라', 150);
INSERT INTO emp3 VALUES (5, '박나래', 100);
INSERT INTO emp3 VALUES (6, '양세찬', 250);
-- 데이터 조회
SELECT * FROM emp3;
-- emp3 테이블의 name컬럼을 사용하는 idx_name 인덱스 생성
CREATE INDEX idx_name ON emp3(name);
-- emp3 테이블의 인덱스 컬럼 조회
SELECT * FROM user_ind_columns;
-- 인덱스 테이블명은 반드시 대문자로 조회
SELECT * FROM user_ind_columns WHERE table_name = 'EMP3';
-- 테이블 조회
select * from emp3;
-- 인덱스 사용하지 않고 조회
SELECT * FROM emp3 order by name;
-- 인덱스 사용
SELECT * from emp3 where name > '0';
/*
뷰(가상 테이블)
- 테이블처럼 사용하는 것
- 쿼리(select)를 저장한 객체
- 실제로 테이블 존재하지 않음
- 이유
1. 보안관리
- 보안등급에 다라 컬럼의 범위를 정하고 권한을 부여
- 연산결과만 제공하고 싶을 때 : 수식이나 알고리즘을 숨길 때
- 원본 스키마를 숨길 때
2. 사용 편리
- 검색 조건이 많은 쿼리를 단순화
- JOIN, 서브쿼리 단순화
- 생성
create view 뷰명
as 쿼리문;
*/
/*
-- view 생성 권한 : grant
cmd
C:\Users\bigdata>sqlplus system/system
SQL> grant create view to scott;
Grant succeeded.
SQL>
*/
-- 뷰 생성
CREATE VIEW emp_v
AS SELECT * FROM emp WHERE deptno = 20;
-- 뷰 확인
SELECT * FROM user_views;
-- 뷰 조회
SELECT * FROM emp_v;
-- 뷰 수정 : ALTER 사용 X
CREATE OR REPLACE VIEW emp_v
AS
SELECT ename, job, deptno
FROM emp
WHERE deptno = 30;
/*
- 읽기 전용 뷰
CREATE VIEW 뷰명
AS
쿼리문
SELECT ename, job, deptno
FROM emp
WHERE deptno = 30
WITH READ ONLY;
*/
-- 뷰 삭제
DROP VIEW emp_v;
SELECT * FROM user_views;
-- 사용예
-- 서브쿼리
SELECT * FROM (SELECT empno, ename, job, deptno
FROM emp
WHERE deptno = 20);
-- 뷰 생성
CREATE VIEW emp_20
AS SELECT empno, ename, job, deptno FROM emp WHERE deptno = 20;
SELECT * FROM emp_20;
-- 뷰 삭제
DROP VIEW emp_20;
SELECT * FROM user_views;
-- 뷰 수정 : ALTER 사용 X
CREATE OR REPLACE VIEW emp_20
AS
SELECT ename, job, sal
FROM emp
WHERE sal >= 2500;
SELECT * FROM emp_20;
/*
인라인 뷰
- SQL문에서 일회성으로 만들어 사용하는 뷰
- select 문에서 사용되는 서브쿼리
- with절의 select 문
*/
-- rownum 사용 : pseudo column(의사컬럼)
SELECT ROWNUM, E.* FROM emp E;
-- sal 기준 정렬 : rownum도 같이 정렬이 된다(rownum앞의 숫자와 비교)
SELECT ROWNUM, E.* FROM emp E ORDER BY sal DESC;
-- 인라인뷰를 서브쿼리에 사용 : 정렬 결과를 순번을 부여
SELECT ROWNUM, E.* FROM (SELECT *
FROM emp E
ORDER BY sal DESC)E;
-- 인라인뷰를 with절에 사용
WITH E AS (SELECT * FROM emp ORDER BY sal DESC)
SELECT ROWNUM, E.* FROM E;
-- 인라인뷰로 top-n 출력(서브쿼리 이용)
SELECT ROWNUM, E.* FROM (SELECT *
FROM emp E
ORDER BY sal DESC)E
WHERE ROWNUM <= 5;
-- 인라인뷰로 top-n 출력(with이용)
WITH E AS (SELECT * FROM emp ORDER BY sal DESC)
SELECT ROWNUM, E.* FROM E
WHERE ROWNUM <= 5;
/*
시퀀스
- 규칙에 따른 순번 생성
- 생성
create sequence 시퀀스명
[increment by n] -- 번호의 증가값
[start with n] -- 시작값
[maxvalue n | nomaxvalue n] -- 최대값
[minvalue n | nominvalue n] -- 최소값
[cycle | nocycle] -- 다시 시작
[cache | nocache] -- 생성할 번호 미리 지정
*/
-- dept 구조 복사 -> dept_seq
CREATE TABLE dept_seq
AS SELECT *
FROM dept
WHERE 1 <> 1;
SELECT * FROM dept_seq;
-- 시퀀스 생성
CREATE SEQUENCE seq_dept_seq
INCREMENT BY 10
START WITH 10
MAXVALUE 30
MINVALUE 0
NOCYCLE
CACHE 2;
SELECT * FROM user_sequences;
-- 시퀀스 사용
-- insert : nextval 사용
INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'DATABASE', 'SEOUL');
INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'WEB', 'INCEON');
INSERT INTO dept_seq (deptno, dname, loc)
VALUES (seq_dept_seq.NEXTVAL, 'MOILE', 'DAEJEON');
SELECT * FROM dept_seq;
-- 마지막 생성 시퀀스 확인
SELECT seq_dept_seq.CURRVAL FROM dual;
/*
- 시퀀스 수정
- 생성
alter sequence 시퀀스명
[increment by n] -- 번호의 증가값
[maxvalue n | nomaxvalue n] -- 최대값
[minvalue n | nominvalue n] -- 최소값
[cycle | nocycle] -- 다시 시작
[cache | nocache] -- 생성할 번호 미리 지정
*/
-- 수정
alter SEQUENCE seq_dept_seq
INCREMENT BY 3
MAXVALUE 99
CYCLE;
SELECT * FROM user_sequences;
-- 시퀀스 삭제
DROP SEQUENCE seq_dept_seq;
SELECT * FROM user_sequences;
/*
트랜잭션
- 하나의 단위로 데이터를 처리
- COMMIT : 트랜잭션 반영
- ROLLBACK : 트랜잭션 취소
- insert 문 참조
*/
/*
세션
- 활동 시간이나 기간
- DB 접속시간 ~ 종료까지의 시간
- 주의할 점!!!
1. 읽기 일관성 : DB는 여러곳에서 동시 다발적으로 접속 -> 데이터를 관리, 사용
2. 수정중인 데이터 잡근 잠금 : lock
2-1 : 일부 데이터를 수정, 삭제 할 경우 해당 데이터만 lock상태로 처리
-> 해당 데이터를 제외한 다른 행의 데이터만 사용
-> COMMIT 이후에 해당 데이터를 사용
2-2 : 전체 데이터를 수정, 삭제 할 경우 테이블 전체 행에 대해 lock상태로 처리
-> 모든 데이터 사용 X
-> COMMIT 이후에 해당 데이터를 사용
2-3 : 모든 lock 상태라 할지라도 insert는 가능
*/
'SQL' 카테고리의 다른 글
| oracle sql (example problem) (0) | 2023.09.02 |
|---|---|
| oracle sql (5) (0) | 2023.09.02 |
| oracle sql (3) (0) | 2023.09.02 |
| oracle sql (2) (0) | 2023.09.02 |
| oracle sql (1) (0) | 2023.09.02 |