MySQL for everyone. SQL for Developers, Data Analysts and BI

1. Introduction to databases

Installation guide

Links:

Download Mysql and Mysql workbench here

Fedora guide

MacOS guide

Windows guide

Apt repos guide (Ubuntu, Debian)

Docker MySQL image

---

A glance at the Workbench

Links:

The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)

---

Data Normalization

Links:

Database normalization

SQL and RDBMS

---

2. DDL = Data Definition Language

DDL review, CREATE DATABASE

Pull Request

Links:

Character Sets and Collations in General

UTF8 string comparisons in MySQL stackoverflow

Create Database

---

CREATE TABLE intro

Pull Request

Links:

CREATE TABLE Statement

The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

---

Integers

Pull Request

Links:

Numeric Data Types

YouTube: Twos complement: Negative numbers in binary

---

Floating-point numbers, exact precision and BIT

Pull Request

Links:

Single-precision floating-point format

Double-precision floating-point format

Floating Point Numbers - Computerphile

Floating Point Numbers (Part1: Fp vs Fixed) - Computerphile

Floating Point Numbers (Part2: Fp Addition) - Computerphile

---

UTF8MB4 Character Set, Collation Explained

Pull Request

Below is the full list of new collations added so far in MySQL 8.0.0

The convention used here is:

• Character-set name (utf8mb4)
• Language identifier (de,is etc)
• Unicode version (0900)
• Accent and case sensitivity (ai_ci means case and accent insensitive)

Collation
utf8mb4_0900_ai_ci
utf8mb4_de_pb_0900_ai_ci
utf8mb4_is_0900_ai_ci
utf8mb4_lv_0900_ai_ci
utf8mb4_ro_0900_ai_ci
utf8mb4_sl_0900_ai_ci
utf8mb4_pl_0900_ai_ci
utf8mb4_et_0900_ai_ci
utf8mb4_es_0900_ai_ci
utf8mb4_sv_0900_ai_ci
utf8mb4_tr_0900_ai_ci
utf8mb4_cs_0900_ai_ci
utf8mb4_da_0900_ai_ci
utf8mb4_lt_0900_ai_ci
utf8mb4_sk_0900_ai_ci
utf8mb4_es_trad_0900_ai_ci
utf8mb4_la_0900_ai_ci
utf8mb4_eo_0900_ai_ci
utf8mb4_hu_0900_ai_ci
utf8mb4_hr_0900_ai_ci
utf8mb4_vi_0900_ai_ci

Basic Multilingual Plane (BMP):

Links:

Plane (Unicode)

The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)

New collations in MySQL 8.0.0

MySQL 8.0 Collations: The devil is in the details

Unicode Collation Algorithm

---

VARCHAR, TEXT, CHAR

Pull Request

Links:

String Data Types

The CHAR and VARCHAR Types

Limits on Table Column Count and Row Size

String Type Storage Requirements

Specifying Character Sets and Collations

---

BLOB, ENUM, SET

Pull Request

Links:

ENUM and SET Constraints

The BLOB and TEXT Types

String Type Storage Requirements

---

Date and Time

Pull Request

Links:

Date and Time Data Types

Unix time

---

DEFAULT values

Pull Request

---

3. Indexes and Keys

Binary Search, B-trees and Keys

Links:

Binary Search Animation by Y. Daniel Liang

B-trees visualization

How MySQL Uses Indexes

---

Primary and Unique keys

Pull Request

Links:

CREATE TABLE Statement

---

Foreign keys

Pull Request

Links:

FOREIGN KEY Constraints

---

ALTER STATEMENT

Pull Request

Links:

ALTER TABLE Statement

ALTER DATABASE Statement

---

How to construct any DDL statement?

Pull Request

---

4. Workbench and EER (Enhanced Entity-Relationship) diagrams

EER diagram with Workbench

Pull Request

---

Export DDL from EER

Pull Request

---

Import and Backup

Pull Request

---

5. DML = Data Manipulation Language

Intro to DML

Pull Request

CRUD operations:

• CREATE procedures: Performs the INSERT statement to create a new record.

• READ procedures: SELECT statement reads the table records based on the primary keynoted within the input parameter.

• UPDATE procedures: Executes an UPDATE statement on the table based on the specified primary key for a record within the WHERE clause of the statement.

• DELETE procedures: DELETE statement deletes a specified row in the WHERE clause.

Links:

Data Manipulation Statements

---

Insert

Pull Request

---

Upload employees test DB

Employees test_db GitHub

---

SELECT and WHERE review

Pull Request

Links:

SELECT Statement

---

Comparison Functions and Operators

Pull Request

Links:

Comparison Functions and Operators

---

LIKE and String Comparison

Pull Request

Links:

Comparison Functions and Operators

---

ORDER BY, LIMIT, Subqueries

Pull Request

---

UPDATE

Pull Request

Links:

UPDATE Statement

---

DELETE

Pull Request

Links:

DELETE Statement

---

6. Mighty Select

Aggregate Functions (COUNT, MAX, AVG and etc)

Pull Request

Links:

Aggregate Function Descriptions

Variance

Standard deviation

---

GROUP BY and HAVING

Pull Request

Links:

SELECT statement

---

UNION

Pull Request

Links:

UNION Clause

---

What is the beast: JOIN Clause?

Pull Request

Links:

Cartesian product

JOIN Clause

---

JOIN types explained with diagrams (not Venn!)

Pull Request

Links:

Relational algebra

---

The Power of JOIN

Pull Request

---

Data relations

Pull Request

---

Greatest N per group

Pull Request

---