sql 정리-2

손현수·2022년 10월 22일

와일드카드 사용

where name like '%dar%' //name column에 dar이 들어가는 row, 데이터 값을 정확히 모를 때 사용한다.

where name like 'intro%' //name column에 앞 글자가 intro인 row, 뒷 글자는 상관없는 경우

where name like '%comp' //name coloumn에 뒷 글자가 comp인 row, 앞 글자는 상관없는 경우

where name like '_A_T%S' //name column에 두번쨰 위치에 A를 포함하고 네번째 위치에 T를 포함하며 마지막 위치는 S로 끝나는 문자열

beteween 연산자

  • 범위에 상한과 하한 기준이 모두 있을 때, 각각의 개별 조건을 사용하는 대신 다음과 같이 between 연산자를 활용하는 하나의 조건을 사용할 수 있다.
mysql> select customer_id, rental_date from rental where rental_date between '2005-06-14' and '2005-06-16';

조인

  • 조인을 이해하기 위해 customer 테이블과 address 테이블에 대한 정의를 확인한다.

  • 주소와 함께 고객의 성과 이름을 검색한다고 가정하자. 이때 쿼리는 customer.first_name, customer.last_name, address.address 열을 검색해야 한다. 같은 쿼리에서 두 테이블의 데이터를 검색해야 하는 상황
  • 주목해야 할 부분은 address 테이블의 고객 ID에 해당하는 customer.address_id이다. customer.address_id 열은 address 테이블의 외래 키이다. 앞으로 살펴볼 쿼리는 서버가 customer.address_id 열을 두 테이블 간의 연결 수단으로 사용하여 두 테이블의 열을 하나의 결과셋에 포함하도록 한다. 이 작업을 조인이라고 한다.

데카르트 곱

  • 가장 쉬운 방법은 customer 및 address 테이블을 쿼리의 from 절에 주고 조인하는 것이다. 
mysql> select c.first_name, c.last_name, a.address from customer as c join address as a;
  • 위의 조인을 교차조인이라고 하는데 거의 사용하지 않는다.

내부 조인

  • 앞서 customer.address_id 열이 두 테이블의 연결 역할을 했는데, 이 정보를 from 절의 하위절에 추가해야 한다.
mysql> select c.first_name, c.last_name, a.address from customer as c join address as a on c.address_id = a.address_id;
  • address_id 열을 사용해서 customer와 address 테이블을 조인하도록 서버에 지시하는 on 하위절을 추가한 결과, 599개의 행을 얻었다. 얘를 들어 customer 테이블의 Marry Smith 행은 address_id 열의 값이 5이다. 서버는 이 값을 사용해서 address_id 값이 5인 address 테이블의 행을 조회한 다음, 해당 행의 address 열에서 '1913 Hanoi Way' 값을 찾는다. 한쪽 테이블에는 address_id 열의 값이 있지만 다른 테이블에는 없는 경우, 해당 값을 포함하는 행에 대한 조인은 실패하고 해당 행이 결과에서 제외된다. 이러한 유형의 조인을 내부 조인이라고 하며 가장 일반적으로 사용되는 조인 유형이다. 즉, customer 테이블의 행에서 address_id 열의 값은 999인데 address 테이블에는 address_id 열의 값이 999인 행이 없다면, 해당 고객의 행은 결과셋에 포함되지 않는다.
  • 조인 유형을 지정하지 않으면 서버는 기본적으로 내부 조인을 수행한다. 그러나 조인에도 여러 유형이 존재하므로 필요한 조인 유형을 정확하게 정의하는 게 좋다.
  • 두 테이블을 조인할 때 사용한 열의 이름이 동일한 경우 다음과 같이 on 하위절 대신 using 하위절을 사용할 수 있다. 
mysql> select c.first_name, c.last_name, a.address from customer as c join address as a using (address_id);

세개 이상 테이블 조인

  • 고객의 주소가 아닌 고객의 도시 이름을 반환하는 쿼리를 작성해본다면 도시명이 필요하다. 도시명이 address 테이블에 저장되어 있지는 않지만 외래 키를 통해 city 테이블을 액세스할 수 있다. 결론적으로 각 고객의 도시를 보여주려면 address_id 열을 사용하여 customer 테이블에서 address 테이블로 연결한 다음 city_id 열을 사용하여 address 테이블에서 city 테이블로 연결한다.
mysql> select c.first_name, c.last_name, ct.city 
from customer as c 
inner join address as a on c.address_id = a.address_id 
inner join city as ct on a.city_id = ct.city_id;
  • 위의 코드를 서브쿼리를 사용하여 쿼리를 작성해본다면 다음과 같다
mysql> select c.first_name, c.last_name, addr.city 
from customer as c 
inner join (select a.address_id, ct.city from address as a 
inner join city as ct 
on a.city_id = ct.city_id) as addr 
on c.address_id = addr.address_id;

셀프 조인

  • 영화 시리즈에서 film 테이블에 해당 영화의 전편을 나타내는 prequel_film_id 열이 포함되어 있다고 가정하면 셀프 조인을 이용해서 전편 제목과 더불어 전편이 있는 모든 영화 제목을 나열하는 쿼리를 다음과 같이 작성할 수 있다.
select f.tilte, f_prnt.title as prequel from film as f
inner join film as f_prnt on f_prnt.film_id = f.prequel_film_id
where f.prequel_film_id is not null;

집합 이론

  • 합집합: union 연산, a union b

  • 교집합: intersect 연산, a intersect b

  • 차집합: except 연산, a except b

  • (a union b) except (a intersect b): a와 b의 합집합에서 교집합을 제외한 부분

  • 두 데이터셋에 대한 집합 연산을 수행할 때는 다음 규칙을 적용해야 한다.

  1. 두 데이터셋 모두 같은 수의 열을 가져야 함
  2. 두 데이터셋의 각 열의 자료형은 서로 동일해야 함(또는 서버가 변환할 수 있어야 함)
  • 집합 연산자 간단한 예시
mysql> select 1 as num, 'abc' as str union select 9 as num, 'xyz' as str;

  • 각 개별 쿼리는 숫자 열과 문자열 열을 가지는 단일 행으로 구성된 데이터셋을 생성한다. 이 경우 집합 연산자 union은 데이터베이스 서버에서 두 집합의 모든 행을 결합한다. 따라서 최종 집합에는 두 열을 가진 두 행이 포함된다. 이 쿼리는 여러 개의 독립적인 쿼리로 구성되므로 "복합 쿼리"라고 한다.

집합 연산자

  • union 연산자: union 및 union all 연산자는 여러 데이터 집합을 결합할 수 있다. union은 결합된 집합을 정렬하고 중복을 제거함 반면 union all은 그렇지 않다.

  • intersect 연산자: 복합 쿼리에서 두 쿼리가 겹치지 않는 데이터셋을 반환할 경우 intersect 연산의 결과는 비어있다. intersect 연산자는 중복 행을 제거한다.

  • except 연산자: except 연산자는 첫번째 쿼리 결과에서 두번째 쿼리 결과와 겹치는 부분을 빼고 반환한다. except 연산자와 except all 연산자에도 차이가 존재하는데 예를 들어 집합 A = {10, 11, 12, 10, 10}, 집합 B = {10, 10}이라고 하자. A except B를 실행하면 {11, 12}가 반환되고 A except all B를 실행하면 {10, 11, 12}를 반환한다. except 연산은 모든 중복 데이터를 제거하는 반면, except all 연산은 집합 B에서 집합 A에 대한 중복 데이터가 발생할 때만 해당 데이터를 제거한다는 것이다.

NULL

  • null에는 다양한 경우가 존재한다.
  1. 해당사항 없음: 예를 들어 ATM 기계에서 발생한 거래내역의 직원 ID 열
  2. 아직 알려지지 않은 값: 예를 들어 고객 테이블에 행이 생성될 때 연방 ID를 알 수 없는 경우
  3. 정의되지 않은 값: 예를 들어 데이터베이스에 아직 추가되지 않은 제품의 계좌가 생성된 경우
  • null로 작업할 때는 다음 사항들을 기억해야 한다.
    - null일 수는 있지만, null과 같을 수는 없다.
    - 두 개의 null은 서로 같지 않다.
  • 표현식이 null인지 확인하려면 다음과 같이 is null 연산자를 사용해야 한다.
mysql> select rental_id, customer_id from rental where return_date is null;

그룹화의 개념

  • 때로는 찾고자 하는 결과를 생성하기 전에 데이터베이스 서버에서 데이터를 약간 손봐야 할 수 있다. 예를 들어 우수 고객에게 무료 대여 쿠폰을 보내야 한다고 가정하자. 16000개가 넘는 대여기록 데이터를 일일이 조회하고 영화를 가장 많이 대여한 고객을 찾는 것은 불가능하다. 대신 group by 절을 사용하여 데이터베이스 서버에 데이터를 그룹화할 수 있다. 다음 예시를 보자
mysql> select customer_id, count(*) from rental group by customer_id;

  • 집계함수 count()는 그룹의 행 수를 세고 별표(*) 문자는 서버가 각 그룹의 모든 행 수를 세도록 한다. 위의 결과를 많은 영화를 대여한 고객 순으로 정렬하려면 order by 2 desc 절을 끝에 추가하면 된다.

  • 데이터를 그룹화할 때 결과셋에 원하지 않는 데이터를 필터링해야 할 수 있다. where 절이 적용된 뒤 group by 절이 실행되므로, 이러한 목적으로 where 절에 필터조건을 추가할 수는 없다. 만약 다음과 같이 쿼리를 작성하면 에러가 발생한다.

mysql> select customer_id, count(*) from rental 
where count(*) >= 40 group by customer_id;
  • 에러가 발생하는 이유는 where 절이 적용될 때 그룹이 아직 생성되지 않았으므로 where 절에서 count(*) 집계함수를 참조할 수 없다. 대신 그룹 필터조건을 having 절에 넣어야 한다.
mysql> select customer_id, count(*) from rental
    -> group by customer_id having count(*) >= 40;
  • having 절을 통해 대여 건수가 40개 미만인 그룹은 필터링되었으므로 결과셋에 40편 이상의 영화를 대여한 고객만 포함됨

집계 함수

  • 집계 함수는 그룹의 모든 행에 대해 특정 연산을 수행한다.
  1. max(): 집합 내의 최댓값을 반환
  2. min(): 집합의 내의 최솟값을 반환
  3. avg(): 집합의 평균값을 반환
  4. sum(): 집합의 총합을 반환
  5. count(): 집합의 전체 레코드 수를 반환
mysql> select max(amount) as max_amt, 
min(amount) as min_amt, 
avg(amount) as avg_amt, 
sum(amount) as sum_amt, 
count(*) as num_payments from payment;

  • 이 쿼리의 결과에 따르면 payment 테이블의 16049개 행에서 영화를 대여료로 지불한 최대 금액은 11.99달러, 최소는 0달러, 평균은 4.2달러, 총 대여료는 67416.51달러이다.

  • 만약 전체가 아닌 각 고객에 대해 동일한 5개의 집계 함수를 실행하도록 위의 쿼리를 확장하려면 group by 절을 추가해야 한다.

mysql> select max(amount) as max_amt, 
min(amount) as min_amt, 
avg(amount) as avg_amt, 
sum(amount) as sum_amt, 
count(*) as num_payments from payment 
group by customer_id;
  • group by 절을 포함시키려면 서버는 먼저 customer_id 값이 동일한 행들을 그룹화한 다음 5개의 집계 함수를 599개의 각 그룹에 적용한다.

고유한 값 계산(count() 함수)

  • 각 그룹에서 count() 함수를 사용할 때 그룹의 모든 customer_id 수를 계산할지 아니면 그룹의 모든 customer_id 중에 고유한 값에 대해서만 계산할지 선택할 수 있다. 
mysql> select count(customer_id) as num_rows, count(distinct customer_id) 
as num_customers from payment;

  • 쿼리의 첫 번째 열은 단순히 payment 테이블의 행 수를 계산하는 반면, 두 번째 열은 customer_id의 값을 비교해서 고유한 값을 가지는 customer_id의 수만 계산한다. distinct를 지정함으로써 count() 함수는 중복을 찾아 제거하기 위해 그룹의 각 멤버에 대한 customer_id 값을 확인한다.

서브쿼리

  • 서브쿼리는 다른 sql 구문(포함 구문)에 포함된 쿼리이다. 서브쿼리는 항상 괄호 안에 들어가며 일반적으로 포함 구문보다 먼저 실행된다.

  • 스칼라 서브쿼리

mysql> select city_id, city from city where country_id <> 
(select country_id from country where country = 'India');
  • 이 쿼리는 인도에 없는 모든 도시를 반환한다. 구문의 마지막 줄에 있는 서브쿼리는 country가 India인 country_id를 반환하며 포함 쿼리는 해당 country_id가 없는 모든 도시를 반환한다.
  • <> 연산자가 의미하는 것: !=

in 연산자와 not in 연산자, all 연산자

  • 서브쿼리가 두개 이상의 행을 반환하는 경우 동등조건을 사용할 수 없다. 하나의 값을 값 집합과 동등하게 비교할 수는 없지만 값 집합 내에서 하나의 값을 찾을 수 있는지 여부는 확인할 수 있다.
mysql> select country_id from country where country in ('Canada', 'Mexico');
+------------+
| country_id |
+------------+
|         20 |
|         60 |
+------------+

  • in 연산자는 country 열에서 두개의 문자열을 찾을 수 있는지 여부를 확인한다. 조건이 만족되면 해당 행이 결과셋에 추가된다.

  • in 연산자를 사용해서 Canada 또는 Mexico에 있는 모든 도시를 반환하는 쿼리

mysql> select city_id, city from city 
where country_id in (select country_id from country where country in ('Canada', 'Mexico'));

  • not in 연산자를 사용하면 'Canada' 또는 'Mexico'에 없는 모든 도시를 표시한다.

  • all 연산자는 한 집합의 모든 값과 하나의 값을 비교할 수 있다. 예를 들어 무료 영화를 대여한 적이 없는 모든 고객을 찾는 쿼리는 다음과 같다.

mysql> select first_name, last_name from customer 
where customer_id <> all (select customer_id from payment where amount = 0);
  • 대부분의 사람들은 all 연산자의 사용을 선호하지 않는다. not in 연산자를 사용해서 동일한 결과를 생성하는 것이 가능
mysql> select first_name, last_name from customer 
where customer_id not in (select customer_id from payment where amount = 0);

상관 서브쿼리

  • 상관 서브쿼리는 포함 구문에 의존적이다. 비상관 서브쿼리와 달리 단독으로 실행할 수 없고 포함 구문을 실행하기 전에 실행되지 않는다. 대신 상관 서브쿼리는 각 후보 행(최종 결과에 포함될 수 있는 행)에 대해 한 번씩 실행된다.
mysql> select c.first_name, c.last_name 
from customer as c 
where 20 = (select count(*) from rental as r where r.customer_id  = c.customer_id);
  • 위의 서브쿼리는 상관 서브쿼리로 각 고객의 영화 대여 횟수를 계산한 후, 정확히 20편의 영화를 대여한 고객을 검색함. 
mysql> select c.first_name, c.last_name 
from customer as c 
where (select sum(p.amount) from payment as p where p.customer_id = c.customer_id) 
between 180 and 240;
  • 위의 쿼리는 모든 영화 대여에 관한 총 지불액이 180달러에서 240달러 사이인 모든 고객을 찾는다.

exists 연산자

mysql> select c.first_name, c.last_name 
from customer as c where exists (select 1 from rental as r 
where r.customer_id = c.customer_id and date(r.rental_date) < '2005-05-25');
  • 위의 쿼리는 총 몇 편의 영화를 대여했는지와 상관없이 2005년 5월 25일 이전에 한편 이상의 영화를 대여한 모든 고객을 찾는다.
profile
손현수
안녕하세요.
이전 포스트

sql 정리-1

다음 포스트

코틀린 정리 1장, 2장

0개의 댓글