Jaybon 의 지식창고

예제 테이블

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 즉, 증가되지 않은 수치가 출력된다.

테이블이 끝날 때까지 반복 

오라클에 인수되기 전의 버전인 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;

https://wanna-b.tistory.com/47

초기화

ALTER TABLE practice_table AUTO_INCREMENT=1;

Tue Jun 23 09:16:59 KST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

context.xml에서 위와 같이 useSSL=false 로 바꾸어준다

참고 사이트

https://leejune.tistory.com/5