SQL Data Types

SQL Data Types

SQL (Structured Query Language) is used to manage and manipulate data in relational databases. Each column in a table has a specific data type that defines the type of data it can hold. These data types help ensure data integrity and optimize storage.

Here’s a breakdown of SQL data types, categorized with syntax and examples:

1. Numeric Data Types

Numeric data types store numerical values, including integers, decimals, and floating-point numbers.

Integer Types:

• TINYINT: A very small integer.

  • Syntax: TINYINT[(M)] [UNSIGNED] [ZEROFILL]
  • Example:
    Copy code

      CREATE TABLE example (
          small_value TINYINT
      );
    

• SMALLINT, MEDIUMINT, INT (or INTEGER), BIGINT: Store increasingly larger integer values.

  • Example:
    Copy code

      CREATE TABLE example (
          medium_value MEDIUMINT,
          large_value BIGINT
      );
    

Decimal Types:

• DECIMAL (or NUMERIC): For exact fixed-point numbers.
  • Syntax: DECIMAL[(precision, scale)]
  • Example:
    Copy code

      CREATE TABLE example (
          price DECIMAL(10, 2) -- 10 digits, 2 after the decimal point
      );
    

Floating-Point Types:

• FLOAT, DOUBLE (or REAL): For approximate values.
  • Example:
    Copy code

      CREATE TABLE example (
          weight FLOAT,
          height DOUBLE
      );
    

2. Character/String Data Types

These store alphanumeric data.

Fixed-Length:

• CHAR: Stores fixed-length strings.
  • Syntax: CHAR(M)
  • Example:
    Copy code

      CREATE TABLE example (
          fixed_name CHAR(10)
      );
    

Variable-Length:

• VARCHAR: Stores variable-length strings.
  • Syntax: VARCHAR(M)
  • Example:
    Copy code

      CREATE TABLE example (
          dynamic_name VARCHAR(50)
      );
    

Large Text:

• TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT: Store large amounts of text.
  • Example:
    Copy code

      CREATE TABLE example (
          description TEXT
      );
    

3. Date and Time Data Types

These types store dates, times, or combinations of both.

Types:

• DATE: Stores a date (YYYY-MM-DD).

  • Example:
    Copy code

      CREATE TABLE example (
          birth_date DATE
      );
    

• TIME: Stores time (HH:MM:SS).

  • Example:
    Copy code

      CREATE TABLE example (
          event_time TIME
      );
    

• DATETIME, TIMESTAMP: Stores date and time.

  • Example:
    Copy code

      CREATE TABLE example (
          order_datetime DATETIME,
          last_updated TIMESTAMP
      );
    

• YEAR: Stores a year (YYYY).

  • Example:
    Copy code

      CREATE TABLE example (
          year_of_release YEAR
      );
    

4. Boolean Data Type

• BOOLEAN: Represents TRUE or FALSE (internally stored as TINYINT(1)).
  • Example:
    Copy code

      CREATE TABLE example (
          is_active BOOLEAN
      );
    

5. Binary Data Types

Binary data types store binary strings.

Types:

• BINARY, VARBINARY: Fixed and variable-length binary data.

  • Example:
    Copy code

      CREATE TABLE example (
          binary_data BINARY(16),
          varbinary_data VARBINARY(255)
      );
    

• BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB: Store binary large objects.

  • Example:
    Copy code

      CREATE TABLE example (
          image_data BLOB
      );
    

6. Spatial Data Types

Used for geographical data.

Types:

• GEOMETRY, POINT, LINESTRING, POLYGON: Store spatial data.
  • Example:
    Copy code

      CREATE TABLE example (
          location POINT
      );
    

General Syntax Example

Here’s a table creation example using multiple data types:

  CREATE TABLE employees (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100),
      salary DECIMAL(10, 2),
      hire_date DATE,
      is_manager BOOLEAN
  );

In this example:

• id is an integer with auto-increment.
• name is a variable-length string.
• salary has a fixed decimal format.
• hire_date stores date information.
• is_manager is a boolean flag.

By understanding these data types and using them appropriately, you can design efficient, maintainable database schemas.