2019. 4. 8. 18:01ㆍDateBase
--------------------------단일행 서브쿼리 연산자-----------------------
단일행 서브쿼리 연산자의 종류
>, <, >=, <=, =, <>
예시) 'Hermann'보다 급여를 많이 받는 사원의 이름과 급여를 조회하기
select first_name, salary
from EMPLOYEES
where salary > (select SALARY
from EMPLOYEES
where first_name = 'Hermann');
예시2) 전체 사원들의 평균급여보다 급여를 적게 받는 사원의 이름과 급여를 조회하기
select first_name, salary
from employees
where salary < (select avg(salary)
from employees);
------------------------다중행 서브쿼리 연산자--------------------------
다중행 서브쿼리 종류
in : 서브쿼리가 반환하는 값 목록 중 어느 하나와 일치하면 true가 반환된다.(해당 행은 선택된다.)
예시) 비교값 in (값1, 값2, 값3 ...)
any : 서브쿼리가 반환하는 값 목록 중 어느 하나와만 제시된 조건이 만족하면 true가 반환된다.
all : 서브쿼리가 반환하는 값 목록 중 모두와 제시된 조건이 만족하면 true가 반환된다.
아래에 제시된 연산식은 결과가 서로 동일(위, 아래)한 연산식이다.
salary > any (서브쿼리) salary > all (서브쿼리) salary = any (서브쿼리)
salary > 최소값 salary > 최대값 salary in (서브쿼리)
.
예시) 직종별 평균급여를 계산했을 때 급여평균이 3000달러를 넘는 직종에 근무하는 사원의 이름, 직종, 급여를 조회
※. in 예시
select first_name, job_id, salary
from EMPLOYEES
where job_id in (select JOB_ID
from EMPLOYEES
group by job_id
having avg(salary) < 3000);
서브쿼리 비교할때 '='를 쓸경우 3000미만인 직종이 2개라 에러가 난다 그래서 in을 사용
예시2) any, all 사용 예시
select first_name, job_id, salary
from employees
where salary > any (6000, 7000, 17000);
any는 괄호 안의 값( 6000, 7000, 17000) 중 하나만 커도 값들이 다나온다.
(any 같다 > 최소값 과 같은 느낌)
select first_name, job_id, salary
from employees
where salary > all (6000, 7000, 17000);
all은 괄호 안의 값 (6000, 7000, 17000) 모두와 커야 값이 나온다
(all 같다 > 최대값 과 같은 느낌)
예시3) 직종이 IT_PROG인 사원들보다 급여를 많이 받는 사원들의 이름, 직종, 급여를 조회하기
IT_PROG 직종의 최고급여보다 급여를 많이 받는 사원을 조회
select first_name, job_id, salary
from employees
where salary > all (select SALARY
from EMPLOYEES
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG'
order by 3;
IT_PROG 직종의 최소급여보다 급여를 많이 받는 사원을 조회
select first_name, job_id, salary
from employees
where salary > any (select SALARY
from EMPLOYEES
where job_id = 'IT_PROG')
and job_id <> 'IT_PROG'
order by 3;
----------------------------------------insert---------------------------------
==============예시를 위해 테이블 생성============
create table courses(
course_no number(3) primary key, -- 과정번호
course_name varchar2(200), -- 과정명
course_price number(7), -- 수강료
course_quota number(3), -- 모집인원
course_teacher varchar2(100), -- 강사
course_start_date date, -- 시작일
course_end_date date, -- 종료일
course_room varchar2(20), -- 강의장
course_create_date date -- 데이터 생성일
)
insert
- 테이블에 새로운 행을 추가
- insert into 구문을 사용한다.
1. 컬럼명을 명시해서 insert구문을 작성한다. ※. 유지보수 측면에서 추천한다.
(테이블의 전체 컬럼 혹은 값이 결정된 컬럼명만 적을 수도 있다.)
insert into 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, 컬럼명4, ...., 컬럼n)
values (값1, 값2, 값3, 값4, ,...., 값n )
2. 컬럼명을 생략하고 insert구문을 작성한다.
(*테이블의 모든 컬럼에 해당하는 값이 values절에 명시되어야한다. (null값도 포함)
*테이블의 컬럼순서와 동일한 순서로 values절에 값이 명시되어야 한다.)
insert into 테이블명
values (값1, 값2, 값3, 값4, ...., 값n)
- 한 번에 한 행만 추가한다.
- 값을 표기할 때 문자 및 날짜는 작은 따옴표로 묶는다.
예시) insert into 예시
insert into courses ( course_no, course_name, course_price, course_quota, course_teacher,
course_start_date, course_end_date, course_room, course_create_date)
values (100, '자바 개발자 과정', 1000000, 20, '홍길동', '2019/05/01', '2019/12/31', 'L2', sysdate);
commit; -- 원래 DB에서 불러오면 오래걸려서 메모리에 저장하는데 commit을 할경우 DB본체에 저장을 해준다.
-- QueryBox에서 왼쪽 하단에 AutoCommit On하면 이제 컴파일 할때마다 저절로 된다.
select * from courses; -- 데이터 확인
예시2) 과정 추가 ( 일부의 값만 입력하기)
(컬럼명시 1번 방법)
insert into courses(course_no, course_name, course_price, course_quota, course_create_date)
values (200, '안드로이드 앱 개발자 과정', 1200000, 15, sysdate);
※. 몇개의 값이 미정인 상태에서는 insert into는 넣어줄것만 지정하고 values도 그 컬럼값만 넣어준다.
(컬럼명시 2번 방법)
insert into courses ( course_no, course_name, course_price, course_quota, course_teacher,
course_start_date, course_end_date, course_room, course_create_date)
values (400, '데이터분석가 과정', 2500000, null, null, null, null, 'L3', sysdate);
※. 데이터가 일부없으면 컬럼을 모두적고 미정인값만 null을 써도 된다.
-----------------------------------update---------------------------------
update
update 구문을 사용하면 하나 혹은 여러 행의 데이터를 변경할 수 있다
방법)
update 테이블명
set 컬럼명1 = 값1, 컬럼명2 = 값2, 컬럼명3 = 값3, ... 컬럼명n = 값n
[where 조건식]
where 절은 생략할 수 있다. 생략하면 모든 행에서 지정된 컬럼의 값을 갱신한다.
예시) courses 테이블의 100번과정의 가격을 50만원으로 설정
update COURSES
set course_price = 500000
where course_no = 100;
예시2) courses 테이블의 200번 과정의 강사명, 시작일, 종료일, 강의실을 변경
update COURSES
set course_teacher = '이순신',
course_start_date = '2019/05/25',
course_end_date = '2019/08/25',
course_room = 'L6'
where course_no = 200;
예시3) 모드 직원들의 급여를 1000달러 인상하기
update EMPLOYEES
set salary = salary + 1000;
예시4) 132번 TJ의 급여를 2007년 입사자들의 평균급여의 70%수준으로 인상하기
update EMPLOYEES
set salary = (select trunc(avg(salary)*0.7)
from EMPLOYEES
where to_char(hire_date, 'yyyy') = 2007)
where employee_id = 132;
예시5) 100번 직원의 부서번호를 300번으로 변경하기
update EMPLOYEES
set department_id = 300 -- 300번애 해당하는 부서번호가 존재하지 않으므로
where employee_id = 100; -- 부서번호를 300번으로 바꿀 수 없다.
※. 부모의 없는 값과 자식이 사용하는 값은 변경이 불가하다.
-------------------------------------delete-----------------------------------
delete
- delete구문을 사용하면 하나 혹은 여러 개의 행을 삭제할 수 있다.
구조
delete from 테이블
[where 조건식] * 조건식을 생략하면 테이블내의 모든 행이 삭제된다.
예시) 과정번호가 100번인 행 삭제하기
delete from COURSES
where course_no = 100;
예시2) 이순신이 강의하는 모든 과정 삭제하기
delete from COURSES
where COURSE_TEACHER = '이순신';
예시3) courses 테이블 모두 삭제
delete from courses; // truncate table courses; 도 가능하다.
// truncate는 테이블의 모든 행을 매우 빠르게 삭제한다.
※. 각각 삭제의 차이
delete from 테이블명;
- 테이블의 데이터만 삭제하며 공간은 남아있음, rollback 가능
truncate table 테이블명;
- 테이블 안의 공간도 모두 삭제(테이블 생성시점과 같아짐), rollback 불가능
drop table 테이블명;
- 테이블 자체를 삭제함, rollback 불가능