MySQL Single table query

Table of Inquiry
Mysql> SELECT * from test;
+-----+--------------+-----+--------+------+------+------+---------------------+
| SID | Name | Age | Gender | CID1 | CID2 | TID | Createtime |
+-----+--------------+-----+--------+------+------+------+---------------------+
| 1 |  guojing | 19 |    M |    2 |    7 | 3 | 2014-11-29 10:00:00 |
| 2 |  Yangguo | 17 |    M |    2 |    3 | 1 | 2014-11-29 10:00:00 |
| 3 |  Dingdian | 25 |    M |    6 |    1 | 7 | 2014-11-29 10:00:00 |
| 4 |  Fufei | 31 |    M |   8 |    10 | 5 | 2014-11-29 10:00:00 |
| 5 |  Huangrong | 16 |    F |    5 |    9 | 9 | 2014-11-29 10:00:00 |
| 6 |  Yuelingshang | 18 |    F |    8 | 4 | NULL | 2014-11-29 10:00:00 |
| 7 |  Zhangwuji | 20 |    M |    1 | 7 | NULL | 2014-11-29 10:00:00 |
| 8 |  Xuzhu | 26 |    M |    2 | 4 | NULL | 2014-11-29 10:00:00 |
| 9 |  Linghuchong | 22 |   M | 11 | NULL | NULL | 2014-11-29 10:00:00 |
| 10 |  Yilin | 19 |   F | 18 | NULL | NULL | 2014-11-29 10:00:00 |
+-----+--------------+-----+--------+------+------+------+---------------------+


single-table query syntax:
SELECT * from table name; #查询整张表的所有数据
SELECT Field 1, Field 2 from table name; #投影
SELECT query list from table name WHERE condition; #选择
WHERE: Relational expressions, used to do comparison operations =, >, >=
！ = Not equal to number
<=> Null value
Logical Relationship:
And: Requires all conditions to be displayed
OR: You just need to meet a condition to show it.
Not: Take counter
Between ... And ... #满足前后两个查询条件
Like "what
%: Any character of any length
_: Single character of any length
In (): Give a list, select the values that match the criteria in the list
Is null: Determines whether it is empty
is NOT NULL: determine if not empty
DISTINCT: The same value is displayed only once

Drder by field name {asc| DESC}; Sort the results of a query
ASC: Ascending, default is ascending can omit
DESC: Descending
If you sort a large amount of data, it is best to set the collation beforehand or the ordering is expensive.

LIMIT: Shows the number of lines to be set
LIMIT [Offset,] line number

Aggregation functions:
Avg: Average
Min: Minimum value
Max: Max value
SUM: And
Count: Sum of number

GROUP BY: Grouping
Having filter filtering conditions group filtering
Field name as field alias

various query instances:
Projection query: Displays the name and age of everyone in the test table
Mysql> SELECT name,age from test;
+--------------+-----+
| Name | Age |
+--------------+-----+
|  guojing | 19 |
|  Yangguo | 17 |
|  Dingdian | 25 |
|  Fufei | 31 |
|  Huangrong | 16 |
|  Yuelingshang | 18 |
|  Zhangwuji | 20 |
|  Xuzhu | 26 |
|  Linghuchong | 22 |
|  Yilin | 19 |
+--------------+-----+
Rows in Set (0.00 sec)


Select query: Search for people older than 20 years in the table
Mysql> SELECT * FROM Test WHERE age>20;
+-----+-------------+-----+--------+------+------+------+---------------------+
| SID | Name | Age | Gender | CID1 | CID2 | TID | Createtime |
+-----+-------------+-----+--------+------+------+------+---------------------+
| 3 |  Dingdian | 25 |    M |    6 |    1 | 7 | 2014-11-29 10:00:00 |
| 4 |  Fufei | 31 |    M |   8 |    10 | 5 | 2014-11-29 10:00:00 |
| 8 |  Xuzhu | 26 |    M |    2 | 4 | NULL | 2014-11-29 10:00:00 |
| 9 |  Linghuchong | 22 |   M | 11 | NULL | NULL | 2014-11-29 10:00:00 |
+-----+-------------+-----+--------+------+------+------+---------------------+
4 rows in Set (0.00 sec)


Projection Plus Select query: Displays the name and age of the data in the table for men
Mysql> SELECT name,age from Test WHERE gender= ' M ';
+-------------+-----+
| Name | Age |
+-------------+-----+
|  guojing | 19 |
|  Yangguo | 17 |
|  Dingdian | 25 |
|  Fufei | 31 |
|  Zhangwuji | 20 |
|  Xuzhu | 26 |
|  Linghuchong | 22 |
+-------------+-----+

DISTINCT: The same value is displayed only once
Mysql> SELECT DISTINCT GenDer from test;
+--------+
| GenDer |
+--------+
| M |
| F |
+--------+


Query for all users older than one year old and greater than 20

Mysql> SELECT name,age from Test WHERE age+1>20;
+-------------+-----+
| Name | Age |
+-------------+-----+
|  Dingdian | 25 |
|  Fufei | 31 |
|  Zhangwuji | 20 |
|  Xuzhu | 26 |
|  Linghuchong | 22 |
+-------------+-----+
5 rows in Set (0.00 sec)

Show women younger than 20
Mysql> SELECT name,age,gender from Test WHERE not age>20 and not gender= ' M ';
+--------------+-----+--------+
| Name | Age | Gender |
+--------------+-----+--------+
|  Huangrong | 16 | F |
|  Yuelingshang | 18 | F |
|  Yilin | 19 | F |
+--------------+-----+--------+


Query users between 20 and 25 years of age
Mysql> SELECT name,age from Test WHERE age>=20 and age<=25;
+-------------+-----+
| Name | Age |
+-------------+-----+
|  Dingdian | 25 |
|  Zhangwuji | 20 |
|  Linghuchong | 22 |
+-------------+-----+


Mysql> SELECT name,age from Test WHERE age between and 25;
+-------------+-----+
| Name | Age |
+-------------+-----+
|  Dingdian | 25 |
|  Zhangwuji | 20 |
|  Linghuchong | 22 |
+-------------+-----+


Display name starts with Y
Mysql> SELECT Name from students WHERE Name like ' Y% ';
+--------------+
| Name |
+--------------+
| Yangguo |
| Yuelingshang |
| Yilin |
+--------------+


Display user name beginning with Hzd
Mysql> SELECT name from Test WHERE Name rlike ' ^[hzd].*$ ';
+-----------+
| Name |
+-----------+
| Dingdian |
| Huangrong |
| Zhangwuji |
+-----------+

Show users with age 18,20,25 in the table
Mysql> SELECT name,age from Test WHERE age in (18,20,25);
+--------------+-----+
| Name | Age |
+--------------+-----+
|  Dingdian | 25 |
|  Yuelingshang | 18 |
|  Zhangwuji | 20 |
+--------------+-----+

Show Course 2 for users who are empty
Mysql> SELECT Name from Test WHERE CID2 is NULL;
+-------------+
| Name |
+-------------+
| Linghuchong |
| Yilin |
+-------------+
2 rows in Set (0.00 sec)

Show Course 2 users who are not empty
Mysql> SELECT Name from Test WHERE CID2 is not NULL;
+--------------+
| Name |
+--------------+
| guojing |
| Yangguo |
| Dingdian |
| Fufei |
| Huangrong |
| Yuelingshang |
| Zhangwuji |
| Xuzhu |
+--------------+


Sort by query result, default to descending order
Mysql> SELECT name from Test ORDER by Name;
+--------------+
| Name |
+--------------+
| Dingdian |
| Fufei |
| guojing |
| Huangrong |
| Linghuchong |
| Xuzhu |
| Yangguo |
| Yilin |
| Yuelingshang |
| Zhangwuji |
+--------------+
Rows in Set (0.05 sec)

Sort by query result, ascending by default
Mysql> SELECT name from Test ORDER by Name DESC;
+--------------+
| Name |
+--------------+
| Zhangwuji |
| Yuelingshang |
| Yilin |
| Yangguo |
| Xuzhu |
| Linghuchong |
| Huangrong |
| guojing |
| Fufei |
| Dingdian |
+--------------+
Rows in Set (0.00 sec)


Display the field name as a name

mysql> SELECT name as name from test;
+--------------+
| name |
+--------------+
| guojing |
| Yangguo |
| Dingdian |
| Fufei |
| Huangrong |
| Yuelingshang |
| Zhangwuji |
| Xuzhu |
| Linghuchong |
| Yilin |
+--------------+

Show only the first two rows that meet the criteria
Mysql> SELECT Name from Test LIMIT 2;
+---------+
| Name |
+---------+
| guojing |
| Yangguo |
+---------+
2 rows in Set (0.00 sec)
Show filter out the last three lines of the first two lines
Mysql> SELECT Name from Test LIMIT 2, 3;
+-----------+
| Name |
+-----------+
| Dingdian |
| Fufei |
| Huangrong |
+-----------+

Show average age
Mysql> SELECT AVG (age) from test;
+----------+
| AVG (age) |
+----------+
| 21.3000 |
+----------+
1 row in Set (0.00 sec)

Show Minimum Age
Mysql> SELECT MIN (age) from test;
+----------+
| MIN (age) |
+----------+
| 16 |
+----------+
1 row in Set (0.02 sec)

Show Maximum Age
Mysql> SELECT MAX (age) from test;
+----------+
| MAX (age) |
+----------+
| 31 |
+----------+

Show the sum of the ages of all
Mysql> SELECT SUM (age) from test;
+----------+
| SUM (age) |
+----------+
| 213 |
+----------+


Mysql> SELECT COUNT (age) from test;
+------------+
| COUNT (age) |
+------------+
| 10 |
+------------+

Average age of male and female classmates in the enquiry form
Mysql> SELECT AVG (age), Gender from Test GROUP by Gender;
+----------+--------+
| AVG (age) | Gender |
+----------+--------+
| 22.8571 | M |
| 17.6667 | F |
+----------+--------+

Shows the number of courses and the number of students who have enrolled in each course greater than or equal to 2
Mysql> SELECT COUNT (CID1) as persons,cid1 from Test GROUP by CID1 have persons>=2;
+---------+------+
| Persons | CID1 |
+---------+------+
|    3 | 2 |
|    2 | 8 |
+---------+------+

This article from "Plum blossom fragrance from bitter cold" blog, please be sure to keep this source http://wangjunkang.blog.51cto.com/8809812/1582651

MySQL Single table query