D427 - 2. MySQL Terms

MySQL

An open-source relational database management system developed by Oracle; widely used for web applications and is the DBMS used in WGU D427.

SQL

Structured Query Language; the standard language used to communicate with relational databases for querying and manipulating data.

Database Instance

A single running copy of the MySQL server process managing one or more databases.

MySQL Workbench

A graphical user interface (GUI) tool for MySQL that allows users to design schemas, write queries, and administer databases visually.

SELECT Statement

The primary DML statement used to retrieve data from one or more tables in a MySQL database.

FROM Clause

Specifies the table(s) from which data is retrieved in a SELECT statement.

WHERE Clause

Filters rows returned by a SELECT, UPDATE, or DELETE statement based on a specified condition.

ORDER BY Clause

Sorts the result set of a SELECT statement by one or more columns in ascending (ASC) or descending (DESC) order.

GROUP BY Clause

Groups rows that have the same values in specified columns into summary rows, typically used with aggregate functions.

HAVING Clause

Filters the results of a GROUP BY clause based on a condition applied to aggregated values; similar to WHERE but for grouped data.

LIMIT Clause

Restricts the number of rows returned by a SELECT statement in MySQL.

DISTINCT Keyword

Eliminates duplicate rows from the result set of a SELECT statement.

AS Keyword (Alias)

Renames a column or table with a temporary alias in the result set for readability.

LIKE Operator

Used in a WHERE clause to search for a pattern in a column using wildcard characters (% and _).

Wildcard %

Used with LIKE to match zero or more characters in a string pattern.

Wildcard _

Used with LIKE to match exactly one character in a string pattern.

IN Operator

Used in a WHERE clause to specify multiple possible values for a column; equivalent to multiple OR conditions.

NOT IN Operator

Excludes rows where the column value matches any value in a specified list.

BETWEEN Operator

Filters values within a specified inclusive range in a WHERE clause.

IS NULL

Tests whether a column value is NULL in a WHERE clause.

IS NOT NULL

Tests whether a column value is not NULL in a WHERE clause.

INNER JOIN

Returns only the rows where there is a matching value in both tables based on the join condition.

LEFT JOIN

Returns all rows from the left table and the matched rows from the right table; unmatched right-table columns return NULL.

RIGHT JOIN

Returns all rows from the right table and the matched rows from the left table; unmatched left-table columns return NULL.

FULL OUTER JOIN

Returns all rows from both tables; unmatched rows from either side return NULL for the other side's columns. MySQL simulates this with UNION of LEFT and RIGHT JOIN.

CROSS JOIN

Returns the Cartesian product of two tables; every row in the left table is combined with every row in the right table.

Self-Join

A join where a table is joined to itself; used to query hierarchical or comparative data within the same table.

Equijoin

A join that uses the equality operator (=) to match rows between two tables based on a common column.

ON Clause

Specifies the join condition between columns of two tables in a JOIN statement.

USING Clause

Simplifies a JOIN condition when both tables share a column with the same name; e.g., JOIN table2 USING (column_name).

Natural Join

Automatically joins tables based on columns with the same name and compatible data types; generally avoided in practice due to ambiguity.

COUNT()

An aggregate function that returns the number of rows (or non-NULL values) in a result set.

SUM()

An aggregate function that returns the total sum of a numeric column.

AVG()

An aggregate function that returns the arithmetic mean of a numeric column.

MIN()

An aggregate function that returns the smallest value in a column.

MAX()

An aggregate function that returns the largest value in a column.

ROUND()

A MySQL scalar function that rounds a numeric value to a specified number of decimal places.

CONCAT()

A MySQL string function that joins two or more strings together.

LENGTH()

A MySQL string function that returns the number of characters in a string.

UPPER()

A MySQL string function that converts all characters in a string to uppercase.

LOWER()

A MySQL string function that converts all characters in a string to lowercase.

TRIM()

A MySQL string function that removes leading and trailing spaces (or specified characters) from a string.

SUBSTRING()

A MySQL string function that extracts a portion of a string starting at a given position for a given length.

NOW()

A MySQL date/time function that returns the current date and time.

CURDATE()

A MySQL date function that returns the current date without time.

YEAR()

A MySQL date function that extracts the year from a date or datetime value.

MONTH()

A MySQL date function that extracts the month number from a date or datetime value.

DAY()

A MySQL date function that extracts the day of the month from a date or datetime value.

IFNULL()

A MySQL control flow function that returns a substitute value if the first expression is NULL.

COALESCE()

Returns the first non-NULL value in a list of expressions; more flexible than IFNULL() with multiple arguments.

CASE Expression

A conditional expression in MySQL that evaluates conditions and returns a value when the first matching condition is found, similar to if-else logic.

UNION

Combines the result sets of two or more SELECT statements, removing duplicate rows by default.

UNION ALL

Combines the result sets of two or more SELECT statements, including all duplicate rows.

EXISTS

A subquery operator that returns TRUE if the subquery produces at least one row.

NOT EXISTS

Returns TRUE if the subquery produces no rows.

ANY / SOME

Compares a value to each value returned by a subquery; returns TRUE if any comparison is true.

ALL

Compares a value to every value returned by a subquery; returns TRUE only if all comparisons are true.

VARCHAR

A variable-length character string data type; stores up to the specified maximum number of characters.

CHAR

A fixed-length character string data type; always stores exactly the specified number of characters, padding with spaces if necessary.

INT

A standard integer data type in MySQL, storing whole numbers from -2,147,483,648 to 2,147,483,647.

TINYINT

An integer data type storing very small whole numbers; often used for Boolean-like flags (0 or 1).

SMALLINT

An integer data type storing small whole numbers (-32,768 to 32,767).

BIGINT

An integer data type for very large whole numbers.

DECIMAL

An exact numeric data type for storing numbers with a fixed number of decimal digits; ideal for monetary values (e.g., DECIMAL(10,2)).

FLOAT

An approximate floating-point numeric data type.

DOUBLE

A double-precision floating-point numeric data type.

DATE

Stores a date value in YYYY-MM-DD format.

DATETIME

Stores a date and time value in YYYY-MM-DD HH:MM:SS format.

TIMESTAMP

Stores a datetime value; automatically records the current date/time on INSERT or UPDATE when configured.

BOOLEAN

Stores TRUE or FALSE; MySQL stores this as TINYINT(1) where 0 is FALSE and 1 is TRUE.

TEXT

A large-object data type for storing long strings of text.

ENUM

A string data type where the value must be chosen from a predefined list of allowed values.

SHOW DATABASES

A MySQL command that lists all databases on the current server instance.

SHOW TABLES

A MySQL command that lists all tables in the currently selected database.

SHOW COLUMNS

A MySQL command that displays the column names and data types of a specified table; also accessible via DESCRIBE or DESC.

USE

A MySQL command that selects a specific database to work with.

DESCRIBE (DESC)

A MySQL command that displays the structure of a table, including column names, data types, and constraints.