数据库总结(二)

1.建立学生信息表（t_stu）

STU_ID	XM	XB
1	张三丰	1
2	林丹
3	胖火锅	2
4	孙二娘	1
5	谭鱼头	1
6	小龙女	2

2.建立学生课程表（t_xk）

XK_ID	XK_MC
20	语文
21	数学
22	体育

3.建立学生成绩表（t_chenj）

AUTOID	STU_ID	XK_ID	FEN	RQ_KS
1	1	20	80.9	2005-5-13
2	1	21	59	2005-5-13
3	1	22	78	2005-5-13
4	2	20	21.2	2006-6-13
5	2	21	89	2006-6-13
6	2	22	56	2006-6-13
7	3	20	45.6	2007-7-13
8	3	21	67.2	2007-7-13
9	3	22	45	2007-7-13
10	4	20	89	2007-7-13
11	4	21	30.3	2007-7-13
12	4	22	67	2007-7-13
13	5	20	99	2007-7-13
14	5	21	90	2007-7-13

4.建立视图（v_stu_chenj）

AUTOID	STU_ID	XK_ID	FEN	RQ_KS	XM	XB	XK_MC
1	1	20	80.9	2005-5-13	张三丰	1	语文
2	1	21	59	2005-5-13	张三丰	1	数学
3	1	22	78	2005-5-13	张三丰	1	体育
4	2	20	21.2	2006-6-13	林丹		语文
5	2	21	89	2006-6-13	林丹		数学
6	2	22	56	2006-6-13	林丹		体育
7	3	20	45.6	2007-7-13	胖火锅	2	语文
8	3	21	67.2	2007-7-13	胖火锅	2	数学
9	3	22	45	2007-7-13	胖火锅	2	体育
10	4	20	89	2007-7-13	孙二娘	1	语文
11	4	21	30.3	2007-7-13	孙二娘	1	数学
12	4	22	67	2007-7-13	孙二娘	1	体育
13	5	20	99	2007-7-13	谭鱼头	1	语文
14	5	21	90	2007-7-13	谭鱼头	1	数学

代码如下：

 

create or replace view v_stu_chenj as
  select cj.autoid,
       cj.stu_id,
       cj.xk_id,
       cj.fen,
       cj.rq_ks,
       s.xm,
       s.xb,
       x.xk_mc
  from T_CHENJ cj,
       t_stu s,
       t_xk x
 where s.stu_id = cj.stu_id
       and x.xk_id = cj.xk_id

 

 

 

 

 

--求60分以上的同学

having min(fen)>60

 
 --问题18：排名问题
select * from
(
   select DENSE_RANK() over(order by fen desc) as 名次,a.*
   from t_chenj a
) tmp
要修改地方
1.排序字段：fen
2.确定升序或降序：升序不要desc，降序要desc
3.确定表名：t_chenj
4.别名：“名次”
效果1：
select * from
(
   select DENSE_RANK() over(order by fen desc) as 名次,a.*
   from t_chenj a where xk_id=21
) tmp

效果2：
 select * from
(
   select RANK() over(order by fen desc) as 名次,a.*
   from t_chenj a where xk_id=21
) tmp

--问题1:用insert语句增加学生(序列,Jake,1)
insert into t_stu t
  (stu_id, xm, xb)
values
  (seq_t_stu.nextval,'jake','1');
  commit;
--改变序列的值（每执行一次nextval，seq_t_stu的值增加1）
select seq_t_stu.nextval from dual

--问题2：把jaka的名字改为“杨七郎”，性别改为女（2）
update t_stu
   set xm = '杨七郎',xb='2'
 where stu_id = 8;
 
 --回滚（撤消）rollback；
 commit;
 
 --问题3：删除杨七郎记录
 delete t_stu t where t.stu_id=7;
 commit;
 

 --查询：单表查询
 --问题4：查询姓名为空的同学（学号，姓名）

 select t.stu_id,t.xm
 from t_stu t
 where t.xb is null;
 
 --问题5：查询学号大于2的所有同学的所有字段信息

 select * from t_stu t
 where t.stu_id>2
 
 --问题6：查询视图

 select * from v_stu_chenj
 
 --问题7：查询今天的日期以及计算99*88
 select sysdate rq,99*88 jieguo from dual
 
 --问题8：查询各科的成绩（学科编号，学科名称，分数（字段命名为考试成绩））
 select a.xk_id,a.xk_mc,b.fen 考试成绩
 from t_xk a,t_chenj b
 where a.xk_id=b.xk_id
 
 --问题9：只显示语文成绩(条件：xk_id=20)，并按从高到低排列

 select cj.xk_id,cj.fen
 from t_chenj cj
 where cj.xk_id=20
 order by nvl(cj.fen,0) desc
 
 --问题10：查询在2005-5-13和2007-7-13之间的考试记录
 select * from v_stu_chenj v
 where v.rq_ks between to_date('2005.5.13','yyyy.mm.dd')
              and to_date('2007.6.13','yyyy.mm.dd')
             
--问题11：查询在2005年5月份的考试记录

 select * from v_stu_chenj v
 where to_char(v.rq_ks,'yyyy.mm')='2005.05'
 
 --问题12：查询在2005年2季度的考试记录

 select v.* from v_stu_chenj v
 where to_char(v.rq_ks,'Q')='2'
 
 select v.*,to_char(v.rq_ks,'Q') 季度 from v_stu_chenj v
 
 --问题13：查询成绩在60到90之间的考试记录

 select * from v_stu_chenj v
 where v.fen between 60 and 90
 
 --问题14：查询性别为1或空（null）的考试记录
 select * from v_stu_chenj v
 where nvl(v.xb,0) in (1,0)
 
 --问题15：查询什么科目都没有考试的同学？

 select s.stu_id,s.xm from t_stu s
 where not exists
 (
  select * from t_chenj c
  where s.stu_id=c.stu_id
 )
 
 
 --问题16：求各科的最高分（最低，平均，考试人数）

 select v.xk_mc,
        max(fen) 最高分,
        min(fen) 最低分,
        avg(fen) 平均分,
        count(*) 考试人数
   from v_stu_chenj v
  group by v.xk_id, v.xk_mc
 
 
 --问题17：统计各同学的总分，平均分
 select v.xm,
        max(fen) 最高分,
        min(fen) 最低分,
        avg(fen) 平均分,
        count(*) 考试科数
   from v_stu_chenj v
  group by v.stu_id, v.xm