SQL: Data types

Data type specifies type of a column. For example, name should be of type string, id should be of type int etc.,

Numeric Types

Data Type	Description
bigint	Minimum: -9,223,372,036,854,775,808 Maximum: 9,223,372,036,854,775,807
int	Minimum: -2,147,483,648 Maximum: 2,147,483,647
smallint	Minimum: -32,768 Maximum:32,767
tinyint	Minimum: 0 Maximum:255
bit	Minimum: 0 Maximum:1
decimal	Minimum: -10^38 +1 Maximum: 10^38 -1
numeric	Minimum: -10^38 +1 Maximum: 10^38 -1
money	Minimum: -922,337,203,685,477.5808 Maximum: +922,337,203,685,477.5807
smallmoney	Minimum: -214,748.3648 Maximum: +214,748.3647
float	Minimum: -1.79E + 308 Maximum: 1.79E + 308
real	Minimum: -3.40E + 38 Maximum: 3.40E + 38

       

Exmaple

CREATE TABLE test 

( 

a bigint,

b int,

c smallint,

d tinyint,

e bit,

f decimal (5, 2),

g numeric,

j float (5, 2),

k real(5, 2)

);  

mysql> CREATE TABLE test  
    -> (  
    -> a bigint,
    -> b int,
    -> c smallint,
    -> d tinyint,
    -> e bit,
    -> f decimal (5, 2),
    -> g numeric,
    -> j float (5, 2),
    -> k real(5, 2)
    -> );  
Query OK, 0 rows affected (0.01 sec)

mysql> describe test;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| a     | bigint(20)    | YES  |     | NULL    |       |
| b     | int(11)       | YES  |     | NULL    |       |
| c     | smallint(6)   | YES  |     | NULL    |       |
| d     | tinyint(4)    | YES  |     | NULL    |       |
| e     | bit(1)        | YES  |     | NULL    |       |
| f     | decimal(5,2)  | YES  |     | NULL    |       |
| g     | decimal(10,0) | YES  |     | NULL    |       |
| j     | float(5,2)    | YES  |     | NULL    |       |
| k     | double(5,2)   | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

Date and time types

Data Type	Description
datetime	Stores in the format of YYYY-MM-DD HH:MM:SS. It support from Jan 1, 1753, to Dec 31, 9999
date	Stores in the format of YYYY-MM-DD
time	Stores in the format of HH:MM:SS
year	Stores in the format of YYYY
smalldatetime	Stores in the format of YYYY-MM-DD HH:MM:SS. It support fromJan 1, to 1900Jun 6, 2079

CREATE TABLE test1(
a datetime,

 b date,

 c time,   

 d year

);

 

mysql> CREATE TABLE test1(
    ->  a datetime,
    ->  b date, 
    ->  c time,
    ->  d year
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> describe test1
    -> ;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a     | datetime | YES  |     | NULL    |       |
| b     | date     | YES  |     | NULL    |       |
| c     | time     | YES  |     | NULL    |       |
| d     | year(4)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

Character data types

Data Type	Description
char	Used to store fixed length character strings.
varchar2	varchar2 is used to store variable length character strings.
varchar	Currently varchar behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
text	Used to store large text.

Char Vs Varchar

VARCHAR is variable-length.CHAR is fixed length.If your content is a fixed size, you'll get better performance with CHAR.

         

CREATE TABLE test2(

  a char(10),

  b varchar(10),

  d text

);

mysql> CREATE TABLE test2(
    ->   a char(10),
    ->   b varchar(10),
    ->   d text
    -> ); 
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> describe test2
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | char(10)    | YES  |     | NULL    |       |
| b     | varchar(10) | YES  |     | NULL    |       |
| d     | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

Storing images

BLOB data type is used to store binary objects, which are large in size.

           

  

Previous                                                 Next                                                 Home