Database
DML
- 데이터 조작어다.
- 데이터의 추가, 조회, 변경, 삭제 명령어다.
데이터 조회(SELECT)
데이터 조회하기
-
테이블의 모든 행, 모든 컬럼을 조회
SELECT * FROM 테이블명; -
테이블의 특정 행, 특정 컬럼을 조회
SELECT 컬럼명, 컬럼명, ... FROM 테이블명; -
산술식 사용하기
- select절, where절, group by절, having절 등에서 산술연산자를 사용할 수 있다.
- 숫자와 날짜 데이터를 대상으로 만 사용할 수 있다.
-
중복 제거하기
- 동일한 컬럼값을 가진 행이 한 번만 조회된다.
SELECT DISTINCT 컬럼명, ... FROM 테이블; -
별칭 사용하기
- select절과 from 절에서는 별칭을 정의할 수 있다.
- 별칭이 특수문자, 공백을 포함하고 있을 때는 ""안에 적는다.
SELECT 컬럼명 as 별칭, 컬럼명 as "별칭", 연산식 as 별칭, ... FROM 테이블명 SELECT 컬럼명 별칭, 컬럼명 "별칭", 연산식 별칭, ... FROM 테이블명
행의 제한(데이터 필터링)
-
WHERE 절을 사용한다.
-
제시된 조건식을 만족하는 행만 조회된다.
SELECT 컬럼명, 컬럼명,.... FROM 테이블 WHERE 조건식; -
논리 연산자
- 두 개이상의 조건식으로 데이터를 제한할 수 있다.
- AND, OR, NOT
SELECT 컬럼명, 컬럼명,.... FROM 테이블명 WHERE 조건식 AND 조건식; SELECT 컬럼명, 컬럼명,.... FROM 테이블명 WHERE 조건식 OR 조건식; SELECT 컬럼명, 컬럼명,.... FROM 테이블명 WHERE 조건식 AND (조건식 OR 조건식); -
기타 연산자
- BETWEEN 하한값 AND 상한값
- 컬럼의 값이 하한값과 상한값 범위내면 true다.
SELECT 컬럼명, 컬럼명, .... FORM 테이블명 WHERE 컬럼명 BETWEEN 값 AND 값;- IN (값, 값, 값, ...)
- 컬럼의 값이 제시된 값들 중 하나와 일치하면 true다.
SELECT 컬럼명, 컬럼명, .... FROM 테이블명 WHERE 컬럼명 IN (값, 값, ...);-
LIKE '패턴'
- 컬럼의 값이 제시된 패턴과 일치하면 true다.
- 패턴문자
패턴문자 설명 '%' 0개 이상의 일련의 임의의 문자를 나타낸다 '_' 임의의 문자 하나를 나타낸다 - 이름 LIKE '이_'; 성이 이씨고, 이름이 한 글자인 사람
- 이름 LIKE '이%'; 성이 이씨인 사람
- 책제목 LIKE '%자바%'; 제목에 "자바"가 포함되어 있는 모든 책
SELECT 컬럼명, 컬럼명, .... FROM 테이블명 WHERE 컬럼명 LIKE '%패턴%'- IS NULL, IS NOT NULL
- 컬럼의 값이 NULL인 행 혹은 NULL이 아닌 경우 TRUE
SELECT 컬럼명, 컬럼명, .... FROM 테이블명 WHERE 컬럼명 IS NULL SELECT 컬럼명, 컬럼명, .... FROM 테이블명 WHERE 컬럼명 IS NOT NULL; - BETWEEN 하한값 AND 상한값
행의 정렬
- order by 절 사용한다.
- order by 절은 select문의 맨 마지막에 등장해야 한다.
SELECT 컬럼명, 컬럼명, .... FROM 테이블명 [where 조건식] [order by {컬럼명|표현식} [ASC|DESC]] -- ASC : 오름차순 정렬 -- DESC : 내림차순 정렬
데이터 추가
- INSERT INTO 명령어 사용
- 구문형식
-- 값이 저장될 컬럼명을 직접 나열하는 방식 -- 생략된 컬럼에는 NULL값이 저장된다. INSERT INTO 테이블명 (컬럼명, 컬럼명, 컬럼명) VALUES (값, 값, 값); -- 컬럼명을 생략하는 방식 -- 모든 컬럼의 값을 전부 적어야 한다. -- 테이블의 컬럼 순서와 동일한 순서로 값을 적어야 한다. INSERT INTO 테이블명 VALUES (값, 값, 값);insert into contacts(name, tel, fax) values('홍길동', '010-1111-111', '02-1111-1111')insert into contacts values('홍길동', '010-1111-111', '서울', 'hong@gmail.com', NULL, sysdate)
데이터 변경
- 테이블에 저장된 데이터를 변경한다.
- UPDATE 명령어 사용
- 구문형식
UPDATE 테이블 SET 컬럼명 = 값, 컬럼명 = 값, 컬럼명 = 값 [WHERE 조건식] -- WHERE 조건식이 없으면 모든 행에서 해당 컬럼의 값을 변경한다. -- WHERE 조건식이 있으면 조건식을 만족하는 행에서만 해당 컬럼의 값을 변경한다.
데이터 삭제
- 테이블에 저장된 데이터를 삭제한다.
- DELETE FROM 명령어 사용
- 구문형식
DELETE FROM 테이블명 [WHERE 조건식] -- WHERE 조건식이 없으면 테이블의 모든 행이 삭제된다. -- WHERE 조건식이 있으면 조건식을 만족하는 행만 삭제한다. - 테이블의 모든 데이터 삭제하기
DELETE FRM 테이블명 - 제시된 조건과 일치하는 행을 삭제하기
DELETE FROM 테이블명 WHERE 조건식
select * from employees;
-- 급여가 10000이상 15000이하인 사원들의 아이디, 이름, 급여를 조회하기
select employee_id, first_name, salary
from employees
where salary >= 10000 and salary <= 15000;
-- 소속부서 아이디가 50번이고, 급여를 3000이상 받는 사원의 아이디, 이름, 급여를 조회
select employee_id, first_name, salary
from employees
where department_id = 50 and salary >= 3000;
-- 직종 아이디가 SH_CLERK이고, 급여를 4000이상 받는 사원의 아이디, 이름, 급여를 조회하기
select employee_id, first_name, salary
from employees
where job_id = 'SH_CLERK' and salary >= 4000;
-- 자신의 상사가 101번이거나 102번인 사원의 아이디, 이름, 직종아디기, 상사의 아이디를 조회하기
select employee_id, first_name, job_id, manager_id
from employees
where manager_id = 101 or manager_id = 102;
-- 자신의 상사가 101번이거나 102번인 사원의 아이디, 이름, 직종아디기, 상사의 아이디를 조회하기 (2)
select employee_id, first_name, job_id, manager_id
from employees
where manager_id in (101,102);
-- 소속부서 아이디가 30번이거나 60번이면서 급여를 5000이상 받는 사원의 아이디, 이름 , 급여, 소속부서 아이디를 조회
select employee_id, first_name, salary, department_id
from employees
where department_id in (30, 60)
and salary >= 5000;
-- is null, is not null을 이용해서 컬럼의 값이 null인 것과 null이 아닌것을 구분해서 조회하기
-- departments 테이블에서 등록된 부서 중에서 부서관리자가 지정되지 않은 부서의 아이디, 부서명, 관리자아이디 조회
select department_id, department_name, manager_id
from departments
where manager_id is null;
-- employees 테이블에 등록된 사원들 중에서 커미션을 받는 사원의 아이디, 이름, 급여, 커미션 조회하기
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null;
-- employees 테이블에 등록된 사원들 중에서 커미션을 받고 ,급여를 10000이상 받는 사원의 아이디, 이름, 급여, 커미션 조회
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null
and salary >= 10000;
-- employees 테이블에 등록된 사원들 중에서 커미션을 받고 ,급여를 10000이상 받는 사원의 아이디, 이름, 급여, 커미션 조회(오름차순)
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null and salary >= 10000
order by commission_pct asc;
-- employees 테이블에서 사원번호가 110번 이상 120번 이하인 사원의 사원아이디, 이름을 조회
select employee_id, first_name
from employees
where employee_id >= 110 and employee_id <= 120;
select employee_id, first_name
from employees
where employee_id between 110 and 120;
-- employees 테이블에서 직종 아이디에 CLERK를 포함하고 있는 사원의 아이디, 이름, 직종아이디 조회
select employee_id, first_name, job_id
from employees
where job_id like '%CLERK';
-- employeees 테이블에 등록된 사원중에서 이름에 S를 포함하고 있는 사원의 아이디, 이름
select employee_id, first_name
from employees
where first_name like '%S%';
-- employees 테이블에 등록된 사원들 중에서 소속부서아이디가 30, 50, 60, 80이 아닌 사원의
-- 아이디, 이름, 부서아이디를 조회
select employee_id, first_name, department_id
from employees
where department_id not in (30, 50, 60, 80)
order by department_id asc;
-- 퀴즈
-- 모든 부서정보를 조회하기
select * from departments;
-- 모든 부서소재지정보를 조회하기
select * from locations;
-- 모든 직종정보를 조회하기
select * from jobs;
-- 직종정보 테이블에서 직종아이디, 최소급여, 최대급여를 조회하기
select job_id, min_salary, max_salary
from jobs;
-- 부서정보 테이블에서 부서아이디, 부서명을 조회하기
select department_id, department_name
from departments;
-- 사원정보 테이블에서 사원아이디, 이름, 입사일, 직종아이디, 급여를 조회하기
select employee_id, first_name, hire_Date, department_id, salary
from employees;
-- 사원정보 테이블에서 사원아이디가 100번인 사원의 이름, 전화번호, 이메일, 입사일, 급여를 조회하기
select first_name, phone_number, email, hire_date, salary
from employees
where employee_id = 100;
-- 사원정보 테이블에서 100번 사원을 상사로 두고 있는 사원들의 이름, 입사일, 직종아이디를 조회하기
select first_name, hire_date, job_id
from employees
where manager_id = 100;
-- 직종테이블에서 직종 최소급여가 10000 이상인 직종의 아이디, 제목, 최소급여, 최대급여를 조회하기
select job_id, job_title, min_salary, max_salary
from jobs
where min_salary >= 10000;
-- 사원테이블에서 커미션을 받는 사원 중에서 급여를 5000이하 받는 사원의 아이디, 이름, 직종, 급여를 조회하기
select employee_id, first_name, department_id, salary
from employees
where commission_pct is not null
and salary <= 5000;
-- 사원테이블에서 직종이 'MAN'이나 'MGR'로 끝나는 사원중에서 급여를 10000이하 받는 사원의 아이디,
-- 이름, 직종아이디, 급여를 조회하기
select employee_id, first_name, job_id, salary
from employees
where (job_id like '%MAN' or job_id like '%MGR')
and salary <= 10000;
-- 사원정보테이블에서 커미션을 받는 사원중에서 147번 사원을 상사를 두고 있으면서, 급여를 10000이상 받는
-- 사원의 아이디, 사원이름, 급여, 커미션을 조회하기
select employee_id, first_name, salary, commission_pct
from employees
where commission_pct is not null
and manager_id = 147
and salary >= 10000;
-- 사원정보테이블에서 101번과 102번이 상사로 정해진 사원들의 아이디, 이름, 상사아이디를 조회하기
select employee_id, first_name, manager_id
from employees
where manager_id in (101, 102);
-- 사원정보테이블에서 소속부서가 결정되지 않은 사원의 아이디, 이름, 직종아이디를 조회하기
select employee_id, first_name, job_id
from employees
where department_id is null;
-- 사원정보테이블에서 60번 부서에 소속된 사원들의 아이디, 이름, 급여, 월급, 연봉을 조회하기
-- (salary는 월급이다. 연봉은 별칭을 부여한다.)
select employee_id, first_name, salary, (salary * 12) as 연봉
from employees
where department_id = 60;
-- 문자함수 연습하기
-- lower(컬럼명 혹은 표현식) - 소문자로 변환
-- upper(컬럼명 혹은 표현식) - 대문자로 변환
-- substr(컬럼명 혹은 표현식, 시작위치, 갯수) - 시작위치에서 갯수만큼 잘라서 반환
-- length(컬럼명 혹은 표현식) - 문자열의 길이 반환
-- instr(컬럼명 혹은 표현식, '문자') - 지정된 문자의 위치를 반환(인덱스는 1부터 시작)
-- instr(컬럼명 혹은 표현식, '문자', 시작위치, n번째) - 지정된 시작위치부터 문자를 검색했을 떄 n번째로 등장하는 위치를 반환
-- lpad(컬럼명 혹은 표현식, 길이 '문자') - 지정된 길이보다 짧으면 부족한 길이만큼 지정된 문자를 왼쪽에 채운다.
-- rpad(컬럼명 혹은 표현식, 길이 '문자') - 지정된 길이보다 짧으면 부족한 길이만큼 지정된 문자를 오른쪽에 채운다.
-- dual == 오라클에서 제공하는 1행 1열짜리 테이블
select lower('Hello World'),
upper('Hello World'),
substr('Hello World', 3, 4),
length('Hello World'), -- 11글자
lengthb('Hello World'), -- 11byte
length('안녕하세요'), -- 5글자
lengthb('안녕하세요'), -- 15byte
instr('010-1111-1111', '-'), -- 4
instr('02)1234-5678', ')'), -- 3
instr('700)1234-5677', ')'), -- 4
lpad('100', 10, '0'), -- 0000000100
lpad('12345', 10, '0'), -- 0000012345
replace('hello', 'l', '*') -- he**o
from dual;
-- employees 테이블에서 이름에 'tay'가 포함되는 사원을 조회하기
select first_name
from employees
where lower(first_name) like '%tay%';
select first_name, job_id
from employees
where lower(first_name) like '%tay%';
-- employees 테이블에서 사용자 아이디, 이름, 원래전화번호, 전화번호 조회하기
-- (전화번호의 2번째 구분점 부터 조회)
select employee_id, first_name, phone_number, substr(phone_number,
instr(phone_number, '.', 1, 2) + 1) short_phone_number
from employees;
-- employees 테이블에서 사원아이디, 이름, 이메일을 조회하기
-- (단, 이메일의 세번째부터 2글자는 **로 바꾸기)
select employee_id, first_name, email, lower(replace(email, substr(email, 3, 2), '**')) secret_email
from employees;
