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

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.

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.

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

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;
  • 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

Column Constraints Expanded

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.

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

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

Other

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