mysql数据库习题2

摸鱼怪_my
483 阅读9分钟

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第6天，点击查看活动详情。
今天完成了数据库实验2，这一节内容很基础，大家快来试试能不能完成吧。
这里是表
course表
image.png
student表
image.png
workk表
image.png
三表对应关系

image.png
对于怎么建表，导入数据默认大家会了。
好吧，还是放出来

建表，插入数据

## 建表，插入数据
/*建立数据库*/
drop database if exists scDB; /*如果存在scDB则删除*/

create database scDB; /*建立库scDB*/
use scDB; /*使用数据库scDB*/

/*建立学生表*/
DROP TABLE IF EXISTS Student;
CREATE TABLE Student (
  sNo	char(4)  NOT NULL  COMMENT ';学号', 
  sName	varchar(20) NOT NULL COMMENT';姓名',          
  ssex 	 char(1) NULL COMMENT';性别' DEFAULT '男',          
  classname VARCHAR(20) not NULL COMMENT';专业班级名',          
 birthday  datetime NULL COMMENT';出生日期', 
 tell_phone VARCHAR(20) COMMENT';联系电话',
  primary key (sNo)
); 


/*建立课程表*/
DROP TABLE IF EXISTS Course;
CREATE TABLE Course (
  cNo	  VARCHAR(20) NOT NULL COMMENT ';课程号',  
  cName	  VARCHAR(20) NOT NULL  COMMENT ';课程名', 
  credit   float NOT NULL COMMENT ';学分数',   
  period    int NOT NULL COMMENT ';学时数',
  cteacher  VARCHAR(20)	NOT NULL COMMENT '任课教师',       
  primary key (cNo)
) ;
/*建立作业表*/
DROP TABLE IF EXISTS workk;
CREATE TABLE workk (
  cNo        VARCHAR(20)		  NOT NULL , 
  sNo      VARCHAR(20) NOT NULL , 

 swork1 TINYINT not null,
  check( swork1 between 0 and 100) ,
 swork2 TINYINT not null,
  check( swork2 between 0 and 100) , 
 swork3 TINYINT not null,
  check( swork3 between 0 and 100) , 

/* 元组级完整性约束条件，主码由三个属性构成*/
 primary key(sNo,cNo),
  /* 表级完整性约束条件，sNo是外码，被参照表是Student */
 foreign key(sNo) references student(sNo),
  /* 表级完整性约束条件，cNo是外码，被参照表是Course*/
 foreign key(cNo) references course(cNo)
); 

/*导入Course表信息*/
insert into Course values('K001','计算机图形学',2.5,40,'胡晶晶');
insert into Course values('K002','计算机应用基础',3,48,'任泉');
insert into Course values('K006','数据结构',4,64,'马跃先');
insert into Course values('M001','政治经济学',4,64,'孔繁新');
insert into Course values('S001','高等数学',3,48,'赵晓尘');

-- '1998-12-21 00:00:00'
/*导入Student表信息*/
INSERT into Student values('0433','张艳','女','生物04','1986-9-13 00:00:00',null);
INSERT into Student values('0496','李越','男','电子04','1984-2-23 00:00:00','1381290****');
INSERT into Student values('0529','赵欣','男','会计05','1984-1-27 00:00:00','1350222****');
INSERT into Student values('0531','张志国','男','生物05','1986-9-10 00:00:00','1331256****');
INSERT into Student values('0538','于兰兰','女','生物05','1984-2-20 00:00:00',	'1331200****');
INSERT into Student values('0591','王丽丽','女','电子05','1984-3-20 00:00:00','1332080****');
INSERT into Student values('0592','王海强','男','电子05','1986-11-1 00:00:00',null);

/*插入作业表*/
INSERT into workk VALUES('K001','0433',60,75,75);
INSERT into workk VALUES('K001','0529',70,70,60);
INSERT into workk VALUES('K001','0531',70,80,80);
INSERT into workk VALUES('K001','0591',80,90,90);
INSERT into workk VALUES('K002','0496',80,80,90);
INSERT into workk VALUES('K002','0529',70,70,85);
INSERT into workk VALUES('K002','0531',80,80,80);
INSERT into workk VALUES('K002','0538',65,75,85);
INSERT into workk VALUES('K002','0592',75,85,85);
INSERT into workk VALUES('K006','0531',80,80,90);
INSERT into workk VALUES('K006','0591',80,80,80);
INSERT into workk VALUES('M001','0496',70,70,80);
INSERT into workk VALUES('M001','0591',65,75,75);
INSERT into workk VALUES('S001','0531',80,80,80);
INSERT into workk VALUES('S001','0538',60,80,80);

一、简单查询和连接查询*

1.查询各位学生的学号、班级和姓名。
2.查询课程的全部信息。
3.查询数据库中有哪些专业班级。
4.查询学时数大于60的课程信息。
5.查询在1986年出生的学生的学号、姓名和出生日期。
6.查询三次作业的成绩都在80分以上的学号、课程号。
7.查询姓张的学生的学号、姓名和专业班级。
8.查询05级的男生信息。
9.查询没有作业成绩的学号和课程号。
10.查询学号为0538的学生的作业1总分。
11.查询选修了K001课程的学生人数。
12.查询数据库中共有多少个班级。
13.查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。

参考答案（ps俺自己做的，可能有错误）

-- 1.	查询各位学生的学号、班级和姓名。
select sNo,classname,sName from student;
-- 2.	查询课程的全部信息。
select * from course;
-- 3.查询数据库中有哪些专业班级。
select classname from student;
-- 4.查询学时数大于60的课程信息。
select *from course where period>60;
-- 5.查询在1986年出生的学生的学号、姓名和出生日期。
select sNo,sName,birthday from student where YEAR(birthday)=1986;

-- 6.查询三次作业的成绩都在80分以上的学号、课程号。
select sNo,cNo from workk where swork1>=80 and swork2>=80 and swork3>=80;
-- 7.查询姓张的学生的学号、姓名和专业班级。
select sNo,sName,classname from student where sName like '张%';
-- 8.查询05级的男生信息。
select * from student where ssex='男' and classname like '%05';
-- 9.查询没有作业成绩的学号和课程号。
select sNo,cNo from workk where swork1=null or swork2=null or swork3=null;
-- 10.查询学号为0538的学生的作业1总分。
select sum(swork1) from workk where sNo='0538';
-- 11.查询选修了K001课程的学生人数。
select COUNT(*) from workk where cNo='K001' ;
-- 12.查询数据库中共有多少个班级。
select classname,COUNT(*)from student GROUP BY classname;
-- 13.查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
--  (select sNo,sum(swork1),sum(swork2),sum(swork3),COUNT(cNo)from workk GROUP BY sNo having COUNT(cno)>=3)
select sNo,avg(swork1),avg(swork2),avg(swork3) from workk  GROUP BY sNo having COUNT(cNo)>=3;
-- 14.查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。
select s.sNo,s.sName,c.cNo from student s,course c,workk w where s.sNo=w.sNo and c.cNo=w.cNo and s.sName='于兰兰';

二、复杂查询*

1.查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
2.查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）
3.查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
4.询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。

参考答案（ps俺自己做的，可能有错误）

1.查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
select * from student where classname in(select classname from student where sName='张志国') and sName !='张志国';

select s.sNo,s.classname,s.sName from student s,(select * from student where sName='张志国') ss where s.classname=ss.classname and s.sName !='张志国';
-- 2.查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
select * from course where period>(select period from course where cName='计算机应用基础');
select c.cNo,c.cName,c.period from course c,(select * from course where cName='计算机应用基础') b
where c.period>b.period;
-- 3.查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
select s.sNo,s.sName from student s,workk where s.sNo=workk.sNo and workk.cNo='K002';
select * from student where sNo in(select sNo from workk where cNo='K002');
select * from student where EXISTS(select * from workk where cNo='K002');
-- 4.查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。
select * from workk where cNo in(select cNo from workk where cNo!='K001' and cNo!='M001');

三、数据操纵*

1.在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
2.将所有课程的学分数变为原来的两倍。
3.删除张乐的信息。

参考答案（ps俺自己做的，可能有错误）

1.在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
-- INSERT into Student values('0433','张艳','女','生物04','1986-9-13 00:00:00'	,null);
INSERT into Student(sNo,sName,ssex,classname) VALUES('0593','张乐','男','电子05');
-- 2.将所有课程的学分数变为原来的两倍。
UPDATE course  set credit=credit*2;
-- 3.删除张乐的信息。
DELETE from student where sName='张乐';

四、视图*

1.创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。
2.创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。
3.创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。

参考答案

-- 1.创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。
drop view student05;
CREATE view student05 as select sNo 学号,sName 姓名,ssex 性别,classname 专业班级,birthday 出生日期  from student where classname='电子05';
-- 2.创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。
drop view stu05;
create view stu05 as select s.sNo 学号,s.sName 姓名,c.cNo,c.cName 课程名,swork1 作业1成绩,swork2 作业2成绩,swork3 作业3成绩 from workk,student s,course c where c.cNo=workk.cNo and s.sNo=workk.sNo and classname='生物05';
-- 3.创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。
drop view workavg;
create view workavg as select sNo 学号,avg(swork1) 作业1平均成绩,avg(swork2) 作业2平均成绩,avg(swork3) 作业3平均成绩 from workk GROUP BY sNo;

不知道大家会不会做这些题呢？希望可以帮助大家巩固mysql知识

avatar
文章
阅读
粉丝