MySQL 핵심 키워드 [ 1 ]

MySQL에서 쓰이는 query문의 키워드들을 하나하나 정리하고 넘어가는 글이다.

FROM

검색할 테이블을 선택할 때 이용한다. employee 테이블에서 정보를 검색해야한다면,

FROM employee

SELECT

SELECT 선언은 테이블에서 한개 혹은 이상의 데이터를 읽수 있게 해준다.

SELECT name, age
FROM employee;

SELECT *

*표시는 테이블의 모든 필드를 검색할 때 이용한다.

주의 사항

• SELECT *는 지금상황에 필요하지 않은 콜럼들도 가져오기때문에 불필요한 I/O를 줄여줍니다.
• 로 설정해두는 것보다 어떤 데이터들을 읽어오는지 하나하나 적어두는 것이 코드를 읽을 때 명료해집니다. 다른 사람이 query문을 볼때도 예상가능해지죠.
• 테이블의 컬럼들은 한번 정해지면 쭉 유지되는 것이 아닙니다. 삭제되거나 추가될수 있습니다. SELECT *는 필드가 바뀔때마다 query문의 결과도 달라지기때문에 지양해야할 선언문입니다.
• 모든 필드를 가져오기 때문에 권한이 없는 유저에게 민감한 데이터를 노출시킬수도 있습니다.

ORDER BY

SELECT 선언문으로 query를 한 결과값은 정렬되어있지 않은 값입니다. 그 결과값을 우리가 원하는 순서대로 정렬하기 위해 ORDER BY를 이용합니다.

SELECT
	name, age
FROM
	employee
ORDER BY
	age;

NOTE! ORDER BY는 기본값이 ASC 오름차순입니다.

FIELD()

특정 값들의 순서대로 정렬 가능합니다. status라는 column이 있고 그 값들이 몇가지로 제한된 값이라면 (ex. ‘Before Start’, ‘In Progress’, ‘Finished’, ‘Canceled’) 아래 처럼 정렬 가능합니다.

SELECT
	name, age
FROM
	employee
ORDER BY
	FIELD(status,
		'In Progress', 
		'Before Start',
		'Calceled',
		'Finished'
	)

위 query문은 FIELD안에 적혀있는 순서대로 정렬됩니다.

WHERE

filtering하는 검색 조건은 WHERE에 넣습니다.

# Syntax
SELECT 
    select_list
FROM
    table_name
WHERE
    search_condition;

검색되는 순서는 ‘FROM’ => ‘WHERE’ => ‘SELECT’ => ‘ORDER BY’ 입니다.

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    job = 'Student';

AND, OR with WHERE

여러가지 검색 조건이 필요할 때는 AND를 이용합니다.

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    job = 'Student' AND    // AND
    age > 20;
SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    job = 'Student' OR
    age = 20;

BETWEEN

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    age BETWEEN 20 AND 30;

CAST

데이트 값을 비교할때는 타입변환이 필요하므로 BETWEEN에서 이용할때면 CAST를 이용합니다.

SELECT 
   name,
   age
FROM 
   employees
WHERE 
   requireddate BETWEEN 
     CAST('2003-01-01' AS DATE) AND 
     CAST('2003-01-31' AS DATE);

LIKE

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    name LIKE '%kim';

이름에 kim이 들어가는 모든 값을 반환합니다.

IN

여러개의 값중 하나라도 일치하는 값들이 필요할 때 이용합니다.

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    job IN ('Student', 'Developer', 'Cook');

직업이 ‘Student’, ‘Developer’, ‘Cook’인 결과를 리턴받습니다.

NOT IN

NOT IN은 어떠한 값도 IN의 값이 포함되지 않은 row들만 반환합니다.

SELECT 
    name,
    age, 
    job
FROM
    employees
WHERE
    job IN ('Student', 'Developer');

Student도 아니고 Developer도 아닌 값을 반환합니다.

IN NULL

값이 NULL이거나 없어야 합니다. 주의해야 할점은 값이 0이거나 ''(empty string)일때를 나타내지않습니다.

‘<>’’ 는 ‘!=’ 와 같습니다.

DISTINCT

같은 값은 한개만 리턴하도록 해주는 선언문입니다. SELECT문 바로 뒤에 명시됩니다.

SELECT 
	DISTINCT name
FROM
    employees;

LIMIT

검색의 갯수를 제한할때 이용합니다. 검색 도중에 5개가 차면 검색을 멈추게 됩니다.

# Syntax
SELECT
	select_list
FROM
	table_name
LIMIT
	[offset,] row_count;

offest만큼 먼저 나온 값의 갯수를 생랼하고 row_count 갯수만큼의 값을 반환합니다.

SELECT 
    name,
    age, 
    job
FROM
    employees
LIMIT
	5;

5개의 결과가 찾아지면 검색이 끝나고 값이 반환됩니다.

Alias — AS

column을 위한 AS

query 결과값을 읽기 편하게 하기 위해 column에 별명을 지을 수 있습니다. AS 키워드를 이용하면 됩니다. AS를 명시하지 않아도 별명이 잘작동합니다.

SELECT
	CONCAT_WS(', ', lastName, firstName) AS 'Full Name'
	# CONCAT_WS(', ', lastName, firstName) 'Full Name' 
FROM
	employees;

성과 이름을 붙이기 위해 CONCAT_WS함수를 이용하였고 결과값 column의 별명을 Full Name으로 성정하였습니다.

# 추가 예제.
SELECT
	orderNumber `Order no.`,
	SUM(priceEach * quantityOrdered) total
FROM
	orderDetails
GROUP BY
	`Order no.`
HAVING
	total > 60000;

table을 위한 AS

테이블또한 AS로 별명을 지을 수 있습니다.

SELECT * FROM employees e;

FROM에서 별명을 설정해놓으면 다른 query 선언문에서도 그 별명을 사용할 수 있습니다.

SELECT 
    e.firstName, 
    e.lastName
FROM
    employees e
    # employees AS e
ORDER BY e.firstName;

JOIN

관계형 데이터베이스는 관련된 테이블들을 foreign key를 이용하여 연결함으로써 구성됩니다. 그렇기 때문에 데이터베이스에서는 JOIN을 이용하여 여러 테이블로부터 필요한 데이터를 받아옵니다.

Inner Join

Inner Join은 교집합을 찾는 것이라고 생각하면 됩니다.
FROM에 명시되는 테이블과 INNER JOIN에 명시되는 테이블에 동시에 충족되는 조건에 맞는 결과값이 리턴되는 것입니다.

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
INNER JOIN committees c
	ON c.name = m.name;

위 query는 members의 name column과 committees의 name column의 값이 같은 것들을 반환합니다.

LEFT JOIN

LEFT JOIN은 FROM에 명시되는 테이블을 위주로 진행하는 JOIN입니다.
FROM에 명시된 테이블에서 SELECT를 진행하되, FROM과 LEFT JOIN에 명시된 테이블들이 ON에 명시된 조건을 통과한다면 LEFT JOIN에 명시된 테이블에 포함된 column들까지도 반환합니다.

SELECT 
    m.member_id, 
    m.name member, 
    c.committee_id, 
    c.name committee
FROM
    members m
LEFT JOIN committees c
	ON c.name = m.name;

INNER JOIN은 조건식이 맞는 교집합만을 반환했지만 LEFT JOIN은 교집합뿐만아니라 조건식을 통과하지 못한 left 테이블(FROM에 명시된 테이블)의 값까지도 반환합니다.

*RIGHT JOIN은 LEFT의 반대로 생각하면 됩니다.

SELF JOIN

다른 테이블끼리 조인하는 것과 같이 하나의 테이블을 스스로 조인할수도 있다. 다만 중요한 것은 테이블 별명을 이용해야한다.
! SELF JOIN을 이용하기 위해서는 테이블 별명을 아용해야만합니다. 왜냐하면 한번의 쿼리에서 같은 테이블을 두번 검색하면 안되기때문입니다. 테이블 별명없이 한번의 쿼리에서 두번이상 테이블을 명세하는 것은 에러를 발생시킵니다.

# 예시 1
SELECT 
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(e.lastName, ', ', e.firstName) AS 'Direct report'
FROM
    employees e
INNER JOIN employees m ON 
    m.employeeNumber = e.reportsTo
ORDER BY 
    Manager;
# 예시 2
SELECT 
    c1.city, 
    c1.customerName, 
    c2.customerName
FROM
    customers c1
INNER JOIN customers c2 ON 
    c1.city = c2.city
    AND c1.customername > c2.customerName
ORDER BY 
    c1.city;

GROUP BY

GROUP BY 키워드에 입력된 필드, 혹은 표현식에 의해 그룹화되어서 반환됩니다. 각 그룹은 오직 하나의 행으로 반환됩니다. 보통 +, -, * 와 같은 연산자들과 같이 이용됩니다. 같은 종류의 속성을 가진 사람이 몇명있는지와 같은 것을 확인할때 주로 사용합니다. ex) 같은 직업을 가진 사람이 몇명있는지.

#Syntax
SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;
TABLE persons
	name
	age
	job
	height

위 테이블이 있을 때 직업에 따라 몇명이 있는지 계산할 때는 아래처럼 query문을 작성합니다.

SELECT 
    job, COUNT(*)
FROM
    persons
GROUP BY job;

GROUP BY DESC, ASC

GROUP BY 에 오름차순 내림차순을 설정힐수 있습니다.

HAVING

filtering 할때 쓸수 있는 또다른 도구입니다. 주로 GROUP BY와 함께 쓰이며 WHERE처럼 작동합니다.

SELECT 
    job, 
    COUNT(*),
FROM
    persons
GROUP BY 
	job;
HAVING
	COUNT > 10 // 10명 이상인 직업만 리턴합니다.