Nice to meet you MySQL - part 3

2023-12-03

The articles are notes I took on the SQL Tutorial - Full Database Course for Beginners course.

They break into four parts:

  1. First article will introduce what is a database, SQL, keys and walk through the installation of MySQL on Mac.
  2. Second article introduces the common SQL data types, basic query syntax …etc.
  3. In third article we will create company tables, practice how to query on data, and introduce Functions, Wildcards …etc.
  4. The last article we introduce ER Diagrams, a way to diagram the requirements into figures, then we learn how to transform the ER Diagrams into actual SQL table schemas.

Now, let’s get started.

TL:DR

  • Company Database Intro
  • Create tables
  • Data insertion
  • More Basic Queries
  • Functions
  • Wildcards
  • Union
  • Join
  • Nested Queries
  • On Delete
  • Triggers
  • Appendix: how to solve slow SQL command
  • References

Company Database Intro

In this article, we are going to build a company database using the schema below:

company_table.png

Create tables

First, we create the employee table:

1// employee
2CREATE TABLE employee (
3	emp_id INT PRIMARY KEY,
4	first_name VARCHAR(40),
5	last_name VARCHAR(40),
6	birth_date DATE,
7	sex VARCHAR(1),
8	salary INT,
9	super_id INT,
10	branch_id INT
11);

For the foreign key super_id and branch_id , we will have to wait for the employee and branch table to be created, then we can mark them as foreign keys.

1// branch
2CREATE TABLE branch (
3	branch_id INT PRIMARY KEY,
4	branch_name VARCHAR(40),
5	mgr_id INT,
6	mgr_start_date DATE,
7	// mark mgr_id as foreign key
8	FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL 
9);

After employee and branch tables are created, we can mark super_id and branch_id inside employee table as foreign keys:

1// mark super_id as foreign key
2ALTER TABLE employee
3ADD FOREIGN KEY(super_id)
4REFERENCES employee(emp_id)
5ON DELETE SET NULL;
6
7// mark branch_id as foreign key
8ALTER TABLE employee
9ADD FOREIGN KEY(branch_id)
10REFERENCES branch(branch_id)
11ON DELETE SET NULL;

Next, we create client, works_with and branch_supplier tables:

1// client
2CREATE TABLE client (
3	client_id INT PRIMARY KEY,
4	client_name VARCHAR(40),
5	branch_id INT,
6	FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
7);
1// works_with
2CREATE TABLE works_with (
3	emp_id INT,
4	client_id INT,
5	total_sales INT,
6	PRIMARY KEY(emp_id, client_id), // mark 2 columns as primary key
7	FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
8	FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
9);
1// branch supplier
2CREATE TABLE branch_supplier (
3	branch_id INT,
4	supplier_name VARCHAR(40),
5	supply_type VARCHAR(40),
6	PRIMARY KEY(branch_id, supplier_name),
7	FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
8);

Data insertion

Insert supervisor

We will insert employee who is supervisor first since they are gonna be used as foreign keys in both employee and branch table:

1// David - Corporate
2
3INSERT INTO employee 
4VALUES(100, 'David', 'Wallace', '1996-11-17', 'M', 250000, NULL, NULL);
5// branch_id will be NULL for now, since branch table has no data inserted yet
6
7INSERT INTO branch 
8VALUES(1, 'Corporate', 100, '2006-02-09');
9
10// branch_id 1 is settled, update branch_id of David inside employee
11UPDATE employee
12SET branch_id = 1
13WHERE emp_id = 100;
1// Michael - Scranton
2
3INSERT INTO employee 
4VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);
5// branch_id will be NULL for now, since branch table has no branch_id 2 yet
6
7INSERT INTO branch 
8VALUES(2, 'Scranton', 102, '1992-04-06');
9
10// branch_id 2 is settled, update branch_id of Michael inside employee
11UPDATE employee
12SET branch_id = 2
13WHERE emp_id = 102;
1// Josh - Stamford
2
3INSERT INTO employee
4VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);
5// branch_id will be NULL for now, since branch table has no branch_id 3 yet
6
7INSERT INTO branch
8VALUES(3, 'Stamford', 106, '1998-02-13');
9
10// branch_id 3 is settled, update branch_id of Michael inside employee
11UPDATE employee
12SET branch_id = 3
13WHERE emp_id = 106;

After that, insert the rest part of the data, I will skip this part, if you would like to check the code, I put it on this repo.

More Basic Queries

  1. Find all employees

    1SELECT * FROM employee;

  2. ORDER BY - Find all employees order by salary

    1SELECT * FROM employee
2ORDER BY salary;

  3. Find all employees order by sex then name

    1SELECT * FROM employee
2ORDER BY sex, first_name, last_name;

  4. LIMIT - Find the first 5 employees in the table

    1SELECT * FROM employee
2LIMIT 5;

  5. Find the first and last name of all employees

    1SELECT first_name, last_name FROM employee;

  6. AS - Find the forename and surname of all employees

    1SELECT first_name AS forename, last_name AS surname
2FROM employee;

  7. DISTINCT - Find out all the different genders

    1SELECT DISTINCT sex FROM employee;

    distinct.png

Functions

  1. COUNT

    1. Find the number of employees (employee list length)

      1SELECT COUNT(emp_id) FROM employee;

    2. Find the number of female employees born after 1970

      1SELECT COUNT(emp_id) FROM employee
2WHERE sex = 'F' AND birth_date >= '1971-01-01';
  2. AVG

    1. Find the average of all employee’s salaries

      1SELECT AVG(salary) FROM employee;
  3. SUM

    1. Find the sum of all employees salaries

      1SELECT SUM(salary) FROM employee;
  4. GROUP BY

    1. Find out how many males and females there are

      1SELECT COUNT(sex), sex
2FROM employee
3GROUP BY sex;

    2. Find the total sales of each salesman

      1SELECT SUM(total_sales), emp_id FROM works_with
2GROUP BY emp_id;

    3. Find out total spend of each client

      1SELECT SUM(total_sales), client_id FROM works_with
2GROUP BY client_id;

Wildcards

Wildcards provide a way for us to grab data that matches specific pattern.

  • LIKE

  • % - any characters, including white spaces or null

  • _ - one character

  • examples

    Find any client who are an LLC

    1SELECT * FROM client
2WHERE client_name LIKE '%LLC'; 
3// xxxLLC

    Find any branch suppliers who are in the label business

    1SELECT * FROM branch_supplier
2WHERE supplier_name LIKE '%Labels%';
3// XXXLabelsXXX

    Find any employee born in Oct.

    1SELECT * FROM employee
2WHERE birth_date LIKE '____-10%'; 
3// XXXX-10XXX...

Union

  1. Intro

    • use for combining selection result
    • must have same number of columns on each select statement
    • similar data type

  2. Example: Find a list of employee and branch names

    1SELECT first_name AS naming FROM employee
2UNION
3SELECT branch_name FROM branch_supplier;

Join

JOIN...ON... is used to combine two or more tables base on a relative columns between them.

  • Inner join - include rows on both table when specified of the rows from both tables matches the ON condition

    Find all branches with a manager and their manager’s name:

    1SELECT employee.first_name, employee.last_name, branch.branch_name 
2FROM employee
3JOIN branch
4ON employee.emp_id = branch.mgr_id;

  • Left join - include all rows from the left table, while on the right table, only show rows which the specified data of the row matches ON condition.

    Find all branches with a manager and the name of their managers along with other employees:

    1SELECT employee.first_name, employee.last_name, branch.branch_name
2FROM employee
3LEFT JOIN branch
4ON employee.emp_id = branch.mgr_id;

  • Right join - include all rows from the rigth table, while on the left table, only show rows which the specified data of the row matches ON condition.

    Find all branches and the name of their managers:

    1SELECT employee.first_name, employee.last_name, branch.branch_name
2FROM employee
3RIGHT JOIN branch
4ON employee.emp_id = branch.mgr_id;

Nested Queries

  • Find names of all employees and it’s who have sold over 30000 to a single client

    1// check if the employee.emp_id is in the result of inner query 
2SELECT employee.first_name, employee.last_name FROM employee
3WHERE employee.emp_id IN (
4	SELECT works_with.emp_id FROM works_with
5	WHERE works_with.total_sales > 30000;
6);
7
8// NOTED: the below query doesn't work
9// if one person sales to multiple clients for more than 30000
10// the person's name will be shown repeatedly
11SELECT employee.first_name, employee.last_name
12FROM employee
13JOIN works_with
14ON employee.emp_id = works_with.emp_id
15WHERE works_with.total_sales > 30000;

  • Find names of all employees who have sold over 30000 to a single client and their min sales over 30000:

    1SELECT employee.first_name, employee.last_name, MIN(works_with.total_sales)
2FROM employee
3JOIN works_with
4ON employee.emp_id = works_with.emp_id
5WHERE employee.emp_id IN (
6	SELECT works_with.emp_id FROM works_with
7	WHERE works_with.total_sales > 30000
8) AND works_with.total_sales > 30000
9GROUP BY employee.emp_id
10ORDER BY employee.emp_id DESC;

  • Find all employees who are handled by the branch that Michael Scott manages, assume you know Michael’s ID

    1SELECT employee.first_name, employee.last_name FROM employee
2WHERE employee.emp_id IN (
3	SELECT branch.branch_id FROM branch
4	WHERE branch.mgr_id = 102;
5) AND employee.emp_id <> 102;

  • Find all clients who are handled by the branch that Michael Scott manages, assume you know Michael’s ID:

    1SELECT client.client_name FROM client
2WHERE client.branch_id = (
3	SELECT branch.branch_id FROM branch
4	WHERE branch.mgr_id = 102
5	LIMIT 1
6);

On Delete

This section explain what ON DELETE SET NULL and ON DELETE CASCADE are:

  • ON DELETE SET NULL

    When the foreign key reference is deleted, set the value to be null .

    For example, branch Corporate’s manager is David, if David’s row inside employee is deleted, the mgr_id of branch Corporate will be set to null:

    1CREATE TABLE branch (
2	branch_id INT PRIMARY KEY,
3	branch_name VARCHAR(40),
4	mgr_id INT,
5	mgr_start_date DATE,
6	FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
7);

  • ON DELETE CASCADE

    When the foreign key reference is deleted, delete the whole row of data.

    Usually when this foreign key is also part of the primary key, then it cannot be null , so we will always have to delete the entire row when the foreign key is being deleted in it’s own table, since the foreign key will become null , which is unacceptable for primary key.

    For example, if branch Corporate (with branch_id 1) is deleted, then the supplier no longer exists, so we should delete the whole supplier row whose supplier is branch Corporate.

    1CREATE TABLE branch_supplier (
2	branch_id INT,
3	supplier_name VARCHAR(40),
4	supply_type VARCHAR(40)
5	PRIMARY KEY(branch_id, supplier_name),
6	FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
7);

Triggers

Trigger is a block of SQL code which will define a certain action that should happen when a certain operation gets performed on the database. It can happen after / before the INSERT , UPDATE and DELETE on a table.

  • Basic example

    1// 1. create trigger table
2CREATE TABLE my_first_trigger_table (
3	message VARCHAR(100)
4);
    1// 2. note that since this action command changes the delimiters,
2// we will have to execute it inside terminal
3// login to mysql, and enter password
4$ mysql -u root -p
    1// 3. choose database
2mysql> use giraffe
    1// 4. write down the trigger action
2DELIMITER $$
3CREATE
4	TRIGGER my_first_trigger BEFORE INSERT 
5	ON employee
6	FOR EACH ROW BEGIN
7		INSERT INTO my_first_trigger_table VALUES('add new employee');
8	END$$
9DELIMITER ;
10
11// NOTED that this part of code only works inside terminal
12// since Workbench doen't support chaning delimiter

    In the above SQL code, we first change the delimiter to $$, if we didn’t change it, the code will finish its execution when it reaches the first ; inside the trigger code.

    Then we define the trigger name (my_first_trigger), timing (BEFORE) and what CRUD action is it listening to (INSERT).

    After that, we define the trigger message and the table to store the message (my_first_trigger_table).

    At last, we change the delimiter back to ; .

    We can test the trigger by inserting new employee to the employee table:

    1INSERT INTO employee
2VALUES(109, 'Oscar', 'Martinez', '1968-02-19', 'M', 69000, 106, 3);
    1// then see if the trigger is executed by checking the my_first_trigger_table
2SELECT * FROM my_first_trigger_table;

    trigger_1.png

    The abstraction syntax will be like this:

    1DELIMITER $$
2CREATE 
3	TRIGGER <name_for_this_trigger> <BEFORE | AFTER> <INSERT | UPDATE | SELECT | DELETE>
4	ON <target_table>
5	FOR EACH ROW BEGIN
6		INSERT INTO <table_to_store_this_trigger_message> VALUES(<trigger_message>);
7	END$$
8DELIMITER ;

  • Another example: use New to access the data inserted

    Let's create another trigger:

    1DELIMITER $$
2CREATE
3	TRIGGER trigger_with_new_data BEFORE INSERT
4	ON employee
5	FOR EACH ROW BEGIN
6		INSERT INTO my_first_trigger_table VALUES(CONCAT('insert ', NEW.first_name));
7	END$$
8DELIMITER ;

    Now test if we can access the new inserted data inside the trigger message:

    1INSERT INTO employee
2VALUES(109, 'Kevin', 'Malone', '1978-02-19', 'M', 69000, 106, 3);
    1SELECT * FROM my_first_trigger_table;

    trigger_2.png

  • Another example: with condition

    1DELIMITER $$
2CREATE
3	TRIGGER trigger_with_condition BEFORE INSERT
4    ON employee
5    FOR EACH ROW BEGIN
6    IF NEW.sex = 'M' THEN
7		INSERT INTO trigger_test VALUES(CONCAT('insert male employee ', NEW.first_name));
8	ELSEIF NEW.sex = 'F' THEN
9		INSERT INTO trigger_test VALUES(CONCAT('insert female employee ', NEW.first_name));
10	ELSE
11		INSERT INTO trigger_test VALUES(CONCAT('insert employee ', NEW.first_name));
12	END IF;
13END$$
14DELIMITER ;

  • Another example: trigger on update

    When update a data, we can access either NEW or OLD value.

    1DELIMITER $$
2CREATE 
3	TRIGGER trigger_when_update AFTER UPDATE
4	ON employee
5	FOR EACH ROW BEGIN
6		INSERT INTO trigger_test VALUES(CONCAT('original name: ', OLD.first_name, ', ', 'new name: ', NEW.first_name));
7	END$$
8DELIMITER ;

    Now, when we update a certain data:

    1UPDATE employee
2SET employee.first_name = 'OOOscar'
3WHERE emp_id = 109;

    We will receive new trigger message like this:

    trigger_3.png

  • Another example: trigger on delete

    When delete, we are able to access OLD data, but not on NEW data, since there’s no new data exists after deletion.

    1DELIMITER $$
2CREATE
3	TRIGGER trigger_when_delete BEFORE DELETE
4    ON employee
5    FOR EACH ROW BEGIN
6		INSERT INTO trigger_test VALUES(CONCAT('delete user ', OLD.first_name));
7	END$$
8DELIMITER ;

  • Appendix

    Delete certain trigger:

    1DROP TRIGGER trigger_with_new_data;

  • Appendix

    When combining MySQL with Nodejs, it is recommended to use a library called Sequalize. It provides ways to config the trigger command without needed to change the delimiters. (Ref.)

Appendix: how to solve slow SQL command

If you are having issue running DROP or other commands, it probably causes by some slow process.

You can run this to check current processes inside your MySQL

1mysql> SHOW PROCESSLIST;

appendix.png

Kill the process you don’t need, or restart certain process which is running slow:

1mysql> KILL <process_ID>;

References

  1. SQL Tutorial - Full Database Course for Beginners
  2. MySQL: Slow Drop table command
  3. Can I launch a trigger on select statement in mysql?
  4. MySQL BEFORE DELETE Trigger