[풀스택과정] 데이터베이스 4. WHERE

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. WHERE 절을 이용한 조건 검색

WHRER 절을 이용한 조건 검색

• WHERE절

– 테이블에 저장된 데이터중에서 원하는 데이터맊 선택적으로 검색하는 기능
– WHERE 절의 조건문은 칼럼 이름, 연산자, 상수, 산술 표현식을 결합하여 다양한 형태로 표현 가능
– WHERE 절에서 사용하는 데이터 타입은 문자, 숫자, 날짜 타입 사용 가능
– 문자와 날짜 타입의 상수 값은 작은 따옴표(' ')로 묶어서 표현하고 숫자는 그대로 사용
– 상수 값에서 영문자는 대소문자를 구별

• 사용법

SELECT [DISTINCT]{* | column[alias]…} FROM table [WHERE condition];

• 사용 예

– 학생 테이블에서 1학년 학생만 검색하여 학번, 이름, 학과 번호를 출력하여라

비교 연산자를 사용한 조건 검색

• 비교 연산자

– WHERE 절에서 숫자, 문자, 날짜의 크기나 순서를 비교하는 연산자

[표 5.1] 비교 연산자

• 사용 예

– 학생 테이블에서 몸무게가 70kg 이하인 학생만 검색하여 학번, 이름, 학년, 학과번호, 몸무게를 출력하여라.

논리 연산자를 이용한 조건 검색

• 논리 연산자

– WHERE 절에서 여러 개의 조건을 결합할 경우
– AND, OR, NOT과 같은 논리 연산자를 사용

[표 5.2] 논리 연산자

AND 논리 연산자를 이용한 조건 검색 예

• 사용 예

– 학생 테이블에서 1학년 이면서 몸무게가 70kg 이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호를 출력하여라

OR 논리 연산자를 이용한 조건 검색 예

• 사용 예

– 학생 테이블에서 1학년이거나 몸무게가 70kg 이상인 학생만 검색하여 이름, 학년, 몸무게, 학과번호를 출력하여라.

NOT 논리 연산자를 이용한 조건 검색 예

• 사용 예

– 학생 테이블에서 학과번호가 „101‟이 아닌 학생의 학번과 이름과 학과번호를 출력해라

SQL 연산자를 이용한 조건 검색

• SQL 연산자

– SQL 연산자는 SQL 언어에만 제공
– SQL 연산자는 모든 데이터 타입에 대해 사용 가능

[표5.3] SQL 연산자

BETWEEN 연산자를 이용한 조건 검색

• BETWEEN 연산자

– 특정 칼럼의 데이터 값이 하한 값 a와 상한 값 b사이에 포함되는 행을 검색하기 위한 연산자
– AND 논리 연산자를 사용핚 것과 같은 결과 집합 출력

• 사용 예

– BETWEEN 연산자를 사용하여 몸무게가 50kg에서 70kg 사이인 학생의 학번, 이름, 몸무게를 출력하여라.

• 사용 예

– 학생테이블에서 81년에서 83년도에 태어난 학생의 이름과 생년월일을 출력해라

IN 연산자를 이용한 조건 검색

• IN(a, b, c, · · ·) 연산자

– IN(a, b, c, ···) 연산자는 특정 칼럼의 데이터 값이 a, b, c, · ··값 중에 하나라도 일치하면 참이 되는 연산자
– OR 논리 연산자를 사용한 것과 같은 결과 집합 출력

• 사용 예

– IN 연산자를 사용하여 102번 학과와 201번 학과 학생의 이름, 학년, 학과번호를 출력하여라

LIKE 연산자를 이용한 조건 검색

• LIKE 연산자

– 칼럼에 저장된 문자열중에서 LIKE 연산자에서 지정한 문자 패턴과 부분적으로 일치하면 참이 되는 연산자

• 사용 예

– 학생 테이블에서 성이 "김"씨인 학생의 이름, 학년, 학과 번호를 출력하여라.

• 사용 예

– 학생 테이블에서 이름이 3글자, 성은 "김"씨고 마지막 글자가 "영"으로 끝나는 학생의 이름, 학년, 학과 번호를 출력하여라

ESCAPE 옵션

• ESCAPE 옵션

– ESCAPE 옵션은 LIKE 연산자에서 사용하는 와일드 문자(%, _)자체를 포함하는 문자열을 검색할 때, 와일드 문자를 일반 문자처럼 취급하기 위한 옵션

NULL

• NULL 개념

– NULL은 미확인 값이나 아직 적용되지 않은 값을 의미
– 0도 아니며 스페이스도 아닌 정의되지 않은 값
– NULL 값과 다른값과의 연산 결과는 NULL
– NULL은 단독으로 인덱스를 사용할 수 없으므로 가능한 정의가능한 값(특정값)으로 정한다면 ( "0", "99" 등 ) 인덱스를 이용할 수 있다. 
– 그렇다고 생각없이 NULL을 특정 값으로 변환하는 것은 향후 큰 문제를 일으킬 수 있으므로 주의 깊게 사용해야 함.
– 예
• 학생 테이블에서 학생의 몸무게가 NULL인 경우 그 학생의 몸무게가 0이라는 의미가 아니라 현재 시점에서 그 학생의 몸무게를 모른다는 의미
• 학생 테이블에서 학생의 지도교수 코드가 NULL인 경우에는 그 학생은 아직 지도교수가 배정되지 않았음을 의미

• 정의

– 컬럼에 아무런 값이 없을 경우, 컬럼은 null 상태라고 이야기를 함 또는 null을 포함했다고 정의함
– null 은 어떠한 not null 또는 Primary key 제약조건을 포함하지 않는 경우 모든 컬럼의 모든 데이터 타입에서도 나타날 수 있음
– null은 값을 알 수 없거나 아무런 의미가 없을 경우 사용됨
– null 값을 0(zero)을 나타내는데 사용하지는 말것(null과 0은 동일핚 값이 아님)

– 참조
• 오라클 데이터베이스는 현재 길이가 "0"인 값에 대하여는 문자형 값(character value)은 null로 취급함
• 그러나 그러나 이러한 부분은 향후 버전에서도 동일하게 인식되지 않을 수 있음
• 따라서 공백 문자열과 null 값을 동일하게 처리하지는 말아야 함

• SQL 함수 상에서의 null

– 모든 스칼라 함수들은(REPLACE, NVL, CONCAT는 제외) null 변수를 입력 받았을 경우 null 값 리턴이 가능함
– 대부분의 집계(Aggregate) 함수는 null 무시하고 수행됨
– 1000, NULL, NULL, NULL, 2000 에 대한 AVG는 1500으로 리턴됨

 

NULL의 적용기준

NULL의 이해

NULL

• null의 비교

– null 값을 테스트 하기 위한 비교 방법은 IS NULL 또는 is NOT NULL 임
– 만약 다른 조건으로 NULL을 함께 사용하였을 경우 결과는 UNKNOWN으로 리턴됨
– WHY? Null은 데이터의 부족함을 나타냄, 즉 null은 같음("=") 또는같지 않음("≠")을 다름 값과 비교하거나 다른 null과 비교할 수 없다.
– 그러나 오라클에서는 두 개의 서로 다른 null에 대하여 같다고 판단할 수 있는 방안은 제시하고 있음
– DECODE 함수를 이용하여 판단하였을 경우
– 복합 키 내에서 null이 나타날 경우(that is oracle considers identical two compound keys containing nulls if all the nonnull components of the keys are equal)

• null의 비교

– null 값을 테스트 하기 위한 비교 방법은 IS NULL 또는 is NOT NULL 임
– 만약 다른 조건으로 NULL을 함께 사용하였을 경우 결과는 UNKNOWN으로 리턴됨
– WHY? Null은 데이터의 부족함을 나타냄, 즉 null은 같음("=") 또는 같지 않음("≠")을 다름 값과 비교하거나 다른 null과 비교할 수 없다.

• 조건상의 null

– UNKNOWN 형태의 평가는 대부분 FALSE와 동일함
– WHERE절의 평가가 UNKNOWN으로 리턴 될 경우 SELECT 절에는 아무런 ROW가 출력되지 않음
– UNKNOWN VS FALSE
• FALSE : 비교값과 같이 않음을 표시(TRUE는 비교값과 같지 않음을 표시)
• 연산 조건 자체가 UNKNOWN일 경우 결과집합도 UNKNOWN

• 사용 예

– 교수 테이블에서 이름, 직급, 보직수당을 출력하여라

NULL 연산자를 이용한 조건 검색

• IS NULL

– IS NULL 연산자는 칼럼 값 중에서 NULL을 포함하는 행을 검색하기 위해 사용하는 연산자

• IS NOT NULL 연산자

– IS NOT NULL 연산자는 NULL이 아닌 행을 검색 경우에 사용하는 연산자

• 사용 예

– 교수 테이블에서 보직수당이 없는 교수의 이름, 직급, 보직수당을 출력하여라

산술식에서의 NULL 처리

• 산술식과 함수에서 NULL 처리

– NULL에 산술식이나 함수를 적용하면 연산 결과도 NULL

• 사용 예

– 교수 테이블에서 급여에 보직수당을 더한 값은 sal_com이라는 별명으로 출력하여라.

연산자 우선순위

[표5.4] 연산자 우선 순위 규칙

• 사용 예

– 102번 학과의 학생 중에서 1학년 또는 4학년 학생의 이름, 학년, 학과 번호를 출력하여라.

• 사용 예

– 102번 학과의 학생 중에서 4학년 학생이거나 소속학과에 상관없이 1학년 학생의 이름, 학년, 학과 번호를 출력하여라.

 

2. 집합 연산자

집합 연산자

• 집합 연산자

– 테이블을 구성하는 행집합에 대해 테이블의 부분 집합을 결과로 반환하는 연산자
– 합병 가능 : 집합 연산의 대상이 되는 두 테이블의 칼럼수가 같고, 대응되는 칼럼끼리 데이터 타입이 동일

• 사용법

SELECT 명령문1 [UNION | UNION ALL | INTERSECT | MINUS] SELECT 명령문2;

[표5.5] 집합 연산자 종류

 

UNION, UNION ALL 연산

• UNION, UNION ALL 연산

– UNION 연산은 수학의 집합 연산에서 합집합을 의미
– UNION 연산은 두 개의 테이블에 속하면서 중복되는 행을 제외한 행 집합을 결과 집합으로 반환
– UNION ALL 연산은 중복되는 행을 제외하지 않고 모두 출력

집합 연산을 위한 테이블 생성

• 사용 예

– 1학년 이면서 몸무게가 70kg 이상인 학생의 집합(stud_heavy)과 1학년 이면서 101번 학과에 소속된 학생(stud_101)으로 구성된 두 개의 테이블 생성

• UNION 연산을 실행하는 두 테이블이 합병 불가능한 경우, 

– 두 질의의 칼럼수가 다르므로 오류 발생

UNION, UNION ALL 연산의 비교

• 사용 예

– stud_heavy와 stud_101테이블에 대해 UNION, UNION ALL 연산을 각각 수행한 결과를 출력하여라

INTERSECT 연산

• INTERSECT 연산

– 수학의 집합 연산에서 교집합을 의미
– INTERSECT 연산은 두 개의 테이블에 모두 속하는 행 집합을 결과 집합으로 반환

• 사용예

 

MINUS 연산

• MINUS 연산

– 수학의 집합 연산에서 차집합을 의미
– MINUS 연산은 데이블 A, B에서 A테이블에 속하지만 B 테이블에는 속하지 않는 행 집합을 결과 집합으로 반환

 

 

 

3. 정렬 방법

정렬

• 정렬(sorting)

– SQL 명령문에서 검색된 결과는 테이블에 데이터가 입력된 순서대로 출력
– 하지만, 데이터의 출력 순서를 특정 컬럼을 기준으로 오름차순 또는 내림차순으로 정렬하는 경우가 자주 발생
– 여러 개의 칼럼에 대해 정렬 순서를 정하는 경우도 발생
– 예
• 학생 테이블에서 학번순이나 교수 테이블에서 급여순으로 정렬하는 경우

 

ORDER BY 절

• 기본적인 정렬 방법

– 문자 값은 알파벳순으로 출력되고, 한글은 가나다순으로 출력된다
– 숫자 값은 가장 작은 값으로 먼저 출력된다
– 날짜 값은 과거의 날짜순으로 출력된다

• 사용법

– ORDER BY : 칼럼이나 표현식을 기준으로 출력 결과를 정렬할 때
사용
– ASC : 오른차순으로 정렬, 기본 값
– DESC : 내림차순으로 정렬하는 경우에 사용, 생략 불가능

 

단일 컬럼을 이용한 정렬 - 오름차순

• 사용예

– 학생 테이블에서 이름을 가나다순으로 정렬하여 이름, 학년, 전화번호를 출력하여라

단일 컬럼을 이용한 정렬 - 내림차순

• 사용예

– 학생 테이블에서 학년을 내림차순으로 정렬하여 이름, 학년, 전화번호를 출력하여라

order by 절 사용 시 정렬 기준의 원칙

• 디폴트는 오름차순 정렬이 원칙
• NUMBER TYPE은 가장 적은 값 부터 출력 됨 ( 예: 1 - 99 )
• 날짜 타입은 가장 빠른 값부터 출력됨 (예 : 80-12-17, 81-02-20, 82-01-23, ..)
• 문자 값은 알파벳 순서로 출력됨.(예 : 특수문자 : A - Z, a - z)
• Null값은 오름차순에서는 제일 나중에 그리고 내림차순에서는 제일 먼저 출력됨
• SELECT 절에 포함되지 않는 컬럼도 정렬을 하기 위한 ORDER BY 절에 올수 있음.

 

다중 열에 의한 정렬

 Order by list에 명시한 순서가 정렬되는 순서임
 Select list에 없는 열로도 정렬은 가능
 모든 사원의 이름과 급여 및 부서번호를 출력하는데, 부서 번호로 결과를 정렬한 다음 급여에 대해서는 내림차순으로 정렬하라.

실습

 부서 10과 30에 속하는 모든 사원의 이름과 부서번호를 이름의 알파벳 순으로 정렬되도록 질의문을 형성하라.

 1982년에 입사한 모든 사원의 이름과 입사일을 구하는 질의문은?

 보너스를 받는 모든 사원에 대해서 이름, 급여 그리고 보너스를 출력하는 질의문을 형성하라. 단 급여와 보너스에 대해서 내림차순 정렬

 보너스가 급여의 20% 이상이고 부서번호가 30인 많은 모든 사원에 대해서 이름, 급여 그리고 보너스를 출력하는 질의문을 형성하라.