SQL 명령어 사용법 기억날 때 마다 와서 써놓기

CREATE

• 데이터베이스 새로 생성할 때

CREATE DATABASE 데이터베이스이름;

• 테이블 생성할 떄

CREATE TABLE 테이블 이름 (
    컬럼1, 데이터타입,
    컬럼2, 데이터타입,
    ....
);


예시
CREATE TABLE STUDENT (
    ID INT,
    NAME VARCHAR(50),
    AGE INT
);

 

LIMIT

조회할 데이터의 개수를 제한한다.

SELECT NAME, AGE FROM STUDENTS LIMIT 5;
//STUDENTS 테이블에서 NAME과 AGE를 조회하되, 최대 5개의 행만 조회하라

 

 

INSERT

테이블에 새로운 데이터를 추가할 때는 INSERT INTO를 사용한다.

INSERT INTO 테이블이름(컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...)

//예시
INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (1, 'ALICE', 23);

 

 

UPDATE

기존 데이터를 수정할 때 사용한다.

조건 제대로 안주면 전부다 바뀌니까 잘 사용해야 한다.

UPDATE 테이블명 SET 컬럼1 = 값1, 컬럼2 = 값2 WHERE 조건;

//예시
UPDATE STUDENTS SET AGE = 24 WHERE ID = 1;
//STUDENTS 테이블에서 ID가 1인 학생의 AGE 값을 24로 수정

 

 

DELETE

테이블에서 데이터를 삭제할 때는 DELETE FROM를 사용한다.

DELETE FROM 테이블명 WHERE 조건;

//예시
DELETE FROM STUDENT WHERE ID = 1;

 

 

PRIMARY KEY(기본 키), FOREIGN KEY(외래 키)

기본 키 : 테이블에서 각 행을 고유하게 식별하는 열(또는 열의 조합)

외래 키 : 다른 테이블의 기본 키를 참조하는 열로, 테이블 간의 관계를 나타낸다.

CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    STUDENT_ID INT,
    FOREIGN KEY (STUDENT_ID) REFERENCES STUDENTS(ID)
);
STUDENT_ID = 정수형 데이터를 저장하는 열로, STUDENTS 테이블의 ID를 참조하는 외래 키이다.
이를 통해 ORDERS 테이블의 각 주문이 어떤 학생과 관련이 있는지를 나타낼 수 있다.

 

 

 

COALESCE

두 컬럼을 병합하여 NULL이 아닌 값을 반환할 때 사용한다.

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

SELECT
    COALECSE(ORDER_ID, CUSTOMER_ID)
FORM food_orders

위의 셀렉트문 처럼 쓰면 b.age 컬럼에서 null 값 대신 20 넣어주세요.

 

아래의 셀렉트문 처럼 쓰면 ORDER_ID, CUSTOMER_ID에서

1번 컬럼만 NULL인 경우 2번 컬럼 값 출력,

2번 컬럼만 NULL 인경우 1번 컬럼 값 출력,

1, 2번 컬럼 둘다 값이 있을 경우 1번 컬럼 값 출력,

둘다 NULL인 경우 NULL값을 출력한다.

 

WINDOW_FUNCTION

각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어 준다.

 

기본 구조

WINDOW_FUNCTION(ARGUMENT) OVER( PARTITION BY 그룹 기준 컬럼 ORDER BY 정렬 기준)

 

WINDOW_FUNCTION : 기능 명을 사용해준다.(SUM,AVG,RANK 등등)

ARGUMENT : 함수에 따라 작성하거나 생략한다.

 

OVER() (이친구도 WINDOW_FUNCTION임)

PARTITION BY : 그룹을 나누기 위한 기준이다. GROUP BY 절과 유사하다.

ORDER BY : WINDOW FUNCTION을 적용할 때 정렬 할 컬럼 기준을 적어준다.

SUM(CNT_ORDER) OVER(PARTITION BY CUISINE_TYPE) AS SUM_CUISINE,
SUM(CNT_ORDER) OVER(PARTITION BY CUISINE_TYPE ORDER BY CNT_ORDER) AS CUM_CUISINE

CNT_ORDER 컬럼을 더하는데 CUISINE_TYPE 기준으로 더해줘. -> 첫행부터 총합이 나옴

CNT_ORDER 컬럼을 더하는데 CUISINE_TYPE 을 기준으로 CNT_ORDER 오름차 순 정렬해서 더해줘 -> 누적합

https://velog.io/@wltn716/SQL-Over-%EC%A0%88

참고하면 좋은 블로그 주소

 

DATE, DATE_FORMAT, 현재시간(날짜)

• DATE

어떤 컬럼의 타입을 DATE 타입으로 변환하여 보여주는 함수.

DATE(컬럼명)

 

• DATE_FORMAT

이 함수는 DATE 타입 컬럼을 년, 월, 일, 주(요일) 로 조회가능하게 해주는 함수이다.

DATE_FORMAT(컬럼, '%Y%M%D%W')

%Y = 년도 숫자 네 자리 전부 %y = 년도 숫자 뒤에 두자리

%M = 월을 영어로 나타냄 %m = 월을 숫자로 나타냄

&D = 일을 1st, 2nd 방식으로 나타냄 &d = 일을 숫자로 나타냄

&W = 영어로 요일을 나타냄 &w = 일요일부터 토요일을  0 ~ 6 으로 나타냄

%H = 24시간 형식으로 시간을 나타냄 %h = 12시간 형식으로 나타냄

%i = 분을 숫자 두자리로 나타내며 대문자는 없음

%s =초를 숫자 두자리로 나타내며 대문자는 없음

 

SELECT
	DATE_FORMAT(DATE(DATE), '%Y') AS '년',
	DATE_FORMAT(DATE(DATE), '%M') AS '월',
	DATE_FORMAT(DATE(DATE), '%Y%m') AS '년월',
	//DATE_FORMAT(DATE(DATE), '%w') AS '주',
	COUNT(1) AS '주문건수' 
FROM food_orders FO INNER JOIN payments P ON FO.order_id = P.order_id
WHERE DATE_FORMAT(DATE(DATE), '%m') = '03' 
GROUP BY 1, 2, 3
ORDER BY 1

//DATE 컬럼을 DATE 함수로 DATE 타입으로 변경했고 
//그 컬럼을 DATE_FORMAT을 사용하여 원하는 년도나 월을 가져와 활용하는 모습

 

 

• CURDATE

현재 날짜를 가져옴.

형식변환 없이 사용하면

네 자리 년도-두 자리월-두 자리 날이 숫자로 나옴.

 

• CURTIME

현재 시간을 가져옴

두 자리 시간:두 자리 분:두 자리 초 나옴

근데 시간이 잘못나와서 찾아보니 기본적으로 UTC로 시간이 맞춰져 있어서 현재시간보다 -09시간 이라는 거 였음..

select @@global.time_zone, @@session.time_zone,@@system_time_zone;
로 시간 참조하고
SET time_zone='+09:00';
이런식으로 시간을 더해서 맞춘다.
9시간 차이난다는게 대부분이니 09시 그대로 쓰면 될듯.
9시간 차이 아니면 자기가 수정해서 써야함

 

 

UTC라 9시간 차이남

지금 14시 24분인데 이럼

SET TIME_ZONE = '+09:00'

 

위 코드로 9시간 더해줌

 

시간 정상화

• NOW

현재 날짜와 시간이 다 나옴.

위의 두함수의 결과를 합한거임.

쿼리가 실행되는 시간을 가져옴

여러 칼럼에 들어가려면 이게 밑에 함수보다 나음

 

• SYSDATE

현재날짜와 시간을 가져옴.

함수가 실행되는 시간을 가져옴.

느린 쿼리를 사용할 경우 시간이 틀려질 수 있다.

 

 

DATEDIFF, TIMESTAMPDIFF

• DATEDIFF

두 날짜 간의 차이를 구해준다.

DATEDIFF('기준이될 날짜형식 컬럼 또는 임의의 날짜', '앞의 기준에서 뺄 날짜')

 

 

• TIMESTAMPDIFF

두 날짜간의 시간차이를 구해준다.

TIMESTAMPDIFF(시간표현단위, 시작체크시간, 종료체크시간)

시간표현 단위

SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

 

 

ROUND, TRUNCADE, FLOOR, CEILING

• ROUND

정해진 자릿 수에 따라 반올림을 해주는 함수이다.

ROUND(컬럼, 반올림기준)

반올림 기준은 필수가 아니며 지정하지 않을 경우 소수점 첫번쨰 자리를 사용한다.

 

SELECT ROUND(10.126) -- 10
SELECT ROUND(10.126, 1) -- 10.1
SELECT ROUND(10.126, 2) -- 10.13


SELECT ROUND(10.126, -1) -- 10
SELECT ROUND(24, -1) -- 20
SELECT ROUND(26, -1) -- 30
SELECT ROUND(150, -2) -- 200

보다시피 기준에 양수를 붙이면 소수점 몇번째 자리에서 반올림을,

음수를 붙이게 되면 소수점이 아닌 정수쪽 몇번째 자리에서 반올림을 한다.

 

• TRUNCADE

정해진 자릿 수에 따라 버림을 해주는 함수이다.

TRUNCADE(컬럼, 버림기준)

SELECT TRUNCATE(10.12345, 0) -- 10
SELECT TRUNCATE(10.12345, 1) -- 10.1
SELECT TRUNCATE(10.12345, 2) -- 10.12
SELECT TRUNCATE(10.12345, 5) -- 10.12345

SELECT TRUNCATE(125.1234, -1) -- 120
SELECT TRUNCATE(125.1234, -2)  -- 100

 

보다시피 기준에 양수를 붙이면 소수점 몇번째 자리에서 버림을,

음수를 붙이게 되면 소수점이 아닌 정수쪽 몇번째 자리에서 버림을 한다.

 

 

• FLOOR

소수점 이하를 무조건 버린다.

무조건 버림 처리하기 때문에 자릿수 지정이 없다.

FLOOR(숫자)

SELECT FLOOR(10.12316) -- 10

 

 

• CEILING

소수점 이하를 무조건 올린다.

무조건 올림 처리하기 때문에 자릿수 지정이 없다.

CEILING(숫자)

SELECT CEILING(10.56) -- 11