[MySQL] 3. SQL 문법

타입

스터디

태그

MySQL

상태

Published

생성일

2022/08/01 13:53

최종 편집 일시

2023/02/27 04:10

상위 항목

1 more property

ERM(Entity-Relationship Modelling)

ERD(Entity-Relationship Diagram)

JOIN

INNER JOIN

LEFT JOIN (LEFT OUTER JOIN)

RIGHT JOIN (RIGHT OUTER JOIN)

데이터 조회(SELECT)

SELECT 와 함께 쓰이는 절들

FROM

WHERE

GROUP BY

HAVING

ORDER BY

실습

모든 열 SELECT

-- 전체 다 불러오기
SELECT * 
FROM customer;
SQL
복사

총 2661 행

아래 생략

WHERE절로 조건 걸기

-- WHERE절 사용
SELECT * 
FROM customer 
WHERE gender = 'man';
SQL
복사

총 1133행

count() 함수 사용

-- COUNT(mem_no) : mem_no 필드를 가지는 행의 개수 구하기
SELECT COUNT(mem_no) AS 회원수 
FROM customer 
WHERE gender='man';
SQL
복사

count(*) : 검색한 테이블의 모든 행 개수 구하기

count(field) : 특정 field 를 명시해서, 해당 field 값이 NULL 인 행은 제외

일반적으로 NOT NULL 인 id 값을 count 함수 인자로 쓴다.

GROUP BY

SELECT addr, COUNT(*) AS 회원수 
FROM customer 
WHERE gender = 'man' 
GROUP BY addr;
-- GROUP BY를 하면 해당 필드로 그룹핑이 되기 때문에, SELECT 시 해당 필드와 COUNT 를 같이 쓸 수 있다
-- GROUP BY 를 한 필드는 SELECT 에도 써주는 것이 바람직하다.
-- COUNT() 함수 안에 들어오는 컬럼은 GROUP BY 절에 안 써줘도 된다 
-- 즉, mem_no 열 뿐만 아니라 다른 열이 들어와도 결과는 똑같음.
-- 주의할 점은 특정 필드를 명시할 때 NULL 이면 해당 행은 카운팅이 안 됨. 
-- 따라서 NOT NULL 인 필드 혹은 전체필드(*) 를 COUNT 함수의 인자로 넣어줘야 하는데, 
-- 쿼리속도 때문에 NOT NULL 인 필드(ID) 를 보통 넣는다.


-- 에러 발생
SELECT addr,COUNT(mem_no) AS 회원수 
FROM customer 
WHERE gender='man'; 
-- group by 를 안 써주고, count() 와 특정 필드를 같이 select 할 수 없음.
SQL
복사

•

GROUP BY addr; : addr 필드의 값이 같은 행들을 그룹핑한다.

그룹핑한 필드는 SELECT 에 적어주는 경우가 많다.

•

COUNT() AS 회원수 : 
쿼리실행 순서가 FROM → WHERE → GROUP BY 이므로, SELECT 는 맨 마지막에 실행된다.
따라서 GROUP BY 에 의해 새롭게 바뀐 테이블에서의 각 범주(addr)의 행 개수를 구한다.
count(*) 와 count(mem_no) 는 똑같은 기능을 하고, 처리속도가 후자가 더 빠르기 때문에 일반적으로는 후자방식을 택한다.

HAVING

WHERE 은 그룹핑을 하기전에 조건걸어주고 , HAVING 은 그룹핑을 한 후에 조건을 걸어준다.

둘 다 필요한 이유는 그룹핑을 하게 되면, 테이블이 변경되기 때문이다.

-- HAVING절은 GROUP BY 에 의해 만들어진 결과에 대해 조건을 걸 때 쓰임
SELECT addr, COUNT(mem_no) AS 회원수 
FROM customer 
WHERE gender = 'man' 
GROUP BY addr 
HAVING COUNT(mem_no) < 100; -- 그룹핑한 행의 회원수가 100 미만일때만
SQL
복사

ORDER BY

맨 마지막에 작성하고, 정렬 기능으로 사용한다.

디폴트는 오름차순(ASC)이고, 내림차순을 하려면 DESC 를 써준다.

-- ORDER BY : 맨 하단에 작성. 오름차순: ASC / 내람차순 : DESC
SELECT addr, COUNT(mem_no) AS 회원수 
FROM customer 
WHERE gender = 'man' 
GROUP BY addr 
HAVING COUNT(mem_no) <100 
ORDER BY COUNT(mem_no) DESC ;
SQL
복사

GROUP BY 여러 필드

여러 필드를 동시에 그룹핑할 수 있다.

-- GROUP BY 여러 필드
SELECT addr, gender, count(mem_no) AS 회원수
FROM customer
WHERE addr IN ('seoul', 'incheon')
GROUP BY addr, gender
ORDER BY addr DESC;
-- IN 연산자 사용
-- addr, gender 필드 값으로 그룹핑이 됨
SQL
복사

테이블 결합(JOIN)

ERM 과 ERD

ERM(Entity-Relationship Modelling)

개체-관계 모델링

•

개체(Entity) : 하나 이상의 속성(Attribute)으로 구성된 개체

쉽게 생각해서 개체 == 테이블 이고 속성 == 필드 이다.

•

관계(Realtionship) : 개체(테이블)간의 관계

ERD(Entity-Relationship Diagram)

개체 간의 관계를 도표로 표현

FK 는 다른 테이블에서의 PK 를 뜻한다.

PK 는 한 테이블 당 무조건 하나이지만, FK 는 한 테이블에서 여러 개가 될 수 있다.

JOIN

INNER 조인

LEFT OUTER 조인 (LEFT 조인)

RIGHT OUTER 조인

그 외 기타 (많이 안 쓰는 듯)

customer 테이블

sales 테이블

product 테이블

INNER JOIN

ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만을 가져온다.

앞에 INNER 를 생략해도 된다. ( INNER JOIN == JOIN )

첫번째테이블이름
INNER JOIN 두번째테이블이름
ON 조건

-- INNER 생략 가능
첫번째테이블이름
JOIN 두번째테이블이름
ON 조건
SQL
복사

-- customer의 mem_no 와 sales의 mem_no 가 같은 행만 가져옴
SELECT *
FROM customer
INNER JOIN sales
ON customer.mem_no = sales.mem_no;

-- 테이블에 별칭을 달아서 사용하는 것도 가능
SELECT *
FROM customer AS a
INNER JOIN sales AS b
ON a.mem_no = b.mem_no;
SQL
복사

SELECT *
FROM customer
INNER JOIN sales
ON customer.mem_no = sales.mem_no
WHERE customer.mem_no = '1000970';
-- customer 와 sales 테이블 둘 다 mem_no 필드가 있으므로 어떤 테이블의 mem_no 인지 명시해줘야함
SQL
복사

LEFT JOIN (LEFT OUTER JOIN)

JOIN 을 기준으로 왼쪽 테이블은 무조건 다 불러온다.

결합을 할 때 왼쪽테이블을 기준으로

ON 절에 매칭되면, 오른쪽테이블과 결합을 하고 (INNER JOIN 과 동일)

ON 절에 매칭되지 않는 오른쪽 테이블의 필드는 NULL 로 채운다.

-- LEFT JOIN
-- 왼쪽테이블(customer)는 무조건 다 불러오고, 
-- 오른쪽테이블(sales)은 ON 절에서 매칭되는 데이터는 결합을 하고, 매칭이 되지 않으면 NULL 로 결합한다
SELECT *
FROM customer
LEFT JOIN sales
ON customer.mem_no = sales.mem_no;
SQL
복사

왼쪽테이블(customer)은 무조건 다 불러오고, 만약 ON 절에서 매칭이 되지 않으면 오른쪽 테이블의 필드를 다 NULL 로 채우는 것을 확인할 수 있다.

RIGHT JOIN (RIGHT OUTER JOIN)

JOIN 을 기준으로 오른쪽 테이블은 무조건 다 불러온다.

결합을 할 때 오른쪽테이블을 기준으로

ON 절에 매칭되면, 왼쪽테이블과 결합을 하고 (INNER JOIN 과 동일)

ON 절에 매칭되지 않는 왼쪽 테이블의 필드는 NULL 로 채운다.

-- RIGHT JOIN
-- 오른쪽테이블(sales)는 무조건 다 불러오고, 
-- 왼쪽테이블(customer)은 ON 절에서 매칭되는 데이터는 결합을 하고, 매칭이 되지 않으면 NULL 로 결합한다
SELECT *
FROM customer
RIGHT JOIN sales
ON customer.mem_no = sales.mem_no
WHERE customer.mem_no IS NULL;
-- NULL / IS NULL 사용
-- IS NULL 을 사용해서 왼쪽테이블의 필드가 NULL 인 행만 가져옴
SQL
복사

테이블 3개 join

/* 테이블 3개 join */
SELECT *
FROM sales
LEFT JOIN customer
ON sales.mem_no = customer.mem_no
LEFT JOIN product
ON sales.product_code = product.product_code;
SQL
복사

sales 테이블을 기준으로 customer 와 product 테이블을 left join 했다.

서브쿼리 (Sub Query)

메인쿼리 안에 있는 또 다른 쿼리를 서브쿼리라고 한다.

모든 서브쿼리는 SELECT 명령문으로 시작한다. (모든 쿼리문은 다 SELECT로 시작)

SELECT절 서브쿼리 : 테이블 필드(열) 구하기

FROM절 서브쿼리 : 테이블 구하기(열 및 테이블 이름도 지정)

WHERE절 서브쿼리 : 리스트 구하기

SELECT절 서브쿼리

select절 서브쿼리 : 필드(열) 구하기

SELECT절 안에서 또 다른 SELECT절을 사용한다.

JOIN 을 사용하지 않고 JOIN 을 흉내낼 수 있는데, 성능이 안 좋아서 JOIN 을 쓰는 것이 더 낫다.

SELECT 서브쿼리사용

-- SELECT절 서브쿼리
SELECT *, 
    (SELECT gender 
    FROM customer 
    WHERE sales.mem_no = mem_no) AS gender
FROM sales;
SQL
복사

234ms

join 사용

-- SELECT절 서브쿼리 vs. JOIN 처리 속도 비교
SELECT sales.*, customer.gender
FROM sales
LEFT JOIN customer
ON sales.mem_no = customer.mem_no;
SQL
복사

4.7ms

join 이 select 서브쿼리보다 무려 50배가량 빠르다

FROM절 서브쿼리

from절 서브쿼리 : 열 이름 및 테이블명(별칭) 지정

회원 당 주문횟수 구하기

-- FROM절 괄호 안에는 테이블이 와야함 
-- -> GROUP BY 절로 새로 생성된 테이블을 사용가능
SELECT *
FROM (
        SELECT mem_no, count(order_no) AS 주문횟수
        FROM sales
        GROUP BY mem_no
    ) AS A;
-- FROM절 서브쿼리를 쓸 때에는 마지막에 alias(별칭)을 꼭 붙혀줘야함.
SQL
복사

WHERE절 서브쿼리

where절 서브쿼리 : 리스트 형태 반환

-- WHERE절 서브쿼리
-- 가입년도가 2019년인 회원의 주문횟수를 구하는 쿼리문
SELECT count(order_no) AS 주문횟수
FROM sales
WHERE mem_no IN (
                SELECT mem_no
                FROM customer
                WHERE YEAR(join_date) = 2019);
SQL
복사

IN 다음에는 리스트가 와야한다.

SELECT mem_no FROM customer WHERE YEAR(join_date) = 2019 결과는 (’100001’, ‘100002’, ‘100003’, ..) 와 같은 리스트 형태이다.

join으로 똑같이 구현한 버전

-- join 버전
SELECT count(order_no) AS 주문횟수
FROM sales
JOIN customer
ON sales.mem_no = customer.mem_no
WHERE YEAR(customer.join_date) = 2019;
SQL
복사

서브쿼리 정리

select,from,where 서브쿼리 중 가장 많이 사용하는 서브쿼리는 from절 서브쿼리 이다.

회원 당 구매횟수 및 회원정보를 구하는 쿼리문 (from서브쿼리 + join)

-- from서브쿼리 와 join 결합
CREATE TEMPORARY TABLE sales_sub_query
SELECT A.구매횟수, B.*
FROM (
        SELECT mem_no
                ,count(order_no) AS 구매횟수
        FROM sales
        GROUP BY mem_no            
    ) AS A -- 회원 당 구매횟수 테이블 생성
JOIN customer AS B
ON A.mem_no = B.mem_no;

SELECT * FROM sales_sub_query;
SQL
복사

cf. CREATE TEMPORARY TABLE

방법1
: 일반적인 create table 방식

CREATE TEMPORARY TABLE table_name( 
        column_1_definition,
        column_2_definition,
        ...,
        table_constraints
    );
SQL
복사

방법2
: select 쿼리문 사용 (select 로 만들어진 결과를 그대로 임시테이블로 생성)

CREATE TEMPORARY TABLE top_costomers
SELECT p.customerNumber,
        c.customerName, 
        ROUND(SUM(p.aount), 2) sales
FROM payments p
INNETER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;
SQL
복사