大型MySQL数据库学习连续剧之千锤百炼

通过学生表、科目表、以及成绩表直接的关系，进行对数据库简单操作的练习。

学生表信息

创建表

-- 创建学生表
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)
);

插入数据

-- 对学生表插入数据
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)
);

插入数据

-- 对科目表插入数据
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
);

插入数据

-- 对成绩表插入数据
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');

连接外键

-- 对科目表插入数据
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);

简单的对表进行一些CURD操作

1. 将地址是西安市的更新为 西安科技二路

    update StudentForm set address = '西安科技二路' where(address = '西安市' or address = '西安');
   

2. 将S1001的email修改为空的字符串

   update StudentForm set eamil = ' ' where(studentNo = 's1001');
   

3. 将第二门课的名字更新为 java基础，课时为60 ，班级是二年级

   update SubjectForm set subjectName = 'java基础', classHour = 60 , gradeId = 2 where(subjectId = 2);
   

4. 将S1001 ，课程编号为 2 的成绩 提高 5分

   update AchievementForm set studentscore =studentscore + 5 where(studentNo = 's1001' and subjectId = 2);
   

5. 将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10

   update AchievementForm set studentscore = 60 , examdate = '2015-10-10' where(studentNo = 's1004' and subjectId = 3);
   

6. 经核实 S1004 课程编号为2 的 缺考

   update AchievementForm set studentscore = '缺考' where(studentNo = 's1004' and subjectId = 2);
   

7. 将计算机网络课程删除掉

   delete from SubjectForm where (subjectname = '计算机网络');
   

关于查询的训练

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

-- 20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com

-- 21、查询住址为“雁塔区”的学生姓名、电话、住址
select studentname, phone, address from StudentForm where (address like '%雁塔区%');
-- 22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select subjectname,classHour,gradeId from SubjectForm where subjectname like '%计算机%' order by classhour;
-- 23、查询电话中含有以“130”开头的学生姓名，住址和电话。
select studentname,address,phone from StudentForm where phone like '130%';
-- 24、查询姓“赵”的学号、姓名和住址。
select studentNo,studentname,address from StudentForm where studentname like '赵%';
-- 25、统计一年级女生的总人数。
select count(sex) from StudentForm where sex = '女' and gradeId = 1;
-- 26、查询李四总成绩
select sum(studentscore) from AchievementForm where studentNo = (select studentNo from StudentForm where Studentname = '李四');
-- 27、学号为s1003的学生所有课总成绩
select sum(studentscore) from AchievementForm where studentNo = 's1003';
-- 28、学号为s1003的学生考试的平均分。
select avg(Studentscore) from AchievementForm where studentNo = 's1003';
-- 29、查询一年级的科目“Mysql”的最高分、最低分、平均分。
select max(studentscore),min(studentscore),avg(studentscore) from AchievementForm where subjectId = (select subjectId from SubjectForm where subjectname = 'MySql深入');
-- 30、查询每个年级的总学时数，并按照升序排列。
select gradeid,sum(classhour) from SubjectForm group by gradeId order by sum(classhour);
-- 31、查询每个参加考试的学员的平均分。（Group by 学号））
select studentNo,avg(studentScore) from AchievementForm group by studentNo;
-- 32、查询每门课程的平均分，并按照降序排列。（group by 课程）
select subjectId,avg(studentscore) from AchievementForm group by subjectId order by avg(studentscore) desc;
-- 33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
select studentNo,sum(studentScore) from AchievementForm group by studentNo order by avg(studentScore) desc;
-- 34.查询一年级的平均年龄。

-- 35、查询每个年级西安地区的学生人数。
select gradeId,count(1) from StudentForm where address like '%西安%' group by gradeId;
-- 36、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
select studentNo,(select s.studentName from StudentForm s where s.studentNo=a.studentNo )studentName ,count(studentScore) from AchievementForm a where studentScore<60 group by studentNo having 
count(studentScore)>=1;
-- 37、查询学生姓名、所属年级名称及联系电话。
select studentName,gradeId,phone from StudentForm;
-- 38、查询年级编号为1的科目名称、年级名称及学时。
select subjectName,gradeId,classhour from SubjectForm where gradeId=1;
-- 39、	查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select (select s.studentname from StudentForm s where s.studentno = a.studentno ), studentScore,examdate 
from AchievementForm a where subjectid=1;
-- 40、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。

-- 41、查询所有科目的参考信息（某些科目可能还没有被考试过）
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;

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;