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
, anddeleted
are the columns in the table.INT
,VARCHAR(50)
,TIMESTAMP
, andBOOLEAN
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 sinceBOOLEAN
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
, anddeleted
are the columns in the table.INT
,VARCHAR(50)
,TEXT
, andTIMESTAMP
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:
PRIMARY KEY
: Refer to the explanation above.NOT NULL
: Refer to the explanation above.DEFAULT
: Refer to the explanation above.ON UPDATE
: Refer to the explanation above.REFERENCES
: A constraint that defines a relationship between two tables.