Contents
Data Types
Description: Data types define the type of data that can be stored in a column. Choosing the correct data type ensures data integrity and optimizes storage.
Examples: INT for integers, VARCHAR for variable-length strings, DATE for dates, FLOAT for floating-point numbers.
CREATE TABLE Customers (
CustomerID int,
Name varchar(255),
Address varchar(255)
);
1.Numeric Data Types
1.1 INT
Description: The INT data type is used to store whole numbers without decimal points. It is commonly used for columns that will store integer values.
Example :
CREATE TABLE Products (
ProductID int,
Quantity int
);
Explanation: Useful for storing numerical values where decimals are not required, such as countable items.
1.2 FLOAT
Description: The FLOAT data type is used to store approximate numeric values with floating decimal points. It is suitable for scientific calculations and measurements.
Example :
CREATE TABLE Products (
ProductID int,
Price float
);
Explanation: Suitable for measurements or calculations where exact precision is not necessary.
1.3 DECIMAL
Description: The DECIMAL data type, also known as NUMERIC, is used to store exact numeric values with a fixed number of decimal places. It is useful for storing financial data where precision is important.
Example :
CREATE TABLE Products (
ProductID int,
Price decimal(10, 2)
);
Explanation: Ideal for financial data where precise values are critical.
2.Character String Data Types
2.1 CHAR
Description: The CHAR data type is used to store fixed-length character strings. The length of the string is specified in parentheses, and shorter strings are padded with spaces.
Example :
CREATE TABLE Employees (
EmployeeID int,
Initials char(3)
);
Explanation: Best used when data entries are of a consistent length.
2.2 VARCHAR
Description: The VARCHAR data type is used to store variable-length character strings. It can store up to the specified number of characters without padding.
Example :
CREATE TABLE Employees (
EmployeeID int,
FullName varchar(255)
);
Explanation: Provides flexibility for data that varies in length.
2.3 TEXT
Description: The TEXT data type is used to store large amounts of character data. It is suitable for storing long text entries such as articles, descriptions, and comments.
Example :
CREATE TABLE Articles (
ArticleID int,
Content text
);
Explanation: Suitable for storing long-form text such as articles or descriptions.
3.Date and Time Data Types
3.1 DATE
Description: The DATE data type is used to store date values in the format YYYY-MM-DD.
Example :
CREATE TABLE Events (
EventID int,
EventDate date
);
Explanation: Useful for storing specific dates (year, month, day).
3.2 TIME
Description: The TIME data type is used to store time values in the format HH:MI:SS.
Example :
CREATE TABLE Events (
EventID int,
EventTime time
);
Explanation: Ideal for capturing times without a date component.
3.3 DATETIME
Description: The DATETIME data type is used to store date and time values in the format YYYY-MM-DD HH:MI:SS.
Example :
CREATE TABLE Events (
EventID int,
EventTimestamp datetime
);
Explanation: Essential for recording exact moments in time (date and time).
3.4 TIMESTAMP
Description: The TIMESTAMP data type is similar to DATETIME but also includes a time zone offset. It is used to track changes to records with a time component.
Example :
CREATE TABLE Events (
EventID int,
CreatedAt timestamp
);
Explanation: Useful for applications requiring awareness of time zone differences, typically in distributed systems.
4.Binary Data Types
4.1 BINARY
Description: The BINARY data type is used to store fixed-length binary data. The length of the data is specified in parentheses.
Example :
CREATE TABLE Files (
FileID int,
FileData binary(50)
);
Explanation: Best for fixed-length binary data like hashed values or fixed-size buffers.
4.2 VARBINARY
Description: The VARBINARY data type is used to store variable-length binary data. It can store up to the specified number of bytes.
Example :
CREATE TABLE Files (
FileID int,
FileData varbinary(max)
);
Explanation: Ideal for binary data of varying lengths, such as files or images.
5.Miscellaneous Data Types
5.1 BOOLEAN
Description: The BOOLEAN data type is used to store true or false values. It is commonly used for columns that need to store binary states.
Example :
CREATE TABLE Features (
FeatureID int,
IsActive boolean
);
Explanation: Used for storing binary states or flags in a database.
5.2 ENUM
Description: The ENUM data type is used to store a predefined set of values. Each value in the ENUM list is assigned an index, and the column can only store one of these values.
Example :
CREATE TABLE Statuses (
StatusID int,
Status enum('Active', 'Inactive', 'Pending')
);
Explanation: Useful for columns that contain a limited set of possible values, providing clarity and data integrity.