The `SELECT` statement is used to query and retrieve data from a database. It allows you to specify which columns and rows you want to fetch.
The `SELECT` statement is powerful and can be combined with various clauses like `WHERE`, `ORDER BY`, `GROUP BY`, and more to refine your queries
**Basic Syntax**:
“`sql
SELECT column1, column2 FROM table_name;
– **`column1, column2`**: Specify the columns you want to retrieve. Use `*` to select all columns.
– **`table_name`**: The name of the table from which to retrieve data.
**Examples**:
- **Select All Columns**
SELECT * FROM employees;
- **Select Specific Columns**:
SELECT name, age FROM employees;
- **Filtering Results with WHERE**:
SELECT * FROM employees WHERE age > 30;
- **Ordering Results with ORDER BY**:
SELECT * FROM employees ORDER BY name ASC;
MODULE X
HAVING vs. WHERE in MySQL
Use `WHERE` for filtering rows before aggregation and `HAVING` for filtering aggregated results after `GROUP BY`.
`WHERE` Clause’
– Used to filter records before any groupings are made.
– Applicable to individual rows in a table.
– Cannot be used with aggregate functions (like `SUM`, `COUNT`, etc.) directly.
*Example
SELECT * FROM employees WHERE age > 30;
`HAVING` Clause’
– Used to filter records after groupings are made.
– Applicable to aggregated results.
– Often used with `GROUP BY` to filter groups based on aggregate values.
Example
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
MODULE XX
This concise overview provides a quick reference to using `LIMIT` and `ALIASING` in MySQL queries for effective data retrieval and manipulation
LIMIT` + ALIASING`
Limits in MySQL
‘LIMIT` Clause’ It is used to restrict the number of rows returned by a query. The syntax is `LIMIT [offset,] row_count` or `LIMIT row_count OFFSET offset`.
‘Example’
`SELECT * FROM employees LIMIT 5;` — returns the first 5 rows.
– ‘Example with Offset’ `SELECT * FROM employees LIMIT 5, 10;` — skips the first 5 rows and returns the next 10.
Aliasing in MySQL
-Column Aliasing: Allows renaming of column headings in the result set for better readability or to avoid conflicts.
– Syntax: `SELECT column_name AS alias_name FROM table_name;`
-Example’: `SELECT employee_id AS ID, employee_name AS Name FROM employees;`
-Table Aliasing:- Renames a table within a query, especially useful in complex queries or joins.
-Syntax:- `SELECT t.column_name FROM table_name AS t;`
– Example:-: `SELECT e.employee_name, d.department_name FROM employees AS e JOIN departments AS d ON e.department_id = d.id;`