2021. 05. 03(월) TIL

Dylan·2021년 5월 3일
TILdb
post-thumbnail

Database

조인

조인의 종류

포괄조인(Outer Join)

  • 등가조인, 비등가조인은 조회하려는 데이터들이 조인에 참여하는 모든 테이블에 데이터가 존쟇는 경우에만 조회된다.
  • 포괄조인은 한쪽 테이블에는 데이터가 있고, 다른쪽 테이블에는 데이터가 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 조회하는 조인방법이다.
  • 포괄조인 사용하기
    • student테이블과 professor테이블을 조인하여 학생이름, 지도교수이름 조회하기. 단 지도교수가 정해지지 않는 학생도 조회하기
      -- Oracle Join
  SELECT s.name 학생명, p.name 교수명
  FROM student s, professor p
  WHERE s.profno = p.profno(+); -- 데이터가 없는 쪽 테이블에 (+)기호를 붙이면 된다.
  
  -- ANSI Join
  SELECT s.name 학생명, p.name 교수명
  FROM students LEFT OUTER JOIN professor p -- 선행테이블의 모든 행이 조회된다.
  ON s.profno = p.profno;
    • student테이블과 professor테이블을 조인하여 학생이름, 지도교수이름을 출력하기. 단, 지도학생이 결정되지 않은 교수도 조회하기
      -- Oracle Join
  SELECT s.name 학생명, p.name 교수명
  FROM student s, professor p
  WHERE s.profno(+) = p.profno;
  
  -- ANSI Join
  SELECT s.name 학생명, p.name 교수명
  FROM student s RIGHT OUTER JOIN professor p -- 후행테이블의 모든 행이 조회된다.
  ON s.profno = p.profno;
    • student테이블과 professor테이블을 조인하여 학생명, 지도교수이름 조회하기. 단, 지도 학생이 결정안된 교수이름과 지도교수가 결졍안된 학생도 조회하기
      -- Oracle Join
  SELECT s.name 학생명, p.name 교수명
  FROM students, professor p
  WHERE s.profno(+) = p.profno -- 지도학생이 결정되지 않은 교수도 조회
  UNION
  SELECT s.name 학생명, p.name 교수명
  FROM student s, professor p
  WHERE s.profno = p.profno(+); -- 지도교수가 결정되지 않은 학생도 조회
  
  -- ANSI Join
  SELECT s.name, p.name
  FROM student s FULL OUTER JOIN professor p
  ON s.profno = p.profno;

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;

행의 정렬

  • 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 조건식

-- 부서아이디, 부서명, 부서관리자아이디, 부서관리자 이름 조회하기
-- 부서아아디 , 부서명, 부서관리자아이디 - departments
-- 부서관리자 이름 - employees
select D.department_id, D.department_name, D.manager_id, E.first_name 
from departments D, employees E 
where D.manager_id = E.employee_id
order by D.department_id  asc;

--포괄 조인을 활용하면, employees테이과 매칭되지 않는 departments의  부서정보도 조회할 수 있다.
select D.department_id, D.department_name, D.manager_id, E.first_name 
from departments D, employees E 
where D.manager_id = E.employee_id(+)
order by D.department_id  asc;

--커미션을 받는 사원들의 사원아이디, 이름, 소속부서아이디, 소속부서명 조회하기
select E.employee_id, E.first_name, E.department_id, D.department_name
from employees E, departments D 
where E.department_id = D.department_id(+)
and E.commission_pct is not null
order by E.employee_id;

-- 90번 부서에 소속된 사원들의 사원 아이디, 이름, 해당 사원의 상사의 이름을 조회하기
select 사원.employee_id, 사원.first_name, 상사.first_name
from employees 사원, employees 상사
where 사원.department_id = 90 
and 사원.manager_id = 상사.employee_id(+);

-- 오라클조인과 ANSI-조인
-- 90번 부서에 소속된 사원들의 아이디, 이름, 직종아이디, 직종제목 조회하기
-- 오라클조인
select E.employee_id, E.first_name, J.job_id, J.job_title
from employees E, jobs J
where E.department_id = 90
and E.job_id = J.job_id;

-- ANSI-조인
select E.employee_id, E.first_name, J.job_id, J.job_title
from employees E join jobs J on E.job_id = J.job_id 
where E.department_id = 90;

-- 9번 부서에 소속된 사원의 아이디, 이름, 직종아이디, 직종제목, 소속부서 아이디, 소속부서명 조회하기
-- 사원의 아이디, 이름, 직종아이디, 소속부서아이디 -  employees 
-- 직종아이디, 직종제목 - jobs 
-- 소속부서아이디, 소속부서명 - departments
select E.employee_id, E.first_name, J.job_id, J.job_title, D.department_id, D.department_name
from employees E, jobs J, departments D 
where  E.department_id = 90
and E.job_id = J.job_id 
and E.department_id = d.department_id;

select E.employee_id, E.first_name, J.job_id, J.job_title,
       D.department_id, D.department_name
from employees E join jobs J        on E.job_id = J.job_id
                 join departments D on E.department_id = D.department_id
where E.department_id = 90;

-- 90번 부사에 소속된 사원의 아이디, 이름, 급여, 급여등급을 조회하기
select E.employee_id, E.first_name, E.salary, S.GRADE
from employees E, salary_grade S 
where E.department_id = 90 
and E.salary >= S.min_salary and E.salary <= S.max_salary; 

select E.employee_id, E.first_name, E.salary, S.GRADE
from employees E join salary_grade S on E.salary >=  S.min_salary and E.salary <= S.max_salary
where E.department_id = 90;

-- 부서아이디, 부서명, 부서담당자 아이디, 부서담당자 이름 조회하기
-- 부서담당자가 결정되어 있지 않은 부서도 조회한다.
select D.department_id, D.department_name, D.manager_id, E.first_name 
from departments  D, employees E 
where D.manager_id = E.employee_id(+);

select D.department_id, D.department_name, D.manager_id, E.first_name 
from departments D left outer join employees E on D.manager_id = E.employee_id;
-- left outer join : 선행테이블의 모든 행이 조회되도록 한다.

select D.department_id, D.department_name, D.manager_id, E.first_name 
from employees E right outer join departments D on E.employee_id = D.manager_id;
-- right outer join : 후행테이블의 모든 행이 조회되도록 한다.

create table sample_books (
	book_no number(4),
	book_title varchar2(200),
	book_writer varchar2(100),
	book_price number(8),
	book_discount_price number(8),
	book_stock number(4),
	book_created_date date default sysdate
);

-- 테이블에 새로운 행 추가하기

-- 행의 모든 컬럼의 값을 지정해서 추가하기 
insert into sample_books 
(book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date)
values 
(101, '자바의 정석', '남궁성', 35000, 29000, 1000, sysdate);

-- 행의 특정 컬럼에만 값을 지정해서 추가하기, 생략된 컬럼에는 null값이 저장된다.
insert into sample_books
(book_no, book_title, book_price)
values 
(102, '이것이 자바다', 28000);

-- 행을 추가할 때 행의 모든 컬럼에 값을 추가하고, 컬럼의 순서 그대로 값을 추가할 때는
-- 컬럼명을 생략할 수 있다.
insert into sample_books
--(book_no, book_title, book_writer, book_price, book_discount_price, book_stock, book_created_date)
values (103, '이것이 데이터분석이다.', '윤기태', 28000, 26000, 100, sysdate);

insert into sample_books
(book_no, book_title, book_writer, book_price, book_discount_price, book_stock)
values 
(104, '스프링 인 액션', '미상', 30000, 27000, 20);


-- 테이블에 저장된 데이터 삭제하기

-- 테이블에 저장된 모든 데이터 삭제
delete from sample_books;
-- 테이블에 저장된 데이터 중에서 특정 행을 삭제하기
delete from sample_books
where book_title like '%자바%';

delete from sample_books
where book_no = 103;

delete from sample_books
where book_no = 104;


-- 테이블에 저장된 데이터 변경하기

-- 테이블의 모든 행에 대해서 book_price  컬럼의 값을 10000으로 변경하기
update sample_books
set 
	book_price = 10000;

-- 테이블의 특정 행에 대해서 book_price 컬럼의 값을 32000으로 변경하기
update sample_books
set 
	book_price = 32000 
where 
	book_no = 101;
	
update sample_books 
set 
	book_writer = '신용권',
	book_price = 35000,
	book_discount_price = 31500,
	book_stock = 50
where 
	book_no = 102;

-- sample_books 테이블에 새로운 책 정보 추가하기 (3개 이상, 책 번호는 겹치지 않도록 한다.) 
insert into sample_books
(book_no, book_title, book_writer, book_price, book_discount_price, book_stock)
values 
(105, '혼자 공부하는 자바', '홍길동', 25000, 22500, 100);

-- 책번호가 104번인 책 삭제하기
delete from sample_books
where book_no = 104;

-- 책번호가 103번인 책 삭제하기
delete from sample_books 
where book_no = 103;

-- 모든 책 정보 삭제하기
delete from sample_books;

commit;
profile
Dylan
이전 포스트

2021. 04. 30(금) TIL

다음 포스트

2021. 05. 04(화) TIL

0개의 댓글