This repo contains notes and code references that I've taken while studying MySQL, mostly based on following resources:
- Caleb Curry's MySQL YouTube playlist: https://www.youtube.com/playlist?list=PL_c9BZzLwBRKn20DFbNeLAAbw4ZMTlZPH
- freecodecamp.org's MySQL tutorial (by Mike Dane): https://www.youtube.com/watch?v=HXV3zeQKqGY
primary key: unique, not null, never change. used for distinguishing entries
natural key: a primary key that exists in real world
surrogate key: a primary key that is generated by the database
foreign key: a column that references to another column in another table. often, it point to a primary key, or any unique value
constraints: aim -> protect data integrity. force relationships to hold true.
index: indexing important columns. primary keys, unique columns are automatically indexed. manually indexing foreign keys is beneficial. increasing the speed.
Relationships:
one-to-one relationships: example -> user - username. one username for one user. just like bijection.
one-to-many relationships: example -> user - posts. many posts for one user. just like a surjection (onto) from posts set to users set.
many-to-many relationships: example -> student - course. a student has many courses and a course has many students. intermediary table is needed. so, it's broken into two one-to-many relationships.
Integrity:
-Entity integrity
-Referential integrity
-Domain integrity
composite (compound) key: a key with 2+ columns. used in intermediary tables to ensure no duplicate combination.
AUTO_INCREMENT
ON DELETE:
-RESTRICT (NO ACTION): You can't delete parent.
-CASCADE: You can delete parent and it will delete all childs.
-SET NULL: You can delete parent and it won't delete the child, but it will set its foreign key null.
Also, there is ON UPDATE stuff.
Normalization:
1NF Singularity: Each column describes only one thing.
Atomicity: Each column describes the entity.
Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key.
2NF Dependency: Attribute depends on the entity. Primary key.
Partial dependency: Dependency on one of the columns in a composite key.
A relation is in 2NF if it is in 1NF and ery non-prime attribute of the relation is dependent on the whole of every candidate key.
No partial dependency.
3NF transitive dependency: depends on the primary key through another column.
no transitive dependency is allowed. no non-primary attribute should depend on another non-primary attribute.
Data Types
CHAR: Fixed length strings. Telephone numbers, zip numbers, abbreviations.
VARCHAR: Strings that are shorter than a specified length.
TEXT:
TINYTEXT TEXT MEDIUMTEXT LONGTEXT
Text data type is stored elsewhere.
ENUM: Map strings to numbers. 0 is reserved to "invalid". User-defined ones start at 1. A (lookup) table can be used instead.
SET: Many-to-many. Unlike enum, multiple selection from multiple options.
INT:
TINYINT SMALLINT MEDIUMINT INT BIGINT
UNSIGNED - SIGNED
DECIMAL: Precision and scale.
FLOAT - DOUBLE: When you don't need exact values, some precision is enough.
DATETIME - DATE - TIME
from where order by limit select
LIKE %: any number of characters _: only one character
Triggers: When something happens, do something. Need to change delimiter, which can only be done in CLI.
DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
INSERT INTO trigger_test VALUES('added new employee');
END$$
DELIMITER ;
DELIMITER $$
CREATE
TRIGGER my_trigger BEFORE INSERT
ON employee
FOR EACH ROW BEGIN
INSERT INTO trigger_test VALUES(NEW.first_name);
END$$
DELIMITER ;
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.sex = 'F' THEN
INSERT INTO trigger_test VALUES('added female');
ELSE
INSERT INTO trigger_test VALUES('added other employee');
END IF;
END$$
DELIMITER ;