[필기정리] Day59 - CURRENT_TIMESTAMP, DATE FORMAT 등

# 시스템 날짜
- CURRENT_TIMESTAMP : 시스템 날짜 확인

SELECT CURRENT_TIMESTAMP;

 

CURRENT_TIMESTAMP는 표준 SQL로 규정되어 있는 함수이다. 

Oracle에서는 SYSDATE 함수, SQL Server에서는 GETDATE 함수를 사용해도 시스템 날짜를 확인할 수 있다.

- DATE FORMAT(날짜, 출력 형식) : 날짜를 문자열로 변환  - MySQL 

SELECT now(); 
SELECT DATE_FORMAT(now(), '%Y/%M/%D'); 
SELECT DATE_FORMAT(now(), '%Y/%m/%D');

↕

- STR_TO_DATE(문자, 출력 형식) : 문자열을 날짜로 변환

SELECT STR_TO_DATE('2008-01-01', '%Y-%m-%D');

날짜 시간형 데이터는 기간형 수치데이터와 덧셈 및 뺄셈을 할 수 있다. 

날짜 시간형 데이터에 기간형 수치데이터를 더하거나 빼면 날짜시간형 데이터가 반환된다. 

예를 들어 특정일로부터 1일 후를 계산하고 싶다면 a + 1 DAY 라는 식으로 계산할 수 있다. 

1일 전이라면 a- 1 DAY로 하면 된다.

CURRENT_DATE는 시스템 날짜의 날짜만 확인하는 함수. 

INTERVAL 1 DAY는 '1일 후'라는 의미의 기간형 상수이다. 

기간형 상수의 기술방법은 데이터베이스마다 조금씩 다르며 세세한 부분까지 표준화가 이루어지지 않았다.

- DATEDIFF : 날짜형 간의 뺄셈

MySQL에서는 DATEDIFF를 사용하여 날짜형 간의 뺄셈을 할 수 있다.

SELECT DATEDIFF('2014-02-28', '2014-01-01');

ex) 시스템 날짜의 1일 후를 계산하기
     날짜를 연산해 시스템 날짜의 1일 후를 검색

SELECT CURRENT_DATE + INTERVAL 1 DAY;

# 날짜 및 시간 처리
- 날짜 및 시간 관련 칼럼 타입
① DATE  : 날짜 타입 

'1000-01-01'에서 '9999-12-31' 까지 나타낼 수 있다. 

기본적으로 지원하는 형태는 'YYYY-MM-DD' 이다.

② DATETIME : 날짜와 시간이 합쳐진 타입 

'1000-01-01 00:00:00'에서 '9999-12-31 23:59:59'까지 나타낼 수 있다. 

기본적으로 지원하는 형태는 'YYYY-MM-DD HH:MM:SS'이다.

③ TIMESTAMP[(M)] : 날짜 및 시간 타입 

'1970-01-01 00:00:00'에서 2037년까지 나타낸다. 

[(M)]자리에는 정밀도. 0~6을 쓸 수 있음. 

숫자를 쓰지 않으면 기본적으로 0. 

TIMESTAMP의 특징은 자동 변경 칼럼 타입이라는 것이다. 

INSERT나 UPDATE문을 사용할 때 매우 유용하다.

④ TIME : 시간 타입 

'-838:59:59'에서 '838:59:59'까지 나타낼 수 있다. 

기본적으로 지원하는 형태는 'HH:MM:SS'이다.

⑤ YEAR[(2/4)] : 연도 타입

2자리 혹은 4자리로 나타낼 수 있으며 자리수를 지정하지 않으면 기본적으로 4자리로 나타낸다.

4자리로 사용할 때는 1901년에서 2155년까지 지원하며

2자리로 사용할 때는 1970년에서 2069년까지 지원한다.

- 날짜 및 시간 관련 함수
① NOW() 또는 SYSDATE() : 현재 날짜, 시간 출력

select sysdate(), now();

② curdate() 또는 current_date() : 현재 날짜 출력

select curdate(), current_date(); 

③ curtime() 또는 current_time() : 현재 시간 출력

select curtime(), current_time();

④ dayofmonth(date) : 날짜 출력

select now(); 
select dayofmonth(now());

⑤ dayofweek(date) / weekday(date) : 숫자로 요일 출력
날짜에 대한 요일(1-일요일, 2-월요일...7-토요일)에 대한 정수를 반환

select dayofmonth(now()), dayofweek(now());

// Tip - DAYNAME 함수
이 함수는 DAYOFWEEK 와는 반대로 요일의 이름을 반환하는 함수입니다.

SELECT DAYNAME(now());

⑥ DAYOFYEAR(date) : 1년 중 며칠이 지났는가를 출력

select dayofyear(now());

⑦ date_add와 date_sub
- date_add : 날짜에서 기준값만큼 더한 값 

- date_sub : 날짜에서 기준값만큼 뺀 값
(기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)
ex)

select date_add(now(), interval 3 day), date_sub(now(), interval 3 day);

 

⑧ year, month
- year : 연도 출력

- month : 월 출력
ex)

select year(now()), month(now());

⑨ date_format(날짜, '형식') : 날짜를 형식에 맞게 출력

년도	%Y	4자리 년도	%y	2자리 년도
월	%M	긴 월 이름(January ...)	%m	숫자의 월(01 ... 12)
	%b	짧은 월 이름(Jan ...)	%c	숫자의 월(1 ... 12)
요일	%W	긴 요일 이름(Sunday ...)	%a	짧은 요일 이름(Sun ...)
일	%D	월 내에서 서수 형식의 일(1th...)	%d	월 내의 일자(01 ... 31)
	%w	숫자의 요일(0=Sunday ...)	%e	월 내의 일자(1 ... 31)
			%j	일년 중의 날수 (001 ... 336)
시	%I	12시간제의 시(1 ... 12)	%k	12시간제의 시 (0...23)
	%h	12시간제의 시(01 ... 12)	%H	12시간제의 시 (00 ... 23)
	%l	12시간제의 시(01 ... 12)
분	%i	숫자의 분(00 ... 59)
초	%S	숫자의 초(00 ... 59)	%s	숫자의 초 (00 ... 59)
시간	%r	12시간제의 시간 (hh:mm:ss AM 또는 PM)	%T	24시간제의 시간 (hh:mm:ss)
주	%U	일요일을 기준으로 한 주(0 ... 53)	%u	일요일을 기준으로 한 주(0 ... 53)
기타	%%	문자 '%'	%p	AM 또는 PM

Q. 교수테이블에서 교수코드, 교수이름, 임용일자를

    년도(4자리), 월(영문), 일(0이 포함된 날짜) 형식으로 출력하라.(haksa_database)
A.

select prof_code, prof_name, date_format(create_date, '%Y %M %d') from professor;

 

# CASE : 조건을 통해 데이터를 변환하는 것

- 검색 CASE

CASE  
WHEN 조건식1 THEN 식1 
[WHEN 조건식2 THEN 식2 ...] 
[ELSE 식3] 
END 

① WHEN 절에는 참과 거짓을 반환하는 조건식을 기술한다. 

② 해당 조건을 만족하여 참이 되는 경우는 THEN 절에 기술한 식이 처리된다.

③ 그 어떤 조건식도 만족하지 못한 경우에는 ELSE 절에 기술한 식이 채택된다. 

    ELSE는 생략 가능하며 생략했을 경우 'ELSE NULL'로 간주된다. 

 

ex)

CREATE TABLE sample37(a int); 
INSERT INTO sample37 VALUES(1); 
INSERT INTO sample37 VALUES(2); 
INSERT INTO sample37 VALUES(NULL); 

SELECT a, CASE WHEN a IS NULL THEN 0 ELSE a END "a(null=0)" FROM sample37; 

SELECT a, COALESCE(a, 0) "a의 값 중 null을 찾는용도" FROM sample37; 

 

- 단순 CASE 

CASE 식1 
	WHEN 식2 THEN 식3 
	[WHEN 식4 THEN 식5 ...] 
	[ELSE 식6] 
END

 

① 식1의 값과 WHEN의 식2의 값과 동일한지 비교한다.

② 식 1의 값이 하고, 값이 같다면 식3의 값이 CASE 문 전체의 결과값이 된다.

    값이 같지 않으면 그 뒤에 서술한 WHEN 절과 비교하는 식으로 진행된다.

③ 비교 결과 일치하는 WHEN 절이 하나도 없는 경우에는 ELSE 절이 적용된다. 

 ex) 검색 CASE로 성별코드를 남자, 여자로 변환하기

SELECT a AS "코드", 
CASE 
	WHEN a = 1 THEN '남자' 
	WHEN a = 2 THEN '여자' 
	ELSE '미지정' 
END  
AS "성별" FROM sample37;

ex) 단순 CASE로 성별 코드를 남자, 여자로 변환하기

SELECT a AS "코드", 
CASE a 
	WHEN 1 THEN '남자' 
	WHEN 2 THEN '여자' 
	ELSE '미지정' 
END  
AS "성별" FROM sample37; 

∴ CASE 문의 ELSE는 생략하지 않는 편이 낫다!

- NULL 비교는 검색 CASE만 가능하다.

SELECT a AS "코드", 
CASE 
	WHEN a = 1 THEN '남자' 
	WHEN a = 2 THEN '여자' 
	WHEN a IS NULL THEN '데이터 없음' 
	ELSE '미지정' 
END 
AS "성별" FROM sample37; 

 

# 타 DB
- DECODE (= MySQL의 CASE) 

  DECODE 함수는 Oracle에서만 지원하는 함수인 만큼 다른 데이터베이스 제품에서는 사용할 수 없다.

 
- NULL 반환 함수

  Oracle은 NVL 함수, SQL Server은 ISNULL 함수

 

- COALESCE : 여러 개의 인수 지정
  주어진 인수 가운데 NULL이 아닌 값 중 가장 먼저 지정된 인수의 값을 반환한다.  
  앞의 예문은 a가 NULL이 아니면 a값을 그대로 출력하고,

  그렇지 않으면(a가 NULL 이면) 0을 출력한다. 

- 디코드 : 문자화

  인코드 : 수치화

Q1. ORDER BY 구에 의해 정렬되는 것은 무언인가?
① 행
② 열
③ 오름차순
A. ① 행 : ORDER BY 구로 행을 정렬할 수 있다.

Q2. 식 중에 NULL 값이 포함된 경우 연산결과는 무엇인가?
① 에러가 발생한다.
② 0
③ NULL
A. ③ NULL : NULL을 포함하는 연산 결과는 모두 NULL이 된다. 0은 되지 않는다.

Q3. SELECT구로 식에 붙일 수 있는 것은 무엇인가?
① 별명
② CASE 문
③ ORDER BY
A. ① 별명 : SELECT 구에서는 식에 별명을 붙일 수 있다.

 

# 서브쿼리
서브쿼리는 SELECT 명령에 의한 데이터 질의로, 상부가 아닌 하부의 부수적인 질의를 의미한다.

(SELECT 명령)

서브쿼리는 SQL 명령문 안에 지정하는 하부 SELECT 명령으로 괄호로 묶어 지정한다. 

사용할 수 있는 곳은 SELECT 구, FROM 구, WHERE 구 등 SELECT 명령의 각 구를 기술할 수 있다.

- DELETE의 WHERE 구에서 서브쿼리 사용하기

CREATE TABLE sample54(no int AUTO_INCREMENT PRIMARY KEY, a int); 

INSERT INTO sample54 (a) values(100); 
INSERT INTO sample54 (a) values(90); 
INSERT INTO sample54 (a) values(20); 
INSERT INTO sample54 (a) values(80);

SELECT * FROM sample54; 
SELECT MIN(a) FROM sample54; # a 중 가장 작은 값 출력

 

- 최솟값을 가지는 행 삭제하기
괄호로 서브쿼리를 지정해 삭제

DELETE FROM sample54 WHERE a = (SELECT MIN(a) FROM sample54);  # MySQL Error

MySQL에서 이를 실행하면 

"You can't specify target table 'sample54' for update in FROM clause" 라는 에러가 발생한다. 

데이터를 추가하거나 갱신할 경우 동일한 테이블을 서브쿼리에서 사용할 수 없도록 되어 있기 때문이다. 

에러를 발생하지 않고 실행하려면 다음과 같이 인라인 뷰로 임시 테이블을 만들도록 처리하면 된다. 

DELETE FROM sample54 WHERE a = (SELECT a FROM (SELECT MIN(a) AS a FROM SAMPLE54) AS x); 
SELECT * FROM sample54;

 

# 클라이언트 변수
@a가 변수가 되고 set이 변수에 대입하는 명령이 된다.

set @a = (SELECT MIN(a) FROM sample54); 
DELETE FROM sample54 WHERE a = @a; 
SELECT * FROM sample54;

# 스칼라 값 (명칭 꼭 기억하기) 
서브쿼리를 사용할 때는 그 SELECT 명령이 어떤 값을 반환하는지 주의할 필요가 있다. 

여러 가지 패턴 중에서도 다음과 같은 네 가지가 일반적인 서브쿼리 패턴이다.

- 서브쿼리 패턴
① 하나의 값을 반환하는 패턴 - 스칼라 값

SELECT MIN(a) FROM sample54;

② 복수의 행이 반환되지만 열은 하나인 패턴

SELECT no FROM sample54;

③ 하나의 행이 반환되지만 열이 복수인 패턴

SELECT MIN(a), MAX(no) FROM sample54;

④ 복수의 행, 복수의 열이 반환되는 패턴

SELECT no, a FROM sample54;

 

- 스칼라 서브쿼리 : 스칼라 값을 반환하는 서브쿼리

앞서 HAVING 구를 설명할 때 '집계함수는 WHERE 구에서는 사용할 수 없다'라고 했으나,

'스칼라 서브쿼리'라면 WHERE 구에 사용할 수 있으므로 

집계함수를 사용해 집계한 결과를 조건식으로 사용할 수 있다.

그와 비슷한 문제로 

'GROUP BY에서 지정한 열 이외의 열을 SELECT 구에 지정하면 에러가 된다'라는 것도 있다. 

하나의 그룹에 다른 값이 여러 개 존재할 경우는 스칼라 값이라고 할 수 없다.

# SELECT 구에서 서브쿼리 사용하기

문법적으로 서브쿼리는 '하나의 항목'으로 취급한다. 

단, 문법적으로는 문제없지만 실행하면 에러가 발생하는 경우가 자주 있다. 

이는 스칼라 값의 반환여부에 따라 생기는 현상으로, 

서브쿼리를 사용할 때는 스칼라 서브쿼리로 되어있는지 확인해야 한다.
SELECT 구에서 서브쿼리를 지정할 때는 스칼라 서브쿼리가 필요하다.

- SELECT 구에서 서브쿼리 사용하기

SELECT  
	(SELECT COUNT(*) FROM sample51) AS sq1, 
	(SELECT COUNT(*) FROM sample54) AS sq2; 

여기서 한 가지 주의할 점이 있는데 서브쿼리가 아닌 상부의 SELECT 명령에는 FROM 구가 없다는 것이다. 

MySQL 등에서는 실제로 from 구를 생략할 수 있다. 

하지만 Oracle 등 전통적인 데이터베이스 제품에서는 FROM를 생략할 수 없다.

이 때 Oracle에서는 다음과 같이 FROM DUAL로 지정하면 실행할 수 있다.

DUAL은 시스템 쪽에서 데이터베이스에 기본으로 작성되는 테이블이다.

- SELECT 구에서 서브쿼리 사용하기(Oracle의 경우)

SELECT  
	(SELECT COUNT(*) FROM sample51) AS sq1, 
	(SELECT COUNT(*) FROM sample54) AS sq2 FROM DUAL; 

- SET 구에서 서브쿼리 사용하기

UPDATE sample54 SET a=(SELECT MAX(a) FROM sample54); # MySQL ERROR 
UPDATE sample54 SET a=(SELECT a FROM (SELECT MAX(a) AS a FROM SAMPLE54) AS x); # 인라인 뷰 형태

- FROM 구에서 서브쿼리 사용하기

SELECT * FROM (SELECT * FROM sample54) sq;

 

위의 중첩된 SELETE 구조를 '네스티드(nested) 구조' 또는 '중첩구조'나 '내포구조'라고 부른다. 

- AS : 서브쿼리 별명 지정

(단, Oracle 에서는 AS를 붙이면 에러가 발생한다. Orcale에서는 AS를 붙이지 않는다.)

ex)

SELECT * FROM(SELECT * FROM sample54) AS sq;

SELECT * FROM (SELECT * FROM (SELECT * FROM sample54) sq1) sq2;

- Oracle에서 LIMIT 구의 대체 명령

SELECT * FROM( 
	SELECT * FROM sample54 ORDER BY a DESC 
) sq 
WHERE ROWNUM <= 2;

 // rownum : 오라클에서만 사용된다.

# INSERT 명령과 서브쿼리

CREATE TABLE sample541(a int, b int);

                                                                                                 
- VALUES 구에서 서브쿼리 사용하기 

INSERT INTO sample541 VALUES( 
	(SELECT COUNT(*) FROM sample51), 
	(SELECT COUNT(*) FROM sample54) 
); 
SELECT * FROM sample541;

# INSERT SELECT
- SELECT 결과를 INSERT 하는 구문

ex)

INSERT INTO sample541 SELECT 1, 2; 
SELECT * FROM sample541;

 

SELECT 명령이 반환하는 값이 꼭 스칼라 값일 필요는 없다.

SELECT가 반환하는 열 수와 자료형이 INSERT할 테이블과 일치하기만 하면 된다.

INSERT SELECT 명령은 SELECT 명령의 결과를 INSERT INTO로 지정한 테이블에 전부 추가한다. 

SELECT 명령의 실행 결과를 클라이언트로 반환하지 않고 지정된 테이블에 추가하는 것이다. 
열 구성이 똑같은 테이블 사이에는 다음과 같은 INSERT SELECT 명령으로 행을 복사할 수도 있다.

CREATE TABLE sample542(a int, b int);

- 테이블의 행 복사하기

ex)

INSERT INTO sample542 SELECT * FROM sample541;

# 서브쿼리의 위치에 따른 명칭
- SELECT문에 있는 서브쿼리 : 스칼라 서브쿼리(값이 하나만 있는 것)
- FROM절에 있는 서브쿼리 : 인라인 뷰
- WHERE절에 있는 서브쿼리 : 서브쿼리

# 서브쿼리의 반환 값에 따른 서브쿼리 종류
- 단일 행 서브쿼리(Single-Row Subquery) : 서브쿼리의 결과가 1행
- 다중 행 서브쿼리(Multiple-Row Subquery) : 서브쿼리의 결과가 여러 행
- 다중 컬럼 서브쿼리(Multi-Column Subquery) : 서브쿼리의 결과가 여러 컬럼

# 스칼라 서브쿼리(Scala Subquery)
- SELECT문에서 사용하는 서브쿼리로 1행만 반환

# 상호연관 서브쿼리(Correlated Subquery)
- 메인쿼리의 값을 서브쿼리가 사용하고, 

  서브쿼리의 값을 받아서 메인쿼리가 계산하는 구조의 쿼리