MySQL单表操作与多表查询练习

最新推荐文章于 2023-01-12 02:34:43 发布
最新推荐文章于 2023-01-12 02:34:43 发布
阅读量300 收藏
点赞数
分类专栏： Linux运维 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/iboyish/article/details/108875428
版权

SQL查询 数据分组 平均年龄 成绩筛选 课程关联
关键词由CSDN通过智能技术生成

有hellodb.students表如下:

MariaDB [hellodb]> select * from students;

+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
MariaDB [hellodb]> select name,age from students  where age>25 and gender = 'M';

+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
7 rows in set (0.000 sec)
2. 以ClassID为分组依据，显示每组的平均年龄
MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄 from students group by classid;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|   NULL |      63.5000 |
|      1 |      20.5000 |
|      2 |      36.0000 |
|      3 |      20.2500 |
|      4 |      24.7500 |
|      5 |      46.0000 |
|      6 |      20.7500 |
|      7 |      19.6667 |
+--------+--------------+
8 rows in set (0.000 sec)


MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄 from students where classid is not null group by classid;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      1 |      20.5000 |
|      2 |      36.0000 |
|      3 |      20.2500 |
|      4 |      24.7500 |
|      5 |      46.0000 |
|      6 |      20.7500 |
|      7 |      19.6667 |
+--------+--------------+
7 rows in set (0.000 sec)

MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄 from students group by classid having classid is not null;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      1 |      20.5000 |
|      2 |      36.0000 |
|      3 |      20.2500 |
|      4 |      24.7500 |
|      5 |      46.0000 |
|      6 |      20.7500 |
|      7 |      19.6667 |
+--------+--------------+
7 rows in set (0.000 sec)
3. 显示第2题中平均年龄大于30的分组及平均年龄
MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄 from students where classid is not null group by classid having 平均年龄 > 30;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      2 |      36.0000 |
|      5 |      46.0000 |
+--------+--------------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄 from students group by classid having classid is not null and 平均年龄 > 30;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      2 |      36.0000 |
|      5 |      46.0000 |
+--------+--------------+
2 rows in set (0.001 sec)
4. 显示以L开头的名字的同学的信息
MariaDB [hellodb]> select * from students where name like  'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.000 sec)
5. 显示TeacherID非空的同学的相关信息
MariaDB [hellodb]> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set (0.000 sec)
6. 以年龄排序后，显示年龄最大的前10位同学的信息
MariaDB [hellodb]> select * from students group by age desc limit 10;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

# sqlserver 中使用top 函数
select top 10 * from students order by age desc;
7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
MariaDB [hellodb]> select * from students where age >= 20 and age <= 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)



MariaDB [hellodb]> select * from students where age between 20 and 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
10 rows in set (0.001 sec)
8. 以ClassID分组，显示每班的同学的人数
MariaDB [hellodb]> select classid 班级,count(stuid) 人数 from students group by classid; 
+--------+--------+
| 班级   | 人数   |
+--------+--------+
|   NULL |      2 |
|      1 |      4 |
|      2 |      3 |
|      3 |      4 |
|      4 |      4 |
|      5 |      1 |
|      6 |      4 |
|      7 |      3 |
+--------+--------+
8 rows in set (0.000 sec)


MariaDB [hellodb]> select classid 班级,count(*) 人数 from students where classid is not null group by classid; 

+--------+--------+
| 班级   | 人数   |
+--------+--------+
|      1 |      4 |
|      2 |      3 |
|      3 |      4 |
|      4 |      4 |
|      5 |      1 |
|      6 |      4 |
|      7 |      3 |
+--------+--------+
7 rows in set (0.000 sec)
9. 以Gender分组，显示其年龄之和
MariaDB [hellodb]> select gender 性别,sum(age) from students group by gender;
+--------+----------+
| 性别   | sum(age) |
+--------+----------+
| F      |      190 |
| M      |      495 |
+--------+----------+
2 rows in set (0.001 sec)
10. 以ClassID分组，显示其平均年龄大于25的班级
MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄  from students group by classid having avg(age) > 25;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|   NULL |      63.5000 |
|      2 |      36.0000 |
|      5 |      46.0000 |
+--------+--------------+
3 rows in set (0.001 sec)

MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄  from students where classid is not null group by classid  having avg(age) > 25;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      2 |      36.0000 |
|      5 |      46.0000 |
+--------+--------------+
2 rows in set (0.001 sec)

MariaDB [hellodb]> select classid 班级,avg(age) 平均年龄  from students group by classid having avg(age) > 25 and classid is not null;

+--------+--------------+
| 班级   | 平均年龄     |
+--------+--------------+
|      2 |      36.0000 |
|      5 |      46.0000 |
+--------+--------------+
2 rows in set (0.001 sec)
11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
MariaDB [hellodb]> select gender,sum(age) over25_sum_age from students where age > 25 group by gender;

+--------+----------------+
| gender | over25_sum_age |
+--------+----------------+
| M      |            317 |
+--------+----------------+
1 row in set (0.002 sec)
12. 显示前5位同学的姓名、课程及成绩
课程表：courses

MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course         |
+----------+----------------+
|        1 | Hamo Gong      |
|        2 | Kuihua Baodian |
|        3 | Jinshe Jianfa  |
|        4 | Taiji Quan     |
|        5 | Daiyu Zanghua  |
|        6 | Weituo Zhang   |
|        7 | Dagou Bangfa   |
+----------+----------------+
7 rows in set (0.001 sec)


考试成绩表：scores

MariaDB [hellodb]> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
|  1 |     1 |        2 |    77 |
|  2 |     1 |        6 |    93 |
|  3 |     2 |        2 |    47 |
|  4 |     2 |        5 |    97 |
|  5 |     3 |        2 |    88 |
|  6 |     3 |        6 |    75 |
|  7 |     4 |        5 |    71 |
|  8 |     4 |        2 |    89 |
|  9 |     5 |        1 |    39 |
| 10 |     5 |        7 |    63 |
| 11 |     6 |        1 |    96 |
| 12 |     7 |        1 |    86 |
| 13 |     7 |        7 |    83 |
| 14 |     8 |        4 |    57 |
| 15 |     8 |        3 |    93 |
+----+-------+----------+-------+
15 rows in set (0.001 sec)

显示前5位同学的姓名、课程及成绩：

MariaDB [hellodb]> select name 姓名,course 课程,score 考试成绩  from students st
    -> inner join scores sc on st.stuid=sc.stuid 
    -> inner join courses co on sc.courseid=co.courseid
    -> limit 5;
+-------------+----------------+--------------+
| 姓名        | 课程           | 考试成绩     |
+-------------+----------------+--------------+
| Shi Zhongyu | Kuihua Baodian |           77 |
| Shi Zhongyu | Weituo Zhang   |           93 |
| Shi Potian  | Kuihua Baodian |           47 |
| Shi Potian  | Daiyu Zanghua  |           97 |
| Xie Yanke   | Kuihua Baodian |           88 |
+-------------+----------------+--------------+
5 rows in set (0.000 sec)

显示考试成绩前5位同学的姓名、课程及成绩：

MariaDB [hellodb]> select name 姓名,course 课程,score 考试成绩  from students st
    -> inner join scores sc on st.stuid=sc.stuid 
    -> inner join courses co on sc.courseid=co.courseid
    -> order by score desc limit 5;
+-------------+----------------+--------------+
| 姓名        | 课程           | 考试成绩     |
+-------------+----------------+--------------+
| Shi Potian  | Daiyu Zanghua  |           97 |
| Shi Qing    | Hamo Gong      |           96 |
| Lin Daiyu   | Jinshe Jianfa  |           93 |
| Shi Zhongyu | Weituo Zhang   |           93 |
| Ding Dian   | Kuihua Baodian |           89 |
+-------------+----------------+--------------+
5 rows in set (0.002 sec)
13. 显示其成绩高于80的同学的名称及课程
MariaDB [hellodb]>  select name 姓名,course 课程,score 考试成绩  from students st 
    -> inner join scores sc on st.stuid=sc.stuid
    -> inner join courses co on sc.courseid=co.courseid
    -> where score > 80;


+-------------+----------------+--------------+
| 姓名        | 课程           | 考试成绩     |
+-------------+----------------+--------------+
| Shi Zhongyu | Weituo Zhang   |           93 |
| Shi Potian  | Daiyu Zanghua  |           97 |
| Xie Yanke   | Kuihua Baodian |           88 |
| Ding Dian   | Kuihua Baodian |           89 |
| Shi Qing    | Hamo Gong      |           96 |
| Xi Ren      | Hamo Gong      |           86 |
| Xi Ren      | Dagou Bangfa   |           83 |
| Lin Daiyu   | Jinshe Jianfa  |           93 |
+-------------+----------------+--------------+
8 rows in set (0.001 sec)

#另一种方法：
MariaDB [hellodb]> select name 姓名,course 课程,score 考试成绩  from students,scores,courses
    -> where students.stuid=scores.stuid and scores.courseid=courses.courseid and score>80;
 

+-------------+----------------+--------------+
| 姓名        | 课程           | 考试成绩     |
+-------------+----------------+--------------+
| Shi Zhongyu | Weituo Zhang   |           93 |
| Shi Potian  | Daiyu Zanghua  |           97 |
| Xie Yanke   | Kuihua Baodian |           88 |
| Ding Dian   | Kuihua Baodian |           89 |
| Shi Qing    | Hamo Gong      |           96 |
| Xi Ren      | Hamo Gong      |           86 |
| Xi Ren      | Dagou Bangfa   |           83 |
| Lin Daiyu   | Jinshe Jianfa  |           93 |
+-------------+----------------+--------------+
8 rows in set (0.001 sec)
14. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
取出每位同学各科平均成绩：

MariaDB [hellodb]> select name 姓名,avg(score) 各科平均成绩  from students st
    -> inner join scores sc on st.stuid=sc.stuid 
    -> group by st.stuid;


+-------------+--------------------+
| 姓名        | 各科平均成绩       |
+-------------+--------------------+
| Shi Zhongyu |            85.0000 |
| Shi Potian  |            72.0000 |
| Xie Yanke   |            81.5000 |
| Ding Dian   |            80.0000 |
| Yu Yutong   |            51.0000 |
| Shi Qing    |            96.0000 |
| Xi Ren      |            84.5000 |
| Lin Daiyu   |            75.0000 |
+-------------+--------------------+
8 rows in set (0.001 sec)


按各科平均成绩排序，取前三名：

MariaDB [hellodb]> select name 姓名,avg(score) 各科平均成绩  from students st 
    -> inner join scores sc on st.stuid=sc.stuid
    -> group by st.stuid order by avg(score) desc limit 3;

+-------------+--------------------+
| 姓名        | 各科平均成绩       |
+-------------+--------------------+
| Shi Qing    |            96.0000 |
| Shi Zhongyu |            85.0000 |
| Xi Ren      |            84.5000 |
+-------------+--------------------+
3 rows in set (0.001 sec)
15. 显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> select course 课程名称,count(course) 学习人数  from students st
    -> inner join scores sc on st.stuid=sc.stuid
    -> inner join courses co on sc.courseid=co.courseid group by course;


+----------------+--------------+
| 课程名称       | 学习人数     |
+----------------+--------------+
| Dagou Bangfa   |            2 |
| Daiyu Zanghua  |            2 |
| Hamo Gong      |            3 |
| Jinshe Jianfa  |            1 |
| Kuihua Baodian |            4 |
| Taiji Quan     |            1 |
| Weituo Zhang   |            2 |
+----------------+--------------+
7 rows in set (0.001 sec)
16. 显示其年龄大于平均年龄的同学的名字
MariaDB [hellodb]> select name,age from students where age > (select avg(age) from students);

+--------------+-----+
| name         | age |
+--------------+-----+
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Sun Dasheng  | 100 |
+--------------+-----+
5 rows in set (0.000 sec)
17. 显示其学习的课程为第1、2，4或第7门课的同学的名字
MariaDB [hellodb]> select name,classid  from students where classid in (1,2,4,7);
+--------------+---------+
| name         | classid |
+--------------+---------+
| Shi Zhongyu  |       2 |
| Shi Potian   |       1 |
| Xie Yanke    |       2 |
| Ding Dian    |       4 |
| Lin Daiyu    |       7 |
| Wen Qingqing |       1 |
| Tian Boguang |       2 |
| Duan Yu      |       4 |
| Xu Zhu       |       1 |
| Lin Chong    |       4 |
| Hua Rong     |       7 |
| Diao Chan    |       7 |
| Xiao Qiao    |       1 |
| Ma Chao      |       4 |
+--------------+---------+
14 rows in set (0.001 sec)
18. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
MariaDB [hellodb]> select classid,name,age  from students group by classid  having count(*) > 3 and age >  avg(age) ;

+---------+------------+-----+
| classid | name       | age |
+---------+------------+-----+
|       1 | Shi Potian |  22 |
|       3 | Yu Yutong  |  26 |
|       4 | Ding Dian  |  32 |
+---------+------------+-----+
3 rows in set (0.000 sec)
19. 统计各班级中年龄大于全校同学平均年龄的同学
MariaDB [hellodb]> select classid,name,age from students where age > (select avg(age) from students ) order by classid ;
+---------+--------------+-----+
| classid | name         | age |
+---------+--------------+-----+
|    NULL | Sun Dasheng  | 100 |
|       2 | Xie Yanke    |  53 |
|       2 | Tian Boguang |  33 |
|       4 | Ding Dian    |  32 |
|       5 | Shi Qing     |  46 |
+---------+--------------+-----+
5 rows in set (0.000 sec)


若classid 为null的不属于本校学生：

MariaDB [hellodb]> select classid,name,age from students where classid is not null and age > (select avg(age) from students where classid is not null ) order by classid;
+---------+--------------+-----+
| classid | name         | age |
+---------+--------------+-----+
|       2 | Xie Yanke    |  53 |
|       2 | Tian Boguang |  33 |
|       3 | Yu Yutong    |  26 |
|       4 | Ding Dian    |  32 |
|       4 | Lin Chong    |  25 |
|       5 | Shi Qing     |  46 |
+---------+--------------+-----+
6 rows in set (0.001 sec)
i_boyish
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql多表操作例题_MySQL多表查询练习题
weixin_42299944的博客
01-19 460
1、查询没有学全所有课的同学的学号、姓名；#先统计一共有多少门课程select count(cid)fromcourse;#查看每个学生选择的课程书select count(course_id)fromscore group by student_id;#查询所学课程数小于总课程数的学生学号select student_idfrom (select count(course_id) c_cours...
mysql数据库部门表_MySQL数据库单表练习
weixin_35677065的博客
02-03 1129
练习1CREATE TABLE product(pid INT PRIMARY KEY,#主键IDpname VARCHAR(20),#商品名称price DOUBLE,#商品价格category_name VARCHAR(32)#商品分类名称);INSERT INTO product(pid,pname,price,category_name) VALUES(1,'联想电脑',5000,'电脑办...
MySQL练习题（单表多表查询）
阿瑾的博客
01-09 411
mysql> select e.depno “部门号”,d.depname “部门名称”,avg(s.inCome-s.outCome) “平均工资” from Employee e,salay s,department d where e.num=s.num and e.depno=d.depno group by d.depno having avg(s.inCome-s.outCome)>2500;求各部门的雇员数（要求显示，部门号、部门名称和部门雇员数）；查找比所有财务部雇员收入都高的雇员的姓名；
七、MySQL 多表查询详解(附练习题及答案----超详细)
Amo Xiang的博客
01-12 3097
MySQL多表查询详解及练习(附答案)
Mysql多表练习题30道
weixin_49627122的博客
08-17 6212
mysql练习题30道，希望大家能够通过这30道题，精通select查询语言
MySQL单表查询练习
12-07
### MySQL单表查询练习知识点详解 #### 1. 查看EMP表中部门号为5的员工的姓名，职位，参加工作时间，工资。 - **知识点**：基础的`SELECT`语句结合`WHERE`子句筛选特定条件的数据。 - **SQL语句**： ```sql ...
mysql 多表联接查询练习
10-31
本篇文章将通过具体的练习案例来深入讲解MySQL中的多表联接查询技巧，特别是内联接（INNER JOIN）与外联接（OUTER JOIN）的应用。 #### 二、数据库表结构及数据 为了更好地理解和实践多表联接查询，这里构建了五个...
Mysql练习题基础操作 单表查询 多表查询 数据更新
最新发布
04-11
在进行MySQL的学习与实践时，基础操作、单表查询、多表查询以及数据更新是四个核心知识点。 1. **基础操作**： - **创建数据库**：使用`CREATE DATABASE`语句创建数据库，如`CREATE DATABASE fuxi;` - **删除...
数据库多表查询练习.txt
01-31
数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作
MySQL多表查询详解
03-08
#### 一、多表查询的基本概念与操作 多表查询是在实际业务场景中非常常见的需求之一。它允许我们从多个表中检索数据，并将这些数据组合在一起以形成有意义的信息。在MySQL中，多表查询通常涉及使用`JOIN`操作来连接...
数据库作业之多表查询
yikenaoguazi的博客
09-08 961
day45-461.表结构:2.建表3.练习题 1.表结构: 2.建表 # 1、创建表 # 创建班级表 create table class( cid int primary key auto_increment, caption varchar(32) not null ); # 创建学生表 create table student( sid int primary key auto_increment, gender char(1) not null, class_id int not null,
MySQL 单表和多表的基本练习题
LP的博客
01-08 759
1 表结构 DROP DATABASE IF EXISTS test1; CREATE DATABASE test1; USE test1; ##部门表 #DROP IF EXISTS TABLE DEPT; CREATE TABLE DEPT( DEPTNO int PRIMARY KEY,##部门编号 DNAME VARCHAR(14) , ##部门名称 LOC VARCHAR(13) ...
MySQL数据库系统select语句相关练习
llh47519587的博客
05-30 1028
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄 select name,age from students where age >25; (2) 以ClassID为分组依据，显示每组的平均年龄 select classid,avg(age) from students group by classid; (3) 显示第2题中平均年龄大于30的分组及平均年龄 s...
mysql多表好h还是单表好_MySQL 多次单表查询和多表联合查询
weixin_31614235的博客
02-18 872
MySQL 多次单表查询和多表联合查询Tip: 不建议执行三张表以上的多表联合查询对数据量不大的应用来说, 多表联合查询开发高效, 但是多表联合查询在表数据量大, 并且没有索引的时候, 如果进行笛卡儿积, 那数据量会非常大, sql 执行效率会非常低多次单表查询在 service 层进行合并好处:1, 缓存效率更高, 许多应用程序可以方便地缓存单表查询对应的结果对象. 如果关联中的某个表发生了变化...
单表查询练习题
oDianZi1234567的博客
08-21 2431
CREATE TABLE employee( id INT, name VARCHAR(20), PASSWORD VARCHAR(20), gender VARCHAR(10), age INT DEFAULT 25, email VARCHAR(50), salary DOUBLE(8,2), state INT, dep...
Mysql的多表与单表练习
Bao_Joy的博客
09-05 553
今日内容 MYSQL多表和单表的练习题 表1 部门表 DEPT 】 – 部门表创建 CREATE TABLE DEPT( DEPTNO INT PRIMARY KEY, DNAME VARCHAR(14), -- 部门名称 LOC VARCHAR(13)-- 部门地址 ) ; –部门表赋值 INSERT INTO DEPT VALUE...
sql单表-多表查询练习
weixin_43447266的博客
08-26 217
create table Student -- 学生表 ( Sno char(3) NOT NULL Primary key , -- 学号 ，设为主键，不允许空值 Sname char(8) NOT NULL, -- 学生姓名 Ssex char(2)NOT NULL, -- 学生性别 Sbirthday datetime, -- 学生出生年月 Class char(5) -- 学生所在班级编号 ); create table Tea
mysql 多表查询练习题（二）答案
凡心所向.AI
08-12 3883
这篇文章是sql语句的答案篇，数据库表信息请查看上一篇 “mysql 多表查询练习题（一）数据准备” 1．查询平均成绩大于70分的同学的学号和平均成绩 select sid,avg(score) avg_sc from sc group by sid having avg_sc>70 2．查询所有同学的学号、姓名、选课数、总成绩 select sc.sid,st.snam...
练习-单表查询操作
weixin_30376453的博客
03-24 185
项目目录 要求：实现书籍的增加页面；查看书籍；删除书籍；编辑功能 创建项目，创建应用app01;检查：settings的app有无创建的app01的应用名;templates有无DIRS；链接mysql数据库表；创建models表中的类 settings #1 检查APP有无自创建的app01 INSTALLED_APPS = [ 'django.contrib.adm...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值