Introduction
A Data type is a programming language, which contain set of values and data. SQL Data Types defines an appropriate kind of data, like integer, floating-point, Boolean etc. A data type also defines the attainable values for that type, the operations that should be possible on that sort and then stored the data.
SQL Data Types are classified into
- String Data Type
- Numeric Data Type
- Date/time Data Type
- Large object Data Type
Description
In SQL, a string can control anything from plain text to double data such as files and images. The string can be related and examined based on pattern identity by using the like operators.
The following are the String Data types in SQL:
| Datatype Syntax | Maximum Size |
|---|---|
| CHAR | Maximum 255 Characters in size |
| VARCHAR | Maximum 255 Characters in size |
| TEXT | Maximum 65,535 characters in size |
| BINARY | Maximum 255 characters in size |
| VARBINARY | Maximum 255 characters in size |
Examples
The below example describe the sample example for string data types.
[c]
sql> Create table employee15(emp_id int,ename varchar(255),job varchar(255),salary int);
Query OK, 0 rows affected (0.64 sec)
[/c]
The above example describes the string data types attributes.
Description
Numeric SQL Data Types combines number data types, including fixed-point, whole numbers and drifting point. In expansion, SQL additionally bolsters BIT data sort for putting away field values. Numeric sorts are signed or unsigned with the exception the BIT sort.
The following are the Numeric Datatypes in SQL
| Datatype Syntax | Maximum Size |
|---|---|
| BIT | Small whole number worth. Marked qualities range from - 128 to 127. Unsigned qualities range from 0 to 255. |
| FLOAT(p) | Floating point number |
| BOOLEAN | Synonym for TINYINT |
| INT(m) | Standard whole number worth. Marked qualities range from - 2147483648 to 2147483647. Unsigned qualities range from 0 to 4294967295. |
| INTEGER(m) | Standard whole number quality. Marked qualities range from - 2147483648 to 2147483647. Unsigned qualities range from 0 to 4294967295. |
| NUMERIC(m,d) | Unloaded settled point number. m defaults to 10, if not indicated, d defaults to 0, if not indicated. |
| DOUBLE(m,d) | Double precision floating point number. |
| DOUBLE PRECISION(m,d) | Double precision floating point number |
Examples
The below example describe the sample example for numeric data types.
[c]
sql> Create table test(id decimal,name varchar(100),col1 decimal(5,2),col2 integer,col3 bigint);
[/c]
The below example describes the numeric data types with numeric function.
Description
SQL produce the sequence for date and time. Beside this SQL holds timestamps datatype for capturing the adjustments made in the row of table. If one used to store the year past date & month, then utilize YEAR data sort.
The following are the Date/Time Data types in SQL.
| Datatype Syntax | Maximum Size | Explanation |
|---|---|---|
| DATE | Esteems ranges from '1000-01-01' to '9999-12-31' | Shown as 'YYYY-MM-DD' |
| TIME | Esteems ranges from '-838:59:59' to '838:59:59' | Shown as 'HH:MM:SS' |
| YEAR[(2|4)] | Year esteems as 2 integers or 4 integers | Default is 4 integers |
| DATETIME | Esteems ranges from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. | Shown as 'YYYY-MM-DD HH:MM:SS' |
Examples
The below example describe the sample example for date/time data types.
[c]
sql> select * from employee25;
+--------+-------+-------+-------------------+
| emp_id | ename | sal | Orderdate |
+--------+-------+-------+-------------------+
| 1001 | maddi | 12000 | 2008-11-11 |
| 1002 | jack | 13100 | 2008-11-11 |
+--------+-------+-------+-------------------+
2 rows in set (0.00 sec)
sql> select * from employee25 where orderdate = '2008-11-11'
[/c]
Here in the above example it will retrieve the date and time data types.
Description
The following are the LOB Data types in SQL.
Substantial ordinarily signifies "around 4kb or more", albeit a few databases can cheerfully handle up to 32kb preceding information turns out to be "extensive". Huge articles can be either literary or paired in nature. PDO permits you to work with this expansive information sort by utilizing the pram_lob code.
| Datatype Syntax | Maximum Size |
|---|---|
| TINYBLOB | Most extreme size of 255 bytes |
| BLOB | Most extreme size of 65,535 bytes |
| LONGTEXT | Most extreme size of 4GB or 4,294,967,295 characters |
Key Points
- SQL Data Types - Is a programming language, which contain set of values and data.
- String Data type - Uses char and varchar.
- Number Data type - Uses integer and floating values.
- Date and Time data type - Uses 'YYYY-MM-DD' and 'HH:MM:SS' format.