Datatypes in MYSQL

☞ Each column of the table is assigned a datatype which conveys the kind of value that will be stored in the column.

☞ SQL supports the following datatypes for the specification of various fields of a table.

1. Numeric
2. Non-numeric or String
3. Date and Time

Numeric

☞ Following are the Numeric data types.

1. INTEGER
2. FLOAT
3. NUMERIC
4. DECIMAL

☞ INTEGER :

• Syntax : INTEGER
• It stores positive whole number up to 11 digits.
• It stores negative whole number up to 10 digits.

☞ FLOAT :

• Syntax : FLOAT
• It stores number with decimal points.
• It occupies 4 bytes.

☞ NUMERIC :

• Syntax : NUMERIC(x,y)
• It stores number with decimal points.
• It holds up to 20 significant digits.
• Negative number holds one place for the sign.
• x = total number of digits
• y = number of places to the right of the decimal point.
• NUMERIC(10,3) stores a number that has 6 places before the decimal and 3 digits after the decimal and 1 digit for the decimal point.

☞ DECIMAL

• Syntax : DECIMAL(size, precision)
• It stores number with decimal points.
• size = total number of digits.
• precision = number of places to the right of the decimal point.
• DECIMAL(10,2) stores a number that has 8 places before the decimal and 2 digits after the decimal point.

Non-numeric or String

☞ Following are the Non-numeric/String data types.

1. CHARACTER (fixed length)
2. CHARACTER (variable length)

☞ CHARACTER (fixed length)

• Syntax : CHAR(x)
• It stores 'x' number of characters in the string.
• A maximum of 256 characters can be stored in a Char.
• If string is not as long as the 'size' parameter, the remaining spaces are left unused.
• The value is enclosed in a single or double quotation marks.
• CHAR(10) will store 10 characters. If string is having less character, then also it will consume space of 10 characters.

☞ CHARACTER (variable length)

• Syntax : VARCHAR(x)
• It stores 'x' number of characters in the string.
• If string is not as long as the 'size' parameter, the remaining spaces are released.
• The value is enclosed in a single or double quotation marks.
• VARCHAR(10) will store 10 characters. If string is having less character, then it will release unused memory spaces.

Date and Time

☞ Following are the Date and Time data types.

1. DATE
2. TIME

☞ DATE

• Syntax : DATE
• It stores a date in 'yyyy/mm/dd' format.
• It is enclosed in a single quotation marks.

☞ TIME

• Syntax : TIME
• It stores a time in hh:mm:ss format.