TYPES

including single quotes

UPDATE string_tbl
SET text_fld = 'This string didn''t work, but it does now';

UPDATE string_tbl
SET text_fld = 'This string didn\'t work, but it does now';

Functions return numbers

• LENGTH(str):	문자열의 현재 길이를 반환. SELECT LENGTH(char_fld) AS char_data, LENGTH(vchar_fld) AS VCHAR_DATA FROM string_tbl; +-----------+------------+ | char_data | VCHAR_DATA | +-----------+------------+ | 9 | 10 | +-----------+------------+ #char_data = 'char data' #vchar_data = 'vchar data'

• POSITION(substr IN str):
  	synonym for LOCATE(substr, str, 1). LOCATE는 3번째 param으로 탐색 위치를 지정할 수 있으며, SQL에서는 0을 위치가 아닌 부정의 의미로만 사용한다. SELECT POSITION('data' IN vchar_fld) AS DATA_POS FROM string_tbl; SELECT LOCATE('data', vchar_fld) AS DATA_POS FROM string_tbl; +----------+ | DATA_POS | +----------+ | 7 | +----------+ #vchar_fld = 'vchar6data';

• STRCMP(expr1, expr2):

  문자열 비교, string.h의 strcmp함수와 동일하게 동작한다. 문자열화 가능한 모든 것에 사용가능하며, NULL이 표현으로 들어올 경우 무조건 NULL이 반환된다. SELECT STRCMP('123', '123') as 'equ', STRCMP('abc', 'bcd') as 'smaller', STRCMP('q', 'a') as 'bigger', STRCMP('q', NULL) as 'null', STRCMP(123, 23) as 'smaller2'; +-----+---------+--------+------+----------+ | equ | smaller | bigger | null | smaller2 | +-----+---------+--------+------+----------+ | 0 | -1 | 1 | NULL | -1 | +-----+---------+--------+------+----------+

• REGEXP, LIKE operator in SELECT

  SELECT
     name, name LIKE '%y' AS ends_in_y
  FROM category;
  
  SELECT
     name, name REGEXP 'y$' AS ends_in_y
  FROM category;
  
  +-------------+-----------+
  | Action      |         0 |
  | Animation   |         0 |
  | Children    |         0 |
  | Classics    |         0 |
  | Comedy      |         1 |
  | Documentary |         1 |
  | Drama       |         0 |
  | Family      |         1 |
  | Foreign     |         0 |
  | Games       |         0 |
  | Horror      |         0 |
  | Music       |         0 |
  | New         |         0 |
  | Sci-Fi      |         0 |
  | Sports      |         0 |
  | Travel      |         0 |
  +-------------+-----------+
  

Functions return strings

• CONCAT(str1, str2, ...):
  	generate string with strings. UPDATE string_tbl SET text_fld = CONCAT(text_fld, ', but now it''s more longer'); SELECT first_name || ' ' || last_name || ' has been a customer since ` || date(create_date) /* * '||' 기호를 concat으로 받아들이는 경우는 sql_mode에 PIPES_AS_CONCAT가 있어야 한다.(ansi로 설정시 추가됨) */

• INSERT(dest_str, pos, offset, src-str):
  	generate with inserting src into dest SELECT INSERT('Helloworld', 6, 0, ', ') AS string; +--------------+ | string | +--------------+ | hello, world | +--------------+

• REPLACE(str, search, replace): generate string with replacing the search string to replace string.

  SELECT REPLACE('goodbye world', 'goodbye', 'hello');
  #hello world
  

• SUBSTRING(str, start, length): generate string extracted from start to length.

  SELECT SUBSTRING('goodbye cruel world', 9, 5);
  #cruel
  

Types

Controlling Number Precision

• CEIL(number):	Round up to integer.

• FLOOR(number):	Round down to integer.

• ROUND(number, pos):
  	Round at next to number of pos(negative pos means interger portion).

• TRUNCATE(number, pos):
  	Truncate the number of pos(same as usage of pos in ROUND function). SELECT ROUND(17, -1), TRUNCATE(17, -1); #20, 10

String representation of temporal data

• Date Format Components

  • YYYY:	1000 ~ 9999

  • MM:	01 ~ 12

  • DD:	01 ~ 31

  • HH:	00 ~ 23

  • HHH(elapsed):	-838 ~ 838

  • MI:	00 ~ 59

  • SS:	00 ~ 59

• Required Date Components Formats

  • DATE:	YYYY-MM-DD, date part only

  • DATETIME:	YYYY-MM-DD HH:MI:SS, date and time (1000-01-01 ~ 9999-01-01)

  • TIMESTAMP:	YYYY-MM-DD HH:MI:SS, date and time in UTC ONLY (1970-01-01 ~ 2038-01-19)

  • TIME:	HHH:MI:SS

• Date Format components

  • %M:	Month name(January ~ December)

  • %m:	Month (01 ~ 12)

  • %d:	Day (01 ~ 31)

  • %j:	Day of year (001 ~ 366)

  • %W:	Weekday name (Sunday ~ Saturday)

  • %Y:	Year in 4 digit (2023)

  • %y:	Year in 2 digit (23)

  • %H:	Hour (00 ~ 23)

  • %h:	Hour (01 ~ 12)

  • %i:	Minutes (00 ~ 59)

  • %s:	Seconds (00 ~ 59)

  • %f:	Microseconds (000000 ~ 999999)

  • %p:	A.M or P.M.

Generation Dates

• CAST(<SRC> AS type):
  	SRC 양식에서 type에 대한 자료로 변형 SELECT CAST('2019-09-17' AS DATE) AS date_field, CAST('108:17:57' AS DATE) AS time_field; # 2019-09-17 | 108:17:57 # Type has changed.

• STR_TO_DATE('temporal string format' AS format_expr):
  	문자열 양식에서 format_expr에 해당하는 부분을 구성요소에 맞게 변형 SELECT STR_TO_DATE('Septempber 17,..HI 2019', '%M %d,..HI %Y'); # 2019-09-17 SELECT STR_TO_DATE('Septempber 17,..HI 2019 12', '%M %d,..HI %Y %H'); # 2019-09-17 12:00:00

• DATE_ADD(SRC-DATE, INTERVAL n-amount AMOUNT-TYPE):
  	SRC-DATE로 부터 Interval만큼 더한 날짜를 반환하는데, INTERVAL 형태에 따라 DATETIME으로 변형 Interval types second minute hour day month year minute_second: m:s sepeated by ':' hour_second: h:m:s sepeated by ':' year_month: sepeated by '-' SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 9 DAY); # 2023-03-05 SELECT DATE_ADD(CURRENT_DATE(), INTERVAL '9:33:34' HOUR_SECOND); # 2023-02-24 09:33:34

Manipulate Dates

• DAYNAME(DATE):	DATE로 부터 day name of week를 추출한다. SELECT DAYNAME(CURRENT_DATE()); # Friday SELECT DAYNAME('2019-09-18'); # Wednesday

• EXTRACT(UNIT FROM DATE):
  	DATE로 부터 UNIT의 수치를 반환한다. UNIT: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND SELECT EXTRACT(HOUR FROM NOW()); # 23 SELECT EXTRACT(DAY '2019-09-18'); # 18

• DATEDIFF(DEST, SRC):
  	DATE DEST로 부터 SRC의 DAY DIFF를 반환한다. UNIT: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND SELECT DATEDIFF('2019-09-03', '2019-06-21 00:00:01'); # 74

Conversion Functions

SELECT
   CAST('2019-09-17' AS DATE) AS date_field,
   CAST('1456328' AS SIGNED INTEGER) AS to_num,
   CAST('099ABC111' AS UNSIGNED INTEGER) AS to_natural;
# 2019-09-17 | 1456328 | 99