SQL_2
Learning SQL 3장 - 4장
쿼리 절에는 select, from, where, group by, having, order by가 있다.
select 절은 데이터베이스 서버가 판단하는 마지막 절 중에 하나이다.
select language_id,
'common' language_usage,
language_id * 3.1415927 lang_pi_value,
upper(name) language_name
from language;
select version(),
user(),
database();
열의 별칭을 select 절의 각 요소 뒤에 추가할 수 있다.
select language_id,
'common' language_usage,
language_id * 3.1415927 lang_pi_value,
upper(name) language_name
from language;
열 별칭을 더 두드러지게 하려면 별칭 이름 앞에 as 키워드를 사용할 수 있다.
select language_id,
'common' as language_usage,
language_id * 3.1415927 as lang_pi_value,
upper(name) as language_name
from language;
쿼리가 중복된 데이터 행을 반환하는 경우, 영화에 출연한 모든 배우의 ID를 검색하는 쿼리를 실행하면 다음과 같다.
select actor_id from film_actor order by actor_id;
배우들이 한 편 이상의 영화에 출연했으므로 동일한 배우 ID가 여러번 표시되는데, 그들이 출연한 각 영화마다 배우 ID가 반복 출력되도록 하는 대신에 배우들의 고유한 집합을 출력하고자 한다. 이는 select 키워드 뒤에 distinct 키워드를 추가하여 확인한다.
select distinct actor_id from film actor order by actor_id;
결과셋에는 각 영화마다 출연한 배우 ID를 전부 나열한 전체 행이 아닌, 배우 ID 하나씩 총 200개의 행이 포함된다.
서버가 중복 데이터를 제거하는 것을 원치 않거나 결과에 중복값이 없는게 확실할 때는 distinct를 지정하는 대신 all 키워드를 지정할 수 있다. 그러나 all 키워드는 기본값이고 명시적으로 이름을 지정할 필요가 없어서, 대부분 쿼리에 all을 포함하지 않는다. *distinct 결과를 생성하려면 데이터를 정렬해야 하므로 결과 셋의 용량이 클 때는 시간이 오래걸릴 수 있으므로, 중복이 없는지 확인하기위해 distinct를 사용하기 보다는 데이터를 먼저 이해하는 것이 중요하다.
from 절은 쿼리에 사용되는 테이블을 명시할 뿐 아니라, 테이블을 서로 연결하는 수단도 함께 정의한다.
4가지 유형의 테이블이 존재하는데, 영구/파생/임시/가상 테이블이 있다. 파생테이블의 간단한 예제는 다음과 같다.
select concat(cust.last_name, ', ', cust.first_name) full_name
from
(select first_name, last_name, email
from customer
where first_name = 'JESSIE'
) cust;
임시 테이블은 다음과 같다.
create temporary table actors_j
(actor_id smallint(5),
first_name varchar(45),
last_name varchar(45));
임시테이블을 사용하여 성이 j로 시작하는 배우를 임시로 저장하는 방법을 보여주는 간단한 예제는 다음과 같다.
insert into actors_j
select actor_id, first_name, last_name
from actor
where last_name like 'j%';
select * from actors_j;
가상테이블에 대한 예제는 다음과 같다.
create view cust_vw as
select customer_id, first_name, last_name, active
from customer;
뷰를 작성하더라도 데이터가 추가 생성되거나 저장되지는 않는데, select 문대신 뷰가 존재한다. 뷰를 다음과 같이 쿼리한다.
select first_name, lastname
from cust_vw
where active = 0;
테이블을 연결하려면 다음과 같이, from 절에 둘 이상의 테이블이 있으면, 그 테이블을 연결하는데 필요한 조건을 포함해야 한다.
select customer.first_name, customer.last_name,
time(rental.rental_date) rental_time
from customer
inner join rental
on customer.customer_id = rental.customer_id
where date(rental.rental_date) = '2005-06-14';
이전 쿼리는 customer 테이블의 열 (first_name, last_name)과 rental 테이블의 열(rental_date) 데이터를 모두 표시하므로 두 테이블이 모두 from 절에 포함된다. 두 테이블을 연결 join 하는 방법은 customer 과 rental 테이블에 모두 저장된 customer_id 이다. 데이터베이스 서버는 customer 테이블의 customer_id 열 값을 사용하여 rental 테이블에서 모든 고객의 대여 내역을 찾도록 지시하는데, 두 테이블의 조인 조건은 from 절의 하위절에 있다. 결합 조건은 on customer.customer_id = rental.customer_id 이다.
테이블의 별칭을 정의할 때 다음과 같은 방법을 사용할 수 있다. 예를 들면 employee.emp_id와 같이 전체 테이블 이름을 사용하거나, 각 테이블의 별칭을 할당하고 쿼리 전체에서 해당 별칭을 사용하는 경우이다.
select c.first_name, c.last_name, time(r.rental_date) rental_time
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14';
from 절에서는 customer 테이블에 c가 할당되고, rental 테이블에 r이 할당되었는데, 이와 같은 별칭은 조인 조건을 정의하는 on 절과, 결과셋에 포함할 열을 지정하는 select 절에서 사용된다.
as 키워드를 사용해서 나타내면 다음과 같다.
select c.first_name, c.last_name, time(r.rental_date) rental_time
from customer as c
inner join rental as r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14';
where 절은 결과셋에 출력되기를 원하지 않는 행을 필터링하는 메커니즘이다. 테이블에서 모든 행을 검색하는 대신에 관심없는 행을 필터링하는 방법이 필요하다. 영화 대여에 관심이 있고 최소 일주일 동안 대여할 수 있는 G등급의 영화에만 관심이 있을 수 있는데, 이러한 기준을 충족하는 영화만 검색하는 where 절은 다음과 같다.
select title
from film
where rating = 'G' and rental_duration >= 7;이 where 절에는 두 개의 필터조건이 포함되었으나 필요에 따라 더 많은 조건을 포함할 수 있는데, 각각의 조건은 and, or 또는 not 과 같은 연산자로 구분된다. where 절에 and 및 or 연산자를 모두 사용할 경우, 조건을 함께 그룹화 하려면 괄호를 사용해야 한다.
select title, rating, rental_duration
from film
where (rating = 'G' and rental_duration >= 7)
or (rating 'PG-13' and rental_duration < 4)이 쿼리는 G등급이면서 7일 이상 대여할 수 있거나, PG-13등급이면서 3일 이하로만 대여할 수 있는 영화만 결과 집합에 포함하도록 저장하는 쿼리이다.
groub by절과 having 절을 사용해보겠다. 데이터에서 결과를 검색하기 전에 데이터베이스 서버가 데이터를 정제하는 흐름을 찾을 수 있다. 데이터를 열 값별로 그룹화하는 group by절이다. 40편 이상의 영화를 대여한 모든 고객을 찾을 때, 서버가 고객별로 모든 대여 내역을 그룹화하고 각 고객의 대여 횟수를 계산한다음 대여 횟수가 40이상인 고객만 반환하도록 지시하는 쿼리를 작성한다. group by를 사용하여 행 그룹을 생성하려면 where 절에서 원시 데이터를 필터링 할 수 있는 having을 사용하여 그룹화된 데이터를 정제한다.
select c.first_name, c.last_name, count(*)
from customer c
inner join rental r
on c.customer_id = r.customer_id
group by c.first_name, c.last_name
having count(*) >= 40;
order by 절을 사용하여 정렬하려면 다음과 같다.
select c.first_name, c.last_name,
time(r.rental_date) rental_time
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14'
order by c.last_name;
성을 기준으로 알파벳순으로 정렬한 것이다. 또한 대규모 고객 목록에는 같은 성을 가진 사람이 여럿 포함되므로 정렬 기준을 확장하여 사람의 이름도 포함할 수 있다. order by 절에서 last_name 열 뒤에 first_name 열을 추가한다.
select c.first_name, c.last_name, time(r.rental_date) rental_time
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14'
order by c.last_name, c.first_name;
내림차순으로 정렬하는 쿼리는 다음과 같다.
select c.first_name, c.last_name
time(r.rental_date) rental_time
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14'
order by time(r.rental_date) desc;
select 절의 열로 설정할 때는 이름 대신 select 절의 열 나열 순서를 기준으로 열을 참조할 수 있다.
order by 절은 select 절의 세번째 요소(열)로 내림차순 정렬을 지정한다.
select c.first_name, c.last_name
time(r.rental_date) rental_time
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14'
order by 3 desc;
time(r.rental_date)가 3번째 열에 속하므로 time을 기준으로 내림차순 정렬이 일어난다.
order by 절의 숫자를 변경하지 않고 select 절에 열을 추가하면 예기치 않은 결과가 발생할 수 있으므로 기능은 자제하여 사용하는 것이 좋다.
3-1) 모든 배우의 배우 id, 이름 및 성을 검색하는데 성기준으로 정렬 후 이름 기준으로 정렬한다.
select actor_id. fname, lname
from actor
order by lname fname;
(order by 3,2;)
3-2)
select actor_id, fname, lname
from actor
where lname ='WILIAMS' or fname = 'DAVIS';
(having)3-3) rental 테이블에서 2005-07-05에 영화를 대여한 고객의 id를 반환하는 쿼리
select r.customer_id
from rental
inner join rental r
on c.customer_id = r.customer_id
while date(r.rental_date) = '2005-07-05'
-----------------------------------------------------
정답
select distinct customer_id
from rental
where date(rental_date) = '2005-07-05';rental.rental_date 열을 사용하고, date() 함수로 시간요소를 무시할 수 있다. 각 고객 id는 하나의 행을 포함한다.
3-4)
select c.email, r.return_date
from customer c
inner join rental r
on c.customer_id = r.customer_id
where date(r.rental_date) = '2005-06-14'
order by 2 desc;
(order by r.return_date;)최신순으로 return_date가 나열되어있어서 select 절의 2번째인 return_date기준으로 내림차순으로 정렬한다.