数据库(Mysql)----mysql查询练习(3、实操)

素颜马尾好姑娘i 2021-09-23 20:48 421阅读 0赞

**– 17.查询班级是’95031’班学生每门课的平均分**

select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;
    
    select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;
    
    select sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;

![在这里插入图片描述][20200130192345628.png]  
**进阶,加入课程名称:**

SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c  WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]

**– 18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录**

SELECT * FROM student AS s, score AS sc WHERE sc.c_no = '3-105' AND sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]  
**– 19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录**

SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]  
**– 20.查询所有学号为108.101的同学同年出生的所有学生的s\_no,s\_name和s\_birthday**

SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]

**– 21.查询 张旭 教师任课的学生的成绩**

select * from student where s_no IN (SELECT s_no FROM score WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name='张旭')));

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]  
**– 22.查询选修课程的同学人数多余 5 人的教师姓名**

SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(s_no) > 5));

Empty set (0.00 sec) 没有符合条件的  
为了效果,添加数据:

INSERT INTO score VALUES('101','3-105','90');
    INSERT INTO score VALUES('102','3-105','91');
    INSERT INTO score VALUES('104','3-105','89');

再次查询可得:  
![在这里插入图片描述][20200130200849701.png]  
**– 23.查询student 表中 不姓"王"的同学的记录**

SELECT * FROM student WHERE s_name NOT LIKE '王%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]  
**– 24. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)**

SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]  
**– 25. 查询student中最大和最小的 s\_birthday的值**

SELECT MAX(s_birthday),MIN(s_birthday) FROM student;

![在这里插入图片描述][20200130201301153.png]  
**– 26. 查询和"李军"同性别的所有同学的s\_name**

SELECT s_name, s_sex FROM student WHERE s_SEX = (SELECT s_sex FROM student WHERE s_name = '李军');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 7]  
**– 27. 查询所有选修’计算机导论’课程的’男’同学的成绩表**

SELECT * FROM score WHERE c_no = (SELECT c_no FROM course WHERE c_name = '计算机导论' ) AND s_no IN(SELECT s_no FROM student WHERE s_sex = '男');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 8]

[20200130192345628.png]: /images/20210923/2100a6f531924f7da95a5aaebc589e62.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]: /images/20210923/12dcf616b5f74242ac9571f050e40370.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]: /images/20210923/8e7358662a9e4aaf919fa20b83a5c911.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]: /images/20210923/482496f95df7452cbe1efc7bb4d9b59d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]: /images/20210923/8c81b054258d4817bb2d7914c90c916c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]: /images/20210923/747937a266a746a8ac40b787cb7f1e6b.png
[20200130200849701.png]: /images/20210923/481b673cabc64a539d652faca64a53ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]: /images/20210923/71e15829a1d94a4ab92f85bfee3317e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]: /images/20210923/b57865564b3c45ad8a5f27b634266b6d.png
[20200130201301153.png]: /images/20210923/676ed52273604382b313ed8b54789c29.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 7]: /images/20210923/a740e9b1c5e84dff834661f8ded64e32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 8]: /images/20210923/c5b04422710643b097d082bba431efb1.png