[Lecture Note] MySQL 02. query

이것이 MySQL이다 강의를 참고하여 정리한 글임을 밝힙니다.

Query 문

관습적으로 예약어는 대문자로 작성하며 정의어는 소문자로 작성하여 구분합니다.

*는 주로 모든 열을 나타낼 때 사용합니다.

CREATE

CREATE TABLE table_name (col_name, data_type, etc)

이 때 etc에 PK 등을 알려줄 수 있으며 AUTO_INCREMENT를 입력한 경우 INSERT시 NULL 값을 임의로 증가하는 방향으로 처리하게 됩니다.

이 때 AUTO_INCREMENT = value를 한다면 시작점을 지정할 수 있습니다.

또한 증가하는 간격을 조정하기 위해선

SET @@auto_increment_increment = value;를 통해 수정할 수 있습니다 .

SELECT

SELECT 문의 구조는 다음과 같습니다.

SELECT expr [FROM table_references] [WHERE condition] [GROUP BY (col_name | expr | position)] [HAVING condition] [ORDER BY (col_name | expr | position)]

단 table_reference의 경우 앞서 USE 데이터 베이스를 하지 않았다면 SELECT * FROM [DB].[TABLE] 형식으로 사용하여도 된다.

WHERE

WHERE 뒤에 오는 조건식에는 다음과 같은 표현들이 들어옵니다.

1. 일반적인 AND, OR, 부등호 식
   • WHERE expr [>,>=.<,<=,==] [AND, OR] […]
2. BETWEEN AND
   • WHERE expr BETWEEN valeu AND value
3. IN()
   • WHERE expr IN(value1, value2, … )
4. LIKE
   • WHERE expr LIKE ‘commn_expr%’

     • WHERE name LIKE ‘김%’ : 성이 김인 사람들을 다 불러 오게 됩니다. 즉 %는 글자 수와 제약받지 않고 다 가져옵니다. 물론 순서는 무관합니다.

   • WHERE expr LIKE ‘_commn_expr’

     • WHERE name LIKE ‘_하나’ : 이름이 ‘하나’인 사람들을 다 불러오게 됩니다. 이 때 _는 한글자만 뜻하게 됩니다.

ORDER BY

ORDER BY 뒤에 col_reference만 오는 경우 해당 열의 값으로 정렬하게 됩니다. 이 때 오름차순이 기봇 세팅입니다.

ORDER BY 뒤에 col_reference

DESC로 할 경우 내림차순으로 나옵니다.

col_reference는 여러개가 올 수 있고 순서대로 먼저 정리하고 그 안에서 정리하는 식으로 정리되지만 지양하길 바랍니다.

INSERT

INSERT [INTO] table(col,...) VALUES (value1,...)

해당 테이블의 column 순서대로 값을 입력하게 됩니다.

특정 열을 입력한다면 tuble형태로 순서를 맞춰서 넣으면 됩니다.

INSERT [INTO] table_name SELECT ~

를 사용할 경우 SELECT 의 결과를 바로 table_name으로 입력할 수 있게 됩니다.

UPDATE

UPDATE table_name SET col1 = val1, ... 

DELETE FROM

DELETE FROM table_name [WHERE condition] [LIMIT value]

LIMIT을 사용하는 경우 상위 value개 만큼만 삭제하게 됩니다.

서브 쿼리 (Sub Query)

한 쿼리 문 안에 다른 쿼리문이 있는 것으로, 주로 WHERE문에 condition을 작성할 때 값을 불러오는 경우에 해당합니다.

SELECT expr FROM table_reference WHERE expr > (SELECT sub_expr FROM sub_table_reference [WHERE ~])

• IN / NOT IN

첫번째 쿼리 결과에서 서브 쿼리의 결과를 제외할 때

SELECT expr FROM table_reference WHERE expr NOT IN(SELECT sub_expr FROM sub_table_reference [WHERE ~])

첫번째 쿼리 결과에서 서브 쿼리의 결과만 사용할 때

SELECT expr FROM table_reference WHERE expr IN(SELECT sub_expr FROM sub_table_reference [WHERE ~])

• 서브 쿼리에서 WHERE 문에서 값을 리턴할 때 여러개의 값이 나오는 경우

이런 경우 에러가 나오기에 다음과 같은 방식으로 작성하여 에러를 피해줍니다.

WHERE expr > ANY (SELECT ~)의 

• CREAT TABLE 과 함께 쓸 경우

테이블을 복사하게 되며 이 때 PK와 FK는 복사되지 않습니다.

CREATE TABLE new_table (SELECT ~)

• 예시 1

결과 값의 한 열을 CASE~WHEN 구문으로 구성한 것이고 이것을 ‘고객 등급’으로 형변환 한 것입니다. 특히 쿼리문 중간에 들어가 서브쿼리로 사용될 수 있습니다.

SELECT U.userID, U.name, SUM(price*amount) AS '총구매액',
	CASE
		WHEN (SUM(price*amount) >= 1500) THEN '최우수고객'
        WHEN (SUM(price*amount) >= 1000) THEN '우수고객'
        WHEN (SUM(price*amount) >= 1) THEN '일반고객'
        ELSE '유령고객'
	END AS '고객등급'
	FROM buytbl B
		RIGHT OUTER JOIN usertbl U
			ON B.userID = U.userID
    GROUP BY U.userID, U.name
    ORDER BY SUM(price*amount) DESC;

WITH CTE

CTE(Common Table Expression)은 크게 비재귀적 CTE와 재귀적 CTE로 있으며 비재귀적 CTE는 다음과 같습니다.

WITH CTE table_name(col_reference)
AS
(쿼리문)

이렇게 한다면 table_name을 일종의 테이블처럼 테이블의 결과는 (쿼리문)의 결과값을 사용하게 됩니다. 이 때 CTE의 열 이름은 (col_rerence)를 따르며 쿼리문과 다를 수도 있습니다.