SQL활용 - DDL(복합키, FOREIGN KEY 제약조건, 테이블 복사, ALTER, DROP), DML(INSERT, UPDATE, DELETE), DCL(GRANT, REVOKE, SAMPLE계정), TCL(COMMIT, ROLLBACK, SAVEPOINT)
④-1 DDL_복합키
- 두 컬럼을 하나로 묶어서 PRIMARY KEY 하나로 설정이 가능하다.
EX) 쇼핑몰 속 찜하기는 하나의 고객은 여러개의 상품을 찜할 수도 있기에 'USER'+'상품' 두개의 컬럼값을 하나의 PRIMARY KEY로 만들 수 있음 => '복합키'
CREATE TABLE MEM_PRIMARYKEY2(
MEM_NO NUMBER,
MEM_ID VARCHAR2(20),
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
PRIMARY KEY(MEM_NO, MEM_ID) --컬럼을 묶어서 RPIMARY KEY 하나로 설정
);
INSERT INTO MEM_PRIMARYKEY2
VALUES(1, 'USER01', 'PASS01', 'HONG', NULL, NULL, NULL);
SELECT * FROM MEM_PRIMARYKEY2;
INSERT INTO MEM_PRIMARYKEY2
VALUES(2, 'USER01', 'PASS02', 'HONG', NULL, NULL, NULL);
--> MEM_NO과 MEM_ID 중 하나만 달라도 정상적으로 삽입 가능
INSERT INTO MEM_PRIMARYKEY2
VALUES(NULL, 'USER02', 'PASS03', 'KIM', NULL, NULL, NULL);
--> 기본키로 설정되어있는 컬럼들에는 NULL값이 들어갈 수 없음- 회원 등급에 대한 데이터(등급코드, 등급명)를 보관하는 테이블
- MEM_GRADE ->테이블명
- GRADE_CODE -> 컬렴명, CHAR(2)형, 기본키
- GRADE_NAME -> 컬럼명, VARCHAR2(20), NULL값 불가
CREATE TABLE MEM_GRADE(
GRADE_CODE CHAR(2) PRIMARY KEY,
GRADE_NAME VARCHAR2(20) NOT NULL
);
INSERT INTO MEM_GRADE
VALUES ('G1', '일반회원');
INSERT INTO MEM_GRADE
VALUES ('G2', '우수회원');
INSERT INTO MEM_GRADE
VALUES ('G3', '특별회원');
SELECT * FROM MEM_GRADE;⑤ FOREIGN KEY(외래키) 제약조건
: 해당 컬럼에 다른 테이블 존재하는 값만 들어와야 될 때 부여하는 제약조건
=> 다른테이블(부모테이블)을 참조한다고 표현
즉, 참조된 다른 테이블이 제공하고 있는 값만 들어올 수 있다.
=> FOREIGN KEY제약조건으로 다른 테이블간의 관계를 형성할 수 있음
- 컬럼레벨방식
컬럼명 자료형 REFEREBCES 참조할테이블명(참조할 컬럼명)
- 테이블레벨방식
FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할 컬럼명)
두 방식 모두 참조할 컬럼명은 생략 가능
생략 시 기본적으로 참조할 테이블의 PRIMARY KEY컬럼으로 참조할 컬럼명으로 잡힘
-자식테이블 생성
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE),
GENDER CHAR(3) CHECK(GENDER IN('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30)
);
SELECT * FROM MEM;
INSERT INTO MEM
VALUES (1, 'USER01', 'PASS01', '홍길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (2, 'USER02', 'PASS02', '김길동', 'G2', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (3, 'USER03', 'PASS03', '김말동', 'G1', NULL, NULL, NULL);
SELECT MEM_ID, MEM_PWD, MEM_NAME, GRADE_NAME
FROM MEM, MEM_GRADE
WHERE GRADE_ID = GRADE_CODE;INSERT INTO MEM
VALUES (4, 'USER04', 'PASS04', '김동동', NULL, NULL, NULL, NULL);
INSERT INTO MEM
VALUES (5, 'USER05', 'PASS05', '김당당', 'G4', NULL, NULL, NULL);
-->ORA-02291: integrity constraint (DDL.SYS_C007105) violated - parent key not found 컬럼의 조건에 맞지 않는 값을 입력해 무결성제약조건에 위배됨
-- PARENT KEY NO FOUND발생
-- G4등급은 MEM_GRADE 테이블의 GRADE_CODE컬럼에서 제공하는 값이 아니기 때문에 오류발생- 만약, 부모테이블(EME_GRADE)에서 데이터값이 삭제된다면?
: MEM_GRADE 테이블로부터 GRADE_CODE G1인 데이터 지우기
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';-> 자식테이블(MEM)중에 G1을 사용하고 있기 때문에 삭제할 수 없음
-외래키 제약조건 부여 시 삭제 옵션을 부여하지 않았음
-자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 "삭제제한옵션"이 걸려있음
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3';-> 자식테이블에서 사용되고 있는 값이 아니기 때문에 삭제 가능
SELECT * FROM MEM_GRADE;-> 행이 삭제됨
ROLLBACK;
--DROP TABLE MEM; --구조를 삭제
--DELETE FROM MEM_GRADE; --데이터를 삭제* 자식테이블 생성 시 (외래키 제약조건 부여 시)
부모테이블의 데이터가 삭제되었을 때 자식테이블에서는 어떻게 처리할 건지 옵션으로 정할 수 있음
# FOREGIN KEY 삭제옵션
삭제옵션을 별도로 지정하지 않으면 기본적으로 ON DELETE RESTRICTED(삭제제한)으로 설정
1) ON DELETE SET NULL : 부모데이터 삭제 시 해당 데이터를 사용하고 있는 자식데이터를 NULL로 변경시키겠다.
- 테이블 생성
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
GENDER CHAR(3) CHECK(GENDER IN('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL
);- 데이터 입력
INSERT INTO MEM
VALUES (1, 'USER01', 'PASS01', '홍', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (2, 'USER02', 'PASS02', '김', 'G2', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (3, 'USER03', 'PASS03', '황', 'G1', NULL, NULL, NULL);- GRADE_CODE가 'G1'인 컬럼 삭제 시
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';SELECT절을 통해 조회해보면
행1, 2는 NULL값으로 변하는 Result Set을 볼 수 있음.
ROLLBACK;
DROP TABLE MEM;
--다시 되돌린 뒤 테이블 구조 삭제2) ON DELETE CASCADE : 부모데이터 삭제 시 해당 데이터를 사용하고 있는 데이터도 같이 삭제해버리는 옵션
- 테이블 생성
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
GENDER CHAR(3) CHECK(GENDER IN('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE
);- 데이터 입력
INSERT INTO MEM
VALUES(1, 'USER01', 'PASS01', '홍길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES(2, 'USER02', 'PASS02', '김길동', 'G2', NULL,NULL,NULL);
INSERT INTO MEM
VALUES (3, 'USER03', 'PASS03', '박길동', 'G1', NULL,NULL,NULL);
INSERT INTO MEM
VALUES (4, 'USER04', 'PASS04', '이길동', NULL ,NULL, NULL, NULL);- NOT NULL 제약조건이 없는 GRADE_CODE는 NULL값도 입력 가능
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';-> 문제없이 잘 삭제됨
조회해보면,
-> 자식테이블(MEM)의 GRADE_ID가 G1인 행들이 모두 함께 삭제됨(행)
- 실습) 전체회원의 회원번호, 아이디, 비밀번호, 이름, 등급명 조회
--ORACLE
SELECT MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_NAME
FROM MEM, MEM_GRADE
WHERE GRADE_ID =GRADE_CODE(+);
--ANSI
SELECT MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_NAME
FROM MEM
LEFT JOIN MEM_GRADE ON(GRADE_ID =GRADE_CODE);
DROP TABLE MEM;하지만, JOIN을 하기 위해서 무조건 외래키 제약조건에 걸려있어야 하는 것은 아님
- 트랜잭션 : 아래와 같은 논리적 단위 => 뒤에서 자세히 다뤄볼 예정.
예시) 비밀번호 변경 처리
1. 아이디, 비밀번호를 입력해서 본인임을 확인
SELECT MEM_ID, MEM_PWD
FROM MEM
WHERE MEM_ID = '사용자가 입력한 아이디'
AND MEM_PWD = '사용자가 입력한 비밀번호';
2. 기존의 비밀번호, 새로운 비밀번호 입력
SELECT MEM_PWD WHERE 컬럼 = '기존의 비밀번호 == 새비밀번호'
3. 기존의 비밀번호와 새로운 비밀번호가 다르다면 변경
UPDATE MEM
SET EME_PWD = '새로 입력한 비밀번호'
WHERE MEM_ID = '아이디'
AND MEM_PWD = '기존비밀번호';
비밀번호 변경완료
COMMIT;
-실행계정 : KH
(7) SUBQUERY를 이용한 테이블 생성(복사뜨는 개념)
- SUBQUERY -> 메인 SQL문을 보조하는 쿼리문
[표현법]
CREATE TABLE 테이블명
AS 서브쿼리;
서브쿼리 수행결과(RESULT SET)으로 테이블을 생성하겠다.
① EMPLOYEE테이블을 복제한 새로운 테이블 생성(EMPLOYEE_COPY)
CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;- 제약조건 NOT NULL만 복사됨
SELECT * FROM EMPLOYEE_COPY;② EMPLOYEE테이블에 있는 컬럼의 구조만 복사하고 싶은 경우 , 데이터값은 제외
CREATE TABLE EMPLOYEE_COPY2
AS SELECT *
FROM EMPLOYEE
WHERE 1=0; --거짓, WHERE절엔 항상 참/ 혹은 거짓 출력이 되기에 거짓을 출력하면 구조만 출력됨- 실습1) 전체 사원들 중 급여가 300만원 이상인 사원들의 사번, 이름, 부서코드, 급여 컬럼 복제
CREATE TABLE EMPLOYEE_COPY3
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;- 실습2) 전체 사원의 사번, 사원명, 급여, 연봉조회 결과 테이블 복제
CREATE TABLE EMPLOYEE_COPY4
AS SELECT EMP_ID, EMP_NAME, SALARY, SALARY *12 "연봉"
--서브쿼리의 SELECT 절 산술연산 또는 함수식이 기술된 경우 앨리어스를 부여해야 함
FROM EMPLOYEE;③ 테이블이 다 생성된 후 뒤늦게 제약조건 추가 (ALTER TABLE 테이블명 XXX) - 구조변경
- PRIMARY KEY : ADD PRIMARY KEY(컬럼명);
- FOROIGN KEY : ADD FORIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할컬럼명)ㅣ
- UNIQUE : ADD UNIQUE(컬럼명);
- CHECK : ADD CHECK(컬럼에 대한 조건);
- NOT NULL : MODIFY 컬럼명 NOT NULL;
- EMPLOYEE COPY 테이블에 없는 PRIMARY EMP_ID컬럼에 제약조건 추가
ALTER TABLE EMPLOYEE_COPY ADD PRIMARY KEY(EMP_ID);-EMPLOYEE_COPY테이블에 DEPT_CODE컬럼에 외래키 제약조건 추가(DEPARTMENT의 DEPT_ID를 참조)
ALTER TABLE EMPLOYEE_COPY ADD FOREIGN KEY(DEPT_CODE) REFERENCES DEPARTMENT(DEPT_ID);
2) ALTER
: 객체 구조를 수정하는 구문
< 테이블 수정 >
ALTER TABLE 테이블명 수정할내용;
- 수정할 내용
1) 컬럼 추가 / 수정 / 삭제
2) 제약조건 추가 / 삭제 => 수정은 불가 (수정하고자하면 삭제 후 새롭게 추가)
3) 테이블명 / 컬럼명 / 제약조건명
=> 테이블 "구조"와 관련된 수정
(1) 컬럼 추가 / 수정 / 삭제
1-1) 컬럼 추가(ADD) : ADD 추가할컬럼명 데이터타입 /*DEFAULT 기본값(생략가능)*/
- CNAME컬럼 추가
ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
--새로 컬럼이 만들어지고 기본적으로 NULL값으로 채워짐-LANME 컬럼 추가 DEFAULT 지정해서 '한국'으로 넣어보자
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR(20) DEFAULT '한국';
--새로 컬럼이 만들어지고 기본값이 NULL이 아닌 DEFAULT 값으로 채워짐
ROLLBACK;1_2_) 컬럼 수정(MODIFY)
- 데이터 타입 수정 : MODIFY 수정할컬럼명 바꾸고자하는 데이터타입;
- DEFAULT 값 수정 : MODIFY 수정할 컬럼명 DEFAULT 바꾸고자하는 기본값;
- DEPT_ID컬럼의 데이터 타입을 CHAR(3)로 변경
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3); - 현재 변경하고자하는 컬럼에 이미 담겨있는 값과 완전히 다른 타임으로 변경이 불가능
예) 문자 -> 숫자(X) / 문자열 사이즈 축소(X) / 문자열 사이즈 확대(O)
ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER; --=> column to be modified must be empty to change datatype
ALTER TABLE DEPT_COPY MODIFY DEPT_TITLE VARCHAR(10); --=> 축소불가- 실습) DEPT_TITLE컬럼의 데이터 타입을 VARCHAR2(40)으로
LOCATION_ID 컬럼의 데이터타입을 VARCHAR2(3)으로
LNAME컬럼의 기본값을 '미국'으로 변경
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(40)
MODIFY LOCATION_ID VARCHAR2(3)
MODIFY LNAME DEFAULT '미국';
--> 하나로 합쳐도 됨
구조는 변경 되었으나
-> 데이터를 바꾸지 않았기 때문에 테이블 안에 데이터는 그대로 '한국'임
CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPT_COPY;1_3) 컬럼삭제(DROP COLUMN) DROP COLUMN 삭제하고자 하는 컬럼명
-DEPT_COPY2 테이블에서 DEPT_ID컬럼 지우기
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_ID;
삭제됨
ROLLBACK;
--DDL구문은 복구가 불가능- 모든 컬럼 삭제
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY2 DROP COLUMN LOCATION_ID;
ALTER TABLE DEPT_COPY2 DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY2 DROP COLUMN LNAME;
--마지막 컬럼을 삭제하려고 하면 : 테이블에 최소 한 개의 컬럼이 있어야 하므로 오류 발생2) 제약조건 추가 / 삭제
2_1) 제약조건 추가
NOT NULL : MODIFY 컬럼명 NOT NULL;
UNIQUE : ADD UNIQUE(컬럼명)
CHECK : ADD CHECK(컬럼명)
PRIMARY KEY : ADD PRIMARY KEY(컬럼명)
FOREIGN KEY : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID)
ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE)
MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;2_2) 제약조건 삭제
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK : DROP CONCTRAINT 제약조건명
NOT NULL : MODIFY 컬럼명 NULL;
- DCOPY_PK 제약조건 지우기
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK;- LNAME의 제약조건 지우기 NOT NULL => NULL
ALTER TABLE DEPT_COPY MODIFY LNAME NULL;
3) 컬럼명 / 제약조건명 / 테이블명 변경(RENAME)
3_1) 컬럼명 변경 : RENAME COLUMN 기준컬럼명 TO 바꿀컬럼명;
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;3_2) 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명;
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C007142 TO DCOPY_98;3_3) 테이블명 변경 RENAME TO 바꿀테이블명;
ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;3. DROP
- 객체를 삭제하는 구문
DROP TABLE DEPT_TEST;
ROLLBACK;-어딘가에 참조되고 있는 부모테이블은 삭제 불가능
만약 삭제하고 싶다면,
1) 자식테이블을 먼저 삭제하고 부모테이블을 삭제한다.
DROP TABLE 자식테이블;
DROP TABLE 부모테이블;
2) 부모테이블만 삭제하는데 맞물려있는 제약조건도 함께 삭제한다.
DROP TABLE 부모테이블 CASCADE CONSTRAINT;
< DML : DATE MANIPULATION LANGAGE >
데이터 조작어
테이블에 새로운 데이터를 삽입(INSERT)하거나, 기존의 데이터를 수정(UPDATE)하거나,삭제(DELETE)하는 구문
* DML - CRUD
C(CREATE) : 추가 INSERT
R(READ) : 조회 SELECT
U(UPDATE) : 갱신 UPDATE
D(DELETE) : 삭제 DELETE
1. INSERT : 테이블에 행을 추가하는 구문
[표현법]
INSERT INTO 테이블명 VALUES(값, 값, 값 ...);
=> 해당테이블에 모든 컬럼에 값을 추가하고자 할 때 INSERT구문을 사용할 수 있음.
주의사항 : 컬럼 순번을 지켜서 VALUES괄호 안에 나열해야 함
INSERT INTO EMPLOYEE
VALUES (900, '박세*', '123123-1231234', 'park@clean.com', '01012345555', 'D1',
'J1', 'S4', 50000000, 0.1, NULL, SYSDATE, NULL, DEFAULT);1-2) INSERT INTO 테이블명(컬럼명1, 컬럼명2, 컬럼명3, ...) VALUES (값1, 값2, 값3, ...)
=> 해당 테이블에 특정 컬럼만 선택해서 그 컬럼에 추가할 값만 제시하고자 할 때 사용
한 행 단위로 추가가 되기 때문에 선택이 안된 컬럼은 기본적으로 NULL값이 들어감
(기본값 DEFAULT 지정 되어있을 경우 기본값이 들어감)
주의사항 : NOT NULL제약조건이 걸려있는 컬럼은 반드시 직접 값을 제시해야 함
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, SAL_LEVEL, HIRE_DATE)
VALUES (901, '김광*', '456456-4564567', 'D2', 'J2', 'S3', SYSDATE);1-3) INSERT INTO 테이블명(서브쿼리);
=> VALUES 로 값을 기입하는 것 대신에 서브쿼리로 조회한 결과값을 INSERT하는 구문
① 테이블 생성
CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);② 데이터 삽입
INSERT INTO EMP_01
(SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+));2. INSERT ALL : 두 개 이상의 테이블에 각각 INSERT할 때 사용, 그 때 사용되는 서브쿼리가 동일한 경우
- 새로운 테이블을 먼저 만들기
- 첫 번째 테이블 : 급여가 300만원 이상인 사원들의 사번, 사원명, 직급명에 대해 보관할 테이블
- 테이블 이름 : EMP_JOB
- 컬럼 : EMP_ID NUMBER, EMP_NAME VARCHAR2(30), JOB_NAME VARCHAR2(20)
CREATE TABLE EMP_JOB(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
JOB_NAME VARCHAR2(20)
);- 두 번째 테이블 : 급여가 300만원 이상인 사원들의 사번, 사원명, 부서명에 대해 보관할 테이블
- 테이블 이름 : EMP_DEPT
- 컬럼 : EMP_ID NUMBER, EMP_NAME VARCHAR2(30), DEPT_TITLE VARCHAR2(20)
CREATE TABLE EMP_DEPT(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);- 급여가 300만원 이상인 사번, 이름, 부서명, 직급명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE SALARY >= 3000000;[표현법]
INSERT ALL
INTO 테이블명1 VALUES(컬럼명, 컬럼명, 컬럼명)
INTO 테이블명2 VALUES(컬럼명, 컬럼명, 컬럼명)
서브쿼리;
- EMP_JOB 테이블에는 급여가 300만원 이상인 사원들의 EMP_ID, EMP_NAME, JOB_NAME
- EMP_DEPT 테이블에는 급여가 300만원 이상인 사원들의 EMP_ID, EMP_NAME, DEPT_TITLE
INSERT ALL
INTO EMP_JOB VALUES(EMP_ID, EMP_NAME, JOB_NAME)
INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_TITLE)
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
WHERE SALARY >= 3000000;- 실습) 사번, 사원명, 입사일, 급여 // EMP_OLE, EMP_NEW
- 구조만 복사
CREATE TABLE EMP_OLD
AS SELECT EMP_ID,EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE 0=1;
CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE 0=1;- INSERT ALL을 사용해 2010/01/01 이전에 입사한 사원은 EMP_OLD, 이후 입사한 사원은 EMP_NEW에 삽입
INSERT ALL
WHEN HIRE_DATE < '2010/01/01' THEN
INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2010/01/01' THEN
INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE;3. UPDATE : 테이블에 기록된 데이터를 수정하는 구문
[표현법]
UPDATE 테이블명
SET 컬럼명 = 바꿀값
,컬럼명 = 바꿀값
,컬럼명 = 바꿀값 => 여러개의 컬럼을 동시 변경 가능 (쉼표, 곡 사용하기)
WHERE 조건 ; => 생략이 가능, 생략 시 전체 모든 행의 데이터가 다 변경.> 그냥 꼭 사용하기
- 테이블 복사
CREATE TABLE DEPT_COPY
AS SELECT *
FROM DEPARTMENT;-부서코드가 D9인 부서명(총무부) 변경 전략기획부로 수정
UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획부'
WHERE DEPT_ID = 'D9';- 테이블복사2
CREATE TABLE EMP_SALARY
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
FROM EMPLOYEE;- EMP_SALARY테이블에 김광* 사원의 급여를 1000만원으로 변경
UPDATE EMP_SALARY
SET SALARY = 10000000
WHERE EMP_NAME = '김광*';- 전체사원의 급여를 기존급여에서 20% 인상한 금액으로 변경
UPDATE EMP_SALARY
SET SALARY = SALARY*1.2;3-1) UPDATE 시에 서브쿼리를 사용
: 서브쿼리를 수행한 결과값으로 UPDATE
[표현법]
UPDATE 테이블명
SET 컬럼명 = (서브쿼리)
WHERE 조건; => 생략 가능
- EMP_SALARY테이블에 김광* 사원의 부서코드를 박세* 사원의 부서코드로 변경
UPDATE EMP_SALARY
SET DEPT_CODE = (SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '박세*')
WHERE EMP_NAME = '김광*';- 방명수 사원의 급여와 보너스를 유재식 사원의 급여와 보너스 값으로 변경
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
FROM EMPLOYEE
WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';- UPDATE 사용 시 수정할 때 아무런 문제가 없을까?
- 송종기 사원의 사번을 200번으로 변경
- 제약조건을 떠올려보면,
UPDATE EMPLOYEE
SET EMP_ID = 200
WHERE EMP_NAME = '송종기'; --> PRIMARY KEY제약조건에 위배
UPDATE EMPLOYEE
SET EMP_NAME = NULL
WHERE EMP_NAME = '송종기'; --> NOT NULL 제약조건에 위배4. DELETE : 테이블에 기록된 데이터를 삭제하는 구문
[표현법]
DELETE FROM 테이블명
WHERE 조건; => WHERE 절은 생략가능. 생략 시 해당테이블의 전체 행 삭제
DELETE FROM EMPLOYEE;
SELECT * FROM EMPLOYEE;
ROLLBACK; --> 롤백 시 마지막 커밋시점으로 돌아감- 박세*, 김광* 사원의 데이터 지우기
DELETE FROM EMPLOYEE
WHERE EMP_NAME IN('김광진', '박세정');
COMMIT;--모든 변경사항을 확정 함- DEPARTMENT 테이블로부터 DEPT_ID가 D1인 부서 삭제
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1'; -- 이미 사용하고 있는 코드이기 때문에 오류 발생
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D3'; -- 사용하지 않는 부서는 정상적으로 삭제 됨4-1) TRUNCATE : 테이블의 전체 행을 삭제할 때 사용하는 구문(절삭)
DELETE보다 수행 속도가 더 빠름 / 별도의 조건 제시 불가, ROLLBACK 불가능
| [표현법] | |
| TRUNCATE TABLE 테이블명 | DELETE FROM 테이블명 |
| 별도의 조건 제시 불가 | 특정 조건 제시 가능 |
| 수행속도 빠름 (DELETE보다) | 수행 속도가 느림(TRUNCATE보다) |
| ROLLBACK 불가 | ROLLBACK가능 |
SELECT * FROM EMP_SALARY;
DELETE FROM EMP_SALARY; -- 0.041초
ROLLBACK;
TRUNCATE TABLE EMP_SALARY; -- 0.104초
--Table EMP_SALARY이(가) 잘렸습니다.
< DCL : DATA CONTROL LANGUAGE >
데이터 제어언어
GRANT, REVOKE
: 계정에게 객체 접근권한, 시스템 권한을 부여(GRANT)하더나, 회수(REVOKE)하는 언어
* 권한 부여(GRANT)
- 시스템권한 : 특정 DB에 접근한느 권한, 객체들을 생성할 수 있는 권한
- 객체 접근 권한 : 특정 객체들에 접근해서 조작할 수 있는 권한
[표현법]
GRANT 권한1, 권한2,...TO 계정명;
- CREATE SESSION : 계정에 접속할 수 있는 권한
- CREATE TABLE : 테이블을 생성할 수 있는 권한
- CREATE VIEW : 뷰를 생성할 수 있는 권한
- CREATE SEQUENCE : 시퀀스를 생성할 수 있는 권한
- CREATE USER : 계정을 생성할 수 있는 권한
....
1. SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;2. SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한 부여
GRANT CREATE SESSION TO SAMPLE;-- SAMPLE계정
CREATE TABLE TEST(
TEST_ID NUMBER
);- 3_1. SAMPLE계정에 테이블을 생성할 수 있는 권한이 없기 때문에 오류 발생
- ORA-01031: insufficient privileges
- SAMPLE계정에 테이블을 생성할 수 있는 CREATE TABLE권한 부여
GRANT CREATE TABLE TO SAMPLE;--SAMPLE계정
--CREATE TABLE 권한 부여 받은 후
CREATE TABLE TEST(
TEST_ID NUMBER
);- 3_2. TABLESPACE가 할당되지 않아서 오류 발생 -->데이터를 저장할 물리적 장소
- ORA-01950: no privileges on tablespace 'SYSTEM'
- SAMPLE계정에 테이블스페이스를 할당해주기(SYSTEM 계정 변경)
ALTER USER SAMPLE QUOTA 2M ON SYSTEM;--SAMPLE계정
--TABLE SPACE 할당 받은 후
CREATE TABLE TEST(
TEST_ID NUMBER
);-테이블 생성완료
테이블 생성 권한을 부여받게 되면, 계정이 '소유'하고 있는 테이블들을 조작하는 것도 가능
SELECT * FROM TEST;
INSERT INTO TEST VALUES(1);-뷰 만들어보기
CREATE VIEW V_TEST
AS SELECT * FROM TEST;4. 뷰 객체를 생성할 수 있는 CRATE VIEW권한이 없기 때문에 오류 발생
-ORA-01031: insufficient privileges
- SAMPLE계정에 뷰를 생성할 수 있는 CREATE VIEW 권한 부여
GRANT CREATE VIEW TO SAMPLE;--SAMPLE계정
CREATE VIEW V_TEST
AS SELECT * FROM TEST;
* 객체권한
특정객체를 조작(SELECT, INSERT, DELETE, UPDATE)할 수 있는 권한
[표현법]
GRANT 권한종류 ON 특정객체 TO 계정명;
[객체 권한의 종류]
SELECT | TABLE, VIEW, SEQUENCE
INSERT | TABLE, VIEW
UPDATE | TABLE, VIEW
DELETE | TABLE, VIEW
- SAMPLE 계정에서 KH계정의 테이블에 접근해서 조회해보기
SELECT *
FROM KH.EMPLOYEE;5. KH계정의 테이블에 접근해서 조회할 수 있는 권한이 없기 때문에 오류 발생
- ORA-00942: table or view does not exist
- SAMPLE계정에서 KH.EMPLOYEE테이블을 조회할 수 있는 권한 부여
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;--SAMPLE계정
SELECT *
FROM KH.EMPLOYEE;
--EMPLOYEE테이블 조회 성공!- SAMPLE 계정에서 KH계정의 테이블에 접근해서 행 삽입해보기
INSERT INTO KH.DEPARTMENT VALUES('D0', '회계부', 'L2');6. KH계정의 DEPARTMENT테이블에 접근할 수 있는 권한이 없기 때문에 오류
- SAMPLE계정에 KH.DEPARTMENT테이블에 행을 삽입할 수 있는 권한 부여
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;INSERT INTO KH.DEPARTMENT VALUES('D0', '회계부','L2');
--> 성공
ROLLBACK;* 권한 회수(REVOKE) : 권한을 회수할 때 사용하는 명령어
[표현법]
REVOKE 권한1, 권한2... FROM 사용자이름;
1. SAMPLE계정에서 테이블을 생성할 수 없도록 권한회수
REVOKE CREATE TABLE FROM SAMPLE;-만약 7실행 후 테이블 생성시
CREATE TABLE TEST2(
TEST_ID NUMBER
);
--7. SAMPLE계정에서 테이블을 생성할 수 없도록 권한을 회수했기 때문에 오류발생* GRANT CONNECT, RESOURCE TO 계정
-최소한의 권한을 부여하고자할 때 CONNECT, RESOURCE만 부여하면 된다.
CONNECT, RESOURCE도 테이블에 저장되어있음.
SELECT *
FROM R0LE_SYS_PRIVS
WHERE ROLE IN('CONNECT', 'RESOURCE');< 롤 ROLE >
- 특정 권한들을 하나의 집합으로 모아놓은 것
CONNECT : CREATE SESSION (데이터베이스에 접속할 수 있는 권한)
RESOURCE : CREATE TABLE, CREATE VIEW (특정 객체들을 생성 및 관리하는 권한)
- 실습) 사용자에게 부여할 권한 : CONNECT, RESOURCE
권한을 부여받을 사용자 : HAHA
권한 부여 후 USER 객체 삭제
CREATE USER HAHA IDENTIFIED BY HAHA;
GRANT CONNECT, RESOURCE TO HAHA;
DROP USER HAHA;< TCL : TRANSACTION CONTROL LANGUAGE >
* 트랜잭션(TRANSACTION)
- 작업단위
- 데이터베이스의 논리적 연산단위
- 데이터의 변경사항(DML)들을 하나의 트랜잭션으로 묶어서 처리
COMMIT(확정)하기 전까지는 변경사항들을 하나의 트랜잭션에 담는다.
- 트랜잭션에 대상이 되는 SQL : DML(INSERT, UPDATE, DELETE) * SELECT는 데이터에 영향을 미치지 않음 *
COMMIT(트랜잭션 종료처리 후 확정)
ROLLBACK (트랜잭션 취소)
SAVAPOINT (임시저장)
- 테이블명 : EMP_02
- EMPLOYEE 테이블 복사
CREATE TABLE EMP_02
AS SELECT * FROM EMPLOYEE;- 사번이 221인 사원 삭제
DELETE FROM EMP_02
WHERE EMP_ID = 221;- 사번이 220인 사원 삭제
DELETE FROM EMP_02
WHERE EMP_ID = 220;- 사번이 800, 이름 홍길동인 사원 추가
INSERT INTO EMP_02(EMP_NO, EMP_ID, EMP_NAME, JOB_CODE, SAL_LEVEL)
VALUES('11111-2222222', 800, '홍길동', 'J9', 'S1');- @주의 : NOT NULL == EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, SAL_LEVEL
COMMIT; --> 확정
SELECT * FROM EMP_02;
ROLLBACK; --> 마지막 커밋으로 돌아감
SELECT * FROM EMP_02;- 사번이 217, 216, 214인 사원 삭제
DELETE FROM EMP_02
WHERE EMP_ID IN(217, 216, 214);-3개행이 삭제된 시점에서 SAVEPOINT 지정
SAVEPOINT SP1;- 사번이 200인 사원 삭제
DELETE FROM EMP_02
WHERE EMP_ID = 200;- SAVEPOINT로 롤백 시,
ROLLBACK TO SP1;
COMMIT;-> SP1 로 돌아감 200인 사원 삭제는 적용되지 않음
-사번이 800인 사원 없애기
DELETE FROM EMP_02
WHERE EMP_ID = 800;-테이블생성(DDL)
CREATE TABLE TEST(
TID NUMBER
);
ROLLBACK; DDL구문(CREATE, ALTER, DROP) 을 실행하는 순간
기존에 트랜잭션에 있는 모든 변경사항들을 무조건 실제 DB에 반영(COMMIT)
그 다음에 DDL이 수행됨
=> DDL수행 전 변경사항이 있다면 정확히 fix(COMMIT, ROLLBACK)하고 DDL을 수행해야 함
COMMIT : 하나의 트랜잭션에 담겨있는 변경사항들(DML)을 실제 DB에 반영하겠다.
실제 반영시킨 후 트랜잭션은 비워짐
ROLLBACK : 하나의 트랜잭션에 담겨있는 변경사항들(DML)을 삭제한 후
마지막 COMMIT 시점으로 돌아감
SAVEPOINT : 지금 시점 임시저장점 정의
-정리해보면,
DDL : CREATE(만들고), ALTER(고치고), DROP(버리고)
DML : SELECT(조회) / INSERT(삽입), UPDATE(갱신), DELETE(삭제) *************
DCL : GRANT(주기), REVOKE(뺏기)
TCL : COMMIT(확정), ROLLBACK(돌아가기), SAVEPOINT(중간저장) ************ DML을 하면 무조건 COMMIT/ROLLBACK해야함