[mysql] DQL

DQL(Data Query Language)

DQL이란?

• DQL(Data Query Language)이란 테이블에 저장된 데이터를 조회(검색)하는데 사용되는 SQL문을 의미
  • Query의 사전적인 의미 ⇒ 묻다 질문하다
  • 데이터베이스라는 상황에 빗대어 볼 때, 데베 서버에 데이터 딸라고 요청하는 것
• 대표적인 DQL은 SELECT : ****테이블에 저장된 데이터를 가져오는 핵심 쿼리문
  • INSERT나 UPDATE 같은 작업과 비교해 봤을 때, 어플리케이션에서 사용되는 쿼리 중에서도 가장 비율이 높고, 성장 이슈 가능성
  • INSERT나 UPDATE 구문은 대부분 행 단위로 처리되지만, SELECT 구문은 여러개의 테이블로부터 데이터를 조합해서 빠르게 가져와야되기 때문에 여러개의 테이블들을 어떻게 읽을 것인가에 따라서 성능이 크게 차이

MySQL의 SELECT 문법 표기

• SELECT 문은 테이블에 저장된 데이터를 꺼내오는 핵심 쿼리문
• SELECT 문법의 키워드와 표현식을 조합하여 복잡하고 다양한 데이터 조회 가능

SELECT
		[ALL | DISTINCT | DISTINCTROW ]
		select+expr [, select_expr] ...
		[FROME table_references]
		[WHERE where_condition]
		[GROUP BY {col_name | expr | position}, ...[WITH ROLLUP]]
		[HAVING where_condition]
		[ORDER BY {col_name | expr | position}
			[ASC | DESC], ... [WITH ROLLUP]]
		[LIMIT {[offset,] row_count | row_count OFFSET offset}]

• [ ] : 해당 키워드나 표현식 자체가 선택사항
• 파이프 | : 해당 키워드나 표현식 중 단 하나만 선택해서 사용할 수 있다.
• {} : 중괄호 내 아이템들 중 반드시 하나를 사용해야 하는 필수 사항임을 의미
• … : 앞에 명시된 키워드나 표현식의 조합이 다시 반복될 수 있음을 의미

<aside> 💡 모든 RDBMS가 DQL을 이용해서 데이터 작업을 수행하지만, RDBMS의 종류와 각 버전에 따라서 DQL 문법이 상이할 수 있기 때문에, 반드시 공식 문서에서 제공하는 메뉴얼에서 SQL 문법을 참조하는 습관을 갖는 것이 중요 +추가 : 자료를 검색할 때, DQL을 DML로 분류해서 설명하는 자료도 있으니 참고하세요옹

</aside>

테스트 데이터 구축

users 테이블과 1대 다 관계를 가진 게시물 테이블 정보

mysql> INSERT INTO users (name, email, password, age) VALUES ("Rebekah Johnson", "Glover12345@email.com", "password", 30);

mysql> INSERT INTO posts (title, content, user_id) VALUES ("위코드 1일차", "HTML과 CSS 익숙해지기", 1);

test data를 구축한 다음 바로 DQL을 사용해서 데이터를 조회하는 방법 알아보자!

 

데이터 조회(검색)하기

모든 데이터 조회

• Westagram 홈 화면에 모든 사람들이 올린 게시물 목록을 보여준다고 가정
• SELECT 문장을 사용해서 posts 테이블에 존재하는 모든 게시물 데이터를 검색 후 반환

	mysql> SELECT * FROM posts; 

모든 정보를 불러오는 sql은 굉장히 간단하지만, 실제로 요구되어지는 복잡한 요청들은 단순히 모든 정보를 불러오는 것으로 실행할 수 없음.

SQL 문장의 조건을 지정하여 데이터를 검색하기

열을 지정하는 방법과 행을 지정하는 방법으로 구분됨

열을 지정하는 방법

• 해당 colum만 명시해주면 아래와 같이 원하는 컬럼만 테이블 형태로 출력해서 볼 수 있음

mysql> SELECT posts.title FROM posts;

행 (row) 지정 검색 - WHERE 절 + 기본 조건 표현식

• posts 테이블 내에 수백 건에서 천만 건의 행(row)에 달하는 데이터가 있을 경우, 필요한 행(row)만 검색
• 위스타그램 전체 게시물 중에서 “Rebekah Johnson” (users 테이블에 첫번째 row에 저장된 사용자)이 작성한 게시물만 출력

mysql> SELECT id, title, content, user_id FROM posts WHERE user_id = 1;

WHERE 절 추가 ⇒ 여기서 =은 이항연산자로 좌변과 우변의 항목을 비교하고 서로 같은 값이면 참, 그렇지 않으면 거짓을 반환함.

이렇게 비교한 결과값으로 True/False로 반환되는 연산자를 우리는 비교 연산자라고 부름(=이외에 <,>,<=,>= 등이 비교 연산자)

이런 여러 연산자를 조합하여 복잡한 조건 표현식을 만들어 요구되어지는 복잡한 쿼리문 작성할 수 있음

행 (row) 지정 검색 - WHERE 절 + 복수의 조건 표현식

• 복수의 조건을 조합할 때, ‘’AND, ’OR’, ’NOT’ 사용
• “Rebekah Johnson” (user_id=1)이 작성한 게시물 중, 생성일(created_at)이 “2022-04-15 00:00:00” 이전인 게시글 검색

mysql> SELECT * FROM posts WHERE user_id = 1 AND created_at < '2022-04-15 00:00:00';
//user_id 가 1인 아이들 중에서 생성일이 "2022-04-15 00:00:00" 이전인 게시글 검색(AND 연산 = 교집합 || OR 연산 = 합집합)

• NOT 연산자를 사용하게 되면 이는 단항연상자로 오른쪽의 조건 표현식이 참이면 NOT 이 붙어서 반대의 거짓(False)을 반환

mysql> SELECT * FROM posts WHERE NOT user_id = 1 AND created_at < '2022-04-15 00:00:00';
//user_id 가 1인 아이들 중에서 생성일이 "2022-04-15 00:00:00" 이전인 게시글을 빼고 정보 송출

검색 조건 지정하기

<aside> 💡 지금까지 조건 검색 시 = , 즉 이항연산자를 사용. 이항연산자로 검색할 경우, 열의 값이 완전히 일치할 때만 참을 반환하여 결과를 반환 즉, 위스타그램 서비스 이용 중 게시물에 포함된 내용을 부분 검색하고 싶은 경우, 검색결과 제대로 얻을 수 없음

⇒사용자가 게시물의 내용, 즉 posts테이블에 있는 content 컬럼에서 “HTML”이라는 문자열이 포함되어 있는 경우를 검색하고 싶은 경우

</aside>

행(row) 지정 검색 - WHERE 절 + LIKE 패턴 매칭

• 사용자가 게시물의 내용(content)에 “HTML”이라는 문자열만으로도 게시물을 검색하고 싶은 경우
• LIKE 서술어와 메타문자 %를 합쳐서,→문자열의 중간에 검색하고자 하는 단어가 포함되어 있는 경우
• →맨 뒤에 있는 경우 등 다양한 경우에 사용할 수 있는 패턴을 만들 수 있음
• → 문자열 맨 앞의 단어와 일치하는 경우

1. 첫번째 패턴, 문자열 맨 앞의 단어와 일치하는 경우
   1. LIKE ‘HTML%’ 를 사용한 방법으로 검색하고자 하는 텍스트 뒤에 메타문자 %를 붙여줌
   2. 텍스트 뒤에 %가 붙어있는 것은 HTML 텍스트 뒤로 임의의 문자열이 존재한다는 것을 의미

   SELECT id, title, content, user_id FROM posts WHERE content LIKE 'HTML%';
   

2. 두번째 패턴, 문자열의 중간에 검색하고자 하는 단어가 포함되어 있는 경우
   1. LIKE ‘%HTML%’를 사용한 방법으로 검색하고자 하는 텍스트 뒤에 메타문자 %를 붙여줌

   SELECT id, title, content, user_id FROM posts WHERE content LIKE '%HTML%';
   

테이블 결합(RDBMS의 꽃)

• 실제 서비스에서 DB는 데이터를 정규화해서 여러 개의 테이블로 나누어 저장
• 실제로 웹 서비스에서 필요로 하는 다양하고 복잡한 데이터를 보여주기 위해서는 복수의 테이블을 결합한 데이터 검색이 필요
• 테이블 결합

1. 곱집합

• 두 개의 집합을 곱하는 방식

교차 결합 (Cross Join)

• 데이터베이스에서 집합과 집합, 즉 테이블과 테이블의 곱집합 계산 법
• 데이터 베이스에서 테이블(집합)과 테이블(집합)의 곱집합을 계산하는 방법으로 교차결합(Cross JOIN) 사용

교차결합의 SQL문

mysql> SELECT
		posts.id,
		posts.title,
		posts.user_id,
		posts.content,
		users.id,
		users.name,
FROM posts, users

교차결합이 지금부터 설명할 내부결합(Inner Join)과 이후에 설명할 외부결합(Outer Join)의 근간이 되기 때문

내부결합(Inner Join)

우리가 목표로 하는 내부 결합된 결과를 어떻게 만들어 내는지

users 테이블의 id와 posts 테이블의 user_id가 매칭되는 포스트들의 결과들만 가져오게 되면, 우리가 목표했던 결합된 테이블을 결과로 얻을 수 있음

<aside> 💡 이렇게 교차 결합된 곱집합의 결합조건(user_id = posts.user_id)을 더해서 검색하는 것을 내부결합이라고 부른다.

</aside>

• 내부결합을 계산하는 SELECT 문

mysql> SELECT
		posts.id,
		posts.title,
		posts.user_id,
		posts.content,
		users.id,
		users.name,
FROM posts, users
WHERE posts.user_id=users.id;

⇒ 실제로는 INNER JOIN 키워드를 사용해서 내부 결합 계산

내부결합(INNER JOIN 키워드 사용)

• SELECT 문장 안에 FROM 키워드 뒤에 table_references라고 써 있음 →FROM 절에 테이블의 이름과 INNER JOIN 이라고 하는 키워드를 합쳐서 사용해야 된다는 것을 알 수 있음
• ⇒좀 더 이해하기 쉬운 형태로 바꿔보자

//SELECT statement
mysql> SELECT *
FROM table_1
INNER JOIN table_2 ON search_condition
WHERE where_condition 
//INNERJOIN 키워드 뒤에 내부 결합 하고자 하는 테이블 명을 적어주자
//그리고 나서 ON이라고 하는 키워드 뒤에 이 두 개의 테이블을 내부 조인할 때 사용하는 결합 조건을 지정해줍시다.

mysql> SELECT
		posts.id,
		posts.title,
		posts.user_id,
		posts.content,
		users.id,
		users.name
FROM posts
INNER JOIN users ON posts.user_id = users.id;

사실 지금까지는 두 개의 테이블만 내부 결합을 계산했으나, 사실 실제로 요구되어지는 다양한 복잡한 데이터 요청에 대해서는 두 개가 아니라 세 개, 네 개 그 이상의 테이블도 이 내부 결합을 사용해서 하나의 테이블로 합쳐야 하는 경우들이 많이 있음 ⇒ 아주 중요한 sql 구문

외부 결합(Outer Join)

• 외부 결합(Outer Join)은 여러 개의 테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블을 기준으로 모두 출력하는 결합 방법입니다.
• 외부결합(Outer Join)에는 대표적으로 LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, FULL (OUTER) JOIN 이 있습니다.

5번째 user는 무직이야 LIKE ME

우리는 때로는 모든 유저를 출력함과 동시에 만약에 이 사람이 직업이 있으면 그 해당 잡을 보여주고, 없다면 지금처럼 NULL로 표시해주기를 원할 수도 있음 ⇒ LEFT (OUTER) JOIN을 사용

LEFT (OUTER) JOIN

• 기준이 되는 테이블의 모든 row와 join이 걸리는 테이블 중에서 left table 과 매칭되는 row만 검색
• join이 걸리는 테이블에서 매칭되는 결과가 없는 경우, 빈 값(NULL)로 표시

mysql> SELECT
		users.id,
		users.name,
		users.job_id,
		jobs.job
FROM users
LEFT JOIN jobs ON users.job_id = jobs.id;

RIGHT (OUTER) JOIN

• JOIN이 걸리는 테이블(right table)의 모든 row와 기준이 되는 테이블에서 right table과 matching되는 row만 검색하고, 매칭되는 데이터가 없는 경우, NULL을 표시

mysql> SELECT users.id, users.name, posts.id, posts.title, posts.content FROM users LEFT JOIN posts ON users.id = posts.user_id;

매칭되는 데이터가 없는 경우, NULL을 표시

FULL (OUTER) JOIN

• LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 결합 방법
• 기준이 되는 테이블과 join이 걸리는 테이블 양쪽 모두의 row를 검색

left outer join과 right outer join을 UNION 하는 방식으로 FULL OUTER JOIN을 구현해서 사용할 수 있다.