MySQL全方位练习（学生表 教师表 课程表 分数表） |
您所在的位置：网站首页 › 用mysql创建一个学生信息表格 › MySQL全方位练习（学生表 教师表 课程表 分数表） |
|
一、创建表并初始化数据 1、表说明 student(学生表)、teacher(教师表)、course(课程表)、sc(分数表) 2、创建表 //学生表create table student( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ssex varchar2(5) );//教师表 create table teacher( tno varchar2( 10) primary key, tname varchar2(20) );//课程表 create table course( cno varchar2( 10), cname varchar2(20), tno varchar2(20), constraint pk_course primary key (cno,tno) );//分数表 create table sc( sno varchar2( 10), cno varchar2(10), score number(4,2), constraint pk_sc primary key (sno,cno) );3、插入初始化数据 /*******初始化学生表的数据******/ insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit; /******************初始化教师表***********************/ insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit; /***************初始化课程表****************************/ insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001'); insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003'); insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit; /***************初始化成绩表***********************/ insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit;二、SQL语句 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号； select a.* from (select * from sc a where a.cno='c001') a, (select * from sc b where b.cno='c002') b where a.sno=b.sno and a.score > b.score; 或 select * from sc a where a.cno='c001' and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno) ****************************************************************** 2、查询平均成绩大于60 分的同学的学号和平均成绩； select sno,avg(score) from sc group by sno having avg(score)>60; ****************************************************************** 3、查询所有同学的学号、姓名、选课数、总成绩； select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno ****************************************************************** 4、查询姓“刘”的老师的个数； select count(*) from teacher where tname like '刘%'; ****************************************************************** 5、查询没学过“谌燕”老师课的同学的学号、姓名； select a.sno,a.sname from student a where a.sno not in ( select distinct s.sno from sc s, ( select c.* from course c , (select tno from teacher t where tname='谌燕') t where c.tno=t.tno ) b where s.cno = b.cno ) 或 select * from student st where st.sno not in ( select distinct sno from sc s join course c on s.cno=c.cno join teacher t on c.tno=t.tno where tname='谌燕' ) ****************************************************************** 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名； select st.* from sc a join sc b on a.sno=b.sno join student st on st.sno=a.sno where a.cno='c001' and b.cno='c002' and st.sno=a.sno; ****************************************************************** 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名； select st.* from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno join teacher t on c.tno=t.tno where t.tname='谌燕' ****************************************************************** 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名； select * from student st join sc a on st.sno=a.sno join sc b on st.sno=b.sno where a.cno='c002' and b.cno='c001' and a.score < b.score ****************************************************************** 9、查询所有课程成绩小于60 分的同学的学号、姓名； select st.*,s.score from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno where s.score 1; ****************************************************************** 42、查询全部学生都选修的课程的课程号和课程名 select distinct(c.cno),c.cname from course c ,sc where sc.cno=c.cno 或 select cno,cname from course c where c.cno in (select cno from sc group by cno) ****************************************************************** 43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名 select st.sname from student st where st.sno not in (select distinct sc.sno from sc,course c,teacher t where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕') ****************************************************************** 44、查询两门以上不及格课程的同学的学号及其平均成绩 select sno,avg(score)from sc where sno in ( select sno from sc where sc.score1 ) group by sno ****************************************************************** 45、检索“c004”课程分数小于60，按分数降序排列的同学学号 select sno from sc where cno='c004' and score |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |