安徽工程大学数据库实验五代码

实验五的代码可以自取，如果又不对的地方还请大佬指出，我只是个蒟蒻，hh实验内容select语句的练习，不多逼逼，直接上题目，上代码

首先是创建test数据库，以及相关表格的导入

create database test;
use test;
create table Student
( Sno char(7) not null primary key,
  Sname char(10)not null,
  Ssex char(2) not null check(Ssex='男' or Ssex='女') default('男'),
  Sage smallint check(Sage>=14 and Sage<=65),
  Clno char(5) not null,
  );
  create table Course
  ( Cno char(1) not null primary key,
    Cname char(20) not null,
	Credit smallint check(Credit>=1 and Credit<=6)
	)
	create table Class
	( Clno char(5) not null primary key,
	Speciality char(20) not null,
	Inyear datetime not null,
	Number smallint not null check(Number>=1 and Number<=60),
	Monitor char(7)
	)
	create table Cj
	(Sno char(7) not null,
	Cno char(1),
	Grade decimal(4,1) check(Grade>0 and Grade<100),
	primary key(Sno,Cno)
	);
	alter table Course add unique(Cname);
	insert into Student values
	 ('2000101','李勇','男',20,'00311'),
	 ('2000102','刘诗曼','女',19,'00311'),
	 ('2000103','王一鸣','男',20,'00311'),
	 ('2000104','张婷婷','女',21,'00311'),
	 ('2001101','李勇敏','女',19,'00311'),
	 ('2001102','贾阿东','男',22,'00311'),
	 ('2001103','陈宝玉','男',20,'00311');
	 insert into Course values
	  ('1','数据库',4),
	  ('2','离散数学',3),
	  ('3','管理信息系统',2),
	  ('4','操作系统',4),
	  ('5','数据结构',4),
	  ('6','数据处理',2),
	  ('7','C语言',4);
	  insert into Class values
	  ('00311','计算机软件','2000-01-01',45,'2000101'),
	  ('00312','计算机应用','2000-01-01',42,'2000103'),
	  ('01311','计算机软件','2000-01-01',39,'2000103');
	  insert into Cj values 
	  ('2000101','1',92),
	   ('2000101','3',88),
	    ('2000101','5',86),
		 ('2000102','1',78),
		  ('2000102','6',55),
		   ('2001101','2',70),
		    ('2001101','4',65),
			 ('2001102','2',80),
			  ('2001102','4',90),
			   ('2001102','6',83);

(1)，查询姓名中第二个字为“勇”的男学生的姓名和班级。

select top 1 Sno,Clno from
(select top 2 Sno,Clno from Student where Sname like '_勇%' 
order by Sno asc )
as ss order by ss.Sno desc

(2)查询成绩为68，78，88和98的选修记录。

select Sno from Cj where Grade in(68,78,88,98);

(3)查询选修了“4”号课程没有成绩的学生学号。ps：表格的数据没有符合题目要求的所以我编造了一个数据，保证查询时是有输出的

insert into Cj values (2000102,4,null);
select Sno from Cj where Cno=4 and Grade is null;

（4）查询“00311”班所有女生的学号、姓名和出生年月。

select Sno,Sname,2022-Sage from Student where Ssex='女';

（5）查询“李勇敢”同学的班长姓名。

where Student.sno in 
(select monitor from Class,Student where Student.clno=Class.clno 
and Sname='李勇敏');

（6）查询“2001102”的选课门数

select count(cno) from cj 
where sno='2001102';

（7）统计学生表的班级数

select count(distinct Clno) from Student;

（8）查询“00311”班级每位同学的课程平均分

select Sno,avg(Grade) from Cj 
where Sno in (select Sno from Student where Clno='00311') 
group by Sno;

（9）查询哪些学生最低分大于70，最高分小于90，输入他们的学号

select Sno from Cj
group by Sno having min(Grade)>70 and max(Grade)>90;

（10）计算Student×Cj×Course表的学生信息。ps：老师说这一题最简单了，我觉得这题好难

select Student.Sno,Sname,Ssex,Sage,Clno,Course.Cno,Cname,Credit
from Student,Cj,Course 
where Student.Sno=Cj.Sno and Course.Cno=Cj.Cno;

（11）以clno升序、sage降序列出Student表的学生信息。

select Sno,Clno,Sage
from Student
order by  Sage desc,Clno asc;

（12）列出成绩高于学号为“2000101”、课程号为“3”的成绩的所有选课记录。

select Grade,Cno,Sno
from Cj
where Grade > 
			(
			select Grade
			from Cj
			where Sno = '2000101' and Cno = 3
			);

（13）查询和“张婷婷”同学在同一班级的学生信息。

where Clno=  (select Clno from Student where Sname='张婷婷');

（14）查询不及格课程在三门及以上的同学。ps:没有符合题意的数据，所以我又编造了一些数据插入表格中

update Cj set Grade=40 where Sno='2000102' and Cno=4;
update Cj set Grade=40 where Sno='2000102' and Cno=1;
select  Sno ,count(Cno)
from Cj
where Grade < 60 
group by (Sno)
having count(Cno) >= 3;

（15）*查询选修了目前Course中所有课程的同学。

select *
from Student
where not exists
 (select *  from Course where not exists
   (select * 
    from Course 
     where Sno=Student.Sno
      and Cno=Course.Cno));

  3完成带有子查询的insert、update、delete的任务，预先写好相应的命令。

首先需要创建表格

create table Score 
( Sno char(7) not null primary key,
  Sname char(10)not null,
  Ssex char(2) not null check(Ssex='男' or Ssex='女') default('男'),
  Sage smallint check(Sage>=14 and Sage<=65),
  Clno char(5) not null,
  avgscore int not null
  );

     （16）对每位同学，求平均成绩，并把结果存入新建立的表中。

select Sno,avg(Grade) from Cj 
where Sno in (select Sno from Student) 
group by Sno;

     （17）将班级号为“01312”班级的所有女学生的成绩加五分。

insert into Score values
('2000101','李勇','男',20,'00311',88),
 ('2000102','刘诗曼','女',19,'00312',45),
  ('2001101','李勇敏','女',19,'00312',67),
   ('2001102','贾阿东','男',22,'00311',84);
update Score set avgscore+=5 where Clno='00312' and Ssex='女';

     （18）删除“01311”班级的所有学生的成绩记录。

 delete from Score where Clno='00311';