MySQL Cheatsheet

MySQL Cheatsheet

Show All Database

SHOW DATABASES

Create Database

CREATE DATABASE <database_name>

Select Database

USE <database_name>

Create Table

Simple Table With Primary & Unique Columns

CREATE TABLE users(
    id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    mobile DECIMAL(10,0),

    PRIMARY KEY(id),
    CONSTRAINT UNIQUE unique_email(email),
    CONSTRAINT UNIQUE unique_mobile(mobile)
)

Simple Table With Primary & Foreign Columns

CREATE TABLE addresses(
    id INT AUTO_INCREMENT,
    user_id INT NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255),
    state VARCHAR(255),
    country VARCHAR(255) DEFAULT 'India',

    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
        ON DELETE CASCADE
)

Table Cloning

CREATE TABLE users_copy LIKE users

Show All Tables

SHOW TABLES

Show All Indexes For A Table

SHOW INDEX FROM <table_name>

Reset Tables With Foreign Keys

-- Disable Foreign Checks
SET FOREIGN_KEY_CHECKS = 0;

-- Truncate users Table
TRUNCATE table users;

-- Enable Foreign Checks
SET FOREIGN_KEY_CHECKS = 1;

Insert Data

INSERT INTO users
    SET
        name = "Ravindra",
        email = "ravindra@example.com",
        mobile = "9876543210"
INSERT INTO users
    (name, email, mobile)
    VALUES
    ("Ravindra", "ravindra@example.com", "9876543210")

Original:  Updated: