728x90
1. ED 명령어
- ED 명령어를 사용하면 바로 전에 사용했던 SQL 문을 메모장으로 열 수 있다.
- 이후 ‘ / ‘ + ENTER 실행하면 메모장으로 ED 명령어로 편집해두었던 내용이 실행된다.
2. SUB QUERY
- 메인 관심사에 해당하는 값을 찾기 위해서 SUB 부분에 해당하는 값을 알아내기 위한 QUERY
- EX) JONES 보다 급여가 많은 사람들의 사번, 이름, 급여
- 이때 MAIN 관심사는 사번, 이름, 급여
- SUB 관심사는 JONES 의 급여
- SINGLE ROW SUBQUERY : 단일 값을 출력하는 SUBQUERY
- MULTIPLE ROW SUBQUERY : 2개 이상의 값을 출력하는 SUBQUERY
SQL> SELECT EMPNO, ENAME, SAL // 메인 쿼리
2 FROM EMP
3 WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME = 'JONES') //서브 쿼리
4 ;- SUBQUERY 를 이용한 QUERY 작성 순서
- SUBQUERY 작성 → MAIN 작성 → MAIN + SUB 연결
- SUBQUERY 가 먼저 실행됨
- SUBQUERY 앞 연산자에 따라서 SUB QUERY 가 SINGLE ROW 인지 MULTIPLE ROW 인지가 결정된다
- S.R.S ⇒ S.R.O (비교 연산자)
- M.R.S ⇒ M.R.O ( IN 등)
활용하기 1)
- BLAKE 보다 늦게 입사한 사원의 이름과 입사일 출력
- SUBQUERY : SELECT HIREDATE FROM EMP WHERE ENAME = ‘BLAKE’
- MAINQUERY : SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE > (SUBQUERY)
SQL> SELECT ENAME, HIREDATE
2 FROM EMP
3 WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'BLAKE')
4 ORDER BY HIREDATE DESC;
ENAME HIREDATE
---------- --------
ADAMS 87/05/23
SCOTT 87/04/19
MILLER 82/01/23
활용하기 2)
- RESEARCH 부서의 부서번호
- 그 번에 해당하는 사번, 이름, 급여
- 1 + 2 QUERY 를 연결해서 작성
SQL> SELECT EMPNO, ENAME, SAL FROM EMP
2 WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE DNAME = 'RESEARCH');
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7566 JONES 2975
3. DB 자료형
- NUMBER : 숫자형 ⇒ NUMBER(숫자) : 4자리 숫자, NUMBER(숫자1, 숫자2) : 정수 숫자1 만큼, 소수 숫자2 만큼 저장하는 자료형
- CHAR : 고정문자 ⇒ CHAR(8) 처음부터 항상 8칸짜리 칸을 만들어두고 8 칸 안에서 데이터를 채움 : 2000 BYTE
- VARCHAR : 가변문자 ⇒ VARCHAR(8) 입력된 데이터에 따라 길이가 가변
- VARCHAR2 : VARCHAR 의 개량형 , 성능 향상 ⇒ 최대 4000 BYTE
| CHAR | VARCHAR2 |
| 처음부터 크기 고정 | 크기가 가변적 |
| 읽기가 빠름 - 크기를 처음부터 알 수 있기 때문 | 읽기가 느림 - 크기를 따로 확인해야함(가변성) |
| 공간 효율 안 좋음 - 1글자 입력해도 고정된 크기만큼 용량을 차지함 | 공간 효율 최고! - 1글자 입력하면 크기는 1글자까지만 |
4. DML : 데이터 조회, 추가, 삭제, 수정에 관한 SQL
⇒ CRUD 의 기본 SELECT, DELETE, UPDATE, INSERT
1) 데이터 추가 : INSERT INTO 테이블명 VALUES ( 행1 값, 행2 값, ——-)
- 데이터 추가 시 가장 유의해야 할 점은 기본적으로 VALUES 뒤에 나오는 값의 순서는 데이블의 컬럼 순서와 동일하다는 점이다.
- 단 INSERT INTO DEPT(LOC, DEPTNO) 이렇게 테이블명 뒤에 컬럼명의 순서를 지정하였다면, 값을 넣을 때도 해당 순서대로 넣어야한다
- INSERT 시 다른 테이블의 데이터를 그대로 가져와서 넣을 수 있다 - 복사&붙여넣기 - 단 두 테이블의 컬럼이 동일해야한다
- INSERT INTO [붙여넣기 테이블명] SELECT [컬럼] FROM [복사할 테이블명] WHERE [조건절]
// 일반적인 INSERT
SQL> INSERT INTO DEPT VALUES (50, 'IT', 'SEOUL');
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT SEOUL
10 ACCOUNTING NEW YORK
// NULL 넣었을 때 INSERT
SQL> INSERT INTO DEPT VALUES (60, 'AI', NULL); // NULL 을 넣으면 말 그대로 NULL 값이 들어간다
1 개의 행이 만들어졌습니다.
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT SEOUL
60 AI
// 데이터 하나 빼고 INSERT
SQL> INSERT INTO DEPT(LOC, DEPTNO)
2 VALUES ('BUSAN', 70);
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT SEOUL
60 AI
70 BUSAN
// SUBQUERY 사용한 INSERT
SQL> INSERT INTO COPY_EMP3
2 SELECT * FROM EMP WHERE DEPTNO = 30;
6 개의 행이 만들어졌습니다.
SQL> SELECT * FROM COPY_EMP3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
2) 데이터 수정 : UPDATE 테이블명 SET 컬럼명 = VALUE, 컬럼명 = VALUE WHERE 조건;
- WHERE 조건절은 사실상 필수! 이게 없다면 해당 테이블의 해당 칼럼명에 해당하는 VALUE 로 테이블 전체가 바뀌게 됨
SQL> UPDATE DEPT
2 SET LOC = '나주'
3 WHERE DEPTNO = 60;
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT SEOUL
60 AI 나주
3) 데이터 삭제 : DELETE (FROM) 테이블명 WHERE 조건;
- 역시나 조건절은 필수! 없으면 해당 테이블의 모든 행이 다 지워진다
- 조건절에서 IN , BETWEEN 등 사용가능
SQL> DELETE FROM DEPT
2 WHERE DEPTNO = 50;
1 행이 삭제되었습니다.
SQL> SELECT * FROM DEPT ORDER BY DEPTNO;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
60 AI 나주
70 BUSAN
SQL> DELETE DEPT
2 WHERE DEPTNO IN (60,70);
2 행이 삭제되었습니다.
SQL> SELECT * FROM DEPT ORDER BY DEPTNO;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
5. DDL : 데이터 정의어 - Data Definition Language
- 테이블의 생성, 수정, 삭제 : create, alter, drop
1) CREATE TABLE : 테이블 생성하기
- CREATE TABLE 테이블명 (COL1 자료형 , COL2 자료형 )
SQL> CREATE TABLE COPY_EMP
2 (EMPNO NUMBER,
3 ENAME VARCHAR2(20),
4 SAL NUMBER(7, 2),
5 HIREDATE DATE);
테이블이 생성되었습니다.
SQL> DESC COPY_EMP;
이름 널? 유형
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
HIREDATE DATE
SQL> INSERT INTO COPY_EMP(EMPNO, ENAME, SAL, HIREDATE)
2 VALUES(1111, '홍길동', 3000, SYSDATE);
EMPNO ENAME SAL HIREDATE
---------- -------------------- ---------- --------
1111 홍길동 3000 22/02/14
2) CREATE TABLE ~ SELECT ~
- 테이블과 내용 전체를 복사 & 붙여넣기
- CREATE TABLE [새로만들 테이블명] AS SELECT * FROM [복사할 테이블명]
- CREATE TABLE [새로만들 테이블명] AS SELECT * FROM [복사할 테이블명] WHERE [조건절] : 해당 조건에 맞는 내용만 가져옴
// CREATE SUBQUERY
SQL> CREATE TABLE COPY_EMP2
2 AS
3 SELECT * FROM EMP;
// CREATE SUBQUERY 조건절
SQL> CREATE TABLE COPY_EMP3
2 AS
3 SELECT * FROM EMP
4 WHERE DEPTNO = 30;
테이블이 생성되었습니다.
SQL> SELECT * FROM COPY_EMP3;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
활용 문제 : EMP 테이블과 동일한 구조의 EMPS 테이블 만들고 DEPTNO 가 10 인 자료 넣어두기
SQL> CREATE TABLE EMPS(EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7,2),
7 COMM NUMBER(7,2),
8 DEPTNO NUMBER(2)
9 );
SQL> INSERT INTO EMPS
2 VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981/06/09', 'YYYY/MM/DD'), 2450, NULL, 10);
SQL> INSERT INTO EMPS
2 VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('1981/11/17', 'YYYY/MM/DD'), 5000, NULL, 10)
SQL> INSERT INTO EMPS
2 VALUES (7934, 'MILLER', 'CLERK', 7782, '1982/01/23', 1300, NULL, 10)
SQL> SELECT * FROM EMPS;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
3) ALTER TABLE : 테이블 수정하기(테이블 컬럼 수정)
말이 수정이지 테이블 ‘컬럼’ 에 대한 수정, 추가 , 삭제 등 포함
- ALTER TABLE 테이블명 ADD COL1, 자료형 : 컬럼 추가
- ALTER TABLE 테이블명 MODIFY COL1 자료형 : 컬럼의 자료형 변경
- ALTER TABLE 테이블명 RENAME COLUMN [기존컬럼명] TO [바꾸고싶은 컬럼명] : 컬럼 이름 수정
- ALTER TABLE 테이블명 DROP COLUMN [컬럼명] : 컬럼명 삭제
SQL> ALTER TABLE COPY_EMP2
2 ADD HP VARCHAR2(11);
HP VARCHAR2(11)
SQL> ALTER TABLE COPY_EMP2
2 MODIFY HP VARCHAR2(15);
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
HP VARCHAR2(15)
SQL> ALTER TABLE COPY_EMP2
2 RENAME COLUMN HP TO MP;
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
MP VARCHAR2(15)
SQL> ALTER TABLE COPY_EMP2
2 DROP COLUMN MP;
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
4) DROP TABLE : 테이블 삭제하기
- DROP TABLE [테이블명]
SQL> SELECT * FROM COPY_EMP2;
SELECT * FROM COPY_EMP2
*
1행에 오류:
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다
5) SHOW RECYCLEBIN - FLASHBACK TABLE [테이블명] TO BEFORE DROP
- DB 에 존재하는 휴지통 : DROP TABLE 을 복구 가능하다
- 삭제되고 긴급하게 바로 복구하는 용도. 설정에 따라 오래 남아 있을 수 있으나 당연하게도 아래 남아있을수록 데이터가 바보가 될 확률이 높다
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
COPY_EMP2 BIN$qIjB0DluRryKTGkvkO86xQ==$0 TABLE 2022-02-14:16:42:52
SQL> FLASHBACK TABLE COPY_EMP2
2 TO BEFORE DROP;
플래시백이 완료되었습니다.
SQL> DESC COPY_EMP2;
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
6) RENAME [기존 테이블명] TO [새로운 테이블명] ⇒ DDL
- 테이블 이름 변경 : RENAME [기존 테이블명] TO [새로운 테이블명]
SQL> RENAME COPY_EMP2 TO CEMP;
테이블 이름이 변경되었습니다.
7) TRUNCATE TABLE [테이블명] ⇒ DDL
- 모든 데이터 삭제!!
- DELETE 와 차이점? : DELETE 는 모든 ROW 를 삭제하는 것이고, TRUNCATE 는 존재 자체를 삭제시키는 것
- 얘는 DDL 이기 때문에 명령어 실행 후 자동으로 COMMIT 하고, 이 때문에 ROLLBACK 가 불가능하다.
8) 테이블 주석 달기 : COMMENT ON ⇒ DDL
- COMMENT ON TABLE [테이블명] IS ‘COMMENT’
COMMENTS
----------------------------------------------------------------------------------------------------
EMP TABLE
EMPLOYEE TABLE
6. TCL : Transaction Control Language
- Transaction 트랜잭션 : 일련의 작업처리를 위한 연관된 DML 모음
- 예를 들어 A 계좌에서 B 계좌로 1000원을 임금한다면
- update 계좌A set 잔액 = 원금-1000 where 계좌번호 = ‘1111’ && update 계좌B set 잔액 = 원금 +1000 where 계좌번호 = ‘2222’ ⇒ 이런 식의 DML 모음
- 트랜젝션은 DML 중 하나만 실행되고 취소되는 것이 아니라 트랜잭션 전체 - ALL -가 적용 -COMMIT- 되거나 -OR - 그렇지 않거나 - NOTHING , ROLLBACK -
- 즉 트랜잭션은 ALL OR NOTHING 의 성격을 갖는다. ALL - COMMIT, NOTHING - ROLLBACK
- 단순히 DML 만 사용했을 때 DB 가 해당 작업을 영구적으로 입력하지는 않음 때문에 DB에 영구적으로 반영하기 위해서는 COMMIT 을 해야할 필요가 있음
- DB는 기본적으로 COMMIT 한 정보에 대해서만 정상적으로 읽어온다.
SQL> UPDATE EMP SET SAL = 100
WHERE EMPNO = 7788 AND ENAME = 'SCOTT';
SQL> COMMIT; // COMMIT 해야 적용완료
커밋이 완료되었습니다.
SQL> SELECT EMPNO, ENAME, SAL FROM EMP;
EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 100
- 알아두면 매우 좋다 : 중요!!
- COMMIT 안하면 아래처럼 동시에 사용하는 다른쪽에서는 추가, 삭제, 수정한 데이터가 제대로 보이지 않는다. 또한 COMMIT 하면 바로 동작하고, ROLLBACK하면 기존의 테이블 값을 돌아간다
- 그렇다면 어떻게 이런식으로 동작할까? ⇒ ROLLBACK SEGMENT(UNDO SEGMENT) 라는 영역이 존재한다. 이 영역은 DML 이 적용되기 전 테이블을 임시로 복사 - 저장해둔다. 만약 COMMIT 이 되지 않은 상태라면 테이블을 읽어올 때 이 영역의 테이블을 읽어오게 된다.
- 마찬가지로 ROLLBACK 될 때도 여기서 가져온 정보를 그대로 붙여넣기 한다고 생각하면 편하다.
2) AUTO ROLLBACK, AUTO COMMIT
- 비정상 종료 시 - EXIT 로 종료하는게 아닌 경우 - AUTO ROLLBACK
- 정상 종료 시 - EXIT 로 정상종료 - AUTO COMMIT
- DDL, DCL 사용 시 - AUTO COMMIT
'DB - SQL' 카테고리의 다른 글
| SQL 다루기(3) : VIEW, INDEX, SEQUENCE, DB사용자 계정 다루기 (0) | 2022.02.19 |
|---|---|
| SQL 다루기(2) : DB 제약 조건과 key (0) | 2022.02.18 |
| DB 기본기 익히기 (3) : JOIN, GROUP BY, ORDER BY (0) | 2022.02.12 |
| DB 기본기 익히기 (2) : SPOOL, FUNCTION, 형변환, 제어문 (0) | 2022.02.12 |
| DB 기본기 익히기 (1) : 기본 slelect (0) | 2022.02.11 |
댓글