MySQL Database Single Table query

I. Query syntax

SELECT Field 1, Field 2 ... From table name                  WHERE condition                  GROUP by field have                  filter                  ORDER by field limit                  number of bars

two. Keyword Execution priority

 from wheregroup Byhavingselectdistinctorder bylimit 1. Find the table: from 2. Take the constraints specified in the Where, go to the File/table to take out a strip of records 3. Group BY, if there is no group by, the whole as a group of 4 . Follow the fields in the select to get a new virtual table, and if there is an aggregate function, aggregate the data in the group 5. Filter the results of 4:having 6. Finding the result: Select7. Go to Weight 8. Sort the results conditionally: ORDER by9. Limit the number of display bars for the result

three. Querying Instances

#Simple QuerySELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee; SELECT*from employee; SELECT name,salary from employee;#avoid repeating distinctSELECT DISTINCT post from employee; #Query by ArithmeticSELECT name, salary*12from employee; SELECT Name, Salary*12As annual_salary from employee; SELECT Name, Salary*12annual_salary from employee;#Defining display FormatsThe CONCAT () function is used for connection strings SELECT CONCAT ('Name:', Name,'Annual Salary:', salary*12) as annual_salary from employee; Concat_ws () The first parameter is a delimiter SELECT concat_ws (':', name,salary*12) as annual_salary from employee;

four. Where Constraints

Note:where is a constraint, and MySQL takes the condition specified in the Where to fetch the data in the table, and the having is filtered after the data is fetched

the WHERE clause can be used:  and the value between 10 and 20 in (80,90,100) value is 10 or 20 or'egon%'     pattern can be% or _,    % means any number of    characters and or not  

Five. Group by

Major premise: You can group by any field, but after the group is divided, can only see the group of that field, to take the other field information in the group, you need to use the function

Instance:
Use the group BY keyword to group    SELECT post from the employee group by post alone;    Note: We group by post field, then the field of the select query can only be post, want to get other related information within the group, need to use the function GROUP by keyword and group_concat () function with    SELECT post,group_ CONCAT (name) from the employee GROUP by post; # Group by post and view member names within a group     SELECT Post,group_concat (name) as Emp_members from the employee GROUP by post; Group BY is used    with the aggregation function from the employee group by post;  # GROUP by Post and see how many people are in each group

six. Querying with aggregate functions 　

First from Find table

Then use the WHERE condition constraint to take the record out of the table

Group BY is then grouped by default, without grouping

And then do the aggregation

The final Select results

Example:    SELECT COUNT (*) from employee;    SELECT COUNT (*) from employee WHERE depart_id=1;    SELECT MAX (salary) from employee;    SELECT MIN (salary) from employee;    SELECT AVG (salary) from employee;    SELECT SUM (salary) from employee;    SELECT SUM (Salary) from employee WHERE depart_id= 3;

Seven. Having filter

 from the employee where salary > 10000 from the employee having salary > 10000;

##1. Where is a constraint declaration that uses where to constrain data from the database, where it works before the result is returned (the table is found first, the data is fetched from the table (file) according to where constraints, and the aggregate function cannot be used in where.)  #2. Having is a filter declaration that filters the results of a query after the query returns a result set (the table is found, the data is fetched from the table (file) according to where constraints, and then the group by group, if there is no group BY, all records are grouped, and then the aggregate function is executed , and then use having to filter the results of the aggregation), you can use aggregate functions in the having.  #3. Having can be placed after group by, and where can only be placed before group by #4. Aggregate statements during query (SUM,MIN,MAX,AVG, Count) is performed precedence over the HAVING clause. The WHERE clause takes precedence over the aggregate statement during the query. 

eight. Query ordering

sort    by single column * From the employee ORDER by salary    ; * From the employee ORDER by salary ASC    ; * From the employee order by salary DESC; Sort by multiple columns: Sort by the age first, and if you are older,    sort by salary from   employee        Order By age,        salary DESC;

Nine. Limit the number of records for a query

Example:     * From the employee ORDER by salary         DESC3;                    # * From the employee ORDER by salary DESC        LIMIT 0,# starting from the No. 0, the first one is queried first,            And then include this one and look      back. 5 * from the employee ORDER by salary        DESC# starting from the 5th one, That is, the 6th is queried first, and then included in the article 5

MySQL Database Single Table query