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;
