MySQL query clause

Tag: Ike DESC Score--Cannot use CREATE database processing

Model

Create Database Sky;

Use Sky;

CREATE TABLE M1 (

ID Int (11),

Name Char (20),

Age tinyint (10),

Sex enum (' Male ', ' female '),

Score tinyint (10),

Address char (20)

) default Charset=utf8;

INSERT INTO M1 values

(1, ' L1 ', 21, ' Male ', 90, ' Beijing '),

(2, ' L2 ', 19, ' Male ', 91, ' Shanghai '),

(3, ' L3 ', 24, ' female ', 95, ' Guangzhou '),

(4, ' L4 ', 22, ' Male ', 89, ' Guangzhou '),

(5, ' L5 ', 20, ' female ', 86, ' Shanghai '),

(6, ' L6 ', 19, ' Female ', 99, ' Guangzhou ');

Execution order

1. Select * from *

2. Where Condition statement # filters only fields that exist in the table

3. GROUP BY group

4. Having a screening

5. Order by sort high and low

6. Limit shows a few lines, descending or ascending

GROUP BY

Function: To group the results of a query, to

1. The field after group by must be a field after select

2. If the field after select is inconsistent with the field after group by, the field after select must be aggregated.

Filter a few cities

Mysql> Select address from M1 group by address;

+---------+

| Address |

+---------+

| Shanghai |

| Beijing |

| guangzhou |

+---------+

3 Rows in Set (0.00 sec)

Having

Role: To further filter the results of the query, only filter the calculated fields

1. Having statements are commonly used in conjunction with the group BY statement to filter the recordset returned by the GROUP BY statement

2. The existence of a having statement compensates for deficiencies in the WHERE keyword cannot be used in conjunction with an aggregate function

Remove a city with a score greater than or equal to 90 of the first two

Mysql> Select Address,avg (score) from M1

Group BY address

have AVG (score) >= 90

ORDER by AVG (SCORE) desc

, limit 2;

+---------+------------+

| Address | AVG (Score) |

+---------+------------+

|    guangzhou | 94.3333 |

|    Beijing | 90.0000 |

+---------+------------+

2 rows in Set (0.00 sec)

ORDER BY

Role: Sort the results of a query

Syntax: ORDER BY field name sorting method

Sort by:

ASC (default): Ascending

DESC: Descending

Sort from high to low

Mysql> SELECT * FROM M1 order by score Desc;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 4 |   L4 | 22 |   Male | 101 | guangzhou |

| 6 |   L6 | 19 |    Women | 99 | Beijing |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 2 | L2 | NULL |    Male | 91 | Shanghai |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

| 5 | L5 | NULL |    Women | 86 | Shanghai |

+------+------+------+------+-------+---------+

6 rows in Set (0.00 sec)

Show address in Beijing and Shanghai, age 20 +, people pressing score ascending

Mysql> SELECT * FROM M1

, where

--Address in ("Beijing", "Guangzhou") and age like ' 2_ '

Order BY score ASC;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

+------+------+------+------+-------+---------+

3 Rows in Set (0.00 sec)

Limit

Role: Limit the number of bars that display query records

Grammar:

Limit n shows N records

Limit M,n from the first m+1 record, showing n

Score field in descending order, showing top three

Mysql> SELECT * FROM M1 order by score Desc;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 6 |   L6 | 19 |    Women | 99 | guangzhou |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

| 5 |   L5 | 20 |    Women | 86 | Shanghai |

+------+------+------+------+-------+---------+

6 rows in Set (0.00 sec)

Mysql> SELECT * FROM M1 ORDER BY score desc LIMIT 3;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 6 |   L6 | 19 |    Women | 99 | guangzhou |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

+------+------+------+------+-------+---------+

3 Rows in Set (0.00 sec)

Socre field descending starting from the second place four article

Mysql> SELECT * FROM M1 ORDER BY score desc LIMIT 1, 4;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 2 |   L2 | 19 |    Male | 91 | Shanghai |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

| 4 |   L4 | 22 |    Male | 89 | guangzhou |

+------+------+------+------+-------+---------+

4 rows in Set (0.00 sec)

Query age is not empty, the top three of the people

Mysql> SELECT * from M1;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 2 | L2 | NULL |    Male | 91 | Shanghai |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

| 4 |   L4 | 22 |   Male | 101 | guangzhou |

| 5 | L5 | NULL |    Women | 86 | Shanghai |

| 6 |   L6 | 19 |    Women | 99 | Beijing |

| 1 |   L1 | 21 |    Male | 90 | Beijing |

+------+------+------+------+-------+---------+

6 rows in Set (0.00 sec)

Mysql> SELECT * FROM M1

--where age was not null

ORDER BY score Desc

, limit 3;

+------+------+------+------+-------+---------+

| ID | name | Age | sex | Score | Address |

+------+------+------+------+-------+---------+

| 4 |   L4 | 22 |   Male | 101 | guangzhou |

| 6 |   L6 | 19 |    Women | 99 | Beijing |

| 3 |   L3 | 24 |    Women | 95 | guangzhou |

+------+------+------+------+-------+---------+

3 Rows in Set (0.00 sec)

MySQL query clause