SQL Data Types - Class 12 PDF Download

Introduction

SQL data types are foundational to database design, defining the nature of data stored in each column of a table. They ensure data integrity, optimize storage, and enable efficient data retrieval. Key types include numeric (e.g., INT, FLOAT), character (e.g., CHAR, VARCHAR), date/time (e.g., DATE, TIMESTAMP), and binary (e.g., BLOB). Each type serves a specific purpose: numeric types handle mathematical operations, character types store text, date/time types manage temporal data, and binary types store large data like images. Understanding SQL data types is crucial for creating robust, efficient, and scalable databases.

Data Types Classification

Data types mainly classified into three categories for every database.

• String Data types
• Numeric Data types
• Date and time Data types

Data Types in MySQL

MySQL support various data types to manage data efficiently. These include numeric types (e.g., INT, FLOAT), character types (e.g., CHAR, VARCHAR), and date/time types (e.g., DATE, TIMESTAMP). 

MySQL Data Types

A list of data types used in MySQL database. This is based on MySQL 8.0.

MySQL String Data Types

CHAR(Size): CHAR(Size) is used to specify a fixed length string that can contain numbers, letters, and special characters. Its size can range from 0 to 255 characters, with the default size being 1.VARCHAR(Size)

VARCHAR(Size): It is used to specify a variable length string that can also contain numbers, letters, and special characters, but unlike CHAR, its size can range from 0 to 65535 characters.

BINARY(Size): BINARY(Size) is equivalent to CHAR() but stores binary byte strings instead of text. Its size parameter specifies the column length in bytes, with the default being 1.

VARBINARY(Size): VARBINARY(Size) is similar to VARCHAR() but for binary byte strings. Its size parameter specifies the maximum column length in bytes.

TEXT(Size): TEXT(Size) accommodates strings that can contain a maximum of 255 characters, ideal for storing large amounts of text that exceed the limitations of CHAR or VARCHAR.

TINYTEXT: TINYTEXT is a data type that holds strings with a maximum length of 255 characters, similar to the TEXT data type but optimized for smaller text.

MEDIUMTEXT: MEDIUMTEXT allows for a string with a maximum length of 16,777,215 characters, suitable for medium to large amounts of text.

LONGTEXT: LONGTEXT is designed for storing very large amounts of text, with a capacity of up to 4,294,967,295 characters.

ENUM(val1, val2, val3,...): ENUM is used for string objects that have only one value from a pre-defined set. An ENUM list can contain up to 65535 values, and inserting a value not in the list results in a blank value.

SET(val1, val2, val3,...): SET is a data type that specifies a string capable of holding one or more values from a set list, allowing up to 64 values at one time.

BLOB(size): BLOB(size) is utilized for storing Binary Large Objects (BLOBs), such as images or other binary data, with a maximum storage capacity of 65,535 bytes.

MySQL Numeric Data Types

BIT(Size): BIT(Size) is used for a bit-value type. The number of bits per value is specified in size, which can range from 1 to 64, with the default value being 1.

INT(size): INT(size) is used for integer values. Its signed range varies from -2,147,483,648 to 2,147,483,647, and its unsigned range varies from 0 to 4,294,967,295. The size parameter specifies the maximum display width up to 255.

INTEGER(size): INTEGER(size) is simply an alias for INT(size), sharing the same properties and constraints.

FLOAT(size, d): FLOAT(size, d) specifies a floating point number with a total number of digits defined by the size and the number of digits after the decimal point by d parameter.

FLOAT(p): FLOAT(p) uses the parameter p to determine the floating point precision. If p is between 0 to 24, the data type defaults to FLOAT(0). If p is between 25 to 53, it defaults to DOUBLE(0).

DOUBLE(size, d): DOUBLE(size, d) is a normal size floating point number. It uses size to specify the total number of digits and d to specify the number of digits after the decimal.

DECIMAL(size, d): DECIMAL(size, d) specifies a fixed point number. The size determines the total number of digits, and d specifies the number of digits after the decimal. The maximum value for size is 65, and for d is 30, with a default of 0.

DEC(size, d): DEC(size, d) is equivalent to DECIMAL(size, d), offering precise control over the numeric storage with a fixed decimal point.

BOOL: BOOL is used for Boolean values, where zero is considered as false and any nonzero value is considered as true.

MySQL Date and Time Data Types

DATE: DATE data type is used to specify date in YYYY-MM-DD format. The valid range for DATE values is from '1000-01-01' to '9999-12-31'.

DATETIME(fsp): DATETIME(fsp) data type combines date and time, formatted as YYYY-MM-DD hh:mm:ss. Its value range spans from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Fractional seconds precision (fsp) can be specified if required.

TIMESTAMP(fsp): TIMESTAMP(fsp) is used for recording moments in time as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). It supports values from '1970-01-01 00:00:01' UTC to '2038-01-01 03:14:07' UTC. Fractional seconds precision can also be applied here.

TIME(fsp): TIME(fsp) data type is used to specify time values without a date, formatted as hh:mm:ss. It accommodates time values ranging from '-838:59:59' to '838:59:59', and can include fractional seconds precision.

YEAR: YEAR data type is used to store a year in four-digit format, supporting values from 1901 to 2155, and 0000.