[Data Base] 2019.06.20. 기록

중요
column 줄일이름 format a숫자
> 숫자만큼 ___ 줄 줄여짐

order by
-정렬(절)
-asc : 오름차순 , 기본 값
-desc : 내림차순
-기억 annsal 사용가능 

데이터가 출력될땐 행단위로 출력된다
-멀티플 정렬시 
첫번째기준인 department_id 오름차순 후 salary 기준으로 내림차순 정렬 발생함
-where 절이나 order by 절에서는 생략 웅엥웅 도 표현된다


3장 -오라클 전용 함수라서 나중에 봐도 됨 
167p 재밌는 함수 -마지막 날에 해준다

4장


조인 191p
-여러개의 데이터에서 정보를 가져오는 작업 
-from 절의 여러개의 테이블을 하나의 테이블로 만드는 작업
-조인조건 : 여러개의 테이블을 하나의 테이블로 만들때 조건
-조인조건이 부적합하거나 없일경우 ' 카타시안 곱 (cartesian product) ' 이 발생
카타시안 프로딕트는 

-조인 조건
-등가조인
-비등가 조인
-포괄 조인
-자체 조인


t 우리회사에 근무하는 사원의 이름과 그 사원이 근무하는 부서 이름을 출력하시오

select last_name, department_name
from employees, departments
/
>카타시안 프로딕트 나타나니 없애줌 (아래)

select last_name, department_name
from employees, departments
where employees.department_id = departments.department_id
/
>스키마가 어쩌고 해서 아래처럼 select 를 수정한다
>성능증가
>조인은 내부에서 정렬을 시키는데 sql에선 정렬은 최악이다 so 조인도 최악. . . .이지만 사용해야함

select employees.last_name, departments.department_name
from employees, departments
where employees.department_id = departments.department_id
/

조인조건 
-where 절에 한다
-" n - 1 " 규칙에 따라 작성되야한다
- N-1 은 웅엥웅


이큐 조인
-조인시 조인시킬 테이블에 같은 데이터가 있을 경우 사용
-가장많이 쓰이는 곳이 PK 와 FK 로 연결되어있는 구조 / PK와 FK를 조인

조인내에서 조인을 할 경우
and로 묶는다 OR시 카타시안 프로딕트발생

테이블에서 FROM절에서 알리아스(별칭) 사용가능 P200
-규직 테이블 풀네임을 쓸 경우에는 테이블 풀네임만 사용하고 별칭사용시 별칭만 사용시
-혼용이 안된단 뜻


추가검색 조건 P201
-

로비큐조인?  =비등가 조인
-데이터가 같지않을때 사용한다
-비교언산자를 사용한다 


아웃토어 조인
-이큐조인의 확장판
-조인 부족한 테이블? 에다가 null을 삽입하여 / 행을 출력할 수 있게 만든다

sol(1)
select employees.last_name, departments.department_name
from employees, departments
where employees.department_id = departments.department_id(+)
/

sol(2)
select employees.last_name, departments.department_name
from employees, departments
where employees.department_id(+) = departments.department_id
/

sol(3)
select employees.last_name, departments.department_name
from employees, departments
where employees.department_id(+) = departments.department_id(+)
/
>오라클에서는 불가능한 방법


t 부서별 연봉 2000연봉이상인 사원의 이름과 그 사원이 근무하는 부서이름, 연봉을 출력하라

my-
select employees.last_name, departments.department_name , salary*12
from employees, departments
where salary >=2000
AND employees.department_id = departments.department_id

True
select e.last_name , d.department_name, 12*e.salary
from employees e, departments d
where e.department_id = d.departments
and 12&e.salary >=12000


t 우리 회사에 근무하는 사원번호, 사원이름, 관리자번호,
select


셀프조인
지금까지 사용한 두가지 이상의 테이블을 가지고 조인한 것을 


알리아스 여러 테이블인척할 수 있다 알리아스를 사용하여 


자체 참조
-특징 : 첫번째 행이 nill이고 데이터가 같다  > 100프로 자체참조


ed > 실행후 > save test.sql > 저장경로에서 test.sql 찾기
> desc employees; 두번 적고 저장 > @test.sql 하면 동시실행됨 



last lase_namefrom employees
/

한 번에 


1- 106
2- 2
3- 34
4- 51

226p
1
select E.department_id, E.last_name, D.department_name
from employees E, departments D
where E.department_id = D.department_id
/
> from 에서 치환하고 다른절에서 사용가능

2
select distinct E.job_id, D.location_id
from employees E,  departments D
where E.department_id = 80
and E.department_id =D.department_id
/
> 중복조건 없앤 후 에 하기 
3
select E.last_name, D.department_name, L.location_id, L.city
from locations L , employees E , departments D
where E.department_id = D.department_id
and L.location_id = D.location_id
and E.commission_pct is not null
/
> 중복  냅둠
> 3개니까 3개 상쇄시킴

4
select E.last_name, D.department_name
from employees E, departments D
where E.last_name like '%a%'
and E.department_id = D.department_id
/


5단원 

p236
min 
max
아스키코드값이 가장 높,낮은것
모든 데이터타입에 사용가능
sum
avg
숫자만 연산 가능 하기에 문자는 불가

count 함수

p241
select count(commission_pct)
from employees
where department_id= 80
/


p242
select count(distnct commission_pct)
from employees;
/
>> employees 안에 있는 commission_pct 하는 이유 
사람의 수를 세는 거라서 

그룹함수는 null값을 무시한다
null값은 인식못한다

count 아스카 ? 함수 ㅅ ㅏ용하면 null값을 인식한다

select avg(nvl(commission_pct,0))
from employees;


select department_id, sum(salary)
from employees
/

가 에러나는 이유 
ERROR at line 1:
ORA-00937: not a single-group group function

department 는 107개 행으로 출력되는데
sum 그룹당 1 나온다

select department_id, sum(salary)
from employees
group by department_id
/

하면 원하는 결과가 나온다 

>cuz department가 그룹이 안되어있어서 그룹해줘야한다
그게 group by ~~~ 절


select department_id, job_id, sum(salary)
from employees
group by department_id, job_id
order by department_id
/
부서별 그룹후 직업별 그룹해준다
-order by 는 보기쉽게 정렬 


메이비

select department_id, job_id, sum(salary)
from employees
group by job_id,department_id
order by department_id
/

여도 결과는 같다 . gruop by 는 그런거 영향 안받음
select 에 영향을 받는다 


t 부서별 월급의 총합이 200000이상인 부서와 월급의 총합을 구하시오 
select department_id, job_id, sum(salary)
from employees
where sum(salary) >=200000
group by department_id, job_id
order by department_id
/
where 절이 출력전이 테이블 데이터를 제한하니까
이미 만들어지지않은 것을 제한 할 수 없어서 오류가 뜬다
그래서 다른 절이 추가된다 (having)


select department_id, job_id, sum(salary)
from employees
having sum(salary) >=200000
group by department_id, job_id
order by department_id
/

where > 행을 제한
having> 그룹을 제한 
위치 상관없다. group by 뒤에 있어도 ㄱㅊ