SQL cheatsheet
· 3 min read
SQL Datatype
| SQL Datatype | Description | |
|---|---|---|
| String | CHAR(size) | A fixed-length string containing numbers, letters or special characters. Length may vary from 0-255. |
| VARCHAR(size) | Variable-length string where the length may vary from 0-65535. Similar to CHAR. | |
| TEXT(size) | Can contain a string of size up to 65536 bytes. | |
| TINY TEXT | Can contain a string of up to 255 characters. | |
| MEDIUM TEXT | Can contain a string of up to 16777215 characters. | |
| LONG TEXT | Can contain a string of up to 4294967295 characters. | |
| BINARY(size) | Similar to CHAR() but stores binary byte strings. | |
| VARBINARY(size) | Similar to VARCHAR() but stores binary byte strings. | |
| BLOB(size) | Holds blobs up to 65536 bytes. | |
| TINYBLOB | It is used for Binary Large Objects and has a maximum size of 255bytes. | |
| MEDIUMBLOB | Holds blobs up to 16777215 bytes. | |
| LONGBLOB | Holds blobs up to 4294967295 bytes. | |
| Numberic | BIT(size) | Bit-value type, where size varies from 1 to 64. Default value: 1 |
| INT(size) | Integer with values in the signed range of -2147483648 to 2147483647 and values in the unsigned range of 0 to 4294967295. | |
| TINYINT(size) | Integer with values in the signed range of -128 to 127 and values in the unsigned range of 0 to 255. | |
| SMALLINT(size) | Integer with values in the signed range of -32768 to 32767 and values in the unsigned range of 0 to 65535. | |
| MEDIUMINT(size) | Integer with values in the signed range of -8388608 to 8388607 and values in the unsigned range of 0 to 16777215. | |
| BIGINT(size) | Integer with values in the signed range of 9223372036854775808 to 9223372036854775807 and values in the unsigned range of 0 to 18446744073709551615. | |
| BOOLEAN | Boolean values where 0 is considered as FALSE and non-zero values are considered TRUE. | |
| FLOAT (p) | The floating-point number is stored. If the precision parameter is set between 0 to 24, the type is FLOAT() else if it lies between 25 to 53, the datatype is DOUBLE() | |
| Date & Time | DATE | Stores date in YYYY-MM-DD format with dates in the range of '1000-01-01' to '9999-12-31'. |
| TIME(fsp) | Stores time in hh:mm:ss format with times in the range of '-838:59:59' to '838:59:59'. | |
| DATETIME(fsp) | Stores a combination of date and time in YYYY-MM-DD and hh:mm:ss format, with values in the range of '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. | |
| TIMESTAMP(fsp) | It stores values relative to the UnixEpoch, basically a Unix Timestamp. Values lie in the range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. | |
| YEAR | Stores values of years as a 4digit number format, with a range lying between -1901 to 2155. |
SQL Command
SQL Constraints
NOT NULL: Specifies that this column cannot store a NULL value.
CREATE TABLE student
(
id INT(8) NOT NULL,
name VARCHAR(30) NOT NULL,
address VARCHAR(50)
);
UNIQUE: Specifies that this column can have only Unique values, i.e the values cannot be repeated in the column.
CREATE TABLE Student
(
ID int(8) UNIQUE,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);