MySQL Server : Open Source Database : Select Query
- MySQL SELECT Query
- MySQL DISTINCT Clause
- MySQL ORDER BY Clause
- MySQL LIMIT Clause
- Downloads Examples
MySQL SELECT Query |
The SELECT Command
Whitespace and Semi-colons
– Whitespace is ignored in SQL statements.
– Multiple statements are separated with semi-colons.
The two statements in the sample below are equally valid.
Example:
SELECT * FROM category;
Or
SELECT last_name, first_name, email
FROM staff;
Case Sensitivity
- SQL is basically not case sensitive.
- It is common practice to write reserved words in all capital letters.
- User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.
SELECT Basics
Whenever you want to retrieve data from a MySQL database,you can issue a SELECT statement that specifies what data you want to have returned and in what manner that data should be returned.
For example,
- you can specify that only specific columns or rows be returned.
- You can also order the rows based on the values in one or more columns.
- In addition, you can group together rows based on repeated values in a column in order to summarize data.
The SELECT clause includes the SELECT keyword and an asterisk (*) or the select list, which is made up of columns or expressions, as shown in the following syntax:
Syntax:
<select> [ * | <column name> | <expression>}
[[AS] <alias>]
[{, {<column name>
| <expression>} [[AS] <alias>]}...]
FROM <table name> [[AS] <alias>]
[WHERE <expression> [{<operator> <expression>}...]]
[ORDER BY <column name> [ASC | DESC]
[{, <column name> [ASC | DESC]}...]]
[LIMIT [<offset>,] <row count>]
SELECT All Columns in All Rows
Syntax:
SELECT table_name.* FROM table;
-- OR
SELECT * FROM table;
--Retrieve all columns from the customer table
Example :
SELECT *FROM customer;
SELECT Specific Columns
The following query is used to retrieve specific columns in all rows of a table.
Syntax:
SELECT table_name.column_name, table_name.column_name
FROM table;
-- OR
SELECT column, column FROM table;
Example:
/*Select the film_title and rating from the film table.*/
SELECT title, rating FROM filmtab;
The WHERE Clause and Basic Operator Symbols
- The WHERE clause is used to retrieve specific rows from tables.
- The WHERE clause can contain one or more conditions that specify which rows should be returned.
Syntax:
SELECT column, column FROM table
WHERE conditions;
Note: The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings. the non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes.
Example:
/*Create a report showing the title
and rating of all films where rating is PG*/
SELECT title, rating FROM film WHERE rating = 'PG';
/*Create a report showing the first and last name of
all customers that do not belong to store id 2.*/
SELECT store_id, first_name, last_name
FROM customer
WHERE store_id <> 2;
Example:
-- Checking for films that run more than 3 hours
SELECT title, rating, rental_rate
FROM film
WHERE length >= 180;
Checking for NULL
- When a field in a row has no value, it is said to be NULL.
- This is not the same as having an empty string. Rather, it means that the field contains no value at all.
Example:
/*Create a report showing films where category is not defined.*/
SELECT title, rating, rental_rate FROM film_details
WHERE category_name IS NULL;
SELECT last_name, first_name FROM stafftab
WHERE picture IS NOT NULL;
Note: When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.
WHERE and ORDER BY
When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.
Example:
/*Create a report showing the first
and last name of all customers whose last names start
with a letter in the last half of the alphabet.
Sort by Last Name in descending order.*/
SELECT first_name, last_name FROM customer
WHERE last_name >= ‘N‘
ORDER BY last_name DESC;
Using Aliases
The column names and the table name can be followed by an optional AS subclause that allows you to assign an alias to these names.
Example:
SELECT fl.title AS 'Film Title', fl.rating AS 'Film Rating‘
FROM filmtab AS fl
WHERE fl.film_id = 998;
MySQL DISTINCT Clause |
Selecting Distinct Records
The DISTINCT keyword is used to select distinct combinations of column values from a table.
Example:
/*Find all the distinct ratings assigned to existing films.*/
SELECT DISTINCT rating FROM film;
/*How many distinct customers have rented movies.*/
SELECT COUNT(DISTINCT customer_id) AS 'Customer Count‘
FROM rental;
SELECT COUNT(customer_id) FROM customer;
MySQL ORDER BY Clause |
Sorting Records
- The ORDER BY clause of the SELECT statement is used to sort records.
- The ORDER BY clause determines the order in which rows are returned in a results set.
Syntax:
ORDER BY <order by definition> <order by definition>::=
<column name> [ASC | DESC][{, <column name> [ASC | DESC]}...]
Note: the ORDER BY clause must include the ORDER BY keywords and at least one column name or a column alias. If you include more than one column, a comma must separate them.
– By default, for each column in an ORDER BY clause, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword after the column name.
– In addition, when more than one column is specified, the rows are sorted first by the column that is listed first, then by the next specified column, and so on.
Example:
SELECT customer_id, store_id, first_name, last_name
FROM customer
ORDER BY last_name;
-- Sorting by a single column last_name
SELECT customer_id, store_id, first_name, last_name
FROM customer
ORDER BY store_id, last_name;
-- Nested sorting By Multiple Columns.
Example:
SELECT film_id, title, rating, rental_rate
FROM film
ORDER BY 4 ASC, 3 DESC;
-- Sorting By Column Position (numeric) instead of column name.
SELECT title, rating, rental_rate
FROM film
ORDER BY rental_rate ASC, rating DESC, title ASC;
-- Specify and mix Ascending and Descending Sorts.
In this case, the returned rows are sorted first by the values in the rental_rate column in ascending order) and then by the values in the rating column in descending order and so on.
MySQL LIMIT Clause |
The LIMIT Clause
-The LIMIT clause is used most effectively in a SELECT statement when it is used with an ORDER BY clause.
-The LIMIT clause takes two arguments,
Syntax:
LIMIT [<offset>,] <row count>
– The first option, <offset>, is optional and indicates where to begin the LIMIT row count.
-If no value is specified, 0 is assumed. (The first row in a result set is considered to be 0, rather than 1.) The second argument, <row count> in the LIMIT clause, indicates the number of rows to be returned.
Example:
SELECT last_name, first_name, email
FROM customer LIMIT 5;
-- above query select any first five customers.
SELECT title, rating, rental_rate
FROM filmtab
ORDER BY rating DESC, title ASC LIMIT 5;
Example:
SELECT title, rating, rental_rate
FROM filmtab
ORDER BY rental_rate ASC, rating DESC, title ASC LIMIT 2,4;
The example includes a LIMIT clause that specifies an offset value of 2 and a row count value of 3. So three rows starting from the third row are returned.
Download Examples |
Recent Comments