DATA TYPE OF MYSQL
·
A data
type specifies a value type of data, ex. integer, floating-point, character,
Boolean, etc with sign(positive or negative).
·
All Operations
are performed on the data type.
·
SQL used
various data types which are:-
Some common data
types in MySQL are:
1. Numeric Data
Types:
- INT: Used for storing integers.
Example: `age INT`.
- DECIMAL: Used for storing fixed-point
numbers.
Example: `price DECIMAL(10,2)` (10 digits
total, 2 decimal places).
- FLOAT: Used for storing floating-point
numbers.
Example: `height FLOAT`.
2. String Data
Types:
- VARCHAR: Used for variable-length character
strings.
Example: `name VARCHAR(50)` (maximum
length of 50 characters).
- CHAR: Used for fixed-length character
strings.
Example: `code CHAR(10)` (always 10
characters long).
- TEXT: Used for storing large text
data.
Example: `description TEXT`.
3. Date and Time
Data Types:
- DATE: Used for storing dates in
'YYYY-MM-DD' format.
Example: `birthdate DATE`.
- TIME: Used for storing time values
in 'HH:MM:SS' format.
Example: `logintime TIME`.
- DATETIME: Used for storing
date and time values in 'YYYY-MM-DD HH:MM:SS' format.
Example: `created_at DATETIME`.
4. Boolean Data
Type:
- BOOLEAN: Used for storing boolean
values (TRUE or FALSE).
Example: `is_active BOOLEAN`.
5. Binary Data
Types:
- BLOB:
Used for storing large binary objects such as images, audio, or video
files.
Example: `image BLOB`.
6. Enumerated Data
Type:
- ENUM: Used for defining a list of
possible values.
Example: `gender ENUM('Male', 'Female',
'Other')`.
In details
String Data Types
|
Data type |
Description |
|
CHAR(size) |
A FIXED length character, stores letters, numbers, and special characters. Size:- from 0 to 255. Default is 1 |
|
VARCHAR(size) |
A VARIABLE length string stores letters, numbers, and special characters. size:- from 0 to 65535 |
|
BINARY(size) |
Equal to CHAR(), but stores binary byte strings. |
|
VARBINARY(size) |
Equal to VARCHAR(), but stores binary byte strings. |
|
TINYBLOB |
For Binary Large Objects. Max length: 255 bytes |
|
TINYTEXT |
String, the maximum length of 255 characters |
|
TEXT(size) |
String, the maximum length of 65,535 bytes |
|
BLOB(size) |
For Binary Large Objects. size up to 65,535 bytes. |
|
MEDIUMTEXT |
String, the maximum length of 16,777,215 characters |
|
MEDIUMBLOB |
For Binary Large Objects. size up to 16,777,215 bytes of data |
|
LONGTEXT |
String, size maximum length of 4,294,967,295 characters |
|
LONGBLOB |
For Binary Large Objects. size up to 4,294,967,295 bytes of data |
|
ENUM(val1) |
Character stores only one value. |
|
SET(val1, val2, val3, ...) |
String object, stores 0 or more values from a list of possible values. The max value of the list is up to 64 in a setlist. |
Numeric Data Types
|
Data type |
Description |
|
BIT(size) |
A bit-value type. size:- from 1 to 64. default value is
1. |
|
TINYINT(size) |
Very small integer. The signed range is from -128 to 127. The unsigned range
is from 0 to 255. The size is 255. |
|
BOOL or BOOLEAN |
Zero is considered false, and 1 value is considered as true. |
|
SMALLINT(size) |
Small integer. The signed range is from -32768 to 32767. The unsigned range is
from 0 to 65535. Size:- maximum
width is 255. |
|
MEDIUMINT(size) |
Medium integer. The signed range is from -8388608 to 8388607. The unsigned range is from 0 to 16777215. Size:- maximum width is 255. |
|
INT(size) or INTEGER(size) |
Medium integer. The signed range is from -2147483648 to 2147483647. The unsigned range is from 0 to 4294967295. Size:- maximum width is 255. |
|
BIGINT(size) |
Large integer. The signed range is from -9223372036854775808 to
9223372036854775807. The unsigned range is from 0 to 18446744073709551615. Size:-
maximum width is 255. |
|
FLOAT(p, d) |
Floating point number. P is precision (before decimal value) and d
is a number of digits after the decimal point. Signed number from -3.402823466e+38 to
-1.175494351e-38, and unsigned number from 0 and 1.175494351e-38
to 3.402823466e+38. |
|
DOUBLE(p, d) |
Large size to float. Size:- SIGNED – from -1.7976931348623157E+308
to -2.2250738585072014E-308 and UNSIGNED from 0 and
2.2250738585072014E-308 to 1.7976931348623157E+308. |
|
DECIMAL(size, d) |
Fixed-point number. The maximum number for size is
65. The maximum number for d is 30. The default value
for size is 10. The default value for d is
0. |
Note: the main difference between float and
double data types is precision (from 0 to 23 for FLOAT, and from 24 to 53 for
DOUBLE) and accuracy (up to approximately 7 decimals for FLOAT, and up to
approximately 15 decimals for DOUBLE)
Date and Time Data Types
|
Data type |
Description |
|
DATE |
Date. Format: yyyy-mm-dd. The supported range is
from '1000-01-01' to '9999-12-31' |
|
DATETIME(fsp) |
Date and time combination. Format: yyyy-mm-dd
hh:mm: ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31
23:59:59'. |
|
TIMESTAMP(fsp) |
Timestamp. Format: yyyy-mm-dd hh:mm: ss. The
supported range is from '1970-01-01 00:00:01' |
|
TIME(fsp) |
Time. Format: hh:mm: ss. The supported range is
from '-838:59:59' to '838:59:59' |
|
YEAR |
Year in four-digit format. Format: 1901 to 2155,
and 0000. |
MS Access Data Types
|
Data type |
Description |
Storage |
|
Text |
Text or combinations of text and numbers. 255 characters maximum |
|
|
Memo |
Used for larger amounts of text. Stores up to 65,536 characters. Note: cannot sort a memo field. They are
searchable |
|
|
Byte |
Whole numbers from 0 to 255 |
1 byte |
|
Integer |
Whole numbers between -32,768 and 32,767 |
2 bytes |
|
Long |
Whole numbers between -2,147,483,648 and 2,147,483,647 |
4 bytes |
|
Single |
Single precision floating-point. |
4 bytes |
|
Double |
Double-precision floating-point. |
8 bytes |
|
Currency |
Use for currency. Store up to 15 digits of whole dollars, plus 4
decimal places. |
8 bytes |
|
AutoNumber |
Auto number fields automatically give each record its own number,
usually starting at 1 |
4 bytes |
|
Date/Time |
Use for dates and times |
8 bytes |
|
Yes/No |
Logical field, use as Yes/No, True/False, or On/Off. (equivalent to -1
and 0). Note: Null values are not allowed in Yes/No fields |
1 bit |
|
Ole Object |
Store pictures, audio, video, or other blobs (Binary Large Objects) |
up to 1GB |
|
Hyperlink |
Contain links to other files, including web pages |
|
|
Lookup Wizard |
A list of options, which can be chosen from a drop-down list |
4 bytes |
0 Comments