MySQL数据库综合练习一

最新推荐文章于 2024-01-21 23:45:38 发布
最新推荐文章于 2024-01-21 23:45:38 发布
阅读量485 收藏 1
点赞数 1
分类专栏： SQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_52147099/article/details/126550040
版权

文章目录


数据准备： 

 drop table if exists student;
 drop table if exists teacher;
 drop table if exists course;
 drop table if exists sc;

create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);

create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);

create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);

create table sc(
sno varchar(10),
cno varchar(10),
score decimal(8,2),
constraint pk_sc primary key (sno,cno)
);


/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
 
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
 
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
 
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003',59);
insert into sc values ('s001','c004',78.9);
insert into sc values ('s002','c004',80.9);
insert into sc values ('s003','c005',81.9);
insert into sc values ('s004','c005',60.9);
insert into sc values ('s001','c006',82.9);
insert into sc values ('s002','c006',72.9);
insert into sc values ('s003','c007',81.9);
insert into sc values ('s001','c007',59); 
insert into sc values ('s005','c001',80.9);
insert into sc values ('s006','c001',81.9);
insert into sc values ('s007','c001',90.9);
insert into sc values ('s004','c002',75);
insert into sc values ('s005','c002',71.2);
insert into sc values ('s006','c002',85.9);
insert into sc values ('s007','c003',59);
insert into sc values ('s004','c004',77.9);
insert into sc values ('s005','c004',84.9);
insert into sc values ('s006','c005',82.9);
insert into sc values ('s007','c005',67.9);
insert into sc values ('s004','c006',62.9);
insert into sc values ('s006','c006',92.9);
insert into sc values ('s007','c007',35.9);
insert into sc values ('s008','c007',69);

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

select s1.sno from sc s1,sc s2
where s1.sno = s2.sno
and s1.cno = 'c001'
and s2.cno = 'c002'
and s1.score>s2.score;

2、查询平均成绩大于60 分的同学的学号和平均成绩

select  sno,avg(score)  from  sc  GROUP BY  sno  HAVING AVG(score)>60

3、查询所有同学的学号、姓名、选课数、总成绩

select s1.sno,s1.sname,count(s2.sno),sum(s2.score) from student s1,sc s2
where s1.sno = s2.sno 
GROUP BY s2.sno

4、查询姓“刘”的老师的个数

select count(tname) from teacher
where tname like '刘%'

5、查询没学过“谌燕”老师课的同学的学号、姓名

SELECT DISTINCT
	s1.sno,
	s1.sname 
FROM
	student s1 
WHERE
	s1.sno NOT IN (
	SELECT
		sc.sno 
	FROM
		sc
		JOIN course c ON c.cno = sc.cno
		JOIN teacher t ON c.tno = t.tno 
	WHERE
		t.tname = '谌燕' 
	)

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

select s1.sno,s1.sname from student s1,sc s2
where s1.sno = s2.sno
and s2.cno in ('c001','c002')
and s2.cno  = 'c002'

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名

SELECT DISTINCT
	s1.sno,
	s1.sname 
FROM
	student s1 
WHERE
	s1.sno IN (
	SELECT
		sc.sno 
	FROM
		sc
		JOIN course c ON c.cno = sc.cno
		JOIN teacher t ON c.tno = t.tno 
	WHERE
		t.tname = '谌燕' 
		GROUP BY sc.sno
		HAVING count(sc.cno) = (
		SELECT COUNT(c.cno)
		FROM course c
		left JOIN teacher t ON t.tno = c.tno
		WHERE t.tname = '谌燕'
		)
	)

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

select s1.sno,s1.sname from student s1,sc s2,sc s3
where s1.sno = s2.sno
and s2.sno = s3.sno
and s2.cno = 'c001'
and s3.cno = 'c002'
and s2.score < s3.score

9、查询所有课程成绩小于60 分的同学的学号、姓名

SELECT DISTINCT
	s.sno,
	s.sname 
FROM
	student s,
	sc 
WHERE
	sc.sno = s.sno 
	AND sc.score < 60

10、查询没有学全所有课的同学的学号、姓名

SELECT DISTINCT
	s.sno,
	s.sname 
FROM
	student s
	JOIN sc ON s.sno = sc.sno 
GROUP BY
	sc.sno 
HAVING
	count( sc.sno ) != (
	SELECT
		count( c.cno ) 
	FROM
		course c 
	)

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

SELECT DISTINCT
	s1.sno,
	s1.sname 
FROM
	student s1
	JOIN sc ON s1.sno = sc.sno 
WHERE
	sc.cno IN ( SELECT sc.cno FROM sc WHERE sc.sno = 's001' ) 
	AND sc.sno != 's001'

12、查询至少学过学号为“s001”同学所有课的其他同学学号和姓名

select student.sno,sname
from student left join sc on student.sno=sc.sno
-- 查找cno属于s002的集合
WHERE cno in(
select cno
from sc
where sno='s002')
GROUP BY student.sno
-- 统计个数
HAVING COUNT(cno)>=(
select count(cno)
from sc
where sno='s002') and sno!='s002';

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩

update  sc s1 join (select sc.cno,avg(sc.score) ascore from sc 
	 where sc.cno in (select c.cno from teacher t,course c where c.tno = t.tno and t.tname = '谌燕')
	 group by sc.cno) s2 
	 set s1.score = s2.ascore where s1.cno = s2.cno

14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名

SELECT DISTINCT
	s1.sno,
	s1.sname 
FROM
	student s1,
	sc 
WHERE
	s1.sno = sc.sno 
	-- 有s001选择的课程
	AND sc.cno IN ( SELECT sc.cno FROM sc WHERE sc.sno = 's001' ) 
	-- 没有s001没有选择的课程
	AND sc.cno NOT IN (
	SELECT DISTINCT
		sc.cno 
	FROM
		sc 
	WHERE
	sc.cno NOT IN ( SELECT sc.cno FROM sc WHERE sc.sno = 's001' )) 
	-- 排除自己
	AND sc.sno != 's001' 
GROUP BY
	sc.sno 
HAVING -- 和s001选择的课程数量相同
	count( sc.sno ) = (
	SELECT
		count( sc.cno ) 
	FROM
		sc 
	WHERE
	sc.sno = 's001')

15、删除学习“谌燕”老师课的SC 表记录

DELETE from sc where cno
IN (SELECT cno from course where tno in (select tno from teacher where tname='谌燕'))

16、向SC 表中插入一些记录，这些记录要求符合以下条件：没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩

insert into sc(sno,cno,score) values ('s007','c002',78.30),('s008','c002',78.30)

	-- 没有上过编号“c002”课程的同学学号
	select distinct sc.sno from sc 
	where sc.sno not in(
	select distinct sc.sno from sc 
	where sc.cno = 'c002')

	-- “c002”号课的平均成绩
	select avg(sc.score) from sc
	group by sc.cno
	having sc.cno = 'c002'

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT
	sc.cno '课程ID',
	max( sc.score ) '最高分',
	min( sc.score ) '最低分' 
FROM
	sc 
GROUP BY
	sc.cno

18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

select sc.cno '课程ID',c.cname '课程名称',avg(sc.score) '平均成绩', 
((select count(s.sno) from sc s where s.score >= 60 and s.cno = sc.cno )/(select count(s.sno) from sc s where s.cno = sc.cno)) '及格率'
from sc,course c
where sc.cno = c.cno
group by sc.cno,c.cname
order by avg(sc.score),'及格率' desc

19、查询不同老师所教不同课程平均分从高到低显示

select sc.cno '课程ID',avg(sc.score) avgScore,c.cname '课程名称',t.tname '老师名称' from sc,course c,teacher t
where sc.cno = c.cno
and c.tno = t.tno
group by sc.cno,c.cname,t.tname
order by avgScore desc

20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select  s1.cno '课程ID',c.cname,count(s1.sno),
(select count(score) from sc where sc.cno = s1.cno and sc.score <= 100 and sc.score >= 85) '[100-85]',
(select count(score) from sc where sc.cno = s1.cno and  sc.score < 85 and sc.score >= 70) '[85-70]',
(select count(score) from sc where sc.cno = s1.cno and  sc.score <70 and sc.score >=60) '[70-60]',
(select count(score) from sc where sc.score < 60 and sc.cno = s1.cno) '[<60]'
from sc s1,course c
where s1.cno = c.cno
group by s1.cno,c.cname

22、查询每门课程被选修的学生数

SELECT
	sc.cno,
	count( sc.cno ) 
FROM
	sc 
GROUP BY
	sc.cno

23、查询出只选修了一门课程的全部学生的学号和姓名

SELECT
	s1.sno,
	s1.sname 
FROM
	student s1
	JOIN sc ON s1.sno = sc.sno 
GROUP BY
	sc.sno 
HAVING
	count( sc.sno ) = 1

24、查询男生、女生人数

select ssex,count(ssex) from student
GROUP BY ssex

25、查询姓“张”的学生名单

select * from student where sname like '张%'

26、查询同名同性学生名单，并统计同名人数

select s1.* from student s1,student s2
where s1.sname = s2.sname 
and s1.ssex = s2.ssex
and s1.sno != s2.sno

select count(*),s1.sname
from student s1,student s2
where s1.sname = s2.sname 
and s1.ssex = s2.ssex
and s1.sno != s2.sno
group by s1.sname

27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是int)

select student.* from student
where sage*365 > (
select DATEDIFF('2002-1-1','1981-1-1')
) and 
sage*365 < (select DATEDIFF('2002-1-1','1981-1-1')+365)

27、1981 年出生的学生名单(注：Student 表中Sage 列的类型是int)

SELECT * 
FROM student
WHERE student.sage = (SELECT TIMESTAMPDIFF(YEAR,'2000-01-01',CURRENT_DATE))

28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT
	sc.cno,avg( sc.score ) 
FROM
	sc 
GROUP BY
	sc.cno 
ORDER BY
	avg(sc.score) asc,sc.cno desc;

29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩

select distinct s1.sno,s1.sname,avg(sc.score) from student s1
join sc on s1.sno = sc.sno
GROUP BY sc.sno
having avg(sc.score)>85

30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数

SELECT
	s1.sname,
	sc.score 
FROM
	student s1,
	sc 
WHERE
	s1.sno = sc.sno 
	AND sc.cno = ( SELECT c.cno FROM course c WHERE c.cname = 'SQL SERVER 2005' ) 
	AND sc.score < 60

31、查询所有学生的选课情况

select s1.sname,count(sc.sno) from student s1,sc
where s1.sno = sc.sno
group by sc.sno

 有可能有人没有选课

SELECT student.sno,cno
from student left join sc on student.sno=sc.sno

32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；

select distinct s1.sno,s1.sname,sc.cno,sc.score from student s1,sc
where s1.sno = sc.sno
and sc.score > 70

-- 将课程号转换为课程名

select sname,cname,score
from student left join sc on student.sno=sc.sno
left join course ON course.cno=sc.cno
where score>70

33、查询不及格的课程，并按课程号从大到小排列

select distinct sc.cno from sc
 where sc.score < 60
 order by sc.cno desc

34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名

select s1.sno,s1.sname,sc.score from student s1,sc
where s1.sno = sc.sno
and sc.cno = 'c001'
and sc.score > 80

35、求选了课程的学生人数

SELECT
	count( DISTINCT sno ) 
FROM
	sc

36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩

不分课程

select s1.sname,sc.score from student s1,sc 
where s1.sno = sc.sno
and sc.cno in (
	select c.cno from course c where c.tno = (select t.tno from teacher t where t.tname = '谌燕')
	) order by sc.score desc limit 1

37、查询各个课程及相应的选修人数

SELECT
	sc.cno,
	count( sc.sno ) 
FROM
	sc 
GROUP BY
	sc.cno

38、查询不同课程 成绩相同 的学生的学号、课程号、学生成绩

select a.sno,a.cno,a.score
from sc as a,sc as b
where a.score=b.score and a.cno!=b.cno
ORDER BY
	a.score

40、统计每门课程的学生选修人数（超过10 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select sc.cno,count(sc.sno) from sc
group by sc.cno
having count(sc.sno) > 3 
order by count(sc.sno) desc,sc.cno asc

41、检索至少选修两门课程的学生学号

select sc.sno,count(sc.cno) from sc
group by sc.sno
having count(sc.cno) > 2

42、查询全部学生都选修的课程的课程号和课程名

select sc.cno,c.cname from course c,sc
where c.cno = sc.cno
group by sc.cno,c.cname
having count(sc.cno) = (select count(sno) from student)

43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名

select s1.sname from student s1
	where s1.sname not in (select distinct s1.sname from student s1,sc
	where s1.sno = sc.sno
	and sc.cno in (select c.cno from course c where c.tno = (select t.tno from teacher t where t.tname = '谌燕')))

44、查询两门以上不及格课程的同学的学号及其平均成绩

select count(s.cno),s.sno,(select avg(sc.score) from sc where sno =s.sno) from sc s
where s.score < 60
group by sno
having count(s.cno) >= 1

45、检索“c004”课程分数小于60，按分数降序排列的同学学号

select sc.sno,sc.score from sc
where sc.cno = 'c004'
and sc.score < 80
order by sc.score desc

46、删除“s002”同学的“c001”课程的成绩

delete from sc where sno='s002' and cno = 'c002'

​

賣可樂的男孩
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL上机考试题(习题,答案).docx
01-12
MySQL上机考试题(习题,答案).docx
mysql面试题上机_MySQL面试问题
weixin_29692653的博客
01-28 399
1)什么是MySQL？MySQL是一个多线程，多用户SQL数据库管理系统，安装量超过1100万。这是世界上第二大最受欢迎和广泛使用的开源数据库。2)用MySQL编写哪种语言？MySQL是用C和C ++编写的，它的SQL解析器是用yacc编写的。3)MySQL的技术规范是什么？MySQL具有以下技术规范 -结构灵活高性能易于管理且易于使用复制和高可用性安全和存储管理4)MySQL中可能有多少触发器？...
MySQL的精选15道面试题（学会了就离入职不远了！！！）
m0_73647713的博客
07-08 1645
MySQL在面试中主要是以题目的方式出现，为此我精选了15题来进行拿捏它！！！
mysql面试题上机_12道MySQL常见的面试题
weixin_42400904的博客
01-19 165
有关事务的面试题原子性：是指事务包含所有操作要么全部成功，要么全部失败回滚。一致性：指事务必须使数据库从一个一致性状态变换成另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A 和 B 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 5000，这就是事务的一致性。隔离性：是当多...
Mysql第三章高级查询(一)上机练习
qq_58749244的博客
08-14 768
--上机练习1 create table preson( number int(4) not null comment '序号' PRIMARY KEY, name VARCHAR(50) not null comment'姓名', sex char(2) null comment'性别', bornDate datetime comment'出生日期' )comment'person结构表' #修改表名称 ALTER table preson rename tb_preson #删除出...
MySQL数据库系统及应用综合练习.doc
03-11
A: 9 B: 10 C: 128 D: 1 答案: C 【8】 在一个关系中，若有这样一个属性存在，它的值能唯一地标识关系中的每一个元组，则 称这个属性为 A: 数据项 B: 候选码 C: 主键 D: 主键值 答案: C 【9】 数据库管理系统能实现...
mysql数据库 实训 试题
12-20
适合初学mysql数据库的学生的试题，有兴趣的可以看一看
MySQL数据库系统及应用综合练习.pdf
03-01
。
MySQL数据库系统及应用综合练习.docx
03-01
。
《MySQL数据库系统及应用》综合练习.doc
03-01
《MySQL数据库系统及应用》综合练习.doc
Mysql机试题目
01-18
一个Mysql数据库的机试题目，你可以在项目中的后台把它做出来，
MySQL数据库考试试题及答案
11-01
MySQL数据库考试试题及答案，详情摘要请参见博客内容，谢谢
sql数据库上机试题三
07-02
sql数据库上机试题三。供大学生初学者用。word文档格式。
MySQL数据库笔试试题及答案（全）
02-01
MySQL数据库笔试试题及答案（全），单选题103题，多选题58题，填空题30题，编程题若干题
MySQL-50道经典sql题汇总
qq_41835813的博客
08-31 7325
建表 DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `CId` varchar(10) default NULL, `Cname` varchar(10) default NULL, `TId` varchar(10) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure fo.
mysql基础上机练习题_mysql基础练习题
weixin_35300124的博客
01-21 603
一、表关系请创建如下表，并创建相关约束二、操作表1、自行创建测试数据/*Navicat MySQL Data TransferSource Server : mysql5.7.1Source Server Version :50717Source Host : localhost:3306Source Database : crmTarget Ser...
mysql上机试题和答案_数据库上机习题和答案.doc
weixin_36253081的博客
01-27 4014
数据库上机习题和答案数据库及应用复习题一、设计题有一个[学生课程]数据库，数据库中包括三个表：学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为关键字。课程表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)...
MySQL综合练习（50道）
weixin_45890771的博客
05-15 2342
MYSQL查询练习经典50道
mysql数据库综合练习
最新发布
yunduan__的博客
01-21 351
left join 左连接的结果包括LEFT OUTER子中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。如果D为负数，这时是指定小数点左边的D位整数位为0,同时小数位均为0。“列函数对于GROUP BY子句定义的每个组各返回一个结果”，AVG函数对每个学生返回一个结果，就是每个学生的平均值。中与连接条件相匹配的数据行，组合成新记录，也就是说，在内连接查询中，只有满足。出现的别名为sc，第2次出现的别名为sc2。
spark综合性练习(spark，kafka，spark streaming，mysql)
03-16
这是一个综合性的练习，涉及到了Spark、Kafka、Spark Streaming和MySQL。Spark是一个分布式计算框架，可以用于大规模数据处理和分析。Kafka是一个分布式消息队列，用于实时数据流处理。Spark Streaming是Spark的一个扩展，可以处理实时数据流。MySQL是一个关系型数据库管理系统，用于存储和管理数据。在这个练习中，可能需要使用Spark读取Kafka中的数据，进行实时处理，并将结果存储到MySQL中。这需要对这些技术有一定的了解和实践经验。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值