DataBase

SQL 코테 대비 문법 정리 - MySQL

lumana 2025. 2. 12. 23:58

SQL 코테 대비 문법 정리 - MySQL

#Database

참고) SQL 쿼리의 실행 순서

FROM 절

WHERE 절

GROUP BY 절

집계 함수

HAVING 절

SELECT 절

ORDERED BY 절

LIMIT/OFFSET(Optional)

DDL

create table student (
  ID varchar(5),
  name varchar(20) not null,
  dept_name varchar(20),
  tot_cred numeric(3,0),
  primary key (ID),
  foreign key (dept_name) references department
);

SELECT, FROM, WHERE

설명

SELECT: 조회할 컬럼을 지정합니다.
- 중복을 제거하려면 select 뒤에 distinct 키워드를 삽입합니다.
FROM: 데이터를 가져올 테이블을 지정합니다.
- 테이블 나열하면 카테시안 곱을 한다.
- 속성 이름이 같으면, 테이블.속성으로 이름이 바뀐다.
WHERE: 특정 조건을 만족하는 행만 필터링합니다.

문법

SELECT column1, column2, ...
FROM table_name
WHERE condition;

예시

SELECT id, name
FROM employees
WHERE salary > 50000;

논리 연산자

SQL은 논리 연산자 and, or, not을 사용할 수 있습니다.
논리 연산자의 피연산자는 <, <=, >, >=, =, <>와 같은 비교 연산자를 포함할 수 있습니다.
비교는 산술 표현의 결과에도 적용될 수 있습니다.

select name
from instructor
where dept_name = 'Comp. Sci.';

AND

select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000;

또한 SQL은 Where 절에서 between 비교 연산자를 사용할 수 있다.

예시: 연봉이 $90,000에서 $100,000 사이인 모든 강사의 이름을 찾기 (즉, >= $90,000 그리고 <= $100,000)

select name
from instructor
where salary between 90000 and 100000;

between A and B 는 A 이상 B 이하로 B를 포함합니다. 하지만 날짜 데이터를 다루는 경우에는 시분초 데이터까지 생각을 하셔야합니다. 만약 날짜 데이터를 사용하는데 시분초가 없이 지금처럼 날짜만 이용한다면 00시 00분 00초로 자동으로 인식됩니다. 즉 '2020-07-15' 는 '2020-07-15 00:00:00' 로 컴퓨터가 인식한다는 의미입니다. 그래서 14일 데이터를 포함하려면 '2020-07-14' 는 14일에서 1초라도 지나는 순간 '2020-07-14 00:00:00' 를 초과하기 때문에 '2020-07-15' 를 이용하는 것입니다.

튜플 비교

튜플 비교의 형식: (column1, column2, ..., columnN) = (value1, value2, ..., valueN)

예시

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

서브쿼리 (Subquery)

앞 챕터에서 관계 연산의 조합을 다루면서 “관계 대수 연산의 결과는 relation이다. 따라서 관계 대수 연산의 결과인 Relation을 다시 관계 대수 연산Relation으로 사용할 수 있다” 라고 했던 걸 기억할 것이다.
이를 SQL에서도 제공하고 있다. select-from-where 쿼리의 결과로 얻은 릴레이션을 다시 쿼리에 사용할 수 있다. 중첩 서브쿼리는 SQL 쿼리 내에서 다른 쿼리를 포함하는 것을 말한다. (서브쿼리를 내부쿼리라고도 한다.)

설명

서브쿼리는 다른 쿼리 내부에 포함되어 실행되는 쿼리입니다.
주로 WHERE, FROM, 또는 SELECT 절 내에서 사용됩니다.

From 절: r_i는 자리에 서브쿼리를 사용할 수 있다.

즉, 서브쿼리가 하나의 테이블처럼 동작하여 그 결과를 사용해 외부 쿼리를 실행한다.

쿼리의 결과가 relation이기 때문

SELECT A1, A2, ...
FROM (SELECT ... FROM ... WHERE ...) AS subquery_name
WHERE ...;

Where 절 : 서브쿼리의 결과를 사용해 조건을 정의할 수 있다. 서브쿼리의 결과는 일반적으로 값 또는 집합을 반환하며, 외부 쿼리는 이를 조건으로 사용하여 필터링을 수행한다.

Where 절의 P는 B (subquery) 형태의 표현식으로 대체될 수 있습니다.

여기서 B는 attribute이고 은 연산입니다.

SELECT A1, A2, ...
FROM table_name
WHERE column_name = (SELECT ... FROM ... WHERE ...);

문법

SELECT column
FROM table_name
WHERE column IN (
    SELECT column FROM another_table WHERE condition
);

예시

SELECT lastname, firstname
FROM employees
WHERE officeCode IN (
  SELECT officeCode
  FROM offices
  WHERE country = 'USA'
);

서브쿼리는 미국에 위치한 사무실의 모든 office code를 반환합니다.
외부 쿼리는 서브쿼리에 의해 반환된 office code에 해당하는 사무실에서 근무하는 직원의 성과 이름을 선택합니다.

Select 절: 서브쿼리를 통해 계산된 값을 특정 열로 선택할 수 있다. 이 경우 서브쿼리는 단일 값을 반환해야 한다.

A_i는 단일 값을 생성하는 서브쿼리로 대체될 수 있습니다.
```
SELECT (SELECT ... FROM ... WHERE ...) AS column_name
FROM table_name;
```

Set MemberShip - IN

IN과 NOT IN 연산자를 사용한다.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT statement);

2017년 가을학기와 2018년 봄 학기에 열린 과목 찾기

ID가 10101인 교수님이 가르치는 과목을 수강하는 학생 수 구하기

Set Comparison - some, all, EXIST

SOME

하나라도 만족하면 TRUE를 반환한다.

예시: 생물학과의 일부(최소 한 명) 강사보다 급여가 높은 강사의 이름 찾기

ALL

모두 조건을 만족해야 TRUE
예시 : Biology 부서의 모든 강사보다 급여가 높은 강사의 이름을 찾기

SELECT name
FROM instructor
WHERE salary > ALL (SELECT salary
FROM instructor
WHERE dept_name = 'Biology');

EXISTS

서브쿼리의 결과가 존재하는지 확인하는 데 사용된다.
반환된 행이 있는지만 체크하고 있으면 True, 없으면 False를 반환한다.
NOT EXISTS는 EXISTS와 반대로 작동한다

예시: 최소 하나의 주문을 한 고객의 이름

SELECT fname, lname
FROM Customers
WHERE EXISTS (SELECT *
FROM Orders
WHERE Customers.customer_id = Orders.c_id);

EXISTS vs IN

공통점 : 둘 다 WHERE 절에서 조건에 따라 필터링된 결과를 조회할 때 사용된다.

차이점 :
EXISTS는 조건에 해당하는 Row가 존재하는지만 체크한다. 즉 (서브쿼리의) SELECT 절까지 평가하지 않는다.
IN은 조건에 해당하는 Row의 Column을 비교한다. 즉 (서브쿼리의) SELECT 절에서 조회한 Column으로 값을 비교하므로 EXIST에 비해 성능이 떨어진다. (대량의 데이터를 다룰 때 비효율적이다)

실행 순서:
EXISTS : 메인 쿼리의 결과 값을 서브 쿼리에 대입하여 조건 비교 후 결과를 출력한다 (메인 쿼리의 각 행에 대해 EXISTS 쿼리가 실행됨).
IN : 서브 쿼리의 결과값을 메인 쿼리에 대입하여 조건 비교 후 결과를 출력한다.

NULL값 처리:
NOT EXISTS: NULL값에 대해 TRUE를 반환한다
NOT IN: NULL값에 대해 FALSE를 반환한다.

다중 정렬 (Multiple Sorting)

우선 순위가 높은 순으로 나열해주면 된다.
ORDER BY HIRE_YMD DESC, DR_NAME ASC

설명

ORDER BY 절에 여러 컬럼을 지정하여 정렬 기준을 추가할 수 있습니다.

문법

SELECT columns
FROM table_name
ORDER BY column1 ASC, column2 DESC;

예시

SELECT *
FROM employees
ORDER BY department_id ASC, salary DESC;

BETWEEN, AND

설명

지정한 두 값 사이의 범위 내에 있는 데이터를 조회할 때 사용합니다.

문법

SELECT column
FROM table_name
WHERE column BETWEEN value1 AND value2;

예시

SELECT *
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-31';

LIKE - `%`

설명

문자열 패턴 매칭을 위해 사용합니다.
%는 0개 이상의 임의의 문자를 의미합니다.

문법

SELECT column
FROM table_name
WHERE column LIKE 'pattern';

예시

SELECT *
FROM employees
WHERE name LIKE 'J%';  -- 'J'로 시작하는 이름들

문자열 포함

특정 문자로 시작하는 데이터 검색

SELECT [필드명] FROM [테이블명] WHERE [필드명] LIKE '특정 문자열%';

특정 문자로 끝나는 데이터 검색

SELECT [필드명] FROM [테이블명] WHERE [필드명] LIKE '%특정 문자열’;

특정 문자를 포함하는 데이터 검색

SELECT [필드명] FROM [테이블명] WHERE [필드명] LIKE '%특정 문자열%';

특정 문자를 포함하지 않는 데이터 검색

SELECT [필드명] FROM [테이블명] WHERE not [필드명] LIKE '%특정 문자열%’;

ex) ADDRESS가 서울인 곳 검색
WHERE ADDRESS LIKE ‘서울%’

%서울% 로 하면 안 된다. 주소가 만약 가나서울다라 라면? 문제가 생긴다.
그리고 서울특별시가 아니라 서울시로 작성하는 사람들도 있기 때문에 서울%로 해준다.

혹은 직접 IN절을 이용해서 ‘서울특별시’, ‘서울시’를 찾아내자.

NULL

데이터베이스에서 NULL 값은 데이터베이스 내에서 특정 attribute(column)에 값이 없거나 알 수 없음을 나타낸다.
null을 포함하는 모든 산술 표현의 결과는 null입니다.
- 예) 5 + null의 결과는 null입니다.
- 주의! NULL = NULL의 결과는 TRUE가 아니라 NULL이다.

IS NULL, IS NOT NULL

예시

select name
from instructor
where salary is null

COALESCE

설명

전달된 인자들 중 NULL이 아닌 첫 번째 값을 반환합니다.

기본 사용법
SELECT COALESCE(칼럼1, 칼럼2, 칼럼3, ... , 칼럼 N, ...) FROM table;
칼럼1이 NULL이 아니면 칼럼1을 반환되고 NULL이면 칼럼2를 반환.
칼럼2가 NULL이 아니면 칼럼2를 반환하고 칼럼1과 칼럼2 모두 NULL이면 칼럼3을 반환.
칼럼 1 ~ 칼럼 5까지 NULL 이면 칼럼6을 반환되는 형식

예시

SELECT name, COALESCE(middle_name, 'N/A') AS middle_name
FROM employees;

특정 칼럼의 NULL 값을 ’N/A’로 대체한다.

IFNULL() - Mysql 방언

설명

IFNULL(expression, alt_value): 표현식이 NULL이면 대체 값을 반환합니다.

문법

IFNULL(expression, alt_value)

예시

SELECT name, IFNULL(phone, 'No Phone') AS phone
FROM customers;

IFNULL(val, x) -- single argument
val 값이 null이면 x를 반환하고, null이 아니면 val을 그대로 반환한다

COALSECE (val1, val2, val3...) -- multiple(N) argument
val1 값이 null이면 val2를 반환, val2도 null이면 val3를 반환한다.
val1 값이 null이면 val2를 반환, val2값이 null이 아니면 val2를 그대로 반환한다.

DATE_FORMAT()

설명

날짜 및 시간 값을 원하는 형식의 문자열로 변환합니다.

문법

DATE_FORMAT(date, format)

년도-월-일 순으로 출력해준다.
SELECT DR_NAME, DR_ID, MCDP_CD, DATE_FORMAT(HIRE_YMD, '%Y-%m-%d')

포맷을 지정해줘야 한다.
대문자 Y는 2009 요런식으로 출력해주고, 소문자 y는 99, 00, 09.. 요런식으로 출력해준다.

예시

SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

DATETIME

DATETIME 타입은 YYYY-MM-DD hh:mm:ss 의 형식이다.

문자열을 Date로 변환
문자열을 Date 형식으로 반환해주는 to_date

to_date('2022-12-20','yyyy-mm-dd')

DATE를 문자열로 변환

to_char

to_char(saldate, 'yyyy-mm-dd') -- saldate의 타입은 Date인 상황

TIMESTAMP

기본적인 '일자'에 시간 정보까지 포함되어있음

문자열을 Timestamp로 변환
문자열을 Timestamp 형식으로 반환해주는 to_timestamp

to_timestamp('2022-12-20','yyyy-mm-dd')

Time

오직 시간 정보만 가짐

Interval: 기간

전체적으로 시작과 끝까지 몇 일/몇 시간 등이 걸렸는지에 대한 정보가 필요할 때 사용

기본적으로 Date 타입에 덧셈/뺄셈 연산을 하게 되면 해당하는 일자를 더하거나 빼서 출력된다.

select to_date('2022-12-20', 'yyyy-mm-dd') + 2

-- 2022-12-22 출력

하지만 Timestamp 타입에 숫자의 연산이 들어가면 오류가 발생

select to_timestamp('2022-12-20 20:35:44', 'yyyy-mm-dd hh24:mi:ss') + 7
-- Error

이를 수행하기 위해서는 interval 키워드가 필요하다

select to_timestamp('2022-12-20 20:35:44', 'yyyy-mm-dd hh24:mi:ss') + interval '7 hour'

만약 interval을 구하기 위해 date 간 뺄셈을 진행하게 되면 정수형이 반환된다.(날짜만을 알려주기에 정수형.)
Date 타입간 덧셈은 허용하지 않는다!
만약 interval을 구하기 위해 Timestamp 간 뺄셈을 진행하게 되면 interval 타입이 반환된다.

DATEDIFF(), TIMESTAMPDIFF()

설명

두 날짜/시간 값 사이의 차이를 지정한 단위(예: DAY, MONTH 등)로 반환합니다.

DATEDIFF(날짜1, 날짜2);

문법

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

예시

SELECT TIMESTAMPDIFF(DAY, '2025-01-01', '2025-01-31') AS diff_days;

SECOND : 초
MINUTE : 분
HOUR : 시
DAY : 일
WEEK : 주
MONTH : 월
QUARTER : 분기
YEAR : 연

extract와 date_part를 이용한 시간 정보 추출

1) extract 키워드 사용

select a.*
	,extract(year from saldate) as year
	,extract(month from saldate) as month
	,extract(day from saldate) as day
from a_table a;

2) date_part 키워드 사용

select a.*
	,date_part('year', saldate) as year
	,date_part('month', saldate) as month
	,date_part('day', saldate) as day
from a_table a;

둘 중에 무엇을 사용해도 무방하나, 문법적인 느낌을 살리기 위해서는 date_part를 사용하는 것을 권장하고 있음
키워드에는 year, month, day(s), hour(s), minute(s), second(s)가 들어갈 수 있음

만약 MYSQL이라면?

MONTH(DATE_OF_BIRTH) 이런 식으로 추출할 수 있다. 방언임!

DATE_TRUNC()

절삭을 시행할 경우 절삭되는 값의 모든 값은 첫 번째 값으로 세팅된다.

select date_trunc('month', '2022-12-21'::date)::date;
-- 2022-12-01이 출력

select date_trunc('year', '2022-12-21'::date)::date;
-- 2022-01-01이 출력

DATE_SUB, SUBDATE()

날짜를 빼줄 수 있는 함수

-- 원하는 단위만큼 빼기
DATE_SUB(date, INTERVAL value addunit)
SUBDATE(date, INTERVAL value addunit)

-- 일단위, 초단위
SUBDATE(date, adddays)
SUBTIME(date, addtime)

-- DATE_SUB
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR);  -- '2020-12-15 11:20:33'
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);  -- '2021-11-15 11:20:33'
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);   -- '2021-12-14 11:20:33'
SELECT DATE_SUB(NOW(), INTERVAL 1 HOUR);  -- '2021-12-15 10:20:33'
SELECT DATE_SUB(NOW(), INTERVAL 1 MINUTE);  -- '2021-12-15 11:19:33'
SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND);  -- '2021-12-15 11:20:32'
SELECT DATE_SUB(NOW(6), INTERVAL 1 MICROSECOND); -- '2021-12-15 11:20:33.268402'

-- SUBDATE
SELECT SUBDATE(NOW(), INTERVAL 1 YEAR);   -- '2020-12-15 11:20:33'
SELECT SUBDATE(NOW(), INTERVAL 1 MONTH);  -- '2021-11-15 11:20:33'
SELECT SUBDATE(NOW(), INTERVAL 1 DAY);   -- '2021-12-14 11:20:33'
SELECT SUBDATE(NOW(), INTERVAL 1 HOUR);   -- '2021-12-15 10:20:33'
SELECT SUBDATE(NOW(), INTERVAL 1 MINUTE);  -- '2021-12-15 11:19:33'
SELECT SUBDATE(NOW(), INTERVAL 1 SECOND);  -- '2021-12-15 11:20:32'
SELECT SUBDATE(NOW(6), INTERVAL 1 MICROSECOND);  -- '2021-12-15 11:20:33.268402'

-- SUBDATE DAYS
SELECT SUBDATE(NOW(), 30);   -- '2021-11-15 13:30:34'

-- SUBTIME
SELECT SUBTIME(NOW(), "10");   -- '2021-12-15 13:30:59'
SELECT SUBTIME(NOW(), "10 10:10:10");  -- '2021-12-05 03:21:11'

CASE WHEN ... THEN ... ELSE ... END

설명

조건에 따라 다른 값을 반환할 때 사용합니다.

문법

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

예시

SELECT name,
       CASE 
         WHEN salary > 50000 THEN 'High'
         ELSE 'Low'
       END AS salary_level
FROM employees;

UPDATE table SET    level = 
   CASE    WHEN gn=’1′    THEN ’10’    WHEN gn=’2′    THEN ’20’    ELSE ‘0’    END WHERE grade=’2′;

select case  when DA < 10 then 0 when DA >= 10 and DA <30 then 10 when DA >= 30 and DA <50 then 30 when DA >= 50 and DA <70 then 50 when DA >= 70 and DA <90 then 70 when DA >= 90 then 90 end as DAgrade, count(*) from table group by DAgrade;

LEFT(), RIGHT()

설명

문자열의 왼쪽(오른쪽) 부분에서 지정한 개수만큼 문자를 반환합니다.

문법

LEFT(string, number_of_characters)

예시

SELECT name, LEFT(name, 3) AS short_name
FROM employees;

SUBSTR()

설명

문자열의 특정 부분을 추출합니다.
MySQL에서는 SUBSTRING()과 동일하게 사용됩니다.

문법

SUBSTR(string, start_position, length)

예시

SELECT name, SUBSTR(name, 2, 3) AS sub_name
FROM employees;

MID

설명

MID()는 MySQL에서 SUBSTR()의 다른 이름으로, 문자열의 일부를 추출하는 함수입니다.

문법

MID(string, start_position, length)

예시

SELECT MID(name, 2, 3) AS mid_name
FROM employees;

JOIN

Join 작업은 카티션 곱으로, 두 relation의 튜플이 일치하는지 확인합니다 (일부 조건 하에).

Join에는 3가지 타입이 있습니다.

Natural join
Inner join
Outer join

Natural Join

Natural Join은 두 테이블 간에 동일한 이름의 컬럼들을 자동으로 사용하여 조인한다.

Natural join은 동일한 attribute에 대해 동일한 값을 갖는 튜플을 일치시키며, 각 공통 열의 한 사본만 유지합니다.
- 연관된 테이블(from절에 사용할 테이블)의 동일한 값을 가진 열은 한 번만 나타납니다

예시: 강의를 수강한 학생의 이름과 강의 과목 ID

select name, course_id
from students, takes
where student.ID = takes.ID;

from 절에서 cartesian 곱으로 얻는 relation에서 student.ID = takes.ID인 레코드를 선택하고 있다.

위 쿼리를 natural join을 통해서 작성한다면?

select name, course_id
from students
natural join takes;

선택 조건을 따로 작성하지 않아도 된다. Attribute의 이름이 같은 것(여기선 ID)끼리 알아서 비교해주기 때문에 쿼리가 매우 simple해졌다

Join Condition - ON

여기까지 봤을 때 의문이 한 가지 떠오를 수 있다.
만약 비교해야 할 Attribute의 이름이 다르다면? 내가 원하는 Attribute 간의 비교를 지정해주고 싶다면?

이 때 사용하는게 ON 절이다. ON 절을 사용하면 명확하게 어떤 열을 기준으로 테이블을 조인할지를 지정할 수 있다.

예를 들어, 두 테이블 A와 B가 있을 때, A.column1과 B.column2가 일치하는 행들을 조인하고 싶다면, ON A.column1 = B.column2라고 명시하면 된다.
조건은 on 키워드를 사용한다는 점을 제외하면 where 절처럼 작성하면 된다.

예시

select *
from student, takes
where student_ID = takes_ID

위 구문을 join과 on을 사용하면 아래와 같이 나타낼 수 있다.

select *
from student join takes
on student_ID = takes_ID

주의!!! Natural join과 join은 다르다. Natural join은 동일한 이름의 Column끼리 비교하지만, Join은 on 절을 통해 비교해야 할 Attribute를 지정해줘야 한다. 만약 on 절을 사용하지 않으면 두 테이블 간의 Cartesian 곱이 이루어진다.

Inner Join

INNER JOIN은 두 테이블 간에 교집합을 반환한다. 즉, ON 조건에 따라 두 테이블에서 일치하는 행만 결합하여 결과로 반환한다.

위에서 Join Condition을 설명하면서 Join ~~~ on 으로 sql을 작성한 것을 봤을텐데, 사실 여기서 사용한 Join이 Inner Join에 해당한다. Inner를 명시하지 않아도 Inner Join으로 작동하는 것이다.

Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Outer Join

OUTER JOIN은 일치하는 행이 없더라도, 한쪽 또는 양쪽 테이블의 모든 행을 포함하여 조인한다.

정보 손실을 방지하는 join 작업의 확장입니다.
- join한 다음, 다른 relation의 튜플과 일치하지 않는 튜플을 join 결과에 추가합니다.
- Outer join은 일치하는 데이터와 일치하지 않는 데이터를 모두 기반으로 합니다.
- NULL 값을 사용합니다.

테이블 A, 테이블 B가 있고, 테이블 A를 LEFT Table이라고 하고, 테이블 B를 RIGHT Table이라고 하자.
Outer Join은 다음과 같이 동작한다.

ON 조건에 따라 테이블 A, B에서 공통된 튜플을 고른다.
일치하지 않는 튜플을 결과 relation에 추가해준다.

여기서 LEFT Table에 남아있는 것을 가져올 지, 아니면 RIGHT Table에 있는 것을 가져올 지, 혹은 두 Table 남아있는 것 모두를 가져올 지에 따라 3가지로 분류할 수 있다.

OUTER JOIN에는 세 가지 타입이 존재한다.

LEFT OUTER JOIN (= LEFT JOIN)
RIGHTOUTER JOIN (= RIGHT JOIN)
FULL OUTER JOIN (= FULL JOIN)

LEFT OUTER JOIN (또는 LEFT JOIN)

한 줄 설명 : 공통 튜플 + LEFT Table에 남아있는 튜플

Left outer join은 두 테이블을 join하고 SQL 표현식이 참인 두 테이블의 모든 일치하는 행을 가져오며, 두 번째 테이블(RIGHT)에서 일치하는 행이 없는 첫 번째 테이블(LEFT)의 행도 가져옵니다.
- 즉, Left outer join은 두 테이블의 일치하는 데이터를 가진 결과 세트 테이블을 반환하고, 왼쪽 테이블의 나머지 행과 오른쪽 테이블의 열에서 null을 반환합니다.

Syntax

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Left outer join은 왼쪽 테이블(table1)의 모든 레코드와 오른쪽 테이블(table2)의 일치하는 레코드를 반환합니다.
- 일치하는 레코드가 없으면 오른쪽의 결과가 NULL이 된다.
- —> 조인 조건에 일치하는 데이터가 오른쪽 테이블에 존재하지 않을 경우 그 결과에 오른쪽 테이블의 해당 열들이 NULL로 표시된다는 말이에요

Right Outer Join

Left Outer Join과 반대로, 공통된 튜플을 가져오고 Right Table에 남아있는 튜플 또한 가져온다

Right outer join은 두 테이블을 join하고 조건에 따라 행을 가져오며, 두 테이블에서 일치하는 행과 JOIN 절 다음에 작성된 테이블에서 일치하지 않는 행도 가져옵니다.

Syntax

SELECT *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Full Outer Join

양쪽 테이블의 모든 행을 반환, 일치하지 않는 부분은 NULL로 채움.
LEFT Outer Join + Right Outer Join 느낌

문법

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

UNION, INTERSECT, EXCEPT

UNION

여러 SELECT 쿼리의 결과를 결합합니다.
기본적으로 중복된 결과는 제거됩니다. (중복 포함: UNION ALL)

INTERSECT

Intersect 연산은 두 SELECT 문을 결합하는 데 사용되며, 두 SELECT 문에서 공통된 레코드만 반환합니다.

헷갈리지 말자 | INTERSECT VS INNER JOIN

INTERSECT : 두 쿼리의 결과에서 공통된 행을 반환

INNER JOIN : 두 테이블 간의 일치하는 행을 결합하여 하나의 결과로 반환

EXCEPT

Except 연산은 두 SELECT 문 결과를 결합하여 첫 번째 집합에 속하는 결과만 반환합니다.

문법

SELECT column FROM table1
UNION
SELECT column FROM table2;

예시

SELECT name
FROM employees
UNION
SELECT name
FROM customers;

LIMIT

설명

반환할 행의 수를 제한합니다.
[offset,] row_count 형식 또는 LIMIT row_count로 사용합니다.

문법

SELECT columns
FROM table_name
LIMIT offset, row_count;

예시

SELECT *
FROM employees
LIMIT 5;  -- 처음 5개의 행만 반환

SELECT * FROM INFO
LIMIT 2, 3;

—> 세번째행부터 3개의 행을 반환. (첫 번째 행의 offset은 0부터 시작함)

SET

설명

데이터 타입으로서의 SET
- 미리 정의된 값들의 집합에서 0개 이상의 값을 저장할 수 있는 데이터 타입입니다.
문법
```
CREATE TABLE example (
    id INT,
    options SET('A', 'B', 'C')
);
```
SET 명령어
- 사용자 변수나 세션 변수를 설정할 때 사용합니다.
문법
```
SET @var_name = value;
```

예시 (데이터 타입)

CREATE TABLE user_preferences (
    user_id INT,
    preferences SET('email', 'sms', 'push')
);

변수

SET @변수명 을 사용시 = 대입연산자를 사용한다
SELECT @변수명 을 사용시 := 과 같은 대입연산자를 사용한다.

SELECT * FROM tb_code WHERE code_cd BETWEEN @start AND @finish;

REGEXP

설명

정규 표현식을 사용하여 문자열의 패턴을 검색합니다.

문법

SELECT columns
FROM table_name
WHERE column REGEXP 'pattern';

예시

SELECT *
FROM employees
WHERE name REGEXP '^J.*';  -- 'J'로 시작하는 이름

RECURSIVE (재귀 CTE, Common Table Expresssion)

설명

MySQL 8.0 이상에서는 WITH RECURSIVE 구문을 사용하여 재귀적(계층적) 데이터를 처리할 수 있습니다.

문법

WITH RECURSIVE cte_name AS (
    -- Anchor member (기본 조회)
    SELECT ...
    UNION ALL
    -- Recursive member (재귀 조회)
    SELECT ...
    FROM cte_name
    WHERE condition
)
SELECT * FROM cte_name;

재귀 쿼리는 두개의 SELECT절로 이루어지며 각 SELECT절은 UNION으로 구분된다. 첫번째 SELECT절은 Non-Recursive한 부분이고 두번째 SELECT절은 Recursive한 부분이다. 두번째 SELECT절이 반복적으로 자기자신을 참조하여 쿼리를 실행한다.

WITH RECURSIVE cte AS
(
  SELECT 1

  UNION ALL

  SELECT n + 1 FROM cte WHERE n < 5 -- FROM절에 자신이 참조됨.
)
SELECT * FROM cte;

+------+
| n    | 
+------+
|    1 | -> 첫번째 SELECT 절이 반환한 행
|    2 | -> 첫 행을 기반으로 두번째 SELECT 절이 반환한 행 (1 + 1 = 2)
|    3 |
|    4 |
|    5 | -> n < 5 일 때까지 반복. n = 5에 반복 종료.
+------+

-- WITH 절 뒤에 RECURSIVE  
WITH RECURSIVE cte AS
(
  -- Non-Recursive : 반복을 진행하기 위한 행 세팅
  SELECT 1  
  UNION ALL
  -- Recursive : 위의 SELECT절에서 세팅된 행을 기반으로 반복을 진행.
  SELECT n + 1 
  FROM cte -- 자기자신 참조 명시
  WHERE n < 5 -- 반복 종료 조건
)
SELECT * FROM cte;

예시 (계층적 데이터 조회)

WITH RECURSIVE EmployeeHierarchy AS (
    -- 최상위 관리자 조회
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- 하위 직원 재귀 조회
    SELECT e.id, e.name, e.manager_id
    FROM employees AS e
    JOIN EmployeeHierarchy AS eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

Aggregate Function

avg: 평균 값
min: 최소 값
max: 최대 값
sum: 값의 합
count: 값의 수
등등
이러한 함수들은 릴레이션의 열 값의 다중 집합(multiset)에서 작동하며 값을 반환합니다.

만약 집계 함수가 계산하려는 데이터 중 NULL 값이 존재하면 어떻게 동작할까?

select sum (salary)
from instructor

위 문장은 null 금액을 무시합니다.
non-null 금액이 없으면(모두 null 값이면) 결과는 null입니다.

where salary is not null 라고 적었던 걸 볼 수 있는데, 여기서 집계 함수를 사용하면 is not null은 필요 없게 된다.

주의) count(*)를 제외한 모든 집계 연산은 집계 속성의 null 값을 무시합니다.

즉 count만 null을 고려한다고 보면 된다.

avg

select avg (salary)
from instructor
where dept_name = 'Comp. Sci.';

count

select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

릴레이션의 튜플(레코드) 수 찾기

select count (*)
from course;

Group By

집계 함수는 보통 Group By 절과 함께 사용된다. Group By는 특정 Attribute를 기준으로 데이터를 그룹화하여 각 그룹에 대해 집계 결과를 계산한다.

예시: 각 학과의 강사 평균 급여를 찾기
한 학교의 모든 강사의 평균 급여를 구해봤지만, 학과별 교수 평균 급여를 구하고 싶다고 해보자. 이 때 교수를 “학과”를 기준으로 묶어서 그룹 내 급여 평균을 구한 뒤 하나의 레코드로 표현하면 되지 않을까?
—> Group By를 사용한다.

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

참고) as 키워드가 등장하는데, as는 테이블 또는 열(Attribute)에 임시 이름을 부여하는 데 사용된다. 위 예시에서는 학과 기준으로 그룹화여 교수 개개인의 연봉이 아닌 학과 평균 연봉을 저장하므로 salary 대신 avg_salary라는 이름을 사용하는 것이 적합하다. 따라서 as 키워드를 사용하였다.

주의) Aggregate function의 대상이 되는 attribute를 제외한 나머지 select 절에 있는 attribute는 group by 목록에 반드시 나타나야 한다.

오류가 있는 예시

/* 오류가 있는 쿼리 */
select dept_name, ID, avg (salary)
from instructor
group by dept_name;

학과별 교수 연봉 평균을 구하기 위해서 학과를 기준으로 그룹화를 했는데, 교수 ID를 선택하는 쿼리는 논리적으로도 문제가 있는 쿼리이다. dept_name으로 그룹화하였기 때문에 select 절에는 [집계 함수의 대상이 되는 attribute(salary), group_by의 대상이 되는 attribute(dept_name)]를 제외하고 다른 attribute가 올 수 없는 것이다.

HAVING

설명

GROUP BY 절로 그룹화한 결과에 조건을 적용할 때 사용합니다.
WHERE는 그룹화 전 행에 조건을 적용하는 반면, HAVING은 그룹 단위로 필터링합니다.

문법

SELECT column, aggregate_function(column)
FROM table_name
GROUP BY column
HAVING aggregate_function(column) condition;

예시

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING emp_count > 10;

HAVING 절은 결과에 표시될 그룹을 필터링하는 조건을 지정할 수 있습니다.

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

주의: HAVING 절의 조건자는 그룹 형성 후 적용되며, WHERE 절의 조건자는 그룹 형성 전에 적용된다.

WHERE 절을 통해서 조건을 만족하지 않는 레코드를 미리 제외시키고 그룹 형성을 해야 연산 횟수가 줄어 효율적이기 때문이다.

TRUNCATE()

설명

숫자 관련 함수: 주어진 숫자를 소수점 이하 지정한 자리수로 잘라냅니다. (버림)
테이블 관련 명령: TRUNCATE TABLE은 테이블의 모든 데이터를 삭제합니다.

문법 (숫자 함수)

TRUNCATE(number, decimals)

예시 (숫자 함수)

SELECT TRUNCATE(123.4567, 2) AS truncated_value;  -- 결과: 123.45

ROUND()

설명

숫자를 지정한 소수점 자리수로 반올림합니다.

문법

ROUND(number, decimals)

예시

SELECT ROUND(123.456, 2) AS rounded_value;  -- 결과: 123.46

Aggregate + 소수점

SELECT ROUND(avg(DAILY_FEE), 0) as AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE = 'SUV'

ROUND 내의 column 명에 집계함수를 넣어주면 된다.

CEIL()

CEILING 은 소수점 이하를 무조건 올리는 역활을 하는 함수입니다.

ROUND와 달리 CEILING은 무조건 정수값으로 출력된다.
자리수 지정 못함

CONCAT()

설명

여러 문자열을 하나로 연결하여 반환합니다.

문법

CONCAT(string1, string2, ...)

예시

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

LPAD()

설명

문자열의 왼쪽에 지정한 문자열로 채워서 원하는 길이의 문자열을 만듭니다.

문법

LPAD(string, length, pad_string)

예시

SELECT LPAD('123', 5, '0') AS padded;  -- 결과: '00123'

RPAD()

설명

문자열의 오른쪽에 지정한 문자열로 채워서 원하는 길이의 문자열을 만듭니다.

문법

RPAD(string, length, pad_string)

예시

SELECT RPAD('abc', 5, 'x') AS padded;  -- 결과: 'abcxx'

FLOOR()

설명

주어진 숫자보다 크지 않은 가장 큰 정수를 반환합니다.

문법

FLOOR(number)

예시

SELECT FLOOR(123.456) AS floor_value;  -- 결과: 123

REPEAT()

설명

지정한 문자열을 주어진 횟수만큼 반복하여 연결한 문자열을 반환합니다.

문법

REPEAT(string, count)

예시

SELECT REPEAT('A', 3) AS repeated;  -- 결과: 'AAA'

REPLACE

설명

문자열 내에서 특정 부분 문자열을 다른 문자열로 대체합니다.

문법

REPLACE(string, from_substring, to_substring)

예시

SELECT REPLACE('Hello World', 'World', 'MySQL') AS replaced;  -- 결과: 'Hello MySQL'

윈도우 함수

윈도우함수는 Group By와 비슷하게 데이터를 그룹화하여 집계해준다. 하지만 Group By는 집계된 결과만 보여주는 반면, 윈도우함수는 기존 데이터에 집계된 값을 추가하여 나타낸다.

쿼리의 작동순서는 from - where - group by - having - select - orderby - limit이다.
즉 GROUP BY는 집계 함수를 사용하여 기존 행에 있던 값들을 계산한 후 새로운 행에 입력해 주는데, 그룹화를 하면서 기존의 상세 데이터들을 잃게 된다.
때문에 GROUP BY 뒤에 오는 SELECT/ ORDER BY에서 개별데이터를 사용하게 되면 에러가 발생한다.

PARTITION BY
PARTITION BY는 SELECT절에서 사용되기 때문에 GROUP BY와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지된다.

Window Function(함수_적용_열) OVER (PARTITION BY 그룹열 ORDER BY 순서열)

-- Window Function : 순위함수(RANK, DENSE_RANK 등)나 집계함수(SUM, MAX 등)
-- PARTION BY : 소그룹으로 분류 
-- ORDER BY : 분류된 소그룹 정렬
-- 순서열 : 정렬 기준 행 설정

SELECT score
     , RANK() OVER (ORDER BY score DESC) AS rank
     , DENSE_RANK() OVER (ORDER BY score DESC) as d_rank
     , ROW_NUMBER() OVER (ORDER BY score DESC) as rownum
   FROM score

PARTITION

설명

테이블 파티셔닝

큰 테이블을 작은 물리적 단위(파티션)로 분할하여 관리 및 성능 최적화를 도모합니다.

문법 (예시)

CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

윈도우 함수의 PARTITION BY
- 윈도우 함수에서 PARTITION BY 절을 사용하여 데이터를 그룹별로 나누고 각 그룹 내에서 순위를 매기거나 집계할 수 있습니다.
문법
```
SELECT column,
       RANK() OVER (PARTITION BY group_column ORDER BY order_column DESC) AS rank
FROM table_name;
```

예시 (윈도우 함수)

SELECT name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

OVER()

작성해야 함.

RANK, DENSE_RANK, ROW_NUMBER (윈도우 함수)

설명

MySQL 8.0 이상에서는 윈도우 함수를 사용하여 순위를 매길 수 있습니다.
- RANK(): 동점이 있으면 순위에 건너뛰기가 발생합니다.
- DENSE_RANK(): 동점이 있더라도 순위 건너뛰지 않고 연속된 순위를 부여합니다.
- ROW_NUMBER(): 각 행에 고유한 순번을 부여합니다.

SELECT score
     , RANK() OVER (ORDER BY score DESC) AS rank
     , DENSE_RANK() OVER (ORDER BY score DESC) as d_rank
     , ROW_NUMBER() OVER (ORDER BY score DESC) as rownum
   FROM score

RANK() : 공동순위 다음 동점의 수만큼 순위를 뒤로 밀어냄
DENSE_RANK() : 공동순위 다음 순위를 밀어내지 않음
ROWNUM() : 정렬된 순서로 순번을 매김

score	rank	d_rank	rownum
100	1	1	1
95	2	2	2
95	2	2	3
90	4	3	4

CTE (Common Table Expression)

설명

CTE는 WITH 절을 사용하여 임시 결과 집합을 정의한 뒤, 이후의 SELECT, INSERT, UPDATE, DELETE 문에서 재사용할 수 있도록 합니다.
MySQL 8.0 이상에서 지원합니다.

문법

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name;

예시

WITH HighSalaryEmployees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM HighSalaryEmployees;

WITH customers_in_usa AS (
  -- 1) 내부
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) 
-- 2) 외부 
SELECT customerName FROM customers_in_usa WHERE state = 'CA' ORDER BY customerName;

작성 예정

FIRST_VALUE()

LAG()

LEAD()

현재글SQL 코테 대비 문법 정리 - MySQL

Brute force Study

배움을 나누는 공간 https://github.com/DawnGlow

Today :
Yesterday :

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28