KOMAS

---

문자열 함수

 

문자열 함수에서 인자가 여러 개일 때 적용대상 @column 위치는 거의 맨 앞이다.

 

concat

select concat(@column, " ", @column) from books;

 

concat_ws

select concat("구분자", @column, @column) from books;

 

substr

substring

substring(@column, start, end) 앞 ~ 뒤 [모두 포함]

substring(@column, start) 앞부터 count

substring(@column, -start) 뒤부터 count

 

replace

replace(@column, "바뀌기 전 문자", "바뀐 후 문자")

 

reverse

reverse(@column)

 

char_length

char_length(@column)

 

upper

대문자로 변경

 

lower

소문자로 변경

 

LPAD

lpad(@column, 총길이, 채울 문자)

 

RPAD

rpad(@column, 총길이, 채울 문자)

 

Binary

binary(@column)  대소문자를 구분한다.

@sql 

where binary(name) like "%C%"

 

 

---

 

형 변환

cast(@column as type)

type - unsigned, signed, decimal, char, date, datetime

 

---

숫자 함수

 

ABS(숫자) : 절댓값

 

CEIL(숫자) : 값보다 큰 정수 중 가장 작은 정수 // 4.7 -> 5

FLOOR(숫자) : 값보다 작은 정수 중 가장 큰 정수 // 4.7 -> 4

ROUND(숫자, 자릿수) : 자릿수 기준 반올림 

TRUNCATE(숫자, 자릿수) : 자릿수 기준 버림

 

POW(X, Y)  : 제곱

SQRT(숫자) : 제곱근

MOD(분자, 분모) : 나머지

 

GREATEST(숫자1, 숫자2, ...) : 가장 큰 수

LEAST(숫자1, 숫자2, ...) : 가장 작은 수

 

---

날짜 함수

 

year(), month(), day(), hour(), minute(), second()

 

date(), time()

 

date_format(@column , '% H')

문자를 얻을 땐 % 대문자

숫자를 얻을 땐 % 소문자

 

date_diff(@column , INTERVAL 1 MONTH)

date_add(@column , INTERVAL 1 MONTH)

 

 

datetime +- INTERVAL expr unit

 

@sql

• SELECT DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people
• SELECT DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people;
• SELECT DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people;
• SELECT birthday + INTERVAL 1 MONTH FROM people;
• SELECT birthday - INTERVAL 5 MONTH FROM people;

 

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

 

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

12.7 Date and Time Functions This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats

dev.mysql.com

 

 

 

---

랭크

 

rank() over (order by @column [asc or desc]) // 중복, 순위 밀림 => 1, 1, 1, 4 ...

dense_rank() over (order by @column [asc or desc]) // 중복, 순위 밀리지 않음 => 1, 1, 1, 2, 2 ...

row_number() over (order by @column [asc or desc])  //  순서대로

select 절에서 만들어지기 때문에 Oracle rownum처럼 추가로 view로 만들어서 정렬할 필요가 없다.

 

---

문법

 

distinct 

중복 행 제거 (컬럼이 아닌, 행 전체 기준)

 

order by

[option] asc, desc 

 

limit

0부터 N까지 조회

특이한 점은 전체를 조회할 때 N을 큰 숫자로 지정함

ex) limit 0, 19221032181328091

 

like, not like

where @column like '% 문자%' // 모든 문자, % 자리에 어떠한 문자도 없어도 조회

where @column like '_문자' // 한 개, 숫자 자릿수도 가능

 

between

@column between 3 and 15 // 3 이상 15 이하

 

if(조건식, true일 경우, false일 경우)

 

ifnull(@column, null일 경우)

 

---

집계 group by

 

mysql5 -> mysql8 변경사항

 

 

select min(stock_quantity), title

from books

group by released_year;

min(stock_quantity)는 집계 함수인 반면

title은 집계 함수의 결과가 아니고, group by의 대상도 아니라서

여러 값이 존재해서 오류를 일으키거나, 임의의 값을 가져오게 된다.

 

 

select min(stock_quantity), title 

from books

group by title;

title이 group by의 대상이라서, 대표 값이 명확히 있기 때문에 가능하다.

 

 

group by 이후에 min(stock_quantitiy) 행을 보고자 할 때 서브 쿼리를 활용

select * 

from books 

where stock_quantity 

in  (select min(stock_quantity)

       from books

       group by released_year);

 

 

group by 

select집계 함수, group by 대상만 사용 가능

 

집계 함수

count(), min(), max(), avg(), sum()

집계함수는 중첩할 수 없다. max(count(@Column)) 불가능

 

 

null 값을 집계 함수에 포함시키려면 IFNULL, CASE를 사용하면 된다.

EX) SUM(IFNULL(<COLUMN>, 1)

 

---

분기문

 

if (조건식) then

     명령문

elseif (조건식) then

     명령문

else

     명령문

end if

 

---

 

case

      when (조건식) then 명령문

      when (조건식) then 명령문

      when (조건식) then 명령문

      else

end case

 

---

 

while(조건식) do

    명령문

end while

 

 

 

---

조인

 

join(기본 조인), inner join(내부 조인), equi join(동등 조인)은 전부 다 같다.

natural join - join을 수행한다. (단, 조인에 사용한 열은 한 번만 추출)

 

마찬가지로

left outer join과 left join의 차이는 없다.

 

명시하는 방법 때문에 종류가 많아 보이지만 사실 같은 것이 많다. 헷갈리지 말자.

 

조인 종류 

inner join

 

left join

right join

full outer join [mysql 에선 지원하지 않는다.]

 

self join

 

cross join

 

---

@sql

SELECT g1.pg, g1.af, b.title FROM
(select max(pages) as pg, author_fname as af
 from books group by author_fname) g1
JOIN books b
ON g1.pg = b.pages AND g1.af = b.author_fname;

 

• from 부속 질의로 필요한 테이블을 만들고 조인하면 유용하다.
• join 조건에 and를 사용하여 더 분명하게 join 해줄 수 있다.

 

ON table.col BETWEEN 10 AND 20

 

• join 조건에 between으로 범위 조인도 가능하다.

 

---

 

UNION

관계없는 테이블을 합칠 수 있음

필드명(as 가능)과 데이터 타입이 같아야 함

중복 값은 기본적으로 제거되지만 필요한 경우 UNION ALL

 

@sql

(select city, char_length(city) from station order by char_length(city) asc, city asc limit 1)
union
(select city, char_length(city) from station order by char_length(city) desc, city asc limit 1);

 

---

부속 질의

 

select

scalar 부속 질의 (단일행, 단일열)

 

from

inline-view (가상의 테이블을 일시적 생성)

 

where

중첩 부속 질의 

단일 행 단일 열 - 비교 연산자

다중 행 단일 열 - all, any

다중 행 다중 열 - in, exists

 

 

---

 

단일 행 단일 열 

비교 연산자, select에서도 사용 가능한 scalar 부속 질의

 

다중 행 단일 열   ALL, ANY

any는 부속 질의의 결과가 하나라도 만족하면 true가 된다.

단일 열 이어야 한다.

 

다중 행 다중 열  IN, EXISTS

select * from students s 
join papers p 
on s.id = p.student_id 
where (p.grade, p.student_id) in ( (60, 1), (75, 1) );    # 괄호 필수

 

 

 

 

 

 

 

 

 

 

 

 

---

 

SQL 프로그래밍

 

delimiter를 변경해주는 이유는 선언 시에 세미콜론이 문장의 끝이 아님을 알려줘야 하므로 변경을 해준다.

 

변수 선언과 대입

set @변수명 = 대입값;

 

 

프로시저

프로시저를 create할 때는 종료 문자 delimiter 변경해주어야 한다.

프로시저 안에서 사용되는 변수 선언 declare 변수명 타입 [default n]

변수의 값 변경은 set

 

 

drop procedure if exists testProcedure;
delimiter $$
create procedure testProcedure(
    in num1 int,           // input
    in num2 int,
    out num3 int,       // output
    out num4 int

)
begin 

   declare num5 int default 5;    // 변수 선언

   set @num10 = 10;    // 변수 선언 대입

    set num3 = num1 + num2;        // 값 변경
    set num4 = num1 - num2;
end $$
delimiter ;

set @add = 0;
set @sub = 0;

call testProcedure(5, 10, @add, @sub);

 

 

 

---

 

함수

프로시저와의 차이점은 파라미터 in, out 이 없다. 입력만 가능하다.

return 에 반환 데이터 형식을 지정해야 한다.

함수 호출은 select 문장 안에서 호출한다.

함수 내부에서 select 사용이 불가능하다.

 

 

---

 

커서

 

# 프로시저 내부

 

declare EOR boolean default false;   // 행의 끝을 알릴 변수 

declare 커서명 cursor for (select sql)   // 커서 선언

declare CONTINUE HANDLER for not found set EOR = true;   // 더 읽을 행이 없을 때 동작할 명령어

 

open 커서명

 

cursor_loop  : loop

    fetch 커서명 into 저장할 변수 // 커서 선언 -> 결과 sql 칼럼 개수에 맞게 변수 설정

 

    if EOR then leave cursor_loop   // 더 읽을 행이 없으면 true

    end if

 

    set sum = sum + 결과 변수

end loop cursor_loop

 

close 커서명

 

 

mysql, rownum 만들기
select @rownum:= @rownum +1 as rownum, t.* 
from table1 t, (select @rownum := 0) r;

 

 

---

 

트리거

삽입, 삭제, 갱신이 일어나면 자동으로 작동되는 개체

삽입, 삭제, 갱신 이전 테이터를 저장 등을 할 수 있다.

after 트리거와 before 트리거 가 있다.

 

create trigger 트리거명 

    after DELETE // 삭제 이후

    on 테이블 // 트리거를 부착할 테이블

    for each row // 각 행마다 적용

begin

    동작할 명령어

end 

 

 

 

---

뷰

create view 뷰네임 as 쿼리

필요한 정보만 노출시켜 보안에 도움이 된다.

복잡한 쿼리를 뷰로 미리 생성해놓고 단순한 쿼리로 조회, 사용할 수 있다.

 

 

---

인덱스

 

검색 속도가 향상된다.

추가적인 공간이 10% 정도 필요하다.

변경 작업이 자주 발생할 경우 오히려 성능이 낮아진다.

 

 

클러스터드 인덱스 

테이블당 하나만 생성 가능

영어사전처럼 정렬을 인덱스에 맞춰서 해놓는다.

기본키를 지정하면 기본키 정렬에 맞게 클러스터드 인덱스가 자동 생성된다.

클러스터드 인덱스는 기본키에 unique not null 칼럼보다 우선 적용된다.

 

보조 인덱스 (비 클러스터드 인덱스와 비슷한 개념)

여러 개 생성 가능

책의 찾아보기처럼 별도로 존재한다.

칼럼을 unique null로 지정하면 보조 인덱스가 자동 생성된다.

 

 

 

 

 

 

---