[2022.10.14.금] DML (데이터 조회, 정렬, 추가, 변경, 삭제)
1. DML
- 데이터 조작어다.
- 데이터의 추가, 조회, 변경, 삭제 명령어다.
1-1. 데이터 조회(SELECT)
1) 데이터조회
- 테이블의 모든 행, 모든 컬럼 조회
SELECT *
FROM 테이블명;
* 각각의 열은 이름을 갖고 있음 (Employee 이름, Employee 아이디 등 )
* 각각의 열은 데이터타입을 갖고 있음
NUMBER(4,0) -> 정수 4자리 ( -9999~9999 )
NUMBER(2,2) -> 정수부 2자리, 소수부 2자리 (xx.xx)
VARCHAR2(10 BYTE) -> 가변적인 크기의 문자
CHAR(2 BYTE) -> 고정적인 크기의 문자 (한글자 X)
(ex 8자리인데 'AB'두개를 저장할 때
VARCHAR2(8 BYTE) ---> 'AB'
CHAR(8 BYTE) ---> 'AB ')
( *한글은 한글자에 3byte )
- 테이블의 특정 컬럼 조회
SELECT 컬럼명, 컬럼명, ...
FROM 테이블명;
- 산술식 사용하기
- select절, where절, group by절, having절 등에서 산술연산자를 사용할 수 있다.
- 숫자와 날짜 데이터를 대상으로 만 사용할 수 있다.
- 별칭 사용하기
- select절과 from 절에서는 별칭을 정의할 수 있다.
- 별칭이 특수문자, 공백을 포함하고 있을 때는 " "안에 적는다.
-- jobs 테이블의 모든 행, 모든 컬럼의 값 조회하기
select *
from jobs;
-- job_history 테이블의 모든 행, 모든 컬럼의 값 조회하기
select *
from job_history;
-- jobs 테이블의 모든 행 조회. 직종아이디와 최저급여, 최고급여 컬럼의 값만 조회하기
select job_id, min_salary, max_salary
from jobs;
-- employees 테이블의 모든 행 조회. 직원아이디, 이름(first_name), 전화번호 컬럼의 값만 조회
select employee_id, first_name, phone_number
from employees;
-- jobs 테이블의 모든 행 조회. 직종아이디, 최고급여, 최소급여, 최고급여와 최소급여의 차이 조회하기
select job_id, max_salary, min_salary, max_salary - min_salary
from jobs;
-- employees 테이블의 모든 행 조회. 직원아이디, 이름, 급여, 연봉(연봉은 급여에 12를 곱한 값) 조회하기
select employee_id, first_name, salary, salary*12
from employees;
-- 조회하는 컬럼명에 별칭 사용하기
-- jobs 테이블의 모든 행 조회. 직종아이디, 최고급여, 최소급여, 최고급여와 최소급여의 차이(salary_gap) 조회하기
select job_id, max_salary, min_salary, max_salary - min_salary as salary_gap
from jobs;
-- departments 테이블의 모든 행, 모든 컬럼을 조회하고, 각 컬럼에 별칭 붙이기
select department_id 아이디, department_name 부서명, manager_id 관리자, location_id 소재지아이디
from departments;
* ER 다이어그램 (Entity Relation)
파란색 글씨 -> 그 테이블의 겹치지 않는 고유한 속성
이름이 같아도, 완전히 같은 값을 갖고 있지 않음
EMPLOYEES 의 job_id ≠ JOBS의 job_id
EMPLOYEES의 manager_id ≠ DEPARTMENTS의 manager_id (이름만 같은거임!)
JOB_HISTORY의 employee_id와 start_date를 합쳤을 때 고유한 속성이 되는 것
(employee_id만 보면 겹치는게 있을 수도 있으니까 start_date까지 합쳐야 겹치지 않음)
삼발이가 있는 쪽이 자식테이블
COUNTRIES의 region_id는 REGIONS의 고유속성인 region_id를 참조해
-> CONTRIES는 REGIONS를 참조함
-> CONTRIES가 자식객체, REGIONS가 부모테이블
서로 삼발이가 있는 경우
EMPLOYEES의 department_id를 기준으로 보면
-> DEPARTMENTS의 department_id를 참조하고 있으니까
-> DEPARTMENTS가 부모, EMPLOYEES가 자식
DEPARTMENTS의 manager_id를 기준으로 보면
-> EMPLOYEES의 employee_id를 참조하고 있으니까
-> EMPLOYEES가 부모, DEPARTMENTS가 자식
2) 행의 제한 (데이터 필터링)
- WHERE 절을 사용한다.
- 제시된 조건식을 만족하는 행만 조회된다
SELECT 컬럼명, 컬럼명,....
FROM 테이블
WHERE 조건식;
-- employees 테이블에서 급여 12000불 이상 받은 직원의 아이디, 이름, 직종아이디, 급여 조회하기
select employee_id, first_name, job_id, salary
from employees
where salary >= 12000;
-- employees 테이블에서 직종아이디가 it_prog인 직원의 아이디, 이름, 입사일, 급여 조회하기
select employee_id, first_name, hire_date, salary
from employees
where job_id = 'IT_PROG'; -- 얘는 대문자로 꼭 써야함!
-- employees 테이블에서 100번 직원에게 보고하는 직원의 아이디, 이름, 직종 조회하기
-- ( = employees 테이블에서 manager_id가 100인 직원의 아이디, 이름, 직종 조회하기 )
select employee_id, first_name, job_id
from employees
where manager_id = 100;
- 논리연산자
- 두 개이상의 조건식으로 데이터를 제한할 수 있다.
- AND, OR, NOT
-- employees 테이블에서 부서가 50이고 급여를 7500 이상 받는 직원의 아이디, 이름, 직종, 급여
select employee_id, first_name, job_id, salary
from employees
where department_id = 50 and salary >= 7500;
-- employees 테이블에서 급여를 7500 이상, 10000 미만으로 받는 직원의 아이디, 이름, 직종, 급여
select employee_id, first_name, job_id, salary
from employees
where salary >= 7500 and salary < 10000;
-- employees 테이블에서 커미션을 받는 직원 중에서 급여를 7500 이상, 10000 미만으로 받는 직원의 아이디, 이름, 급여, 커미션
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null and salary >= 7500 and salary < 10000;
-- employees 테이블에서 급여를 5000 미만으로 받거나 10000 이상 받는 직원의 아이디, 이름, 급여
select employee_id, first_name, salary
from employees
where salary < 5000 or salary >= 10000;
-- employees 테이블에서 급여가 7000 이하이고, 소속부서가 50번이거나 80번인 사원의 아이디, 이름 , 급여, 부서아이디
select employee_id, first_name, salary, department_id
from employees
where salary <= 7000 and (department_id = 50 or department_id = 80);
- null 연산자
- is null, is not null
- 자바의 null : 참조변수가 객체를 참조하지 못하게 하는 값
참조변수가 참조하는 객체가 없을 때 가지는 값
참조변수만 null 값을 가질 수 있다. - 데이터베이스의 null : 아직 값이 결정되지 않았다.
null이 포함된 산술 연산의 결과는 항상 null이다.
null은 비교 연산자 ( !=, = )로 비교할 수 없다. ( 아직 값이 아니기 때문에 )
컬럼의 데이터타입과 상관없이 null을 가질 수 있다.
- 자바의 null : 참조변수가 객체를 참조하지 못하게 하는 값
- is null, is not null
-- department 테이블에서 manager_id가 null인 부서의 부서아이디, 부서이름 조회하기
select department_id, department_name
from departments
where manager_id is null; -- 컬럼명 is null : 지정된 컬럼의 값이 null이면 연산결과가 true
-- department 테이블에서 manager_id가 null이 아닌 부서의 부서아이디, 부서이름 조회하기
select department_id, department_name
from departments
where manager_id is not null; -- 컬럼명 is not null : 지정된 컬럼의 값이 null이 아니면 연산결과가 true
-- employees 테이블에서 소속부서가 아직 결정되지 않은 직원의 아이디, 이름, 입사일 조회하기
-- ( = department_id 가 null인 직원 )
select employee_id, first_name, hire_date
from employees
where department_id is null;
- 기타 연산자
- BETWEEN 하한값 AND 상한값
- 컬럼의 값이 하한값과 상한값 범위내면 true다.
- IN (값, 값, 값, ...)
- 컬럼의 값이 제시된 값들 중 하나와 일치하면 true다.
- LIKE '패턴'
- 컬럼의 값이 제시된 패턴과 일치하면 true다.
- 패턴문자
% : 0개 이상의 일련의 임의의 문자
_ : 임의의 문자 하나 - '이%' : '이'로 시작하는 모든 문자를 true로 판정
where user_name like '이%'; '이', '이상', '이순신'
'이_ _' : '이'로 시작하는 3글자로 된 문자를 true로 판정
where user_name like '이_'; '이순신', '이성계'
'%자바%' : '자바'를 포함하고 있는 모든 문자를 true로 판정
where book_title like '%자바%'; '자바의 정석', '이것이 자바다', '혼자서 공부하는 자바'
- 컬럼의 값이 제시된 패턴과 일치하면 true다.
- BETWEEN 하한값 AND 상한값
-- employees 테이블에서 급여를 5000 이상 6000 이하로 받는 직원의 아이디, 이름, 급여 조회
select employee_id, first_name, salary
from employees
where salary >= 5000 and salary <= 6000;
select employee_id, first_name, salary
from employees
where salary between 5000 and 6000;
-- employees 테이블에서 부서아이디가 10, 20, 30 중 하나인 부서에 소속된 직원의 아이디, 이름, 부서아이디 조회
select employee_id, first_name, department_id
from employees
where department_id = 10 or department_id = 20 or department_id = 30;
select employee_id, first_name, department_id
from employees
where department_id in (10, 20, 30);
3) 행의 정렬
- order by 절 사용한다.
- order by 절은 select문의 맨 마지막에 등장해야 한다.
- ASC : 오름차순 정렬
DESC : 내림차순 정렬
* SQL 퀴즈
--------------------------------------------------------------------------------
-- SQL 퀴즈
--------------------------------------------------------------------------------
-- departments 테이블의 모든 행과 모든 컬럼 조회하기 ( *쓰는것보다 일일이 쓰는게 좋음 )
select department_id,
department_name,
manager_id,
location_id
from departments;
-- departments 테이블에서 소재지 아이디가 1700인 부서의 부서아이디, 부서명
select department_id, department_name
from departments
where location_id = 1700;
-- locations 테이블에서 소재지 아이디가 1700인 소재지의 소재지 아이디, 우편번호, 주소, 도시명
select location_id, postal_code, street_address, city
from locations
where location_id = 1700;
-- departments 테이블에서 부서명이 'IT'인 부서의 부서아이디, 부서명, 부서매니저 아이디, 소재지 아이디
select department_id, department_name, manager_id, location_id
from departments
where department_name = 'IT';
-- jobs 테이블에서 최소급여가 2000 이상 5000 이하인 직종의 아이디, 직종이름, 최소급여, 최대급여
select job_id, job_title, min_salary, max_salary
from jobs
where min_salary between 2000 and 5000;
-- EMPLOYEES 테이블에서 80번 부서에 근무하고, 급여를 8000이상 받는 직원의 아이디, 이름, 직종, 급여
select employee_id, first_name, job_id, salary
from employees
where department_id = 80 and salary >= 8000;
-- EMPLOYEES 테이블에서 급여를 7000 이상 받고, 소속부서 아이디가 50이거나 80인 직원의 아이디, 이름, 급여, 부서아이디
select employee_id, first_name, salary, department_id
from employees
where salary >= 7000 and (department_id = 50 or department_id = 80);
-- EMPLOYEES 테이블에서 커미션을 받는 직원의 아이디, 이름, 급여, 커미션을 조회하기
-- 단, 커미션을 기준으로 오름차순으로 정렬한다.
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null
order by commission_pct asc;
데이터 조회
SELECT 컬럼명, 컬렴명, 컬럼명 --- 3
FROM 테이블명 --- 1
[WHERE 조건식] --- 2 (컬럼의 별칭을 사용할 수 없음-select보다 조건식이 먼저 실행되기 때문)
[ORDER BY 정렬기준] --- 4 (컬럼의 별칭을 사용할 수 있음)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY*12 as ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY >= 150000; // 오류발생 ( where 절에 별칭 불가)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY*12 as ANNUAL_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY ANNUAL_SALARY ASC; // 실행가능 ( ordery 절에 별칭 가능 )
1-2. 테이블 생성
--------------------------------------------------------------------------------
-- sample_users 테이블 생성하기
--------------------------------------------------------------------------------
create table SAMPLE_USERS (
-- 컬럼명 데이터타입 제약조건 기본값
user_id varchar2(20) primary key,
user_password varchar2(20) not null,
user_email varchar2(255) not null unique,
user_name varchar2(100) not null,
user_tel varchar2(20),
user_point number(10,0) default 0,
user_disabled char(1) default 'N',
user_created_date date default sysdate,
user_updated_date date default sysdate
);
데이터타입
varchar2 : 가변길이 문자열 저장 (최대길이)
number : 숫자 저장 (소수점 포함)
char(n) : n자리 문자열
제약조건
primary key (기본키) : 값이 꼭 있어야 하고, 고유한 값이어야 함. 테이블당 1개
not null : 값을 꼭 입력해야 함. null값을 허용하지 않음
unique : 중복 허용하지 않음
기본값
sysdate : system date
1-3. 데이터 추가 (insert into, values)
insert into sample_users (user_id, user_password, user_email, user_name, user_tel)
values ('hong', 'zxcv1234', 'hong@naver.com', '홍길동', '010-1234-5678');
insert into sample_users (user_id, user_password, user_email, user_name)
values ('kim', 'zxcv1234', 'kim@naver.com', '김유신');
insert into sample_users (user_id, user_password, user_email, user_name, user_tel, user_point)
values ('kang', 'zxcv1234', 'kang@naver.com', '강감찬', '010-7890-3423', 1000);
select user_id, user_email, user_name
from sample_users;
- 생략된 컬럼에는 NULL값이 저장된다.
∴ 제약조건이 not null 인 컬럼은 값을 꼭 정해줘야 함
1-4. 데이터 변경 (update, set)
-- sample_users 테이블의 모든 행에 대해서 user_point의 값을 2000으로 변경하기
update sample_users
set
user_point = 2000;
-- sample_users 테이블에서 user_id가 hong인 사용자의 user_point값을 100으로 변경하기
update sample_users
set
user_point = 100
where user_id = 'hong';
-- sample_users 테이블에서 user_id가 hong인 사용자의 user_point값을 50 증가시키기
update sample_users
set
user_point = user_point + 50
where user_id = 'hong';
-- sample_users 테이블에서 user_id가 hong인 사용자의 이메일, 비밀번호, 전화번호를 변경하기
update sample_users
set
user_email = 'hong@google.com',
user_tel = '010-1234-1234',
user_password = 'zxcv1234' -- 맨 마지막 컬럼에는 , 를 생략
where user_id = 'hong';
1-5. 데이터 삭제 (delete)
DELETE FROM 테이블명
WHERE 조건식
: 데이터는 삭제되고 영역은 남음
TRUNCATE FROM 테이블명
: 데이터와 영역 모두 삭제
-- SAMPLE_USERS 테이블에 저장된 모든 행을 삭제하기
-- WHERE 절 없이 DELETE FROM 구문을 사용하면 테이블의 모든 행을 삭제한다.
DELETE FROM SAMPLE_USERS;
-- SAMPLE_USER 테이블에서 USER_ID가 eungsu인 사용자 행을 삭제하기
DELETE FROM SAMPLE_USERS
WHERE USER_ID = 'eungsu';
-- DEPARTMENTS 테이블에서 DEPARTMENT_ID가 60번인 행 삭제하기 (오류)
-- ORA-02292: 무결성 제약조건(HR.JHIST_DEPT_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 60;
* 자주 발생하는 오류들
--------------------------------------------------------------------------------
-- SQL 오류
--------------------------------------------------------------------------------
-- ORA-00942: table or view does not exist
-- 테이블이나 뷰의 이름이 올바르지 않을 때 발생한다.
SELECT *
FROM DEPARTMENT; -- DEPARTMENTS가 올바른 테이블 이름
INSERT INTO SAMPLE_USER -- SAMPLE_USERS가 올바를 테이블 이름
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUES
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕', '010-1234-5678');
-- ORA-00923: FROM keyword not found where expected
-- FROM 키워드가 없습니다.
SELECT *
FORM EMPLOYEES; -- 철자 오류
SELECT EMPLOYEE_ID, FIRST_NAME LAST_NAME SALARY -- SELECT 절에서 컬럼명을 적을 때 , 누락
FROM EMPLOYEES;
-- ORA-00926: missing VALUES keyword
-- INSERT 문에 VALUES 키워드가 누락되었다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUE -- VALUES라고 적어야 한다.
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕', '010-1234-5678');
-- ORA-00913: too many values
-- INSERT 문에서 컬럼의 갯수보다 값을 갯수가 많다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUES
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕', '010-1234-5678', 100);
-- ORA-00947: not enough values
-- INSERT 문에서 컬럼의 갯수보다 값의 갯수가 적다.
INSERT INTO SAMPLE_USERS
(USER_ID, USER_PASSWORD, USER_EMAIL, USER_NAME, USER_TEL)
VALUES
('sejong', 'zxcv1234', 'sejong@naver.com', '세종대왕');
-- ORA-02292: integrity constraint (HR.JHIST_DEPT_FK) violated - child record found
-- 이 행을 참조하는 자식 레코드가 있기 때문에 이 행을 삭제할 수 없다.
-- (참조하는 자식레코드가 있는 경우는 삭제, 변경 불가)
DELETE FROM DEPARTMENTS
WHERE DEPARTMENT_ID = 60; -- EMPLOYEES테이블에서 DEPARTMENT_ID가 60번이 행이 존재하기 때문에 60번 부서는 삭제할 수 없다.
UPDATE DEPARTMENTS
SET
DEPARTMENT_NAME = 300
WHERE
DEPARTMENT_ID = 60; -- EMPLOYEES테이블에서 DEPARTMENT_ID가 60번이 행이 존재하기 때문에 60번 부서의 아이디를 변경할 수 없다.