现有以下几个表:
学生表(student):
科目表(subjects):
科目表不够完善,还需加上gradename
字段
成绩表(result):
针对现有的表,做出以下练习:
数据修改与删除
- 将地址是西安市的更新为 西安科技二路
update student set address = '西安科技二路' where address = '西安市';
- 将S1001的email修改为空的字符串
update student set email= '' where studentno = 's1001';
- 将二年级的第二门课的名字更新为 java基础,课时为60
select gradid from grade where gradename='二年级';
update result set subjectname = 'java基础',classhour = 60 where gradeid=(select gradid from grade where gradename='二年级');
- 将S1001 ,课程编号为 2 的成绩提高 5分
update result set studentresult=studentresult + 5 where studentno='sl001' and subjuctid = 2;
- 将 S1004 ,课程编号为3 的成绩更新为60,考试时间更新为 2015-10-10
update result set studentresult = 60,examdate='2015-10-10' where studentno='s1004' and subjectid = 3;
- 经核实 S1004 课程编号为2 的 缺考
update result set studentresult = null where studentno= 's1004' and sbujectid = 2;
- 将计算机网络课程删除掉
delete from subjects where subjectname = '计算机网络';
数据查询
- 查询全部一年级的学生信息。
select gradeid from grade where gradename = '一年级';
select * from student where grade = (select gradeid from grade where gradename = '一年级');
2、 查询全部二年级的学生的姓名和电话。
select gradeid from grade where gradename = '二年级';
select studentname,phone from student where gradeid = (select gradeid from grade where gradename = '二年级');
3、 查询全部一年级女同学的信息。
select gradeid from grade where gradename = '一年级';
select * from student where gradeid = (select gradeid from grade where gradename = '一年级') and sex = '女';
4、 查询课时超过60的科目信息。
select * from subjects where class