SQL 2. 데이터 제한 및 정렬

본문

2-1. 선택되는 행 제한

․ WHERE절을 사용하여 반환되는 행을 제한한다.

․ WHERE절은 FROM절 다음에 온다.

① 선택되는 행 제한

WHERE절을 사용하여 질의에서 반환되는 행을 제한할 수 있다.

WHERE절은 만족해야 할 조건을 포함하여 FROM절 바로 다음에 온다.

조건이 참일 경우, 조건을 만족하는 행이 반환된다.

구문 설명 :

WHERE 조건을 만족하는 행만 질의하도록 제한한다.

condition열 이름, 표현식, 상수 및 비고 연산자로 구성된다.

WHERE절은 열 값, 리터럴 값, 산술식 또는 함수를 비교할 수 있으며 다음 세 가지 요소로 구성된다.

․ 열 이름

․ 비교 조건

․ 열 이름, 상수 또는 값 목록

예제) WHERE절 사용

오라클 디비 예제 :

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90;

☞설명)

예제에서 SELECT문은 업무ID가 SA_REP인 모든 사원의 이름, 업무ID및 부서 번호를 검색한다.

SA_REP라는 업무는 EMPLOYEES테이블의 업무 ID열과 일치하도록 대문자로 지정되었다.

오라클에서는 문자열의 대소문자를 구분합니다.

MSSQL 디비 예제 :

SELECT title_id , ord_date, stor_id FROM sales where stor_id='6380'

2-2. 문자열 및 날짜

․ 문자열 및 날짜 값은 작은 따옴표로 묶습니다.

․ 오라클에서는 문자 값은 대소문자를 구분하며 날짜 값은 날짜형식을 구분한다.

․ 오라클의 기본 날짜 형식은 DD-MON-RR이다.

예제)

오라클 디비 예제 : SELECT last_name, job_id, department_id

FROM employees

WHERE last_name = ‘Whalen';

☞설명)

WHERE절의 문자열 및 날짜는 작음 따옴표(‘ ’)로 묶지만 숫자 상수는 작은 따옴표로 묶지 않는다.

오라클에서는 모든 문자 검색은 대소문자를 구분한다.

EMPLOYEES테이블에는 모든 이름이 대소문자를 혼합하여 저장되어 있으므로 다음 예제의 경우 행이 반환되지 않는다.

오라클 디비 예제 : SELECT last_name, job_id, department_id

FROM employees

WHERE last_name = 'WHALEN';

오라클 데이터베이스는 세기,연도,월,일,시,분,초를 나타내는 내부 숫자 형식으로 날짜를 저장하는데 기본날짜표시 형식은

DD-MON-RR이다.

MSSQL 디비 예제 :

select au_id, au_fname, au_lname from authors where city='Oakland';

select au_id, au_fname, au_lname from authors where city='oakland';

MSSQL에서는 대소문자를 기본적으로 구별하지 않는다.

구별하도록하려면 설치시에 대소문자를 구별하도록 설치 할 수 있다.

mysql나 오라클에선 대소문자를 구별을 하므로 'Oakland' 와 'oakland'는 다른 데이터이다.

숫자를 제외한 나머지는 리터럴값에 ''를 해줘야한다.

2-3. 비교 조건

연산자 의미

=같음

> 보다 큼

>= 크거나 같음

< 보다 작음

<= 작거나 같음

<> 같지 않음

비교 조건은 표현식을 다른 값이나 표현식과 비교하는 조건부에 사용되며 WHERE절에서 다음 형식으로 사용된다.

구문

... WHERE expr operator value

예제

... WHERE hire_date='01-JAN=95'

... WHERE salary>=6000

... WHERE last_name='Smith'

WHERE절에는 별칭을 사용할 수 없습니다.

참고 : 오라클에선 != 및 ^= 기호 역시 같지 않음 조건을 나타낸다.

MSSQL에선 != 및 <> 기호 역시 같지 않음 조건을 나타낸다.

예제) 비교 조건 사용

오라클 디비 예제 : SELECT last_name, salary FROM employees WHERE salary <= 3000;

☞설명)

예제에서 SELECT문은 EMPLOYEES테이블에서 급여가 3000이하인 사원의 이름과 급여를 검색한다.

WHERE절에 명시적(explicit)값이 제공되며, 명시적 값인 3000과 EMPLOYEES테이블의 SALARY열에 있는 급여 값을 비교

한다.

MSSQL 디비 예제 :

select title, price from titles where price = 20

select title, price from titles where price >= 20

select title, price from titles where price <= 20

select title, price from titles where price <> 20

또는

select title, price from titles where price != 20

-- 오라클은 '^='도 같지 않다라는 뜻

select title_id , title , price from tiles where title_id = 'bu1032'

-- 대소문자 구별 하지 않는다. --오라클은 한다.

select title_id as 제목 , title , price from titles where 제목 = 'bu1032'

-- 오류 where절엔 별칭을 사용할 수 없다.

2-4. 다른 비교 조건

연산자 의미

BETWEEN

... AND ...

두 값 사이(지정한 값 포함)

IN(set) 값 목록 중의 갑과 일치

LIKE 문자 패턴 일치

IS NULL 널 값

예제) BETWEEN 조건 사용

BETWEEN조건을 사용하여 갑의 범위에 따라 행을 표시합니다.

오라클 디비 예제 :

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;

☞설명)

BETWEEN범위 조건을 사용하면 값의 범위에 따라 행을 표시할 수 있으며 지정 범위에는 하한값과 상한값이 포함된다.

예제의 SELECT문은 EMPLOYEES에 테이블에서 급여가 $2,500이상이고 $3,500이하인 사원의 행을 반환한다.

BETWEEN조건으로 지정한 값도 범위에 포함되며 하한값을 먼저 지정해야 한다.

MSSQL 디비 예제 :

select title_id , title, price from titles where price between 10 and 30

또는

select title_id , title, price from titles where price >= 11 and price <= 20

-- where 절에 or 사용

select title_id , title, price from titles where price < 11 or price > 20

select title_id , title, price from titles where price <= 11 or price >= 20

---잘못된 표기

select title_id, title, type from titles where price >= 10 and <=30

select title_id , title, price from titles where price =< 11 or price => 20

예제) IN 조건 사용 : IN멤버 조건을 사용하면 값이 목록에 있는지 확인할 수 있습니다.

오라클 디비 예제 :

SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

☞설명)

값이 특정 값 집합에 있는지 확인하려면 IN조건을 사용한다. IN조건은 멤버조건이라고도 한다.

예제는 관리자의 사원번호가 100, 101또는 201인 모든 사원의 사원번호, 이름 급여 및 관리자의 사원번호를 표시한다.

IN조건은 모든 데이터 유형에 사용할 수 있다.

예제)

오라클 디비 예제 :

SELECT employee_id, manager_id, department_id FROM employees WHERE last_name IN ( 'Hartstein' , 'Vargas' );

☞설명)

예제는 EMPLOYEES테이블에 WHERE절의 이름 목록에 포함된 이름을 가진 사원의 행을 반환한다.

목록에서 사용되는 문자 또는 날짜는 작은 따옴표(‘ ’)로 묶어야 한다.

MSSQL 디비 예제 :

select title_id , title, price from titles where price = 10.95 or price = 20

또는

select title_id , title, price from titles where price in ( 10.95 , 20 )

select title_id , title , price from titles where title_id = 'bu1032' or title_id='mc3021'

또는

select title_id , title , price from titles where title_id in ('bu1032' , 'mc3021');

2-5. LIKE 조건 사용

․ LIKE조건을 사용하면 유효한 검색 문자열 값인 대체 문자를 사용하여 검색할 수 있다.

․ 검색 조건은 리터럴 문자 또는 숫자를 포함할 수 있다.

기호 설명

%에는 문자가 오지 않거나 여러 개 올 수 있습니다.

0개 이상의 일련의 문자를 나타냅니다.

_ 문자 하나를 나타냅니다.

검색할 값을 정확하게 알지 못하는 경우 LIKE조건을 사용하여 문자 패턴이 일치하는 행을 선택할 수 있다.

문자 패턴 일치 연산을 대체 문자검색이라고도 하며 검색 문자열은 두 가지 기호를 사용하여 구성할 수 있다.

예제)

오라클 디비 예제 : SELECT first_name FROM employees WHERE first_name LIKE 's%';

☞설명)

SELECT문은 EMPLOYEES테이블에서 이름이 S로 시작하는 모든사원의 이름을 반환한다.

이 경우 대문자 S를 사용했으므로 소문자 s로 시작하는 이름은 반환되지 않는다.

LIKE조건은 일부 BETWEEN비교 연산에 대한 단축 방법으로 사용될 수 있다.

예제)

오라클 디비 예제 :

SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%95';

☞설명)

예제는 1995년 1월 1995년 12월 사이에 입사한 모든 사원의 이름과 입사일을 표시한다.

예제) LIKE 조건 사용

오라클 디비 예제 :

SELECT last_name FROM employees WHERE last_name LIKE '_o%';

☞설명)

%및 _ 기호는 리터럴 문자를 결합할 때 사용된다.

예제는 이름의 두 번째 문자가 o인 모든 사원의 이름을 표시한다.

* ESCAPE옵션

검색할 문자에 실제로‘%’ 및 ‘_’문자가 포함된 경우 ESCAPE옵션을 사용하여 이스케이프 문자를 지정한다. ‘SA_'를 포함

하는 문자열을 검색하는 경우 다음SQL문을 사용하여 검색할 수 있다.

예제)

오라클 디비 예제 :

SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%Sa₩_%' ESCAPE '₩';

☞설명)

ESCAPE옵션은 백슬래시(₩)를 이스케이프 문자로 식별한다.

이 패턴에서 이스케이프문자가 밑줄(_) 앞에 있으므로 밑줄을 리터럴로 해석한다.

MSSQL 디비 예제 :

select au_lname , au_fname from authors where au_lname like 's%'

-- au_lname에서 s로 시작되는 데이터가 있는 행 출력

select au_lname , au_fname from authors where au_lname like '_e%'

-- 두번째 글짜가 e로 시자되는 데이터

select au_lname , au_fname from authors where au_lname like 's_i%'

-- s로 시작되고 한글자 다음이 i인 모든 데이터(예, smith)

select au_lname , au_fname from authors where au_lname like 's__i%'

-- s로 시작되고 한글자 다음다음글자가 i인 모든 데이터

select au_lname , au_fname from authors where au_lname like '%s__i'

-- 끝에서 네번째가 s로 시작되고 i로 끝나는 데이터

select au_lname , au_fname from authors where au_lname like '%s__i%'

-- 네글자중 s로 시작해서 다음다음 글자가i인 모든 데이터

select au_lname , au_fname from authors where au_lname like '%s%i'

select title_id , title , price from titles where title like '%computer%'

-- computer라는 글자가 있는 모든 데이터

select au_lname , au_fname from authors where au_lname like 's₩_i%' escape '₩'

-- '_'로 인식시키기 위해선 '_' 앞에 '₩'를 붙여주고 뒤에 escape '₩' 적어준다.

2-6. NULL 조건 사용

․ IS NULL연산자를 사용하여 널 여부를 테스트한다.

․ NULL조건에는 IS NULL조건과 IS NOT NULL조건이 있다.

․ IS NULL조건은 널 여부를 테스트한다.

․ 널 값은 알 수 없는 값으로서 사용, 할당 및 적용할 수 없다.

․ 널 값은 어떤 값과도 동일성 여부를 판별할 수 없으므로 =를 사용하여 테스트할 수 없다.

예제)

오라클 디비 예제 :

SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;

☞설명)

예제는 관리자가 없는 모든 사원의 이름과 관리자를 검색한다.

예제)

오라클 디비 예제 :

SELECT last_name, job_id, commission_pct FROM employees WHERE commission_pct IS NULL;

☞설명)

커미션을 받지 않는 업무에 해당하는 모든 사원의 이름, 업무ID및 커미션을 표시한다.

MSSQL 디비 예제 :

select * from titles where price = NULL; --(x)

select * from titles where price is NULL; --(o)

select * from titles where price is not NULL; --(o)

select * from titles where price <> NULL; --(x)

select * from titles where price != NULL; --(x)

2-7. 논리조건

연산자 의미

AND 구성요소 조건이 모두 TRUE면 TRUE를 반환합니다.

OR 구성요소 조건 중 하나라도 TRUE면 TRUE를 반환합니다.

NOT 뒤따르는 조건이 FALSE면 TRUE를 반환합니다.

논리조건은 두 구성 요소 조건의 결과를 결합하여 이를 기반으로 하나의 결과를 생성하거나 단일 조건의 결과를 부정시키

기도 한다.

조건의 전체 결과가 참인 경우에만 행(row)이 반환된다.

․ AND : AND는 조건이 모두 TRUE여야 한다.

․ OR : OR는 조건 중 하나가 TRUE면 된다.

․ NOT

이제까지는 모든 예제에서 WHERE절에 하나의 조건만 지정했지만, AND 및 OR연산자를 사용하여 하나의 WHERE절에 여

러 조건을 지정할 수 있다.

예제) AND 연산자 사용

오라클 디비 예제 : SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >=10000

AND job_id LIKE '%MAN%';

☞설명)

예제에서 조건이 모두 TRUE인 경우에만 레코드가 선택되므로 업무ID에 문자열 MAN이 포함되고 급여가 $10,000이상인

사원만 선택된다.

모든문자 검색은 대소문자를 구분하므로 MAN을 소문자로 표시하면 행이 반환되지 않는다.

문자열은 따옴표로 묶어야 한다.

MSSQL 디비 예제 :

select title_id , title , price from titles where price >= 20 and title like '%com%'

AND진리표 AND로 두 표현식을 결합한 결과를 표시한다.

AND TRUE FALSE NULL

TRUE TRUE FALSE NULL

FALSE FALSE FALSE FALSE

NULL NULL FALSE NULL

예제) OR 연산자 사용

오라클 디비 예제 : SELECT employee_id, last_name, job_id, salary

FROM employees

WHERE salary >=10000

OR job_id LIKE '%MAN%';

☞설명)

예제에서 조건 중 하나가 TRUE면 레코드가 선택되므로 업무ID에 MAN이 포함되거나 급여가 $10,000이상인 사원이 선택

된다.

MSSQL 디비 예제 :

select title_id , title , price from titles where price >= 20 or title like '%com%'

OR 진리표

OR TRUE FALSE NULL

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE NULL

NULL TRUE NULL NULL

예제) NOT 연산자 사용

오라클 디비 예제 : SELECT last_name, job_id

FROM employees

WHERE job_id

NOT IN ( 'IT_PROG', 'ST_CLERK', 'SA_REP' );

☞설명)

예제는 업무ID가 IP_PROG, ST_CLERK 또는 SA_REP가 아닌 모든 사원의 이름과 업무ID를 표시합니다.

참고 NOT 연산자는 BETWEEN, LIKE, NULL등 다른 SQL연산자와 함께 사용할 수 있습니다.

. . . WHERE job_id NOT IN ( 'AC_ACCOUNT', 'AD_VP' )

. . . WHERE salary NOT BETWEEN 1000 AND 15000

. . . WHERE last_name NOT LIKE '%A%'

. . . WHERE commission_pct IS NOT NULL

MSSQL 디비 예제 :

select title_id , title , price from titles

where title_id not in ('bu1032' , 'mc3021')

select title_id , title, price from titles

where price not between 10 and 30

또는

select title_id , title, price from titles

where price < 10 or price > 30

select title_id , title , price from titles

where title not like '%computer%'

2-8. 우선선위 규칙

계산 순서 연산자

1 산술 연산자

2 연결 연산자 ||

3 비교 조건

4 IS [NOT] NULL, LIKE, [NOT] IN

5 [NOT] BETWEEN

6 NOT논리 조건

7AND논리 조건

8 OR 논리 조건

괄호를 사용하여 우선순위 규칙을 무시하고 우선순위를 변경할 수 있다.

우선순위 규칙은 표현식을 평가하고 계산하는 순서를 결정한다.

표에 기본 우선순위 순서가 나와 있다.

먼저 계산할 식의 좌우에 괄호를 사용하여 기본 순서를 무시하고 우선순위를 변경할 수 있다.

예제) 우선순위 규칙

오라클 디비 예제 : SELECT last_name, job_id, salary

FROM employees

WHERE job_id = 'SA_REP'

OR job_id = 'Ad_PRES'

AND salary > 15000;

☞설명) AND 연산자의 우선순위 예제

예제에는 두 가지 조건이 있다.

․ 첫 번째 조건은 업무ID가 AD_PRES면서 급여가 15,000을 넘어야 한다.

․ 두 번재 조건은 업무ID가 SA_PEP여야 한다. 따라서

SELECT문은 다음과 같이 인식한다.

“사장이면서 급여가 $15,000를 넘는 사원 또는 영업 사원인 사원의 행(row)을 선택한다.“

MSSQL 디비 예제 :

SELECT title_id, title , price FROM　titles

WHERE title_id = 'bu1032'

OR title_id = 'mc3021'

AND price = 2.99

예제) 우선순위 규칙 : 괄호를 사용하여 우선순위를 강제로 지정한다.

오라클 디비 예제 : SELECT last_name, job_id, salary

FROM employees

WHERE ( job_id = 'SA_REP'

OR job_id = 'AD_PRES' )

AND salary > 15000;

☞설명) 괄호 사용

예제에는 두 가지 조건이 있다.

․ 첫 번째 조건은 업무ID가 AD_PRES 또는 SA_REP여야 합니다.

․ 두 번째 조건은 급여가 $15,000를 넘어야 합니다.

따라서 SELECT문은 다음과 같이 인식합니다.

“사장 또는 영업사원이면서 급여가 $15,000를 넘는 사원의 행을 선택합니다.”

MSSQL 디비 예제 :

SELECT title_id, title , price FROM　titles WHERE ( title_id = 'bu1032'

OR title_id = 'mc3021' ) AND price = 2.99

또는

SELECT title_id, title , price FROM　titles WHERE title_id = 'bu1032' AND price = 2.99

OR title_id = 'mc3021' AND price = 2.99

2-9. ORDER BY절

․ OREDR BY절을 사용하여 행을 정렬합니다.

- ASC : 오름차순, 기본값

- DESC : 내림차순

․ OREDR BY절은 SELECT문의 가장 끝에 둔다.

① ORDER BY절

질의 결과로 반환되는 행의 순서는 정의되어 있지 않으므로 ORDER BY절을 사용하여 행을 정렬한다.

ORDER BY절은 SQL문의 가장 끝에 두어야 하며 정렬 조건으로 표현식, 별칭 또는 열 위치를 지정할 수 있다.

구문

SELECT expr

FROM table

[WHERE condition(s)]

[ORDER BY { column, expr } [ASC | DESC]];

구문 설명 :

ORDER BY 검색된 행의 표시 순서를 지정한다.

ASC 행을 오름차순(기본순서)으로 정렬한다.

DESC 행을 내림차순으로 정렬한다.

ORDER BY절을 사용하지 않으면 정렬 순서를 정의할 수 없으며 동일한 질의를 두 번 수행할 경우에도 행이 동일한 순서

로 인출되지 않는다.

그러나 ORDER BY절을 사용하면 특정 순서로 행을 표시할 수 있습니다.

예제) 오름차순으로 정렬

오라클 디비 예제 : SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date;

오름차순 정렬에서 ASC는 생략이 가능하다.

MSSQL 디비 예제 :

SELECT pub_id ,title, title_id, price FROM titles

ORDER by pub_id

또는

SELECT pub_id ,title, title_id, price FROM titles

ORDER by pub_id ASC

또는

SELECT pub_id ,title, title_id, price FROM titles

ORDER by 1 ASC

예제) 내림차순으로 정렬

오라클 디비 예제 : SELECT last_name, job_id, department_id, hire_date

FROM employees

ORDER BY hire_date DESC;

☞설명)

행 표시 순서를 바꾸려면 ORDER BY절에서 열 이름 다음에 DESC키워드를 지정한다.

예제는 가장 최근에 입사한 사원 순으로 결과를 정렬한다.

MSSQL 디비 예제 :

SELECT title, pub_id , title_id, price FROM titles ORDER by pub_id DESC;

또는

SELECT title, pub_id , title_id, price FROM titles ORDER by 2 DESC;

참고

데이터 기본 정렬

기본 정렬 순서는 오름차순이다.

․ 숫자 값은 작은 값부터 표시된다.(예: 1-999)

․ 날짜 값은 이론 값부터 표시된다.(예: 01-JAN-92가01-JAN-95보다 먼저 표시됨)

․ 문자 값은 영문자순으로 표시된다.(예: A가 먼저 표시되고 Z가 나중에 표시됨)

․ 널 값은 오름차순에서 마지막에 표시되고 내림차순에서는 처음에 표시된다.

예제) 열 별칭을 기준으로 정렬

오라클 디비 예제 : SELECT employees_id, last_name, salary * 12 annsal

FROM employees

ORDER BY annsal;

☞설명)

ORDER BY절에 열 별칭을 사용할 수 있다. 예제는 연봉을 기준으로 데이터를 정렬한다.

MSSQL 디비 예제 :

SELECT pub_id ,title, title_id, price * 12 annsal FROM titles

ORDER by annsal;

예제) 여러 열을 기준으로 정렬

오라클 디비 예제 : SELECT last_name, department_id, salary

FROM employees

ORDER BY department_id, salary DESC;

☞설명)

․ ORDER BY목록의 순서가 정렬 순서입니다.

․ SELECT 목록에 없는 열을 기준으로 정렬할 수도 있습니다.

여러 열을 기준으로 질의 결과를 정렬할 수 있으며 테이블의 열 수만큼 가능하다.

OREDR BY절에 열을 지정하고 쉼표로 열 이름을 구분한다.

열 순서를 바꾸려면 이름 뒤에 DESC를 지정한다.

SELECT절에 포함되지 않는 열을 기준으로 정렬할 수도 있다.

예제) department_id는 오름차순 , salary는 내림차순으로 정렬하라.

오라클 디비 예제 : SELECT last_name, salary

FROM employees

ORDER BY department_id, salary DESC;

☞설명)

모든 사원의 이름과 급여를 표시하고 부서 번호를 기준으로 정렬한 후 급여를 기준으로 내림차순으로 결과를 정렬한다.

MSSQL 디비 예제 :

--두개 이상의 열을 이용한 정렬