MySQL Cheat Sheet

This is a cheatsheet for MySQL. I hope you will find it interesting and helpful.

CREATE table

Name of the table is customer. Primary key is id.

CREATE TABLE `customer` (
    `id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(30) NOT NULL,
    `age` INT UNSIGNED DEFAULT NULL,
    `accountstatus` ENUM('ACTIVE', 'INACTIVE', 'DELETED') DEFAULT 'ACTIVE',
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id_customer_UNIQUE` (`id`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

Creating another table customerorder. Primary key is orderid and foreign key is customerid.

CREATE TABLE `customerorder` (
    `orderid` INT UNSIGNED AUTO_INCREMENT NOT NULL,
    `customerid` INT UNSIGNED NOT NULL,
    `amount` FLOAT(10 , 2 ) DEFAULT 0,
    `orderstatus` ENUM('PAID', 'DUE') DEFAULT 'DUE',
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`orderid`),
    UNIQUE KEY `orderid_customerorder_UNIQUE` (`orderid`),
    KEY `fk_customerid_customerorder` (`customerid`),
    CONSTRAINT `fk_customerid_customerorder` FOREIGN KEY (`customerid`)
        REFERENCES `customer` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

RENAME table

Say we have a table c1 and we want to rename it to c2.

RENAME TABLE c1 TO c2;

DROP table

Drop table c2.

DROP TABLE IF EXISTS c2;

INSERT

Inserting data into the customer table.

INSERT INTO `customer` (`name`, `age`, `lastmodified` , `created`)
VALUES
('Yusuf', 25, '2016-01-01 10:20:30', '2016-01-01 10:20:30');

Multiple Insert

INSERT INTO `customer` (`name`, `age`, `lastmodified` , `created`)
VALUES
('Yusuf', 25, '2016-01-01 10:20:30', '2016-01-01 10:20:30'),
('Dawood', 20, '2016-01-01 10:20:30', '2016-01-01 10:20:30');

INSERT INTO `customerorder` (`customerid`, `amount`, `orderstatus`, `lastmodified` , `created`)
VALUES
('1', 250, 'PAID', '2016-01-01 10:20:30', '2016-01-01 10:20:30'),
('2', 100, 'PAID', '2016-01-01 10:20:30', '2016-01-01 10:20:30'),
('1', 500, 'PAID', '2016-01-01 10:20:30', '2016-01-01 10:20:30'),
('1', 700, 'PAID', '2016-01-01 10:20:30', '2016-01-01 10:20:30'),
('2', 800, 'PAID', '2016-01-01 10:20:30', '2016-01-01 10:20:30');

SELECT

Select customer by id.

SELECT 
    *
FROM
    customer
WHERE
    id = 1;

Select only required columns by id.

SELECT 
    id AS customerid, name, age, accountstatus, lastmodified, created
FROM
    customer
WHERE
    id = 1;

SELECT - filter by date

Select customer filter by date.

SELECT 
    *
FROM
    customer
WHERE
    created >= '2016-01-01 00:00:00'
    AND created <= '2016-01-01 23:59:59';

SELECT - filter by exact text

Select customer filter by exact name.

SELECT 
    *
FROM
    customer
WHERE
    name = 'Yusuf';

SELECT - filter by starting text

Select customer whose name start with the given word.

SELECT 
    *
FROM
    customer
WHERE
    name LIKE 'Yu%';

SELECT - filter by ending text

Select customer whose name ends with the given word.

SELECT 
    *
FROM
    customer
WHERE
    name LIKE '%od';

SELECT - filter by text in the value

Select customer whose name contains a given word.

SELECT 
    *
FROM
    customer
WHERE
    name LIKE '%su%';

UPDATE

Update the customer table.

UPDATE customer 
SET 
    name = 'Yusuf',
    age = 25
WHERE
    id = 1;

DELETE

Delete from customer table.

DELETE FROM customer 
WHERE
    id = 1;

ADD Column

Add a new column dateofbirth to customer table.

ALTER TABLE customer
ADD COLUMN dateofbirth DATETIME DEFAULT NULL
AFTER age;

MODIFY Column

Modify column dateofbirth of customer table.

ALTER TABLE customer
MODIFY COLUMN dateofbirth DATE DEFAULT NULL
AFTER age;

Rename Column

Rename column dateofbirth to dob of customer table.

ALTER TABLE customer
CHANGE COLUMN dateofbirth dob DATE DEFAULT NULL
AFTER age;

Rename and modify Column

Rename column dob to dateofbirth and modify type DATE to DATETIME of customer table.

ALTER TABLE customer
CHANGE COLUMN dob dateofbirth DATETIME DEFAULT NULL
AFTER age;

DROP Column

Drop column dateofbirth of customer table.

ALTER TABLE customer
DROP COLUMN dateofbirth;

ADD UNIQUE KEY

Add Unique key orderid_customerorder_UNIQUE to customerorder table.

ALTER TABLE customerorder
ADD UNIQUE KEY `orderid_customerorder_UNIQUE` (`orderid`);

DROP UNIQUE KEY

Drop Unique key orderid_customerorder_UNIQUE of customerorder table.

ALTER TABLE customerorder
DROP INDEX orderid_customerorder_UNIQUE;

ADD Index

Add Index fk_customerid_customerorder to customerorder table.

ALTER TABLE customerorder
ADD INDEX `fk_customerid_customerorder` (`customerid`);

DROP Index

Drop index fk_customerid_customerorder of customerorder table.

ALTER TABLE customerorder
DROP INDEX fk_customerid_customerorder;

ADD Constraint - Foreign Key

Add foreign key customerid to customerorder table.

ALTER TABLE customerorder
ADD CONSTRAINT `fk_customerid_customerorder` FOREIGN KEY (`customerid`)
    REFERENCES `customer` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

DROP Constraint - Foreign Key

Drop constraint fk_customerid_customerorder of customerorder table.

ALTER TABLE customerorder
DROP FOREIGN KEY fk_customerid_customerorder;