[ SQL ] Oracle 확장 sql문 #1 _ 그룹화 & 시퀀스

Oracle 확장 sql문

[ 그룹화 ]

• 다중행 함수의 집계 관련 함수는 그룹화를 하지 않으면 일반 컬럼과 사용할 순 없다

 1. group by 문

• group by 문을 사용하여 테이블의 컬럼을 그룹으로 나누고 각 그룹에 대한 요약 정보를 반환한다.
• select 문에 그룹 함수를 포함하고 group by 문에 개별 컬럼을 지정하지 않는 경우 개별 결과도 선택할 수 없다.
• where 문을 사용하면 컬럼을 그룹으로 나누기 전에 컬럼을 제외할 수 있다.
• group by 문에는 별칭을 사용할 수 없다.
• 여러 개의 group by 문을 나열하여 그룹과 하위 그룹에 대한 요약 결과를 반환할 수 있다.
• group by 문은 컬럼을 그룹화하지만, 결과에 대한 순서는 지정하지는 않으며 그룹화 순서를 지정하려면 order by 문을 사용한다.

select [distinct] *|columns [as 별칭] from tables
[where 조건식;]
group by column;
[order by column [asc/desc];]

• select 명령어
  표시할 컬럼이나 명령어를 지정한다. 컬럼은 하나 이상의 컬럼으로 이루어진 리스트이다.
• distinct 명령어
  중복을 방지한다.
• *(애스터리스크) 기호
  모든 컬럼을 선택한다.
• column 변수
  지정된 컬럼명을 설정한다.
• distinct 명령어
  중복을 방지한다.
• [as 별칭]
  컬럼명의 별칭이나 가상 컬럼을 생성하며 as 명령어는 생략할 수 있다.
• from tables
  컬럼을 포함하는 테이블들을 지정한다
• [where 조건식;]
  조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한한다.
  조건식은 조회할 조건에 대한 조건이나 표현식으로 ;(세미콜론)으로 종료한다.
• group by column
  테이블의 컬럼을 그룹으로 나누고 각 그룹에 대한 요약 정보를 반환하고 ;(세미콜론)으로 종료한다.
• [order by column [asc/desc];] 문
  오름차순과 내림차순과 같은 정렬 방식을 설정한다.
  asc 명령어는 오름차순으로 정렬하고 desc 명령어는 내림차순으로 정렬하며 ;(세미콜론)으로 종료한다.

 

▼ SELECT * FROM EMP;

 

 ex )  emp 테이블에서 부서번호로 그룹화하여 월급의 평균을 구한다

SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;

 

EMPNO : 사원번호

DEPTNO : 부서번호

 

 

 

 2. having 문

• having 문을 사용하여 표시할 그룹을 지정하면 집계 정보를 기초로 그룹을 추가로 제한할 수 있다.
• having 문은 반환되는 그룹을 지정된 조건이 참인 그룹으로 제한한다.
• having 문을 having 문을 사용할 때 수행단계
  ① 컬럼이 그룹화된다.
  ② 그룹 함수가 그룹에 적용된다.
  ③ having 문의 조건과 일치하는 그룹이 표시된다.
• having 문이 group by 문 앞에 올 수 있지만 권장하지 않는다.
• having 문이 그룹에 적용되기 전에 그룹이 형성되고 그룹 함수가 계산된다.

 select [distinct] *|columns [as 별칭] from tables
[where 조건식;]
group by column
having 반환되는 그룹;
[order by column [asc/desc];]

• select 명령어
  표시할 컬럼이나 명령어를 지정한다. 컬럼은 하나 이상의 컬럼으로 이루어진 리스트이다.
• distinct 명령어
  중복을 방지한다.
• *(애스터리스크) 기호
  모든 컬럼을 선택한다.
• column 변수
  지정된 컬럼명을 설정한다.
• distinct 명령어
  중복을 방지한다.
• [as 별칭]
  컬럼명의 별칭이나 가상 컬럼을 생성하며 as 명령어는 생략할 수 있다.
• from tables
  컬럼을 포함하는 테이블들을 지정한다
• [where 조건식;]
  조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한한다.
  조건식은 조회할 조건에 대한 조건이나 표현식으로 ;(세미콜론)으로 종료한다.
• group by column
  테이블의 컬럼을 그룹으로 나누고 각 그룹에 대한 요약 정보를 반환하고 ;(세미콜론)으로 종료한다.
• [order by column [asc/desc];] 문
  오름차순과 내림차순과 같은 정렬 방식을 설정한다.
  asc 명령어는 오름차순으로 정렬하고 desc 명령어는 내림차순으로 정렬하며 ;(세미콜론)으로 종료한다.

 ex )  emp 테이블에서 급여가 500 이상인 부서번호와 급여를 구한다.

SELECT MAX(SAL), DEPTNO FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) > 500;

 

 

 

 

[ 시퀀스 ]

• 시퀀스(sequence)는 값이 일정한 규칙에 따라서 연속적으로 자동 증가하는 오라클 객체이다.
• 시퀀스는 기본키(primary key)에서 사용하며 유일한 숫자를 자동으로 생성하는 자동 번호 발생기다.
• 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동으로 생성할 수 있다.
• 시퀀스가 메모리에 캐시 되었을 때 시퀀스값의 액세스 효율이 향상한다.
• 시퀀스는 테이블과는 독립적으로 저장되고 생성되며 공유할 수 있다.
• 시퀀스는 다음 2 개의 제약조건을 사용하여 값을 관리한다.
  * 시퀀스명.nextval : 시퀀스의 다음 값을 얻으며 nextval 은 next value 의 약자이다.
  * 시퀀스명.currval : 시퀀스의 현재 값을 얻으며 currval 은 current value 의 약자이다.
• 시퀀스값을 삭제하거나 데이터베이스가 비정상적으로 종료하면 1, 2, 3, 5, 7 과 같이 시퀀스값에 간격이 생기고 번호가 중간에 점프하므로 일정하게 순차적 번호 관리를 할 수가 없다.
• 일정하게 순차적 번호를 관리해야 하는 프로그램에서는 사용할 수가 없다.
• 시퀀스는 주로 일정하게 순차적 번호 관리가 필요 없는 게시판 번호로 많이 사용된다.
• 시퀀스 형식의 마지막에는 ;(세미콜론)으로 종료한다.

 

create sequence 시퀀스명
[start with n]
[increment by n]
[maxvalue n]
[minvalue n]
[cycle]
[cache n];

• create sequence 시퀀스명
  지정한 시퀀스명으로 시퀀스를 생성한다.
• [start with n]
  시퀀스 번호의 시작값을 n 에 지정하고 생략하면 기본적으로 1 부터 시작한다.
• [increment by n]
  시퀀스의 증가치를 n 에 지정하고 생략하면 기본적으로 1 씩 증가한다.
• [maxvalue n]
  시퀀스가 가질 수 있는 최대값을 n 에 지정한다.
• [minvalue n]
  시퀀스가 가질 수 있는 최소값을 n 에 지정한다.
• [cycle]
  지정된 시퀀스 값이 최대값까지 증가가 완료되면 다시 초기값에서 시작한다.
• [cache n]
  시퀀스가 관리할 캐시 개수를 n 에 지정하고 기본적으로 20 개를 관리한다.
  myboard 테이블을 생성하고 myboard 테이블의 num 컬럼에 시퀀스를 부여한다.

 

 ex 1 )  myboard 테이블에 데이터를 입력한다.

CREATE TABLE MYBOARD( NUM NUMBER(4) PRIMARY KEY, AUTHOR VARCHAR2(12), TITLE VARCHAR2(30), CONTENT VARCHAR2(60) ); --Table MYBOARD이(가) 생성되었습니다. CREATE SEQUENCE MYBOARD_SEQ; --Sequence MYBOARD_SEQ이(가) 생성되었습니다.

 

 

CF) 시퀀스 조회

CREATE SEQUENCE [시퀀스 명]
INCREMENT BY [증감숫자]  -- 시퀀스 증가값.(기본값 1), 
START WITH [시작숫자]    -- 시퀀스 시작값.
MAXVALUE [최대값] -- 시퀀스 최대값.(생략시 최대값 무한대 NOMAXVALUE)
MINVALUE [최소값] -- 시퀀스 최소값.(생략시 최소값 무한대 NOMINVALUE)
CYCLE OR NOCYCLE  -- 기본값은 NOCYCLE. CYCLE로 지정하면 최대값에 도달했을때 다시 최소값부터 시작.

 

 

▼ SELECT * FROM USER_SEQUENCES; -- 전체 시퀀스 조회

▼ SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'MYBOARD_SEQ';  -- 생성한 시퀀스 조회

 

 

 ex 2 )  myboard 테이블에 데이터를 입력한다.

INSERT INTO MYBOARD(NUM,AUTHOR,TITLE,CONTENT) VALUES(MYBOARD_SEQ.NEXTVAL,'철수','제목','내용이다.'); --1 행 이(가) 삽입되었습니다. SELECT * FROM MYBOARD;