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
  • LIMIT is 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_rows allows you to start at a specific row (offset) and select a certain number of rows.
    • The offset is zero-based.
    • Example: LIMIT 5, 5 starts 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 LIMIT keyword is a MySQL-specific extension to the SQL standard.
DISTINCT
  • DISTINCT returns 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;
  • SELECT DISTINCT * (all columns) is generally not useful if a table has a unique ID column, as it will return the entire dataset.
  • DISTINCT is best used with a limited selection of columns.
AS
  • AS is 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