ishanshre

Posted on Mar 5, 2023

MySql CheatSheet

SQL Commands

Create Database

Create new database or schema
Command - CREATE DATABASE <DATABASE NAME>;
Eg:- CREATE DATABASE myDB;

Drop Database

Delete the database
Command - DROP DATABASE <DATABASE NAME>;
Eg:- DROP DATABASE myDB;

Use Database

-Set the database default

Command- USE <DATABASE NAME>;
Eg:- USE myDB;

Alter Database

We can change the database to read only and vice versa. When the database is read only we can only view its data. We cannot do any operations in database except reading and altering the read only to false or 0
Command- ALTER <DATABASE NAME> READ ONLY=1;
Command- ALTER <DATABASE NAME> READ ONLY=0;

Table

Create Table

Use CREATE to create new table in the database
Command:-

CREATE TABLE <TABLE NAME> (
column 1 DATATYPE,
column 2 DATATYPE,
column 3 DATATYPE,
… 
);

CREATE TABLE user (
    user_id INT,
    username VARCHAR(50),
    password VARCHAR(50),
    joined_date DATE,
    last_login DATE
);

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    created DATE DEFAULT (CURRENT_DATE())
);

Rename Table

Command:- RENAME TABLE <TABLE NAME> TO <NEW TABLE NAME>;
Eg:- RENAME TABLE user TO account;

Alter Table

Alters the table details
Commands:-
Add column

ALTER TABLE account
ADD phone_number VARCHAR(10);

Rename column

ALTER TABLE account
RENAME phone_number to email;

Change column datatype

ALTER TABLE account
MODIFY COLUMN email VARCHAR(255);

Change the column order

ALTER TABLE account
MODIFY email VARCHAR(255)
AFTER password;

Drop Column

ALTER TABLE account
DROP COLUMN email

Insert Rows

Insert single data or single row

INSERT INTO account
VALUES (1,"user1","userPassword1","2021-01-23","2022-12-01");

Insert multiple rows or multiple data

INSERT INTO user
VALUES (3, "admin1","admin1pass","2021-01-02","2022-01-09"),
    (3, "admin2","admin2@pass","2021-01-02","2022-01-09"),
    (4, "admin3","admin3@pass","2021-01-02","2022-01-09"),
    (5, "admin4","admin4@pass","2021-01-02","2022-01-09");

Insert data into selected columns of the row

INSERT INTO user (user_id, username, password)
VALUES (6, "admin5","admin6pass");

Update columns data with where clause

UPDATE accounts
SET password = "hello@123"
WHERE user_id = 2;

Delete row data(where clause is important in DELETE command other wise our whole data will be deleted

DELETE FROM accounts
WHERE user_id = 2;

Enable/Disable safe mode in MySql

SET SQL_SAFE_UPDATES = 0

SET SQL_SAFE_UPDATES = 1

Disable autocommit

SET AUTOCOMMIT = OFF;

Manually create a save point and make changes

COMMIT;

Roll Back Changes(only possible if commit changes

ROLLBACK;

Add UNIQUE Constraints to column when creating a table

CREATE TABLE profile (
    profile_id INT,
    name VARCHAR(50) UNIQUE,
    age INT
);

Add UNIQUE to a column of a table

ALTER TABLE profile
ADD CONSTRAINT
UNIQUE (profile_id);

Set Column NOT NULL when creating a table

CREATE TABLE student (
    student_id INT NOT NULL,
    class INT
);

Set column NOT NULL for existing tables

ALTER TABLE student
MODIFY class INT NOT NULL;

Set a default value new table

CREATE TABLE employees (
   id INT,
   name VARCHAR(255),
   salary DECIMAL(10,2) DEFAULT 0
);

Set a default value for existing table

ALTER TABLE employees 
ALTER salary SET DEFAULT 10;

Set Primary key for new table

CREATE TABLE employees (
     id INT PRIMARY KEY,
     name VARCHAR(50)
);

Set Primary key for existing table

ALTER TABLE employees
ADD CONSTRAINT
PRIMARY KEY(id);

Set Primary key for new table with auto increment

CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Set auto_increment value starts from

ALTER TABLE employee 
AUTO_INCREMENT = 1000;

Creating a foriegn key(one to many relationship)

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(5),
);

INSERT INTO customers (name)
VALUES ("ishan"),("binod"),("anil");
SELECT * FROM customers;

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(10,2),
    customer_id INT,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
);
SELECT * FROM orders;

Drop the foreign key

ALTER TABLE orders 
DROP FOREIGN KEY orders_ibfk_1

Add foriegn key to existing table with custom name

ALTER TABLE orders 
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id);

Inner Join

Select records from two tables having matching values in both table

SELECT customers.customer_id, customers.name, orders.amount
FROM customers INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Left join

Selects all records from left table
Right side table records are not displayed if record does not matches from the left

SELECT *
FROM customers LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Right join

Selects all the records from the right table
Selects all the records from the left table that matches with the right table

SELECT *
FROM customers RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

COUNT function

Counts the records in that column

SELECT COUNT(amount) AS no_of_amounts
FROM orders;

MAX Function

Max returns the highest order of the record in the column

SELECT MAX(amount) as maximum_amount
FROM orders;

SELECT MAX(name) AS large_name
FROM customers;

MIN function

MIN returns the lowest order of the record in the column

SELECT MIN(amount) as minimum_amount
FROM orders;

SELECT MIN(name) AS small_name 
FROM customers;

AVG function

AVG returns the average of the records in the columns

SELECT AVG(amount) AS average_amount
FROM orders;

SUM function

SUM returns the sum of the records in the columns

SELECT SUM(amount) AS sum_of_amount
FROM orders;

CONCAT function

returns a records concatinating two or more columns

SELECT CONCAT(first_name, " ", last_name) AS "Full Name"
FROM employees;

AND, OR, NOT, BETWEEN AND IN

They are logical keywords for logical operations

SELECT *
FROM employees
WHERE hire_date < "2015-01-15 AND job = "Teacher";

SELECT * 
FROM employees
WHERE job = "Manager" OR job = "Boxer";

SELECT *
FROM employees
WHERE NOT age = 20;

SELECT * 
FROM employees
WHERE NOT age = 23 AND NOT age = 40;

SELECT *
FROM employees
WHERE hire_date BETWEEN "2022-5-20" AND "2023-1-2";

SELECT *
FROM employees
WHERE jobs IN ("Cook","Teacher","Doctor","Manager");

Wildcards (%, _)

% represents number of characters
_ represents only one characters

SELECT *
FROM employees
WHERE first_name LIKE "I%";

SELECT *
FROM employees
WHERE last_name LIKE "%t";

SELECT *
FROM employees
WHERE jobs LIKE "_OO_";

SELECT *
FROM employees
WHERE jobs LIKE "_e_ach_r";

SELECT *
FROM employees
WHERE hire_date LIKE "____-01-__";

SELECT *
FROM employees
WHERE last_name LIKE "_a%";

ORDER BY

Default order : Ascending (ASC keyword)
To order in reverse, add DESC keyword

SELECT * FROM employees
ORDER BY last_name;

SELECT * FROM employees
ORDER BY first_name DESC;

SELECT * FROM employees
ORDER BY hire_date DESC;

LIMIT and OFFSET

LIMIT is used for returning limited records, especially usefull for large datasets
OFFSET is used to display next records to limit. Especially used in pagination

-- Displays next 10 records i.e. record from 11 to 20
SELECT * FROM employees
LIMIT 10
OFFSET 1;

UNION and UNION ALL

Combines the result of two or more tables
Must have same number of columns in the tables
UNION remove the duplicates
UNION ALL allows duplicates

SELECT first_name,  last_name FROM employees
UNION
SELECT first_name, last_name FROM customers

SELECT * FROM incomes
UNION
SELECT * FROM expences

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers

Self Join

Join a table to itself
Comparing rows with other rows in same table

SELECT a.first_name, a.last_name, 
       CONCAT(b.first_name,b.last_name) as "Referred by"
FROM customers as a
INNER JOIN customers as b
ON a.referred_id = b.customer_id;

SELECT a.first_name, a.last_name
       CONCAT(b.first_name, b.last_name) as "Supervised by"
FROM employees as a
LEFT JOIN employees as b
ON a.supervisor_id = b.employee_id;

VIEW

VIEW is a virtual table of a result of a query
Can perform operations of table in view as well
VIEW get updated as corresponding table updates

CREATE VIEW employeee_attendances AS
SELECT first_name, last_name
FROM employees;

CREATE VIEW email_employees_list AS
SELECT email 
FROM employees;

INDEX

INDEX is applied to the column
INDEX is a BTree data structure
It is used for increasing the speed of searching
Disadvantage of Index is that it slows down the update
Longer the column, longer the operations takes

SHOW INDEX FROM customers;

CREATE INDEX last_name_idx
ON customers(last_name);

CREATE INDEX last_name_first_name_idx
ON customers(last_name, first_name);

ALTER TABLE customers
DROP INDEX last_name_idx;

SUBQUERIES

A query within a query
Syntax:- query(subquery)
Result of the subquery is used in the outer query.

SELECT * FROM employees
WHERE hourly_pay > (SELECT AVG(hourly_pay) FROM employees);

SELECT first_name, last_name
FROM customers
WHERE customer_id IN
(SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL);

GROUP BY clause

Allows to group rows based upon one or more column
Most often used with aggregate function such as max, avg, etc.
Use HAVING instead of WHERE keyword when using GROUP BY
Using WHERE results an error with GROUP BY

SELECT COUNT(amount), ordered_date
FROM orders
GROUP BY ordered_date;

SELECT SUM(amount), customer_id
FROM orders
GROUP BY customer_id;

SELECT MAX(amount), customer_id,
FROM orders
GROUP BY customer_id;

SELECT COUNT(amount) AS total_orders, customer_id
FORM orders
GROUP BY customer_id
HAVING total_orders > 1;

ROLLUP clause

Extension of GROUP BY clause
Super aggregate value or grand total in new row

SELECT SUM(amount), ordered_date
FROM orders
GROUP BY ordered_date WITH ROLLUP;

SELECT COUNT(order_id), ordered_date
FROM orders
GROUP BY ordered_date WITH ROLLUP;

SELECT SUM(hourly_pay) AS "Hourly Pay", employee_id
FROM employees
GROUP_BY employee_id WITH ROLLUP;

ON DELETE

Important for tables with relations

-- ON DELETE SET NULL = SET NULL when FK is deleted
-- ON DELETE CASCADE = DELETE record when FK is deleted

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100),
    created DATE DEFAULT (CURRENT_DATE())
);

CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    body VARCHAR(1000),
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    );

CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    body VARCHAR(100),
    post_id INT,
    user_id INT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- adding FK and ON DELETE CASCADE to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE

-- adding FK and ON DELETE SET NULL to exisiting table
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL

Stored Procedure

It is same as function or methods in programming.
It is a prepared SQL code that can be used again and again.
Some advantages are reduces network traffic, increase performance, secure and admin can grant permission to use.
The only disadvantage is it increases the memory usuage of every connection.
We also change the delimiter from ; to $$ for the procedure.

DELIMITER $$
CREATE PROCEDURE get_users()
BEGIN
     SELECT * FROM users;
END $$
DELIMITER ;

To call a procedure

CALL get_users();

To drop a procedure

DROP PROCEDURE get_users();

Passing a argument

DELIMITER $$
CREATE PROCEDURE get_user_by_id(IN id INT)
BEGIN
     SELECT * FROM user
     WHERE user_id = id;
END $$
DELIMITER ;

CALL get_user_by_id(1);

Trigger

A special type stored procedure that automatically runs when an event occurs.

CREATE TRIGGER before_hourly_pay_update
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);

CREATE TRIGGER before_hourly_pay_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);