| 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
martes, 6 de mayo de 2025
Date time type
Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
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 | -263 | 0 | 263-1 | 264-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;
+---+
lunes, 17 de marzo de 2025
Functions and Operators
Functions and Operators
https://dev.mysql.com/doc/refman/8.4/en/functions.html
Data type
https://dev.mysql.com/doc/refman/8.4/en/data-types.html
sábado, 18 de enero de 2025
lunes, 13 de enero de 2025
join
CREATE TABLE members (
member_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (member_id)
);
CREATE TABLE committees (
committee_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (committee_id)
);Code language: SQL (Structured Query Language) (sql)Second, insert some rows into the tables members and committees :
INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
mysql> SELECT * FROM members;
+-----------+--------+
| member_id | name |
+-----------+--------+
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
+-----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM committees;
+--------------+--------+
| committee_id | name |
+--------------+--------+
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
+--------------+--------+
4 rows in set (0.00 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m inner join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 3 | 2 | Mary | Mary |
| 5 | 3 | Amelia | Amelia |
+-----------+--------------+--------+--------+
3 rows in set (0.01 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m left join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 2 | NULL | Jane | NULL |
| 3 | 2 | Mary | Mary |
| 4 | NULL | David | NULL |
| 5 | 3 | Amelia | Amelia |
+-----------+--------------+--------+--------+
5 rows in set (0.00 sec)
mysql> select m.member_id,c.committee_id,m.name,c.name from members m right join committees as c on c.name=m.name;
+-----------+--------------+--------+--------+
| member_id | committee_id | name | name |
+-----------+--------------+--------+--------+
| 1 | 1 | John | John |
| 3 | 2 | Mary | Mary |
| 5 | 3 | Amelia | Amelia |
| NULL | 4 | NULL | Joe |
+-----------+--------------+--------+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM members where name not in(SELECT name FROM committees);
+-----------+-------+
| member_id | name |
+-----------+-------+
| 2 | Jane |
| 4 | David |
+-----------+-------+
2 rows in set (0.01 sec)
mysql> select * from committees where name not in ( select name from members);
+--------------+------+
| committee_id | name |
+--------------+------+
| 4 | Joe |
+--------------+------+
1 row in set (0.00 sec)
Suscribirse a:
Entradas (Atom)