MySQL BLOG

viernes, 23 de mayo de 2025

Date Practice

SELECT

NOW() AS Today,

DATE_SUB(NOW(), INTERVAL 1 DAY) AS Yesterday,

DATE_ADD(NOW(), INTERVAL 1 DAY) AS Tomorrow,

ADDTIME(NOW(), '01:01:00') AS Today_1_Hour_1_Min,

DATEDIFF(NOW(), DATE_SUB(NOW(), INTERVAL 13 DAY)) AS Days_Difference_Between_2_Dates;

mysql> select now() Today,date_sub(now(),interval 1 day) Yesterday, date_add(now(),interval 1 day) Tomorrow,addtime(now(),'1:1') today_1_hour_1_min,datediff(now(),date_sub(now(),interval 13 day)) days_difference_between_2_dates\G;

*************************** 1. row ***************************

Today: 2025-05-24 01:25:42

Yesterday: 2025-05-23 01:25:42

Tomorrow: 2025-05-25 01:25:42

today_1_hour_1_min: 2025-05-24 02:26:42

days_difference_between_2_dates: 13

---------------

Date format

mysql> select date_format(now(),'%a-%d-%b-%Y') TODAY;

+-----------------+

| TODAY |

+-----------------+

| Sat-24-May-2025 |

+-----------------+

select FROM_UNIXTIME(1447430881,'%a-%d-%b-%Y') TODAY;

+-----------------+

| TODAY |

+-----------------+

| Fri-13-Nov-2015 |

+-----------------+

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

martes, 6 de mayo de 2025

Date time type

Data Type	“Zero” Value
`DATE`	`'0000-00-00'`
`TIME`	`'00:00:00'`
`DATETIME`	`'0000-00-00 00:00:00'`
`TIMESTAMP`	`'0000-00-00 00:00:00'`
`YEAR`	`0000`

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html

Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

MySQL supports the SQL standard integer types INTEGER (or INT) and SMALLINT. As an extension to the standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the required storage and range for each integer type.

Table 13.1 Required Storage and Range for Integer Types Supported by MySQL

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
`TINYINT`	1	`-128`	`0`	`127`	`255`
`SMALLINT`	2	`-32768`	`0`	`32767`	`65535`
`MEDIUMINT`	3	`-8388608`	`0`	`8388607`	`16777215`
`INT`	4	`-2147483648`	`0`	`2147483647`	`4294967295`
`BIGINT`	8	`-2⁶³`	`0`	`2⁶³-1`	`2⁶⁴-1`

viernes, 18 de abril de 2025

MySQL ANY ALL IN

mysql> select 4 >any(select 33 as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 0 |

+---+

1 row in set (0.00 sec)

mysql> select 34 >any(select 33 as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 1 |

+---+

1 row in set (0.01 sec)

-----------

ALL

mysql> select 34 >ALL(select 33 as V UNION ALL select 11) as R;

+---+

| R |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.4/en/any-in-some-subqueries.html

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=

MySQL exists

select exists (select 'A' where 5>10) as R;

+---+

| R |

+---+

| 0 |

+---+

Because the evaluated query return empty set, if return any value doesn't matter the value it will return 1, doesn't matter if is null or 0.

select 'A' where 5>10;

Empty set (0.00 sec)

------------------------ exists here will return 1

select exists (select NULL) as R;

+------+

| R |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

select exists (select 0) as R;

+---+

| R |

+---+

| 1 |

+---+

select 'Ambiorix' where exists(select 1);

+----------+

| Ambiorix |

+----------+

| Ambiorix |

+----------+

MySQL IF

mysql> select if(10>8,'A','B') as result;

+--------+

| result |

+--------+

| A |

+--------+

--------------

mysql> select if(10>15,'A','B') as result;

+--------+

| result |

+--------+

| B |

+--------+

MySQL case

mysql> set @i=4;

mysql> select case @i

-> when @i>3 then'A'

-> else 'B'

-> end as t;

------------

mysql> select case

-> when 5>4 then 'A'

-> when 5<6 then 'B'

-> else 'C'

-> end as t;

+---+

select upper(case when 3> 2 then 'h' when 5>1 then 'e' else 'r' end) as Letter;

+--------+

| Letter |

+--------+

| H |

+--------+