MySQL - ILLYAKO/mywiki GitHub Wiki

SQL Query Optimization Strategies:

Indexing for efficient data retrieval,
Selecting necessary columns,
Efficient WHERE clause usage,
Minimizing aggregate functions,
JOIN optimization,
Considering subqueries

Use indexes effectively in relational databases like MySQL and Postgres.
Avoid SELECT * and retrieve only necessary columns.
Avoid redundant or unnecessary data retrieval.
Use LIMIT to preview query results.
Instead of HAVING use WHERE to define the filters.
Avoid unnecessary DISTINCT conditions.
Use IN predicate when querying an index column.
Optimize JOIN operations.
Use JOIN instead of SUBQUERY.
Avoid using OR in join queries.
Minimize the use of subqueries.
Utilize stored procedures “functions”.
Consider partitioning and sharding for MySQL and Postgres(write to Master, read from Slave).
Normalize database tables.The purpose is to avoid complexities, eliminate duplicates, and organize data in a consistent way:

14.1NF

a single cell must not hold more than one value (atomicity)
there must be a primary key for identification
no duplicated rows or columns
each column must have only one value for each row in the table
14.2NF
it’s already in 1NF
has no partial dependency. That is, all non-key attributes are fully dependent on a primary key.
14.3NF
be in 2NF
have no transitive partial dependency.

Use UNION ALL instead of UNION.
When using table joins that involve tables with one-to-many relationships, use EXISTS rather than DISTINCT.
Use aggregate functions like COUNT(), SUM(), AVG(), etc., judiciously.
Avoid using aggregate functions on the right side of the operator.
Optimize subquery performance.
Use wildcards only at the end of the phrase.
Leverage cloud database-specific features.
Monitor query performance.
Run large queries during off-peak hours.

Connection refers to the establishment of a communication link (TCP/IP) between a client application and a MySQL database server.
Session is the period of time between a client logging in (connecting to) a MySQL database and the client logging out (exiting) the MySQL database.

Check MySQL version in cmd:

>mysql --version
mysql  Ver 8.0.13 for Win64 on x86_64 (MySQL Community Server - GPL)

Log into MySQL (mysql -u [your MySQL username] -p)

>mysql -u **** -p
Enter password:*****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.2.0 MySQL Community Server - GPL

Exit

mysql> exit
Bye

Show the database list:

SHOW DATABASES;

Create a new database:

CREATE DATABASE myDB;

Activate a database:

USE myDB;

Delete the database:

DROP DATABASE myDB;

Change the database to a read-only mode:

ALTER DATABASE myDB READ ONLY = 1;

Disable the read-only mode:

ALTER DATABASE myDB READ ONLY = 0;

Create a new table:

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

Show all columns:

SHOW COLUMNS FROM employees FROM mydb;

Select the table:

SELECT * FROM employees;

Rename the table:

RENAME TABLE employees TO workers;

Delete the table:

DROP TABLE workers;

Add column to table:

ALTER TABLE employees
ADD phone_number VARCHAR(15);

Change column name:

ALTER TABLE employees
RENAME COLUMN phone_number TO email;

Change column description:

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100);

Change column position:

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

Put the column in the first position

ALTER TABLE employees
MODIFY email VARCHAR(100)
FIRST;

Delete the column

ALTER TABLE employees
DROP COLUMN email;

Add one row with all columns to the table

INSERT INTO employees
VALUES (1, 'Eugene', 'Krabs', 25.50, '2023-01-02');

Add many rows with all columns to the table

INSERT INTO employees
VALUES (2, 'Squidward', 'Tentacles', 15.00, '2023-01-03'),
       (3, 'Spongebob', 'Squarepants', 12.50, '2023-01-04'),
       (4, 'Pataric', 'Star', 12.5, '2023-01-05'),
       (5, 'Sandy', 'Cheeks', 17.25, '2023-01-06');

Add one row with limited columns

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

Select all data:

SELECT * FROM employees;

Select top rows:

SELECT TOP 10 * FROM employees;

Select certain data from the table:

SELECT first_name, last_name FROM employees;

Select data with conditions:

SELECT * FROM employees WHERE employee_id = 1;
SELECT * FROM employees WHERE hourly_pay >= 15;
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;

Update data in the table

UPDATE employees
SET hourly_pay = 10.50,
    hire_date = '2023-01-07'
WHERE employee_id = 6;

Update to NULL:

UPDATE employees SET hire_date = NULL WHERE employee_id = 6;

Update all info in the column:

UPDATE employees SET hourly_pay = 10.25;

Delete row:

DELETE FROM employees WHERE employee_id = 6;

Autocommit, commit, rollback

Default Autocomit is "ON".
Disable auto-commit (transaction will not be saved automatically):

SET AUTOCOMMIT = OFF;

If we don't want the last transaction, we can rollback last actions:

DELETE FROM employees;
ROLLBACK;

We should save each transaction manually with:

DELETE FROM employees;
COMMIT;

Date and time:

CREATE TABLE test(
    my_date DATE,
    my_time TIME,
    my_datetime DATETIME
);
INSERT INTO test
VALUES(CURRENT_DATE(), CURRENT_TIME(), NOW());

Transaction

A Collection of queries.
One unit of work. ROLLBACK - restore data after trying

START TRANSACTION;
    DELETE FROM customers WHERE customer_id > 3;
ROLLBACK;

COMMIT - it will be done

START TRANSACTION;
    DELETE FROM customers WHERE customer_id > 3;
COMMIT;

UNIQUE Data in the column

CREATE TABLE products(
    product_id INT,
    product_name VARCHAR(25) UNIQUE,
    price DECIMAL(4, 2)
);

Add UNIQUE constraint in the existing table

ALTER TABLE products
ADD CONSTRAINT
UNIQUE(product_name);

INSERT INTO products
VALUES (1, 'hamburger', 3.99),
       (2, 'fries', 1.89),
       (3, 'soda', 1.00),
       (4, 'ice cream', 1.75),
       (5, 'fries', 1.89);

NOT NULL constraint

CREATE TABLE products(
    product_id INT,
    product_name VARCHAR(25) UNIQUE,
    price DECIMAL(4, 2) NOT NULL
);

Modify the existing table

ALTER TABLE products
MODIFY price DECIMAL(4, 2) NOT NULL;

CHECK constraint is used to limit what values can be placed in the column

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hourly_pay DECIMAL(5, 2),
    hire_date DATE,
    CONSTRAINT chk_hourly_pay CHECK(hourly_pay >= 10.00) 
);

ALTER TABLE employees
ADD CONSTRAINT chk_hourly_pay CHECK(hourly_pay >= 10.00);

Delete constraint

ALTER TABLE employees
DROP CHECK chk_hourly_pay;

DEFAULT constraint

CREATE TABLE products(
    product_id INT,
    product_name VARCHAR(25),
    price DECIMAL(4, 2) DEFAULT 0
);

ALTER TABLE products
ALTER price SET DEFAULT 0;

INSERT INTO products (product_id, product_name)
VALUES (104, "straw"),
       (105, "napkin"),
       (106, "fork");

PRIMARY KEY

Primary keys are a single field or combination of fields that are defined to become a unique identifier for a row in a table. The primary key applies to the column which is NOT NULL and UNIQUE and used as indistinction. The table has only one PK.

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

ALTER TABLE transactions
ADD CONSTRAINT PRIMARY KEY(transaction_id);

AUTO_INCREMENT constraint, apply to the column as the key.

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

ALTER TABLE transactions
AUTO_INCREMENT = 1000;

FOREIGN KEYS

as primary key from one table that can be found within a different table. we establish a link between two tables. it is preventing from destroying the links.

DROP TABLE IF EXISTS customers;
CREATE TABLE customers(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);
INSERT INTO customers (first_name, last_name)
VALUES ("Fred", "Fish"),
       ("Larry", "Lobster"),
       ("Bubble", "Bass");
DROP TABLE IF EXISTS transactions;
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)
);
INSERT INTO transactions (amount, customer_id)
VALUES (4.99, 3),
       (2.89, 2),
       (3.38, 3),
       (4.99, 1);

Delete Foreign keys:

ALTER TABLE transactions DROP FOREIGN KEY transactions_ibfk_1;

Add a name to the foreign key

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

JOINS (inner, left, right)

INSERT INTO transactions (amount, customer_id)
VALUES (1.00, NULL);
INSERT INTO customers (first_name, last_name)
VALUES ('Poppy', 'Puff');

INNER JOIN

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

inner_join

LEFT JOIN

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

left_join

RIGHT JOIN

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

right_join

CROS JOIN

SELECT *
FROM transactions CROSS JOIN customers;

cross_join

Built-in Functions:

SELECT COUNT(amount) AS count FROM transactions;
SELECT MAX(amount) AS maximum FROM transactions;
SELECT MIN(amount) AS minimum FROM transactions;
SELECT AVG(amount) AS average FROM transactions;
SELECT SUM(amount) AS sumFROM transactions;
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;

AND, OR, NOT, BETWEEN

the logical operator checks more than one condition

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' AND NOT job = 'asst. manager';
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: %(many) _(one) used to substitute characters.

SELECT * FROM employees
WHERE first_name LIKE 's%';
SELECT * FROM employees
WHERE first_name LIKE '_ook'; -- return cook
SELECT * FROM employees
WHERE first_name LIKE '_a%'; -- second character is a

ORDER BY

clause sorts the results of a query in either ascending(ASC) or descending(DESC) order (ASC is the default).

SELECT * FROM employees
ORDER BY last_name DESC;
SELECT * FROM transactions
ORDER BY amount ASC, customer_id DESC; -- ordered by two columns

LIMIT

clause is used to limit the number of records. Can be used in pagination.

SELECT * FROM customers
LIMIT 3; -- return first 3 rows
SELECT * FROM customers
LIMIT 5, 3; -- return 3 rows with offset 5 rows (useful for pagination)

UNION

combines the results of two or more SELECT statements (should have the same amount of columns)

SELECT * FROM income
UNION                                   -- does not allow duplicates
SELECT * FRom expenses;
SELECT first_name, last_name FROM employees
UNION ALL                                   -- includes duplicates
SELECT first_name, last_name FRom customers;

SELF JOIN

Join another copy of a table to itself, used to compare rows of the same table. helps to display a hierarchy of data.

SELECT a.customer_id, 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.referral_id = b.customer_id;
SELECT a.first_name, a.last_name,
       CONCAT(b.first_name, ' ', b.last_name) AS 'reports to'
FROM employees AS a
LEFT JOIN employees AS b
ON a.supervisor_id = b.employee_id;

Temporary Table

A TEMPORARY table is visible only within the current session and is dropped automatically when the session is closed.

CREATE TEMPORARY TABLE temp_table_name
SELECT * FROM original_table;

Create VIEW

A virtual table based on the result set of an SQL statement
Updated automatically.The database engine recreates the view, every time a user queries it.
The fields in a view are fields from one or more real tables in the database
They're not real tables but can be interacted with as if they were

CREATE VIEW employee_attendance AS
SELECT first_name, last_name
FROM employees;
SELECT * FROM employee_attendance;

Drop VIEW

DROP VIEW employee_attendance;

INDEX (BTree data structure)

Indexes are used to find values within a specific column more quickly
MySQL normally searches sequentially through a column
The longer the column, the more expensive the operation is
UPDATE takes more time, SELECT takes less time

SHOW INDEXES FROM customers;
CREATE INDEX last_name_idx ON customers(last_name);

Multi Column INDEX (leftmost prefix)

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 another query. Query(Subquery)

SELECT first_name, last_name, Hourly_pauy,
    (SELECT AVG(hourly_pay) FROM employees) AS avg_pay
FROM employees;
SELECT first_name, last_name, hourly_pay
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 transactions
WHERE customer_id IS NOT NULL);

GROUP BY

aggregate all rows by a specific column, often used with aggregate functions ex. SUM(), MAX(), MIN(), AVG(), COUNT().

SELECT SUM(amount), order_date FROM transactions
GROUP BY order_date;
SELECT SUM(amount), customer_id FROM transactions
GROUP BY customer_id;
SELECT COUNT(amount), customer_id FROM transactions
GROUP BY customer_id
HAVING COUNT(amount) > 1 AND customer_is IS NOT NULL;

ROLLUP

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

SELECT SUM(amount), order_date FROM transactions
GROUP BY order_date WITH ROLLUP;
SELECT COUNT(transaction_id), order_date FROM transactions
GROUP BY order_date WITH ROLLUP;

ON DELETE

ON DELETE SET NULL = When a FK is deleted, replace FK with NULL
ON DELETE CASCADE = When a FK is deleted, delete row ON DELETE SET NULL Set up when CREATE TABLE

CREATE TABLE transactions(
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(5, 2),
    customer_id INT,
    order_date DATE,
    FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
    ON DELETE SET NULL
);

Change existing table

ALTER TABLE transactions DROP FOREIGN KEY fk_customer_id;
ALTER TABLE transactions 
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL;

ON DELETE CASCADE Change existing table

ALTER TABLE transactions DROP FOREIGN KEY fk_customer_id;
ALTER TABLE transactions 
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

Stored function

https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');

SELECT hello(name) FROM customers;

DELIMITER //

CREATE FUNCTION SimpleCompare(n INT, m INT)
  RETURNS VARCHAR(20)

  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END //

DELIMITER ;

Stored procedure

is prepared SQL code that you can save great if there's a query that you write often
A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. You cannot invoke a procedure in an expression.

reduces network traffic
increases performance
secure, admin can grant permission to use
increases memory usage of every connection

DELIMITER $$
CREATE PROCEDURE get_customer()
BEGIN
    SELECT * FROM customers;
END $$
DELIMITER ;
CALL get_customers();
DROP get_customers;

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

DELIMITER $$
CREATE PROCEDURE find_customer(IN f_name VARCHAR(50),
                               IN l_name VARCHAR(50))
BEGIN
    SELECT * FROM customers
    WHERE first_name = f_name AND last_name = l_name;
END $$
DELIMITER ;
CALL find_customer('Larry', 'Lobster');
DROP find_customer;

Trigger

when an event happens (BEFORE, AFTER), do something ex. (INSERT, UPDATE, DELETE) checks data, handles errors, and auditing tables.

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

SHOW TRIGGERS;

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

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';

CREATE TRIGGER after_salary_insert
AFTER INSERT ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + NEW.salary
WHERE expense_name = 'salaries';

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + (NEW.salary - OLD.salary)
WHERE expense_name = 'salaries';