[SQL] 시퀀스 / distinct / 조건절

[테이블 준비단계]

 1) 테이블을 만들기 위해 스크립트 작성

 

테이블을 어떻게 만들지 기본적인 견적을 만들기 위해 엑셀 사용

 

 ⊙ 굳이 엑셀이 아니어도 됨. - 자신이 알아볼 수 있는 스크립트를 작성함 (칼럼명, 제약조건, DATA타입 등)

 ⊙ 위의 스크립트를 기반으로 만든 create 문

create table sungjuk( 
sno number primary key 
,uname varchar2(50)    not null
,kor number(3)           check (kor between 0 and 100) 
,eng number(3)          check (eng between 0 and 100) 
,mat number(3)          check (mat between 0 and 100) 
,tot number(3)            default 0 
,aver number(5,2)        default 0 
,addr varchar2(30)        check(addr in ('Seoul', 'Jeju', 'Suwon', 'Busan')) 
,wdate date                default sysdate 
);

 

default		칼럼에 데이터가 입력되지 않은 경우, 자동으로 default 뒤에 있는 것이 입력 (ex. default 0)
[관계형 DB에서 테이블의 핵심기능]
C	Create	insert문
R	Read	select문
U	Update	update문
D	Delete	delete문

 

[자동으로 일련번호 부여]

 ⊙ 일련번호를 넣을 때 굳이 지정하지 않고 자동으로 만들 수 있음

 ⊙ Oracle : 시퀀스

 ⊙ M*SQL : identity

 

[시퀀스 (sequence)]

 1) 정의 : 연속적인 숫자값을 자동으로 증가시키는 숫자를 발생시키는 객체

 2) 생성 

create sequence 시퀀스 이름	시퀀스 이름 지정
increment by 숫자	몇 숫자 단위로 증가하는가
start with 숫자	시작하는 숫자
maxvalue 숫자	최대값
minvalue 숫자	최소값
cycle | nocycle	일련번호 순환여부
cache | nocache	빠른 처리를 위해 시퀀스의 값을 미리 받아둠, 서버가 꺼졌다 켜지면 중간에 고백이 생김 (nocache : 미리 받은게 없어서 그때그때 받아야 함)

 

<시퀀스 적용 예시>

create seqeunce sungjuk_seq
increment by 1
start with 103
maxvalue 100000000
nocache
nocycle;

cf) 아무런 조건 없이 시퀀스가 진행될 경우 시작값 : 1 / 증가값 : 1

 

 3) 시퀀스 호출 함수

  ⊙ 주의 : 시퀀스 생성 후 nextval을 호출해야 시퀀스에 초기값이 설정됨

  ⊙ nextval : 다음 값을 반환 함. 다음 번호 발급

select sungjuk_seq.nextval from dual;

★ dual : 임시테이블

 

  ⊙ currval : 현재 값을 반환함. 최근 발급된 번호

 select sungjuk-seq.currval from dual;       - 현재 시퀀스번호 확인

 

4) 서브쿼리를 이용한 일련번호 발급

  ⊙ 주의 : 시퀀스와 혼합해서 사용하지 않도록 주의

문) c_emp 테이블에 데이터 입력시 sequence를 이용해서 id를 입력하도록 206에서 시작하여 1씩 증가되고, 최대값은 999로 설정하여 시퀀스를 생성하시오
    시퀀스 이름 : c_emp_seq
create sequence c_emp_seq
create by 1
start with 206
maxvalue 999;

 

   ⊙ 시퀀스 목록 조회

     - select * from user_objects where object_type='sequence';

 

   ⊙ 국어점수의 최고점 조회 (현재 아무것도 넣은 것이 없기 때문에 null값 발생)

select max(kor) from sungjuk;

 

  ⊙ null 값이 나오면 0으로 바뀜

select nvl(max(kor), 0) from sungjuk;
elect nvl(max(kor),0)+1 from sungjuk;     - 0이 나오게 하고 그 뒤에 +1을 붙여서 1을 더해줌

 

   5) 성적 데이터 입력

성적 테이블에 입력

 

⊙ 오류 보고 

- SQL 오류: ORA00933: SQL command not properly ended 00933. 00000 -  "SQL command not properly ended" 

▶ Oracle 쿼리문 오류, 쉼표, 조건절 등 다시 확인 해볼 것

 

 

[distinct / group by]

1) distinct : 칼럼에 중복내용이 있으면 대표값 1개만 출력

 ⊙ 형식 : distinct 칼럼명

select distinct(addr) from sungjuk;                                  - addr가 겹치지 않고 대표값만 1개씩 출력됨
select distinct(addr) from sungjuk order by addr;               - 나온 addr 값을 오름차순으로 정렬
select distinct(addr) from sungjuk roder by addr desc;        - 나온 addr 값을 내림차순으로 정렬

 

  2) group by 절 : 칼럼에 동일 내용끼리 그룹화 시킴

   ⊙ 형식 : group by 칼럼명1, 칼럼명2 ~~

select addr from sungjuk group by addr;      - addr를 기준으로 그룹화

   ⊙ 오류발생 - ORA-00979: not a GROUP BY expression00979. 00000 -  "not a GROUP BY expression" 

       ▶ group by에 의한 결과값이 오로지 1개만 존재하는 값만 조회할 수 있다

 

   ⊙ 집계함수와 많이 사용 (통계 할 때)

<집계함수 예시>
select count(*),             - 레코트 개수
        sum(kor),             - 국어점수의 합
        avg(eng),             -  영어점수 평균 (주의할 것 ! : 평균 - avg), aver 아님
        max(mat),            - 수학점수 최고점
        min(tot)              - 총점 최저점
from sungjuk;

 

  ⊙ 예제

문) 각 주소별 인원수를 구하시오 (= 성적테이블을 주소를 기준으로 count 하시오) select addr, count(*) from sungjuk groop by addr; →  count(*) 로 칼럼명이 표현 되기 때문에 이를 바꾸기 위해서 'select addr, count(*) as cnt' 라고 적을 수 있음   ★ +) 칼럼명을 바꿀 때★ count(*) 라는 칼럼명을 바꿀 때, 위에서처럼  as 를 사용하여 : select addr, count(*) as cnt  라고 적을 수도 있고 as 없이 : select addr, count(*) cnt 도 가능   문) 주소별 인원수를 구한 후 주소순으로 정렬하시오 (내림차순이라는 말이 없으면 오름차순 정렬) select addr, count(*) from sungjuk group by addr order by addr;   문) 주소별 인원수를 구한 후 인원수 순으로 내림차순 정렬하시오 select addr, count(*) from sungjuk group by addr order by count(*) desc;    문) 주소별 국어점수 평균(소수점 제외)을 구한 후 국어점수 평균순으로 내림차순 정렬해서 조회하시오  select addr, rount(avg(kor), 0)                - ★ round(  ,  ) 함수를 이용해 소수점 관리 (  , 소수점 자리수) from sungjuk group by addr order by aver(kor) desc;   문) 지역별 국, 영, 수 최고점을 지역별순으로 정렬해서 조회하시오 select addr, max(kor), max(eng), max(mat) from sungjuk gropup by addr order by addr;

 

   ⊙ 1차값이 동일하다면 그 그룹내에서 2차 그룹이 가능하다 (3차까지도 많이 쓰지만 7~8차까지 가지는 않음)

문) 지역별로 그룹핑을 하고 만일 지역이 동일하다면 수학점수 별로 그룹핑을 하시오
select addr, mat
from sungjuk groupby addr, mat;        - 지역별로, 그 다음 수학점수 별로

 

 ⊙ 예제

문) aver 칼럼값을 구한 후 aver 칼럼값이 50점 이상인 레코드 대상으로 지역별 국영수 평균을 반올림해서 소수점 1자리까지 구한 후 조회하시오 update sungjuk set aver=(kor+eng+mat)/3;       - 먼저 'aver 칼럼값'을 구함   select * from sungjuk;              - 'aver칼럼값' 이 맞게 들어갔는지 확인   select addr, round(aver(kor), 1), round(avg(eng), 1), round(avg(mat), 1) - 국영수 평균을 소수점 1자리까지 반올림 from sungjuk where aver>=50                      - 'aver 칼럼값이 50점 이상인 레코드' group by addr                         - '지역별로 묶음' order by addr;

 

 

 

[조건절]

 ⊙ where 조건절

 ⊙ having 조건절 → group by 와 함께 사용

 ⊙ on      조건절 → 테이블 조인할 때 사용

select addr, count(*)
from sungjuk group by addr
having count(*)=3;                   - 성적테이블의 addr를 기준으로 그룹핑하여 count(*)가 3인 것만 보고싶을 때

 

 ⊙ 예제

문) 지역별 국어점수 평균을 구한 후 그 평균이 80점 이상인 지역만 조회 select addr, avg(kor) from sungjuk group by addr having aver(kor)>=80;   문) 지역별 국어점수 평균 (소수점 제외)을 구한 후 칼럼명을 aver_kor로 설정하고 평균이 80점 이하인 지역만 조회 select addr, round(avg(kor), 0) as aver_kor from sungjuk group by addr having aver(kor)<=80   문) 지역별 국어점수 평균을 구한 후 그 평균 60~79점 사이만 조회 select add, avg(kor) from sungjuk group by addr having avg(kor) between 60 and 79;