SQL

</>   SQL Tutorial

SQL

Data Types

What is SQL Database?

A data type specifies a particular type of data, such as integer, floating-point, Boolean etc..

A data type also specifies the possible values for that type , the operations that can be performed and that type and the way the values of that type are stored .

MYSQL supports all standard SQL Numeric data types

Type Length in BytesMinimum ValueMax val(signed)Min val (unsigned)Max val
(unsigned)
TINYINT1-1281270255
SMALLINT2-32768327667065535
MEDIUMINT3-83886088388607016777215
INT4-214748364821474836480429497295
BIG INT8-92233720368
54775808		9223372036
854775808		01844674407370
9551615
  • The FLOAT and DOUBLE types represent approximate numeric data values.
  • MYSQL allows non standard syntax
  • FLOAT(M,D)
  • REAL(M,D)
  • Here values can be stored up to M digits in total where D represents decimal point.

Fixed Point Types

  • Used to preserve exact precision
  • For example currency data.
  • DECIMAL and NUMERIC type store exact numeric data types

MYSQL DATA AND TIME TYPES

  • Represent DATE,TIME,DATETIME,TIMESTAMP AND YEAR
  • Each type has range of valid values as well as a zero values
TypesDescription Display Format
DATETIME Use when you need values contained both date and time informationYYYY-MM-DD HH:MM:SS
DATEUse when you need only date informationYYYY-MM-DD
TIMESTAMPValues are converted from the current time zone to UTC while storing and converted back from UTC to the current time zone when retrievedYYYY-MM-DD HH:MM:SS

Time Types:

  • Fetch and display time value in 'HH:MM:SS' OR 'HHH:MM:SS' format
  • Range: from '-838:59:59' to '839:59:59'
  • MYSQL explain abbreviated TIME with colons as tiem of the day
  • Suppose '09:10' means '09:10:00' not '00:09:10'
  • Two right most digits represent seconds

    • Example: for example we think ‘0910’ and 0910 as meaning 09:10 :00 ,i.e. 10 minutes after 9 0’ clock but reality is MYSQL understand them as 00:09:10 i.e 9 minutes and 10 second

String Types:

  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TEXT

Char And Varchar Types:

  • CHAR AND VARCHAR types are similar , but differ in the way they are stored and retrieved.
  • They also differ in maximum length.
Type DescriptionRange in Characters
CHAR It is used to specify a fixed length string that can contain numbers, letters, and special characters.The length can be any value from 0 to 255
VARCHAR It is used to specify a variable length string that can contain numbers, letters, and special characters.The length can be any value from 0 to 65535

Binary And Varbinary Types:

  • Similar to char and varchar.
  • But they are contain binary strings rather than non binary strings.