目录
- 1.连接查询
- 1.1 内连接
- 1.2 外连接
- 左连接
- 右连接
- 1.3全连接
- 2.分组查询
- 2.1 语法
- 2.2 查询
1.连接查询
- 笛卡尔积，表记录的乘积
- 内连接查询时，若没有条件，或者条件为真，则返回笛卡尔积
- 创建两张表student_info,student_score
MariaDB [wisan_db]> desc wisan_db.student_info;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
MariaDB [wisan_db]> select * from student_info;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | xiang | 18 |
| 2 | peng | 19 |
| 3 | fan | 17 |
| 4 | yi | 20 |
| 5 | jing | NULL |
| 6 | wisan | 19 |
| 7 | flora | 18 |
| 8 | wang | NULL |
+----+-------+------+
MariaDB [wisan_db]> desc wisan_db.student_score;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | tinyint(4) | YES | | NULL | |
| grade | char(1) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
MariaDB [wisan_db]> select * from student_score;
+----+-----------+-------+-------+
| id | name | score | grade |
+----+-----------+-------+-------+
| 2 | peng | 88 | A |
| 3 | fan | 90 | A |
| 5 | jing | 100 | A |
| 6 | wisan | NULL | C |
| 7 | flora | 85 | B |
| 8 | wang | NULL | C |
| 10 | lisi | 60 | B |
| 11 | biyue | 85 | B |
| 13 | zhangshan | 70 | B |
| 15 | qiuwu | 70 | B |
+----+-----------+-------+-------+1.1 内连接
- 说明：组合两个表中的记录，返回满足条件的两个表交集部分。
- 关键字：inner join on 连接条件
- 语句：select * from a_table inner join b_table on a_table.id = b_table.id;
##没有条件时
MariaDB [wisan_db]> select * from student_info as a inner join student_score as b;
+----+-------+------+----+-----------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+----+-----------+-------+-------+
| 1 | xiang | 18 | 2 | peng | 88 | A |
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 2 | peng | 88 | A |
| 4 | yi | 20 | 2 | peng | 88 | A |
| 5 | jing | NULL | 2 | peng | 88 | A |
| 6 | wisan | 19 | 2 | peng | 88 | A |
| 7 | flora | 18 | 2 | peng | 88 | A |
| 8 | wang | NULL | 2 | peng | 88 | A |
| 1 | xiang | 18 | 3 | fan | 90 | A |
| 2 | peng | 19 | 3 | fan | 90 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 4 | yi | 20 | 3 | fan | 90 | A |
| 5 | jing | NULL | 3 | fan | 90 | A |
| 6 | wisan | 19 | 3 | fan | 90 | A |
| 7 | flora | 18 | 3 | fan | 90 | A |
....................................................
##一共有8*10条记录##有条件时（a.id=b.id），此时输出表的交集
MariaDB [wisan_db]> select * from student_info as a inner join student_score as b on a.id = b.id;
+----+-------+------+----+-------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+----+-------+-------+-------+
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
+----+-------+------+----+-------+-------+-------+1.2 外连接
左连接
- 说明：返回左表(即关键词左边的表)所有记录和右表满足条件的记录(不足的NULL代替)
- 关键字：left join on 连接条件
- 语句：select * from a_table left join b_table on a_table.id = b_table.id;
##
MariaDB [wisan_db]> select * from student_info as a left join student_score as b on a.id = b.id;
+----+-------+------+------+-------+-------+-------+
| id | name | age | id | name | score | grade |
+----+-------+------+------+-------+-------+-------+
| 1 | xiang | 18 | NULL | NULL | NULL | NULL |
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 4 | yi | 20 | NULL | NULL | NULL | NULL |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
+----+-------+------+------+-------+-------+-------+
8 rows in set (0.001 sec)右连接
- 说明：返回右表(即关键词右边的表)所有记录和左表满足条件的记录(不足的NULL代替)
- 关键字：right join on 连接条件
- 语句：select * from a_table right join b_table on a_table.id = b_table.id;
MariaDB [wisan_db]> select * from student_info as a right join student_score as b on a.id = b.id;
+------+-------+------+----+-----------+-------+-------+
| id | name | age | id | name | score | grade |
+------+-------+------+----+-----------+-------+-------+
| 2 | peng | 19 | 2 | peng | 88 | A |
| 3 | fan | 17 | 3 | fan | 90 | A |
| 5 | jing | NULL | 5 | jing | 100 | A |
| 6 | wisan | 19 | 6 | wisan | NULL | C |
| 7 | flora | 18 | 7 | flora | 85 | B |
| 8 | wang | NULL | 8 | wang | NULL | C |
| NULL | NULL | NULL | 10 | lisi | 60 | B |
| NULL | NULL | NULL | 11 | biyue | 85 | B |
| NULL | NULL | NULL | 13 | zhangshan | 70 | B |
| NULL | NULL | NULL | 15 | qiuwu | 70 | B |
+------+-------+------+----+-----------+-------+-------+1.3全连接
- 关键字：union | union all
- 语法: (查询语句) union|union all (查询语句)
- 注意：
1.查询的COLUMN数(列数)必须相等
2.没有要求时，合并的表列名以第一个查询语句为准
3.union会合并完全相同的记录，但是比较耗时，union all则不会，通常使用union all
4.在单个查询的语句中即便排序，也不会有排序效果，但是可以对最终结果排序
##union查询name，age字段,两条记录（wang null）合并为一条
MariaDB [wisan_db]> (select name,age from student_info) union (select name,score from student_score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL | ####
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
## union all查询name,score字段
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL | ######
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| wang | NULL | ######
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
18 rows in set (0.001 sec)
##在第二个查询语句排序，结果并没有排序
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score order by score);
+-----------+------+
| name | age |
+-----------+------+
| xiang | 18 |
| peng | 19 |
| fan | 17 |
| yi | 20 |
| jing | NULL |
| wisan | 19 |
| flora | 18 |
| wang | NULL |
| peng | 88 |
| fan | 90 |
| jing | 100 |
| wisan | NULL |
| flora | 85 |
| wang | NULL |
| lisi | 60 |
| biyue | 85 |
| zhangshan | 70 |
| qiuwu | 70 |
+-----------+------+
##对整个结果排序
MariaDB [wisan_db]> (select name,age from student_info) union all (select name,score from student_score order by score) order by age;
+-----------+------+
| name | age |
+-----------+------+
| jing | NULL |
| wang | NULL |
| wisan | NULL |
| wang | NULL |
| fan | 17 |
| flora | 18 |
| xiang | 18 |
| peng | 19 |
| wisan | 19 |
| yi | 20 |
| lisi | 60 |
| qiuwu | 70 |
| zhangshan | 70 |
| flora | 85 |
| biyue | 85 |
| peng | 88 |
| fan | 90 |
| jing | 100 |
+-----------+------+2.分组查询
2.1 语法
SELECT column, group_function,... FROM table [WHERE condition] GROUP BY group_by_expression [HAVING group_condition];- 说明
group_function：聚合函数。
group_by_expression：分组表达式，多个之间用逗号隔开。
group_condition：分组之后对数据进行过滤。
分组中，select后面只能有两种类型的列：
- group_function：
函数名 | 作用 |
max | 查询指定列的最大值 |
min | 查询指定列的最小值 |
count | 统计查询结果的行数 |
sum | 求和，返回指定列的总和 |
avg | 求平均值，返回指定列数据的平均值 |
2.2 查询
- 建表
MariaDB [wisan_db]> select * from student_info;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | xiang | 18 |
| 2 | peng | 19 |
| 3 | fan | 17 |
| 4 | yi | 20 |
| 5 | jing | NULL |
| 6 | wisan | 19 |
| 7 | flora | 18 |
| 8 | wang | NULL |
+----+-------+------+
8 rows in set (0.001 sec)
MariaDB [wisan_db]> select * from student_score;
+----+-----------+-------+-------+
| id | name | score | grade |
+----+-----------+-------+-------+
| 2 | peng | 88 | A |
| 3 | fan | 90 | A |
| 5 | jing | 100 | A |
| 6 | wisan | NULL | C |
| 7 | flora | 85 | B |
| 8 | wang | NULL | C |
| 10 | lisi | 60 | B |
| 11 | biyue | 85 | B |
| 13 | zhangshan | 70 | B |
| 15 | qiuwu | 70 | B |
+----+-----------+-------+-------+- 单独查询
##每个组只取第一个
MariaDB [wisan_db]> select * from student_score group by grade;
+----+-------+-------+-------+
| id | name | score | grade |
+----+-------+-------+-------+
| 2 | peng | 88 | A |
| 7 | flora | 85 | B |
| 6 | wisan | NULL | C |
+----+-------+-------+-------+- group_concat()函数输出里面的所有成员
MariaDB [wisan_db]> select group_concat(name),group_concat(score),grade from student_score group by grade;
+----------------------------------+---------------------+-------+
| group_concat(name) | group_concat(score) | grade |
+----------------------------------+---------------------+-------+
| peng,fan,jing | 88,90,100 | A |
| zhangshan,biyue,lisi,qiuwu,flora | 70,85,60,70,85 | B |
| wisan,wang | NULL | C |
+----------------------------------+---------------------+-------+- count()函数统计里面的成员数量
MariaDB [wisan_db]> select count(name),grade from student_score group by grade;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
| 2 | C |
+-------------+-------+- HAVING，对分组后的结果筛选,分组人数>=3
MariaDB [wisan_db]> select count(name),grade from student_score group by grade having count(name)>=3;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
+-------------+-------+- with roollup在最后进行统计
MariaDB [wisan_db]> select count(name),grade from student_score group by grade with rollup;
+-------------+-------+
| count(name) | grade |
+-------------+-------+
| 3 | A |
| 5 | B |
| 2 | C |
| 10 | NULL |
+-------------+-------+
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
