MySQL Select Queries
Interacting with Data in MySQL: SELECT Statements
- SELECT statements, also known as queries, are used to display data from tables based on specified filters and constraints.
- Manually writing SELECT statements is a common task when interacting with databases.
Basic SELECT Query
- The most basic SELECT query can be accessed from the Workbench GUI by mousing over a table and clicking the grid icon.
- This selects every column from the table.
- Whitespace and capitalization do not matter in SQL.
- The
*character is a wildcard that represents all columns. - You can specify individual columns to select by replacing the
*with a list of column names (e.g.,SELECT column1, column2 FROM table_name;). - The order of columns in the SELECT statement determines the order in which they are displayed in the output.
Modifying SELECT Queries
The keywords LIMIT, DISTINCT, AS, and ORDER BY can be used to modify basic SELECT queries.
LIMIT
LIMITis used to restrict the number of rows returned by a query.- It's useful for testing queries or examining table contents without loading the entire dataset.
- MySQL Workbench automatically limits results to 1000 rows unless overwritten.
- Syntax:
SELECT * FROM table_name LIMIT number_of_rows; LIMIT offset, number_of_rowsallows you to start at a specific row (offset) and select a certain number of rows.- The offset is zero-based.
- Example:
LIMIT 5, 5starts on the 6th row (ID value 6) and selects 5 rows.
- To select all rows after a certain offset, use an arbitrarily large value for the second parameter.
- The
LIMITkeyword is a MySQL-specific extension to the SQL standard.
DISTINCT
DISTINCTreturns only unique values from the selected columns.- Syntax:
SELECT DISTINCT column_name FROM table_name; - If multiple columns are selected with
DISTINCT, the query returns unique combinations of values across those columns.- Example:
SELECT DISTINCT genre, studio FROM movies_basic;
- Example:
SELECT DISTINCT *(all columns) is generally not useful if a table has a unique ID column, as it will return the entire dataset.DISTINCTis best used with a limited selection of columns.
AS
ASis used to change the appearance of column headings in the output of a SELECT query.- It's purely cosmetic and doesn't affect the actual data.
- Syntax: `SELECT column_name AS