mysql数据库查询练习

最新推荐文章于 2024-07-23 07:27:56 发布
最新推荐文章于 2024-07-23 07:27:56 发布
阅读量314 收藏
点赞数
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/geor_ge_/article/details/108852103
版权
本文通过一系列MySQL查询练习，展示了如何筛选年龄大于25岁且为男性的学生信息，按班级分组的平均年龄，以及成绩优秀的学员姓名、课程等。涉及到了WHERE、GROUP BY、HAVING和JOIN等SQL语句的应用。
摘要由CSDN通过智能技术生成

mysql数据库查询练习

SELECT语句的执行流程：

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY -->  LIMIT

#学生信息表
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.00 sec)

#班级表
MariaDB [hellodb]> select * from classes;
+---------+----------------+----------+
| ClassID | Class          | NumOfStu |
+---------+----------------+----------+
|       1 | Shaolin Pai    |       10 |
|       2 | Emei Pai       |        7 |
|       3 | QingCheng Pai  |       11 |
|       4 | Wudang Pai     |       12 |
|       5 | Riyue Shenjiao |       31 |
|       6 | Lianshan Pai   |       27 |
|       7 | Ming Jiao      |       27 |
|       8 | Xiaoyao Pai    |       15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)

#班级课程表
MariaDB [hellodb]> select * from coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
|  1 |       1 |        2 |
|  2 |       1 |        5 |
|  3 |       2 |        2 |
|  4 |       2 |        6 |
|  5 |       3 |        1 |
|  6 |       3 |        7 |
|  7 |       4 |        5 |
|  8 |       4 |        2 |
|  9 |       5 |        1 |
| 10 |       5 |        9 |
| 11 |       6 |        3 |
| 12 |       6 |        4 |
| 13 |       7 |        4 |
| 14 |       7 |        3 |
+----+---------+----------+
14 rows in set (0.00 sec)

#课程表
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.00 sec)

#学生成绩表
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.00 sec)
  1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
   MariaDB [hellodb]> select name,age,gender from students where age > 25;
   +--------------+-----+--------+
   | name         | age | gender |
   +--------------+-----+--------+
   | Xie Yanke    |  53 | M      |
   | Ding Dian    |  32 | M      |
   | Yu Yutong    |  26 | M      |
   | Shi Qing     |  46 | M      |
   | Tian Boguang |  33 | M      |
   | Xu Xian      |  27 | M      |
   | Sun Dasheng  | 100 | M      |
   +--------------+-----+--------+
   7 rows in set (0.00 sec)
  1. 以ClassID为分组依据，显示每组的平均年龄
   MariaDB [hellodb]> select classid,AVG(age) from students group by classid;
   +---------+----------+
   | classid | AVG(age) |
   +---------+----------+
   |    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.01 sec)
  1. 显示第2题中平均年龄大于30的分组及平均年龄
   MariaDB [hellodb]> select classid,AVG(age) from students group by classid having AVG(age) > 30;
   +---------+----------+
   | classid | AVG(age) |
   +---------+----------+
   |    NULL |  63.5000 |
   |       2 |  36.0000 |
   |       5 |  46.0000 |
   +---------+----------+
   3 rows in set (0.00 sec)
  1. 显示以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.00 sec)
  1. 显示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.00 sec)
  1. 以年龄排序后，显示年龄最大的前10位同学的信息
   MariaDB [hellodb]> select * from students order 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 |
   |    23 | Ma Chao      |  23 | M      |       4 |      NULL |
   |    18 | Hua Rong     |  23 | M      |       7 |      NULL |
   +-------+--------------+-----+--------+---------+-----------+
   10 rows in set (0.00 sec)
  1. 查询年龄大于等于20岁，小于等于25岁的同学的信息
   #方法1
   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.00 sec)
   
   #方法2
   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.00 sec)
  1. 以ClassID分组，显示每班的同学的人数
   MariaDB [hellodb]> select classid,COUNT(*) from students group by classid;
   +---------+----------+
   | classid | COUNT(*) |
   +---------+----------+
   |    NULL |        2 |
   |       1 |        4 |
   |       2 |        3 |
   |       3 |        4 |
   |       4 |        4 |
   |       5 |        1 |
   |       6 |        4 |
   |       7 |        3 |
   +---------+----------+
   8 rows in set (0.00 sec)
  1. 以Gender分组，显示其年龄之和
   MariaDB [hellodb]> select gender,sum(age) from students group by gender;
   +--------+----------+
   | gender | sum(age) |
   +--------+----------+
   | F      |      190 |
   | M      |      495 |
   +--------+----------+
   2 rows in set (0.00 sec)
  1. 以ClassID分组，显示其平均年龄大于25的班级
    MariaDB [hellodb]> select classid,AVG(age) from students group by classid having AVG(age) >25;
    +---------+----------+
    | classid | AVG(age) |
    +---------+----------+
    |    NULL |  63.5000 |
    |       2 |  36.0000 |
    |       5 |  46.0000 |
    +---------+----------+
    3 rows in set (0.00 sec)
  1. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
    MariaDB [hellodb]> select gender,sum(age) from students where age > 25 group by gender ;
    +--------+----------+
    | gender | sum(age) |
    +--------+----------+
    | M      |      317 |
    +--------+----------+
    1 row in set (0.00 sec)
  1. 显示前5位同学的姓名、课程及成绩
    MariaDB [hellodb]> select name,course,score from students s inner join scores sc on s.stuid=sc.stuid inner join courses cs on cs.courseid=sc.courseid order by score desc limit 5;
    +-------------+----------------+-------+
    | name        | course         | score |
    +-------------+----------------+-------+
    | 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.00 sec)
  1. 显示其成绩高于80的同学的名称及课程
    MariaDB [hellodb]> select name,course,score from students s inner join scores sc on s.stuid=sc.stuid inner join courses cs on cs.courseid=sc.courseid where score > 80;
    +-------------+----------------+-------+
    | name        | course         | score |
    +-------------+----------------+-------+
    | 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.01 sec)
  1. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
    MariaDB [hellodb]> select name,AVG(score) from students s inner join scores sc on s.stuid=sc.stuid group by score order by score desc limit 3;
    +-------------+------------+
    | name        | AVG(score) |
    +-------------+------------+
    | Shi Potian  |    97.0000 |
    | Shi Qing    |    96.0000 |
    | Shi Zhongyu |    93.0000 |
    +-------------+------------+
    3 rows in set (0.00 sec)
  1. 显示每门课程课程名称及学习了这门课的同学的个数
    #以班级课程计算课程人数
    MariaDB [hellodb]> select course,count(*) from courses cs inner join coc c on cs.courseid=c.courseid inner join students s on c.classid=s.classid group by course;
    +----------------+----------+
    | course         | count(*) |
    +----------------+----------+
    | Dagou Bangfa   |        4 |
    | Daiyu Zanghua  |        8 |
    | Hamo Gong      |        5 |
    | Jinshe Jianfa  |        7 |
    | Kuihua Baodian |       11 |
    | Taiji Quan     |        7 |
    | Weituo Zhang   |        3 |
    +----------------+----------+
    7 rows in set (0.00 sec)
    
    #以学习成绩计算课程人数
    MariaDB [hellodb]> select course,count(*) from courses cs inner join scores sc on cs.courseid=sc.courseid inner join students s on s.stuid=sc.stuid group by course;
    +----------------+----------+
    | course         | count(*) |
    +----------------+----------+
    | 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.00 sec)
  1. 显示其年龄大于平均年龄的同学的名字
    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 (4.14 sec)
  1. 显示其学习的课程为第1、2，4或第7门课的同学的名字
    #以学习成绩表计算
    MariaDB [hellodb]> select course,name from courses co inner join scores sc on co.courseid=sc.courseid inner join students s on sc.stuid=s.stuid where sc.courseid in(1,2,4,7);
    +----------------+-------------+
    | course         | name        |
    +----------------+-------------+
    | Hamo Gong      | Yu Yutong   |
    | Hamo Gong      | Shi Qing    |
    | Hamo Gong      | Xi Ren      |
    | Kuihua Baodian | Shi Zhongyu |
    | Kuihua Baodian | Shi Potian  |
    | Kuihua Baodian | Xie Yanke   |
    | Kuihua Baodian | Ding Dian   |
    | Taiji Quan     | Lin Daiyu   |
    | Dagou Bangfa   | Yu Yutong   |
    | Dagou Bangfa   | Xi Ren      |
    +----------------+-------------+
    10 rows in set (0.45 sec)
    
    #以班级课程表计算
    MariaDB [hellodb]> select course,name from courses co inner join coc c on co.courseid=c.courseid inner join students s on c.classid=s.classid where co.courseid in(1,2,4,7);
    +----------------+---------------+
    | course         | name          |
    +----------------+---------------+
    | Kuihua Baodian | Shi Zhongyu   |
    | Kuihua Baodian | Shi Potian    |
    | Kuihua Baodian | Xie Yanke     |
    | Kuihua Baodian | Ding Dian     |
    | Hamo Gong      | Yu Yutong     |
    | Dagou Bangfa   | Yu Yutong     |
    | Hamo Gong      | Shi Qing      |
    | Hamo Gong      | Xi Ren        |
    | Dagou Bangfa   | Xi Ren        |
    | Taiji Quan     | Lin Daiyu     |
    | Taiji Quan     | Ren Yingying  |
    | Hamo Gong      | Yue Lingshan  |
    | Dagou Bangfa   | Yue Lingshan  |
    | Taiji Quan     | Yuan Chengzhi |
    | Kuihua Baodian | Wen Qingqing  |
    | Kuihua Baodian | Tian Boguang  |
    | Hamo Gong      | Lu Wushuang   |
    | Dagou Bangfa   | Lu Wushuang   |
    | Kuihua Baodian | Duan Yu       |
    | Kuihua Baodian | Xu Zhu        |
    | Kuihua Baodian | Lin Chong     |
    | Taiji Quan     | Hua Rong      |
    | Taiji Quan     | Xue Baochai   |
    | Taiji Quan     | Diao Chan     |
    | Taiji Quan     | Huang Yueying |
    | Kuihua Baodian | Xiao Qiao     |
    | Kuihua Baodian | Ma Chao       |
    +----------------+---------------+
    27 rows in set (0.00 sec)
  1. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
    #把原表a拆开拼成b表，然后a跟b相连接
    MariaDB [hellodb]> select name,age from students a inner join (select classid,AVG(age) aage from students where classid > 0 group by classid having count(stuid) >= 3) b on a.classid=b.classid where a.age > b.aage;
    +---------------+-----+
    | name          | age |
    +---------------+-----+
    | Shi Potian    |  22 |
    | Xie Yanke     |  53 |
    | Ding Dian     |  32 |
    | Yu Yutong     |  26 |
    | Yuan Chengzhi |  23 |
    | Xu Zhu        |  21 |
    | Lin Chong     |  25 |
    | Hua Rong      |  23 |
    | Huang Yueying |  22 |
    +---------------+-----+
    9 rows in set (0.00 sec)
  1. 统计各班级中年龄大于全校同学平均年龄的同学
    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.00 sec)
Geor_ge_
关注
专栏目录
Mysql数据库基本查询操作练习题
m0_65071501的博客
08-15 2097
给出四个表，分别为：sc、student、course、teacher，用mysql语言进行查询
数据库查询张三的MYSQL成绩_MySQL数据库查询练习题
weixin_29531897的博客
01-19 6834
练习题目查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数1.1 查询同时存在" 01 "课程和" 02 "课程的情况1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )1.3 查询不存在" 01 "课程但存在" 02 "课程的情况查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩查询在 SC 表存在成绩的学生信息查询所有...
MySQL数据库查询练习（一）
YING—country的博客
11-05 1685
MySQL数据库查询练习练习01练习02 练习01 如下有一个department表，employee表，jobs表，job_grade表，locations表 先创建以上五个表，然后再进行一下练习； -- 子查询 select * from employees; -- 1.查询 最低工资 大于 50号部门最低工资 的 部门id 和 其最低工资 select min(salary) from employees where department_id = 50; -- 50号部门最低工资 se
MYSQL查询练习
最新发布
2301_77699763的博客
07-23 927
insert into t_worker values(101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2--8');mysql> insert into t_worker value(101,1002,'2019-2-6',5200.00,'团员','李名博','1997-2-8');mysql> insert into t_worker value(103,1006,'2019-5-5',5500.00,'党员','孙菲菲','1996-9-2');
mysql数据库
noobzhu的博客
09-22 234
文章目录mysql建表约束mysql 语句mysql的四种连接查询事务 mysql建表约束 主键约束：唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束 create table user( id int primary key, name varchar(20) ); insert into user values(1,‘张三’); insert into user values...
MySQL 数据库查询练习
weixin_38166557的博客
09-13 165
-- ---------------------------- -- mysql练习sql脚本 -- ---------------------------- create database db10; -- 创建db10数据库 use db10; -- 切换到db10数据库 create table dept( ...
MySQL数据库查询操作 练习
xr6xr的博客
01-11 755
create database mydb1; use mydb1; create table emp( empno int, – 工号 ename varchar(10), – 员工姓名 job varchar(10), – 部门 mgr int, – 部门编号 hiredate varchar(10), salary double, – 薪水 comm double, deptno int )charset utf8; insert into emp values(1001,‘甘宁’,‘文员’,
MySQL数据库练习题(50道).pdf
04-25
本资源为MySQL数据库练习题，共50道题，涵盖数据库设计、数据表创建、数据插入、查询等多方面的知识点。下面是对该资源的详细知识点总结： 一、数据库设计 * 数据库设计是指根据具体的应用需求，设计和创建一个...
MySQL数据库操作练习题及答案（以员工信息数据库为例）经典.rar
05-30
这份"MySQL数据库操作练习题及答案（以员工信息数据库为例）经典.rar"资料，旨在帮助学习者深入理解和掌握MySQL的各种核心功能，包括数据的过滤、连接、分组、聚合、子查询、字符串处理、表操作以及触发器的使用。...
MySQL数据库考试练习题 mysql试题集 共28页.pdf
03-17
根据提供的文件信息，我们可以推断出这份文档主要包含MySQL数据库相关的考试练习题目。MySQL是一种广泛使用的开源关系型数据库管理系统（RDBMS），因其性能稳定、易于使用和成本低廉等特点，在互联网应用开发中非常...
mysql数据库查询作业_MySQL 数据库查询练习
weixin_32861907的博客
02-18 335
+----------+-------+---------+--------+------+------------+------+------+--------+| deptname | empno | empname | job | mgr | hiredate | sal | comm | deptno |+----------+-------+---------+------...
mysql查询必练50题
10-10
摘要： -- 1、查询“01”课程比“02”课程成绩高的所有学生的学号； SELECT a.sid FROM (SELECT sid, score FROM sc WHERE cid=01) a, (SELECT sid ,score FROM sc WHERE cid=02) b WHERE a.score > b.score AND a.sid= b.sid; SELECT a.sid FROM student a LEFT JOIN SC b ON b.cid=01 AND a.sid=b.sid LEFT JOIN SC c ON c.cid=02 AND a.sid=c.sid WHERE b.score>c.score; -- 2、查询平均成绩大于60分的同学的学号和平均成绩； SELECT sid 学号, AVG(score) 平均成绩 FROM sc GROUP BY sid HAVING 平均成绩 > 60; SELECT sid, AVG(score) FROM sc GROUP BY sid HAVING AVG(score)>60; -- 3、查询所有同学的学号、姓名、选课数、总成绩； SELECT s.sid 学号, s.sname 姓名, COUNT(*) 选棵数, SUM(score) 总成绩 FROM student s, sc WHERE s.sid = sc.sid GROUP BY sc.sid; SELECT s.sid, sname, COUNT(*), SUM(score) FROM student s LEFT JOIN sc ON s.sid = sc.sid GROUP BY sc.sid;
数据库查询操作练习题答案
09-28
数据库查询操作经典练习题60道答案
mysql数据库查询练习一-【组合两个表】【第二高的薪水】【分数排名】【至少出现3次的数字】
生有涯，知无涯
07-11 391
1.联表查询 表1: Person +-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ P...
【MySQL】——数据库的基本查询练习
sjp11的博客
07-11 963
​ 💂 个人主页:努力学习的少年 🤟 版权: 本文由【努力学习的少年】原创、在CSDN首发、需要转载请联系博主 💬 如果文章对你有帮助、欢迎关注、点赞、收藏(一键三连)和订阅专栏哦 ​...
史上最全MySql查询练习
????allubiba_H的博客
08-01 2934
本文章拥有MySQL中使用的所有查询操作
mysql数据库多表查询的基础练习题1
travellingMan的博客
09-12 1599
一 ，学生选课系统表的准备 teacher 教师表 CREATE TABLE teacher ( id int(11) NOT NULL primary key auto_increment, name varchar(20) not null unique ); student 学生表 CREATE TABLE student ( id int(11) NOT NULL ...
MySQL查询语句练习题，测试基本够用了
LYX_WIN
11-11 883
【代码】MySQL查询语句练习题，测试基本够用了。
mysql查询练习数据库
09-28
MySQL查询练习数据库是一个用于练习和学习SQL查询语句的数据库。它通常包含了多个数据表和示例数据，以帮助用户熟悉和掌握MySQL查询语言。 在这个数据库中，用户可以通过编写不同的SQL查询语句来对数据表进行查询...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值