WikiGalaxy
SQL Data Types
Introduction to SQL Data Types
Overview:
SQL data types define the type of data that can be stored in a table column. They ensure data integrity and optimize storage space.
Importance:
Choosing the correct data type is crucial for database performance, storage efficiency, and data integrity.
Categories:
SQL data types are categorized into numeric, character, date/time, and binary types.
Numeric Data Types
Integer Types:
Include INT, SMALLINT, and BIGINT, used for storing whole numbers.
Decimal Types:
Include DECIMAL and NUMERIC, used for fixed-point numbers with precision.
Floating Point Types:
Include FLOAT and DOUBLE, used for approximate numeric values.
CREATE TABLE Numbers (
id INT,
price DECIMAL(10, 2),
rating FLOAT
);
Character Data Types
CHAR and VARCHAR:
CHAR is for fixed-length strings, while VARCHAR is for variable-length strings.
TEXT:
Used for large amounts of text data, such as articles or descriptions.
CREATE TABLE TextData (
name VARCHAR(100),
description TEXT
);
Date and Time Data Types
DATE:
Stores date values without time components, e.g., 'YYYY-MM-DD'.
TIME:
Stores time values without date components, e.g., 'HH:MM:SS'.
TIMESTAMP:
Stores both date and time, often used for tracking changes.
CREATE TABLE Events (
event_date DATE,
event_time TIME,
last_updated TIMESTAMP
);
Binary Data Types
BINARY and VARBINARY:
Used for fixed and variable-length binary data, respectively.
BLOB:
Stands for Binary Large Object, used for storing binary data such as images or files.
CREATE TABLE Files (
file_name VARCHAR(255),
file_data BLOB
);
Boolean Data Type
BOOLEAN:
Represents true or false values, often used for flags or binary conditions.
CREATE TABLE Users (
username VARCHAR(50),
is_active BOOLEAN
);
Spatial Data Types
GEOMETRY:
Stores geometric data like points, lines, and polygons.
POINT:
Represents a single location in space.
LINESTRING:
Represents a sequence of points forming a line.
CREATE TABLE Locations (
id INT,
coordinates POINT
);
XML Data Type
XML:
Stores XML formatted data, useful for applications that require structured data exchange.
CREATE TABLE XmlData (
id INT,
data XML
);
