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;
class file for jakarta.persistence.EntityManager not found 원인: implementation 'org.springframework.boot:spring-boot-starter-data-jpa:2.6.7를 해놓고 hibernate를 따로 implementation하여 생긴 오류
Value too long for column "WRITER CHARACTER VARYING(20)": 원인 1 : database varchar(길이) 보다 입력길이가 길어서 나는 오류 varchar길이를 늘린다.
원인 2 : 인코딩 설정이 잘못되서 문자를 길게 받아들이는 오류 application.property server.servlet.encoding.charset=utf-8 server.servlet.encoding.force=true 추가
Neither BindingResult nor plain target object for bean name 'command' available as request attribute 원인: jsp form 태그 안에서 전달받은 model.addAttribute 를 못알아내고 기본 값인 command로 인식 contoller에서 model.addAttribute("name",new dto()) 라고 했다면 jsp form 태그 시작에 modelAttribute="name"를 지정해줘야한다
다익스트라 알고리즘은 출발지 -> 경유지 -> 목적지를 이용하여 최단 경로를 구하게 된다.
이 과정을 반복하게 되면 고정된 출발지에서 모든 edge의 최단 경로를 구할 수 있다.
필요 사항
우선 순위 큐 (순서대로 확인하는 것이 아닌, 거리가 가장 짧은 것부터 수행하기 때문에 수행 시간을 단축)
고정된 출발지에서 목적지까지의 경로 값 저장 배열
최소 비용을 만들기 위해 필요한 Node의 개수는 항상 3개라고 생각해야 한다. [출발, 경유, 목적]
출발지 -> 경유지, 경유지 -> 목적지로 따로 생각해야 한다.
경유지를 반복문을 돌리므로 목적지는 복수개다.
주목할 부분은 경유지 -> 목적지 인 부분이다.
목적지였던 부분이 다음부터 경유지로 사용이 되어
경유지가 향하는 모든 edge의 비용을 확인한다.
퍼져나가는 방식으로 최종적으로 모든 노드의 최소 비용을 알 수 있다.
round 1 = 경유지 -> 목적지
round 2 = 경유지 -> 목적지
round 3 = 경유지 -> 목적지
[목적지가 최소 경로 비용으로 처리되면 다음부터는 목적지가 경유지로 쓰임]
BFS 동작 방식과 유사하다.
동작 예시
다익스트라 알고리즘은 경유지로 가장 먼저 자기 자신을 기준으로 update 한다.
1번 노드에서 3, 4번 노드를 update 시켜줄 수 있다.
[1 -> 1 -> 3] [1 -> 1 -> 4]
dist배열 = [INF, 0, INF, 6, 3, INF] // 0번은 편의를 위해 사용하지 않는다.
3, 4번 노드가 update 되고,
큐에 3, 4번 노드가 들어갔다.
4번 노드의 가중치가 가장 작으므로 우선순위 큐에 의해 가장 먼저 나온다.
4번 노드에서 2, 3번 노드를 update 시켜줄 수 있다.
[1 -> 4 -> 2] [1 -> 4 -> 3]
dist배열 = [INF, 0, 4, 4, 3, INF]
2, 3번 노드가 update 되고,
큐에 2, 3번 노드가 들어갔다.
현재 큐에 3번 노드가 두 번 들어갔다.
이 경우는 먼저 들어간 3번 노드는 가중치가 6으로 dist [3]의 값보다 크므로 사용되지 않고 버려진다.
1 -> 1 -> 3 경로보다 1 -> 4 -> 3 경로가 최소 비용이므로 1 -> 4 -> 3을 사용하면 되기 때문이다.
(update 된 거리보다 크다면, update 한 노드는 큐에 들어간 것이므로 최소가 보장. 버려도 되는 이유)
이 같은 방식으로 시작 노드를 기준으로 퍼져나가며 모든 노드의 최소 비용을 계산한다.
다익스트라 알고리즘은 방향 그래프여야 하며, 거리가 음수면 안된다.
거리가 음수, 그리고 사이클이 존재한다면 거리를 계속 줄이는 음수 사이클이 발생. -무한대 형성
public class Dijkstra {
static int[] dist;
static int[] path; // 자신의 앞 경로를 갖고 있다. ** 모든 경로를 확인할 수 있다.
static List<List<Node>> list = new ArrayList<>(); // 간선들
static final int edgeCount = 5 + 1;
static final int vCount = 7;
public static void main(String[] args) {
//노드 5개
dist = new int[edgeCount]; // 0 사용안함.
path = new int[edgeCount];
//간선 7개
for(int i = 0; i<=vCount; i++) {
list.add(new ArrayList<>());
}
// (1 - > 5) 2000 쓰는 경로
list.get(1).add(new Node(4, 2000));
list.get(1).add(new Node(5, 2000));
// (1 -> 5) 10 쓰는 경로
list.get(1).add(new Node(2, 5));
list.get(2).add(new Node(5, 5));
// (1 -> 5) 3 쓰는 경로
list.get(1).add(new Node(3, 1));
list.get(3).add(new Node(4, 1));
list.get(4).add(new Node(5, 1));
// TODO: 이 부분 주의 자신은 0으로 만들어야함
// 자기 자신 빼고 나머지 INF 로 초기화
Arrays.fill(dist, 100_000_000);
dist[1] = 0; // 시작
dijk();
// 결과
System.out.println(Arrays.toString(dist));
// 경로
System.out.println(Arrays.toString(path));
}
// TODO: 출발지 1번은 계속 고정
public static void dijk(){
Queue<Node> q = new PriorityQueue<>((o1,o2) -> o1.w - o2.w);
q.add(new Node(1, 0)); // 경유지, 비용 (1 -> 1 -> n)
while(!q.isEmpty()) { // TODO: 경유지 뽑기 // 1 -> M
Node mid = q.poll(); // 1과 연결된 경유지
// 과거 , 현재
if(mid.w > dist[mid.dest]) // update 된 거리보다 큐에서 뽑은게 더 크면 사용 안하고 버림
continue;
// TODO: 경유 -> 도착 꺼내는 부분
for(Node d : list.get(mid.dest)){
if(dist[d.dest] > dist[mid.dest] + d.w){ // 갱신이 되는 부분.
dist[d.dest] = dist[mid.dest] + d.w;
// 갱신이 된 것. 1->n , n->M
// (1 -> 도착지) [[ (1 -> 경유(테이블) + 경유(테이블) - > 도착지 ]]
// 계속 갱신 계속 생신
// TODO: 현재 목적지가 다음부터는 경유지로 쓰인다.
q.offer(new Node(d.dest, dist[d.dest]));
// 갱신된 (1 -> 목적지) 를 넣어준다.
// 갱신된걸 넣지 않으면 for 부분을 또 확인하기 때문에 갱신된 값을 넣어주는게 좋다.
path[d.dest] = mid.dest; // 길찾기 용도 (자신의 앞 노드)
}
}
}
}
}
// 경유지로도 쓰고, 목적지로도 쓰임
class Node{
int dest; // 도착지
int w; // 가중치
Node(int dest, int w){
this.dest = dest;
this.w = w;
}
public String toString(){
return this.dest + " " + this.w;
}
}
