SQL DATA TYPE

-- CHAR and VARCHAR
CHAR(n) > Only N characters 'Allowed'
CHAR is faster for fixed length text
	: State_Abbreviations - CA, NY
    : YES_or_NO Flags - Y, N
    : Zip_codes - 59715, 94924
Otherwise - use VARCHAR

ㅇㅇ

 

-- INT and etcINT
1B - TINYINT
2B - SMALLINT
3B - MEDIUMINT
4B - INT
8B - BIGINT
CREATE TABLE tableName (columnName DATATYPE UNSIGNED);

ㅇㅇ

 

-- Decimal
DECIMAL(total_numer_of_digits, digits_after_decimal)
	: 0.1 - possible
	: 1450.2 - impossible
    : 0.123 - warning - SHOW WARNINGs

ㅇㅇ

 

-- FLOAT and DOUBLE
store larger numbers using less space
but, it comes at the cost of precision
FLOAT - memory_needed(4B) - precision_issues(~7digits)
DOUBLE - memory_needed(8B) - precision_issues(~15digits)
docs says: you shold try to use the most precise type in all cases
         : double - if accuracy is not too important or if speed is the highest priority
         : BIGINT - for high precision you can always convert to a fixed-point type

ㅇㅇ

 

-- Dates and Times
DATE: 'YYYY-MM-DD' - values with a date but no time
TIME: 'HH:MM:SS' - values with a time but no date
DATETIME: 'YYYY-MM-DD HH:MM:SS' - values with a date and time

ㅇㅇ

 

-- CURRENT
CURDATE(CURRENT_DATE)
CURTIME(CURRENT_TIME)
NOW(CURRENT_TIMESTAMP)

ㅇㅇ

 

-- Useful data functions
DAY() : DAYOFMONTH랑 같음, 며칠인지 return
DAYOFWEEK(): 무슨 요일인지 숫자로 return 일(1)
DATOFYEAR(): 00월 00일이 0000년에 몇 번째 날인지 return
MONTHNAME(): 월 이름 return, such as october
WEEK(): 그 해의 몇 번째 주인지 return
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

ㅇㅇ

 

-- Useful time functions
MINUTE()
HOUR()
SECOND()
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

ㅇㅇ

 

-- Formatting DATES
DATE_FORMAT(column_field, formatting)
	: %b 월이름 약어
	: $c 월 숫자
    : %M 월이름 그대로
    : %a 요일 약어
    : %w 주 약어
	: %e 패딩없는 일(day)
    
    : 시분초 지정자도 있음
    :https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

ㅇㅇ

 

-- DATE MATH
DATEDIFF(a, b): a, b의 날짜 차이를 숫자(일수)로 계산
DATE_ADD(date, interval, expr): DATE_ADD(CURDATE(), INTERVAL 1 YEAR) 1년 뒤 계산
DATE_SUB(): DATA_ADD와 유사한 동작
TIMEDIFF(), ADDTIME(), SUBTIME()
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

ㅇㅇ

 

-- TIMESTAMP
DATETIME보다 메모리를 덜 차지함
1970 01 01 00:00:01 ~ 2038 01 19 03:14:07
DATETIME은 1000 01 01 00:00:00 ~ 9999 12 31 23:59:59
TIMESTAMPADD()
TIMESTAMPDIFF()
CREATE TABLE captions (
    text VARCHAR(150),
    created_at TIMESTAMP default CURRENT_TIMESTAMP
);
CREATE TABLE captions(
    text VARCHAR(150),
    created_at TIMESTAMP default CURRENT_TIMESTAMP,
    updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

ㅇㅇ

 

--
Q1. What's a good use case for CHAR? > 고정 데이터 연도나 우편번호 등
Q2. Fill in the Blanks
CREATE TABLE inventory (
    item_name __________,	VARCHAR(150)
    price ______________,	DECIMAL(7, 2)
    quantity ___________	INT
);
Q3. What's the difference between DATETIME and TIMESTAMP? > 메모리 차이, 날짜 범위 등
Q4. Print Out The Current Time > SELECT CURTIME();
Q5. Print out The Current Date(but not time) > SELECT CURDATE();
Q6. Print out the current day of the week > SELECT DAYOFWEEK(CURDATE()); / SELECT DATE_FORMAT(NOW(), '%w');
Q7. print out the current day and time using this format mm/dd/yyyy
> SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y')
> SELECT DATE_FORMAT(NOW(), '%M %D at %k:%i')
Q8. create a tweets table that stores
	the tweet content
    a username
    time it was created
>
CREATE TABLE tweets(
    content VARCHAR(180),
    username VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW() 또는 CURRENT_TIMESTAMP();
);

ㅇㅇ