数据库查询练习-简单查询练习及笔记(2)

数据库查询练习-简单查询练习及笔记(2)

1 分组计算平均成绩

查询每门课的平均成绩;

select avg(degree) from score where cno='3-105';
#select avg(要求平均分的字段名称) from 表名 where 根据那个字段求值='字段值';
/*
mysql> select avg(degree) from score where cno='3-105';
+-------------+
| avg(degree) |
+-------------+
| 85.3333     |
+-------------+
1 row in set (0.02 sec)
*/

如果数据量大 ,一次一次写比较麻烦,所以用到分组;

select  avg  (degree) from score group by cno;
#select  avg  (要求平均分的字段名称) from 表名 group by 根据那个字段来求;
/*
mysql> select  avg  (degree) from score group by cno;
+---------------+
| avg  (degree) |
+---------------+
| 85.3333       |
| 76.3333       |
| 81.6667       |
+---------------+
3 rows in set (0.02 sec)

mysql> select cno, avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 | 85.3333     |
| 3-245 | 76.3333     |
| 6-166 | 81.6667     |
+-------+-------------+
3 rows in set (0.02 sec)
*/

2 分组条件与模糊查询

查询 score 表中至少有 2 名学生选修，并以 3 开头的课程的平均分数。

select cno from score group by cno having count(*)>=2 and  cno like '3%';
#select 要查询的字段 from 在那张表中查询 group by 以哪个字段来分组 having count(*)>=条件 and  要模糊查询的字段 like '模糊查询的字符%';
#    %表示模糊查询通配符;
/*
mysql> select cno from score group by cno having count(*)>=2 and  cno like '3%';
+-------+
| cno   |
+-------+
| 3-105 |
| 3-245 |
+-------+
2 rows in set (0.03 sec)
*/

3 模糊查询

通配符:

_表示任意一个字母 定长

%表示多个字母 不定长

select * from  student where sbirthday like  '1974%';
#select 那一个字段*表示所有 from  查那个表 where 模糊查询的字段 like  '模糊查询的值%';
/*
mysql> select * from  student where sbirthday like  '1974%';
+-----+--------+------+---------------------+--------+
| sno | sname  | ssex | sbirthday           | sclass |
+-----+--------+------+---------------------+--------+
| 106 | 陆军   | 男   | 1974-06-03 00:00:00 | 95031  |
| 109 | 赵铁柱 | 男   | 1974-06-03 00:00:00 | 95031  |
+-----+--------+------+---------------------+--------+
2 rows in set (0.04 sec)
*/