2022-02-03 DML / DDL

저번에 서브커리에 대해서도 약간 내용을 넣었는데, 그중에 스칼라 서브쿼리를 살짝 보면서 시작해보자. 스칼라 서브쿼리는 상호연관 서브쿼리이면서 결과값이 1개인 서브쿼리이다.

1. 스칼라 서브쿼리 예시
   -- 모든 사원의 이름, 부서코드, 부서명 출력
   SELECT EMP_NAME, DEPT_CODE,(SELECT DEPT_TITLE FROM DEPARTMENT WHERE DEPT_ID = E.DEPT_CODE) "부서명"
   FROM EMPLOYEE E

2. 스칼라 서브쿼리의 WHERE절 사용
   -- 자신이 속한 부서의 평균 급여보다 급여를 많이받는 직원의 이름, 부서, 급여를 조회
   SELECT EMP_NAME, DEPT_CODE, SALARY,
   FLOOR((SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = E1.DEPT_CODE)) 평균급여
   FROM EMPLOYEE E1
   WHERE SALARY >
   (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE = E1.DEPT_CODE);

3. 서브쿼리 FROM절에 사용 -> 인라인 뷰
   // 뷰 : 데이터 처리를 위해 사용되는 가상의 테이블
   회사에서 급여를 많이받는사람 순서대로 5명
   SELECT EMP_NAME, SALARY FROM EMPLOYEE WHERE SALARY >= 3700000;
   -- 직접 조회해봤을 때 3700000이상이면 상위 5명이라 설정
   -- 이렇게 해 주었을 경우 매번 급여가 바뀔때마다 바꿔줘야해서 비효율적
   -- ROWNUM : SELECT된 행마다 자동으로 순차적으로 번호를 붙여주는 값

---

SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;
--얘를 가상의 테이블로 쓴다는 얘기 // 급여를 많이 받는 순으로 내림차순했음

---

SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT ROWNUM, EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC);
-- 급여를 많이받는 순으로 ROWNUM 번호가 만들어짐

---

SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT ROWNUM, EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
WHERE ROWNUM<=5;
-- 급여를 많이받는 순으로 5명까지

요렇게만 정리하자

다음으로 간단하게 정리할 것은 중복을 표현하는 방법이다. 이것도 코드만 올린다.

중복
-- RANK() OVER 중복순위가 있는 경우 중복순위만큼 건너뛰고 다음번호를 반환(총 23명이면 마지막 번호 -> 23이 나옴 단, 마지막 등수가 중복등수가 아닌 경우)
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE;

---

-- DENSE_RANK() OVER 중복순위와 상관없이 순차적으로 다음번호 반환 (총 23명인 경우 중복번호만큼 마지막 숫자가 줄어듬 -> 22)
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE;

---

-- ROW_NUMBER() OVER 중복과 관계없이 무조건 순서대로 반환 (총 23명인 경우 무조건 마지막 번호가 23 중복된 번호도 그냥 상관없이 다음번호로 표현)
SELECT EMP_NAME, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE;

이제 본격적인 내용으로 들어가보자. 오늘 정리할 내용은 DML과 DDL이다.

DML

DML은 (Data Manipulation Language)의 약자로, 데이터를 조작하는 언어이다.

DML

• 데이터 조작 언어
• 테이블에 값을 삽입, 수정, 삭제하는 역할
• INSERT(삽입), UPDATE(수정), DELETE(삭제)

위와 같이 INSERT, UPDATE, DELETE3가지 종류가 있다.
필자는 크게 4가지 방법으로 INSERT를 사용하는 방법을 설명하겠다.

INSERT

INSERT 사용법
1. INSERT INTO 테이블이름 VALUE(값1,값2,값3...);--순사대로 모든 컬럼의 값
2. INSERT INTO 테이블이름 (컬럼1,컬럼2,컬럼3...) VALUE(값1,값2,값3...);
3. 서브쿼리를 이용한 ISNERT
4. INSERT ALL : 여러테이블에 한번에 INSERT를 하는 구문

4가지로 정리해보았다. 각각 예시를 들며 봐보자.

INSERT INTO 테이블이름 VALUE(값1,값2,값3...);--순사대로 모든 컬럼의 값

CREATE TABLE EMP_COPY1(
    EMP_NAME    VARCHAR2(20),
    EMAIL       VARCHAR2(30),
    DEPT_CODE   CHAR(2),
    JOB_CODE    CHAR(2),
    SALARY      NUMBER
);

먼저 EMP_COPY1이라는 테이블을 만들어서 이 테이블을 이용해 만들어본다.
위의 테이블은 이름, 이메일, 부서코드, JOB코드, 급여의 값을 가지고있다.
테이블을 생성했다면 이제 값을 넣어주기만 하면된다.

INSERT INTO EMP_COPY1 VALUES('직원1','emp1@naver.com','D5','J1',3000000);
 -- 대이터를 정의한 순서대로 넣는다.

이런식으로 넣어주면 처음에 테이블을 만들때 쿼리를 정의한 순서대로 값을 넣어준다. 이때 데이터크기가 첫 설정을 벗어나거나, 데이터의 형태가 다를때 에러가 나온다.

INSERT INTO EMP_COPY1 VALUES('EMAIL1@NAVER.COM','D9','이름','J3',4000000); 
-- 데이터를 정의한 순서대로 넣는다. 크기를 2바이트로 지정해놓은 CHAR(2)애다가 6바이트짜리 한글을 넣으려그러니 에러
INSERT INTO EMP_COPY1 VALUES('EMAIL1@NAVER.COM','D9','이름','J3'); 
-- 들어가는 컬럼수보다 적게 입력해도 에러 // 더 채우라고 알려줌

2. INSERT INTO 테이블이름 (컬럼1,컬럼2,컬럼3...) VALUE(값1,값2,값3...);

이 방법은 정의해준 컬럼의 순서대로 넣는것이 아니라 내가 넣고싶은 순서대로 넣을 수 있다.

INSERT INTO EMP_COPY1 (SALARY,JOB_CODE,EMAIL,EMP_NAME,DEPT_CODE)
VALUES(5000000,'J3','EMP3@iei.or.kr','emp3','D9');

처음에 만들어놓은 컬럼을 전부 사용하지 않아도 알아서 NULL값으로 채우준다.

INSERT INTO EMP_COPY1 (EMP_NAME,EMAIL,DEPT_CODE,JOB_CODE) 
VALUES('직원4','emp3@naver.com','D2','J7'); 
-- 테이블에 포함되어있는 컬럼을 넣어주지 않아도 괜찮다. NULL값으로 대체된다.

3. 서브쿼리를 이용한 ISNERT

INSERT INTO EMP_COPY1 (SELECT EMP_NAME, EMAIL, DEPT_CODE, JOB_CODE, SALARY FROM EMPLOYEE); 
--서브쿼리의 조회 결과가 메인 테이블에 전부 들어간다.
--컬럼 숫자, 데이터 타입이 맞아야지 가능하다.
SELECT EMP_NAME, EMAIL, DEPT_CODE, JOB_CODE, SALARY FROM EMPLOYEE; 
-- 요 쿼리를 입력했다. 조회결과를 바로 INSERT

서브쿼리를 이용한 INSERT는 가볍게봐도 괜찮을 것 같다.
위에 것으로 보면, EMP_NAME, EMAIL, DEPT_CODE, JOB_CODE, SALARY을 조회하는 서브쿼리의 값을 그대로 넣어준 것 이다. 형태가 같지 않다면 넣어줄 수 없다.

4. INSERT ALL : 여러테이블에 한번에 INSERT를 하는 구문

먼저 테이블을 생성해보자.

CREATE TABLE EMP_COPY2(
    EMP_NAME    VARCHAR(20),
    DEPT_CODE   CHAR(2)
);
CREATE TABLE EMP_COPY3(
    EMP_NAME    VARCHAR(20),
    JOB_CODE    CHAR(2)
);

이제 두 테이블에 맞는 값을 한번에 넣어준다.

INSERT ALL
INTO EMP_COPY2 VALUES(EMP_NAME,DEPT_CODE)
INTO EMP_COPY3 VALUES(EMP_NAME,JOB_CODE)
SELECT EMP_NAME, DEPT_CODE, JOB_CODE FROM EMPLOYEE WHERE SALARY>=3000000;

EMP_NAME, DEPT_CODE, JOB_CODE 의 3가지 쿼리를 모드 INSERT해서 두 테이블에서 각각 필요한 쿼리의 값을 가져가도록 만든다.

이게 INSERT기본이다. 다음은 UPDATE이다. UPDATE는 쉽다.

UPDATE
UPDATE : 데이블 내부의 데이터를 수정할때 사용
UPDATE 의 구성 : 테이블명 SET 수정컬럼=구정값, 수정컬럼=수정값.... WHERE 조건

UPDATE

UPDATE EMP_COPY1 SET SALARY = 2400000 WHERE EMP_NAME = '직원4';
COMMIT;

위의 코드는 '직원4'의 급여를 2400000으로 변경하는 것이다.
변경이 완료됐으면, COMMIT을 이용하여 저장해준다.

부서코드가 D2인 직원들의 급여를 현재 급여보다 50만원씩 인상
UPDATE EMP_COPY1 SET SALARY = SALARY+500000 WHERE DEPT_CODE = 'D2';

이렇게 해주면 부서코드가 'D2'인 모든 직원의 급여를 50만원씩 인상한다.
만약 WHERE절을 사용해 조건을 걸지 않는다면, 모든 열이 영향을 받는다.
대부분은 사용한다.

이름이 직원4인 직원의 부서코드, 직급코드 변경
UPDATE EMP_COPY1 SET DEPT_CODE = 'D3', JPB_CODE = 'J6' WHERE EMP_NAME = '직원4';
-- 한번의 여러개의 데이터를 수정할때는 ','(콤마) 사용

이렇게 한번의 여러개의 데이터를 UPDATE할 때는 그냥 , 찍고 다음 업데이트 내용을 적는다.

UPDATE시에도 SUBQUERY 사용 가능
UPDATE EMP_COPY1 SET SALARY = (SELECT SALARY FROM EMP_COPY1 
WHERE EMP_NAME = '송종기') WHERE EMP_NAME = '노옹철'; 
-- SUBQUERY활용
SELECT * FROM EMP_COPY1;

이렇게 작성하면, 서브쿼리에서 이미 송종기의 급여를 찾아놓고, UPDATE로 노옹철의 급여를
6000000으로 둘이 같게 만든다.

UPDATE는 여기까지다. 이 뒤는 이거보다도 종류가 적은 DELETE이다.

DELETE

DELETE는 간단하다. 그냥 삭제하는 거다. 예시도 하나밖에 안들거다.

-- DELETE : 테이블의 행을 삭제
-- DELETE FROM 테이블명 WHERE 조건
COMMIT;
SELECT * FROM EMP_COPY1;
DELETE FROM EMP_COPY1 WHERE EMAIL = '이름'

위의 코드가 의미하는 것은 EMAIL쿼리의 값이 '이름'인 열을 삭제하는 것 이다.
만약 이것도 WHERE절을 안해주면 다 삭제된다. 삭제하고 커밋해주자.

DDL

DDL

• 데이터 정의 언어
• 객체를 만들고, 수정하고, 삭제하는 구문
• CREATE(생성), ALTER(수정), DROP(삭제)

오늘은 이중에서 CREATE(생성)만 알아볼 것 이다. 필자가 거기까지밖에 공부를 안했다..!

CREATE(생성)

• DDL의 한 종류로 테이블이나 인덱스, 유저 등 다양한 데이터베이스 객체를 생성하는 구문

관리자 계정과 사용자 계정이 있는데, 이는 사용할 수 있는 권한에 차이가 있다.

관리자 계정 : 데이터베이스의 생성과 관리를 담당하는 계정이며, 모든 권한과 책임을 가지는 계정
사용자 계정 : 데이터베이스에 대하여 질의, 갱신, 보고서 작성 등을 수행할 수 있는 계정으로
업무에 필요한 최소한의 권한만 가지는 것을 원칙으로 함

사용자 계정을 만들기 위해서는 ADMIN계정을(관리자 계정) 사용하여야 한다.

표현식
CREATE USER 사용자이름 IDENTIFIED BY 비밀번호;
[ex]
1) CREATE USER AB IDENTIFIED BY AB;
2) CREATE USER test01 IDENTIFIED BY 1234;

이렇게 계정을 만들고나면, 바로 접속하면 접속이 안된다. 유저 계정으로 접속할 수 있도록 관리자 계정에서 권한을 주어야한다.

GRANT CONNECT, RESOURCE TO test01;

이렇게 해주면 TEST01 사용자 계정에 접속 권한과, RESOURCE권한이 생긴다.

REVOKE CONNECT, RESOURCE FROM test01;

이러면 다시 뺏을 수 있다.
어쨌든, CONNECT권한을 주면 접속이 가능해지고, RESOURCE권한을 주면 다양한 DDL 사용이 가능하다.

이렇게 사용자 계정을 생성했으면, 테이블도 만들어보자.

CREATE TABLE USER(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PW VARCHAR2(30),
    USER_NAME VARCHAR2(20),
    GENDER CHAR (6),
    PHONE CHAR(13)
);

요런식으로 만들 수 있다.
근데 테이블을 만들때, 다른사람들이 이 쿼리가 뭘 의미하는지 쉽게 알 수 있도록 해주는게 있다.
바로 주석이다.

-- 생성된 테이블 컬럼에 주석 달기
-- COMMENT ON COLUMN 테이블몀.컬럼명 IS '주석내용';
COMMENT ON COLUMN USER.USER_ID IS '회원아이디';
COMMENT ON COLUMN USER.USER_PW IS '회원 비밀번호';

이런식으로 주석을 넣어놓으면, 테이블 영역을 열어서 주석을 확인할 수 있다.

이제 테이블 쿼리에 값도 넣어보자. 아까 사용했던 INSERT를 다시 사용한다.

INSERT INTO USER_NOCONS VALUES(1,'USER01','1234','유저1','남자','010-1111-1111');

요로코롬 만드는거다.
근데 우리가 평소에 쓰는 아이디는 보통 중복을 사용할 수 없다. 이럴때 제약조건을 거는 것 이다.

제약조건
1. NOT NULL : 데이터에 NULL을 허용하지 않는다.
2. UNIQUE : 중복된 값을 허용하지 않는다.
3. PRIMARY KEY : NULL을 허용하지 않고, 중복을 허용하지 않는다.
컬럼의 고유 식별자로 사용하기 위함이다.
4. FOREIGN KEY : 참조되는 테이블의 컬럼 값이 존재하면 허용한다.
5. CHECK : 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용한다.

오늘은 위의 5개중 위의 2개, NOT NULL과 UNIQUE를 알아보자.
역시 필자가 여기까지만 공부했다...

NOT NULL

NULL을 허용하지 않는 테이블
CREATE TABLE USER_NOTNULL(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) NOT NULL, -- 해당컬럼에는 NULL을 허용하지 않음
    USER_PW VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(20),
    GENDER CHAR (6),
    PHONE CHAR(13)
);
INSERT INTO USER_NOTNULL VALUES(1,'USER01','1234','유저1','남자','010-1111-1111');
INSERT INTO USER_NOTNULL VALUES(1,'USER02',NULL,'유저1','남자','010-1111-1111');
SELECT * FROM USER_NOTNULL;

위와 같은 코드를 짜면, USER_NOTNULL테이블은 첫번째 INSERT는 받아들이지만, 2번째 값은 받아들이지 못한다. 왜냐하면 두번째 INSERT는 USER_PW에 NULL값을 넣으려고 했기 때문이다.

사실 NOTNULL은 이게 끝이다. 그냥 NULL값을 못받아들이게 하는 것 이다.

UNIQUE

UNIQUE
CREATE TABLE USER_UNIQUE1(
    USER_NO NUMBER UNIQUE, --해당컬럼은 중복 데이터를 허용하지 않음
    USER_ID VARCHAR2(20) NOT NULL UNIQUE, -- 띄어쓰기로 구분함
    USER_PW VARCHAR2(30)NOT NULL,
    USER_NAME VARCHAR2(20),
    GENDER CHAR (6),
    PHONE CHAR(13) UNIQUE
);
INSERT INTO USER_UNIQUE1 VALUES(1,'USER01','1234','유저1','남자','010-1111-1111');
INSERT INTO USER_UNIQUE1 VALUES(2,'USER02','2222','유저2','남자','010-1111-1111');
SELECT * FROM USER_UNIQUE1;

위와 같은 코드를 짜면, USER_NOTNULL테이블은 첫번째 INSERT는 받아들이지만, 2번째 값은 받아들이지 못한다. 전화번호(PHONE) 값을 같게 만들어놨기 때문이다. PHONE값 말고도, USER_NO, USER_ID 값 이 세 값중 하나라도 중복이 있다면, INSERT를 받아들이지 않을 것 이다.

CREATE TABLE USER_UNIQUE2(
    USER_NO NUMBER, --해당컬럼은 중복 데이터를 허용하지 않음
    USER_ID VARCHAR2(20) NOT NULL, -- 띄어쓰기로 구분함
    USER_PW VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(20),
    GENDER CHAR (6),
    PHONE CHAR(13),
    UNIQUE(USER_NO),
    UNIQUE(USER_ID),
    UNIQUE(PHONE) -- TABLE레벨에 넣어주는 방법이다.
);

이거는 이제 컬럼 옆에 조건을 넣어주지 않고, TABLE레벨에 넣어주는 방식이다.

CREATE TABLE USER_UNIQUE3(
    USER_NO NUMBER, --해당컬럼은 중복 데이터를 허용하지 않음
    USER_ID VARCHAR2(20) NOT NULL, -- 띄어쓰기로 구분함
    USER_PW VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(20),
    GENDER CHAR (6),
    PHONE CHAR(13),
    UNIQUE(USER_NO, USER_ID) -- 둘다 중복될때만 중복을 잡는다.
);

마지막으로 이건 아까와 같은 방식인데, UNIQUE안에 값을 한꺼번에 넣어준 것 이다.
이게 위의 코드와 같다고 생각하면 안된다. 엄연히 다른 코드이다.

INSERT INTO USER_UNIQUE3 VALUES(1,'USER01','1234','유저1','남자','010-1111-1111');

INSERT INTO USER_UNIQUE3 VALUES(1,'USER02','2222','유저2','남자','010-1111-1111'); 
-- USER_NO은 겹치는데 USER_ID는 안겹치므로 이 구문은 허용된다. 이러면 안된다.

INSERT INTO USER_UNIQUE3 VALUES(2,'USER02','2222','유저2','남자','010-1111-1111'); 
-- 얘도 들어감

INSERT INTO USER_UNIQUE3 VALUES(2,'USER01','2222','유저2','남자','010-1111-1111'); 
-- 얘까지도 들어감 왜냐하면 하나의 열에서 2개가 다 겹치는게 아니면 안잡음

INSERT INTO USER_UNIQUE3 VALUES(2,'USER02','2222','유저2','남자','010-1111-1111'); 
-- 얘는 이제 아예 겹치는게 있으니까 잡음

이렇게 결과가 나온다.
오늘도 꽤 길게 썼다. 내일 또 쓰러 오겠다.
고생했다!! 빠잉!