SQL - SPLessons

SQL Data Types

Chapter 5

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQL Data Types

SQL Data Types

shape 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 types

shape 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

shape Examples

The below example describe the sample example for string data types.

sql> Create table employee15(emp_id int,ename varchar(255),job varchar(255),salary int);
Query OK, 0 rows affected (0.64 sec)

The above example describes the string data types attributes.

Numeric Data types

shape 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

shape Examples

The below example describe the sample example for numeric data types.

sql> Create table test(id decimal,name varchar(100),col1 decimal(5,2),col2 integer,col3 bigint);

The below example describes the numeric data types with numeric function.

Date/Time Data types

shape 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’

shape Examples

The below example describe the sample example for date/time data types.

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'

Here in the above example it will retrieve the date and time data types.

Large Object(LOB) Data Types

shape Description

 The following are the LOB Data types in SQL.

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

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.

Summary

shape 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.