Example 1: sql data types
BIT TINYINT SMALLINT INT BIGINT DECIMAL NUMERIC FLOAT REAL
DATE TIME DATETIME TIMESTAMP YEAR
CHAR VARCHAR TEXT NCHAR NVARCHAR NTEXT
BINARY VARBINARY IMAGE
CLOB BLOB XML JSON
Example 2: sql 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 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.