테이블에 내용을 추가INSERT, 수정UPDATE, 삭제DELETE하기 위한 DML
| 고객코드 | 고정 문자열 7자리, 공백 허용하지 않음, 기본키 적용 |
| 고객명 | 가변 문자열 15자리(한글은 5글자), 동백 허용하지 않음 |
| 성별 | 고정 문자열 1자리, 공백허용하지 않음.M(남성), W(여성) |
| 생일 | 고정 문자열 8자리, 공백 허용하지 않음 |
| 전화번호 | 가변 문자열 15자리, 공백 허용 |
| 이메일 | 가변 문자열 50자리, 공백 허용 |
| 누적포인트 | 숫자 10자리, 소수점 이하 없음 |
PRIMARY KEY는 무조건 NOT NULL
고객 테이블 구조
테이블명: TB_CUSTOMER
기본키 설정방법: 컬럼명 자료형 PRIMARY KEY
TABLE 생성시
컬럼명 자료형 NULL/NOT NULL PRIMARY KEY,
컬럼명 자료형 NULL/NOT NULL
nVARCHAR2(n)
없으면 고정길이, 있으면 가변길이
없으면 200BYTE, 있으면 400BYTE
크기
없으면 영문 1BYTE 한글 3BYTE 크기를 BYTE 수 단위로 받음, 있으면 유니코드 문자형 모든 문자 2BYTE 크기를 글자의 단위로 받음
문자니까 CHAR필수
1) 테이블에 새로운 행을 추가하는 INSERT문
| 특정한 컬럼에만 DATA를 입력 | 모든 컬럼에 DATA를 입력 |
| INSERT INTO table name(column_value, ...) VALUES(column_value,...) |
INSERT INTO table_name VALUES(column_value,...) |
새로운 행을 추가하기 위해 insert문을 사용하면 한 번에 하나의 행만 삽입된다. 작성한 칼럼 목록 순서대로 VALUES에 지정된 값이 삽입된다.
날짜->' '
숫자-> 그대로
문자-> 자바 CHAR(' ')/String(" ")
오라클 CHAR/VARCHAR2->' ', 별칭->" "
작업단위를 짧게 잡아 COMMIT
INSERT 구문에서 오류 발생의 예
- 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호 안에 기술한 값의 개수가 적으면 에러가 발생.
- 칼럼명에 기술된 목록의 수보다 VALUES 다음에 나오는 괄호에 기술한 값의 개수가 많으면 에러가 발생.
- 칼럼명이 잘못 입력되었을 때
- 칼럼과 입력할 값의 데이터 타입이 서로 맞지 않은 경우에도 에러가 발생한다.
1>칼럼명을 생략한 INSERT 구문
모든 칼럼에 자료를 입력한 경우에는 굳이 칼럼 목록을 기술하지 않아도 됨.
단, 디버그 시 용이하지 않으므로 칼럼명을 써 주는것이 좋다.
INSERT INTO DEPT
VALUES(20, 'RESEARCH', 'DALLAS');NULL은 암시적으로 들어가게 하거나 컬럼 생략시 명시적으로 입력해 줘야 한다.
NULL 값을 갖는 칼럼을 추가하기 위해 NULL 대신 ''를 사용 가능=> NOT NULL일 때 '' 불가능(가장 많이 만나는 오류)
--암시적으로 NULL값 삽입
INSERT INTO DEPT(DEPTNO, DNAME)
VALUES(30, 'SALES');--명시적
INSERT INTO DEPT
VALUES(40, 'OPERATIONS',NULL);
--DEPT 테이블의 전체 레코드 삭제
DELETE FROM DEPT;
--DEPT 테이블 삭제
DROP 사용1. 레코드는 가져오지 않고 기존 테이블의 컬럼만 가져오게 구조 복사
CREATE TABLE 테이블
AS
SELECT 컬럼명, 컬럼명 FROM 테이블 WHERE 1=0;
2. 기존 테이블에 존재하는 데이터를 다른 테이블에 입력할 때
INSERT INTO table_name(column1, column2,...)
SELECT column1, column2,...FROM table_name WHERE 조건;
2>테이블의 내용을 수정하기 위한 UPDATE 문
UPDATE table_name
SET column_name1 = value1, column_name2 = value2,...
WHERE conditions; //where절을 빼먹으면 다 업데이트 된다. 주의
1. 테이블의 모든 행 변경
--입사일을 오늘로 수정
UPDATE EMP
SET HIRE_DATE=SYSDATE;
--SYSDATE: 현재 날짜2. 테이블의 특정 행만 변경
SUBSTR(SUBSTRING): 잘라와라
SELECT * FROM EMP WHERE SUBSTR(HIRE_DATE, 1, 2)='08'; --첫번째부터 2개를 잘라와라, 0번째부터X
3.테이블에서 2개 이상의 칼럼 값 변경
테이블에서 하나의 컬럼이 아닌 복수 개의 칼럼 값을 변경하려면 기존 SET절에 콤마를 추가하고 칼럼=값을 추가 하면 된다.
--LAST_NAME 이 Russell인 사원의 급여를 17000로, 커미션 비율이 0.45로 인상된다.
SELECT*
FROM EMP
WHERE LAST_NAME='Russell';
UPDATE EMP
SET SALARY=17000, COMMISSION_PCT=0.45
WHERE LAST_NAME='Russell';
3>테이블에 불필요한 행(레코드)를 삭제하기 위한 DELETE문
테이블에 특정 로우(행)의 데이터를 삭제
--새로운 테이블 생성한 후 30번 부서를 삭제
DELETE FROM DEPT WHERE DEPTNO=30;
DML
INSERT INTO 테이블명(컬럼, 컬럼,...)
VALUES(값, 값,...)
--EMP01테이블에 EMPLOYEES 테이블에서 부서코드가 30인 직원의
--사번, 이름, 소속부서, 입사일을 삽입.
--EMP_MANAGER테이블에 EMPLOYEES 테이블의 부서코드가 30인 직원의
--사번, 이름, 관리자 사번을 조회하여 삽입.
--두개 이상의 테이블에 INSERT ALL(마지막에 SELECT가 있어야함)을 이용하여 한번에 삽입 가능
--단, 각 서브쿼리의 조건절이 같아야함
INSERT ALL
INTO EMP01
VALUES(EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE)
INTO EMP_MANAGER
VALUES(EMPLOYEE_ID, FIRST_NAME, MANAGER_ID)
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, HIRE_DATE, MANAGER_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;
SELECT * FROM EMP01;
SELECT * FROM EMP_MANAGER;-- EMPLOYEES 테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 저장할 수 있는
-- 테이블 EMP_OLD와 EMP_NEW 생성
CREATE TABLE EMP_OLD
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;
CREATE TABLE EMP_NEW
AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE 1=0;
-- EMPLOYEES 테이블의 입사일 기준으로 2006년 1월 1일 이전에 입사한 사원의 사번, 이름,
-- 입사일, 급여를 조회해서 EMP_OLD 테이블에 삽입하고 그 후에 입사한 사원의 정보는 EMP_NEW 테이블에 삽입
--INSERT ALL INTO EMP_OLD
--VALUES (EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
--SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
--FROM EMPLOYEES WHERE HIRE_DATE <='06.01.01';
INSERT ALL
WHEN HIRE_DATE< '2006/01/01' THEN
INTO EMP_OLD
VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= '2006/01/01' THEN
INTO EMP_NEW
VALUES(EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY)
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES;4>MERGE문
조건을 비교해서 테이블에 해당 조건에 맞는 데이터가 없으면 INSERT문, 있으면 UPDATE를 수행하는 문장
MERGE INTO table_name
USING(update나 insert 될 데이터 원천)
ON(update될 조건)
WHEN MATCHED
MERGE INTO TB_CUSTOMER CU --CU별칭
USING TB_ADD_CUSTOMER NC --NC 별칭
ON (CU.CUSTOMER_CD = NC.CUSTOMER_CD)
WHEN MATCHED THEN
UPDATE SET CU.CUSTOMER_NM = NC.CUSTOMER_NM,
CU.MW_FLG= NC.MW_FLG,
CU.BIRTH_DAY = NC.BIRTH_DAY,
CU.PHONE_NUMBER=NC.PHONE_NUMBER
WHEN NOT MATCHED THEN
INSERT (CU.CUSTOMER_CD, CU.CUSTOMER_NM, CU.MW_FLG, CU.BIRTH_DAY,
CU.PHONE_NUMBER, CU.EMAIL, CU.TOTAL_POINT, CU.REG_DTTM)
VALUES (NC.CUSTOMER_CD, NC.CUSTOMER_NM, NC.MW_FLG, NC.BIRTH_DAY,
NC.PHONE_NUMBER, '', 0, TO_CHAR(SYSDATE, 'YYYYMMDDHHMISS'));
SELECT * FROM TB_CUSTOMER;