MySQL

In MySQL, data types define the type of data that can be stored in a column. Choosing the appropriate data type helps ensure efficient storage and processing. For example, employee IDs should be stored as integers, while names and descriptions are stored as text. Below, you’ll find a list of commonly used data types, categorized for different types of information, followed by table definitions for company-related data like employees, departments, and branches.

Common Data Types in MySQL

Below is a list of 30+ data types commonly used in MySQL, organized by their function.

1. Numeric Data Types: These data types are used for storing numbers, often for IDs, salaries, and counts.
   1. INT: Stores whole numbers. Example: Employee ID.
   2. BIGINT: Stores large integers.
   3. SMALLINT: Stores smaller integers with less storage.
   4. TINYINT: Stores very small integers (0-255).
   5. DECIMAL(p,s): Stores fixed-point numbers. Example: Salaries.
   6. FLOAT: Stores floating-point numbers for precision-based calculations.
   7. DOUBLE: A double-precision floating-point number, for more precision than FLOAT.
   8. BIT: Stores bit values (0 or 1). Example: Active/Inactive status.
   9. BOOL/BOOLEAN: Alias forTINYINT(1), stores true/false values.
2. String Data Types: These types store text data such as names, emails, and descriptions.
   1. CHAR(n): Fixed-length strings. Example: Department codes.
   2. VARCHAR(n): Variable-length strings. Example: Employee names.
   3. TEXT: Large text fields, often used for descriptions.
   4. TINYTEXT: Smaller text fields.
   5. MEDIUMTEXT: Medium-sized text fields.
   6. LONGTEXT: Very large text fields.
   7. BINARY(n): Fixed-length binary data.
   8. VARBINARY(n): Variable-length binary data.
   9. ENUM: A string object that can have one value chosen from a list. Example: Employee job roles.
   10. SET: Similar toENUMbut allows multiple values from a list.
3. Date and Time Data Types: Used to store date and time values, ideal for tracking employee start dates or branch creation dates.
   1. DATE: Stores only the date (YYYY-MM-DD).
   2. TIME: Stores time (HH:MM:SS).
   3. DATETIME: Stores both date and time (YYYY-MM-DD HH:MM:SS).
   4. TIMESTAMP: Records date and time with time zone info.
   5. YEAR: Stores a year as a 4-digit value.
4. Spatial Data Types: These types are used to store geographical data like branch locations.
   1. GEOMETRY: Stores geometric data.
   2. POINT: Represents a point in space (X, Y coordinates).
   3. LINESTRING: Stores a path consisting of points.
   4. POLYGON: Stores polygons, often used for mapping areas.
5. Other Data Types: Specialized types for unique use cases.
   1. JSON: Stores JSON-formatted data. Example: Employee attributes.
   2. BLOB: Stores binary large objects, like files or images.
   3. TINYBLOB: Smaller binary large objects.
   4. MEDIUMBLOB: Medium-sized binary large objects.
   5. LONGBLOB: Large binary large objects.
   6. UUID: A 128-bit universally unique identifier.

Key Data Types in Company Management Tables

1. Company Table
   • Stores details about the company
   • Common data types used:

   CREATE TABLE Company (
   CompanyID INT PRIMARY KEY AUTO_INCREMENT,
   CompanyName VARCHAR(100) NOT NULL,
   FoundedDate DATE,
   Headquarters VARCHAR(255)
   );

2. Employees Table
   • This table holds employee information
   • Common data types used:

   CREATE TABLE Employees (
   EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
   FirstName VARCHAR(50) NOT NULL,
   LastName VARCHAR(50) NOT NULL,
   DepartmentID INT,
   Salary DECIMAL(10, 2),
   HireDate DATE
   );

3. Departments Table
   • Stores department details like department name and codes.
   • Common data types used:

   CREATE TABLE Departments (
   DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
   DepartmentName VARCHAR(100) NOT NULL,
   DepartmentCode CHAR(5) NOT NULL
   );

4. Branches Table
   • Stores details of branch locations, including geographical data.
   • Common data types used:

   CREATE TABLE Branches (
     BranchID INT PRIMARY KEY AUTO_INCREMENT,
     BranchName VARCHAR(100) NOT NULL,
     Location POINT
   );

Complete Code Sample

Here’s the combined SQL code for defining Company, Employees, Departments, and Branches tables with appropriate data types.

CREATE TABLE Company (
  CompanyID INT PRIMARY KEY AUTO_INCREMENT,
  CompanyName VARCHAR(100) NOT NULL,
  FoundedDate DATE,
  Headquarters VARCHAR(255)
);

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  DepartmentID INT,
  Salary DECIMAL(10, 2),
  HireDate DATE
);

CREATE TABLE Departments (
  DepartmentID INT PRIMARY KEY AUTO_INCREMENT,
  DepartmentName VARCHAR(100) NOT NULL,
  DepartmentCode CHAR(5) NOT NULL
);

CREATE TABLE Branches (
  BranchID INT PRIMARY KEY AUTO_INCREMENT,
  BranchName VARCHAR(100) NOT NULL,
  Location POINT
);

This content provides an overview of 30+ MySQL data types, ensuring that each column in the Company, Employees, Departments, and Branches tables can store the right kind of data.

Sample implementation of MySQL data types

CREATE TABLE customer(
customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(300),
profile_picture BLOB,
gender CHAR(1),
birth_year YEAR,
complete_address JSON,
joining_date DATE,
active_flag TINYINT,
salary MEDIUMINT,
credit_limit SMALLINT,
to_date_purchase_amount DECIMAL(10,2),
marital_status ENUM('Single', 'Married', 'Divorced', 'Widowed'),
education SET('Bachelors', 'Masters', 'PHD'),
feedback TEXT,
record_creation TIMESTAMP);

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100),
encrypted_security_question VARBINARY(512),
encrypted_security_answer VARBINARY(512),
password_hash BINARY(64) -- Assuming 64-byte hash value (e.g., SHA-256)
);

CREATE TABLE simulation_results (
id INT AUTO_INCREMENT PRIMARY KEY,
simulation_id INT,
experiment_start_time DATETIME,
experiment_duration TIME,
pressure DOUBLE,
velocity FLOAT,
energy DOUBLE
);

To gain complete access, login with gmail or outlook, no need of signup, click here