In SQL, data types define the type of data that can be stored in a table's column. They help the database to understand what kind of data is expected and how much storage will be required. Choosing the right data type for each column is crucial for optimizing storage and ensuring data integrity.
Here are some of the most common data types used in SQL:
| Data Type | Description | Example |
|---|---|---|
INT |
Used for storing whole numbers (integers). | 123 |
VARCHAR(n) |
Used for variable-length strings, where "n" defines the maximum number of characters. | 'Hello World' |
TEXT |
Used for long text fields. Can store large amounts of text data. | 'A large text field...' |
DATE |
Stores date values in the format YYYY-MM-DD. | '2024-10-14' |
DECIMAL(p, s) |
Used for fixed-point numbers, where "p" is the total number of digits and "s" is the number of digits after the decimal point. | DECIMAL(5,2) → 123.45 |
FLOAT |
Used for floating-point numbers (decimal numbers). | 123.45 |
BOOLEAN |
Stores a TRUE or FALSE value. | TRUE / FALSE |
String data types are used to store text values. Here are some examples:
CHAR(n): Fixed-length string of "n" characters. Pads with spaces if necessary.VARCHAR(n): Variable-length string up to "n" characters. More efficient than CHAR for dynamic text fields.TEXT: Large variable-length string, used for long-form text (like descriptions).Numeric data types store numbers and are used for mathematical operations. The most common ones include:
INT: Whole numbers without decimals.DECIMAL(p, s): Fixed-point numbers with "p" total digits and "s" digits after the decimal point.FLOAT: Floating-point numbers for storing approximate decimal values.SQL provides several data types for storing date and time values:
DATE: Stores date values in the format YYYY-MM-DD.TIME: Stores time values in the format HH:MM:SS.DATETIME: Stores both date and time values.TIMESTAMP: Stores date and time, typically for tracking changes in records.