웹이야기

MySQL 날짜함수, 시간함수 CURDATE() - 현재 날짜를 return. CURDATE() 형태로 사용 Retuns the current date as a value in 'YYYY-MM-DD' or 'YYYYMMDD' format, depending on whether the function is used in string or numeric context. mysql> SELECT CURDATE(); +------------+ | CURDATE() | +------------+ | 2020-04-01 | +------------+ 1 row in set (0.00 sec) mysql> SELECT CURDATE()+0; +-------------+ | CURDATE()+0 | +---------..

MySQL 날짜함수, 시간함수 CONVERT_TZ() CONVERT_TZ(dt, from_tz, to_tz) 의 형태 - 특정 시간에 대해 타임존(time zone)을 변경하여 변경된 시간을 return 하는 함수정도? CONVERT_TZ() converts a datetime value dt from the time zone given by from tz to the the time zone given by to_tz and returns the resulting value. This function retuns NULL if the arguments are invalid. If the value falls out the supported range of the TIMESTAMP type when co..

MySQL 날짜함수, 시간함수 ADDTIME() ADDTIME(expr1, expr2) 의 형태 ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression. mysql> SELECT ADDTIME('2007-12-31 23;59;59.999999', '1 1:1:1.000002'); +---------------------------------------------------------+ | ADDTIME('2007-12-31 23;59;59.999999', '1 1:1:1.000002') | +--------------------------..

MySQL 날짜함수, 시간함수 ADDDATE() ADDDATE(date, INTERVAL expr unit) 또는 ADDDATE(expr, days) 형태 When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument. mysql> SELECT DATE_ADD('2001-01-02', INTERVAL 31 DAY); +-----------------------------------------+ | DATE..

Date and Time Functions 날짜 함수, 시간 함수 정도 되겠지 MySQL에 날짜와 시간에 관한 함수를 보니 약 60개정도가 있다. 사소하게 보이는 함수도 많고, 유용하게 보이는 것도 많고, 처음보는 것도 있고, 혹은 전혀 쓰지 않을 것 같은 것들도 있다. 이것들을 간단하게 정리해보려고 한다. Name Description ADDDATE() ADD time values (intervals) to a date value ADDTIME Add time CONVERT_TZ() Convert from one time zone to another CURDATE() Return the current date CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE() ..

TRUNCATE(X, D) Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero. mysql> SELECT TRUNCATE(1.223, 1); +--------------------+ | TRUNCATE(1.223, 1) | +--------------------+ | 1.2 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT TR..

SIN(X) Returns the sine of X, while X is given in radians. mysql> SELECT SIN(PI()); +------------------------+ | SIN(PI()) | +------------------------+ | 1.2246467991473532e-16 | +------------------------+ 1 row in set (0.03 sec)

SIGN(X) Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive. mysql> SELECT SIGN(-23), SIGN(0), SIGN(234) ; +-----------+---------+-----------+ | SIGN(-23) | SIGN(0) | SIGN(234) | +-----------+---------+-----------+ | -1 | 0 | 1 | +-----------+---------+-----------+ 1 row in set (0.00 sec)

ROUND(X), ROUND(X, D) Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified. D can be negative to cause D digits left of the decimal point of the value X to become to zero. mysql> SELECT ROUND(-1.23); +--------------+ | ROUND(-1.23) | +--------------+ | -1 | +--------------+ 1 row in set (0.10 sec) mysql> SELECT ROUND(-1..

RAND([N]) Returns a random floating-point value v in the range 0

MOD(N, M) N % M N MOD M 3개의 코드는 동일하다. MOD = modulo 는 나눗셈의 나머지를 계산하는 수학적 연산. Modulo operation. Returns the remainder of N divided by M . mysql> SELECT MOD(234, 10), 234%10, 234 MOD 10 ; +--------------+--------+------------+ | MOD(234, 10) | 234%10 | 234 MOD 10 | +--------------+--------+------------+ | 4 | 4 | 4 | +--------------+--------+------------+ 1 row in set (0.00 sec)

FLOOR(X) Returns the largest integer value not greater than X. mysql> SELECT FLOOR(1.23), FLOOR(-1.23) ; +-------------+--------------+ | FLOOR(1.23) | FLOOR(-1.23) | +-------------+--------------+ | 1 | -2 | +-------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT CEIL(1.23) ; +------------+ | CEIL(1.23) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec) CEIL() 참조 https:/..

CONV(N, from_base, to_base) Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or string. The minimum base is 2 and the maximim base is 36. If from base is a negative number, N is regard..

CEIL(X) CEIL() is a synonym for CEILING() CEILING(X) Returns the smallest integer value not less than X. mysql> SELECT CEILING(1.1) ; +--------------+ | CEILING(1.1) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> SELECT CEILING(1) ; +------------+ | CEILING(1) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> SELECT CEILING(-3.2); +---------------+ | ..

ABS(X) Returns the absolute value of X. This function is safe to use with BIGINT values. mysql> SELECT ABS(2) ; +--------+ | ABS(2) | +--------+ | 2 | +--------+ 1 row in set (0.25 sec) mysql> SELECT ABS(-32) ; +----------+ | ABS(-32) | +----------+ | 32 | +----------+ 1 row in set (0.01 sec)

MySQL 수학함수 정도 ??? Name Description ABS() Return the absolute value ACOS() Return arc cosine ASIN() Return arc sine ATAN() Return arc tangent ATAN2(), ATAN() Return the arc tangent of the two argument CEIL() Return the smallest integer value not less than the argument CEILING() Return the smallest integer value not less than the argument CONV() Convert numbers between different number bases COS()..

MySQL 산술연산자 쯤? Arithmetic Operators Name Description %, MOD Modulo operator * Multiplication operator + Addition Operator - Minus Operator - Change the sign of the argument / Division operator DIV Integer division 평소에 많이 봐왔던 것들이다. DIV Integer division. Discards from the division result any fractional part to the right of the decimal point. If either operand has a noninteger type, the operands ar..

Flow Control Operators 제어 흐름 함수 ??? Name Description CASE CASE operator IF() If/else construct IFNULL() Null if/else construct NULLIF() Return NULL if expr1 =expr2 1) CASE CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END mysql> SELECT CASE 1 WHEN 1 THEN 'one'..

Assignment Operators 지정 연산자쯤 되겠지? Name Description := Assign a value = Assign a value(as part of a SET statement, or as part of the SET clause in an UPDATE statement) 1) := Assignment operator 흠.. 거의 사용을 안해봐서... 그냥 예제만 mysql> SELECT @var1 :=1 , @var2 ; +-----------+-------+ | @var1 :=1 | @var2 | +-----------+-------+ | 1 | NULL | +-----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> SE..

Logical Operators 논리연산자쯤 되겠지. Name Description AND, && Logical AND NOT, ! Legates value OR, || Logical OR XOR Logical XOR In SQL, all alogical operators evaluates to TRUE, FALSE, or NULL (UNKNOWN). In MySQL, these are implemented as 1(TRUE), 0(FALSE), and NULL. Most of this is common to different SQL database aserver, although some servers may return any nonzero value for TRUE. MySQL evaluates a..