이것이 MySQL이다. [Chapter 6 - SQL 기본(SELECT문, SQL문, CTE)]

6.1 SELECT 구문

6.1.1 원하는 데이터를 가져와주는 기본적인 <SELECT...FROM>

SELECT 문은 가장 많이 사용하는 구문으로 데이터베이스 내의 테이블에서 원하는 정보를 추출하는 명령이다.

SELECT문은 다양한 옵션으로 인해 구문 형식이 복잡해보이지만 많이 사용되는 형태로 요약한 구조는 다음과 같다.

 

 

USE 구문

SELECT문을 학습하기 위해서는 가장 먼저 사용할 데이터베이스를 지정해야 한다.

이렇게 지정된 DB는 다른 DB를 사용하겠다고 명시하지 않은 이상 모든 SQL문은 USE 구문에 의해 사용된 DB에서 수행된다.

 

 

SELECT와 FROM

원래 테이블의 전체 이름은 '데이터베이스 이름.테이블 이름'형식으로 표현된다.

하지만 선택된 데이터베이스 이름이 자동으로 붙게되어 생략이 가능한 것이다.

 

 

특정 열만 조회도 가능하다.

 

 

주석

주석은 주로 코드에 설명을 달거나 해당 부분의 실행을 막고 싶을 때 사용한다. 

한 줄 주석 : -- , -- 뒤에 바로 붙여서 쓰면 안되며 공백이 하나 이상 있어야 한다.

여러 줄 주석 : /**/  

 

 

열 이름의 별칭

열 이름을 별도의 별칭으로 지정할 수 있다.

AS를 통해 별칭을 지정한다. 공백이 있을 경우 작은 따옴표를 사용한다.

 

 

6.1.2 특정한 조건의 데이터만 조회하는 <SELECT...FROM...WHERE>

user 테이블 전체 조회

기본적인 WHERE절

WHERE절은 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용한다.

WHERE 절에서 이름이 LEE인 것만 조회하였기 때문에 위와 같은 결과가 나온다.

 

 

BETWEEN...AND와 IN() 그리고 LIKE

BETWEEN...AND

특정 범위의 데이터를 출력할 수 있다.

연속된 값일 경우 가능하다. 

22부터 24까지 나이를 가진 사람을 모두 조회하는 쿼리이다.

 

 

IN

IN은 연속적인 값이 아닌 이산적인 값을 조회할 때 사용가능하다.

haho와 park의 이름을 가진 사람을 모두 조회하는 쿼리이다.

 

 

LIKE

문자열의 내용을 검색하기 위해서 LIKE 연산자를 사용할 수 있다.

%는 무엇이든 허용한다는 뜻이다.

_는 글자와 매치하기 위해 사용한다.

이름에 a가 들어가는 사람을 모두 조회하는 쿼리이다.

 

 

ANY/ALL/SOME 그리고 서브쿼리

서브쿼리란 쿼리문 안에 또 쿼리문이 들어 있는 것을 의미한다.

서브쿼리 시 여러 개의 열이 아닌 단일 열을 반환해야 한다.

이름이 haho인 사람보다 나이가 더 많은 사람을 모두 조회하는 쿼리이다.

 

ANY

쿼리의 결과 중 하나라도 조건을 만족하는 경우 참을 반환한다.

이름이 LEE인 사람보다 나이가 더 많은 사람을 모두 조회하는 쿼리이다.

하위 쿼리만 실행하면 22, 32가 출력되어 22보다 큰 age와 32보다 큰 age를 출력한다.

 

 

ALL

쿼리의 결과가 모든 조건을 만족하는 경우 참을 반환한다.

이름이 LEE인 사람보다 나이가 더 많은 사람을 조회하는 쿼리이다.

하위 쿼리는 22, 32라는 값이 나오지만 32보다 큰 값은 없기에 아무것도 나오지 않는다.

 

 

SOME

쿼리의 결과가 하나라도 만족하면 참을 반환 ANY와 동일하다.

 

 

원하는 순서대로 정렬하여 출력: ORDER BY

ORDER BY절은 결과물에 대해 영향을 미치지 않고 결과가 출력되는 순서를 조절하는 구문이다.

기본적으로 오름차순(ASC)으로 정렬되며 내림차순는 DESC를 사용한다.

나이순대로 오름차순 정렬하였다.

 

 

중복된 것은 하나만 남기는 DISTINCT

중복된 것은 1개씩만 보여주며 제거한다.

DISTINCT 사용 전

DISTINCT 사용 후

이름 LEE의 중복이 제거되었다.

 

 

출력하는 개수를 제한하는 LIMIT

결과 값의 행의 수를 제한하는데 사용된다.

[LIMIT 시작, 개수] 또는 [LIMIT 개수 OFFSET 시작 형식]으로 사용 가능하다.

2개의 행을 건너 뛰고 3개의 행을 조회한다.

 

 

테이블을 복사하는 CREATE TABLE...SELECT

이 구문은 테이블을 복사해서 새로운 테이블을 생성할 때 사용할 때 주로 사용된다.

 

test란 테이블에 user테이블에 있는 id와 name 컬럼을 복사한다.

해당 test를 조회하면 복사가 잘 되어 있는 것을 확인할 수 있다.

 

 

6.1.3 GROUP BY 및 HAVING 그리고 집계 함수

GROUP BY절

특정 열의 값에 따라 결과를 그룹화하는 데 사용된다.

집계 함수는 GROUP BY 절과 함께 쓰이며 데이터를 그룹하는 기능을 한다.

users 테이블

 

집계함수 

SUM 함수는 합을, MIN는 최소 값을, MAX는 최대값을, AVG는 평균 값을, COUNT는 개수를 출력한다.

 

 

Having 절

앞에서 했던 sum

GROUP BY 절과 함게 사용되어 그룹하된 결과 집합에 대한 조건을 지정하는데 사용한다.

WHERE 절과 유사하지만 그룹에 대한 조건을 지정한다는 점에서 차이가 있다.

HAVING 절을 통해 amount의 합계가 3보다 큰 사람의 이름과 amount의 합계를 조회한다.

 

 

ROLLUP

그룹된 결과 집합을 계층적으로 표시하는데 사용되는 연산자로 여러 수준의 합계를 한번에 계산한다.

총합 또는 중간 합계가 필요할 때 사용한다.

 

 

 

6.1.4 SQL 분류

SQL 문은 크게 DML, DDL DCL로 분류한다.

 

 

DML

DML은 데이터를 조작하는데 사용하는 언어이다.

DML 구문이 사용되는 대상은 테이블 행이다.

SQL문 중 SELECT, INSERT, UPDATE, DELETE가 이 구문에 해당한다. 또 트랜잭션이 발생하는 SQL도 이 DML이다.

 

트랜젝션이란 테이블의 데이터를 변경할 때 실제 테이블에 적용하지 않고 임시로 적용시키는 것을 말한다.

 

 

DDL

데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성, 삭제, 변경하는 역할을 한다.

CREATE, DROP, ALTER 등이 해당한다.

DDL은 트랜잭션을 발생시키지 않는다. 따라서 되돌림이나 완전 적용을 시킬 수 없다.

 

 

DCL

사용자에게 어떤 권한을 부여하거나 뺏을 때 사용하는 구문이다.

GRANT, REVOKE, DENY 등이 해당한다.

 

 

 

6.2 데이터의 변경을 위한 SQL문

INSERT문 기본

INSERT는 테이블에 데이터를 삽입하는 명령이다.

 

테이블 이름 다음에 나오는 열은 생략 가능하다.

생략할 경우 VALUE 나오는 값들의 순서 및 개수가 정의된 열 순서 및 개수와 동일해야 한다.

생략하고 아무 값도 안넣은 곳은 NULL이 들어간다. (제약 조건 NOT NULL일 경우 아무 값도 안들어감)

 

 

자동으로 증가하는 AUTO_INCREMENT

AUTO_INCREMENT는 자동으로 1부터 증가하는 값을 입력해준다.

AUTO_INCREMENT를 지정할 때는 꼭 PRIMARY KEY 또는 UNIQUE로 지정해 줘야 하며 데이터 형은 숫자 형식만 사용할 수 있다.

AUTO_INCREMENT로 지정된 열은 INSERT 문에서 NULL 값으로 지정하면 자동으로 값이 들어간다.

 

 

AUTO_INCREMENT 입력 값 변경하기

 

 

AUTO_INCREMENT 증가값 지정하기

 

 

대량의 샘플 데이터 생성

INSERT INTO ... SELECT 구문을 사용하여 다른 테이블의 데이터를 가져와서 대량으로 입력하는 효과를 낸다.

INSERT문과 SELECT 문의 열 개수는 일치해야 한다.

 

 

 

6.2.2 데이터 수정 : UPDATE

데이터베이스 테이블 내의 기존 레코드의 값을 수정하는데 사용된다.

즉, 특정 조건을 만족하는 레코드 값을 업데이트 하거나 변경할 때 사용.

 

WHERE 절은 생략 가능하지만 생략하면 테이블 전체 행이 변경된다.

 

 

 

6.2.3 데이터 삭제 : DELETE FROM

데이터베이스 테이블에서 하나 이상의 레코드를 삭제하는데 사용된다.

즉, 특정 조건을 만족하는 행을 삭제할 때 사용.

DELETE는 데이터를 삭제하고 테이블 구조를 유지하지만 DROP은 객체를 완전히 제거하여 테이블 구조와 데이터 모두 삭제한다.

 

만약 WHERE이 생략되면 전체 데이터를 삭제한다.

 

 

6.2.4 조건부 데이터 입력, 변경

INSERT IGNORE

새로운 레코드를 삽입하려고 할 때, 레코드가 이미 테이블에 존재하는 경우에는 무시하고 삽입을 시도하지 않는 것을 의미한다.

중복된 행이 테이블에 삽입되는 것을 방지할 수 있다.

users 테이블

 

 

기본 키인 id가 1인 값은 중복되어 있기 때문에 insert가 되지 않는다. 

 

오류 때문에 데이터가 들어가지는 않지만 무시하고 넘어간다.

 

이를 통해 IGNORE를 사용하면 오류를 발생시키지 않고, 무시하고 추가할 수 있다는 것을 확인할 수 있다.

 

 

ON DUPLICATE KEY UPDATE

새로운 레코드를 삽입하려고 할 때, 테이블에 이미 동일한 기본 키, 유니크 제약 조건을 가진 레코드가 있는 경우 해당 행을 업데이트 하거나 변경한다.

id 1이 존재한다면 해당 컬럼의 name과 age를 각각 aaaa와 55으로 바꾼다.

즉, 중복 문제를 해결할 수 있다.

 

 

6.3 WITH절과 CTE

6.3.1 WITH절과 CTE 개요

WITH 절은 CTE를 표현하기 위한 구문으로 MySQL 8.0 이후부터 사용 가능하다.

WITH 구문은 주로 긴 쿼리를 분한하거나 하위 쿼리의 결과를 재사용할 때 사용한다.

 

CTE는 기존의 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며 더 간결한 식으로 보여지는 장점이 있다.

CTE를 사용하면 반복되는 부분을 제거하고 복잡한 쿼리의 가독성을 향상시킨다.

CTE는 비재귀적 CTE와 재귀적 CTE가 있다.

 

 

6.3.2 비재귀적 CTE

말 그대로 재귀적이지 않는 CTE이다.

단순한 형태이며 복잡한 쿼리문장을 단순화시키는데 적합하게사용될 수 있다.

비재귀적 CTE의 형식

기존 테이블은 db에 있는 테이블을 사용하지만 CTE는 바로 위에 WITH절에서 정의한 CTE 테이블 이름을 사용한다.

 

 

위 테이블은 기본적인 이름과 총 양을 조회하여 나온 것이다.

만약 위에 조건에서 양이 많은 순서대로 조회하고 싶다면? ORDER BY을 사용해도 되지만 SQL 문이 복잡해진다.

이 때 CTE를 사용한다.

 

 

즉 WITH.. AS를 통해 CTE 테이블 이름과 들어갈 컬럼과 데이터를 만들고

그 아래 SELECT 문을 통해 구문을 단순화 하여 조회하는 것이다.

 

CTE는 임시 결과를 생성하고 특정 쿼리에서만 사용되는 반면 뷰는 데이터베이스에 저장되어 재사용 가능하고 데이터베이스의 구조를 논리적을 분리하여 관리하는 데 사용된다.