문자열 함수
문자열 함수에서 인자가 여러 개일 때 적용대상 @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
랭크
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로 지정하면 보조 인덱스가 자동 생성된다.
'CS > Database' 카테고리의 다른 글
[데이터베이스] 무결성 제약 조건 (0) | 2022.06.15 |
---|---|
[데이터 베이스] 제 4 정규형, 제 5 정규형 (0) | 2022.06.13 |
[MYSQL] 각종 도구 사용 명령어 (0) | 2022.05.03 |
[데이터베이스] 자연 키 vs 대리 키 (0) | 2022.04.22 |
[데이터베이스] SQL 순서 (문법과 실행 순서) (0) | 2022.04.05 |