Jaybon 의 지식창고

스칼라 서브쿼리를 사용해서 이름 순으로 순위를 매길 수 있다

select name, (select count(*)+1 from player WHERE name < p.name) rank
from player p
GROUP BY name;

예제 테이블

create table sakila.cal(
    week varchar(100),
    day varchar(3),
    num_day varchar(100) 
) engine=InnoDB default charset=utf8;

예제 데이터

Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','일','1');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','월','2');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','화','3');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','수','4');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','목','5');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','금','6');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('1','토','7');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','일','8');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','월','9');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','화','10');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','수','11');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','목','12');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','금','13');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('2','토','14');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','일','15');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','월','16');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','화','17');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','수','18');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','목','19');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','금','20');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('3','토','21');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','일','22');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','월','23');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','화','24');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','수','25');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','목','26');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','금','27');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('4','토','28');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','일','29');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','월','30');
Insert into sakila.CAL (WEEK,DAY,NUM_DAY) values ('5','화','31');

주, 요일, 날짜가 있는 테이블

SELECT * FROM sakila.cal;

이해를 돕기위해 순차적으로 진행할 것이다.

1. 첫째 주

SELECT * FROM sakila.cal WHERE week LIKE '1' ;

첫째 주의 데이터가 모두 출력되었다.

2. 첫째 주의 일요일만 출력

SELECT if(DAY ='일', NUM_DAY,"") "일" FROM sakila.cal WHERE week LIKE '1' ;

조건 문을 걸어서 첫째주의 일요일은 출력하고 일요일이 아니면 공백으로 두게 하였다.

3. 첫째 주를 MAX로 주어서 일요일만 출력

SELECT MAX(if(DAY ='일', NUM_DAY,"")) "일" FROM sakila.cal WHERE week LIKE '1';

GROUP BY가 없어도 열이 하나이기 때문에 문제없이 출력된다.

공백("")과 "1"에서 MAX값은 "1"이다. (반대로 MIN을 주면 공백이 출력될 것이다.)

4. 모든 주를 그룹으로 묶고 일요일만 출력

SELECT MAX(if(DAY ='일', NUM_DAY,"")) "일" FROM sakila.cal GROUP BY week;

week를 그룹으로 묶고 일요일만 출력해보자.

5. 모든 주를 그룹으로 묶고 모든 요일 출력

SELECT
  MAX(if(DAY ='일', NUM_DAY,"")) "일", 
  MAX(if(DAY ='월', NUM_DAY,"")) "월",
  MAX(if(DAY ='화', NUM_DAY,"")) "화", 
  MAX(if(DAY ='수', NUM_DAY,"")) "수",
  MAX(if(DAY ='목', NUM_DAY,"")) "목", 
  MAX(if(DAY ='금', NUM_DAY,"")) "금",
  MAX(if(DAY ='토', NUM_DAY,"")) "토"
FROM CAL
GROUP BY WEEK
ORDER BY WEEK;

달력이 완성되었다.

번외

1. 그룹으로 묶지 않고, Max를 주지 않았을 경우

SELECT
  if(DAY ='일', NUM_DAY,"") "일", 
  if(DAY ='월', NUM_DAY,"") "월",
  if(DAY ='화', NUM_DAY,"") "화", 
  if(DAY ='수', NUM_DAY,"") "수",
  if(DAY ='목', NUM_DAY,"") "목", 
  if(DAY ='금', NUM_DAY,"") "금",
  if(DAY ='토', NUM_DAY,"") "토"
FROM CAL
ORDER BY WEEK;

공백인 행까지 모두 나온다.

MySQL의 번들 스키마 sakila의 film을 이용한다

SELECT title, length FROM sakila.film;

랭킹 매기기 (공동순위 X)

영화의 길이로 랭킹을 매길 것이다.

SELECT title, length, @curRank := @curRank + 1 AS rank
FROM      film f, (SELECT @curRank := 0) r
ORDER BY  length desc;

해석 :

FROM 절의 인라인 서브쿼리 SELECT문은 변수를 생성하기 위해서 부른 것이다.

curRank를 0으로 초기화 해서 불렀다. 

서브쿼리는 r 같은 별칭을 달아줘야한다.

본 쿼리에서 SELECT문에서 length를 호출하는 순간 ORDER BY가 호출되는 듯 하다.
( 내 임의로 생각한 예측임- 그러지 않으면 랭킹이 매겨질 수 없다.)

그리고 3번째 컬럼 @curRank := @curRank + 1 AS rank 

curRank변수에 1을 더하고 (0+1), 컬럼명을 rank로 한다.

테이블이 끝날 때까지 반복

결과

랭킹 매기기 (공동순위 O)

같은 길이의 영화는 같은 랭킹이 되도록 할 것이다.

SELECT
	title,
	length,
	if(length < @prevLength, if(length = (@prevLength := length), @curRank := @curRank + 1, 0), @curRank) AS rank
FROM film f, (SELECT @curRank := 0, @prevLength := 9999) r
ORDER BY  length desc;

해석 :

FROM 절의 인라인 서브쿼리 SELECT문은 변수를 생성하기 위해서 부른 것이다.

curRank를 0으로 초기화 해서 불렀다. 

prevLength를 9999로 초기화 해서 불렀다.

서브쿼리는 r 같은 별칭을 달아줘야한다.

본 쿼리에서 SELECT문에서 length를 호출하는 순간 ORDER BY가 호출되는 듯 하다.
( 내 임의로 생각한 예측임- 그러지 않으면 랭킹이 매겨질 수 없다.)

3번째 컬럼에 조건문을 건다.

조건문의 구조는 IF( 조건, 조건이 true일 때 실행, 조건이 false 일 때 실행 ) 이다.

현재 행의 length가 prevLength보다 작다면  true이기 때문에 2번째 칸의 코드를 실행한다.
(  if(length = (@prevLength := length), @curRank := @curRank + 1, 0)   )

난해할 수 있지만 이렇게 만든 이유가 있다.

조건은 (length < @prevLength) 이미 달성되었지만 남은 숙제가 있다.

prevLength 값을 현재 length로 바꿔줘야하는데 출력은 또 rank로 해줘야하는 것이다.

일단 첫번째 칸인 조건  ->  length = (@prevLength := length)

@prevLength := length로 현재 length로 바꿔주고 length 와 비교하는 조건문을 만들었다. 무조건 true가 떨어진다.

그 후 true 실행문인 @curRank := @curRank + 1 를 실행한다. 즉, 증가된 숫자로 바꾼뒤 출력한다. (0 -> 1)

반대로 현재 행의 length가 prevLength와 같다면 false가 되서 curRank 즉, 증가되지 않은 수치가 출력된다.

테이블이 끝날 때까지 반복 

-----------------------

데이터베이스에는

undo

redo

[홍길동 5000원] 일경우

10000원으로 업데이트하고 커밋을 아직 안했을 경우

undo 로그에는 [홍길동 5000원]의 값이 저장되어 있다 (커밋하기 전의 데이터)

롤백하면 undo로그에서 값을 가져온다

1번유저가 트랜잭션중

2번유저가 들어오면

select를 처음 끝 두번하게되면

처음엔 커밋전이라 5000원

끝엔 커밋후라  10000원을 보게된다

oracle은 READ COMMITTED

MySql은 Repeatable Read

한번 undo를 봤다면 끝까지 같은 값을 본다 - 정합성 문제가 발생하지 않는다

https://joont92.github.io/db/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EA%B2%A9%EB%A6%AC-%EC%88%98%EC%A4%80-isolation-level/

커밋 리드

언커밋 리드

겟인데어 41~ 강

--------------------------

오라클에 인수되기 전의 버전인 5.7.30을 다운로드

https://developer-joe.tistory.com/113

blog.naver.com/callsonda/220363810001

https://extbrain.tistory.com/58

실제사용

ALTER TABLE `test_sch`.`practice_table` 
DROP COLUMN `status`,
ADD COLUMN `class_id` INT NULL AFTER `room`,
ADD INDEX `class_id_idx` (`class_id` ASC);
;
ALTER TABLE `test_sch`.`practice_table` 
ADD CONSTRAINT `fk_class_id`
  FOREIGN KEY (`class_id`)
  REFERENCES `test_sch`.`class_table` (`id`)
  ON DELETE CASCADE
  ON UPDATE SET NULL;