MySQL

This story is a part of — My CS Notebook

MySQL is a relational database that can serve as an example of what one might find when working with databases. Why NoSQL, Document-based, and SQL styles vary, they do share a significant amount in common.

I do think it is worth mentioning that MySQL Server is written in C (source code here) and in most cases is just implemented as a delegate Linux instance or cluster.

The following is an overview of some of the most frequently used tools in MySQL

MySQL Basic Queries

Below is a “gist” of example in MySQL syntax that should how to use the following:

• SELECT— used to query data
• ORDER BY — used to organize the result set of a query
• WHERE — used to apply conditional logic to query
• DISTINCT — used to query non-duplicates
• AND, OR, IN — chaining term for conditional logic
• BETWEEN — range, or inequalities within conditional logic
• LIKE — wildcard text parsing for conditional logic (special applications listed after “gist”)
• LIMIT — limiting the number of records in the result set
• OFFSET — defining the starting index of the result set
• IS NULL — built-in “null” check of column values for conditional logic

/**** Special Applications of LIKE ****/
§  Can be used with the “%” and “_” operator
§  firstName LIKE ‘a%’ returns strings in firstName column that starts with ‘a’ (non-case-sensitive)
§  lastName Like ‘%on” return strings in lastName column that end with ‘on’ (non-case-sensitive)
§  lastName like ‘%on%” return strings in lastName column that contain ‘on’ (non-case-sensitive)
§  firstName like ‘T_m’ returns string in lastName column that start with ‘T’ and end with ‘m’
§  Can escape queries using “\” (Backslash) or define escaped character like: productCode LIKE ‘%$_20%’ ESCAPE ‘$’

MySQL Basic Joins

A “JOIN” in MySQL allows you to Query a “combined” table composed of two tables. You can hypothetically chain joins but in most cases, this will happen in another form.

There are 4 types of joins in MySQL:

1. INNER JOIN (default) — returns all matches (to the query) where both tables meet the condition
2. LEFT JOIN — returns all rows on the first table AND all matching rows (to the query) on the second table; if there are no matching rows, the values are null
3. RIGHT JOIN — returns all matching rows (to the query) on the first table AND all rows on the second table; if there are no matching rows, the values are null
4. FULL OUTER — returns all matches (to the query) from either tables

Diagram from W3

Example Implementations:

MySQL Grouping

Grouping in MySQL modifies the result set of a query. Often this can be similar to “binning” or “categorizing” a result set.

There are a couple of implementations of grouping:

• HAVING — preprocesses the GROUP BY result set with a conditions
• WITH ROLLUP — causes group by to include a “high-level” summary, often a SUM of all the values. For example: for the query listed in the gist, a hypothetical output would be.

RED: 10
BLUE: 20
GREEN: 20
NULL: 50 (total count)

MySQL Sub-queries and Common Table Expressions (CTE)

Sub-queries and CTEs allow a client to make more complex query sets. An instant might common along where you want to chain conditions, aggregate results, or otherwise achieve more difficult computations: this is where subqueries and CTEs come to the rescue.

Terms:

• Subquery — nested query
• Derived table — the table within the nested query; must have an alias
• Alias — “namespace” solution to avoid name-clashing with the query
• UNION — combines two result sets into one
• Exists — Built-in method; test for the existence of conditions in the subquery result set; returns a boolean

MySQL Data Modifiers

Data is in a constant state of evolution. Some data structures like Blockchain are immutable, however, most, allow changes to data outside of “insertion.” MySQL is one of these databases.

Commonly you will hear CRUD (Create, Retrieve, Update, Delete). MySQL does not try to make a fashion statement outside of these lines.

In addition to the basic CRUD implementation, MySQL allows Imports and Exports to move data in and out.

There are three unique examples to look out for:

• ON DELETE CASCADE — when a parent row is deleted all children row attached to the primary key of the parent row are deleted
• DELETE JOIN — similar to update join on basic queries, but delete on condition when met between two tables
• REPLACE — default behavior is to insert, however, if there is an existing entry a row within a REPLACE query with overwriting the existing duplicate

MySQL Transactions

By default MySQL “auto commits” transactions. This means that if you execute a query it is permanent. However, you can disable MySQL auto-commit behavior and do transactions on your own.

SET autocommit = 0;

• START TRANSACTION | BEGIN — to engage a transaction
• COMMIT — to finalize the transaction
• ROLLBACK — to undo the transaction

START TRANSACTION;
[query a];
[query b];
COMMIT; 
# To Undo
ROLLBACK;

Database & Table Commands

Basics

• USE — select a database;
• CREATE DATABASE— create a database;
• DROP DATABASE— drop a database;
• CREATE TABLE — creates table;
• ALTER TABLE — alters table;

# Alter Table use cases
& Add more columns
& Modfiy columns
& Rename columns
& Drop columns
& Rename table
& Add index

• RENAME TABLE — renames table;
• TRUNCATE TABLE — clears all records, resets all sequences (like AUTO_INCREMENT); similar to DELETE FROM table-name;
• TEMPORARY TABLE — creates a “dummy” duplicate; is session-specific;

Creating a Table

CREATE TABLE [IF NOT EXISTS] table_name (
   column_definition, 
   column_definition, 
   table constraints
), ENGINE=storage_engine)
Column Definition ormat: column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] column_contstraint
Column Constraints: Unique, Check, Primary Key, Foriegn Key
Generated Columns: column_name datatype GENERATE ALWAYS as (expression)
Data Types: [CHAR, VARCHAR, TINYTEXT, TEXT, BLOB, MEDIUMTEXT, MEDIUMBLOB, LONGTEXT, LONGBLOB, TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, BIT, BINARY, ENUM, SET, DATE, TIME, DATETIME, TIMESTAMP, YEAR]

Column Constraints Expanded

o   NOT NULL – Entered value to field cannot be null
o   Primary Key Constraint – Must have a unique identifier that is not a duplicate with the existing result set
o   Foreign Key Constraint – Must have a relationship with another table that has a Primary Key
o   Disabling Foreign Key Checks – Sometimes a table will have relationships but not every relationship is present and you want to loosen inserts into a column with a foreign key constraint allowing null
o   Unique Constraint – Column specific, no duplicate entries, PK Constraint implements this by default
o   Check Constraint – Insert value meets defined conditions

Table Locking

Locking can happen in a number of ways with MySQL: it can be defined by the administrator or even a cascading effect of an SQL action like creating an index. Sometimes this behavior is desired or best practice, but sometimes the developer needs control over it.

When creating an index, you can toggle the “insert/update” lock within the command.

CREATE INDEX idx-name ON table-name LOCK=NONE;

However, in more general practice, you can control this with a query.

LOCK TABLE table-name [READ|WRITE];
UNLOCK TABLE table-name [READ|WRITE];

Storage Engines

• MyISAM — optimized from compression
• InnoDB — most common
• Merge
• Memory (HEAP) — super fast, but dependant on memory
• ARCHIVE — compressed format, slow retrieval, less expensive storage
• CSV — stores data via CSV; easily interacts with other data platforms

Globalization

• Charset — “UTF-8”,”UTF-8mb4", “latin1”, “big5”, etc.
• Collation — “binary”, “utf8_general_ci”,”big5_chinese_ci”, etc.
• Both can be set at 4 levels: server, database, table, and column level

SHOW CHARACTER SET; 
SHOW COLLATION WHERE Charset = 'latin1';

Other

SHOW DATABASES; ==> List databases on server;
SHOW TABLES; ==> List tables in database;
SHOW PROCESSLIST; ==> Shows active process on server;
KILL [PID]; ==> Kills active process
DESCRIBE [table|column|database]; ==> Gives meta data about the architecture
EXPLAIN [query]; ==> Gives meta data about the architecture execution
VIEWS; ==> Save queries

This story is a part of — My CS Notebook

Enjoy the read?

Leave a clap or comment

Share with friends or on your favorite social platform