Mysql数据库第三章高级查询上机

--上机练习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

#删除出生日期字段
alter table tb_preson drop bornDate

#添加出生日期
alter table tb_preson add bornDate date null

#修改字段名称
alter table tb_preson CHANGE name id VARCHAR(50) not null

--上机练习2
alter table result add CONSTRAINT FK_student_result FOREIGN KEY(studentNo)
REFERENCES student(studentNo);

--上机练习3
/*科目表数据*/
select * from subject
insert into subject(subjectName,classHour,gradeid)
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

/*成绩表数据*/
select * from result;
insert into result(studentNo,subjectNo,examDate,studentResult)
VALUES(10002,1,'2021-8-7',54),(10003,1,'2021-8-7',124),(10004,2,'2021-8-7',74);

/*student表数据*/
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10000','123','郭靖','男','1','13645667783','天津市河西区','1990-09-08 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10001','123','李文才','男','1','13645667890','地址不详','1994-04-12 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10002','123','李斯文','男','1','13645556793','河南洛阳','1993-07-23 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10003','123','张萍','女','1','13642345112','地址不详','1995-06-10 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10004','123','韩秋洁','女','1','13812344566','北京市海淀区','1995-07-15 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10005','123','张秋丽','女','1','13567893246','北京市东城区','1994-01-17 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10006','123','肖梅','女','1','13563456721','河北省石家庄市','1991-02-17 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10007','123','秦洋','男','1','13056434411','上海市卢湾区','1992-04-18 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('10008','123','何睛睛','女','1','13053445221','广州市天河区','1997-07-23 00:00:00',NULL,NULL);
insert into `student` (`studentNo`, `loginPwd`, `studentName`, `sex`, `gradeId`, `phone`, `address`, `bornDate`, `email`, `identityCard`) values('20000','123','王宝宝','男','2','15076552323','地址不详','1996-06-05 00:00:00',NULL,NULL);

--上机练习4
#将学生表中的学号为20000的学生修改邮箱和密码
SELECT * FROM STUDENT
update student set email='sum@163.com',loginPwd='123456' where studentNo=20010;
#将科目表中课时大于200的-10
select * from subject
update `subject` set  classHour=classHour-10 where classHour>200 AND subjectNo=1;
#将所有年级编号为1的学生信息保存到新表 
create table student_grade1(select studentName,sex,bornDate,phone from student where gradeId=1) 

--上机练习5
#查询2016-2-17考试前5的学生
select subjectNo,examDate,studentResult from result 
WHERE examDate='2016-2-17'
ORDER BY studentResult desc
limit 5 
#将所有女学生按年级从大到小排序,从2条记录到6条
select * from student where sex = '女'
ORDER BY bornDate 
limit 1,6
#按出生日期统计学生人数 将各组在人数达到2人的年份和人数显示出来
select year(bornDate),count(*)FROM student
GROUP BY YEAR(bornDate)
HAVING count(*)>=2
#查询参加2016年17日考试的所有学员的分
SELECT max(studentResult) as 最高分,min(studentResult) as 最低,avg(studentResult)as 平均分 FROM result

--上机练习6
#查询参最近一次Logic Java考试成绩的学生
select max(studentResult)as 最高分, min(studentResult)as 最低分 from result
where examDate =(
  select max(examDate) from result where subjectNo=(
        select subjectNo from `subject` where subjectName= "Logic Java"
    )
)AND subjectNo =(
 select subjectNo from `subject` where subjectName= "Logic Java"
)

--上机练习7
#使用IN关键字的子查询出来查询第一学期s1学期开设的课程
select * from `subject`
where gradeID= (
    select gradeID from grade where gradeName = 'S1'
)

--上机练习8
-- 查询未参加“HTML”课程最近一次考试的在读学生名单
select * from student where studentNo not in(
 select studentNo from result where examDate=(
     select max(examDate) from result where subjectNo=(
       SELECT subjectNo FROM `subject` WHERE subjectName ="HTML"
     )
 )AND subjectNo =(
     SELECT subjectNo FROM `subject` WHERE subjectName ="HTML"
 )
)