MySQL tutorial 2023 

![mysql.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/654025/c989e351-ad94-5b78-3944-ce14639e4978.png)

# 参考動画
https://youtu.be/5OdVJbNCSso

# ソース
```sql:mysql
DROP DATABASE IF EXISTS mydb;

CREATE DATABASE mydb;

USE mydb;

-- Create Table
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)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE test (
    my_date DATE,
    my_time TIME,
    my_detetime DATETIME
);

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

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),
    transaction_date DATETIME DEFAULT NOW()
);

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


-- カラム一覧
SHOW COLUMNS FROM employees;
SHOW COLUMNS FROM test;
SHOW COLUMNS FROM products;
SHOW COLUMNS FROM transactions;
SHOW COLUMNS FROM customers;

-- データの追加
INSERT INTO employees 
VALUES 
    (1, "Eugene", "Krabs", 25.50, "2023-01-02"),
    (2, "Squidward", "Tentacles", 15.00, "2023-01-03"),
    (3, "Spongebob", "Squarepants", 12.50, "2023-01-04"),
    (4, "Patrick", "Star", 12.50, "2023-01-05"),
    (5, "Sandy", "Cheeks", 17.50, "2023-01-06"),
    (6, "Sheldon", "Plankton", 10.50, "2023-01-07")
;

INSERT INTO test VALUES (CURRENT_DATE(), CURRENT_TIME(), NOW());

INSERT INTO products
VALUES 
    (100, "humburger", 3.99),
    (101, "fries", 1.99),
    (102, "soda", 1.00),
    (103, "ice cream", 2.99),
    (104, "cookie", 1.99)
;

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


INSERT INTO customers (first_name, last_name)
VALUES
    ("Fred", "Fish"),
    ("Larry", "Lobster"),
    ("Bubble", "Bass"),
    ("Poppy", "Puff"),
    ("Sheldon", "Plankton")
;

INSERT INTO transactions (amount, customer_id) 
VALUES 
    (4.99, 3),
    (2.89, 2),
    (3.38, 3),
    (4.99, 1),
    (1.00, NULL),
    (2.49, 4),
    (5.48, NULL)
;


-- カラムの追加
ALTER TABLE employees ADD email VARCHAR(15);
ALTER TABLE employees MODIFY email VARCHAR(100) AFTER last_name;

ALTER TABLE employees ADD COLUMN job VARCHAR(25) AFTER hourly_pay;

ALTER TABLE customers ADD referral_id INT;
ALTER TABLE employees ADD supervisor_id INT;

ALTER TABLE customers ADD COLUMN email VARCHAR(50);

ALTER TABLE transactions ADD COLUMN order_date DATE; 

-- Update
UPDATE employees SET hourly_pay = 10.25 WHERE employee_id = 5;

UPDATE employees SET job = "manager" WHERE employee_id = 1;
UPDATE employees SET job = "cashier" WHERE employee_id = 2;
UPDATE employees SET job = "cook" WHERE employee_id = 3;
UPDATE employees SET job = "cook" WHERE employee_id = 4;
UPDATE employees SET job = "asst. manager" WHERE employee_id = 5;
UPDATE employees SET job = "janitor" WHERE employee_id = 6;

UPDATE customers SET referral_id = 1 WHERE customer_id = 2;
UPDATE customers SET referral_id = 2 WHERE customer_id = 3;
UPDATE customers SET referral_id = 2 WHERE customer_id = 4;

UPDATE employees SET supervisor_id = 5 WHERE employee_id = 2;
UPDATE employees SET supervisor_id = 5 WHERE employee_id = 3;
UPDATE employees SET supervisor_id = 5 WHERE employee_id = 4;
UPDATE employees SET supervisor_id = 5 WHERE employee_id = 6;
UPDATE employees SET supervisor_id = 1 WHERE employee_id = 5;

UPDATE customers SET email = "FFish@gmail.com" WHERE customer_id = 1;
UPDATE customers SET email = "LLobster@gmail.com" WHERE customer_id = 2;
UPDATE customers SET email = "BBass@gmail.com" WHERE customer_id = 3;
UPDATE customers SET email = "PPuff@gmail.com" WHERE customer_id = 4;

UPDATE transactions SET order_date = "2023-01-01" WHERE transaction_id = 1000;
UPDATE transactions SET order_date = "2023-01-01" WHERE transaction_id = 1001;
UPDATE transactions SET order_date = "2023-01-02" WHERE transaction_id = 1002;
UPDATE transactions SET order_date = "2023-01-02" WHERE transaction_id = 1003;
UPDATE transactions SET order_date = "2023-01-03" WHERE transaction_id = 1004;
UPDATE transactions SET order_date = "2023-01-03" WHERE transaction_id = 1005;
UPDATE transactions SET order_date = "2023-01-03" WHERE transaction_id = 1006;

-- Delete
-- DELETE FROM employees WHERE employee_id = 5;

-- テーブルのデータ
SELECT * FROM employees;
SELECT * FROM test;
SELECT * FROM products;
SELECT * FROM customers;
SELECT * FROM transactions;

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

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

SELECT transaction_id, amount, first_name, last_name 
FROM transactions INNER JOIN customers 
ON transactions.customer_id = customers.customer_id;

-- function
SELECT COUNT(amount) AS "today's transactions" 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 sum FROM transactions;

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

-- oparater
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-05";
SELECT * FROM employees WHERE job IN ("cook", "cashier", "janitor");

-- wild card characters % _
SELECT * FROM employees WHERE first_name LIKE "s%";
SELECT * FROM employees WHERE last_name LIKE "%r";
SELECT * FROM employees WHERE first_name LIKE "sp%";
SELECT * FROM employees WHERE job LIKE "_ook";
SELECT * FROM employees WHERE hire_date LIKE "____-01-__";
SELECT * FROM employees WHERE hire_date LIKE "____-__-_2";
SELECT * FROM employees WHERE job LIKE "_a%";

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

-- Limit
SELECT * FROM customers LIMIT 2;
SELECT * FROM customers ORDER BY last_name LIMIT 2;
SELECT * FROM customers LIMIT 1, 2;

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

DELETE FROM customers WHERE customer_id = 5;

-- SELF JOIN
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 
 INNER JOIN employees AS b 
 ON a.supervisor_id = b.employee_id;


-- Views
SELECT * FROM employees;
CREATE VIEW employee_attendance AS SELECT first_name, last_name FROM employees;
SELECT * FROM employee_attendance ORDER BY last_name ASC;
DROP VIEW employee_attendance;
SELECT * FROM customers;
CREATE VIEW customer_emails AS SELECT email FROM customers;
INSERT INTO customers VALUES (5, "Pearl", "Krabs", NULL, "PKrabs@gamil.com");
SELECT * FROM customer_emails;

-- INDEX
CREATE INDEX last_name_idx ON customers(last_name);
SHOW INDEXES FROM customers;
CREATE INDEX last_name_first_name_idx ON customers(last_name, first_name);
SHOW INDEXES FROM customers;
ALTER TABLE customers DROP INDEX last_name_idx;
SHOW INDEXES FROM customers;
SELECT * FROM customers WHERE last_name = "Puff" AND first_name = "Poppy";

-- subquery
SELECT first_name, last_name, hourly_pay,
(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
SELECT * FROM transactions;
SELECT SUM(amount), order_date FROM transactions GROUP BY order_date;
SELECT MAX(amount), order_date FROM transactions GROUP BY order_date;
SELECT MIN(amount), order_date FROM transactions GROUP BY order_date;
SELECT AVG(amount), order_date FROM transactions GROUP BY order_date;
SELECT COUNT(amount), order_date FROM transactions GROUP BY order_date;
SELECT SUM(amount), customer_id FROM transactions GROUP BY order_date;

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

-- ROLLUP
SELECT * FROM transactions;
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;
SELECT COUNT(transaction_id) AS "# of orders",  customer_id FROM transactions GROUP BY customer_id WITH ROLLUP;
SELECT * FROM employees;
SELECT SUM(hourly_pay) AS "hourly pay", employee_id FROM employees GROUP BY employee_id WITH ROLLUP;

-- ON DELETE

-- Stored PROCEDURE
DELIMITER //
CREATE PROCEDURE get_customers() 
BEGIN 
    SELECT * FROM customers; 
END //
DELIMITER ;

CALL get_customers();

DROP PROCEDURE get_customers;

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

CALL find_customer(2);

DELIMITER //
CREATE PROCEDURE find_customer_name(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_name("Larry", "Lobster");

-- Trigger
ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2) AFTER hourly_pay;
SELECT * FROM employees;
UPDATE employees SET salary = hourly_pay * 2080;
SELECT * FROM employees;

CREATE TRIGGER before_hourly_pay_update BEFORE UPDATE ON employees FOR EACH ROW SET NEW.salary = (NEW.hourly_pay * 2080);
SHOW TRIGGERS;
UPDATE employees sET hourly_pay = 50 WHERE employee_id = 1;
SELECT * FROM employees;

DELETE FROM employees WHERE employee_id = 6;

CREATE TRIGGER before_hourly_pay_insert BEFORE INSERT ON employees FOR EACH ROW SET NEW.salary = (NEW.hourly_pay * 2080);
INSERT INTO employees VALUES(6, "Sheldon", "Plankton", NULL, 10, NULL, "janitor", "2023-01-05", 5);
SELECT * FROM employees;

CREATE TABLE expenses(
    expense_id INT PRIMARY KEY,
    expense_name VARCHAR(50),
    expense_total DECIMAL(10, 2)
);

INSERT INTO expenses VALUES (1, "salaries", 0), (2, "supplies", 0), (3, "taxes", 0);

UPDATE expenses SET expense_total = (SELECT SUM(salary) FROM employees) WHERE expense_name = "salaries";

SELECT * FROM expenses;

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

DELETE FROM employees WHERE employee_id = 6;

SELECT * FROM expenses;

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

INSERT INTO employees VALUES(6, "Sheldon", "Plankton", NULL, 10, NULL, "janitor", "2023-01-07", 5);

SELECT * FROM expenses;

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

UPDATE employees SET hourly_pay = 100 WHERE employee_id = 1;

SELECT * FROM expenses;

```