'SQL'에 해당되는 글 99건

조건에 따라 값을 지정해주는 Case 문

칼럼의 값이 숫자로 되어 있는 경우 이해도가 떨어지므로 이름으로 표기하는게 좋겠다는 생각이 들 수 있다. 이때 CASE WHEN END 함수를 이용하면 된다.

Case 문의 형식은 아래와 같이 2가지로 표시할 수 있다.

칼럼에 조건을 걸어서 출력하고 싶은 글자로 표시하는 방법과 칼럼 값을 출력하고 싶은 글자로 표시하는 방법

예제를 보면 이해가 빠를 것이므로 예문을 보자. 

SELECT CASE AAA WHEN '1' THEN a WHEN '2' THEN b ELSE c END FROM table_name;

설명 - AAA 칼럼의 값이 '1' 이면 a, '2' 이면 b, 둘다 아닐경우 c 를 출력

설명 : code별로 2015년 2월의 개수를 구하고자 한다. 아래처럼 조회(출력)하면 코드가 숫자로 정렬된다.

SELECT CODE,COUNT(*) AS "개수"

FROM table_name

WHERE regisdate = "201502" 

GROUP BY CODE;

설명 : 코드를 직접 값으로 지정하여 글자로 정렬되어 출력된다.

SELECT CASE CODE WHEN 1 THEN '사과' ELSE '포도' END, COUNT(*) AS "개수"

FROM table_name

WHERE regisdate = "201208"

GROUP BY CODE;

-- 조건 함수처리
SELECT empno, ename, case when sal >=0 and sal <= 1000 then 'E'
when sal > 1000 and sal <= 2000 then 'D'
when sal > 2000 and sal <= 3000 then 'C'
when sal > 3000 and sal <= 4000 then 'B'
when sal > 4000 and sal <= 5000 then 'A'
end 
FROM table_name;

조건안에 칼럼명이 들어간 경우에는 case 컬럼명 대신 case 만 적어도 인식된다.

(case when(is_finish = 0 and is_checking = 0) then role when is_checking = 1 then '완료' when is_checking = 2 then '수정' when is_checking = 3 then '검토중' else '대기' end) as is_checking

처럼 when 에 두가지 조건을 제시해서 결과 표시에는 특정 필드를 적어줄 수도 있다.

아니면 특정한 값을 계산해서 표시할 수도 있다.

오라클에서는 Decode 함수가 많이 사용된다.

-- decode 함수 : decode(A,B,'1') A가 B라면 1을 출력
select empno, ename, sal,
decode(sign(sal-4000),1,'A',
decode(sign(sal-3000),1,'B',
decode(sign(sal-2000),1,'C',
decode(sign(sal-1000),1,'D', 'E')
))) GRADE
from emp;

인덱스 설정법

인덱스와 조인(JOIN)에 대한 이해만 정확하게 하고 있으면 최적화된 SQL을 작성하는데 큰 도움이 된다.

인덱스(Index)란?

• 해당 데이터의 검색을 위해 전체 테이블을 읽지 않고 해당 row의 주소를 통해서 직접 접근할 경우에 사용
• 특정 칼럼에 Index를 생성하면, 해당 칼럼의 데이터들을 정렬하여 별도의 공간에 데이터의 물리적 주소와 함께 저장한다.
  
• NULL 은 Index 만들어지지 않는다. NULL이 많은 항목은 Index 로는 부적합하다.
• Index 의 가장 중요한 기능은 데이터의 조회(select) 속도를 빠르게 하는 것이다.
  Index가 없다면 특정한 값을 찾기 위해 모든 데이터를 다 뒤져야 한다.
• 테이블을 삭제하면 관련 인덱스는 자동으로 삭제된다.
• 인덱스를 사용하면 디스크의 I/O를 감소시킬 수 있다.
  

기본적으로 조건절에 등장하는 칼럼과 조인 조건으로 사용되는 컬럼에는 인덱스를 만드는 것이 성능 면에서 유리하다.

인덱스 설정

MySQL 인덱스 설정 예시

ALTER TABLE `SALES_DATA`
  ADD KEY `shop_agent_ym` (`shop_code`,`agent_code`,`YM`);

ref : 조인의 순서와 상관없이 사용되며, primary key나 unique 키 등의 제약조건이 없다. 인덱스의 종류와 관계없이 동등 조건으로 검색할 때는 ref 접근 방법이 사용된다.

WHERE 절은 "테이블내의 모든 행을 검색하는 대신 검색 조건을 지정하여 사용자가 원하는 행들만 검색하는 기능"이다.

WHERE 조건식은 단일 조건식과 복수 조건식이 있다.

연산의 우선순위는 비교연산자 (=, !=, <>, >, >=, <, <=) SQL연산자(BETWEEN, IN, LIKE, IS NULL), NOT, AND, OR 순이다.

검색조건을 설정할 때 LIKE '%검색어%' 를 사용해야 할 경우가 있는데 변수 앞에 '%' 를 사용하면 해당 칼럼에 Index를 설정했다 하더라도 FULL SCAN을 한다.

조건절에 자주 등장하는 칼럼이라고 판단하여 최적화된 인덱스를 생성했다 하더라도, Index를 사용할 수 없는 상황이 발생하는데 대표적인 경우가 칼럼의 내외부 변형, IS NULL, IS NOT NULL을 사용한 비교, 여러 칼럼에 대한 OR조건 사용, 부정형(NOT, <>, !=, NOT EXISTS) 비교, 그리고 위에 언급한 LIKE 검색시 변수 앞에 %를 사용하는 경우이다.

가령, 주소록 필드를 저장할 때 통으로 된 하나의 필드로 저장하는 경우에는 검색하고자 하는 값이 중간에 들어가게 되어 FULL SCAN을 할 수 밖에 없다. 이럴 때는 칼럼을 여러개로 분리하여 앞에 %를 사용하지 않도록 하고 AND 조건으로 검색을 하도록 한다.

select * from dept where substr(dname,1,3) = 'DEF' ; 에서 dname 칼럼에 번형을 가한 경우에는 dname 칼럼에 인덱스(Index)를 설정해 두었더라도 Index를 타지 않는다.

select * from dept where dname LIKE 'DEF%' ; 로 수정하면 Index를 탄다.

다음은 Oracle SQL Developer 에서 scott 계정에 접속하여 테스트한 결과이다.

먼저, sal 칼럼에 Index를 설정했다.

create index emp_sal_idx on emp (sal) ;  -- sal 칼럼에 인덱스 생성

인덱스(Index) 삭제 명령어는 drop index emp_sal_dix; -- 인덱스 삭제

select * from emp where sal * 12 < 10,000 ;

select * from emp where sal < 10,000 / 12 ;

WHERE 조건식 을 어떻게 사용하느냐에 따라 성능에 영향을 미칠 수 있다. 개발자들의 SQL 사용 능력의 개인차가 심하다. 동일한 실행결과를 추출하는 다양한 형태의 SQL 이 존재한다.

성능향상을 위해서는

• index를 타는지 항상 체크하라.
  index를 활용하지 않은 검색은 데이터가 많으면 많을수록 성능은 급격히 떨어진다
  하지만 성능을 최적화하기 위해 무조건 index를 생성하는 것은 아니다. 경우에 따라서는 테이블 전체를 검색하는 방법이 성능이 보장될 수도 있다.
  
• select 쿼리문 작성시 필요한 칼럼(column)만 명시하라. select * 을 사용하는 것을 피하라.
  사용하지 않는 데이터를 호출하는 것만으로도 이미 많은 부하가 생긴다. 특히 text 타입의 데이터를 호출하는 경우는 그 정도가 심해진다.
• where조건문의 왼쪽은 되도록 변형되지 않은 순수한 column만을 선언하라.
  where 조건을 AND 등으로 여러개 조건을 설정해야 할 경우라면 가장 왼쪽 조건에서 대부분의 데이터가 걸러지도록 칼럼을 선택하고 설정된 칼럼에 인덱스를 설정하는 것이 좋다.
  
• JOIN을 사용하는 경우 INNER JOIN을 되도록 사용하라.
  두개의 테이블을 JOIN 해서 사용할 때 JOIN 방법에 따라서 수행속도의 차이는 현저히 나타난다. 처리결과는 같지만 연결하기 위해 처리하는 일량에서 차이가 난다.
  
• 서브쿼리 사용시 불필요한 select 구문을 줄여라.
  

전반적인 성능향상을 꾀하려면 CPU, 메모리, I/O 의 밸런스를 맞추어야 한다. DB성능 튜닝은 이 세가지 자원에 걸리는 부하를 적절하게 분산시켜 비용대비 최적의 성능을 뽑아내는것이 핵심이다.

일반적으로 대용량 테이블이라 하더라도 인덱스가 제대로 작동하고 있다면 검색 자체가 성능저하를 가져 오지는 않는다. 다만, 복수의 JOIN과 같이 대량의 Nested Loop 가 발생한다면 엄청난 양의 I/O가 발생하고 이것이 RDB전체의 성능을 크게 저하시키는 원인이 되기도 한다.

HDD의 I/O 속도는 메모리에 비하여 많이 느리다. 10,000RPM HDD와 DDR3-2500 메모리의 성능 차이는 약 800배에 이른다. DBMS 같은 시스템에서는 효과적으로 버퍼(캐시)를 구현하여 I/O 발생을 최소화하도록 구현하고 있다. 그래서 히트(hit)율이 높을 때는, HDD에서 SSD로 교체해도 성능 향상 효과가 미미한 경우가 있다. 하지만 SSD를 사용하면 버퍼 크기를 작게 유지해도 되기 때문에 대용량의 메모리를 유지할 필요가 적어지기도 한다.

MYSQL Clone 의 성능을 다룬 자료가 있는데 다시 보면 도움이 될 거 같아서 링크를 걸어둔다. http://blog.embian.com/5

NULL 검색

where username is null;

MySQL 운영하면서 겪은 문제점과 해법들

원본 : http://vstarmanv.tistory.com/350

컨설팅 회사에 MySQL을 운영하면서 겪고 있는 문제들을 해소하고자 여러가지를 물어보았고 큰 도움을 얻었다.본격적인 컨설팅은 아니었기 때문에 돈은 지불하지 않고 도움을 받았다.

1. 데이터가 이천만건 정도 있는데 테이블의 스키마를 변경하려고 하면 많은 시간이 걸리는데 어떻게 해야하나?

• 페이스북이 개발한 툴킷을 이용한다. 온라인으로 스키마 변경하는데는 3가지 방법이 있는데 페이스북에서 개발한 방법이 제일 좋다.
• InnoDB 대신 TokuDB 를 사용할 수도 있다. 컬럼을 추가하거나 수정, 삭제, 인덱스 걸기 등의 스키마 변경시 InnoDB엔진이 몇시간이 걸릴 때 TokuDB 엔진은 몇초면 된다.

2. 데이터베이스 부하가 심하다. 쿼리 최적화 외에 성능 개선할 방법이 없을까?

• MySQL의 변형인 MariaDB를 추천한다. MySQL으로 운영하다가도 바이너리만 MariaDB 를 설치하면 된다. MySQL 상용버전에 들어있는 Thread Pool 같은 기능을 무료로 사용 가능하며, MySQL보다 더 많은 테스트가 이루어지고 테이블 변경시 변경 상황 등이 제공된다. Oracle MySQL 을 쓰면서 아쉬웠던 점이 해결된다.
• TokuDB 라는 것도 있다. InnoDB를 대신하여 사용 가능하며 쓰기 속도가 크게 향상되었다. SSD에서 Insert 는 InnoDB 대비 9배 빨라졌다. 단, 유료다. 그러나 연구용이나 스타트업은 싸게 해준다.

3. 읽기쪽 부하를 줄이기 위해 Slave를 추가해야하는데 지금까지는 잠시 서비스를 잠시 중단하고 복사하는 방법을 사용했는데 좋은 방법이 없을까?

• Xtrabackup 을 사용하면 서버가 돌아가는 중간에도 데이터를 백업할 수 있다.
• 증분 백업도 가능하다.
• Xtrabackup 의 단점은 데이터를 통째로 바이너리 형태로만 백업한다는 점이다.
• SQL 형태로 뽑아내고 싶다면 Mydumper 를 추천한다.
  

4. 몇천만건이 넘어가는 데이터를 쌓을 때 주의할 점은?

• 100만건이 넘어가면 꼭 파티셔닝을 고려해야 한다.
• 대체로 시간단위로 데이터를 물리적으로 분리해서 저장한다. 적절히만 사용해도 수십억건의 데이터 관리도 불가능하지 않다.
  

5. 검색 부하가 매우 심하다. 좋은 검색 엔진을 추천해달라. 

• MyISAM 스토리지 엔진은 Fulltext 검색에 부하가 심하다.
  
• 스핑크스를 추천한다.
• 추천 받아서 적용해보니, 위치검색, 한글 검색, 비 텍스트 검색 모두에서 매우 빠른 성능을 보여준다. 게다가 확장성이 좋아서 저가 서버에 분산 배치해두었는데 로드가 거의 없다. 기존에는 MyISAM Full-text Search 을 쓰다가 Groonga Full-text Search 를 사용했었는데 MyISAM 은 Table locking 때문에 정기적으로 크게 느려졌고, Groonga 라는 검색엔진은 버그도 많고 비 텍스트 검색에서 최악의 성능을 보여준다. 암튼 MySQL 서버 로드가 무지 무지 올라갔었다. 그런데 스핑크스를 도입하면서 전반적으로 빨라지고 위치검색도 덤으로 10배 빨라졌다. 매우 만족한다.

6. 마스터 서버가 다운된적이 있다. 이중화 하려면 어떻게 해야하나?

• MySQL MHA 를 추천한다.
• 기타 대안 : MySQL Cluster 혹은 MySQL-MMM 

7. 어떤 쿼리가 부하를 일으키는지 확인하는데 효과적인 방법 없을까? Slow Log 가 너무 많이 쌓여서 우선순위대로 정렬이 되면 좋겠다.

MONyog를 추천한다.

• 실시간으로 쿼리를 분석하고 느린 쿼리들을 취합하여 가장 느린 쿼리부터 개선할 수 있다. 
• 어떤 쿼리가 Lock 을 유발하는지 알 수 있다. 놀라운것은, 어떤 쿼리가 Lock에 영향을 받았는지 까지 알 수 있다. 환상적이다.
• Cache Miss 같은 것이 어떤곳에서 발생하는지를 보고 메모리를 늘려야 하는지 등을 결정할 수 있게 해준다.
• Connection Refuse 가 얼마나 일어나는지 확인가능하다.
• 어떤 테이블이 많이 접근되는지 알 수 있다. (가장 접근이 많은 테이블 부터 최적화를 할 수 있다)

[기타] 내가 생각하는 MySQL의 장점

온라인 스키마 변경, 고가용성 때문에 NoSQL을 고려한적이 많았으나 MySQL에서 해결 가능하다. NoSQL은 숙련된 인력을 구하기 힘들기 때문에 개발 비용이 더 올라갈 수 있고 데이터 분석에 SQL 만큼 좋은 인터페이스를 가진 것이 거의 없다. 빨리 치고 나가야하는 스타트업에게 MySQL은 좋은 선택인것 같다

DBMS는 컴퓨터에 저장된 대량의 데이터를 체계적으로 관리하고 사용자가 원하는 정보를 효과적으로 검색하기 위한 시스템 소프트웨어를 의미한다

DBMS에 의해 관리되는 데이터의 집합을 ‘데이터베이스’라고 한다.

데이터베이스(DB)에는 많은 테이블이 존재할 수 있다.

테이블 1개로 원하는 데이터를 모두 저장할 수도 있지만 보통은 테이블을 설계할 때 서로 연관되게 여러개의 테이블을 만든다.

테이블에 있는 데이터 검색은 SELECT 명령어를 사용한다.

데이터베이스(DB)에서 데이터를 검색하려면 SQL을 사용하여 데이터를 요청한다.

SQL문은 여러 줄에 걸쳐서 작성 가능하고 반드시 ;(세미콜론)으로 끝난다.

요청하는 문법은 SELECT / FROM / WHERE 로 되어 있다.

SELECT 칼럼1, 칼럼2 FROM 테이블명 WHERE 조건

위 그림으로 설명하자면 칼럼1은 품목, 칼럼2는 단가, 칼럼3는 코드 라고 보면 된다.

테이블로부터 (FROM 테이블) 꺼내는(SELECT) 것이 무엇인가를 정해주는 거라고 이해하면 쉽다.

테이블은 데이타베이스의 기본적인 데이타 저장 단위 이다. 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 된다.

SELECT * FROM 테이블;   → *는 모든 칼럼을 의미한다. 즉 테이블의 모든 칼럼을 다 조회(출력)하라는 의미이다. 만약 데이터 갯수(record : 행)가 1만개 정도 된다고 하면, 모든 데이터를 모니터 상에 출력하는 것은 무의미할 것이다. 보여줄 데이터를 딱 10개만 보여주라는 의미로 한정하는 것은 LIMIT 10 을 붙이면 된다.

SELECT * FROM 테이블 LIMIT 10;

출력하고 싶은 조건을 지정할 때는 WHERE 를 사용해서 WHERE 품목 = '구두' 로 한정할 수 있다.

SELECT * FROM 테이블 WHERE 품목 = '구두';

SQL문은 대소문자를 구별하지 않는다. 즉 SELECT 라고 대문자로 써도 되고, select 라고 소문자로 써도 된다.

테이블내의 특정 칼럼 데이터를 보고자 한다면 SELECT 뒤에 해당 칼럼을 차례대로 기술하고 쉼표로 구분해서 여러개를 지정할 수 있다.

SELECT 품목, 단가 FROM 테이블명;

WHERE 조건을 주는 여러가지 사항을 잘 알아둘 필요가 있다.

MySQL 주소에서 지역명 정리하기

DB 데이터 지역명이 서울특별시, 부산광역시 등으로 길게 나오는 경우보다 간략하게 서울, 부산으로 표기되도록 하는게 검색하는데 좀더 편하다.

아래 SQL문은 DB에 저장된 데이터에 지역명을 간략하게 업데이트 하는 명령어다.

SELECT * FROM addr_data WHERE addr0 LIKE '서울%';
SELECT * FROM addr_data WHERE addr0 LIKE '서울특별시%';
update addr_data set addr0=replace(addr0,'서울특별시','서울');
update addr_data set addr0=replace(addr0,'경기도','경기');
update addr_data set addr0=replace(addr0,'강원도','강원');
update addr_data set addr0=replace(addr0,'경상북도','경북');
update addr_data set addr0=replace(addr0,'경상남도','경남');
update addr_data set addr0=replace(addr0,'충청북도','충북');
update addr_data set addr0=replace(addr0,'충청남도','충남');
update addr_data set addr0=replace(addr0,'전라북도','전북');
update addr_data set addr0=replace(addr0,'전라남도','전남');
update addr_data set addr0=replace(addr0,'제주도','제주');
update addr_data set addr0=replace(addr0,'인천광역시','인천');
update addr_data set addr0=replace(addr0,'대전광역시','대전');
update addr_data set addr0=replace(addr0,'부산광역시','부산');
update addr_data set addr0=replace(addr0,'대구광역시','대구');
update addr_data set addr0=replace(addr0,'광주광역시','광주');
update addr_data set addr0=replace(addr0,'울산광역시','울산');
update addr_data set addr0=replace(addr0,'세종특별자치','세종');

[오라클] 테이블 생성/변경시 제약조건(Constraint)

테이블을 생성/변경할 때 제약조건을 설정할 수가 있으며, 제약조건이란 테이블의 해당칼럼에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 정하는 규칙이다.

- 제약조건은 테이블 Level 및 컬럼 Level 에서 규칙을 적용한다.

- 제약조건은 종속성이 존재할 경우 게시물 삭제를 방지한다.

- 테이블에서 행이 삽입, 갱신, 삭제될 때마다 테이블에서 규칙을 적용한다.

- 테이블 생성과 동시에 지정할 수도 있고, 테이블 생성 후에 추가할 수도 있다.

- 제약조건은 user_constraints 데이터 딕셔너리에서 검색할 수 있다.

제약조건의 종류

제약조건 정의 방법

1. 테이블 Level 지정 방법

   - 테이블 칼럼 정의와는 별개로 정의한다.

   - NOT NULL 제약조건을 제외한 모든 제약조건 정의가 가능하다.

2. 칼럼 Level 지정방법

   - 한개의 칼럼에 한개의 제약조건만 정의 가능하다.

   - 모든 제약조건 정의 가능하다.

통화이력 산출 SQL

최근 한달동안 통화이력이 없는 전화번호 현황을 출력하고 싶다면 어떻게 해야 할까?

where 날짜 >= date_add(now(), interval -1 month) -- 한달전
where 날짜 >= date_add(now(), interval -1 day) -- 하루전
where 날짜 >= date_add(now(), interval -1 hour) -- 한시간전
where date_format(D_time,"%Y-%m-%d") = current_date; -- 오늘날짜인 거 가져오기
where D_time > date_sub(now(), interval 1 day); -- 등록된지 24시간이 안된 거

SELECT TO_DAYS('2015-01-20') - TO_DAYS('2014-11-15' ) AS "날짜차이";

SELECT now(), hour(now())as "시간" , minute(now()) as "분", second(now()) as "초", curdate(), curtime();

SELECT WEEKDAY(CURDATE()), WEEKDAY('2015-11-12');

0=월요일,1=화요일,2=수요일,3=목요일,4=금요일,5=토요일,6=일요일을 의미한다.

# 1일 추가하기
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);

# 한달 더하기
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
 

테이블을 생성하고 자료를 업로드할 때 자료에 한글이 있을 경우 깨지지 않게 처리를 하려면 인코딩 타입을 잘 맞춰줘야 한다.

mysql> show variables like '%char%';  -- character set 현황 조회

mysql> set character_set_server=utf8;  -- character set 변경

mysql> set character_set_database=utf8; -- character set 변경

mysql> create database work default character set utf8 collate utf8_general_ci;  -- 데이터베이스 생성

mysql> show databases;  -- 데이터베이스 보기

생성된 데이터베이스로 접속

mysql> use work;  -- 데이터베이스 접속

데이터베이스를 생성할 때 가장 편하게 하는 방법은 툴을 이용하는 방법이다.

phpMyAdmin 을 이용하여 데이터베이스를 백업하고 몇가지 툴 기능을 이용해보자.

위와 같이 설정하면 테이블 구조만 백업된다.

EditPlus 로 열어보니....

로 테이블 구조가 만들어져 있었다.

처음에 테이블 만들 때 Engine=MyISAM 으로 설정하지 않아서 InnoDB 로 만들어져 있었나 보다.

이 테이블 구조에서 테이블명을 변경하고 필요한 칼럼 등을 수정하고 나서

phpMyAdmin 에 복사하여 붙여넣기를 하면 손쉽게 테이블이 생성된다.

테이블명을 test_exercise 라고 변경하고 나서 한번 올려보자.

테이블이 생성된 것을 확인할 수 있다.

간단한 테이블 생성은 phpMyAdmin 을 이용하면 쉽게 해결할 수 있다.

MySQL 특정 칼럼의 행 비교후 개수 카운트

테이블을 정렬하여 테이블을 새로 생성하여 만든 테이블이 test_sort 테이블이다.

테이블의 특정 칼럼(serviceNo)이 중복된 값을 가지고 있다. 중복된 숫자를 카운트로 좀 나타내고 싶다.

어떻게 해야 할까?

결론부터 얘기하지면 아래 모습으로 결과를 얻고 싶다.

위아래 칼럼이 서로 같은 경우 개수를 증가하게 표시를 하고 싶다.

select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort limit 20;

일단 전부를 다 표시하는 것보다 20개만 발췌하여 결과값을 얻고 싶었다.

checking 이란 칼럼은 실제 test_sort 테이블에는 존재하지 않는 칼럼이라는 것은 위의 그림에서 확인할 수 있다.

그럼 이런 결과를 도출할 수 있을까?

사용자변수를 이용하여 1행, 2행, 3행 이렇게 비교를 하는 것이다.

SELECT / FROM / WHERE 구조의 의미는 다 아는 것이지만 다시 한번 살펴보자.

가장 먼저 FROM 테이블명에서 값을 가져오고, 그 다음에 WHERE 에서 조건에 맞는 것만 가져온다.

그 다음에 SELECT 구문에서 조회(출력)한다.

1행에서 @serviceNo_prev:=serviceNo 에는 serviceNo 값을 할당한다.

하지만 if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1)이 먼저 실행된다.

@serviceNo_prev 라는 사용자변수에는 값이 할당되지 않았다.

따라서 첫행에서는 값이 서로다르다. 따라서 @rownum:=1 이 선택된다.

2행에서는 if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1)

2행의 serviceNo 값과 @serviceNo_prev 에는 1행의 serviceNo 를 할당했으므로 서로 동일하다.

따라서 @rownum:=@rownum+1 즉, 2가 된다.

이런 식으로 처리하여 행간에 값을 비교하여 checking 칼럼을 생성한다.

그런데 보여주고자 하는 값이 uid, Name, serviceNo, checking, openDate, closeDate 이고 serviceNo_prev 는 아니라고 하면 어떻게 해야 할까?

윗 그림 전체를 임시테이블로 생각하면 된다. 즉 실제 물리적인 테이블에 저장된 것이라고 보고

테이블에서 원하는 필드만 조회(출력)해서 보여주면 된다.

select uid, Name, serviceNo, checking, openDate, closeDate from (select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort limit 20) a;

이렇게 이해하면 쉽게 이해가 될 것이다.

/* 테이블 구조만 복사 */

CREATE TABLE `생성될 테이블명` LIKE `구조를 복사할 테이블`;

/* 구조와 내용을 복사 */

CREATE TABLE `생성될 테이블명` AS SELECT * FROM `구조를 복사할 테이블`;

테이블 구조만 복사해서 새로운 테이블을 생성해보자.

테이블 구조에 필요한 칼럼이 하나 빠져있다. checking 칼럼을 추가한다.

- 지정 칼럼 뒤에 : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입 AFTER 칼럼이름
- 제일 앞에        : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입 FIRST
- 마지막에 추가  : ALTER TABLE 테이블명 ADD COLUMN 칼럼명 칼럼타입

칼럼 type 이 default 가 NULL 이라서 default 를 1로 변경하고자 한다.

/* 컬럼 타입 수정 */
alter table 테이블명 modify 컬럼명 varchar(14);
alter table test_fin modify checking int(2) not null default '1';

alter table test_fin modify checking tinyint(1) not null default '1';

공간을 가장 작게 차지하는 tinyint 로 변경해봤다.

이제 새로 만든 테이블에 위에서 작업한 내용을 복사해서 테이브를 집어넣어 보자.

INSERT INTO 테이블명 (칼럼1, 칼럼2, ...)

SELECT 구문

을 사용하면 된다.

insert into test_fin (uid, Name, serviceNo, checking, openDate, closeDate)
select uid, Name, serviceNo, checking, openDate, closeDate from (select uid, Name, serviceNo, if(serviceNo=@serviceNo_prev,@rownum:=@rownum+1,@rownum:=1) as checking, openDate, closeDate, (@serviceNo_prev:=serviceNo) as serviceNo_prev from test_sort) a;

제대로 데이터가 입력되었는지 확인해보니 정상적으로 입력되었다.

테이블 작업을 해서 새로운 테이블에 칼럼을 추가한 결과를 저정했다.

위 작업을 하면서 결과가 제대로 나오기 위해서는 EditPlus 에서 내용을 입력한 후 한줄로 처리한 다음에 복사하여 붙여넣기를 할 경우에만 정상적으로 값이 나왔다.

두줄로 분리하여 작업을 한 경우에는 사용자변수가 제대로 처리가 안되었다.

테이블 칼럼을 삭제해야 할 경우가 있다.

이럴 때 사용하는 명령어는

alter table 테이블명 drop [column];

예제를 통해 테이블 칼럼이 삭제되었는지 확인해보자.

테이블 설계시에 character set 과 콜레이션은 신중하게 골라야 한다.

데이터베이스에서 문자셋을 섞어 쓰면 엉망이 된다.

특정 문자가 데이터에 나타나기 전까진 잘 동작하다가 어느 시점에 문제가 발생하기 시작한다.

character set 과 콜레이션을 상호 변환하는 일은 일부 연산에 오버헤드를 더한다.

일부 character set 은 CPU 연산이 더 필요하고, 메모리와 저장공간을 더 소비하며,

심한 경우 indexing을 무용지물로 만들기도 한다.

오류를 해결하려면 ALTER TABLE 을 이용해 문자셋을 변경해야 한다.

ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;
ALTER TABLE 테이블명 DEFAULT CHARACTER SET utf8;   -- utf8 대신 euckr 로 설정도 가능

DATABASE와 TABLE 생성시 CHARACTER SET 을 생락하면 기본값을 가져가게 된다.
DATABASE는 my.ini의 설정값을 기본값으로 갖고, TABLE은 DATABASE의 설정값을 기본값으로 갖게 된다.

두개의 테이블을 다른 character set을 가진 문자 칼럼에 대해 조인한다면,

MySQL 은 그중 하나를 변환해야 한다. 변환은 인덱스를 무용지물로 만들기도 하는데,

변환이 칼럼을 감싸는 함수와 같기 때문에 인덱스 적용이 안된다.

multibyte character set 에서는 문자 1개의 길이가 1byte가 아니다.

multibyte character set 을 다룰 땐 char_length() 함수를 써서 문자의 개수를 세야 한다.

select uid, char_length(kor) from mail_data where char_length(kor) > 50;

경고(warnings)가 발생하면 show warnings; 로 경고 내용을 확인해 보는게 좋다.

default character set 으로 utf-8 로 지정하면 편하다.

한글을 utf-8 로 지정하면 한 글자당 3bytes 를 차지한다.

영문은 utf-8 로 지정하면 한 글자당 1byte 를 차지한다. (저장공간 낭비 안함)

MySQL 사용자 정의 변수

MySQL에서 하나의 명령문에 있는 값을 다른 명령문으로 전달하는 것이 가능하다.

사용자 정의 변수에 값을 저장한 후에 나중에 그 값을 참조할 수 있다.

한 클라이언트에서 정의된 사용자 변수는 다른 클라이언트에서는 보이지 않으며 사용할 수 없다. 

해당 커넥션에서 정의된 모든 변수는 클라이언트가 종료되면 자동으로 해제된다.

사용자 변수는 @var_name 이라고 쓰며, 항상 @ 하나가 따라와야 한다.

사용자 변수 이름은 알파벳, 숫자, ‘.’, ‘_’, 그리고 ‘$’을 사용할 수 있다.

사용자 변수 값 할당 방법

SET @var_name = expr  (정수, 실수, 문자열, NULL) SET 변수할당은 = 또는 := 둘다 사용 가능하다.

비 SET 문에서는 := 로 할당한다. = 는 비교 연산자로 취급되기 때문이다.

여러개의 변수를 선언할때는 DECLARE @ 변수명 자료형, @변수명 자료형

과 같은 방법으로 계속 적어주면 된다

SELECT 문의 가장 기본은 SELECT/FROM/WHERE이다. 

복잡한 쿼리문을 작성할 때도 SELECT/FROM/WHERE를 작성하고 나머지 부분을 채우는 것이 좋다

SET @rownum = 0;

select (@rownum := @rownum + 1) as No, name, email from emp order by name DESC;

SET을 지정하지 않고 한번에 처리하려면

select (@rownum := @rownum + 1) as No, e.name, e.email from (selct @rownum := 0) dummy, emp e order by e.name DESC;

No 를 지정하면 순차적으로 번호가 증가되어 결과를 찾을 때 좀더 편할 것이다.

from 절에서 조인(join) 으로 사용한 emp 테이블의 별칭을 e 로 지정했는데 select 문에서 e.name, e.email 등과 같이 명확하게 해주면 좋지만 그냥 name, email 로 적어도 무방하다.

참조

http://www.mysqlkorea.com/sub.html?mcode=manual&scode=01&m_no=21582&cat1=9&cat2=292&cat3=0&lang=k

텍스트 자료를 분석하기 위해서 MySQL DB에 저장하는 방법이다.

텍스트 자료를 DB에 저장하기 위해서는 한글의 경우 Encoding 모드(character set)가 서로 일치해야 한다.

MySQL DB 에 테이블을 생성할 때부터

업로드할 텍스트자료의 형식을 일치시켜 줘야 한다.

가장 먼저 DB character set 설정이 어떻게 되어 있는지부터 확인한다.

mysql> show variables like '%char%';  -- character set 정보 조회

character set 이 UTF-8 로 설정되어 있는 것을 확인할 수 있다.

만약 이 설정값이 latin1 으로 되어 있다면, my.cnf 에서 변경해줘야 한다.

변경을 해줬다면 데몬을 재실행하여야 한다.

character set 설정 상태를 확인했다면 이제 DB 를 생성해보자.

1.39 sec 는 최적화가 되어 있는 상태로 보면 된다.

저장할 디렉토리가 /usr/local/apache/htdocs/sql_backup/ 라고 가정하고 파일을 저장해보자.

디렉토리에는 쓰기권한이 설정되어 있어야 한다.

SELECT * INTO OUTFILE '/usr/local/apache/htdocs/sql_backup/memberfile.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM member;

로 하면 텍스트 형태의 파일로 저장된 것을 확인할 수 있다.

대용량 테이블을 운용하는 경우 도움이 될만한 아티클 "Ten ways to improve the performance of large tables in MySQL"이 있어, 이 블로그에 정리해 봅니다.
아래 가지 10가지 방법을 잘 고민해 보면 좋은 도움을 받을 수 있을 것 같습니다.

오늘은 성능 문제를 일으키는 원인이 되는 대용량 테이블의 성능을 개선하는 방법을 살펴본다. 여기의 조언들은 일부는 많은 테이블을 가진 큰 데이터베이스에 적용할 수 있지만 대부분의 경우는 개별적으로 특별히 큰 테이블이 더 큰 문제라는 것이다.

테이블의 내용 변경의 속도는 테이블 사이즈가 커질수록 줄어드는 것을 일반적으로 알려진 사실이다. 아래는 B+Tree 인덱스의 성능을 시계열로 보여주는 그래프이다.



위의 그래프는 MySQL@Facebook에서 포스팅한 것이다. 이것은 insert buffer를 해제한 상태(물론 추천 하지는 않고, 데모 목적)에서 테이블에 10억 행을 insert했을 때의 성능 저하를 나타낸 것이다. 이 벤치 마크는 iibench 도구를 사용했고, TokuTek에서 디자인한 것이다.

그래서 우리는 이러한 성능 저하가 인덱스 구조에 의한 것이라고 생각했지만, 그래도 우리는 이 곡선을 급격하게 내려가지 않고, 옆으로 길게 늘어지도록 할 수 있는 방법을 모색해 봐야 한다.

대용량 테이블의 성능에 영향을 줄일 수 있는 10가지 방법

용어 정의

• Change Buffer: MySQL 5.5이전에는 insert buffer라고 했으며, 이름 그대로 INSERT의 성능을 향상시키기 위한 버퍼다. insert buffer는 새로운 행을 삽입하는 INSERT에 대해서만 유효했는데, Change Buffer는 UPDATE 및 DELETE도 좋은 효율성을 보여준다.

출처 : http://www.mimul.com/pebble/default/2013/12/07/1386399914136.html

특정 게시물 앞, 뒤 레코드값 출력

MySQL에서 원하는 게시물 앞뒤 5개를 출력하고 싶어서 MySQl 쿼리문을 날려봤다.

결과가 어떻게 나오는지 달라진다.

이전 : SELECT * FROM 테이블 WHERE 필드 < 번호 ORDER BY 필드 DESC LIMIT 5;
해당 : SELECT * FROM 테이블 WHERE 필드 = 번호;
이후 : SELECT * FROM 테이블 WHERE 필드 > 번호 ORDER BY 필드 LIMIT 5;

이걸 연결해서 출력하고 싶어서 오라클 전문가에게 도움을 청해서 도움을 받아서 실행을 해봤다.

출력하고자 하는 uid 값이 제대로 출력이 되었다.

select uid from (
 select * from TABLE_NAME where uid = 10
 union
 select * from (select * from TABLE_NAME where uid > 10 order by uid asc limit 5 ) a
 union
 select * from (select * from TABLE_NAME where uid < 10 order by uid desc limit 5 ) b
 ) c order by uid;

select uid 대신에 select name, email 등과 같은 칼럼을 직접 지정해주면 원하는 것만 결과를 뽑아낼 수가 있다.

만약 PHP 게시판에서 찾는 검색조건에 이걸 넣고 싶다면 어떻게 해야 할까?

보통 검색 조건은 $sqlque .= " and ".$where." like '%".trim($keyword)."%'";

로 되어 있다.

이 경우에는 IN 조건으로 찾으면 된다.

$sqlque .= " and uid IN (select uid from ( select * from ".TABLE_NAME." where uid = '".trim($keyword)."'
 union
 select * from (select * from ".TABLE_NAME." where uid > '".trim($keyword)."' order by uid asc limit 5 ) a
 union
 select * from (select * from ".TABLE_NAME." where uid < '".trim($keyword)."' order by uid desc limit 5 ) b
 ) c order by uid " ;

SYSDBA 접속 및 User 생성, 권한부여

ORACLE DB에 접속하려면, 먼저 USER를 생성하고 DBA의 권한을 부여해야 한다.
오라클을 설치 완료했다면, RUN SQL Command Lind 프롬프트를 실행해 보자.

CMD 도스창 모드에서

(1) sysdba 접속
sysdba 계정으로 먼저 로그인한다. 비밀번호는 오라클 설치시에 설정한 비밀번호이다.

sqlplus sys/oracle as sysdba

(2) User 생성
이제 새로운 User를 생성해 보자.
일반적으로 학습용 DBA의 아이디 비밀번호는 SCOTT / TIGER 를 설정한다.
연습할 User 와 비밀번호는 user01 / user01 로 설정한다.

SQL> create user user01 identified by user01;   -- 사용자 할당 및 비밀번호 생성

SQL> grant connect, resource, dba to user01;    -- 접속권한 부여

(3) user01 접속

이제 user01 로 접속해보자.

sqlplus user01/user01

(4) 테이블 업로드

실습할 SQL 파일을 읽어서 DB에 저장하기 위해서

C:\temp 폴더에 script_v2.0.sql 파일을 복사한다.

SQL> @c:\temp\script_v2.0.sql;   -- 파일에서 불러오기

(5) 사용자 비밀번호 변경

SQL> alter user user01 identified by tiger;    -- user01 사용자의 비밀번호를 tiger 로 변경

[오라클] SQL 실습을 위한 오라클 11g 설치

오라클에서 회원가입만 하면 누구든지 오라클 SQL를 다운로드 받아 설치해서 연습해볼 수 있게 제공하고 있으니 부담없이 다운로드 받아 설치해보자.

설치를 위해서는 총 3개의 파일이 필요하다.

1. JDK 설치

2. 오라클 DB 설치

3. 오라클 SQL Developer

[JDK 설치]

http://java.oracle.com 에 접속한다.

자신의 운영체제에 맞는 걸 선택해서 다운로드 받은 다음에 설치하면 된다.

설치시 특별한 설명이 필요한 것이 없으니 설치과정은 생략한다.

[Oracle DBMS 설치]

데이터베이스(DBMS) 중에서 가장 안정성이 높아서 기업에서 많이 사용하는 오라클 데이터 베이스 설치를 하려면 http://www.oracle.com/kr/index.html 에 접속하여 회원가입을 한다.

로그인할 때 비밀번호는 대문자를 1글자 넣어야 된다. 회원 아이디 생성할 때 비밀번호에 대문자, 숫자가 들어간다는 걸 기억하고 있어야....

보통 대문자 설정을 잘 안하는데 오라클, 아이폰 사이트 로그인할 때는 비밀번호에 대문자가 들어간다.

다운로드를 누르고 oracle 11g를 선택하면 http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 로 접속된다.

약간 아래로 내리면 오라클 데이터베이스 11g release 2 가 보인다.

본인 PC에 맞는 운영체제에 따라 파일 2개(File1, File2) 를 내려받는다.

다운로드 받은 2개의 파일을 압축을 풀면 아래의 형태로 보일 것이다.

setup 을 눌러서 설치한다.

전자메일 입력란에 보안관련 알림을 받기 위한 사용자 email을 입력한다.
‘My Oracle Support를 통해 보안 갱신 수신’ 체크박스는 해제하고 [다음] 버튼을 선택한다

프록시 서버 설정창이 나오면 ‘구성 상의 중요 보안문제에 대한 알림을 수신하지 않습니다’ 체크박스를 선택하고 [계속] 버튼을 선택한다.

설치 옵션은 ‘데이터베이스 생성 및 구성’을 선택하고 ‘다음’ 버튼을 선택한다

‘데스크톱 클래스’를 선택하고 [다음] 버튼을 선택 한다.

전역 데이타베이스 이름은 orcl 로 설정한다. 다른 이름으로 변경하고 싶다면 변경해도 된다.

그리고 설치할 드라이브를 C로 변경하고 싶다면 Oracle Base 에서 D를 C로 변경만 해주면 자동으로 변경된다.

중간에 오류가 나는 경우가 있다. 이 경우에는 모두 무시를 선택하고 설치했더니 이상없이 설치가 잘 되었다.

설치 확인을 위해서 PC와 노트북에서 모두 설치를 해봤다. 노트북에서 에러가 나서 모두 무시하고 설치를 했다.

설치하는 과정에 시간이 좀 걸린다.

설치가 다 되고 나면 비밀번호 관리를 눌러서 비밀번호 설정을 해준다.

연습할 대상 DB 테이블 접속을 위해서 사용자 비밀번호 지정을 해줘야 한다.

scott 계정에 잠금설정을 해제하고 새로운 비밀번호를 scott 로 입력해도 좋고 tiger 로 입력해도 좋다.

본인이 원하는 비밀번호를 설정하면 된다.

설치가 다 되면 닫기를 눌러준다.

설정이 다 끝났으면 접속은 다음 글에서 접속하면 된다.

[ 오라클 Developer 다운로드]

오라클 Developer 를 다운로드 받으면 SQL 명령문 연습을 편하게 할 수 있다.

다운로드만 하고 실행파일만 실행하면 별도의 설치없이 이용이 가능하다.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

본인 PC 운영체제에 맞는 버전을 받으면 된다.

혹시라도 접속에 문제가 된다면 이전 버전을 받는다.

다운로드 받아서 원하는 폴더에 압축을 풀고나서 실행파일만 실행해주면 된다.

접속을 하기 위해서는 아래 그림과 같은 순서로 관리자 접속을 위한 설정을 해준다.

비밀번호는 DBMS 설치하면서 설정한 비밀번호 (ex, oracle) 를 입력한다.

그리고 정상적으로 접속되는지 확인한 다음에 저장한다.

이제 scott 계정 접속을 설정한다.

user01 계정 생성하는 방법이다.

여기서 직접 등록해도 되고 다음 게시물에서처럼 등록해도 된다.

이제 접속 설정을 위한 준비는 다 끝났다.

이제 열심히 SQL 명령문을 연습해보고 이해하자.

문법 : 

SELECT   select_list

FROM   table 또는 view

WHERE  조건   연산자  ( SELECT  select_list

      FROM  table

     WHERE  조건  ) ;

sub query 는 메인 쿼리 안에 포함되어  있는 또다른 별도 Select 문이다.

여러번의 Select문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 Select 문으로 쉽게 얻을 수 있다.

sub query 가 먼저 실행되고, 그 결과가 메인 쿼리에 전달되어 실행된다.

두개의 테이블을 sub query 로 조인할 때에는 반드시 일치되는 공통 칼럼을 사용해야 한다.

- sub query 는 Select문의 select 절, FROM 절, Where절, Having 절에 올 수 있다.

  . select 절에 오는 sub query 는 스칼라 서브쿼리,

  . FROM 절에 오는 서브쿼리는 inline view 서브쿼리

- sub query 는 SELECT 문만 아니라 다른 문(INSERT, DELETE, UPDATE) 에도 쓸 수 있다

- sub query 에는 반드시 괄호를 사용한다.

- operator(연산자) 는 단일행 연산자 ( > , = , >= , < , <= , != )와

                              복수행 연산자 ( IN , ANY ,ALL,EXISTS )를 사용 할 수 있다.

- 단일행 sub query : sub query 의 결과가 1개의 행만 나오며, 반드시 단일행 연산자를 사용해야 한다. 

- 복수행 sub query : sub query 가 여러 개의 행을 리턴하며, 반드시 복수행 연산자를 사용해야 한다.
- sub query 에는 order by 를 사용할 수 없다.
- sub query 에는 NULL 값이 있으면 결과가 출력되지 않는다.

예제

단일행 sub query

-- 1. EMP 테이블에서 'SCOTT' 보다 급여를 많이 받는 사람의 이름과 급여를 출력

SQL> select ename, sal
from emp
where sal > (select sal from emp where ename = 'SCOTT');

-- 2. 사원테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여 출력
SQL> select empno, ename, sal
from emp
where sal > (select sal from emp where ename ='BLAKE');

-- 3. 사원테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색
SQL> select empno, ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'MILLER');
select hiredate from emp where ename = 'MILLER';

-- 4. emp 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 사원번호, 이름, 담당업무, 입사일자, 급여를 출력
SQL> select empno, ename, job, hiredate, sal
from emp
where job = (select job from emp where empno=7521)
and sal > (select sal from emp where empno = 7934);

-- 5. 서브쿼리에서 그룹함수 사용
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);

-- 6. 사원테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색
SQL> select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);

-- 7. emp 테이블에서 20번 부서의 최소급여보다 많은 모든 부서의 최소급여 출력

--  having 절에서의 서브쿼리

SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);


복수행 sub query
- 복수행 서브쿼리 : IN, ALL, ANY
- 복수행 서브쿼리란 sub query 의 결과가 2건 이상 출력되는 것을 말한다.

- 메인 쿼리의 비교 조건이 sub query 의 결과 중에서 하나라도 일치하면 검색 가능

-- 1. emp 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호 출력
SQL> select empno, ename, job, hiredate, sal, deptno
from emp
where sal IN (select min(sal) from emp group by job)

order by 3;

-- 2. 사원테이블에서 업무가 MANAGER인 사원의 최소급여보다 적은 급여를 받는 사원들의 이름 검색
SQL> select empno, ename, job, sal, deptno
from emp
where sal < ALL (select sal from emp where job = 'MANAGER');

-- ALL 을 사용한 것과 min(sal)을 사용한 것이 결과는 동일하다.
SQL> select empno, ename, job, sal, deptno
from emp
where sal < (select min(sal) from emp where job = 'MANAGER');

-- 3. ANY : 최소값보다 큰, ALL : 최소값보다 작은
SQL> select empno, ename, job, sal, deptno
from emp
where sal > ANY (select sal from emp where job = 'MANAGER');

SQL> select empno, ename, job, sal, deptno
from emp
where sal > (select min(sal) from emp where job = 'MANAGER');

-- 4. EXISTS 연산자 : sub query 에서 검색된 결과가 하나라도 존재하면 메인 쿼리 조건이 참이 되는 연산

-- 사원중에서 comm 을 받은 사원이 한 명이라도 있으면 모든 사원 출력
SQL> select *
from emp 
where exists (select empno from emp where comm IS NOT NULL);

다중컬럼 서브쿼리 : pairwise (컬럼을 쌍으로 묶어서 동시에 비교하는 방식)

-- sub query 에서 여러 개의 컬럼 값을 검색하여 메인 쿼리의 조건절과 비교하는 sub query

--5-1. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력

-- 부서별 : group by 를 사용해야 한다는 의미, 최대 급여 : max(sal)
SQL> select deptno, empno, ename, sal
from emp
where (deptno, sal) IN (select deptno, max(sal) from emp group by deptno);

다중컬럼 서브쿼리 : unpairwise (칼럼별로 나누어 비교하고 나중에 AND 연산 처리하는 방식)

--5-2. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력
SQL> select deptno, empno, ename, sal
from emp
where deptno in (select deptno from emp group by deptno)
and sal in (select max(sal) from emp group by deptno);

-- 6. 사원테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색

-- 오류가 생길 수 있는 쿼리문
SQL> select empno, ename, deptno, sal
from emp
where sal in (select max(sal) from emp group by deptno);

-- 정확한 쿼리문
select empno, ename, deptno, sal
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

SQL 그룹함수, having절

단일 행 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수이다.

그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY절을 이용하여 그룹화 할 수 있고 HAVING를 이용하여 그룹에 대한 조건을 제한할 수 있다

기본구문은 아래와 같다.
SELECT column_name, group_function(column_name)
FROM table_name
WHERE column_name =  'value'  -- where 절에는 그룹함수가 올 수 없음
GROUP BY column_name;  -- group by 절 뒤에는 컬럼 별칭을 사용할 수 없음

- GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.

- 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.

- 그룹함수를 사용하여 특정 그룹으로 구분할 때는 GROUP BY 절을 사용하며, 특정 그룹없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용한다.

- Count(*) 함수는 서로 다른 모든 행에 대하여 연산하지 않고 각 그룹화된 행에서 계산된다.

그룹함수의 종류

Count : NULL 이 아닌 행의 개수

SUM : NULL 값을 제외한 입력 데이터들의 합계

AVG : NULL 값을 제외한 입력 데이터들의 평균

MAX : 입력 데이터들의 최대 값

MIN : 입력 데이터들의 최소 값

STDDEV : NULL 값을 제외한 입력 데이터들의 표준편차

VARIANCE : NULL 값을 제외한입력 데이터들의 분산 값

예제

-- 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력

SQL> SELECT deptno, count(*), round(avg(sal),0), min(sal), max(sal), sum(sal)
FROM emp
GROUP BY deptno
ORDER BY sum(sal) desc;

-- 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회
SELECT job, COUNT(empno) "인원수", round(AVG(sal)) "평균급여액", MAX(sal) "최고급여액",
       MIN(sal) "최저급여액", SUM(sal) "급여합계"
  FROM emp
  GROUP BY job;

SELECT deptno, ename, AVG(sal)
FROM emp
GROUP BY deptno;
위 구문은 정상적인 결과를 조회할까?
※ 그룹함수 사용시 GROUP BY 절로 묶이지 않은 단순 컬럼은 SELECT 리스트에 사용 불가능하고 사용시 에러가 발생한다. 정상적으로 처리되려면 SELECT 문에서 ename 을 제거하던가, 아니면 GROUP BY 문에 ename을 추가하던지 해야 한다.

SELECT deptno, ename, AVG(sal)
FROM emp
GROUP BY deptno, ename;


HAVING 절
SELECT COUNT(column) as cnt FROM table GROUP BY column HAVING COUNT(column) > 5;
  - WHERE 절에서는 집계함수를 사용 할 수 없다.
  - HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
  - HAVING절은 GROUP BY절과 함께 사용된다.

-- 사원수가 다섯 명이 넘는 부서와 사원수를 조회
SELECT d.dname, COUNT(e.empno) "사원수"
 FROM emp e, dept d
 WHERE e.deptno = d.deptno
 GROUP BY d.dname
 HAVING COUNT(e.empno) > 5;

ANSI 조인으로 표기하면....

SELECT d.dname, COUNT(e.empno) "사원수"
 FROM emp e INNER JOIN dept d
 ON e.deptno = d.deptno
 GROUP BY d.dname
 HAVING COUNT(e.empno) > 5;

-- 전체 월급이 4000을 초과하는 job에 대해서 job과 월급여 합계를 조회.
-- 단 판매원(SALESMAN)은 제외하고 월 급여 합계로 내림차순 정렬
SELECT job, SUM(sal) "급여 합계"
 FROM emp
 WHERE job != 'SALESMAN'          -- 판매원은 제외
 GROUP BY job                    -- 업무별로 Group By
 HAVING SUM(sal) > 4000     -- 전체 월급이 4000을 초과하는
 ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬

분석 :
먼저 직업별로 조회해본다. select job from emp group by job;
그러면 직업 전체 현황이 조회된다. 판매원을 뜻하는 job 이 뭔지 정확하게 파악할 수 있다.
비교조건이 월급이다.
SUM(sal) > 4000 을 초과하는 job 을 조회하라는 것인데
where 조건에는 그룹함수 SUM(sal) 을 사용하여 비교할 수가 없으므로 Having SUM(sal) 로 비교해야 한다.

**별 평균/합계가 2000 이상 과 같은 조건이 나오면 Group by Having 절을 사용한다고 기억하면 된다.