Example 1: sql data types
# Numeric Types
BIT TINYINT SMALLINT INT BIGINT DECIMAL NUMERIC FLOAT REAL
# Date & Time Types
DATE TIME DATETIME TIMESTAMP YEAR
# Char & String Types (N) Denotes Unicode Versions
CHAR VARCHAR TEXT NCHAR NVARCHAR NTEXT
# Binary Data Types
BINARY VARBINARY IMAGE
# Misc
CLOB BLOB XML JSON
Example 2: sql data types
-- Text Data Types:
CHAR(size)
Fixed length string which can contain letters, numbers and special
characters. The size parameter sets the maximum string length, from
0 – 255 with a default of 1.
VARCHAR(size) Variable length string similar to CHAR(), but with a maximum string
length range from 0 to 65535.
BINARY(size) Similar to CHAR() but stores binary byte strings.
VARBINARY(size) Similar to VARCHAR() but for binary byte strings.
TINYBLOB Holds Binary Large Objects (BLOBs) with a max length of 255 bytes.
TINYTEXT Holds a string with a maximum length of 255 characters. Use
VARCHAR() instead, as it’s fetched much faster.
TEXT(size) Holds a string with a maximum length of 65535 bytes. Again, better to
use VARCHAR().
BLOB(size) Holds Binary Large Objects (BLOBs) with a max length of 65535
bytes.
MEDIUMTEXT Holds a string with a maximum length of 16,777,215 characters.
MEDIUMBLOB Holds Binary Large Objects (BLOBs) with a max length of 16,777,215
bytes.
LONGTEXT Holds a string with a maximum length of 4,294,967,295 characters.
LONGBLOB Holds Binary Large Objects (BLOBs) with a max length of
4,294,967,295 bytes.
ENUM(a, b, c,
etc…)
A string object that only has one value, which is chosen from a list of
values which you define, up to a maximum of 65535 values. If a value
is added which isn’t on this list, it’s replaced with a blank value instead.
Think of ENUM being similar to HTML radio boxes in this regard.
CREATE TABLE tshirts (color ENUM(‘red’, ‘green’,
‘blue’, ‘yellow’, ‘purple’));
SET(a, b, c, etc…)
A string object that can have 0 or more values, which is chosen from a
list of values which you define, up to a maximum of 64 values. Think of
SET being similar to HTML checkboxes in this regard.
Example 3: sql What type is that value?
SELECT TYPEOF(value);
Example 4: sql types
1- Data Manipulation Language(DML)-(SELECT, INSTERT, UPDATE...)
DML statements affect records in table. These are
basic operations we perform on data such as selecting
few records from a table, inserting new records,
deleting unnecessary records, updating existing records.
2-Data Definition Language (CREATE , ALTER , DROP)...
DDL statements are used to modify database or
table schema. These statements handle the design
and storage of database objects.
3-Data Control Language (GRANT , REVOKE)...
DCL statements control the level of access that
users have on database objects
4- Transaction Control Language (BEGIN TRAN, COMMIT TRAN, ROLLBACK)...
TCL statements allows us to control and manage
transactions to maintain the integrity of data
withing SQL statements.