Common MySQL Queries

Extending Chapter 9 of Get it Done with MySQL 5&Up;

TreeView

Aggregates

Basic aggregation

Aggregate across columns

Aggregates across multiple joins

Aggregates excluding leaders

Aggregates of specified size

All X for which all Y are Z

All X meeting multi-row conditions

Avoid repeat aggregation

Break out sum on condition

Cascading aggregates

Combine Group_Concat() with counts

Correlated aggregating subqueries

Cross-aggregates

Delete all except the latest

Every paying customer wins a prize

Group data by datetime periods

Grouping()

How long has the machine run?

League table

List equivalences

Pairwise matchmaking

Rollup on just some columns

Show only one child row per parent row

Skip repeating values

Totals and subtotals--simply

Track state changes

Values linked with all values of another column

Windowing: introduction

Within-group aggregates

Within-group aggregates with a wrinkle

Within-group quotas (Top N per group)

Aggregates and Statistics

Average top 50% values per group

Correlation

Count unique values of one column

Exponential moving average

Median

Mode

Moving average

Multiple sums across a join

Percentiles

Rank order

Running sums with & without CTEs

Sum across categories

Aggregates and bands

Aggregates from bands of values

History of world records

Nested, banded aggregation

Pivot tables (crosstabs)

Pivot table basics: rows to columns

Automate pivot table queries

Group column statistics in rows

Monthly expenses

Monthly sales

Pivot table using math tricks

Pivot table with CONCAT

Pivot table without GROUP_CONCAT

Unpivot a table

Data comparison

Backslashes in data

Compare data in two tables

Show rows where column value changed

Track price changes

Using bit operators

Database metadata

Compare structures of two tables

Compare two databases

Database size

Find size of all server databases

Generate a query from information_schema

List databases, tables, columns

List structural database differences

List users of a database

Foreign keys

Change or drop a foreign key

Find child tables

Find cross-database foreign keys

Find parent tables

Primary keys

Add auto-incrementing primary key to a table

Auto-increment: reset next value

Find primary key of a table

Show

Show Create Trigger

Show Table Status from info_schema

Show Tables

Date and time

Age in years

Compute date from year, week, weekday

Comvert ISO 8601 timestamp

Count business days between two dates

Count Tuesdays between two dates

Data uniqueness in a period

Date of 1st day of last, this, next month

Date of Easter

Date of first Friday of next month

Date of last or next Thursday

Date of Monday in a given week of the year

Date of Monday of this week

Date of Nth weekday of a month

Datetime difference

Duration in years, months, days, time

First & last days of last month

Julian date

Last business day before a reference date

Make a calendar table

Scope to the week of a given date

Sum accumulated time by date

Sum booking days for a given month

Sum time values

Track when a value changed

US holiday dates

What month does a week fall in?

YearMonth()

Period arithmetic

Audit trails and point-in-time architecture

Find overlapping periods

Find sequenced duplicates

In/out at given date and time

Peak visit counts by datetime period

Sum for time periods

Scheduling

Appointments available

Find available reservation periods

Game schedule

Is a given booking period available?

Pivot table schedule

Frequencies

Display column values which occur N times

Display every Nth row

Graphs and Hierarchies

Dijkstra's shortest path algorithm

Edge node path

Integrity rules in tree tables

Multiple trees in one table

Tree query performance

Trees and other hierarchies

Trees of known depth

Trees, networks and parts explosions in MySQL

JOIN

Intersection and difference

JOIN order

Many-to-many joins

What else did buyers of X buy?

Joins vs subqueries

Join or subquery?

Parents with no kids

Parties who have contracts with one another

The unbearable slowness of IN()

The [Not] Exists query pattern

What exams did a student not register for?

Miscellania

Query execution order

NULLs

List NULLs at end of query output

Parents with and without children

Ordering resultsets

Next row

Order by leading digits

Order by month name

Order by numerics then alphas

Pagination

Row_Number()

Suppress repeating ordering values

Relational division

All possible recipes with given ingredients

All X for which all Y are Z (relational division)

Who makes all the parts for a given assembly?

Sequences

Find adjacent unbooked theatre seats

Find average row-to-row time interval

Find blocks of unused numbers

Find missing numbers in a sequence

Find missing values in a range

Find previous or next value in a sequence

Find row with next value of specified column

Find sequence starts and ends

Find shortest & longest per-user event time intervals

Find specific sequences

Find the next value after a sequence

Gaps in a time series

Make a table of sequential ints

Make values of a column sequential

Sequences with CTEs

Track stepwise project completion

Winning streaks

Spherical geometry

Great circle distance

Statistics without aggregates

Bayesian probability

Top ten

Stored procedures

A cursor if necessary, but not necessarily a cursor

Emulate sp_exec

Error handling pre-5.7

Variable-length argument for query IN() clause

Why minimise their use?

Strings

Count delimited substrings

Count substrings

Levenshtein distance

Proper case

Regex and case sensitivity

Retrieve octets from IP addresses

Return digits or alphas from a string

Split a string

Strip HTML tags

User admin

Password expiry dates