SELECT - MySQL

Overview

The SELECT command in MySQL retrieves data from one or more relational database tables. It is the foundation of data retrieval and manipulation operations, allowing users to extract specific information based on defined criteria.

Syntax

SELECT [DISTINCT] select_list
FROM table_name
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING condition]
[ORDER BY order_by_expression]
[LIMIT row_count]
[OFFSET offset_value]

Options/Flags

  • DISTINCT: Removes duplicate rows from the result set.
  • WHERE: Specifies the conditions that rows must meet to be included in the result set.
  • GROUP BY: Groups rows by the specified expressions, combining rows with the same values.
  • HAVING: Filters the grouped rows based on a condition specified in the GROUP BY clause.
  • ORDER BY: Sorts the result set in ascending or descending order based on the specified expression.
  • LIMIT: Limits the number of rows returned by the query.
  • OFFSET: Skips the specified number of rows before starting to return rows.

Examples

Select all columns from a table

SELECT * FROM table_name;

Select specific columns

SELECT column1, column2 FROM table_name;

Filter rows using WHERE

SELECT * FROM table_name WHERE column1 > 10;

Group rows and calculate aggregate values

SELECT department, SUM(salary) AS total_salary
FROM employee
GROUP BY department;

Sort results

SELECT * FROM table_name ORDER BY column1 DESC;

Common Issues

  • Syntax errors: Ensure the query follows the correct syntax rules and avoid missing or misplaced characters.
  • Invalid table or column names: Confirm the existence of specified tables and columns.
  • Incorrect data types: Verify that data types in WHERE conditions match the actual data types in the table.
  • Ambiguous column names: Specify the table name or use column aliases to resolve ambiguity.

Integration

  • Combine with JOIN clauses for data from multiple tables.
  • Use subqueries to embed additional queries within the SELECT statement.
  • Incorporate into stored procedures and triggers for automated data retrieval.
  • INSERT: Adds new rows to a table.
  • UPDATE: Modifies existing rows in a table.
  • DELETE: Removes rows from a table.