《Mysql基础》【Mysql表查询、去重、表连接、左连接 右连接、子表查询、排序、分组等】 编程入门 学习分享 【公开免费】
-- mysql数据库程序设计笔记：
第三章：查询
1、单表查询：
1）、简单查询 查所有列：
格式：select * from 表名;
举例：
mysql> select * from tb_student;
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 5 | 201338947 | 李天 | 女 | 1984-07-22 | 山西太原 | 汉 | 4班 | 1423377766656789 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
| 7 | 201323462 | 刘泽 | 男 | 1997-09-22 | 山西太原 | 汉 | 2班 | 1423374573456789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
8 rows in set (0.00 sec)
2）、根据列查：
格式：select 列1,列2,列3.... from 表名;
测试：
mysql> select studentName,sex,classNo from tb_student;
+-------------+------+---------+
| studentName | sex | classNo |
+-------------+------+---------+
| 张骞 | 男 | 3班 |
| 王博 | 男 | 2班 |
| 黎明 | 女 | 1班 |
| 李强 | 男 | 5班 |
| 李天 | 女 | 4班 |
| 刘强 | 男 | 3班 |
| 刘泽 | 男 | 2班 |
| 刘慧 | 女 | 1班 |
+-------------+------+---------+
8 rows in set (0.00 sec)
3）、去重查询：distinct
格式：select distinct(列名) from 表名;
测试：
mysql> select distinct(classNo) from tb_student;
+---------+
| classNo |
+---------+
| 3班 |
| 2班 |
| 1班 |
| 5班 |
| 4班 |
+---------+
5 rows in set (0.03 sec)
4)、年计算查询：(年龄 为自定义别名)
mysql> select year(now())-year(birthday) '年龄' from tb_student;
+-------+
| 年龄 |
+-------+
| 43 |
| 42 |
| 40 |
| 43 |
| 39 |
| 33 |
| 26 |
| 38 |
+-------+
8 rows in set (0.01 sec)
5）、自定义别名：
mysql> select studentName '姓名',sex '性别',classNo '班级' from tb_student;
+------+------+------+
| 姓名 | 性别 | 班级 |
+------+------+------+
| 张骞 | 男 | 3班 |
| 王博 | 男 | 2班 |
| 黎明 | 女 | 1班 |
| 李强 | 男 | 5班 |
| 李天 | 女 | 4班 |
| 刘强 | 男 | 3班 |
| 刘泽 | 男 | 2班 |
| 刘慧 | 女 | 1班 |
+------+------+------+
8 rows in set (0.00 sec)
2、查询条件：（筛选、数据选择、过滤数据）
格式 ：select 列 from 表名 where 条件;
常用条件：
——————————————————————————————————————————————————
查询条件
比较： =，<>，!=，<，<=，>，>=，!<，!>，not 和比较运算式
集合： in, not in
字符串：like ,not like
空值： is null, is not null
多重： and ,or
——————————————————————————————————————————————————
1）、查询1班同学：
测试：
select studentName '姓名',sex '性别',classNo '班级' from tb_student where classNo='1班';
+------+------+------+
| 姓名 | 性别 | 班级 |
+------+------+------+
| 黎明 | 女 | 1班 |
| 刘慧 | 女 | 1班 |
+------+------+------+
2 rows in set (0.03 sec)
2）、查合格分,90分合格：
mysql> select * from tb_score where score>=90;
+----+-----------+----------+------+-------+
| id | studentNo | courseNo | term | score |
+----+-----------+----------+------+-------+
| 2 | 201348589 | 9311 | 1 | 96 |
| 8 | 201323457 | 9312 | 4 | 95 |
+----+-----------+----------+------+-------+
2 rows in set (0.00 sec)
mysql>
3）、查询不是1班的同学：
select studentName '姓名',sex '性别',classNo '班级' from tb_student where classNo!='1班';
+------+------+------+
| 姓名 | 性别 | 班级 |
+------+------+------+
| 张骞 | 男 | 3班 |
| 王博 | 男 | 2班 |
| 李强 | 男 | 5班 |
| 李天 | 女 | 4班 |
| 刘强 | 男 | 3班 |
| 刘泽 | 男 | 2班 |
+------+------+------+
6 rows in set (0.00 sec)
4)、带between条件的查询，出生1981到1983年在之间的同学：
mysql> select * from tb_student where birthday between '1981-01-01' and '1983-12-30';
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
2 rows in set (0.00 sec)
5)、查询出生1981到1983年在之间的同学：
mysql> select * from tb_student where birthday >= '1981-01-01' and birthday <'1983-12-30';
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
2 rows in set (0.00 sec)
6)、用in查询1班和2班的同学：
mysql> select * from tb_student where classNo in('1班','2班');
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
| 7 | 201323462 | 刘泽 | 男 | 1997-09-22 | 山西太原 | 汉 | 2班 | 1423374573456789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
4 rows in set (0.00 sec)
7)、查不在1班和2班的同学：
mysql> select * from tb_student where classNo not in('1班','2班');
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 5 | 201338947 | 李天 | 女 | 1984-07-22 | 山西太原 | 汉 | 4班 | 1423377766656789 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
4 rows in set (0.00 sec)
8)、查询刘姓同学：
mysql> select * from tb_student where studentName like '刘%';
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
| 7 | 201323462 | 刘泽 | 男 | 1997-09-22 | 山西太原 | 汉 | 2班 | 1423374573456789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+--------+---------+-------------------+
3 rows in set (0.00 sec)
9)、查询不姓刘的同学：
mysql> select * from tb_student where studentName not like '刘%';
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 5 | 201338947 | 李天 | 女 | 1984-07-22 | 山西太原 | 汉 | 4班 | 1423377766656789 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
5 rows in set (0.00 sec)
10）、查询姓名中含有强的同学：
mysql> select * from tb_student where studentName like '%强%';
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
2 rows in set (0.00 sec)
11）、下划线转义查询，需要加 escape '#'
测试：
查询含别名的班级：
mysql> select * from tb_student where classNo like '%#_%' escape '#';
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
| 9 | 201328864 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 14233723414556789 |
| 10 | 201324565 | 天一 | 南 | 1985-01-22 | 山西大同 | 汉 | 1班_火箭 | 14233723414556789 |
| 11 | 201323772 | 刘泽好 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_加强 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
3 rows in set (0.00 sec)
12）、用 regexp查询 1班和3班的学生：
mysql> select * from tb_student where classNo regexp '1班|3班';
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
| 9 | 201328864 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 14233723414556789 |
| 10 | 201324565 | 天一 | 南 | 1985-01-22 | 山西大同 | 汉 | 1班_火箭 | 14233723414556789 |
| 11 | 201323772 | 刘泽好 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_加强 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+--------+----------+-------------------+
7 rows in set (0.02 sec)
13）、查询缺少课程的信息：
mysql> select * from tb_class where classNo is null;
+----+-----------+---------+-----------+------------+------------+-------+----------+
| id | studentNo | classNo | className | department | enrollTime | grade | classNum |
+----+-----------+---------+-----------+------------+------------+-------+----------+
| 13 | | NULL | | | 1999-09-01 | 3 | 56 |
+----+-----------+---------+-----------+------------+------------+-------+----------+
1 row in set (0.00 sec)
14）、查询是傣族并且是男性同学：
mysql> select * from tb_student where nation='傣族' and sex='男';
+----+-----------+-------------+------+------------+--------+--------+----------+---------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+--------+--------+----------+---------------+
| 13 | 201385 | 天一 | 男 | 1985-01-22 | 运城 | 傣族 | 1班_火箭 | 1423374456789 |
| 19 | 201385 | 张氏一 | 男 | 1981-01-22 | 运城 | 傣族 | 2班 | 1423374456789 |
| 22 | 201555 | 张通透 | 男 | 1987-01-22 | 临淄 | 傣族 | 5班 | 14233723334 |
+----+-----------+-------------+------+------------+--------+--------+----------+---------------+
3 rows in set (0.00 sec)
15)、查询是傣族或者是山西侯马的同学：
mysql> select * from tb_student where nation='傣族' or native='山西侯马';
+----+-----------+-------------+------+------------+----------+--------+----------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+----------+------------------+
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 13 | 201385 | 天一 | 男 | 1985-01-22 | 运城 | 傣族 | 1班_火箭 | 1423374456789 |
| 19 | 201385 | 张氏一 | 男 | 1981-01-22 | 运城 | 傣族 | 2班 | 1423374456789 |
| 20 | 201399 | 柳依依 | 女 | 1985-01-22 | 太原 | 傣族 | 3班 | 1423456289 |
| 22 | 201555 | 张通透 | 男 | 1987-01-22 | 临淄 | 傣族 | 5班 | 14233723334 |
+----+-----------+-------------+------+------------+----------+--------+----------+------------------+
6 rows in set (0.00 sec)
16)、查询是傣族或者是山西侯马的女性同学的姓名、民族、城市：
mysql> select studentName,nation,native from tb_student where sex='女' and (native='山西侯马' or nation='傣族');
+-------------+--------+--------+
| studentName | nation | native |
+-------------+--------+--------+
| 柳依依 | 傣族 | 太原 |
+-------------+--------+--------+
1 row in set (0.00 sec)
3、排序查询：查询sql最后加：order by 列名;
1）、按姓名升序：
mysql> select * from tb_student order by studentName;
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
| 9 | 201328864 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 14233723414556789 |
| 12 | 201664 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 1423372333344 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
| 21 | 201664 | 刘明辉 | 女 | 1989-01-22 | 临汾 | 维吾尔族 | 4班 | 1423367789 |
| 7 | 201323462 | 刘泽 | 男 | 1997-09-22 | 山西太原 | 汉 | 2班 | 1423374573456789 |
| 11 | 201323772 | 刘泽好 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_加强 | 14233723414556789 |
| 13 | 201385 | 天一 | 男 | 1985-01-22 | 运城 | 傣族 | 1班_火箭 | 1423374456789 |
| 10 | 201324565 | 天一 | 南 | 1985-01-22 | 山西大同 | 汉 | 1班_火箭 | 14233723414556789 |
| 19 | 201385 | 张氏一 | 男 | 1981-01-22 | 运城 | 傣族 | 2班 | 1423374456789 |
| 23 | 201442 | 张泽好 | 女 | 1988-01-22 | 晋中 | 汉 | 6班 | 14233723449 |
| 22 | 201555 | 张通透 | 男 | 1987-01-22 | 临淄 | 傣族 | 5班 | 14233723334 |
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 5 | 201338947 | 李天 | 女 | 1984-07-22 | 山西太原 | 汉 | 4班 | 1423377766656789 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 20 | 201399 | 柳依依 | 女 | 1985-01-22 | 太原 | 傣族 | 3班 | 1423456289 |
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 18 | 201664 | 王梦 | 女 | 1980-01-22 | 山西大同 | 汉 | 1班 | 1423372333344 |
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
19 rows in set (0.05 sec)
2）、按姓名降序：最后加：desc;
select * from tb_student order by studentName desc;
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
| 3 | 201345232 | 黎明 | 女 | 1983-11-22 | 山西太原 | 汉 | 1班 | 142337776669966 |
| 18 | 201664 | 王梦 | 女 | 1980-01-22 | 山西大同 | 汉 | 1班 | 1423372333344 |
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 20 | 201399 | 柳依依 | 女 | 1985-01-22 | 太原 | 傣族 | 3班 | 1423456289 |
| 4 | 201357356 | 李强 | 男 | 1980-03-22 | 山西侯马 | 汉 | 5班 | 1423379476543589 |
| 5 | 201338947 | 李天 | 女 | 1984-07-22 | 山西太原 | 汉 | 4班 | 1423377766656789 |
| 1 | 201338941 | 张骞 | 男 | 1980-01-22 | 山西侯马 | 汉 | 3班 | 1423373457656789 |
| 22 | 201555 | 张通透 | 男 | 1987-01-22 | 临淄 | 傣族 | 5班 | 14233723334 |
| 23 | 201442 | 张泽好 | 女 | 1988-01-22 | 晋中 | 汉 | 6班 | 14233723449 |
| 19 | 201385 | 张氏一 | 男 | 1981-01-22 | 运城 | 傣族 | 2班 | 1423374456789 |
| 10 | 201324565 | 天一 | 南 | 1985-01-22 | 山西大同 | 汉 | 1班_火箭 | 14233723414556789 |
| 13 | 201385 | 天一 | 男 | 1985-01-22 | 运城 | 傣族 | 1班_火箭 | 1423374456789 |
| 11 | 201323772 | 刘泽好 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_加强 | 14233723414556789 |
| 7 | 201323462 | 刘泽 | 男 | 1997-09-22 | 山西太原 | 汉 | 2班 | 1423374573456789 |
| 21 | 201664 | 刘明辉 | 女 | 1989-01-22 | 临汾 | 维吾尔族 | 4班 | 1423367789 |
| 8 | 201323457 | 刘慧 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班 | 14233723414556789 |
| 6 | 201323145 | 刘强 | 男 | 1990-04-22 | 山西应县 | 汉 | 3班 | 142337346246789 |
| 12 | 201664 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 1423372333344 |
| 9 | 201328864 | 刘名义 | 女 | 1985-01-22 | 山西大同 | 汉 | 1班_超越 | 14233723414556789 |
+----+-----------+-------------+------+------------+----------+----------+----------+-------------------+
19 rows in set (0.00 sec)
3）、按姓名降序后，从第二条数据开始查，查询三条数据：
mysql> select * from tb_student order by studentName desc limit 1,3;
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| id | studentNo | studentName | sex | birthday | native | nation | classNo | studentID |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
| 18 | 201664 | 王梦 | 女 | 1980-01-22 | 山西大同 | 汉 | 1班 | 1423372333344 |
| 2 | 201348589 | 王博 | 男 | 1981-01-22 | 山西大同 | 汉 | 2班 | 1423377764456632 |
| 20 | 201399 | 柳依依 | 女 | 1985-01-22 | 太原 | 傣族 | 3班 | 1423456289 |
+----+-----------+-------------+------+------------+----------+--------+---------+------------------+
3 rows in set (0.01 sec)
4、聚合函数查询：
1）、常用聚合函数表：
统计表记录数：count(*)
统计列值个数：count(列)
求列值最大值：max(列)
求列值最小值：min(列)
求列总和： sum(列)
求列中平均值：avg(列)
2）、查询班级人数：
mysql> select count(*) num from tb_student;
+-----+
| num |
+-----+
| 17 |
+-----+
3）、查询选课的总人数：
mysql> select count(distinct(studentNo)) from tb_score;
+----------------------------+
| count(distinct(studentNo)) |
+----------------------------+
| 8 |
+----------------------------+
1 row in set (0.00 sec)
4）、查询编号为：9311的平均成绩：
mysql> select avg(score) from tb_score where courseNo='9311';
+------------+
| avg(score) |
+------------+
| 78.5000 |
+------------+
1 row in set (0.00 sec)
5）、查询课程编号为 9311的最高成绩：
select max(score) from tb_score where courseNo='9311';
+------------+
| max(score) |
+------------+
| 96 |
+------------+
1 row in set (0.00 sec)
5、分组聚合函数查询：格式：group by 列名; （必要条件：其中列名不能是函数表达式）
1）、仅分组，无筛选条件查询：（返回每组的第一条数据组成的信息）
select * from tb_score group by ;
（ 注意：此查询无意义，一般不这样写，一般加入筛选条件或函数）
对：返回每组的第一条数据组成的信息 啥意思？解释如下：
先看看有哪些数据：
mysql> select * from tb_score order by studentNo;
+----+-----------+----------+------+-------+
| id | studentNo | courseNo | term | score |
+----+-----------+----------+------+-------+
| 6 | 201323145 | 9334 | 2 | 69 |
| 10 | 201323145 | 9334 | 8 | 69 |
| 8 | 201323457 | 9312 | 4 | 95 |
| 12 | 201323457 | 9312 | 10 | 55 |
| 7 | 201323462 | 9362 | 3 | 88 |
| 11 | 201323462 | 9362 | 9 | 58 |
| 1 | 201338941 | 9397 | 2 | 70 |
| 5 | 201338947 | 9322 | 3 | 77 |
| 9 | 201338947 | 9322 | 7 | 67 |
| 3 | 201345232 | 9323 | 3 | 84 |
| 2 | 201348589 | 9311 | 1 | 96 |
| 4 | 201357356 | 9311 | 4 | 61 |
+----+-----------+----------+------+-------+
12 rows in set (0.00 sec)
先想一想：可以看到：若按studentNo分组：得到：
id为6、10的为一组，
id为8、12为一组，
id为7、11的为一组，
id为5、9的为一组，
剩余不同的各一组。
共计8组，每组返回一条数据，共8条数据；
id列值依次为：6、8、7、1、5、3、2、4
那么根据分组查：
mysql> select row() NO, * from tb_score group by studentNo;
+----+-----------+----------+------+-------+
| id | studentNo | courseNo | term | score |
+----+-----------+----------+------+-------+
| 6 | 201323145 | 9334 | 2 | 69 |
| 8 | 201323457 | 9312 | 4 | 95 |
| 7 | 201323462 | 9362 | 3 | 88 |
| 1 | 201338941 | 9397 | 2 | 70 |
| 5 | 201338947 | 9322 | 3 | 77 |
| 3 | 201345232 | 9323 | 3 | 84 |
| 2 | 201348589 | 9311 | 1 | 96 |
| 4 | 201357356 | 9311 | 4 | 61 |
+----+-----------+----------+------+-------+
8 rows in set (0.00 sec)
是不是和想的一样？
现在已经明白了吧。
2）、查询每个学生的平均分、最高分、选课数：
mysql> select studentNo,avg(score),max(score),count(courseNo) from tb_score group by studentNo;
+-----------+------------+------------+-----------------+
| studentNo | avg(score) | max(score) | count(courseNo) |
+-----------+------------+------------+-----------------+
| 201323145 | 69.0000 | 69 | 2 |
| 201323457 | 75.0000 | 95 | 2 |
| 201323462 | 73.0000 | 88 | 2 |
| 201338941 | 70.0000 | 70 | 1 |
| 201338947 | 72.0000 | 77 | 2 |
| 201345232 | 84.0000 | 84 | 1 |
| 201348589 | 96.0000 | 96 | 1 |
| 201357356 | 61.0000 | 61 | 1 |
+-----------+------------+------------+-----------------+
8 rows in set (0.01 sec)
其中：看其中一组数据：
8 | 201323457 | 9312 | 4 | 95 |
12 | 201323457 | 9312 | 10 | 55 |
平均值(95+55)/2=75;最大值：95;课程数 2;
由此可见，查询的是对的。
3）、having （1、分组后还要加条件用having，2、若没有group by,会把所有数据分一个组。）
分组后加条件：查询平均分在75分及以上的每个学生的平均分、最高分、选课数：
mysql> select studentNo,avg(score),max(score),count(courseNo) from tb_score group by studentNo having avg(score)>=75;
+-----------+------------+------------+-----------------+
| studentNo | avg(score) | max(score) | count(courseNo) |
+-----------+------------+------------+-----------------+
| 201323457 | 75.0000 | 95 | 2 |
| 201345232 | 84.0000 | 84 | 1 |
| 201348589 | 96.0000 | 96 | 1 |
+-----------+------------+------------+-----------------+
3 rows in set (0.00 sec)
查询大于70分的平均成绩：（若没有group by,会把所有数据分一个组。）
mysql> select avg(score) from tb_score having avg(score)>=70;
+------------+
| avg(score) |
+------------+
| 74.0833 |
+------------+
1 row in set (0.00 sec)
6、连接查询：
inner join：查询关联匹配的所有数据；
left join: 先查询左表，其他表的列没有显示null;
right join: 先查询右表，其他表的列没有显示null;
1）、交叉查询：
格式：select a.列1,a.列2,b.列1,b.列2 .... from 表1 a,表2名称 b;
(a、b别称可以自定义。)
查询每个学生选修课程：
select tb_student.*,tb_score.*
from tb_student,tb_score
where tb_student.studentNo=tb_score.studentNo;
2)、内连接 ：格式： 表1 inner join 表2 on 条件
查询每个学生选修课程：
select tb_student.*,tb_score.*
from tb_student inner join tb_score on
tb_student.studentNo=tb_score.studentNo;
3）、查成绩大于60分的有哪些同学？
select a.studentName
from tb_student a inner join tb_score b
on a.studentNo=b.studentNo and b.score>60;
+-------------+
| studentName |
+-------------+
| 张骞 |
| 王博 |
| 黎明 |
| 李强 |
| 李天 |
| 李天 |
| 刘强 |
| 刘强 |
| 刘泽 |
| 刘慧 |
+-------------+
10 rows in set (0.00 sec)
(实际工作中经常带有别名进行查询)
4）、left join 左连接查询：
select a.studentName,b.score
from tb_student a left join tb_score b
on a.studentNo=b.studentNo;
+-------------+-------+
| studentName | score |
+-------------+-------+
| 张骞 | 70 |
| 王博 | 96 |
| 黎明 | 84 |
| 李强 | 61 |
| 李天 | 77 |
| 刘强 | 69 |
| 刘泽 | 88 |
| 刘慧 | 95 |
| 李天 | 67 |
| 刘强 | 69 |
| 刘泽 | 58 |
| 刘慧 | 55 |
| 刘名义 | NULL |
| 天一 | NULL |
| 刘泽好 | NULL |
| 王梦 | NULL |
| 张氏一 | NULL |
| 柳依依 | NULL |
| 刘明辉 | NULL |
| 张通透 | NULL |
| 张泽好 | NULL |
+-------------+-------+
21 rows in set (0.00 sec)
（成绩表中没有关联的成绩信息，显示null）
5）、right join 右连接查询：
先加入2条数据：
insert into tb_score(studentNo,courseNo,term,score)values('201300462','93001','14','88');
insert into tb_score(studentNo,courseNo,term,score)values('201300457','93002','102','45');
查询：
select a.studentName,b.score
from tb_student a right join tb_score b
on a.studentNo=b.studentNo;
+-------------+-------+
| studentName | score |
+-------------+-------+
| 张骞 | 70 |
| 王博 | 96 |
| 黎明 | 84 |
| 李强 | 61 |
| 李天 | 77 |
| 李天 | 67 |
| 刘强 | 69 |
| 刘强 | 69 |
| 刘泽 | 88 |
| 刘泽 | 58 |
| 刘慧 | 95 |
| 刘慧 | 55 |
| NULL | 88 |
| NULL | 45 |
+-------------+-------+
14 rows in set (0.00 sec)
（学生表中没有关联的学生编号，就会显示为null）
浙公网安备 33010602011771号