The path of programming: MySQL series single-table query

Syntax for single-table queries

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

Keyword execution priority (emphasis)

Priorities in Focus: Priority of keyword execution  from wheregroup Byhavingselectdistinctorder bylimit

Priority order

1. Find the table: from

2. Take the where specified constraints, go to the File/table to remove a record

3. Group BY, if no group by, is grouped by the records taken out.

4. To filter the results of a group

5. Execute Select

6. Go to the heavy

7. Sort the results conditionally: ORDER BY

8. Limit the number of display bars of the result

where constraint

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% '
The pattern can be either% or _,
% means any number of characters
_ Represents a character
5. Logical operators: logical operators and or not can be used directly in multiple conditions

#1: Single condition querySELECT name from employee WHERE Post='Sale'; #2: Multi-criteria QuerySELECT name,salary from employee WHERE Post='Teacher'and salary>10000;#3: keyword between andSELECT name,salary from employee WHERE salary between10000 and 20000; SELECT name,salary from employee WHERE salary not between10000 and 20000; #4: keyword is null (determines if a field is null cannot be used as an equals sign, need to be)SELECT name,post_comment from the 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="'; Attention"'is an empty string, NOT NULL PS: Perform update employee set post_comment="'where id=2; And then check it out, there's going to be a result.#5: Keyword in collection querySELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or salary=9000 ; SELECT name,salary from Employee WHERE salary in (3000,3500,4000,9000) ; SELECT name,salary from employee WHERE salary not in (3000,3500,4000,9000) ;#6: Keywords like fuzzy queryWildcard characters '%' SELECT*From employee WHERE name is like'eg%'; Wildcard ' _ ' SELECT*From employee WHERE name is like'al__';

Group query: GROUP BY

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

Only_full_group_by

#view MySQL 5.7 By default Sql_mode as follows:Mysql> Select @@Global. Sql_mode;only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero, No_auto_create_user,no_engine_substitution#!! NoteThe semantics of only_full_group_by is to determine that the values of all the columns in the Select target list are explicit semantics, and in a nutshell, in only_full_group_by mode, the target The value in the list is either the result of the aggregation function or the value from the expression in the group by list. #set Sql_mole as follows (we can remove only_full_group_by mode):Mysql> SetGlobalSql_mode='Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_user,no_engine_ SUBSTITUTION';!! Sql_mode Settings!!! 

!! Sql_mode Settings!!!

Mysql> Select @@Global. Sql_mode;+-------------------+| @@Global. Sql_mode |+-------------------+| |+-------------------+RowinchSet (0.00sec) MySQL> select * fromEMP Group by post;+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+---- -------+| ID | name | sex | Age | hire_date | Post | post_comment | Salary | Office | depart_id |+----+------+--------+-----+------------+----------------------------+--------------+------------+-- ------+-----------+| 14 | Koushik |  Male | 28 | 2016-03-11 | Operation |   NULL |    10000.13 |         403 |  3 | | 9 | Crooked |  Female | 48 | 2015-03-11 | Sale |    NULL |    3000.13 |         402 |  2 | | 2 | Alex |  Male | 78 | 2015-03-02 | Teacher | NULL |    1000000.31 |         401 |  1 | | 1 | Egon |  Male | 18 | 2017-03-01 | Old boy in Shahe office Diplomatic Ambassador |    NULL |    7300.33 |         401 | 1 |+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-- ---------+rowsinchSet (0.00sec)#because there is no set only_full_group_by, so can also have the result, the default is the first record in the group, but in fact, it is meaninglessMySQL> SetGlobalSql_mode='only_full_group_by'; Query OK, 0 rows affected (0.00sec) MySQL> Quit#after the setting is successful, be sure to exit and then login again to take effectByemysql>Use db1;database changedmysql> select * fromEMP GROUP by Post;#ErrorERROR 1055 (42000):'db1.emp.id'isn'T in GROUP byMysql> select Post,count (ID) fromEMP GROUP by Post;#You can only view group by and use aggregate functions+----------------------------+-----------+| Post | Count (ID) |+----------------------------+-----------+|         Operation | 5 | |         Sale | 5 | |         Teacher | 7 | |         Old boy in Shahe office Diplomatic Ambassador | 1 |+----------------------------+-----------+rowsinchSet (0.00 sec)

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

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;

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 |+--------------------+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 |+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+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 |+-----------+--------------------------------------------- ----------+rowsinchSet (0.00sec) Verification

Validation

Query sort: ORDER BY

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;

Limit the number of records for a query: limit

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

Querying using regular expressions

SELECT * FROM employee WHERE name REGEXP'^ale'; SELECT* FROM employee WHERE name REGEXP'on$'; SELECT* FROM employee WHERE name REGEXP'm{2}'; summary: How to match a string where name='Egon'; WHERE name like'yua%'; WHERE name REGEXP'on$';

The path of programming: MySQL series single-table query