SQL - DML이란? (쿼리 연습)

SQL (Structured Query Language)

DML 데이터 조작 언어 Data Manipulate Language.

: 구조(definition)안에 쓰여진 데이터를 조작 하는 언어.

SELECT  /  INSERT / UPDATE / DELETE

검색(질의)    등록             갱신          삭제         

문법

SELECT [컬럼명1], [컬럼명2]

FROM [테이블명];

SELECT *(모든 데이터)

FROM country; 

조건 검색: WHERE

SELECT [컬럼명1], [컬럼명2]

FROM [테이블명]

WHERE [컬럼명1] = [값];

SELECT ⇢ 선택.

FROM ⇢ ~테이블로부터

WHERE  ⇢ ~한 조건으로  

some example) 

-- 대륙이 아시아인 국가의 모든 정보를 조회하라.

SELECT * : 모든정보 
FROM country : 국가정보가 있는 테이블 
WHERE continent = 'ASIA'; : 대륙칼럼 값이 Asia

-- 대륙이 아시아이고 지역이 중동인 국가의 모든 정보 조회하라.

SELECT * : 모든정보 
FROM country : 국가정보가 있는 테이블 
WHERE continent = 'Asia'; : 대륙칼럼 값이 아시아.

     AND region = 'Middle east'; 그리고 지역이 중동.

-- 대륙이 아시아이거나 유럽인 국가의 모든 정보 조회.

SELECT * : 모든정보 

FROM country : 국가정보가 있는 테이블 
WHERE continent = 'Asia'; : 대륙칼럼 값이 아시아.

         OR continent = 'Europe'; 그리고 지역이 중동.

--GNP가 10000 이상인 국가의 국가명, 국가코드 조회

SELECT name, code
FROM country
WHERE GNP >= 10000;

-- 독립연도(IndepYear) 값이 없는 국가명, 인구수 조회
SELECT name, population
FROM country
WHERE indepyear IS NULL;

-- 독립한적 없고 기대수명이 70 이상, 국가명,코드,면적,인구수,gnp조회.
SELECT name, code, SurfaceArea, population, GNP
FROM country
WHERE indepyear IS NULL 
AND LifeExpectancy >=70 ;

JOIN-ON 2개 이상의 테이블간의 관계. 

2개 이상의 테이블을 연결하여 데이터를 검색하는 방법

보통 공통된 값인 PK 및 FK 값을 사용하여 조인한다.

조인은 조인 연산자에 따라, From 절의 조인 형태에 따라서 구별한다.

도시정보는 city, 국가정보는 country

-관계 생각

-외래키 찾아본다 (workbench)

외래키 정의에 관계있는 테이블과 칼럼이 있다.

(city - countryCode, country - code)

외래키가 있다면 연결join 시킬수 있다.

SELECT [컬럼명1], [컴럼명2] ...

FROM [테이블명 별칭1]

JOIN [테이블명 별칭2] ON 별칭1.외래키 = 별칭2.기본키

WHERE [조건컬럼]=[값];

--도시 인구수가 100만 이상인 도시명과 국가명

SELECT ci.name , co.name

FROM city ci JOIN country co //관계된 두 데이블 연결.

ON ci.countryCode = co.code // 연결고리.! (외래키)

WHERE ci.population >= 1000000; //도시인구수 

-- 국가 인구수가 1000만 이상인 모든 도시의 정보

SELECT ci.*

FROM city ci JOIN country co

ON ci.countryCode = co.code

WHERE co.population >= 10000000;

FROM [테이블1 별칭1]

JOIN [테이블2 별칭2] ON [테이블1의 외래키] = [테이블2의 기본키]

WHERE [어느테이블의 컬럼이 ~한] ⇢ 조건1

AND [어느테이블의 컬럼이 ~한] ⇢ 조건2

독립한적이 없는 국가 중 도시 인구수가 100만 이상인 도시의 도시명 , 인구수.

SELECT 

FROM country co

JOIN city ci ON co.code = ci.countryCode

WHERE co.IndepYear IS NULL

AND ci.population >= 1000000

유효한 active고객의 고객번호,성명,이멜,연락처,우편번호,주소 조회

SELECT cu.customer_id, cu.first_name, cu.last_name, cu.email, ad.phone, ad.postal_code, ad.address 

FROM customer cu JOIN address ad 

ON cu.address_id = ad.address_id

WHERE cu.active IS TRUE

유효하지않은 고객의 고객번호 성명 이멜 연락처 + 국가명 도시명 우편번호 주소

SELECT cu.customer_id, cu.first_name, cu.last_name, cu.email, ad.phone, ci.city, co.country, ad.postal_code, ad.address 
FROM customer cu JOIN address ad ON cu.address_id = ad.address_id 
JOIN city ci ON ci.city_id= ad.city_id 
JOIN country co ON ci.country_id = co.country_id
WHERE cu.active IS false

영화의 영화id, 분류명, 제목 , 출시년도 조회. 

SELECT film.film_id, category.name, film.title, film.release_year

FROM film film

JOIN film_category film_category ON film.film_id =  film_category.film_id

JOIN category category ON film_category.category_id = category.category_id

영화의 영화id, 분류명, 제목 , 출시년도, 출연배우 조회. 

SELECT film.film_id, category.name, film.title, film.release_year, actor.first_name, actor.last_name

FROM film film

JOIN film_category film_category ON film.film_id =  film_category.film_id

JOIN category category ON film_category.category_id = category.category_id

JOIN film_actor film_actor ON film.film_id = film_actor.film_id

JOIN actor actor ON film_actor.actor_id = actor.actor_id 

고객번호가 3번인 고객의 

대여번ㅇ호 대여일 대여금액 지불날자 조회. 

사용테이블: customer, rental - rental_id, rental_date/ payment- amount, payment_date

SELECT r.rental_id, r.rental_date, p.amount, p.payment_date

FROM customer c

JOIN rental r ON c.customer_id = r.customer_id 

JOIN payment p ON r.rental_id = p.rental_id

WHERE c.customer_id = 3;

고객번호가 3번인 고객의 

이메일 대여번ㅇ호 영화제목 분류명 출시년도 대여일자 반납일자 

table: customer rental inventory film - title, film_category category-name

SELECT c.email, r.rental_id, f.title, f.release_year, r.rental_date, r.return_date

FROM customer c

JOIN rental r ON  c.customer_id = r.customer_+id

JOIN inventory i ON r.inventory_id = i.inventory_id

JOIN film f ON i.film_id = f.film_id

JOIN film_category fc ON f.film_id= fc.film_id

JOIN category ca ON fc.category_id = ca.category_id

WHERE c.customer_id = 3;

고객번호가 27번인 고객의

고객번호, 이메일 국가명 도시명 우편번호 주소 대여번호 영화제목 분류명 대여금액 대여일 

SELECT c.customer_id, co.country, ci.city, ad.postal_code, r.rental_id, f.title, ca.name, p.amount, r.rental_date

FROM customer c

JOIN address ad ON c.address_id = ad.address_id 

JOIN city ci ON ad.city_

id = ci.city_id

JOIN country co ON ci.country_id = co.country_id

JOIN rental r ON c.customer_id = r.customer_id

JOIN payment p ON r.rental_id = p.rental_id

JOIN  inventory i ON r.inventory_id =i.inventory_id

JOIN film f ON i.film_id = f.film_id

JOIN film_category fc ON fc.film_id = f.film_id

JOIN category ca ON fc.category_id = ca.category_id

WHERE c.customer_id = 27;

INNER / OUTER JOIN

FROM 절의 JOIN 형태에 따른 분류 

• INNER JOIN : JOIN 조건에서 값이 일치하는 행만 반환

• OUTER JOIN : JOIN 조건에서 한쪽 값이 없더라도 행을 반환

OUTER JOIN

• 조인 조건을 만족하지 않는 데이터를 처리하기 위한 JOIN으로 INNER JOIN이 두 테이블에 있는 일치하는 값만 가져오는 것에 비해 OUTER JOIN은 어느 한 쪽의 데이터를 모두 가져온다.

• JOIN 조건에 일치하지 않은 값을 추가 할 때 사용한다.

고객 중 대여한 고객의 모든 고객정보와 대여기록

⇢ INNER JOIN

고객중 대여한 고객이 있다면, 고객정보와 대여기록 

                           없다면, 고객 정보만.. 

⇢LEFT OUTER JOIN. 

GROUP BY

•  GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
•  나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가.

개수/합계/평균

SELECT [컬럼들..]

FROM [테이블..]

WHERE[조건들..]

GROUP BY[기준 칼럼]

A.테이블 하나일때

   : 등록된 고객수

         SELECT COUNT(customer_id)

          FROM customer;

: 보유영화개수

SELECT COUNT(film_id)

          FROM film;

B. 테이블이 2개 이상일때 

 : 각 고객이 대여를 위해 결제한 횟수 

SELECT cu.customer_id , COUNT(p.payment_id)

FROM customer cu

INNER JOIN payment p 

ON cu.customer_id = p.customer_id

GROUP BY cu.customer_id;   

국가별 도시수.

SELECT COUNT(ci.city_id)

FROM country co

INNER JOIN city ci ON co.country_id = ci.country_id

GROUP BY co.country_id ;

국가별 고객수.

SELECT COUNT(cu.customer_id)

FROM customer cu

INNER JOIN address ad ON cu.address_id = ad.address_id

INNER JOIN city ci ON ad.city_id = ci.city_id

INNER JOIN country co ON ci.country_id = co.country_id

GROUP BY co.country_id;

국가별 도시수 

SELECT co.name, COUNT(ci.id)

FROM country co 

INNER JOIN city ci ON co.Code =ci.countryCode

GROUP BY co.code;

-대륙별 국가 수 

SELECT continent, COUNT(code)

FROM country

GROUP BY continent;

ORDER BY 정렬.

ORDER BY [칼럼] [정렬방식]

LIMIT [개수]

고객이 지불한 총 대여금액.

SELECT cu.first_name, SUM(p.amount)

FROM customer cu 

JOIN payment p ON cu.customer_id = p.customer_id

GROUP BY cu.customer_id

ORDER BY SUM(p.amount) DESC; 

대여건수가 가장 많은 고객 상위 5명,

SELECT cu.first_name, COUNT(r.rental_id)

FROM customer cu 

JOIN rental r ON cu.customer_id = r.customer_id

GROUP BY cu.customer_id 

ORDER BY COUNT(r.rental_id) desc

LIMIT 5;

연체 기록 있는 고객의 연체 건수(반납일자없음)

 SELECT cu.first_name, COUNT(r.rental_id)
FROM customer cu
JOIN rental r ON cu.customer_id = r.customer_id
WHERE r.rental_date IS NOT NULL AND r.return_date IS NULL
GROUP BY cu.customer_id

각 분류별 영화 수

SELECT ca.name, COUNT(f.film_id)

FROM film f

JOIN film_category fc ON f.film_id = fc.film_id

JOIN category ca ON ca.category_id = fc.category_id

GROUP BY ca.category_id

각 영화 출연 연기자 수 (상위 10개 ) 

SELECT f.title, COUNT(fa.actor_id)

FROM film f

JOIN film_actor fa ON f.film_id = fa.film_id

GROUP BY f.film_id

ORDER BY COUNT(fa.actor_id) desc

LIMIT 10;

SELECT *
FROM attraction;

SELECT * FROM daily_visitor;

SELECT * FROM daily_visitor
WHERE attraction_code=1
AND visit_date < '20100105';

SELECT * FROM daily_visitor 
WHERE SUBSTR(visit_date, 1, 6) = '201001';