Select
Use SELECT
statements to get data from a table or view.
2. Explanation
SELECT
This is followed by a comma-delimited column or *
a list of asterisks (), indicating that you want to return all columns.
FROM
Specifies the table or view for which you want to query data.
JOIN
Get data from other tables based on some connection conditions.
WHERE
Filters the rows in the result set.
GROUP BY
Combines a set of rows into small groupings and applies aggregate functions to each small group.
HAVING
Filters are based on GROUP BY
small groupings defined by clauses.
ORDER BY
Specifies the list of columns to use for sorting.
LIMIT
Limit the number of rows returned.
3.select * Problem
- Using an asterisk (
*
) may return data for columns that are not used. It generates unnecessary I/O disk and network traffic between the MySQL database server and the application.
- If you explicitly specify a column, the result set is more predictable and easier to manage. Imagine that when you use an asterisk (
*
) and someone changes the table data by adding more columns, you will get a different result set than expected.
- Using an asterisk (
*
) may expose sensitive information to unauthorized users
4.DISTINCT clause
When you query data from a table, you may receive duplicate row Records. In order to delete these duplicate rows, you can SELECT
use clauses in the statement DISTINCT
.
SELECT DISTINCT columns FROM table_name WHERE where_conditions;
Examples/:
SELECT LastName FROM Employees ORDER by LastName;
SELECT DISTINCT LastName FROM Employees order by LastName;
5.DISTINCT clause-to-null handling
will leave NULL as the same data, leaving a null.
Select distinct state from customers;
6.distinct use on multiple columns
You can use clauses that have multiple columns DISTINCT
. In this case, MySQL uses a combination of all columns to determine the uniqueness of the rows in the result set.
Select distinct state,city from customers where the state was not null for order by state,city;
7.distinct compared to ORDER by
If you SELECT
use clauses in a statement without GROUP BY
using an aggregate function, the GROUP BY
clause behaves like a DISTINCT
clause.
This one is actually suitable for a column.
Generally, DISTINCT
clauses are GROUP BY
special cases of clauses. DISTINCT
GROUP BY
The difference between clauses and clauses is that GROUP BY
clauses can sort the result set, and the DISTINCT
clauses are not sorted.
If you add an ORDER BY clause to DISTINCT
a statement that uses a clause, the result set is sorted and is the GROUP BY
same as the result set returned by the statement that uses the clause.
8.distinct and Aggregation functions
You can use clauses with aggregate functions, such as SUM,AVG and Count DISTINCT
, to remove duplicate rows before MySQL applies the aggregate function to the result set.
Select COUNT (distinct state) from customers where country= ' USa ';
9.distinct with limit
If you want to DISTINCT
use the clause with the limit clause, MySQL LIMIT
stops the search as soon as the number of unique rows specified in the clause is found.
MySQL Query data