Common MySQL Database SQL statement writing and running results

Provincial City Questions
#省份表
select * from Province;
+----+----------+
| ID | Province |
+----+----------+
| 1 | Guangdong |
| 2 | Hunan |
| 3 | Hubei |
+----+----------+
3 Rows in Set

Mysql> #城市表
SELECT * from city;
+----+------+------------+
| ID | City | Provinceid |
+----+------+------------+
| 1 |          guangzhou | 1 |
| 2 |          shenzhen | 1 |
| 3 |          Huizhou | 1 |
| 4 |          Changsha | 2 |
| 5 |          Wuhan | 3 |
+----+------+------------+
#获得所有的城市并得出该城市的省
Select C.id,c.city,p.province from city C, left joins province p on C.provinceid=p.id;
+----+------+----------+
| ID | City | Province |
+----+------+----------+
| 1 | guangzhou | Guangdong |
| 2 | shenzhen | Guangdong |
| 3 | Huizhou | Guangdong |
| 4 | Changsha | Hunan |
| 5 | Wuhan | Hubei |
+----+------+----------+
#获得所有省份 and investigate the number of cities in the province
Select P.id,p.province,count (*) from the city C left join province p on c.provinceid=p.id Group by C.provinceid;

+----+----------+----------+
| ID | Province | COUNT (*) |
+----+----------+----------+
| 1 |        Guangdong | 3 |
| 2 |        Hunan | 1 |
| 3 |        Hubei | 1 |
+----+----------+----------+


Student score Table

Mysql> select * from score;
+----+----------+-------+-------+
| ID | Username | Class | Score |
+----+----------+-------+-------+
| 1 | Zhang San |    language | 81 |
| 2 | Zhang San |    Math | 75 |
| 3 | John Doe |    language | 76 |
| 4 | John Doe |    Math | 90 |
| 5 | Harry |    language | 81 |
| 6 | Harry |   Math | 100 |
| 7 | Harry |    English | 90 |
+----+----------+-------+-------+

Get the total number of students, average score
Select Id,username,sum (Score), AVG (score) from score Group by username;
+----+----------+------------+------------+
| ID | Username | SUM (Score) | AVG (Score) |
+----+----------+------------+------------+
| 1 | Zhang San | 156 | 78.0000 |
| 3 | John Doe | 166 | 83.0000 |
| 5 | Harry | 271 | 90.3333 |
+----+----------+------------+------------+

#获得所有分数都大于80的学生的名字
SELECT * FROM score GROUP by username have min (score) >80;
+----+----------+-------+-------+
| ID | Username | Class | Score |
+----+----------+-------+-------+
| 5 | Harry |    language | 81 |
+----+----------+-------+-------+

SELECT DISTINCT (username) from score where username isn't in (select username from score where score<80);
+----------+
| Username |
+----------+
| Harry |
+----------+



Contains values
SELECT * from Test;
+----+---------+
| ID | Data |
+----+---------+
| 1 | 2 |
| 2 | |
| 3 | 1,22,23 |
| 4 | 2,34 |
| 5 | 1,2,6 |
+----+---------+
5 rows in Set
Get 2 of all data, but not 22 etc.
Mysql> SELECT * FROM Test where find_in_set (2,data);
+----+-------+
| ID | Data |
+----+-------+
| 1 | 2 |
| 2 | |
| 4 | 2,34 |
| 5 | 1,2,6 |
+----+-------+

Common MySQL Database SQL statement writing and running results