mysql多表查询

交叉连接

#SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];

#SELECT <字段名> FROM <表1>, <表2> [WHERE子句];

字段名：需要查询的字段名称。

<表1><表2>：需要交叉连接的表名。

WHERE 子句：用来设置交叉连接的查询条件

#创建school库 创建tb_students_info表

内连接

#SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句];

字段名：需要查询的字段名称。

<表1><表2>：需要内连接的表名。

INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。

ON 子句：用来设置内连接的连接条件。

#tb_students_info别名为s tb_course别名为c 取s的name和c的course_name值 条件是s的course_id = c的id

group by 和聚合函数

mysql> SELECT sex,COUNT(sex) FROM tb_students_info GROUP BY sex;
+--------+------------+
| sex    | COUNT(sex) |
+--------+------------+
| Female |          5 |
| Male   |          6 |
+--------+------------+
2 rows in set (0.01 sec)
 
mysql> SELECT sex,COUNT(sex) FROM tb_students_info ;
+------+------------+
| sex  | COUNT(sex) |
+------+------------+
| Male |         11 |
+------+------------+
1 row in set (0.00 sec)
 
mysql> SELECT sex 性别,COUNT(sex) 个数 FROM tb_students_info GROUP BY sex;
+--------+--------+
| 性别   | 个数   |
+--------+--------+
| Female |      5 |
| Male   |      6 |
+--------+--------+
2 rows in set (0.00 sec)
 
mysql> SELECT sex 性别,COUNT(1) 个数 FROM tb_students_info GROUP BY sex;
+--------+--------+
| 性别   | 个数   |
+--------+--------+
| Female |      5 |
| Male   |      6 |
+--------+--------+
2 rows in set (0.00 sec)

group by 与 with rollup

mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex WITH ROLLUP;
+--------+-------------------------------------------------------------+
| sex    | GROUP_CONCAT(name)                                          |
+--------+-------------------------------------------------------------+
| Female | Henry,Jim,John,Thomas,Tom                                   |
| Male   | Dany,Green,Jane,Lily,Susan,LiMing                           |
| NULL   | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan,LiMing |
+--------+-------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex ;
+--------+-----------------------------------+
| sex    | GROUP_CONCAT(name)                |
+--------+-----------------------------------+
| Female | Henry,Jim,John,Thomas,Tom         |
| Male   | Dany,Green,Jane,Lily,Susan,LiMing |
+--------+-----------------------------------+
2 rows in set (0.00 sec)
 
mysql> select avg(age) from tb_students_info;
+----------+
| avg(age) |
+----------+
|  22.7273 |
+----------+
1 row in set (0.00 sec)
 
mysql> select avg(age) from tb_students_info group by sex;
+----------+
| avg(age) |
+----------+
|  22.6000 |
|  22.8333 |
+----------+
2 rows in set (0.00 sec)

子查询

mysql> SELECT name FROM tb_students_info  WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)
 
mysql> SELECT id FROM tb_course WHERE course_name = 'Java';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
 
mysql> SELECT name FROM tb_students_info  WHERE course_id IN (1);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)
 
mysql> SELECT name FROM tb_students_info  WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
9 rows in set (0.01 sec)
 
mysql>  SELECT name FROM tb_students_info WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');
+------+
| name |
+------+
| Jane |
+------+
1 row in set (0.00 sec)
 
mysql> SELECT name FROM tb_students_info WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python');
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
10 rows in set (0.00 sec)
 
mysql> SELECT * FROM tb_students_info WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+--------+------+--------+--------+-----------+
| id | name   | age  | sex    | height | course_id |
+----+--------+------+--------+--------+-----------+
|  1 | Dany   |   25 | Male   |    160 |         1 |
|  2 | Green  |   23 | Male   |    158 |         2 |
|  3 | Henry  |   23 | Female |    185 |         1 |
|  4 | Jane   |   22 | Male   |    162 |         3 |
|  5 | Jim    |   24 | Female |    175 |         2 |
|  6 | John   |   21 | Female |    172 |         4 |
|  7 | Lily   |   22 | Male   |    165 |         4 |
|  8 | Susan  |   23 | Male   |    170 |         5 |
|  9 | Thomas |   22 | Female |    178 |         5 |
| 10 | Tom    |   23 | Female |    165 |         5 |
| 11 | LiMing |   22 | Male   |    180 |         7 |
+----+--------+------+--------+--------+-----------+
11 rows in set (0.00 sec)
 
mysql>  SELECT * FROM tb_students_info WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | Male |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.00 sec)