ON DELETE RESTRICTED -> 다른테이블에서 참조하고 있는 경우 삭제가 안된다.
ON DELETE SET NULL -> 참조하고 있는 테이블에서 데이터가 NULL로 변경된다.
-> CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 AS 절을 추가하여 EMP 테이블과 동일한 내용과 구조를 갖는 테이블이 생성가능하다.
-> 서브쿼리문의 SELECT 절에 *대신 원하는 컬럼명을 명시하면 기존 테이블에서 일부의 컬럼만 복사 할 수 있다.
-> 서브 쿼리문의 SELECT 문을 구성할 때 WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사한다.
-> 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아니다. 이 역시 서브쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는
조건을 지정하게 되면 테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 됩니다.
-> WHERE 1=0; 조건은 항상 거짓입니다. 이를 이용하여 테이블의 데이터는 가져오지 않고 구조만 복사하게 된다.
모든컬럼에 대해 순서대로 넣어줘야한다.
--두번째 같은경우엔 컬럼명 입력할경우 입력한 컬럼명에만 값이 들어간다.
가운데 2개는 null로 비워진다.
-> 대신에 사용하는 쿼리문의 테이블이랑 조건절이 같아야 한다.
조건의 값이 없으면 INSERT를 한다.
-> 완전 삭제할때 쓰게 된다.
=================================================================================
=================================================================================
DELETE FROM SHOP_MEMBER WHERE USER_ID='user01';
--ON DELETE SET NULL -> 참조하고 있는 테이블에서 데이터가 NULL로 변경된다. 이거써보겠다.
CREATE TABLE SHOP_BUY2(
BUY_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20),
PRODUCT_NAME VARCHAR2(20),
REG_DATE DATE,
FOREIGN KEY (USER_ID) REFERENCES SHOP_MEMBER (USER_ID) ON DELETE SET NULL
);--기본값으로 쓸려고 안넣었는데 이번엔 제약조건 하나 더 넣었다. 참조하고있는데이터가 삭제되면
--내값을 NULL로바꿀꺼야 라는 설정이다.
INSERT INTO SHOP_BUY2 VALUES(1,'user04','축구화',SYSDATE);
--user04로해야한다.,user01,02,03은 sho_buy에서 쓰고있어서 안된다.
select * from shop_buy2;
select * from shop_member;
DELETE FROM SHOP_MEMBER WHERE USER_ID='user04';
select * from shop_member; --04가 지워졌다.
select * from shop_buy2;
--ON DELETE CASCADE -> 참조하고 있는 테이블의 데이터가 삭제되면 내테이블 값도 삭제
CREATE TABLE SHOP_BUY3(
BUY_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20),
PRODUCT_NAME VARCHAR2(20),
REG_DATE DATE,
FOREIGN KEY (USER_ID) REFERENCES SHOP_MEMBER (USER_ID) ON DELETE CASCADE
);
INSERT INTO SHOP_BUY3 VALUES(1,'user05','축구화',SYSDATE);
select * from shop_buy3;
DELETE FROM SHOP_MEMBER WHERE USER_ID='user05';
select * from shop_buy3; -- 값이 사라졌다. 참조하고있는데이터에서 그값을 지워버렸는데 CASCADE로만들어서
--이건 바로 삭제가 되어버렸다. NULL로바뀌는건 SET NULL이다.
CREATE TABLE USER_CHECK(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10) CHECK (GENDER IN('남','여')) --남 여 아니면 값이 안들어가진다.
);
INSERT INTO USER_CHECK VALUES(1,'user01','pass01','홍길동','남자');
--에러가 날것이다.무슨 에러냐면 아까 남이나 여로만 값을 집어넣을수 있게 해놓았다.
INSERT INTO USER_CHECK VALUES(1,'user01','pass01','홍길동','남');
select * from USER_CHECK;
SELECT * from employee;
select * from job;
select * from department;
--EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME 출력하기
select EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
from employee
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE);
--테이블을 손쉽게 만든것이다. 복사이다.
--원하는 컬럼으로 구성된 복제 테이블 생성하기
CREATE TABLE EMPLOYEE_COPY AS
select EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
from employee
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE);
select * from employee_copy;
--drop table employee_copy CASCADE CONSTRAINT;
--테이블의 구조만 복사하기,WHERE 1=0; 조건은 항상 거짓입니다. 이를 이용하여 테이블의 데이터는 가져오지 않고 구조만 복사하게 된다.
--where 1=0조건은 항상 거짓이라 테이블의 데이터는 가져오지않고 구조만 복사
CREATE TABLE EMPLOYEE_COPY2 AS
select EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
from employee
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE)
where 1=0;
select * from employee_copy2;
CREATE TABLE TESTTBL(
DATA1 NUMBER DEFAULT 0,
DATA2 NUMBER
);
INSERT INTO TESTTBL VALUES(1,1); --잘 들어간다.
select * from TESTTBL;
INSERT INTO TESTTBL VALUES(DEFAULT,0);--이거일경우 아까 테이블 만들때 DEFAULT 0으로 줬기때문에 0이들어가진다.
select * from TESTTBL;
INSERT INTO TESTTBL VALUES(2);--DEFAULT라도 값을 써줘야들어가지 없으면 값이 안들어가진다.
--------------------------------------------------------------------------------
/*
@ DML (데이터 조작어)
-> 데이터를 조작하기 위해 사용
-> 데이터의 삽입, 수정, 삭제, 조회
-> 사용자랑 시스템에서 가장 많이 사용
-> INSERT(데이터 삽입), UPDATE(데이터 수정), DELETE(데이터 삭제), SELECT(데이터 조회)
@ INSERT
-> 테이블에 새로운 행을 추가하는 구문
-> 테이블 행갯수가 증가
INSERT INTO [테이블명] VALUES (1,2,3,4,5,6);
INSERT INTO [테이블명] (컬럼명1,컬럼명2,....) VALUES (1,2,3,4,5,6);
--숫자들어가고 날짜들어가고 등등 있다면 첫번째 형식같은경우 컬럼명 지정안해줬기때문에
모든컬럼에 대해 순서대로 넣어줘야한다.
--두번째 같은경우엔 컬럼명 입력할경우 입력한 컬럼명에만 값이 들어간다.
가운데 2개는 null로 비워진다.
*/
--첫번째 형식 컬럼 다 넣고 한 것이다.
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE, JOB_CODE,
SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE, ENT_DATE, ENT_YN)
VALUES
(999, '김종규','930817-1111111','jk@naver.com','01099960865','D2','J8',
'S1',10,0,'200',sysdate,default,default);
select * from employee where emp_id=999;
--첫번째 형식 컬럼 다 넣고 한 것이다.하나씩 들어간걸 해본것이다.
--컬럼명을 안적을 경우 모든 순서대로 다 넣어줘야한다.
INSERT INTO EMPLOYEE VALUES
(998, '시진','741130-1111111','jk@naver.com','01099960865','D2','J8',
'S1',10,0,'200',sysdate,default,default);
select * from employee where emp_id in (997,998,999);
--컬럼값을 지정해준상태에서 value값을 넣어준다면 상관이 없다. 어떤값은 빠져도된다.
--테이블명을 적은것과 안적은것의 차이
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, JOB_CODE,
SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE, ENT_DATE, ENT_YN)
VALUES
(997, 'adf','930211-1111111','jk@naver.com','01099960865','J8',
'S1',10,0,'200',sysdate,default,default);
select * from employee where emp_id in (997,998,999);
--그후 집어넣은값 다 삭제하기
delete from employee where emp_id=997;
delete from employee where emp_id=998;
delete from employee where emp_id=999;
--테이블 만들어보겠다.
CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);
select * from EMP_01;
--이렇게 값을 넣을텐데 - 실행x
INSERT INTO EMP_01 VALUES(1,'홍길동','기술지원부');
--
select EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
--원하는 서브쿼리로 테이블 생성하기
INSERT INTO EMP_01(
select EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
);
--확인하기
select * from EMP_01;
/*
@ INSERT ALL
-> 쿼리문을 통해서 데이터 삽입을 하는 경우
-> 두개 이상의 테이블에 INSERT ALL을 통해서 한번에 데이터삽입가능
-> 대신에 사용하는 쿼리문의 테이블이랑 조건절이 같아야 한다.
*/
--테이블 만들기(테이블 구조만 복사하기)
CREATE TABLE EMP_DEPT_D1
AS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE 1=0; -->테이블 구조만 복사하기 위한 조건
select * from EMP_DEPT_D1;
--테이블 만들기(테이블 구조만 복사하기)
CREATE TABLE EMP_MANAGER
AS
SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
WHERE 1=0;
select * from EMP_MANAGER;
/*
EX)
EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에서 부서코드가D1인 직원을 조회해 사번,이름,소속부서,
입사일을 삽입하고
EMP_MANAGER 테이블에 EMPLOYEE 테이블에서 부서코드가 D1인 직원을 조회해 사번, 이름, 관리자
사번을 조회해서 한번에 삽입
--보기만하기
*/
--보기만하기 적용X,이렇게 나온걸 EMP_DEPT_D1에 넣을것이다.
select EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE='D1';
--적용X
/*(EMP_DEPT_D1 테이블에 EMPLOYEE 테이블에서 부서코드가D1인 직원을 조회해 사번,이름,소속부서,
입사일을 삽입하고)
*/
--적용하지말기
INSERT INTO EMP_DEPT_D1(
select EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE='D1';
);
select EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
WHERE 1=0;
/*
EMP_MANAGER 테이블에 EMPLOYEE 테이블에서 부서코드가 D1인 직원을 조회해 사번, 이름, 관리자
사번을 조회해서 한번에 삽입
*/
--적용하지는 말기
INSERT INTO EMP_MANAGER(
select EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE='D1';
);
/*
@ INSERT ALL
-> 쿼리문을 통해서 데이터 삽입을 하는 경우
-> 두개 이상의 테이블에 INSERT ALL을 통해서 한번에 데이터삽입가능
-> 대신에 사용하는 쿼리문의 테이블이랑 조건절이 같아야 한다.
*/
--이렇게 보자면 조회하는 테이블과 조건절이 같으니 INSERTALL을쓰면된다.
INSERT ALL
INTO EMP_DEPT_D1 VALUES(EMP_ID,EMP_NAME,DEPT_CODE,HIRE_DATE)
INTO EMP_MANAGER VALUES(EMP_ID,EMP_NAME,MANAGER_ID)
SELECT EMP_ID,EMP_NAME,DEPT_CODE,HIRE_DATE,MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE='D1';
--조건은 테이블명이 같아아햐고 조건절이 같아야한다.
--쓸일이 많지는 않다.
select * from EMP_DEPT_D1;
select * from EMP_MANAGER;
--------------------------------------------------
/*
@ UPDATE
-> 테이블에 기록된 컬럼의 값을 수정하는 구문
-> 테이릅 전체 행의 갯수에는 변화가 없음
*/
CREATE TABLE DEPT_COPY
AS
SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_COPY;
--D9를 전략기획팀으로 바꿔
UPDATE DEPT_COPY SET DEPT_TITLE='전략기획팀'
WHERE DEPT_ID='D9';
--바뀌었다는 것이다.
SELECT * FROM DEPT_COPY;
/*
EX) 방명수사원의 급여와 보너스율을 유재식동일하게 변경을 해보겠다.
유재식 사원의 급여,보너스율 죄한다음 그값으로 업데이트 하면 된다.
*/
--원래방식
SELECT SALARY, BONUS FROM EMPLOYEE WHERE EMP_NAME='유재식';
--원래는 이런식으로 적용하면되는데(적용하지말기)굳이이렇게?
UPDATE EMPLOYEE
SET SALARY = 3400000,
BONUS=0.2
WHERE EMP_NAME='방명수';
--서브쿼리로 만들어서 적용해보기
UPDATE EMPLOYEE
SET SALARY = (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='유재식'),
BONUS=(SELECT BONUS FROM EMPLOYEE WHERE EMP_NAME='유재식')
WHERE EMP_NAME='방명수';
--조건넣고 쿼리문넣고 그렇게 해서 끝낸것이다.
--확인해보기
select EMP_NAME, SALARY, BONUS FROM EMPLOYEE
WHERE EMP_NAME='유재식' or EMP_NAME='방명수';
--그럼 같아진걸 볼 수 있다.
/*
@MERGE
-> 구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능
-> 두테이블에서 지정하는 조건의 값이 존재하면 UPDATE 또는 DELETE를 하고,
조건의 값이 없으면 INSERT를 한다.
--원본데이터 수정하면 안되니 새로운데이터 삽입하겠다.
*/
CREATE TABLE M_TEST01 (
ID CHAR(20),
NAME VARCHAR2 (20)
);
INSERT INTO M_TEST01 VALUES('USER11','김종규');
INSERT INTO M_TEST01 VALUES('USER22','최종규');
INSERT INTO M_TEST01 VALUES('USER33','박종규');
SELECT * FROM M_TEST01;
CREATE TABLE M_TEST02 (
ID CHAR(20),
NAME VARCHAR2 (20)
);
INSERT INTO M_TEST02 VALUES('USER12','종규');
INSERT INTO M_TEST02 VALUES('USER22','종규');
INSERT INTO M_TEST02 VALUES('USER32','종규');
--확인
select * from M_TESt02;
--UNION써가지고 합쳐서 한 것이다.
select * from M_TESt02
UNION
SELECT * from M_TEST01 ORDER BY 2;
/*
@MERGE
-> 구조가 같은 두개의 테이블을 하나의 테이블로 합치는 기능
-> 두테이블에서 지정하는 조건의 값이 존재하면 UPDATE 또는 DELETE를 하고,
조건의 값이 없으면 INSERT를 한다.
*/
SELECT * FROM M_TEST01;
SELECT * FROM M_TEST02;
--이게 조건이다.
MERGE INTO M_TEST01 USING M_TEST02 ON(M_TEST01.ID=M_TEST02.ID)
WHEN MATCHED THEN UPDATE SET M_TEST01.NAME=M_TEST02.NAME --위의 ON이 참이면 업데이트
WHEN NOT MATCHED THEN--위의 ON이 거짓이면
INSERT VALUES(M_TEST02.ID,M_TEST02.NAME); --INSERT실행
/*
M_TEST01과 M_test02의 값이 같은경우 이게 조건이다.
TEST01의 ID과 TEST02의 ID가 같은경우엔 UPDATe SET을 하고 01번
네임을 02번네임으로 수정해버리겠다.
없는 경우엔 삽입을 해버리겠다.
TEST01테이블에 MERGE를 할껀데 TEST02랑 MERGE를 할꺼야
아이디가 같으면 있으면 TEST01꺼를 TEST02값으로 바꾸고
같은게 없으면 새로값을 넣어줄께 M_TEST02에다가 넣어줄꺼야
겹치는게 있으면 UPDATE할 것이다.
*/
--그후 확인 user22가 같기때문에 바꿔버린 것이다.
select * from M_TESt02
UNION
SELECT * from M_TEST01 ORDER BY 2;
select * from M_TEST01;
select * from M_TEST02;
/*
@DELETE
-> 테이블의 행을 삭제하는 구문
-> 테이블의 행의 갯수가 줄어듬
-> DELETE시에 조건문을 사용하지 않게되면 TABLE전체 데이터삭제 될 수 있음
-> 외래키 제약조건이 설정되어 있으면 삭제 안되는 경우 있음
*/
SELECT * FROM M_TEST01;
DELETE FROM M_TEST01 WHERE ID='USER22';
SELECT * FROM M_TEST01;
--조건문을 안적으면 다 지워진다 테이블은 있을 것이고 이건 지워진다.
/*
@TRUNCATE
-> 테이블 전체 행 삭제시 사용
-> DELETE보다 수행속도가 빠름
-> 복구가 안됨
-> 외래키 제약조건이 있는경우 적용이 안됨
-> 완전 삭제할때만 쓰게된다고 생각해라.
*/
select * from M_TEST02;
--3개 행 지워버렸다.
DELETE FROM M_TEST02;
select * from M_TEST02;
--DEPT_COPY를 검색해보자.
SELECT * FROM DEPT_COPY;
--이건 삭제가 아니라 잘려버리게 된다.
TRUNCATE TABLE DEPT_COPY;
-------------------------------------------------------------------
