MySQL - Revisiting Data Types 중간문제

Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

172강

show databases;
use new_testing_db;
show tables;

-- 1 What's a good use case for CHAR? => 길이가 고정된 문자열. ex) NY등 수도 축약문자, M or F(남 혹은 여), AM or PM CHAR는 고정된 타입이기에 
-- 속도가 빠르다는 장점이 있다.

-- 2
CREATE TABLe inventory(
item_name VARCHAR(100),
price DECIMAL(8,2),
quantity int
);

-- 3 What's the difference between DATETIME and TIMESTAMP 
-- DATETIME은 4바이트, TIMESTAMP는 8바이트
-- DATETIME은 1970~ 2038년까지 밖에 포함못함.
-- 하지만 DATETIME은 속도가 빠르고 공간도 더 적게 차지해

-- 4 Print Out the current Time
SELECT CURTIME();
SELECT TIME(NOW());
SELECT NOW();

-- 5 Print Out the current DATE(but not the time)
SELECT CURDATE();
SELECT DATE(NOW());
-- SELECT DATETIME(NOW()); 이건 안됨.

-- 6 Print Out the current day of the week (The number)
SELECT DAYOFWEEK(CURDATE());
SELECT DAYOFWEEK(NOW());
SELECT DATE_FORMAT(NOW(), '%w') + 1;	-- 또는 위에 것들에 -1을 해준다.

-- 7 Print Out the current Day Of the week (the day name : sunday, monday...)
SELECT dayname(CURDATE());
SELECT dayname(now());
SELECT DATE_FORMAT(NOW(), '%W');

-- 8 Print out the current day and time using this format : mm/dd/yyyy
SELECT date_format(now(),'%m/%d/%Y');

-- 9 Print out the current day and time using this format : Junuary 2nd at 3:15, April 1st at 10:18
SELECT date_format(NOW(), '%M %D at %H:%i');

-- 10 Create a tweets table that stores : * The Tweet content * A Username * Time it was created
CREATE TABLE tweets(
content VARCHAR(140),
username varchar(20),
created_at TIMESTAMP DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP
);

DESC tweets;