MySql数据库练习

 建表，增删改，查询下列问题

 学生表

科目表

成绩表

create database day1101;
use day1101;
-- 创建表
-- 学生表
create table StudentForm(
	studentNo varchar(20) primary key,
	studentName varchar(20),
	loginPassword varchar(50),
	sex varchar(5),
	phone varchar(200),
	address varchar(20) default '宿舍',
	born datetime,
	email varchar(200),
	gradeId int(5)
);
show create table StudentForm;

	CREATE TABLE `studentform` (
  `studentNo` varchar(20) NOT NULL,
  `studentName` varchar(20) DEFAULT NULL,
  `loginPassword` varchar(50) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `phone` varchar(200) DEFAULT NULL,
  `address` varchar(20) DEFAULT '宿舍',
  `born` datetime DEFAULT NULL,
  `email` varchar(200) DEFAULT NULL,
  `gradeId` int DEFAULT NULL,
  PRIMARY KEY (`studentNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into StudentForm values
('s1001','张三','zhagnsan','男','13022225555',default,'1994-01-01',null,1),
('s1002','李四','lisi','男','13266669999',default,'1991-07-07',null,1),
('s1003','张丽','123456','女','13099999999',default,'1992-05-06',null,1),
('s1004','王磊','123456','男','15066668888','西安','1991-06-07',null,1),
('s1005','张丹','zhangdan','女','15036998888',default,'1992-06-07',null,1),
('s1006','李克','123456','男','15022226669','西安市雁塔区','1993-12-01','liliang@126.com',1),
('s1007','李丹','123456','女','15036699965',default,'1992-11-11','201612011419478126com',1),
('s1008','王亮','123456','男','15022223333','西安市','1993-12-02',null,2),
('s1009','赵龙','123456','男','13022229999','西安市','1992-06-07',null,2),
('s1010','徐丹','123456','女','15899996666',default,'1993-05-06',null,2);

-- 科目表
create table SubjectForm (
	subjectId int(5) primary key auto_increment,
	subjectName varchar(200) not null,
	classHour int(10) not null,
	gradeId int(5)
);
show create table SubjectForm;
CREATE TABLE `subjectform` (
  `subjectId` int NOT NULL AUTO_INCREMENT,
  `subjectName` varchar(200) NOT NULL,
  `classHour` int NOT NULL,
  `gradeId` int DEFAULT NULL,
  PRIMARY KEY (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into SubjectForm values 
(null,'MySQL深入',65,1),
(null,'C语言面向过程',45,1),
(null,'计算机基本原理',70,1),
(null,'毛邓概论',61,1),
(null,'英语',55,1),
(null,'jap',40,2),
(null,'数据结构',60,2),
(null,'oracle',65,2),
(null,'计算机网络',50,2);

-- 成绩表
create table AchievementForm(
	id int(10) primary key auto_increment,
	studentNo varchar(20),
	subjectId int(5),
	studentScore varchar(10),
	examDate datetime
);


alter table AchievementForm add constraint fk_StudentForm_AchievementForm foreign key (studentNo) references StudentForm(studentNo);
alter table AchievementForm add constraint fk_SubjectForm_AchievementForm foreign key (subjectId) references SubjectForm(subjectId);
show create table AchievementForm;

CREATE TABLE `achievementform` (
  `id` int NOT NULL AUTO_INCREMENT,
  `studentNo` varchar(20) DEFAULT NULL,
  `subjectId` int DEFAULT NULL,
  `studentScore` int DEFAULT NULL,
  `examDate` datetime DEFAULT NULL,
  
	PRIMARY KEY (`id`),
  KEY `fk_StudentForm_AchievementForm` (`studentNo`),
  KEY `fk_SubjectForm_AchievementForm` (`subjectId`),
  CONSTRAINT `fk_StudentForm_AchievementForm` FOREIGN KEY (`studentNo`) REFERENCES `studentform` (`studentNo`),
  CONSTRAINT `fk_SubjectForm_AchievementForm` FOREIGN KEY (`subjectId`) REFERENCES `subjectform` (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert into AchievementForm values
(null,'s1001',1,'80','2015-07-01'),
(null,'s1002',1,'40','2015-07-01'),
(null,'s1001',2,'10','2015-07-01'),
(null,'s1002',2,'20','2015-07-01'),
(null,'s1003',1,'60','2015-07-01'),
(null,'s1001',3,'82','2015-07-03'),
(null,'s1001',4,'90','2015-07-03'),
(null,'s1001',5,'75','2015-07-01'),
(null,'s1002',3,'65','2015-07-03'),
(null,'s1002',4,'35','2015-07-03'),
(null,'s1002',5,'87','2015-07-01'),
(null,'s1003',2,'65','2015-07-01'),
(null,'s1003',3,'45','2015-07-03'),
(null,'s1003',4,'92','2015-07-03'),
(null,'s1003',5,'55','2015-07-01'),
(null,'s1004',1,'65','2015-07-01'),
(null,'s1004',2,'85','2015-07-01'),
(null,'s1004',3,'45','2015-07-03');

-- 数据修改与删除

update StudentForm set address = '西安科技二路' where address = '西安市'; 
-- 1.将地址是西安市的更新为 西安科技二路
update StudentForm set email = '  ' where studentNo = 's1001';
-- 2.将S1001的email修改为空的字符串
update SubjectForm set subjectName = 'java基础',classHour = 60,gradeId = 2 where subjectId = 2;
-- 3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
update AchievementForm set studentScore = studentScore + 5 where studentNo = 's1001' and subjectId = 2;
-- 4.将S1001 ，课程编号为 2 的成绩 提高 5分
update AchievementForm set studentScore = 60,examDate = '2015-10-10' where  studentNo = 's1004' and subjectId = 3;
-- 5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
update AchievemnetForm set studentScore val where studentNo = 's1004' and subjectId = 2;
-- 6.经核实 S1004 课程编号为2 的 缺考
delete from SubjectForm where subjectName = '计算机网络';
-- 7.将计算机网络课程删除掉

-- 数据查询

select * from StudentForm where gradeId = 1;
-- 1.查询全部一年级的学生信息
select studentName,phone from StudentForm where gradeId = 2;
-- 2.查询全部二年级的学生的姓名和电话。
select * from StudentForm where gradeId = 1 and sex = '女';
-- 3.查询全部一年级女同学的信息。
select * from SubjectForm where classHour > 60;
-- 4.查询课时超过60的科目信息。
select * from SubjectForm where gradeId = 2;
-- 5.查询二年级的科目名称
select studentName,address from StudentForm where gradeId = 2 and sex = '男';
-- 6.查询二年级男同学的姓名和住址。
select studentName,gradeId from StudentForm where email is null;
-- 7.查询无电子邮件的学生姓名和年级信息。
select studentName,gradeId from StudentForm where born >= 1992-01-01;
-- 8.查询出生日期在1992年之后的男学生姓名和年级信息。'
select studentNo,studentScore from AchievementForm where examDate = '2015-07-03' and subjectId = 3;
-- 9.参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
select * from studentForm where gradeId = 1 order by born;
-- 10、按照出生日期查询一年级的学生信息。
select * from AchievementForm where subjectId = 1 order by studentScore desc;
-- 11.按成绩由高到低的次序查询参加编号为1的科目考试信息。
select * from AchievementForm where examDate = '2015-07-01' order by studentScore desc limit 2;
-- 12.查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
select subjectName,classHour from SubjectForm order by classHour desc limit 1;
-- 13.查询课时最多的科目名称及课时。
select gradeId,studentName from StudentForm order by born desc limit 1;
-- 14.查询年龄最小的学生所在的年级及姓名。
select subjectName from SubjectForm where subjectId = (select subjectId from AchievementForm order by studentScore limit 1);
-- 15.查询考试的最低分出现在哪个科目
select * from AchievementForm where studentNo = 's1001' order by examDate;
-- 16.查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
select * from StudentForm where (year(curdate()) - substring(born from 1 for 4)) > 25;
-- 17.查询年龄超过25周岁的学生信息。
select * from StudentForm where (substring(born from 6 for 6)) = 1;
-- 18.查询1月份过生日的学生信息
select studentName,gradeId from StudentForm where (substring(born from 6 for 7)) = month(curtime()) and (substring(born from 9 for 10)) = day(curtime());
-- 19.查询今天过生日的学生姓名及所在年级。

-- 20.新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
select studentName,phone,address from StudentForm where address like '%雁塔区%';
-- 21.查询住址为“雁塔区”的学生姓名、电话、住址
select * from SubjectForm where subjectName like '计算机%';
-- 22.查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select studentName,address,phone from StudentForm where phone like '130%';
-- 23.查询电话中含有以“130”开头的学生姓名，住址和电话。
select studentNo,studentName,address from StudentForm where studentName like '赵%';
-- 24.查询姓“赵”的学号、姓名和住址。
select count(sex) from StudentForm where sex = '女';
-- 25.统计一年级女生的总人数。
select sum(studentScore) from AchievementForm where studentNo = (select studentNo from StudentForm where studentName = '李四');
-- 26.查询李四总成绩
select sum(studentScore) from AchievementForm where studentNo = 's1003';
-- 27.学号为s1003的学生所有课总成绩
select avg(studentScore) from AchievementForm where studentNo = 's1003';
-- 28.学号为s1003的学生考试的平均分。
select max(studentScore),min(studentScore),avg(studentScore) from AchievementForm where subjectId = (select subjectId from SubjectForm where subjectName = 'MySQL深入');
-- 29.查询一年级的科目“Mysql”的最高分、最低分、平均分。
select gradeid,sum(classhour) from SubjectForm group by gradeId order by sum(classhour);
-- 30.查询每个年级的总学时数，并按照升序排列。
select studentNo,avg(studentScore) from AchievementForm group by studentNo;
-- 31.查询每个参加考试的学员的平均分。（Group by 学号）
select subjectId,avg(studentScore) from AchievementForm group by subjectId order by avg(studentScore) desc;
-- 32.查询每门课程的平均分，并按照降序排列。（group by 课程）
select studentNo,sum(studentScore) from AchievementForm group by studentNo order by avg(studentScore) desc;
-- 33.查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
select gradeId,avg((year(curdate())-substring(born from 1 for 4))) from StudentForm;
-- 34.查询一年级的平均年龄。
select gradeId,count(1) from StudentForm where address like '%西安%' group by gradeId;
-- 35.查询每个年级西安地区的学生人数。

-- 36.查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列

-- 37.查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
select studentName,gradeId,phone from StudentForm
-- 38.查询学生姓名、所属年级名称及联系电话。
select subjectName,gradeId,classhour from SubjectForm where gradeId=1;
-- 39.查询年级编号为1的科目名称、年级名称及学时。
select  ( select s.studentname from StudentForm s where s.studentno=r.studentno ), r.studentScore,r.examdate from AchievementForm r where subjectid=1;
-- 40.查询参加科目编号为1的考试的学生姓名、分数、考试日期。

-- 41.查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
select j.subjectName as "科目名称", r.studentNo as"学号", r.studentScore as "分数" from SubjectForm as j left outer join AchievementForm as r on j.subjectid=r.subjectid;
-- 42.查询所有科目的参考信息（某些科目可能还没有被考试过）
select j.subjectName as "科目名称" from AchievementForm as r right outer join SubjectForm as j on j.subjectId=r.subjectId where r.studentScore is null and r.studentNo is null;
-- 43.查询没有被考过的科目信息。