SQL 요약 (기본구조+구문)

SQL이란?

SQL(Structured Query Language)은 데이터베이스 관리 시스템(DBMS)에서 데이터를 관리하고 조작하기 위해 사용되는 표준 언어입니다. SQL은 관계형 데이터베이스에서 데이터를 조회, 삽입, 업데이트, 삭제하는 등의 작업을 수행할 수 있도록 설계되었습니다.
SQL은 여러 데이터베이스 시스템에서 사용되며, 대표적으로 MySQL, PostgreSQL, Oracle, Microsoft SQL Server 등이 있습니다.

SQL = DB에서 Data를 조작하기 위해 사용하는 언어

1. 데이터 정의 언어(DDL: Data Definition Language) - 데이터베이스 구조를 정의하거나 수정

--CREATE: 데이터베이스, 테이블, 인덱스 등을 생성합니다.
CREATE TABLE Users ( ID INT PRIMARY KEY, Name VARCHAR(100), Email VARCHAR(100) );
--ALTER: 기존 데이터베이스 객체를 수정합니다.
ALTER TABLE Users ADD COLUMN Age INT;
--DROP: 데이터베이스, 테이블, 인덱스 등을 삭제합니다.
DROP TABLE Users;

2. 데이터 조작 언어(DML: Data Manipulation Language) - 데이터를 조작

--SELECT: 데이터베이스에서 데이터를 조회합니다.
SELECT * FROM Users;
--INSERT: 테이블에 새로운 데이터를 삽입합니다.
INSERT INTO Users (ID, Name, Email) VALUES (1, 'John Doe', 'john@example.com');
--UPDATE: 테이블의 기존 데이터를 수정합니다.
UPDATE Users SET Email = 'john.doe@example.com' WHERE ID = 1;
--DELETE: 테이블의 데이터를 삭제합니다.
DELETE FROM Users WHERE ID = 1;

3. 데이터 제어 언어(DCL: Data Control Language) - 권한을 부여하고 제어

--GRANT: 사용자에게 권한을 부여합니다.
GRANT SELECT ON Users TO 'username';
--REVOKE: 사용자에게 부여된 권한을 취소합니다.
REVOKE SELECT ON Users FROM 'username';​

4. 트랜잭션 제어 언어(TCL: Transaction Control Language) - 트랜잭션을 제어

--COMMIT: 트랜잭션에서 수행된 모든 변경 사항을 저장합니다.
COMMIT;
--ROLLBACK: 트랜잭션에서 수행된 모든 변경 사항을 취소합니다.
ROLLBACK;​

본 글에서는 주로 사용되는 조회기능인 DML의 SELECT 관련 쿼리문을 살펴본다.

SQL문 기본구조

select <필드명>or<*>
from <테이블명>
where <조건문>
group by <필드명>or<필드번호>
order by <필드명> desc /* desc = descending */

각종 함수

집계,중복제거,사잇값,일부조회

집계 count()
중복제외 distinct
사잇값 between n and m
일부조회 limit n

문자열 조작

문자바꾸기 replace(바꿀 컬럼, 현재 값, 바꿀 값)
문자나누기 substr(조회 할 컬럼, 시작 위치, 글자 수) 또는 substring(조회 할 컬럼, 시작 위치, 글자 수)
문자합치기 concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

조건문

단일조건문 if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
다중조건문 case when 조건1 then 값(수식)1 when 조건2 then 값(수식)2 else 값(수식)3 end

데이터타입변경

문자로 변경 cast(바꿀숫자데이터 as char)
숫자로 변경 cast(바꿀문자데이터 as decimal)

서브쿼리( Subquery ) - 쿼리의 객체화

select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

연결( JOIN ) - 테이블 병합

/* LEFT JOIN (기준 테이블에 결합) 테이블1에 테이블2가 결합 */
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

/* INNER JOIN (교집합 결합) 테이블1과 테이블2에 모두 값이 있는 것들만 결합*/
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

결측치 제거&대체

결측치 제거 where 컬럼 is not Null
결측치 대체 coalesce(조회 할 컬럼, Null일 경우 대체값)

이상치 제거&대체

이상치 제외 case when 조건문 then null else 기존값 end
이상치 대체 case when 조건문 then 대체값 else 기존값 end

응용

Pivot Table 만들기

--1) 비교 행,열 별 값 조회

/*
select 컬럼1,
       컬럼2,
       count(1) 대체명
from 테이블
where 조건문
group by 1, 2
*/

select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2

--2) subquery 활용해 pivot table 제작

select restaurant_name,
       max(if(hh='15', cnt_order, 0)) "15",
       max(if(hh='16', cnt_order, 0)) "16",
       max(if(hh='17', cnt_order, 0)) "17",
       max(if(hh='18', cnt_order, 0)) "18",
       max(if(hh='19', cnt_order, 0)) "19",
       max(if(hh='20', cnt_order, 0)) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

Window Function - 각 행의 관계를 정의

ex) rank, sun, avg
--RANK
rank() over (partition by 그룹 기준 컬럼 order by 정렬 기준)
--SUM 단순합
sum(argument) over (partition by 그룹 기준 컬럼)
--SUM 누적합
sum(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

형태변환 - formating

date(문자열) → [datetime] type변환
date_format(날짜데이터,'형식') → [string] 형식+type변환


select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments