MYSQL--SELECT练习题1

于 2023-01-11 10:30:04 发布
阅读量141 收藏
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kidding10086/article/details/128639787
版权

文章目录

一.问题

有 3 个表 STUDENT(学生表)，LESSON（课程表），TIMETABLE（学生选课表）。其中

STUDENT（SNO，SNAME）代表（学号，姓名）

（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

TIMETABLE（ID,SNO，CNO，SCGRADE）代表（序号，学号，课号，成绩）

问题：

  1. 找出没选过“黎明”老师的所有学生姓名。

  2. 列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

  3. 既学过 1 号课程又学过 2 号课所有学生的姓名。

建表代码如下

START TRANSACTION;

DROP TABLE IF EXISTS TIMETABLE;
DROP TABLE IF EXISTS STUDENT;
DROP TABLE IF EXISTS LESSON;


CREATE TABLE STUDENT
(
 SNO VARCHAR(200) PRIMARY KEY,
 SNAME VARCHAR(200)
)DEFAULT CHARSET = 'utf8';

CREATE TABLE LESSON
(
 CNO VARCHAR(200) PRIMARY KEY,
 CNAME VARCHAR(200),
 CTEACHER VARCHAR(200)
) DEFAULT CHARSET = 'utf8';

CREATE TABLE TIMETABLE
(
 ID  INT PRIMARY KEY AUTO_INCREMENT,
 SNO VARCHAR(200),
 CNO VARCHAR(200),
 SCGRADE VARCHAR(200),
 FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
 FOREIGN KEY(CNO) REFERENCES LESSON(CNO)
)DEFAULT CHARSET = 'utf8';


INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO LESSON ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 

INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '1', '学生 1'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '2', '学生 2'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '3', '学生 3'); 
INSERT INTO STUDENT ( SNO, SNAME ) VALUES ( '4', '学生 4'); 

INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80');
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO TIMETABLE ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 


commit;

二.解析

问题1

找出没选过“黎明”老师的所有学生姓名。

思路：首先找出所有选过”黎明“老师课程的学生，将其存储在视图LimingStudent中。然后用STUDENT表与LimingStudent进行比对，即可找出剩下没选过”黎明“老师课程的学生。

step1: 利用表连接，将timetable中的课程号换成上课老师的姓名

select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id;

在这里插入图片描述

step2: 找到所有选过“黎明”老师课程的学生编号

select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';

在这里插入图片描述

PS:上面语句中的表a 即为step1中的表

step3: 将step2中的查询结果存储在视图LimingStudent中

create view LimingStudent as select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';

step4: 将LimingStudent中的学生编号与Student中的学生编号进行比对，找到没选过“黎明”课的学生.

 select s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);

在这里插入图片描述

step5: 由于表连接是两个表的所有表项按照笛卡尔积一一比对，所以结果有重复。因此不要忘了要去重

 select distinct s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);

在这里插入图片描述

最后记得删除视图！

完整代码为

create view LimingStudent as select a.sno from (select t.id,t.sno,l.cteacher,t.scgrade from timetable t join lesson l on t.cno = l.cno order by id) a where cteacher = '黎明';
 select distinct s.sname from student s join LimingStudent on s.sno not in (select sno from LimingStudent);
 drop view LimingStudent;

问题2

列出 2 门以上（含 2 门）不及格学生姓名及平均成绩。

思路: 找到两门以上不及格的学生编号，而后利用表连接找出学生姓名，利用分组函数avg找出平均成绩。

step1: 找到挂过科的学生的学生编号

select sno,cno,scgrade from timetable where scgrade < 60;

step2: 将step1中的表按照sno进行分组，找出每个学生不及格的课程数目

select t.sno,count(t.cno)count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno;

在这里插入图片描述

step3: 利用step2中的表，找到挂科数大于2门的学生编号

 select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;

在这里插入图片描述

step4: 将step3中的结果创建一个视图存储起来。

create view view1 as select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;

step5: 利用表连接查询对应的学生姓名

 select s.sname,v.sno from student s join view1 v on s.sno = v.sno;

在这里插入图片描述

step6: 利用分组函数avg计算出大于2门不及格的学生的平均成绩。注意sql语句的where部分，要用in来判断，而不能用=，如果view1中的数据大于1，用=会报错。

 select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno;

在这里插入图片描述

step7: 将step5的结果与step6的结果利用表连接合并

 select t1.sname ,t2.avg from ( select s.sname,v.sno from student s join view1 v on s.sno = v.sno) t1 join ( select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno) t2 on t1.sno = t2.sno;

在这里插入图片描述

完整代码

create view view1 as select t1.sno from  (select t.sno,count(t.cno) count from(select sno,cno,scgrade from timetable where scgrade < 60) t group by t.sno) t1 where t1.count>=2;
select t1.sname ,t2.avg from ( select s.sname,v.sno from student s join view1 v on s.sno = v.sno) t1 join ( select sno,avg(scgrade) avg from timetable where sno in (select sno from view1) group by sno) t2 on t1.sno = t2.sno;
drop view view1;

问题3

既学过 1 号课程又学过 2 号课所有学生的姓名。

思路：首先找出学过1号课程的学生姓名，然后找出学过2号课程的学生姓名，最后取交集。

step1: 先在timetable表中找到选1号课程学生的学生编号

select sno,cno from timetable where cno = 1;

在这里插入图片描述

step2: 利用表连接，找到选1号课程对应的学生姓名

select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;

在这里插入图片描述

step3: 将step2的结果存储在一个视图中

create view view1 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;

step4: 按照上面的步骤选出选择2号课程的学生姓名，并存储在一个视图中

create view view2 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 2) t join student s on t.sno = s.sno;

step5: 将两个视图连接查询，找出同时存在于两个视图中的学生姓名

select v1.sname from view1 v1 join view2 v2 on v1.sname = v2.sname;

在这里插入图片描述

完整代码

create view view1 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 1) t join student s on t.sno = s.sno;
create view view2 as  select s.sname,t.cno from (select sno,cno from timetable where cno = 2) t join student s on t.sno = s.sno;
select v1.sname from view1 v1 join view2 v2 on v1.sname = v2.sname;
drop view view1;
drop view view2;
_kidding_
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql中select 的题型_Mysql语句查询练习（45道题）
weixin_39642761的博客
02-02 488
题目：设有一数据库，包括四个表：学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示，数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。表1-1数据库的表结构表(一)Student (学生表)属性名数据类型可否为空含 义Snovarchar (20)否学号(主码)Sna...
select练习
uEven的博客
03-31 59
SELECT employee_id,last_name,salary *12 "ANNUAL SALARY" FROM employees; #计算12月的基本工资和奖金 SELECT employee_id,last_name,salary * 12 * (1+IFNULL(commission_pct,0))"ANNUAL SALARY" FROM employees; #查询employees表中去除重复的job_id以后的数据 SELECT DISTINCT job_id FROM employ
select语句练习
jiandanbuguo的博客
08-17 683
建表并插入数据 # tb_class CREATE TABLE tb_class( cid INT(11) AUTO_INCREMENT PRIMARY KEY, cname VARCHAR(50) NOT NULL ); # tb_class数据 INSERT INTO tb_class(cname) VALUES('KB01'),('KB02'),('KB03'); # tb_student CREATE TABLE tb_student( sid INT(11) AUTO_INCREMENT
mysql----select----练习题(2)
VIC1921507475的博客
01-12 378
-- 14、查询所有学⽣的Sname、Cno和Degree列。 /* select Cno,degree from SCORE where SNAME in ( select SNAME from STUDENG) */ select b.sname,a.cno,a.degree from SCORE a inner join STUDENG b on a.sno=b.sno -- 15、查询所有学⽣的Sno、Cname和Degree列。 select a.Sno,b.Cname,a....
MySQL笔试题——select
一念流转
05-21 291
grade表的源数据： 问题一：编写SQL语句实现下面的查询结果 首先根据subject分类，再根据score降速排序，并显示学生的排名，成绩相同的学生排名同一，紧接着下一位学生排名包含成绩重叠的学生。 两层嵌套查询，内部先匹配subject，再根据score字段标识排名。 select *, (select count(*)+1 from grade as B where A.subject = B.subject and A.score < B.score) as rank from
MySQL练习题(附带数据)
12-02
MySQL练习题(附带数据)， 包含：DDL：数据定义语言，定义库，表结构等，包括create,drop,alter等 DML：数据操作语言，增删改查数据，包括insert,delete,update,select等 DCL：数据控制语言，权限，事务等管理。 DQL:...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
干货题目 mysql 面试题集锦 常见练习题目 考试题目 共28页.pdf
08-21
() mysql> select employee921.id,employee921.name,employee921.salary,employee921.dep tid ticl from employee921 where salary > (select avg(salary) from employee921 where deptid = tid); 效率低的一个语句...
【MySQL作业】SELECT 数据查询——美和易思select 选择列表应用习题
01-19
1、查询所有客户的地址和电话号码。 SELECT address, phone FROM customer 2、查询所有商品的名称、种类和单价信息。 SELECT goodsname 商品名称,category 种类,unitPrice 单价 FROM goods 3、查询所有订单的...
【MySQL作业】SELECT 数据查询——美和易思定制显示查询结果应用习题
12-14
1、分类显示所有商品信息，按商品价格升序排列。 “分类显示所有商品信息”表明查询表“商品”，排序列为“类型”；“按商品价格升序排列”表明显示同一类型的商品时，需按照“价格”进行升序排列。所以，第一排序...
sql select 练习
weixin_44724691的博客
03-20 349
EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False 【例3.60】 select Sname from TEST.Student where exists ( select * from TEST.SC where Sno=Student.Sno and Cno='1' ) select * from TEST.SC select * fr...
简单练习select语句
斯特凡今天也很帅的博客
07-01 752
文前附sql语句学习链接 https://www.w3cschool.cn/sql/ 一、基础语句 1.找出工资是800的员工 select ename,sal from emp where sal=800; 2.找出叫Smith的人 select ename,sal from emp where ename='smith'; 3.找出工资大于1500的员工 select ename,sal from emp where sal>1500; 4.找出工资小于1000的员工 5.找出工资大于
MYSQL数据库练习题操作（select）大全
angnai5032的博客
01-05 265
1、创建表 表一：student学生use) 1、create table student( sno varchar(20) primary key not null comment'学号(主码)', sname varchar(20) not null comment'学生姓名', ssex varchar(20) not null comment...
mysql中 select 查询20道经典基础题
OKkkbh的博客
11-27 648
---1：查询EMP表的全部职工的EMPNO、ENAME和JOB。 select empno,ename,job from emp; ---2：查询EMP表的全部职工的EMPNO、ENAME和JOB，按salary升序次序排列。 select empno, ename, job from emp order by salary; ---3：查询EMP表的全部列，列的次序为： ---JOB，SALARY，ENAME，EMPNO，MGR，HIREDATE，BONUS，DEPTNO，查询结果按年薪降序。 se
MYSQL SELECT查询语句——数据库例题解析（以自建数据库为例）
selectEdenHu的博客
08-13 772
** MYSQL SELECT查询语句——数据库例题解析（以自建数据库为例） ** 话不多说，上图（以下是已经创建好的五张表，分别是属性表，英雄表，皮肤表，玩家表以及皮肤拥有明细表） * 属性表 英雄表 皮肤表 玩家表 皮肤拥有明细表 例题1.检索注册日期在2017年之前的女玩家的ID和姓名。 SELECT ID,姓名 FROM 玩家表 WHERE 注册日期<'2017-1-1...
mysql中select查询强化50题（附解答）
OKkkbh的博客
11-27 2035
masql的查询操作练习题。题目多样，思路多变。嵌套查询，子查询，连接查询一篇博文一网打尽！真正强化了解数据库。
select练习题（二）答案
lizhiyu211的专栏
05-16 378
--1select Sname,Ssex,Classfrom s;--2select depart from tgroup by depart;--3select * from s;--4select * from sc where degree between 60 and 80;--5select * from sc where degree in(85,86,88);--6select * ...
【数据库34道习题（一）】通过34道习题来真正掌握sql语句的运用
maikotom的博客
02-28 515
mysql> select * from emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-----------+------+------------+---------+-------
MySQL练习题（单表多表查询）
阿瑾的博客
01-09 388
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 sql练习题_Mysql----sql语句练习题(一)
最新发布
06-10
好的，我来为您解答MySQL的SQL语句练习题(一)。 1. 创建一个名为“test”的数据库。 ``` CREATE DATABASE test; ``` 2. 在“test”数据库中创建一个名为“student”的数据表，包含以下字段：学号（id，整型，主键...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值