MySQL Basic
Sungwa Yu
Published 03-25-2021 Modified 03-25-2021 1.7k Words 10 Mins

Note taken from “freeCodeCamp.org” YouTube video.

Crouse developed by Mike Dane from mikedane.com.

Basic

Data Type

  • INT
  • DECIMAL(a,b) a: total digits b: digits after ‘.’
  • VARCHAR(N)
  • BLOB
  • DATE
  • TIMESTAMP

Set Up Table

Table

Image from “freeCodeCamp.org” YouTube video

Create Table

1
2
3
4
5
CREATE TABLE student (
	student_id INT PRIMARY KEY,
  name VARCHAR(20),
  major VARCHAR(20)
);

or

1
2
3
4
5
6
CREATE TABLE student (
	student_id INT,
  name VARCHAR(20),
  major VARCHAR(20),
  PRIMARY KEY(student_id)
);

SELECT * FROM student;

Constraints

AUTO_INCREMENT

Don’t need to specify that argument and give value, it will increase the value automatically

NOT NULL

Need to be initial

UNIQUE

For that column, no two rows have the same data

DEFAULT

1
2
3
4
5
6
CREATE TABLE student (
  student_id INT AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  major VARCHAR(20) UNIQUE,
  minor VARCHAR(20) DEFAULT 'Undeclared' 
);

Describe Table

DESCRIBE student;

Delete Table

DROP TABLE student;

Modify Table

Add column:

ALTER TABLE student ADD gpa DECIMAL;

Delete column:

ALTER TABLE student DROP COLUMN gpa;

Insert

INSERT INTO student VALUES(1, 'Jack', 'Biology');

If don’t know some arguments’ value:

INSERT INTO student(student_id, name) VALUES(1, 'Jack');

Update

1
2
3
UPDATE student
SET major = 'Bio'
WHERE major = 'Biology';

OR/AND keyword, Set multiple

1
2
3
UPDATE student
SET major = 'Bio', minor = 'undeclared'
WHERE major = 'Biology' OR major = 'CS';

Other comparision options: =, <>, >, <, >=, <=

Update all row

1
2
UPDATE student
SET major = 'Undeclared'

Delete

Delete all rows

1
DELETE FROM stduent;

Delete specified rows

1
2
DELETE FROM stduent
WHERE student_id = 5;

Basic Queries

SELECT

Select all

1
2
SELECT *
FROM student;

Select specified columns

1
2
SELECT name, major
FROM student;

or

1
2
SELECT stduent.name, student.major
FROM student;

Select by order (column)

Ascending by default (ASC)

1
2
3
SELECT stduent.name, student.major
FROM student
ORDER BY name;

Descending order (DESC):

1
2
3
SELECT stduent.name, student.major
FROM student
ORDER BY name DESC;

More order

If name the same, further order by id

ORDER BY name, id

LIMIT

Only get two rows

1
2
3
SELECT *
FROM student
LIMIT 2;

IN

1
2
3
SELECT *
FROM student
WHERE name IN ('Claire', 'Kate', 'Mike');

Advance

Table

Image from “freeCodeCamp.org” YouTube video

Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  first_name VARCHAR(40),
  last_name VARCHAR(40),
  birth_day DATE,
  sex VARCHAR(1),
  salary INT,
  super_id INT,
  branch_id INT
);

CREATE TABLE branch (
  branch_id INT PRIMARY KEY,
  branch_name VARCHAR(40),
  mgr_id INT,
  mgr_start_date DATE,
  FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

CREATE TABLE client (
  client_id INT PRIMARY KEY,
  client_name VARCHAR(40),
  branch_id INT,
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

CREATE TABLE works_with (
  emp_id INT,
  client_id INT,
  total_sales INT,
  PRIMARY KEY(emp_id, client_id),
  FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
  FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

CREATE TABLE branch_supplier (
  branch_id INT,
  supplier_name VARCHAR(40),
  supply_type VARCHAR(40),
  PRIMARY KEY(branch_id, supplier_name),
  FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);


-- -----------------------------------------------------------------------------

-- Corporate
INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);

-- Scranton
INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee
SET branch_id = 2
WHERE emp_id = 102;

INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2);
INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2);
INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);

-- Stamford
INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee
SET branch_id = 3
WHERE emp_id = 106;

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3);
INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);


-- BRANCH SUPPLIER
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper');
INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms');
INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils');
INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper');
INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');

-- CLIENT
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2);
INSERT INTO client VALUES(401, 'Lackawana Country', 2);
INSERT INTO client VALUES(402, 'FedEx', 3);
INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3);
INSERT INTO client VALUES(404, 'Scranton Whitepages', 2);
INSERT INTO client VALUES(405, 'Times Newspaper', 3);
INSERT INTO client VALUES(406, 'FedEx', 2);

-- WORKS_WITH
INSERT INTO works_with VALUES(105, 400, 55000);
INSERT INTO works_with VALUES(102, 401, 267000);
INSERT INTO works_with VALUES(108, 402, 22500);
INSERT INTO works_with VALUES(107, 403, 5000);
INSERT INTO works_with VALUES(108, 403, 12000);
INSERT INTO works_with VALUES(105, 404, 33000);
INSERT INTO works_with VALUES(107, 405, 26000);
INSERT INTO works_with VALUES(102, 406, 15000);
INSERT INTO works_with VALUES(105, 406, 130000);

Queries

Select all employees

1
SELECT * FROM employee;

Select all exployees ordered by salary

1
2
3
SELECT *
FROM employee
ORDER BY salary;

Select all exployees ordered by sex then name

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

Select first 5 exployees

1
2
3
SELECT *
FROM employee
LIMIT 5;

Select first and last name of all exployees

1
2
SELECT first_name, last_name
FROM employee;

AS keyword

Name a column differently

Select the forename and surnames names of all employees

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

DISTINCT keyword

Select all the different genders

1
2
SELECT DISTINCT sex
FROM employee;

Function

COUNT function

Find the number of employees

1
2
SELECT COUNT(emp_id)
FROM employee;

Find the number of female employees born after 1970

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

AVG function

Average of a column

Find the averge of all employee’s salaries

1
2
SELECT AVG(salary)
FROM employee;

SUM function

Find the sum of all employee’s salaries

1
2
SELECT SUM(salary)
FROM employee;

GROUP BY keyword

Find out how many males and females there are

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

Find the total sales of each salesman

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

Wildcards

% is any number characters

_ is one character

LIKE keyword

Find any client’s who are an LLC

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

Find any branch suppliers who are in the label business

1
2
3
SELECT *
FROM branch_supplier
WHERE supplier_name LIKE '% Label%';

Find any employee born in October

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

Find any clients who are schools

1
2
3
SELECT *
FROM client 
WHERE client_name LIKE '%school%';

UNION

Find a list of employee and branch names

1
2
3
4
5
6
7
8
SELECT first_name
FROM employee
UNION
SELECT branch_name
FROM branch
UNION
SELECT client_name
FROM client;

Other uses:

1
2
3
4
5
SELECT first_name AS Compaany_Names
FROM employee
UNION
SELECT branch_name
FROM branch;
1
2
3
4
5
SELECT client_name, branch_id
FROM client
UNION
SELECT supplier_name, branch_id
FROM branch_supplier;

or

1
2
3
4
5
SELECT client_name, client.branch_id
FROM client
UNION
SELECT supplier_name, branch_supplier.branch_id
FROM branch_supplier;

Find a list of all money spent or earned by the company

1
2
3
4
5
SELECT salary
FROM employee
UNION
SELECT total_sales
FROM works_with;

JOIN

Combine rows from two or more tables based on related column

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

LEFT JOIN:

Includes all the rows in employee table (result Null for not match)

RIGHT JOIN:

includes all the rows in branch table (result Null for not match)

FULL JOIN:

LEFT JOIN + RIGHT JOIN

Not in MySQL

Nested Queries

Use more SELECT

Find the names of all employees who have sold over 30,000 to a single client

1
2
3
4
5
6
7
SELECT employee.first_name, employee.last_name
FROM employee
WHERE employee.emp_id IN (
	SELECT work_with.emp_id
  FROM works_with
  WHERE works_with.total_sales > 30000
);

Find all clients who are handled by the branch that Michael Scott manages. Assume given Michael’s ID

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

If use =, better limit the result to 1.

Delete entries when they have foreign key

ON DELETE

Foreign keys set to null

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

DELETE FROM emplyee
WHERE emp_id = 102;

SELECT * FROM branch;

ON DELETE CASCADE

Delete the entire row

Can be used when the deleting key is primary key since primary key cannot be null

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

DELETE FROM branch
WHERE branch_id = 2;

SELECT * FROM branch_supplier;

Trigger

In command prompt, type

1
2
3
4
5
6
7
8
9
10
11
12
use student;

DELIMITER $$

CREATE
	TRIGGER my_trigger BEFORE INSERT 
	ON employee
	FOR EACH ROW BEGIN 
		INSERT INTO trigger_test VALUES('added new employee');
	END$$
	
DELIMITER ;
1
2
3
4
INSERT INTO employee
VALUES(109, 'Oscar', 'Martinez', '1968-02-19', 'M', 69000, 106, 3);

SELECT * FROM triiger_test;

NEW

Refer to the new row inserting

1
2
3
4
5
6
7
8
9
10
DELIMITER $$

CREATE
	TRIGGER my_trigger BEFORE INSERT 
	ON employee
	FOR EACH ROW BEGIN 
		INSERT INTO trigger_test VALUES(NEW.first_name);
	END$$
	
DELIMITER ;

IF statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DELIMITER $$

CREATE
	TRIGGER my_trigger BEFORE INSERT 
	ON employee
	FOR EACH ROW BEGIN 
		IF NEW.sex = 'M' THEN
			INSERT INTO trigger_test VALUES('added male employee');
		ELSEIF NEW.set = 'F' THEN
			INSERT INTO trigger_test VALUES('added female employee');
		ELSE
			INSERT INTO trigger_test VALUES('added other employee');
		END IF;
	END$$
	
DELIMITER ;

Trigger for update, delete…

TRIGGER my_trigger BEFORE UPDATE …

TRIGGER my_trigger BEFORE DELETE …

AFTER

TRIGGER my_trigger AFTER UPDATE …

DROP TRIGGER

DROP TIGGER my_trigger;

ER Diagram

ER Diagram

by Mike Dane from mikedane.com

Schema

Schema

by Mike Dane from mikedane.com