[大数据]mysql查询练习2

更新表中的数据

update score set score=96 where student_number='101' and course_number='3-105';

分组计算平均成绩

查询每门课的平均成绩avg()

select avg(score)	from score where course_number='3-105';
select course_number,avg(score)	from score group by course_number;

分组条件和模糊查询

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

select course_number,avg(score),count(*) from score group by course_number 
having count(course_number)>=2 and course_number like '3%';

like 是模糊查询
%是通配符
group by 字段名
后面带的条件是having

多表查询

查询所有学生的student_name,course_number和score列
需要student和score表

select student_number,student_name from student;
select student_number,course_number,score from score;

select student_number,course_number,score from student,
score where student.student_number=score.student_number;

–查询所有学生的student_nnmber,course_name和score

三表查询

select student_number,course_name,score from student,course
score where course.course_number=score.course_number;

查询所有学生的student_name,course_name和score

三表关联查询

select student_name,course_name,score from student,course
score where student.student_number=score.student_number and course.course_number=score.course_number;

查询95031班学生每门课的平均分

mysql> select course_number,avg(score)
-> from score
-> where student_number in (select student_number from student where class='95031')
-> group by course_number;
+---------------+------------+
| course_number | avg(score) |
+---------------+------------+
| 3-105         |    86.0000 |
| 3-245         |    86.0000 |
| 6-166         |    86.0000 |
| 9-888         |    86.0000 |
+---------------+------------+

?
查询选修3-105课程的成绩高于109号同学选修3-105成绩的所有记录

mysql> select * from score 
where course_number='3-105' 
and score>(select score from score where student_number='103' and course_number='3-105');
+----------------+---------------+-------+
| student_number | course_number | score |
+----------------+---------------+-------+
| 101            | 3-105         |    96 |
+----------------+---------------+-------+
1 row in set (0.00 sec)

year函数与带in关键字的子查询；

mysql> select * from student where year(student_birthday) in ( select year(student_birthday) from student where student_number in (‘101’,‘108’));
±---------------±-------------±------------±--------------------±------+
| student_number | student_name | student_sex | student_birthday | class |
±---------------±-------------±------------±--------------------±------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 107 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 108 | 说匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 111 | 放王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±---------------±-------------±------------±--------------------±------+
6 rows in set (0.00 sec)

union 连接两个SQL语句

as 别名 将列名做别名显示

mysql> select teacher_name as name,teacher_sex as sex,teacher_birthday as birthday from teacher
    -> union
    ->  select student_name,student_sex,student_birthday from student;
+----------+-----+---------------------+
| name     | sex | birthday            |
+----------+-----+---------------------+
| 李诚     | 男  | 1958-12-02 00:00:00 |
| 王萍     | 女  | 1972-05-05 00:00:00 |
| 刘冰     | 女  | 1977-08-14 00:00:00 |
| 张旭     | 男  | 1969-03-12 00:00:00 |
| 曾华     | 男  | 1977-09-01 00:00:00 |
| 匡明     | 男  | 1975-10-02 00:00:00 |
| 王丽     | 女  | 1976-01-23 00:00:00 |
| 李军     | 男  | 1976-02-20 00:00:00 |
| 王芳     | 女  | 1975-02-10 00:00:00 |
| 陆君     | 男  | 1974-06-03 00:00:00 |
| 说匡明   | 男  | 1975-10-02 00:00:00 |
| 额李军   | 男  | 1976-02-20 00:00:00 |
| 放王芳   | 女  | 1975-02-10 00:00:00 |
| 跟陆让君 | 男  | 1974-06-03 00:00:00 |
+----------+-----+---------------------+

any 表示任意一个，eg大于其中至少一个，要大于其中的最小值

not in 不在其中

all 表示所有的关系

把表分成两个通过复制表数据进行查询

mysql> select * from score a where score<
    -> (select avg(score) from score b where a.course_number=
    -> b.course_number);
+----------------+---------------+-------+
| student_number | course_number | score |
+----------------+---------------+-------+
| 102            | 3-105         |    86 |
| 103            | 3-105         |    86 |
| 104            | 3-105         |    86 |
| 105            | 3-105         |    86 |
| 106            | 3-105         |    86 |
+----------------+---------------+-------+
5 rows in set (0.00 sec)



mysql> select class from student where student_sex='男'
    -> group by class
    -> having count(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.00 sec)

notlike 模糊查询取反
select * from student where student_name notlike '%王'

查询student表中每个学生的姓名和年龄