mysql_commands

A - Mysql basics.md

What is a database?

Any collection of related information e.g: Phonebook,shopping list, todo list etc.

Always use semicolon after commands

create database

create database DATABASE_NAME;

switch/select to a database

USE DATABASE_NAME;

show current database

SELECT database()

create table

CREATE TABLE TABLE_NAME ( student_id INT ,
name VARCHAR(20), major VARCHAR(20), PRIMARY KEY(student_id) );

see structure of the table

DESCRIBE TABLE_NAME;

Drop table

DROP TABLE student;

Add column to a existing table

ALTER TABLE student ADD gpa DECIMAL(3,2);

Delete column

ALTER TABLE student DROP COLUMN gpa;

Day 1.md

show databases;

use myDb;

CREATE TABLE employess ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), hourly_pay DECIMAL(5, 2), hire_date DATE );

SELECT * FROM employees;

DROP TABLE employees;

ALTER TABLE employees ADD phone_number VARCHAR(15);
ALTER TABLE employees RENAME COLUMN phone_number TO email;

ALTER TABLE employees MODIFY COLUMN email VARCHAR(100);
ALTER TABLE employees MODIFY email varchar(100) AFTER last_name;
ALTER TABLE employees MODIFY email varchar(100) FIRST;

ALTER TABLE employees DROP COLUMN email;

INSERT

multiple rows (each separated by , )

INSERT INTO employees VALUES (2,"Squidward", "Tentacles", 15, "2023-01-03"),
(3,"Sponogebob", "Squarepants", 12.50, "2023-01-04"),
(4,"Patrick", "Star", 12.50, "2023-01-05"),
(5,"Sandy", "Cheeks", 17.25, "2023-01-06");

a single column (but with incomplete data)

INSERT INTO employees (employee_id, first_name,last_name) VALUES (6,"Sheldon", "Plankton")

Note: for Postgres, values quotes must be SINGLE
e.g: INSERT INTO login_providers ("name") VALUES ('facebook.com'); ----> Right
but INSERT INTO login_providers ("name") VALUES ("facebook.com"); ----> Wrong

SELECT

SELECT * FROM employees;
SELECT last_name,first_name FROM employees;

SELECT * FROM employees WHERE first_name = 'Sponogebob';
SELECT * FROM employees WHERE hire_date <= "2023-01-03";
SELECT * FROM employees WHERE employee_id != 1;

SELECT * FROM employees WHERE hire_date IS NULL;
SELECT * FROM employees WHERE hire_date IS NOT NULL;

Day 2.md

CURRENT_DATE() & CURRENT_TIME() , NOW()

CREATE TABLE test( my_date DATE, my_time TIME, my_datetime DATETIME )

SELECT * FROM test;

INSERT INTO test VALUES(CURRENT_DATE(),CURRENT_TIME(), NOW());
INSERT INTO test VALUES(CURRENT_DATE()+1,NULL, NULL); // +1 will add tomorrow

UNIQUE

CREATE TABLE products( product_id INT,
product_name VARCHAR(25) UNIQUE, <--- product_name column will be unique
price DECIMAL(4,2) )

(OR IF ALREADY TABLE CREATED)

ALTER TABLE products ADD CONSTRAINT UNIQUE(product_name);

NOT NULL

CREATE TABLE products( product_id INT,
product_name VARCHAR(25) NOT NULL, <--- product_name column will be NOT NULL
price DECIMAL(4,2) )

(OR IF ALREADY TABLE CREATED)

ALTER TABLE products MODIFY product_name VARCHAR(25) NOT NULL

DEFAULT value

CREATE TABLE products( product_id INT,
product_name VARCHAR(25),
price DECIMAL(4,2) DEFAULT 0.00 <--- price column will have a default value of 0.00 if not provided
)

(OR IF ALREADY TABLE CREATED)

ALTER TABLE products ALTER price SET DEFAULT 0.00;

Day 3.md

PRIMARY KEY

CREATE TABLE transactions( transaction_id INT PRIMARY KEY,
amount DECIMAL(5,2)
);

(OR IF ALREADY TABLE CREATED)

ALTER TABLE transactions
ADD CONSTRAINT PRIMARY KEY(transaction_id);

AUTO_INCREMENT

CREATE TABLE transactions( transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5,2)
);

(OR IF ALREADY TABLE CREATED, SET AUTO_INCREMENT starting from)

ALTER TABLE transactions AUTO_INCREMENT = 1000;

Foreign Key

CREATE TABLE transactions( transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5,2),
customer_id INT,
FOREIGN KEY(customer_id) REFERENCES customers(customer_id) <--- Foreign key constraint of customer_id of "customers" table
);

Joins

(Taking transactions-customers relation, 1 customer can have multiple transaction) Reference: https://www.youtube.com/watch?v=G3lJAxg1cy8

INNER JOIN

SELECT * FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;

left join

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

right join

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

Day 4.md

Functions

COUNT(column)

SELECT COUNT(amount) as Count FROM transactions;

Others - MAX, MIN, SUM, AVG, DISTINCT

String fuunctions

SELECT CONCAT(first_name," ",last_name) AS full_name FROM customers;

Logical operator (AND, OR, NOT , BETWEEN , IN)

SELECT * FROM employees WHERE hire_date < "2023-01-05" AND job = 'cook';
SELECT * FROM employees WHERE job = 'cook' OR job = 'cashier';
SELECT * FROM employees WHERE NOT job = 'manager';

SELECT * FROM employees WHERE NOT job = 'manager' AND NOT job = "janitor"; <-- combined

SELECT * FROM employees WHERE hire_date BETWEEN "2023-01-04" AND "2023-01-07";

SELECT * FROM employees WHERE JOB IN ("cook", "cashier","janitor");

Wildcard characters (using LIKE)

select * from employees WHERE first_name LIKE "s%" <-- STARING WITH s
select * from employees WHERE hire_date LIKE "2023%" <-- STARING WITH 2023
select * from employees WHERE last_name LIKE "%n" <-- ends WITH n
select * from employees WHERE job LIKE "_ook" <-- _ means 1 random character, matches "cook", "look"
select * from employees WHERE hire_date LIKE "____-__-02" <---- matches "2023-12-02"
select * from employees WHERE job LIKE "_a%" ; <--- matches "manager", "janitor"

order by

SELECT * FROM employees ORDER BY last_name; <-- by default ASC
SELECT * FROM employees ORDER BY last_name DESC; <-- descending

LIMIT

SELECT * FROM customers LIMIT 2;
SELECT * FROM customers ORDER BY last_name DESC LIMIT 2; <-- can be used with ORDER BY
SELECT * FROM customers LIMIT 2, 1; <--- with offset, 1st one is offset, 2nd one is limit

Day 5.md

Union , Union All

A union combines the results of two or more select statements. In order to do so both need to same number of columns.

SELECT first_name FROM employees 
UNION
SELECT first_name FROM customers;

Union prevents duplicates, Union All allows duplicates.

Self join

SELECT  a.first_name, a.last_name, 
CONCAT(b.first_name, " ",b.last_name) as "Supervisor name"
FROM employees AS a INNER JOIN employees AS b on a.supervisor_id = b.employee_id;

Views

• a virtual table based on the result-set of an SQL statement.
• The fields in a view are fields from one or more real tables in a database
• They're not real tables, but can be interacted with as if they were.
• Views are automatically updated as data is added, edited ,deleted in their real table.

create view

CREATE VIEW employee_attendance as 
SELECT first_name, last_name FROM employees;

select from that view (like a real table)

SELECT * FROM employee_attendance
ORDER By last_name ASC;

delete that view

DROP VIEW employee_attendance;

Day 6.md

Indexes

Show all indexes of that table

SHOW INDEXES FROM customers;

Create an index on a column and give a name

CREATE INDEX first_name_idx ON customers(first_name);

Delete the index

ALTER TABLE customers DROP INDEX first_name_idx;

Subquery

• a query within another query
• query(subquery)

Below query gives us the average hourly pay from all employees

SELECT AVG(hourly_pay) FROM EMPLOYEES;

We can now find the first name, last name, hourly pay and average hourly pay

SELECT first_name, last_name,hourly_pay, 
   (SELECT AVG(hourly_pay) FROM EMPLOYEES)  as avg_pay
FROM employees;

We can also filter out the first name, last name of employees whose hourly_pay is greater than average hourly pay

SELECT first_name, last_name,hourly_pay
FROM employees WHERE hourly_pay > (SELECT AVG(hourly_pay) FROM EMPLOYEES) ;

Group By

• Aggregate all rows by a specific column often used with aggregate functions
• eg: SUM(), MAX(), MIN(), COUNT()

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

GROUP BY with HAVING

SELECT SUM(amount) , customer_id FROM transactions GROUP BY customer_id 
HAVING SUM(amount) > 4;

ROLLUP

• extension of the GROUP BY clause
• produces another row and shows the GRAND TOTAL(super-aggregate value)

SELECT SUM(amount) , customer_id FROM transactions GROUP BY customer_id
WITH ROLLUP;

Day 7.md

ON DELETE

If we want to modify a ON DELETE from Restrict(by default) to SET NULL / CASCADE ,first we have to drop the existing relation

ALTER TABLE transactions DROP FOREIGN KEY transactions_ibfk_1

Then add the updated constraint

ALTER TABLE transactions ADD CONSTRAINT fk_customer_id FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL; <--------set null or cascade

STORED PROCEDURE

• Stored procedure is prepared SQL code that you can save.
• It's great if there's a query that you write often.

Creating a procedure

DELIMITER $$
CREATE PROCEDURE get_left_join_customers()
BEGIN
	SELECT first_name, last_name FROM customers
	LEFT JOIN transactions 
	ON customers.customer_id = transactions.customer_id;
END$$
DELIMITER ;

Invoking a procedure

CALL get_left_join_customers();

Delete a procedure

DROP PROCEDURE get_left_join_customers();

Stored procedures with parameters

Create

DELIMITER $$
CREATE PROCEDURE find_customer(IN id INT)
BEGIN
  SELECT * FROM customers WHERE customer_id = id;
END;
DELIMITER ;

Invoke

CALL find_customer(1);

Day 8.md

Triggers

• When an event happens, do something (INSERT, UPDATE, DELETE)
• benefits = checks data, handles errors, auditing tables

Below command will update the salary column automatically when an UPDATE occurs on the hourly_pay column

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

Show all triggers

SHOW TRIGGERS;

Below command will update the salary column automatically when an INSERT occurs on the hourly_pay column

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

(Complex trigger) Suppose there is a separate expenses table , which need to be updated when an employee is deleted

CREATE TRIGGER after_salary_delete 
AFTER DELETE ON employees 
FOR EACH ROW 
UPDATE expenses 
SET expense_total = expense_total - OLD.salary
WHERE expense_name = 'salaries"