MySQL Query data

Select

Use SELECT statements to get data from a table or view.

　　

2. Explanation

• SELECTThis is followed by a comma-delimited column or * a list of asterisks (), indicating that you want to return all columns.
• FROMSpecifies the table or view for which you want to query data.
• JOINGet data from other tables based on some connection conditions.
• WHEREFilters the rows in the result set.
• GROUP BYCombines a set of rows into small groupings and applies aggregate functions to each small group.
• HAVINGFilters are based on GROUP BY small groupings defined by clauses.
• ORDER BYSpecifies the list of columns to use for sorting.
• LIMITLimit 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