데이터베이스 복습

데이터 베이스
데이터 베이스 관리 / 데이터 베이스를 이용한 개발 (sql을 이용 / 데이터 베이스 설계)


DB -> 무결성이 보장된(정확성) 데이터를 모아 놓은 집합, 저장공간
   - 내가 필요할 때 꺼내서 활용 할 수있게 데이터 베이스에 저장을 해놓는다.
  데이터 / 정보: 데이터를 가공해서 얻어낸 결과 ->      1+1 =  2                            1+1 =   3
                                                                                            데이터/ 정보                   데이터/ 잘못된 정보
   
데이터 무결성: 데이터의 정확성 -> 데이터가 정확해야 올바른 정보를 얻을 수 있다.
테이블 -> 데이터를 저장하는 단위     
       분류를 만들어서 분류에 맞는 데이터만 저장해서 정확성을 보장하기위해 테이블에 저장

컬럼(열) -> 하나의 테이블은 여러개의 컬럼들로 구성

PK->테이블을 대표하는 컬럼에 부여, not null + uk : 반드시 값이 있어야하고, 중복되지 않는 값이여야 한다. 
                         nn + uk ex)주번, 학번 을 놓고 보면 대학교에서는 학번을 기본키로 / 주번,학번은 후보키
                                   
  desc 를 보면  테이블에 1. 타입별로 나타내는것 / 2. 제약조건(nn+uk)을 통해 데이터 무결성을 높여준다 -> 2가지 방법을 통해

  
null(비어있는 값) -> 공간은 있으나 비어있다
0: 숫자로 1byte할당    /  스페이스바: 문자 data, 1byte할당   /null: 공간은 있으나 비어있다, 
                                                                                                              null 값도 데이터 저장공간을 차지한다.

FK-> 다른 테이블의 컬럼의 데이터를 참조
    테이블과 테이블의 관계 설정, 종속적인 삭제 방지

테이블과 테이블을 FK를 사용하여 연결된 -> 관계형 데이터베이스


      emp           FK로       dept         FK           loca
  e_id  m_id   d_id       d_id        l_id        l_id      city
   PK <- FK로              PK로 emp의 e_id가 d_id참조        PK
      e_id를 자체 참조
       자체 참조의 FK의 처음은 null값  

"보통" FK와 PK 컬럼 이름은 같다  -> 데이터도 같은지 ?    FK는 중복,null값 허용   PK는 nn+uk의 특성(중복X,값이 반드시 있어야함)


데이터를 저장하는 데이터베이스에서
select 명령문을 통해 데이터를 얻는다.
select명령문을 쓸때
1. code를 작성하고 실행은X
2. 결과 예측
3. code를 실행             출력이 된 데이터가 의미하는것이 내가 원하는 데이터인지?
4. 결과를 예측과 비교해서 분석 해본다.    


실행순서 1 from  2 where  3 group by   4 having  5 select   6 order by


select distinct * cloumn alias
from
where
group by
having
order by



select         -> list절  -> 컬럼을 명시 (보려고 하는 data를 소유한 컬럼 이름을 명시하는 절)

select + distinct(중복제거)
       + *(모든컬럼)
   + 컬럼(원하는 컬럼이름 직접명시)
   + 컬럼 alias (열머리글 이름을 변경)               컬럼의 열머리글(출력이되는 컬럼명)
   + 컬럼 as alias   -> 그냥 blank 보다 성능이 좋다.  blank를 보면 column으로 인식해서 일단 from절에서 테이블인지 검증하고 그다음 alias로 인식
   + 컬럼 alias
   + 컬럼 "alias"  -> 대소문자 구문 가능
   
   
 select last_name "NAME" -> 원래 열머리글은 대문자라 굳이 대문자로 한번 더 변환 안시켜도 됨
 select last_name as "name" -> as, " " 둘 중 하나만 써도 가능 비효율적
 
 
 from   table1, table2      조인:    n개의 조인일때 n-1개의 조인조건 반드시 명시해야한다
                                  똑같은 이름 나열시 반드시 table의 접두어 명시    테이블의 풀네임 or alias 사용가능
  

where절 : 내가 원하는 (조건에 맞는) 데이터만 볼 수 있게 한다.  -> 조건을 명시하는 절
       행에 대한 조건을 주는 절
    1. 반드시 조건식을 명시 해야한다 ex) sal(cloumn이름) >(연산자) 1000(값)    ->  컬럼, 연산자, 비교를 할 값
    2. 값을 명시 할때 날짜, 문자에는 ' ' 를 써야한다.   컬럼과 테이블명을 구분하기위해
3. 값을 명시 할때 테이블에 저장된 형식 그대로 사용해야한다.
         last_name='SMITH' 로 쓰면 안된다. 테이블엔 last_name='Smith' 형식으로 저장되어 있다.
 
-where절  
단일비교연산자: = > < <=
비교연산자 : between A and B (A<B)
복수연산자 : in (30,50)    복수 값을 찾는 연산자
         like    -> 찾으려는 값에 대해 일부만 알고 있을때
          % (문장전체), _(문자하나)
         'S%' S로시작하는것 '%S' S로 끝나는것   '%S%' S가 들어가는것
    last_name = 'k%'    ->  이름이 k%인 사람을 찾아라    like일때만 %와_의 뜻이 달라짐. 다를땐 문자열

       조건이 추가 될때 and나 or로 추가 가능한데 이때 조건식도 같이 추가되어야한다!
          d_id = 10 and   d_id =90       90앞에도 조건식 d_id=을 써야한다.
  
not between
    not in             부정
    not like
    is not null
not 조건식도 가능은 하다.   not d_id=90  -> 90이 아닌것


--group by   : 그룹화 -> 같은것 끼리 모으는것
  avg(cloumn)  sum(cloumn)  max(cloumn)  min(cloumn)  count(cloumn)    -> 전부다 null값을 무시
                                                           count(*)만 null값도 포함
  avg, sum 은 숫자, 날짜 데이터에만 사용가능
  
  nvl(cloumn,0)   -> cloumn에 null이 있을때 0으로 출력
  
  함수 -> 하나 또는 여러개의 값을 받아 하나의 값을 낸다.
  
select 절에 그룹 함수가 사용 될때  그룹화 되지않은 컬럼들은 group by 절에 명시 되어야한다.
   ex) select a b sum(c)    -> group by a,b 가 명시되어야 한다.
   
   group by에 여러 컬럼이 명시되면 첫 컬럼을 기준으로 먼저 그룹화 후에 그룹화된 것에서 두번째 컬럼의 데이터를 그룹화한다.
   
   
 group by department_id, employee_id  -> department_id먼저 그룹화 되고 그룹화 된컬럼 안에서 employee_id가
                                         그룹화 되지만 employee_id는 PK로 중복이 없어서 107개의 데이터 그대로 나온다.
 
    컬럼의 특성이 다 다르기 때문에 그룹화 할때 컬럼의 특성을 알 필요가 있다.



--having: 그룹에 대한 조건을 주는 절
  select department_id, employee_id,salary
  from employees
  having avg(salary) > 15000           -> 그룹이 먼저 만들어지고 나서 그룹에 대한 조건을 줘야하므로 오류난다.
  
  
  where avg(sal)> 1   where절에는 그룹함수를 쓸수 없다X
  
  
  having   sal>1000   having절에는 일반 조건에 들어가는 것도 쓸 수 있지만 비효율적.
  
  
  --order by : 정렬   
   asc 오름차순(생략가능)
   desc 내림차순
   
 select department_id, salary "annsal"
  from employees
  order by "annsal"    -> 머릿말 기본은 대문자로 alias는 소문자이니까 order by절에도 "annsal"로 소문자로 써줘야한다
  
  
  
  select avg(commission_pct), sum(commission_pct)/107
  from employees;


avg(commission_pct)와 sum(commission_pct)/107가 같을거 같지만 그룹함수는 null을 무시하므로
avg(commission_pct)와 sum(commission_pct)/35의 값이 같게 나온다 하지만 틀린값인데


select avg(nvl(commission_pct,0)), sum(commission_pct)/107
  from employees;
  
  avg(nvl(commission_pct,0))로   안의 nvl이 먼저 실행되고 바깥의 avg 평균이 구해진다
  그래서 avg(nvl(commission_pct,0)) =  sum(commission_pct)/107 같아진다 -> 올바른답
  
  
    select department_id, max(avg(salary))
  from employees
  group by department_id;
  
      avg(salary)는 여러 값이 나오지만 max(avg(salary))는 하나의 값이 나와서 group by로 그룹화한게 틀린것.
  

 부서별 최대 연봉을 받는 사람의 이름을 출력하라.
select department_id, last_name, max(salary)
from employees
group by department_id, last_name;->이건 틀림   




---조인
등가조인: PK와 FK로 연결이된 두개의 테이블에서 데이터를 가져온다

   select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

행을 하나로 조합을 할때 무결성이 보장이 되어야 한다.
무결성을 보장하기 위해서 조건을 주자 (d_id = d_id) -> 조인조건
행에 대한 조건은 where절에 주니까 조인조건도 where절에 주게 되고 
n개의 테이블을 조인할때 조인조건은 최소 n-1개가 필요하다.


eq 등가조인 :PK-FK로 연결된 테이블에서 데이터를 같게해서 조인.
non eq 비등가조인 : 데이터가 전혀 다를때 사용   ex) between A and B
outor  포괄조인 : 어느 한쪽에 데이터가 더 많을때 사용

 select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;   
-> 106명나오는데   우리회사 사원은 107명
한명의 사원이 부서번호가 null 값이다.    우리회사 사원 전부 부서명을 알고싶을때 쓰는 

--outor조인
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);    -> 부족한 쪽에(+) 사용   부서가 없는 사원은 departments의 데이터가 없으므로 d.department_id에(+)를 써서 outor조인

   emp          dept(+)
   100   10    10   a
   101   20    20   b
   102   null 여기에 매칭이 되는 행이 없을때 없는쪽에(+)를 써서 조인.
   
   
   select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
-> 16개의 부서는 근무하는 사원이 없는 부서인데 outor조인으로 없는것이 나온것.
-> 부서중에 사원이 없는 부서가 있는데 employees의 e.department_id 쪽 데이터가 없으므로 (+)를 써서 outor조인