[22.11.15] 18일차 [데이터베이스] SQL 문법 where 비교연산자(2), ERD 만들기, join(1)

hr 테이블 사용

비교연산자3 in

• 다중행비교연산자, 우변에 값리스트가 올수 있음.
• (=, OR)의 성격을 내포하고 있음.
• 우변의 값리스트와 비교해서 하나이상 동일하면 true를 반환하는 비교연산자

select employee_id, last_name, salary, manager_id
from employees
where manager_id in(100,101,201);
-- (==)
select employee_id, last_name, salary, manager_id
from employees
where manager_id  = 100 or manager_id  = 101 or manager_id  = 201;

select employee_id, last_name, job_id
from employees
where job_id in('SA_REP','IT_PROG');

비교연산자4 like

패턴일치 여부를 비교하는 연산자

• 기호
  1) %:0 또는 여러개의 문자
  2) _ :
• a로 시작되는 문자열 : 'a%'
• a가 포함된 문자열 : '%a%'
• a로 끝나는 문자열 : '%a'
• a로 시작해서 b로 끝나는 문자열 ; 'a%b'
• 두번째 문자가 a인 문자열
• 끝에서 세번째 문자가 a인 문자열 : '%a__'

select last_name
from employees
where last_name like '_o%'; -- 두번째 글자가 o

select employee_id, last_name, job_id
from employees
where job_id like '%rep'; -- rep로 끝나는

select employee_id, last_name, salary, hire_Date
from employees
where hire_date like '198%'; --1980년도 입사

비교연산자5 is null

값이 null인지를 비교해주는 연산자

select employee_id, last_name, manager_id
from employees
where manager_id is null; -- 우리 회사 사장 출력!!

-- 수당을 받지 않는 사원 출력
select employee_id, last_name, salary, commission_pct
from employees
where commission_pct is null;

where절에 여러개 조건문 작성하기

• and, or 논리연산자로 연결
  우선순위 : and > or
  우선순위 지정을 원하는 경우 괄호 사용해야 함.

and VS or

select employee_id, last_name, job_id, salary
from employees
where salary>=10000
and job_id like '%man%';

select employee_id, last_name, job_id, salary
from employees
where salary>=10000
or job_id like '%man%';

select employee_id, last_name, salary, job_id, department_id
from employees
where job_id= 'SA_REP'
or job_id = 'IT_PROG'
and salary>= 10000;
;

and 연산자 먼저 계산됨

select employee_id, last_name, salary, job_id, department_id
from employees
where (job_id= 'SA_REP'
or job_id = 'IT_PROG')
and salary>= 10000;

비교연산자 정리

	<-->
=	<-->	<>, !=
>, >=	<-->	<,<=
between A and B	<-->	not between A and B A미만 B초과
in(=,or)	<-->	not in(<>,and)
like	<-->	not like
is null	<-->	is not null

select employee_id, last_name, salary, department_id
from employees
where salary not between 5000 and 20000;

select employee_id, last_name, job_id
from employees
where job_id not in ('sa_rep', 'it_prog', 'st_man');

select employee_id, last_name, hire_date
from employees
where hire_date not like '19%';

select employee_id, last_name, manager_id
from employees
where manager_id is not null;
-- 사장빼고 전부 출력됨

예제) employees 테이블로부터 커미션을 받는 사원들의 employee_id, last_name, commission_pct, salary를 출력하는 구문을 작성하시오.

select employee_id,last_name, commission_pct, salary
from employees
where commission_pct is not null;

order by절(정렬)

특정 컬럼을 기준으로 정렬된 결과를 출력해 줌.

• select * | 컬럼명1,컬럼명2, ...
  from 테이블명
  (where 조건문)
  (order by 컬럼명 | 표현식 | 컬럼alias | 위치표기법 [asc | desc])
• 정렬방법
  1) asc : 오름차순, default(기본값)
  2) desc : 내림차순

select employee_id, last_name, salary, department_id
from employees
order by salary desc;

select employee_id, last_name, salary, department_id
from employees
order by salary;

** 표현식 및 컬럼alias 기준으로 정렬가능

select employee_id, last_name, 12*salary as ann_sal
from employees
order by 12*salary desc;
-- (==)
select employee_id, last_name, 12*salary as ann_sal
from employees
order by ann_sal desc;

** 위치 표기법 : 5번째 컬럼 기준으로 정렬 원할 경우 숫자만 적어도 정렬 가능

select employee_id, last_name, salary, job_id, department_id
from employees
where department_id <80
order by 5;

** 다중 컬럼 기준으로 정렬

select last_name, department_id, salary
from employees
order by department_id, salary desc;

- 첫번째 컬럼 기준으로 오름차순 정렬하고, 그안에서 두번째 컬럼 기준으로 내림차순 정렬

select last_name, department_id, salary
from employees
order by department_id desc, salary desc;

연습문제 1) employees 테이블로부터 2000년도에 입사한 모든 사원의 last_name과 hire_date를 출력하시오.

select last_name, hire_date
from employees
where hire_date between '2000-01-01' and '2000-12-31';
--(==)
select last_name, hire_date
from employees
where hire_date like '2000%';

연습문제2) employees 테이블로부터 커미션을 받지 않는 모든 사원의 last_name, salary, commission_pct를 출력하되 salary를 기준으로 내림차순 정렬하시오.

select last_name, salary, commission_pct
from employees
where commission_pct is null
order by salary desc;

ERD 만들기

데이터모델링 단계

1. 요구사항 수집 및 분석
2. 개념 모델링
   👉 피터첸의 ERD(뼈대)
   👉 테이블, 컬럼, 관계 등이 결정됨.
3. 논리 모델링
   👉 구체화된 ERD(IE표기법, 테이블 차트)
   👉 데이터타입, 컬럼사이즈, 제약조건 등의 결정됨.
4. 물리 모델링
   👉 DB에 테이블 구현(create table ----;)

역모델링(리버스 모델링)

현재 DB에 존재하는 테이블을 기존으로 ERD를 그려주는 기능

• MySQL Workbench [Home] - [Models] - [Create EER Model from Database]
  
• 접속 정보 입력 후 [Next]
• DBMS 접속이 완료되면 [Next]
  
• ERD를 생성할 기존 Database(Schema) 선택 후 [Next] hr 선택하고 next
• 선택한 스키마 확인 작업이 완료되면 [Next]
• 리버스 엔지니어링(역모델링) 작업을 할 테이블 확인 후 [Execute]

• ERD 확인하기

🔗조인(join)

조인이란?

여러테이블의 데이터를 함께 출력하기 위한 문법

• select 컬럼명1, 컬럼명2, 컬럼명3,...
  from 테이블1 join 테이블2
  on 테이블1.컬럼명 = 테이블2.컬럼명 --> 조인조건문

(예제1) employees 테이블과 departments 테이블로부터 employee_id, last_name, salary, department_id, department_name을 함께 출력하시오.

department_id를 기준으로 조인

select employee_id, last_name, salary, employees.department_id, department_name
from employees join employees.departments
on employees.department_id = departments.department_id;