MySQL Query data

Source: Internet
Author: User
Tags mysql query

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.