DATA TYPE OF MYSQL

 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(pd)

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(pd)

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(sized)

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.
Mysql 8.0 does not support the year in a two-digit format.

 

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

 

Post a Comment

0 Comments