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

待我称王封你为后i 2021-09-23 20:44 482阅读 0赞

**– 1.查询student表中所有的记录**`SELECT * FROM student;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]  
**– 2.查询student表中所有记录的s\_name,s\_sex和s\_class列**

SELECT s_no,s_name,s_class FROM  student;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]  
**– 3.查询教师所有的单位但是不重复的t\_depart列**

SELECT distinct (t_depart) FROM teacher;

![在这里插入图片描述][20200130172821443.png]

**– 4.查询score表中成绩在60-80之间所有的记录(sc\_degree)**

SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;
    SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ;

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

**– 5.查询score表中成绩为85, 86, 或者88的记录(sc\_degree)**

SELECT * FROM score WHERE sc_degree IN(85, 86, 88);

![在这里插入图片描述][20200130180439229.png]  
**– 6.查询student表中’95031’班或者性别为’女’的同学记录**

SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';

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

**– 7.以class降序查询student表中所有的记录**

SELECT * FROM student ORDER BY s_class desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]  
**– 8.以c\_no升序.sc\_degree降序插叙score表中所有的数据先以c\_no进行升序,若c\_no相同,则以sc\_degree降序**

SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;

**– 9.查询’95031’班的学生人数**

SELECT COUNT(s_no) FROM student WHERE s_class = '95031';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]  
**–10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)**

**子查询:**

--查询步骤
    1.先找到最高分
    最高分: SELECT MAX(sc_degree) FROM score;
    2.通过我们找到的最高分的分数来从score中找到我们需要的学生号和课程号
    SELECT c_no, s_no FROM score WHERE sc_degree = (最高分)
    最终:
    SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);

**排序:**

排序 最高分有多个的情况下可能有数据问题
    limit x,y  (x:表示从X条数据开始  y:需要查出多少条)
    SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;这个OK
    但是我们再插入一条数据:
    INSERT INTO score VALUES('101','9-888','92');
    再用排序法去查得到:
    +-------+------+
    | c_no  | s_no |
    +-------+------+
    | 9-888 | 101  |
    +-------+------+
    有两条数据但是只显示一条,有问题

![在这里插入图片描述][20200130191023363.png]  
**– 11.查询每门课的平均成绩**

SELECT c_no,AVG(sc_degree) FROM score GROUP BY c_no;

![在这里插入图片描述][2020013019121510.png]  
**– 12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分**

SELECT AVG(sc_degree),c_no  from score GROUP BY c_no HAVING COUNT(c_no)  >= 2 AND c_no like '3%'  ;

![在这里插入图片描述][20200130191520680.png]  
**– 13.查询分数大于70但是小于90的s\_no列:**

SELECT s_no,sc_degree  FROM score WHERE sc_degree BETWEEN 71 AND 89;

![在这里插入图片描述][20200130191249889.png]

**– 14.查询所有的学生 s\_name , c\_no, sc\_degree列**

SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]  
**– 15.查询所有学生的s\_no, c\_name, sc\_degree列**

SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;

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

**– 16.查询所有的学生 s\_name , c\_name, sc\_degree列**

SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 8]  
**– 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 9]

**– 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 10]  
**– 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 11]  
**– 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 12]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70]: /images/20210923/12c7d0aeb30a45e9bfffc9b0ef944fbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 1]: /images/20210923/73116f99b3054ad0be2df66bb292c950.png
[20200130172821443.png]: /images/20210923/9933d4fae1bf4a2db136bac79385fc81.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 2]: /images/20210923/86f9f95cdf1c47c2980273d783292f53.png
[20200130180439229.png]: /images/20210923/0f8cc6afe9f04d7d849a8c7720d38ebe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 3]: /images/20210923/eaeaffe799284217b4a59d23e77740d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 4]: /images/20210923/a24f90701ef448a3b12e62c1d9ad4c48.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 5]: /images/20210923/7c0360b8ce99489596fc33aaab20bb53.png
[20200130191023363.png]: /images/20210923/00c5ba1319b644ffbadd6d8427ee1c5c.png
[2020013019121510.png]: /images/20210923/a3b4fa4c3fcc433e92f432bda74a603f.png
[20200130191520680.png]: /images/20210923/87187ae61b76496c9af21e521e2c010c.png
[20200130191249889.png]: /images/20210923/178e7948c6db4b31ab8a8168134bf680.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 6]: /images/20210923/6a363f973d954e0ebc9b9fa401e5331e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 7]: /images/20210923/49658328845a4929971ecb26c698faa9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 8]: /images/20210923/42dbcbd6bb7e431a8559bce9307979af.png
[20200130192345628.png]: /images/20210923/38c89f363bec41a3afc41f4a26d793d1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 9]: /images/20210923/507db5f729ca468c8568def5d6703399.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 10]: /images/20210923/8b5c4873ad084b23ace828875e878040.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 11]: /images/20210923/b7fa64ed1d9f41d1904de807b9ed6d68.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjI5NTQz_size_16_color_FFFFFF_t_70 12]: /images/20210923/744578e3649b4c16b45cc30d6b90d6d7.png