数据库练习（1）

学生表

科目表

成绩表

-- 创建表
-- 学生表
create table Student (
	studentNo varchar(10) primary key,
	studentName varchar(20),
	loginPassword varchar(20),
	sex varchar(10),
	phone varchar(20),
	address varchar(50) default '宿舍'，
	born datetime,
	email varchar(30),
	gradeId int(5)
)

-- 科目表
create table Subject (
	subjectId int(5) primary key auto_increment,
	subjectName varchar(50),
	classHour int(10),
	gradeId int(5)
)

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

-- 数据修改与删除
-- 将地址是西安市的更新为 西安科技二路
   update Student set address = '西安科技二路' where address = '西安市';
-- 将S1001的email修改为空的字符串
	 update Student set email = ' ' where studentNO = 'S1001';
-- 将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
   update Subject set SubjectName = 'java基础' classHour = 60, gradeID = 2 where subjectID = 2;
-- 将S1001 ，课程编号为 2 的成绩 提高 5分
   update Score set studentScore = studentScore + 5 where studentNo = 'S1001' and subjectId = 2;
-- 将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
   update Score set studentScore = 60,examDate = '2015-10-10' where studentNo = 'S1004' and subjectId = 3;
-- 经核实 S1004 课程编号为2 的 缺考
   update Score set studentScore val where studentNo = 'S1004' and studentNo = 2;
-- 将计算机网络课程删除掉
   delete from Subject where subjectName = '计算机网络';

-- 数据查询：
-- 查询全部一年级的学生信息。
   select * from Student where gradeId = 1;
-- 查询全部二年级的学生的姓名和电话。
   select studentName,phone from Student where gradeId = 2;
-- 查询全部一年级女同学的信息。
   select * from Student where gradeID = 1 and sex = '女';
-- 查询课时超过60的科目信息。
   select * from Subject where classHour > 60;
-- 查询二年级的科目名称
   select * from Subject where gradeId = 2;
-- 查询二年级男同学的姓名和住址。
   select studentName,address from Student where gradeId = 2 and sex = '男';
-- 查询无电子邮件的学生姓名和年级信息。
   select studentName,gradeId from Student where email = null;
-- 查询出生日期在1992年之后的男学生姓名和年级信息。 
   select studentName,gradeID where born > 1992-01-01;
-- 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
   select studentNo,studentScore from Score where examDate = '2015-07-03' and subjectId = '3';
-- 按照出生日期查询一年级的学生信息。
   select * from Student where gradeId = 1 order by born;
-- 按成绩由高到低的次序查询参加编号为1的科目考试信息。
   select * from Score where subjectId = 1 order by studentScore desc;
-- 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息
   select * from Score where examDate = '2015-07-01' order by studentScore desc limit 2;
-- 查询课时最多的科目名称及课时。
   select subjectName,classHour from Subject order by classHour desc limit 1;
-- 查询年龄最小的学生所在的年级及姓名。
   select gradeId,studentName from Student order by born desc limit 1;
-- 查询考试的最低分出现在哪个科目
   select subjectName from Subject where subjectId = (select subjectId from Score order  by studentScore limit 1);
-- 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
   select * from Score where studentNo = 's1001' order by examDate;
-- 查询年龄超过25周岁的学生信息。
   select * from Student where (year(curdate()) - substring(born from 1 for 4)) > 25;
-- 查询1月份过生日的学生信息
   select * from Student where (substring(born from 6 for 6)) = 1;
-- 查询今天过生日的学生姓名及所在年级。
   select studentName,gradeId from StudentForm where (substring(born from 6 for 7)) = month(curtime()) and (substring(born from 9 for 10)) = day(curtime());
-- 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
   select ?
-- 查询住址为“雁塔区”的学生姓名、电话、住址
   select studentName,phone,address from Student where address like '%雁塔区%';
-- 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
   select * from Subject where subjectName like '%计算机%';
-- 查询电话中含有以“130”开头的学生姓名，住址和电话。
   select studentName,address,phone from Student where phone like '130%';
-- 查询姓“赵”的学号、姓名和住址。
   select studentNo,studentName,address from Student where studentName like '赵%';
-- 统计一年级女生的总人数。
   select count(sex) from Student where sex = '女';
-- 查询李四总成绩
   select sum(studentScore) from Score where studentNo = (select studentNo from Student where studentName = '李四');
-- 学号为s1003的学生所有课总成绩
   select sum(studentScore) from Score where studentNo = 's1003';
-- 学号为s1003的学生考试的平均分。
   select avg(studentScore) from Score where studentNo = 's1003';
-- 查询一年级的科目“Mysql”的最高分、最低分、平均分。
   select max(studentScore),min(studentScore),avg(studentScore) from Score where subjectId = (select subjectId from Subject where subjectName = 'MySQL');
-- 查询每个年级的总学时数，并按照升序排列。
   select gradeid,sum(classhour) from Subject group by gradeId order by sum(classhour);
-- 查询每个参加考试的学员的平均分。（Group by 学号）
   select studentNo,avg(studentScore) from Score group by studentNo;
-- 查询每门课程的平均分，并按照降序排列。（group by 课程）
   select subjectId,avg(studentScore) from Score group by subjectId order by avg(studentScore) desc;
-- 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
   select studentNo,sum(studentScore) from Score group by studentNo order by avg(studentScore) desc;
-- 查询一年级的平均年龄。
   select gradeId,avg((year(curdate())-substring(born from 1 for 4))) from Student;
-- 查询每个年级西安地区的学生人数。
   select gradeId,count(1) from Student where address like '%西安%' group by gradeId;
-- 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
   select studentNo,avg(studentScore) from Score group by studentNo having avg(studentScore)>=60 order by avg(studentScore)
-- 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
   select studentNo,(select s.studentName from Student s where s.studentNo=r.studentNo )studentName ,count(studentScore) from Score where studentScore<60 group by studentNo having count(studentScore)>=1;
-- 查询学生姓名、所属年级名称及联系电话。
   select studentName,gradeId,phone from Student;
-- 查询年级编号为1的科目名称、年级名称及学时。
   select subjectName,gradeId,classhour from Subject where gradeId=1;
-- 查询参加科目编号为1的考试的学生姓名、分数、考试日期。
  
-- 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
   select subjectname,studentScore,examDate from Score,student,subject WHERE Score.SubjectId = Subject.SubjectId and Score.StudentNo = Student.StudentNo and studentForm.StudentNo='s1001';
-- 查询所有科目的参考信息（某些科目可能还没有被考试过）
   
-- 查询没有被考过的科目信息。