MySQL 중급(3)

0. 실습 Url

0.1 Url에서 실습 가능

• https://demo.phpmyadmin.net/master-config/index.php?route=/database/structure&server=2&db=world 에서 실습가능

1. 서브 쿼리(Sub-Query)

1.1 서브 쿼리(Sub-Query)

1
2
3
4
5

SELECT
    (SELECT count(name) FROM City) AS total_city,
    (SELECT count(name) FROM Country) AS total_country,
    (SELECT count(DISTINCT(Language)) FROM CountryLanguage) AS total_language
FROM DUAL

• 서브쿼리는 쿼리문 안에 있는 쿼리를 의미
• SELECT절, FROM절, WHERE절 등에 사용 가능
• 전체 나라수, 전체 도시수, 전체 언어수를 출력하는 코드 (SELECT절에 사용)

1
2
3
4
5
6
7
8
9

SELECT *
FROM
	(SELECT countrycode, name, population
     FROM City
     WHERE population > 8000000) AS City
JOIN
	(SELECT code, name
     FROM Country) AS Country
ON City.CountryCode = Country.Code

• 인구수가 800만 이상인 도시의 국가코드, 국가이름, 인구수를 출력 (FROM절에 사용)

1
2
3
4
5

SELECT code, name, HeadOfState
FROM Country
WHERE code IN(
    SELECT DISTINCT(Countrycode) FROM City WHERE population > 8000000
    )

• 인구수가 800인 도시의 국가 코드, 국가 이름, 대통령 이름을 출력 (WHERE절에 사용)

2. Index

2.1 Index

1
2

CREATE INDEX 인덱스명
ON 테이블명(컬럼1, 컬럼2)

• 테이블에서 데이터를 검색할때 빠르게 찾을수 있도록 해주는 기능
• WHERE 절에 들어가는 컬럼을 INDEX로 설정해 놓으면 설정한 컬럼을 조건으로 검색할때 빠르게 검색 가능
• 자주 검색하는 조건을 INDEX로 설정하면 빠르게 검색 가능
• 너무 많은 INDEX가 설정되면 데이터 입력될때마다 INDEX에 데이터를 넣어야해서 데이터 입력시 속도가 느려질수 있음
• INDEX는 검색 조건으로 자주 사용하는 컬럼에 설정해 놓으면 좋음

1
2

CREATE INDEX population
ON City(population)

• City 테이블에 Population 컬럼을 인덱스로 추가

1
2

DROP INDEX population
ON City

• City 테이블에 Population 컬럼을 인덱스로 제거

2.2 Explain

1
2
3
4

EXPLAIN
SELECT *
FROM City
WHERE population > 1000000

• 쿼리를 실행하기 전에 INDEX로 검색을 하는지 확인 할수있음
• 100만이 넘는 도시의 데이터를 출력의 실행계획을 확인
• Extra 컬럼에 INDEX가 없으면 Using Where로 검색이 되지만 INDEX가 있으면 Using INDEX로 검색됨을 확인할수 있음
• Type 컬럼의 값이 ALL이면 INDEX를 사용하지 않고 있다는 의미

2.2.1 검색절차

• 스토리지(INDEX + Data) -> Rows -> MySQL 엔진(필터링 전) -> Filtered -> MySQL 엔진(필터링 후)

3. View

3.1 View

1
2

CREATE VIEW 뷰이름 AS
쿼리

• 가상 테이블로 특정 데이터만 보고자 할때 사용
• 실제 데이터를 저장하고 있지는 않음
• 특정 컬럼의 데이터를 보여주는 역할만 함
• 뷰를 사용함으로 쿼리를 더 단순하게 만들수 있음
• 한번 생성된 뷰는 수정이 불가능 하며, INDEX설정이 불가능

1
2
3

CREATE VIEW code_name AS
SELECT code, name
FROM Country

• 국가코드와 국가이름이 있는 뷰 생성

1
2
3
4

SELECT *
FROM City
JOIN code_name
ON City.Countrycode = code_name.code

• City 테이블에 국가 이름 추가

1

DROP VIEW 뷰이름

• 뷰 삭제 코드

1

DROP code_name

• code_name 뷰 삭제함