Oracle DB

[Oracle DB] SQL - QUERY(SELECT) / DML(UPDATE, INSERT, DELETE) / DDL(CREATE, ALTER, DROP)

소댓 2023. 2. 24. 17:52

* SQL문의 6가지 절

 

SELECT COL1, COL2, *, SAL*12(연산), ||, ALIAS, FUNCTION, DISTINCT, ‘문자열’

FROM TAB1, TAB2, TAB3 …

WHERE CONDITION (> 조건 : 꼭 연산자가 와야 함)

1.     비교연산자

2.     BETWEEN 작은값 AND 큰값

3.     IN

4.     LIKE

5.     IS NULL

GROUP BY COL1, COL2, …. > 두 값이 모두 동일한 애들끼리 소그룹 생성

                                                    Ex. 시간별 제품 판매량 / 제품별 판매량

HAVING CONDITION (> 조건 : 연산자 포함)

                                      마찬가지로 1~5

                                      But, WHERE : ROW에 대한 제한

                                        HAVING: GROUP에 대한 제한

ORDER BY COL1 ASC (>생략하면 자동 ASC), COL2 ASC

                             DESC                                               DESC

                          >> COL이 2개 오면, 앞의 기준으로 1차 정렬,  그 다음에 같은 기준 간에는 뒤의 기준으로 2차 정렬

 

                     ALIAS

                    POSITION (> 몇 번째 열 기준으로 정렬하라)

 

 

 

* SQL

 

- QUERY 역할 : SELECT

 > 존재하는 데이터를 꺼내오는 명령

- DML : INSERT, UPDATE, DELECT

> Data Manipulate Language (데이터 조작어)

> 기존의 테이블에 행을 추가, 변경, 삭제를 하는 명령어로 되어 있음

 - DDL: CREATE, ALTER, DROP, RENAME

> 데이터 정의어

 

< DML >

 

* INSERT : 기존의 테이블에 행을 추가

- 테이블에 새로운 행을 추가

SQL> INSERT INTO DEPT

  2  VALUES (50, '영업', '서울') ;

 

- NULL 값도 추가 가능, 단 'NULL'이라고 하면 문자로 등록됨

SQL> INSERT INTO DEPT

  2  VALUES (60, '전략', NULL);

- 또 다른 NULL 입력 방법

SQL> INSERT INTO DEPT (DEPTNO, LOC)

  2  VALUES (70, '부산');

> 위처럼 했을 때 지정되지 않은 컬럼은 'NULL'

> 그 대신 위처럼 입력할 때는, 컬럼과 데이터가 순서대로 매치되어야.

 

- 1111 홍길동 3000 직속상사 7839 부서 20 MANAGER NULL 오늘 > 작성

SQL> INSERT INTO EMP

  2  VALUES (1111, '홍길동', 'MANAGER', 7839, SYSDATE, 3000, NULL, 20);

 

- 이렇게 쓰면 컬럼 순서 편한대로 쓸 수 있음.. 단 데이터와는 순서 맞춰야!

SQL> INSERT INTO EMP(ENAME, SAL, HIREDATE, JOB, MGR, DEPTNO, EMPNO, COMM)

  2  VALUES ('이순신', 3000, SYSDATE, 'MANAGER', 7839, 20, 2222, NULL);

 

* UPDATE : 기존 테이블의 행을 변경

UPDATE 테이블

SET 컬럼 = 값;

 > ‘컬럼’의 값을 ‘값’으로 교체
UPDATE DEPT

SET DNAME = ‘IT’;

 > 그 테이블의 모든 부서 이름이 ‘IT’로 바뀌게 됨

 > UPDATE의 대상은 전체 테이블이기 때문 …

 > 따라서, 조건절 사용!

UPDATE DEPT

SET DNAME = ‘IT’;

WHERE DEPTNO = 60;

 > 60번 부서만 부서의 이름을 ‘IT’로 바꿈

 

* DELETE : 기존 테이블의 행을 삭제

SQL> -- 테이블의 데이터 삭제

SQL> -- 그냥 DELETE하면 테이블 안의 모든 데이터 사라짐 > 조건 설정

SQL>

SQL> DELETE FROM DEPT

2  WHERE DEPTNO IN (50, 60, 70);

 

SQL> -- 1111, 2222번 사원을 삭제

SQL> DELETE FROM EMP

  2  WHERE EMPNO IN (1111, 2222);

 

< DDL >

 

- CREATE : 테이블 생성

> NUMBER(4) : 숫자값 네자리 -> 38자리까지 가능

> NUMBER(7,2) : 숫자 7자리가 가능하고, 소수점은 2자리까지 표현 -> 정수분 최대 5자리까지

   >> NUMBER(4)는 정수형 / NUMBER(7,2)는 실수형

> VARCHAR2(10) : 가변형 문자 10자 -> 10자는 영문자 기준 (한글은 5자)

> DATE : 날짜

> CHAR: 문자

 

# 3가지 문자형

> CHAR(8) : A | B | C |  |  |  |  |  |  > 남는 공간까지 간직하기 때문에 계속 8칸 [고정문자]

> VARCHAR2(8) : A | B | C > 남는 공간 반납 [가변문자]

> VARCHAR(8)

 

 - 1. CHAR

           장점: 읽기 성능이 좋음

           단점: 공간을 효율적으로 사용하지 못함

 - 2. VARCHAR2

           장점: 공간을 효율적으로 사용한다.

           단점: 데이터를 읽어올 때 길이가 매 행마다 다름  > 읽기 성능이 저하

 

 

@ 회원이라는 테이블을 만들 때,

           회원번호 / 이름 / 등록일자 / ID / PW / 전화번호 / 주민등록번호 / 주소

           > 회원번호: NUMBER

           > 이름: VARCHAR2

           > 등록일자: DATE

           > ID/PW: VARCHAR2

           > 전화번호: VARCHAR2

                              01012345678로 입력하면

                             1012345678이라는 결과가 나와 버림..(0이 생략됨)

                             따라서, 아예 문자로 취급함

           > 주민등록번호: CHAR

                                    길이가 다 일정하기 때문에 CHAR

           > 주소: VARCHAR2

                      주소는 길이가 각기 다름

 

[ 실습 ]

 

* 테이블 복사

- 4개의 데이터를 COPY_DEPT 테이블에 복사

> ORACLE은 1번에 1개씩 INSERT 가능

SQL> INSERT INTO COPY_DEPT

  2  VALUES ( 10, 'ACCOUNTING', 'NEW YORK');

SQL> INSERT INTO COPY_DEPT

  2  VALUES (20, 'RESEARCH', 'DALLAS');

SQL> INSERT INTO COPY_DEPT

  2  VALUES (30, 'SALES', 'CHICAGO');

SQL> INSERT INTO COPY_DEPT

  2  VALUES (40, 'OPERATIONS', 'BOSTON');

 

- 서브쿼리를 이용해서 간단하게 테이블을 복사할 수 있음!

> EMP 테이블을 SELECT한 것으로 COPY_EMP3를 만듦

> 데이터까지 똑같이 들어있음

SQL> CREATE TABLE COPY_EMP3

  2  AS

3  SELECT * FROM EMP;

 

- 이미 있는 테이블에 데이터 복사하는 법은?

SQL> INSERT INTO COPY_EMP2

2  SELECT * FROM EMP;

 

* COPY에 열 추가

- DESC COPY_EMP <== 실습을 위해 EMP 파일이라고 생각하기!

COPY_EMP에 핸드폰 번호 추가하기

SQL> ALTER TABLE COPY_EMP

2  ADD HP CHAR(10);

 

* COPY한 열에 데이터 추가

위처럼 새로 추가된 HP 컬럼은 값이 NULL이다.

새로 추가한 컬럼에 값을 넣고 싶다면?

- SMITH에 010-1234-5678 추가

SQL> UPDATE COPY_EMP

  2  SET HP = '01012345678'

  3  WHERE ENAME = 'SMITH';

>> 실제 길이가 열의 최대값보다 길어서 오류가 남 짧게 하면 가능

SQL> UPDATE COPY_EMP

  2  SET HP = '0101234567'

  3  WHERE ENAME = 'SMITH';

 

* COLUMN 의 이름 변경

- 'HP'라는 COLUMN의 이름 수정

SQL> ALTER TABLE COPY_EMP

  2  RENAME COLUMN HP TO MP;

 

* COLUMN의 자료형 및 데이터 수정

-  'MP'라는 COLUMN의 자료형 수정

SQL> ALTER TABLE COPY_EMP

  2  MODIFY MP CHAR(13);

- 'SMITH'의 번호를 010-1234-5678 형식으로 변경

SQL> UPDATE COPY_EMP

  2  SET MP = '010-1234-5678'

3  WHERE ENAME = 'SMITH';

 

* 다시 안쓰는 COLUMN 지우기

SQL> ALTER TABLE COPY_EMP

  2  DROP COLUMN MP;

 

>> 이와 같은 명령어를 자주 쓰는 것이 좋을까?

개발 초기(기획, 설계)에는 쓸 수 있지만,

        나중에는 컬럼 하나를 추가하기 위해서도 엄청난 코드를 수정하게 됨

 

*  TABLE 삭제

SQL> DROP TABLE COPY_EMP;

>> TABLE이 영구히 삭제된다.

 

데이터베이스를 관리하는 DBA라는 센터가 있음

이 센터에서 주기적으로 데이터를 백업함

데이터도 RECYCLEBIN에 가있음

SQL> SHOW RECYCLEBIN

 

> 지운 TABLE을 되살리는 방법은?

SQL> FLASHBACK TABLE COPY_EMP

  2  TO BEFORE DROP;

 

*  테이블 이름 변경

SQL> RENAME COPY_EMP TO CEMP;

 

더보기

* DB 초기화 방법

 CMD에 SYSTEM/ORACLE로 로그인 후, @ 치고DB 초기화 파일 드래그 앤 드랍 - ENTER

 

 

++ 응용 문제

더보기

< DML 연습 >

 

TEST_EMP 테이블의 내용이 아래와 같게 DML을 사용해보세요

 

​SQL> CREATE TABLE TEST_EMP

2 AS

3 SELECT * FROM EMP;

 

SQL> DELETE FROM TEST_EMP

2 WHERE ENAME IN ('TURNER', 'ADAMS', 'JAMES');

 

​SQL> INSERT INTO TEST_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

2 VALUES (111, 'HONG', 'MANAGER', 7369, (SELECT HIREDATE FROM TEST_EMP WHERE ENAME = 'MILLER'), 2400, NULL, 10);

 

SQL> INSERT INTO TEST_EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

2 VALUES (222, 'LEE', 'CLERK', 7788, (SELECT HIREDATE FROM TEST_EMP WHERE ENAME = 'WARD'), 5000, 300, 30);

 

 

< SELECT TEST 문제>

 

1.SELECT문의 6가지절에 대한 설명하시오.

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

 

​

[2~8] 다음은 설명을 보고 적절한 조치를 취할 수 있는 SQL문을 작성하시오 .

​

2. 사원이름과 직업, 급여, 급여등급 을 출력하라 ?

 

​SQL> SELECT E.ENAME, E.SAL, S.GRADE

2 FROM EMP E, SALGRADE S

3 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

 

3. 10 번 부서의 사원들과 같은 월급을 받는 사원들의 이름과 월급과 부서 번호를 출력하라 ?

 

​SQL> SELECT ENAME, SAL, DEPTNO

2 FROM EMP

3 WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO = 10);

 

​

4. 이름에 T 를 포함하고 있는 사원들과 동일한 부서에서 근무하고 있는 사원의 사원 번호와 이름을 출력하라 ?

 

SQL> SELECT EMPNO, ENAME

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME LIKE '%T%');

 

5. BLAKE 이란 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하라? ( SUBQUERY와 JOIN 두가지 방법 모두 기술)

 

SQL> SELECT C.ENAME, C.HIREDATE

2 FROM EMP E, EMP C

3 WHERE C.HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'BLAKE')

4 AND E.EMPNO = C.EMPNO;

 

​SQL> SELECT C.ENAME, C.HIREDATE

2 FROM EMP E JOIN EMP C

3 ON (E.ENAME = 'BLAKE')

4 AND E.HIREDATE < C.HIREDATE;

 

6. JOB이 MANAGER 일때 급여를 10% 인상 CLERK일때 5% 인상하는 결과를 출력하시오.

(단 ,이름 , JOB , 급여 , 인상급여 를 출력하되 DECODE 함수와 CASE문 두가지로 작성)

 

​SQL> SELECT ENAME, JOB, SAL, DECODE(JOB, 'MANAGER', SAL*1.1, 'CLERK', SAL*1.05, SAL) "UPSAL"

2 FROM EMP;

=

SQL> SELECT ENAME, JOB, SAL,

2 CASE

3 WHEN JOB = 'MANAGER' THEN SAL*1.1

4 WHEN JOB = 'CLERK' THEN SAL*1.05

5 ELSE SAL

6 END "UPSAL"

7 FROM EMP;

 

​7. 10번 부서 중에서 30번 부서에는 없는 업무를 하는 사원의 사원번호, 이름 , 부서명, 입사일, 지역을 출력하라.

 

​SQL> SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC

2 FROM EMP E, DEPT D

3 WHERE E.DEPTNO = 10

4 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO = 30)

5 AND E.DEPTNO = D.DEPTNO;

=

SQL> SELECT E.EMPNO, E.ENAME, D.DNAME, E.HIREDATE, D.LOC

2 FROM EMP E JOIN DEPT D

3 USING (DEPTNO)

4 WHERE DEPTNO = 10

5 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO = 30);

 

8. 급여가 30번 부서의 최고 급여보다 많은 사원의 사원번호, 이름 , 급여를 출력하라.

 

SQL> SELECT EMPNO, ENAME, SAL

2 FROM EMP

3 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)