MySQL Single-table query

The syntax of a single-table query

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

Second, the execution order of the keyword1. Find the table: from2. Take the where specified constraints, go to the File/table to remove a record3. Group BY, if no group by, is grouped by the records taken out.4. To filter the results of a group5. Execute Select6. Go to the heavy7. Sort the results conditionally: ORDER by8. Limit the number of display bars of the result

SELECT Field 1, Field 2 ...    ⑤ from       table name ①       WHERE condition       ②       GROUP by field   ③ have       filter ④       ORDER by Field⑥ limit       number of bars 
    ⑦

Three, simple query

#简单查询    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from     employee;    SELECT * from employee;    Select Name,salary from Employee, #避免重复DISTINCT    Select DISTINCT post from employee;    #通过四则运算查询    SELECT name, salary*12 from employee;    SELECT name, salary*12 as annual_salary from employee;    Select name, salary*12 annual_salary from employee, #定义显示格式   CONCAT () function for connection string   SELECT CONCAT (' name: ', Name, '  Annual salary: ', salary*12)  As annual_salary from    employee;      Concat_ws () The first parameter is a delimiter of   SELECT concat_ws (': ', name,salary*12) as  annual_salary from    employee;   Combine Case statement:   SELECT       (case if           name = ' Egon ' then               name when           name = ' Alex ' then               CONCAT (Name, ' _BIGSB ')           ELSE               concat (NAME, ' SB ')           END       ) as New_name   from       EMP;

　　

Iv. where constraints

The WHERE clause can be used:

1. Comparison operators:> < >= <= <>! =

2. Between and 100 values between 10 and 20

3. In (80,90,100) value is 10 or 20 or 30

4. Like ' egon% ' pattern can be% or _,% means any number of characters _ denotes one character

5. Logical operators: logical operators and or not can be used directly in multiple conditions

#1: Single-condition query SELECT name from employee WHERE post= ' sale '; #2: Multi-criteria Query SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000; #3: keyword between and select n    Ame,salary from employee WHERE salary between 10000 and 20000;    SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;    #4: The keyword is null (determines whether a field is null, cannot be used as an equals sign, need to be) SELECT name,post_comment from employee WHERE post_comment is null;            SELECT name,post_comment from employee WHERE post_comment are not NULL; SELECT name,post_comment from employee WHERE post_comment= ';        Note ' is an empty string, not a null PS: Perform update employee set post_comment= ' where id=2; And then check it out, there's going to be a result. # #: keyword in collection query SELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or S        alary=9000;    SELECT Name,salary from the employee WHERE salary in (3000,3500,4000,9000); SELECT name,salary from Employee WHERE salary not in (3000,3500,4000,9000); #6: keyword like fuzzy query wildcard '% ' SELECT * from employee WHERE name like ' eg    %‘; Wildcard ' _ ' SELECT * from the employee WHERE name like ' al__ ';

Five group queries: GROUP BY 1 What is a group? Why are they grouped?

# 1. First make it clear: The grouping occurs after the where, that is, the grouping is based on the records obtained after the Where # 2. Grouping refers to classifying all records according to one of the same fields, such as grouping positions on employee information sheets, or grouping by gender # 3. Why should we group them? take     the maximum wage per department to get the number of    men and women in each department tips: The field behind the word ' every ' is the basis of our group #4, the premise:     can be grouped by any field, but after grouping, such as group by post, can only view the post field, if you want to view the information in the group, need to use the aggregation function

2 GROUP by

use GROUP BY keyword grouping    alone SELECT post from the employee GROUP by post;    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

Emphasize:

If we use unique fields as the basis for grouping, then each record becomes a group, and this grouping does not make sense for a field value between multiple records, which is usually used as the basis for grouping

3 Aggregation Functions

# emphasis: Aggregation functions aggregate the contents of a group, and if there is no grouping, the default set of 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;

4 having filter

Having the same place as where is!!!!!!

##1. Where occurs before grouping group by, so there can be any field in the where, but the aggregate function must never be used.  #2. Having a group by, and having the ability to use grouped fields, cannot be taken directly to other fields, you can use aggregate functions

Mysql>SELECT @ @sql_mode;+--------------------+| @ @sql_mode |+--------------------+| Only_full_group_by |+--------------------+1 rowinchSet (0.00sec) MySQL> select * fromEMP where salary > 100000;+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+| ID | name | sex | Age | hire_date | Post | post_comment | Salary | Office |  depart_id |+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+| 2 | Alex |  Male | 78 | 2015-03-02 | Teacher | NULL |    1000000.31 |         401 | 1 |+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+1 rowinchSet (0.00sec) MySQL> select * fromEmp having salary > 100000; ERROR1463 (42000): non-grouping field'Salary'  isUsedinchHaving clausemysql> select Post,group_concat (Name) fromEMP GROUP by post have salary > 10000;#error, cannot be taken directly to salary field after groupingERROR 1054 (42S22): Unknown column'Salary' inch 'Having clause'MySQL> select Post,group_concat (Name) fromEMP GROUP BY Post have AVG (Salary) > 10000;+-----------+-------------------------------------------------------+| Post | Group_concat (name) |+-----------+-------------------------------------------------------+| Operation | Cheng Bites the iron, Cheng bites the copper, the way bites the silver, Cheng Bites the gold, Koushik | | Teacher | Jackie Chan, Jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |+-----------+--------------------------------------------- ----------+2 rowsinchSet (0.00 sec)

Validation

Six-query sort: ORDER BY

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

　　

Seven limit the number of records to query: limit

Example:    SELECT * FROM employee ORDER by salary DESC         LIMIT 3;                    #默认初始位置为0         SELECT * from the employee ORDER by salary DESC        LIMIT 0,5; #从第0开始, that is, the first one is queried, and then this one is included. 5    SELECT * FR OM employee ORDER by salary DESC        LIMIT 5,5; The #从第5开始, that is, the first query 6th, and then include this one, and then look back to article 5.

　　

Eight using regular expression queries

The REGEXP operator is used to match regular expressions in MySQL. The pattern description ^ matches the starting position of the input string. $ matches the end position of the input string: matches any character (including carriage return and new line) [...] The character set is combined. Matches any one of the characters contained. For example, ' [ABC] ' can match ' a ' in ' plain '. [^...] Negative character set. Matches any character that is not contained. For example, ' [^ABC] ' can match ' P ' in ' plain '. P1|P2|P3 matches P1 or P2 or p3. For example, ' Z|food ' can match "z" or "food". ' (z|f) Ood ' matches "Zood" or "food". # ^  matches the data with the name "E" select * from the person where name REGEXP ' ^e '; # $  matches the data whose name ends with "n" select * from Per Son where name REGEXP ' n$ '; # . Match the person with the "X" after the second bit of the name "." Represents any character select * from the person where name REGEXP '. x '; # [ABCI] matches the name of the person with the specified collection content in the names select * from the people where name REGEXP ' [ABCI] '; # [^alex] matches content that does not conform to the conditions in the set, ^ represents the inverse select * from the person where name REGEXP ' [^alex] '; #注意1: ^ only within [] is the meaning of the reverse, in other places is the beginning of the match # NOTE 2:　　 Simply understand that name  REGEXP ' [^alex] ' is equivalent to the name! = ' Alex ' # ' a|x ' matches any value in the criteria select * from the person where name REGEXP ' a|x '; #查询以w开头以i结尾的数据select * from the person where name regexp ' ^w.*i$ '; #注意: ^w represents the beginning of W,. * Indicates that there can be any number of characters in the middle, i$ means ending with I

MySQL Single-table query