SQL 데이터 조작문 - 데이터 검색

데이터 검색

SQL 검색문의 기본적인 구조

select 열_리스트

from 테이블_리스트

where 조건

예를 들어, '컴퓨터과 학생의 이름(name)과 학번(sno)을 검색하라는 검색문은 다음과 같이 표현한다.

select name, sno

from student

where dept = '컴퓨터';

테이블의 열 전부를 검색하는 경우

select *

from student;

조건 검색

query: 학생(student) 테이블에서 학과(dept)가 '컴퓨터'이고 학년(year)이 4인 학생의 학번(sno)과 이름(name)을 검색하라.

select sno, name

from student

where dept = '컴퓨터' and year = 4;

# 일반적으로 where절에 나오는 조건식에는 비교 연산자 ==, !=, >=, <=, >, < 등과 불리언 연산자 and, or 그리고 not 을 사용할 수 있다. 또한 필요한 경우에 괄호를 사용할 수 있다.

순서를 명시하는 검색

query: 등록(enrol) 테이블에서 중간성적(midterm)이 90점 이상인 학생의 학번(sno)과 과목번호(cno)를 검색하되 학번(sno)에 대해서는 내림차순으로, 또 같은 학번에 대해서는 과목번호(cno)의 오름차순으로 검색하라.

select sno, cno

from enrol

where midterm >= 90

order by sno desc, cno asc;

# 일반적으로 검색 결과는 시스템이 정하는 순서에 따라 출력된다. 그러나 사용자가 검색 결과의 순서를 오름차순(asc) 이나 내림차순(desc)을 명시할 수 있다. 여기서 처음 나온 sno는 1차(주) 정렬이고, 두 번째 나온 cno는 2차(부) 정렬이다. 2차 정렬은 1차 정렬 범위내에서 정렬시키는 것을 말한다.

산술식과 문자 스트링이 명시된 검색

query: 등록(enrol) 테이블에서 과목번호(cno)가 'C312'인 중간성적(midterm)에 3점을 더한 점수를 학번(sno), '중간성적 =' 이란 텍스트를 갖는 시험, 그리고 점수라는 열이름으로 검색하라.

select sno as '학번', '중간시험=' as 시험, midterm+3 as '점수'

from enrol

where cno = 'C312';

# 일반적으로 select 절에는 열이름뿐만 아니라 문자 스트링을 명시할 수 있으며 열이름, 상수 그리고 산술 연산자로 구성된 산술식이 나타날 수 있다. 필요한 경우에 괄호를 산술식에 사용할 수 있다. 

"as  학번" 이라는 명시는 sno의 별명을 지정한 것이다. "as 시험"과 "as 점수"가 명시되지 않았다면 열이름이 공백이 되었을 것이다 .뒤에 나오는 질의문 예는 편의상 모두 적절히 별명이 명시된 것으로 가정한다.

복수 테이블로부터의 검색

query: 과목번호(cno) 'C413'에 등록한 학생의 이름(name), 학과(dept), 성적(grade)를 검색하라.

select student.name, student.dept, enrol.grade

from student, enrol

where student.sno = enrol.sno 

and enrol.cno = 'c413';

# 관계 데이타베이스 시스템이 다른 유형의 데이타베이스 시스템에 비해 강력한 점은 둘 이상의 테이블을 조인할 수 있다는 것이다. 위의 예는 SQL에서 조인이 어떻게 표현되는가를 보여주고 있다. 

조인 검색은 FROM 절에 관련 테이블들을 여러 개 명시할 수 있다는 것이다. 이때 열이름이 모호하게 되면 열이름을 붙인 한정된 열이름을 써야 한다.

이 조인이 필요한 테이블을 SQL2에서는 사용자가 FROM절에 직접 명시할 수 있도록 하고 있는데 다음 세 가지 형태가 있다.

1. 테이블1 join 테이블2 on 조건식

2. 테이블1 join 테이블2 using(열_리스트)

3. 테이블1 natural join 테이블2

따라서 앞의 질의문을 이 형식으로 표현하면 다음과 같다.

select name, dept, grade

from student join enrol on (student.sno = enrol.sno)

where enrol.cno = 'C413';

select name, dept, grade

from student join enrol using(sno)

where enrol.cno = 'C413';

select name, dept, grade

from student natural join enrol

where enrol.cno = 'C413';

# 이 세 형식은 모두 그 의미가 동등하지만 표현에 약간의 차이가 있다. 

형식 1은 where절에서 명시하는 조인 조건을 from 절로 옮긴 것과 같다. 

형식 2는 열_리스트에 나열된 공통 열을 이용하여 동등 조인한 것인데 공통 열은 결과 테이블에 한 번씩 다른 나머지 열보다 먼저 나타난다.

형식 3은 조인되는 두 테이블에 공통되는 모든 열을 포함시킨 동등 조인으로 공통 열은 결과 테이블에 한 번씩 왼쪽에 먼저 나타난다.

자기 자신의 테이블에 조인하는 검색

query: 같은 학과 학생들의 학번을 쌍으로 검색하라. 단, 첫 번째 학번은 두 번째 학번보다 적게하라.

select s1.sno, s2.sno

from student s1, student s2

where s1.dept = s2.dept

and s1.sno < s2.sno;

집단 함수(aggregate function) 를 이용한 검색

'학생수는 몇 명인가?' 하는 간단한 잘의어는 지금까지 나열한 것으로 표현할 수 있다. 이런 문제를 해결하기 위하여 SQL은 특별히 집단 함수 또는 열함수(column function) 를 제공하고 있다. 이 집단 함수에는 count, sum, avg, max, min이 있는데 이들의 기능들은 한 열의 값 집합에 적용하여 다음과 같은 결과를 생성한다.

count : 값의 개수

sum : 값의 총계

avg : 평균 값

max : 최대 값

min : 최소 값

물론 sum, avg는 수값에만 적용된다.

query: 학생 테이블에 학생수가 얼마인가를 검색하라.

select count(*) as 학생수

from student;

# count(*)의 처리 대상은 행(튜플)의 집합이고 그 결과는 그 집합에 속하는 행의 수이다. 이때 중복되는 행도 함께 포함된다.

query: 학번(sno) 이 300인 학생이 등록한 과목(cno)은 몇 개인가?

select count(distinct cno)

from enrol

where sno = 300;

# 수를 계산하려면 count(distinct 열_이름) 으로 명시하면 된다. 명시된 열에 속한 값의 집합에서 상이한 값만을 대상으로 sum이나 avg를 구할 때는 "DISTINCT"를 명시해야 한다.

query: 과목 C413에 대한 중간 성적의 평균은 얼마인가?

select avg(midterm) as 평균점수

from enrol

where cno = 'C413';

# avg 함수 결과값의 타입은 해당 열의 데이타 타입과 같다.

Group By를 이용한 검색

query: 과목별 기말 성적의 평균을 검색하라.

select cno, avg(final) as '기말평균'

from enrol

group by cno;

# group by는 논리적으로 from 절에 있는 테이블을 group by절에 명시된 열의 값에 따라 그룹으로 분할한다. 물론 그렇다고 테이블이 데이타베이스내에서 물리적으로 분할되는 것은 아니다.

위의 예에서는 등록(enrol) 테이블을 5개의 그룹(C123, E123, ...)으로 분할하여 각 그룹별로 select 문을 실행한다. 이와 같이 하나의 테이블을 접근하여 몇 개의 그룹으로 만들 수 있으나 group by 자체는 어떤 순서를 명시하지는 않는다.

따라서 과목별 순서를 명시하려면 order by cno절을 group by절 다음에 첨가하면 된다.

having 을 사용한 검색

query: 세 사람 이상 등록한 과목의 기말 평균 성적을 검색하라.

select cno, avg(final) as 평균

from enrol

group by cno

having count(*) >= 3;

# having은 각 그룹의 구성 요건을 명시한다. 만일 group by절이 생략되면 테이블 전체를 하나의 그룹으로 취급한다.

부속 질의문(sub query)을 사용한 검색

query: 과목 번호(cno) 'C413'에 등록한 학생의 이름(name)을 검색하라.

select name

from student

where sno in

(select sno

from enrol

where cno = 'C413');

# 부속 질의문은 다른 질의문에 중첩되어 사용된 검색문으로서 select-from-where-group by-having의 형태를 취한다. 위의 예에서 부속 질의문은 in 다음에 사용되었는데 이때 부속 질의문은 검색 결과로 학번값의 집합 {100, 300, 400} 을 생성하고 in은 집합의 멤버십 연산자로 해석할 수 있다. 

그러므로 위의 질의문은 다음과 같은 질의문의 뜻이 된다.

select name

from studnet

where sno in (100, 300, 400);

또한 위 원래의 질의문은 조인을 사용하는 질의문으로 표현할 수 있다. 

select student.sname

from studnet, enrol

where student.sno = enrol.sno

and enrol.cno = 'C413';

이 원래 질의에 대한 이 두 가지 형색의 질의문은 모두 맞다. .이것은 하나의 질의에 대한 표현 형식이 여러 가지 있을 수 있다는 것을 뜻한다. 다만 어느 형식을 쓰느냐 하는 것은 사용자의 기호에 달려 있다. 

query: 과목번호 'C413'에 등록하지 않은 학생의 이름을 검색하라.

select name

from student

where sno not in 

{select sno

from enrol

where cno = 'C413');

# 부속 질의문은 in 이외의 경우에도 사용할 수 있다. 

query: 학생 김 연아와 같은 학과에 속하는 학생의 이름과 학과를 검색하라.

select name, dept

from student

where dept = 

(select dept

from student

where name = '김 연아');

like를 사용하는 검색

query: 과목번호(cno)가 c로 시작되는 과목의 번호와 과목이름(cname)을 검색하라.

select cno, cname

from course

where cno like 'c%';

# like 프레디킷은 열 이름과 함께 스트링 상수를 명세한 검색 조건이다.

이 예에서 사용된 '%' 는 c로 시작하기만 하면 어떤 길이의 어떤 문자 스트링도 관계없다는 뜻이다. 

만일 sname like s'__'  라 하면 s로 시작되는 세 문자를 말하고, sname like '%s__' 라 하면 스트링 끝에서 세 번째가 s인 문자열을 한다. 또 단순히 sname like '%s%' 라 하면 s가 포함된 문자열을 말한다.

NULL을 사용한 검색

학생 테이블에 학번이 600, 이름이 김 연아, 학과가 NULL인 튜플이 있다고 가정한다.

query: 학과(dept)가 NULL인 학생의 학번과 이름을 검색하라.

select sno, snmae

from student

where dept is NULL

# 일반적으로 NULL을 검색 조건속에 명시할 때는 '열_이름 is [not] NULL' 의 형식만 허용된다. 

열_이름 = NULL의 형식은 불법적인 것으로 허용되지 않는다. 또한 널값은 조건식을 실행할 때 어떤 다른 값과 비교하면 어떤 비교 연산자이든지간에 거짓으로 된다. 

exists를 사용하는 검색

query: 과목 'C413'에 등록한 학생의 이름을 검색하라.

select name

from student

where exists

(select *

from enrol

where sno = student.sno

and cno = 'C413');

# exists는 존재 정량자(existential quantifier)로서 exists 다음에 나오는 검색문의 실행 결과 검색된 튜플이 존재하는가를 검사한다. 따라서 이 부속 질의문은 exists(select * from) 검색문을 실행한 뒤 그 결과가 공집합이 아니면 참이고, 공집합이면 거짓으로 판정한다.

위의 예에서는 각 학생이름에 대한 학번을 검색한 뒤 그 학번에 연관된 등록(enrol) 테이블에 exists 검색문을 수행해서 참일 때 그 학생이름을 결과에 속하도록 한다. 따라서, 이 질의문은 사실상 '학생 테이블에서 학생이름을 검색하는데 어떤 학생이냐 하면 과목 'C413'에 등록하여 등록(enrol) 테이블에 튜플이 존재하는 그런 학생이다.' 라는 뜻이 된다.

query: 과목 'C413'에 등록하지 않은 학생의 이름을 검색하라.

select name

from student

where not exists

(select *

from enrol

where sno = student.sno

and cno = 'C413');

union 이 관련된 검색

query: 3학년이거나 또는 과목 'C324'에 등로한 학생의 학번을 검색하라.

select sno

from student

where year = 3

union

select sno

from enrol

where cno = 'C324';

# union은 일반 집합론의 합집합과 같다. 따라서 union이 사용되면 결과 테이블에서 중복되는 튜플은 제거된다.