多表查询练习

于 2023-08-12 23:16:17 发布
阅读量79 收藏
点赞数
文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zuizuishuaideye_/article/details/132254356
版权

创建一张表并插入数据接着实现以下要求：

1.查询student表的所有记录
2.查询student表的第2条到4条记录
3.从student表查询所有学生的学号(id)、姓名(name) 和院系(department)的信息
4.从student表中查询计算机系和英语系的学生的信息
5.从student表中查询年龄18~22岁的学生信息
6.从student表中查询每个院系有多少人
7.从score表中查询每个科目的最高分
8.查询李四的考试科曰(c name) 和考试成绩(grade)
9.用连接的方式查询所有学生的信息和考试信息
10.计算每个学生的总成绩
11.计算每个考试科目的半均成绩
12.查询计算机成绩低于95的学生信息
13.查询同时参加计算机和英语考试的学生的信息
14.将计算机考试成绩按从高到低进行排序
15.从student表和score表中查询出学生的学号，然后合并查询结果
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

一.创建表

mysql> create database mydb3;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb3;
Database changed
mysql> create table student(
    -> id int(10) not null unique primary key,
    -> name varchar(20) not null,
    -> sex varchar(4),
    -> birth year,
    -> department varchar(20),
    -> address varchar(50)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table score(
    -> id int(10) not null unique primary key auto_increment,
    -> stu_id int(10) not null,
    -> c_came varchar(20),
    -> grade int(10)
    -> );
Query OK, 0 rows affected, 3 warnings (0.02 sec)

二.插入数据

mysql> insert into student values(901,'张老大','男',1985,'计算机系','北京市海淀区');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(901,'张老二','男',1986,'中文系','北京市昌平区');
ERROR 1062 (23000): Duplicate entry '901' for key 'student.PRIMARY'
mysql> insert into student values(902,'张老二','男',1986,'中文系','北京市昌平区');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(903,'张三','女',1990,'中文系','湖南省永州市');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(904,'李四','男',1990,'英语系','辽宁省阜新市');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(905,'王五','女',1991,'英语系','福建省厦门市');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(906,'赵六','男',1988,'计算机系','湖南省衡阳市');
Query OK, 1 row affected (0.00 sec)

三.完成题目

1.查询student表的所有记录

mysql> select *from student;
+-----+--------+------+-------+------------+--------------+
| id  | name   | sex  | birth | department | address      |
+-----+--------+------+-------+------------+--------------+
| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |
| 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
| 905 | 王五   | 女   |  1991 | 英语系     | 福建省厦门市 |
| 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
6 rows in set (0.00 sec)

2.查询student表的第2条到4条记录

mysql> select *from student limit 1,3;
+-----+--------+------+-------+------------+--------------+
| id  | name   | sex  | birth | department | address      |
+-----+--------+------+-------+------------+--------------+
| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |
| 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
+-----+--------+------+-------+------------+--------------+
3 rows in set (0.00 sec)

3.从student表查询所有学生的学号(id)、姓名(name) 和院系(department)的信息

mysql> select id 学号,name 姓名,department 院校 from student;
+------+--------+----------+
| 学号 | 姓名   | 院校     |
+------+--------+----------+
|  901 | 张老大 | 计算机系 |
|  902 | 张老二 | 中文系   |
|  903 | 张三   | 中文系   |
|  904 | 李四   | 英语系   |
|  905 | 王五   | 英语系   |
|  906 | 赵六   | 计算机系 |
+------+--------+----------+
6 rows in set (0.00 sec)

4.从student表中查询计算机系和英语系的学生的信息

mysql> select *from student where department in('计算机系','英语系');
+-----+--------+------+-------+------------+--------------+
| id  | name   | sex  | birth | department | address      |
+-----+--------+------+-------+------------+--------------+
| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
| 905 | 王五   | 女   |  1991 | 英语系     | 福建省厦门市 |
| 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
4 rows in set (0.00 sec)

5.从student表中查询年龄18~22岁的学生信息

mysql> insert into student values(907,'刘亦菲','女',2001,'中文系','北京市东城区');
Query OK, 1 row affected (0.00 sec)

mysql> select *from student where year(now())-birth between 18 and 22;
+-----+--------+------+-------+------------+--------------+
| id  | name   | sex  | birth | department | address      |
+-----+--------+------+-------+------------+--------------+
| 907 | 刘亦菲 | 女   |  2001 | 中文系     | 北京市东城区 |
+-----+--------+------+-------+------------+--------------+
1 row in set (0.00 sec)

6.从student表中查询每个院系有多少人

mysql> select department 院系,count(*) 人数 from student group by department;
+----------+------+
| 院系     | 人数 |
+----------+------+
| 计算机系 |    2 |
| 中文系   |    3 |
| 英语系   |    2 |
+----------+------+
3 rows in set (0.00 sec)

7.从score表中查询每个科目的最高分

mysql> select c_came 学科,max(grade) 最高分 from score group by c_came;
+--------+--------+
| 学科   | 最高分 |
+--------+--------+
| 计算机 |     98 |
| 英语   |     94 |
| 中文   |     95 |
+--------+--------+
3 rows in set (0.00 sec)

8.查询李四的考试科曰(c name) 和考试成绩(grade)

mysql> select c_came 学科,grade 成绩 from score inner join student on score.stu_id=student.id where student.name='李四';
+--------+------+
| 学科   | 成绩 |
+--------+------+
| 计算机 |   70 |
| 英语   |   92 |
+--------+------+
2 rows in set (0.00 sec)

9.用连接的方式查询所有学生的信息和考试信息

mysql> select *from score inner join student on score.stu_id=student.id;
+----+--------+--------+-------+-----+--------+------+-------+------------+--------------+
| id | stu_id | c_came | grade | id  | name   | sex  | birth | department | address      |
+----+--------+--------+-------+-----+--------+------+-------+------------+--------------+
|  1 |    901 | 计算机 |    98 | 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
|  2 |    901 | 英语   |    80 | 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
|  3 |    902 | 计算机 |    65 | 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |
|  4 |    902 | 中文   |    88 | 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |
|  5 |    903 | 中文   |    95 | 903 | 张三   | 女   |  1990 | 中文系     | 湖南省永州市 |
|  6 |    904 | 计算机 |    70 | 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
|  7 |    904 | 英语   |    92 | 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
|  8 |    905 | 英语   |    94 | 905 | 王五   | 女   |  1991 | 英语系     | 福建省厦门市 |
|  9 |    906 | 计算机 |    90 | 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
| 10 |    906 | 英语   |    85 | 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
+----+--------+--------+-------+-----+--------+------+-------+------------+--------------+
10 rows in set (0.00 sec)

10.计算每个学生的总成绩

mysql> select name,sum(grade) from student inner join score on score.stu_id=student.id group by score.stu_id;
+--------+------------+
| name   | sum(grade) |
+--------+------------+
| 张老大 |        178 |
| 张老二 |        153 |
| 张三   |         95 |
| 李四   |        162 |
| 王五   |         94 |
| 赵六   |        175 |
+--------+------------+
6 rows in set (0.00 sec)

11.计算每个考试科目的半均成绩

mysql> select c_came,avg(grade) from student inner join score on score.stu_id=student.id group by score.c_came;
+--------+------------+
| c_came | avg(grade) |
+--------+------------+
| 计算机 |    80.7500 |
| 英语   |    87.7500 |
| 中文   |    91.5000 |
+--------+------------+

12.查询计算机成绩低于95的学生信息

mysql> select *from student inner join score on score.stu_id=student.id where score.c_came='计算机' and score.grade<95;
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| id  | name   | sex  | birth | department | address      | id | stu_id | c_came | grade |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |  3 |    902 | 计算机 |    65 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |  6 |    904 | 计算机 |    70 |
| 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |  9 |    906 | 计算机 |    90 |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
3 rows in set (0.00 sec)

13.查询同时参加计算机和英语考试的学生的信息

mysql> select *from student where id in(select stu_id from score where c_came='英语' and stu_id in(select stu_id from score where c_came='计算机'));
+-----+--------+------+-------+------------+--------------+
| id  | name   | sex  | birth | department | address      |
+-----+--------+------+-------+------------+--------------+
| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |
| 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |
+-----+--------+------+-------+------------+--------------+
3 rows in set (0.00 sec)

14.将计算机考试成绩按从高到低进行排序

mysql>  select *from student inner join score on student.id=score.stu_id where score.c_came='计算机' order by score.grade desc;
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| id  | name   | sex  | birth | department | address      | id | stu_id | c_came | grade |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
| 901 | 张老大 | 男   |  1985 | 计算机系   | 北京市海淀区 |  1 |    901 | 计算机 |    98 |
| 906 | 赵六   | 男   |  1988 | 计算机系   | 湖南省衡阳市 |  9 |    906 | 计算机 |    90 |
| 904 | 李四   | 男   |  1990 | 英语系     | 辽宁省阜新市 |  6 |    904 | 计算机 |    70 |
| 902 | 张老二 | 男   |  1986 | 中文系     | 北京市昌平区 |  3 |    902 | 计算机 |    65 |
+-----+--------+------+-------+------------+--------------+----+--------+--------+-------+
4 rows in set (0.00 sec)

15.从student表和score表中查询出学生的学号，然后合并查询结果

mysql> select distinct student.id 学号,student.name 姓名 from student inner join score on student.id=score.stu_id;
+------+--------+
| 学号 | 姓名   |
+------+--------+
|  901 | 张老大 |
|  902 | 张老二 |
|  903 | 张三   |
|  904 | 李四   |
|  905 | 王五   |
|  906 | 赵六   |
+------+--------+
6 rows in set (0.00 sec)

16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql> select name 姓名,department 院系,c_came 考试科目,grade 成绩 from student inner join score on student.id=score.stu_id where student.name like '张%' or student.name like '王%';
+--------+----------+----------+------+
| 姓名   | 院系     | 考试科目 | 成绩 |
+--------+----------+----------+------+
| 张老大 | 计算机系 | 计算机   |   98 |
| 张老大 | 计算机系 | 英语     |   80 |
| 张老二 | 中文系   | 计算机   |   65 |
| 张老二 | 中文系   | 中文     |   88 |
| 张三   | 中文系   | 中文     |   95 |
| 王五   | 英语系   | 英语     |   94 |
+--------+----------+----------+------+
6 rows in set (0.00 sec)

17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql> select name 姓名,year(now())-birth as 年龄, department 院系,c_came 考试科目,grade 成绩 from student inner join score on student.id=score.stu_id where student.address like '%湖南%';
+------+------+----------+----------+------+
| 姓名 | 年龄 | 院系     | 考试科目 | 成绩 |
+------+------+----------+----------+------+
| 张三 |   33 | 中文系   | 中文     |   95 |
| 赵六 |   35 | 计算机系 | 计算机   |   90 |
| 赵六 |   35 | 计算机系 | 英语     |   85 |
+------+------+----------+----------+------+
3 rows in set (0.00 sec)

Over.

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值