Let us try to understand SQL with an example of a blogging site in MySQL.

Here is how we can create a database named blog:

CREATE DATABASE blog;

Now, we will create a table named users in the blog database:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT(1) DEFAULT 0,
    INDEX email_idx (email)  -- Added index for email lookups
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 

Let us explain the above SQL query:

  • CREATE TABLE is a SQL command used to create a new table.
  • users is the name of the table.
  • id, name, email, password, created_at, updated_at, and deleted are the columns in the table.
  • INT, VARCHAR(50), TIMESTAMP, and BOOLEAN are the data types of the columns.
  • charset=utf8 collate=utf8_general_ci specifies the character set and collation for the table.

Data type definitions:

  • INT: Integer, can store whole numbers ranging from -2147483648 to 2147483647.

  • VARCHAR(50): Variable-length character string with a maximum length of 50 characters in our case.

  • UNIX_TIMESTAMP(): MySQL function that returns current Unix timestamp.

  • TINYINT(1): A single-byte integer that is being used as a boolean since BOOLEAN is not supported in MySQL.

Constraints used in the query:

  • PRIMARY KEY: A column or a group of columns that uniquely identifies each row in a table.
  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that all values in a column are different.
  • DEFAULT: Specifies a default value for a column when no value is specified.
  • ON UPDATE: Sets the column to the current timestamp when the row is updated.

Let us now create a table named posts in the blog database:

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted TINYINT(1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,  -- Added ON DELETE
    INDEX user_id_idx (user_id)  -- Added index for foreign key
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Let us explain the above SQL query:

  • CREATE TABLE is a SQL command used to create a new table.
  • posts is the name of the table.
  • id, user_id, title, body, created_at, updated_at, and deleted are the columns in the table.
  • INT, VARCHAR(50), TEXT, and TIMESTAMP are the data types of the columns.

Data type definitions:

  • INT: Refer to the explanation above.
  • VARCHAR(50): Refer to the explanation above.
  • TEXT: A string data type with a variable length. The maximum length is 65,535 characters.
  • UNIX_TIMESTAMP(): Refer to the explanation above.
  • TINYINT(1): Refer to the explanation above.
  • FOREIGN KEY: A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

Constraints used in the query: