MySql CheatSheet
Published: 2023-03-05 14:29:20

# 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,
… 
);
```
- Eg:

```
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);
```