小菜鸟之Oracle数据库

select * from STUDENT;
select * from mark;
select * from COURSE;

select * from teacher;

--注释
select sname from student;--投影查询
select * from student;
select sname as 姓名,ssex as 性别,sage from student ;--属性起别名
select sname 姓名,ssex 性别,sage from student ;
select distinct sage,ssex  from student;--distinct 修饰组合，去掉重复的
select    5
from      1
where     2
group by  3
having    4
order by  6
 select * from student
where SNATIVEPLACE='上海'
select * from student where sage=19;--查询十九岁
= >  <=  >= ！=  and or not   not > and >or
select * from student
where(sage=19 or sage =21)and ssex ='男';

select * from student where SNATIVEPLACE !='福建' and
select * from student where  not  sage=19 or ssex='女' and SNATIVEPLACE ='福建'--除福建
select * from student where (SNATIVEPLACE='福建'and ssex='女') or ( not SNATIVEPLACE='上海'and ssex='男')
--来自福建的女生以及上海以外的男生
select * from STUDENT
where SAGE between 19 and 20---20  dao 19
select * from STUDENT
where sage in(19,20,22) and ssex='男'--in集合数  19 20 22 男生
select * from STUDENT where sname like '王%'
--模糊查找 like '%' 下划线表示几个名__  %任意几个名
select * from STUDENT where sname like '徐__'
insert into STUDENT(sid,SNAME) values(10039,'小雅')
select  * from STUDENT where SSEX is null  --数据为空 is null
select  ssex,min(SAGE),max(SAGE),avg(SAGE),sum(SAGE),count(*) from STUDENT group by SSEX
select SNATIVEPLACE,count(*) from STUDENT where SSEX='男' group by SNATIVEPLACE; 统计各个地区男生数
select * from MARK
select sid,avg(cmark) from mark group by sid;--每个学生均分
select sid,min(cmark),count(*) from mark  where CMARK<60 group by sid--不及格数目（查询条件出现个，每个就是分组
select SCLASS,count(distinct SNATIVEPLACE) from STUDENT group by SCLASS--各个学生分别来自多少不同的地区
select *from STUDENT

select ssex,sage, count(*)   from student group by ssex,sage  --可以同时性别，年龄进行分组
select  max(SAGE) from STUDENT
select sid from mark group by sid having avg(cmark)>80
select SID from MARK where CMARK>80 group by SID--where中不能出现avg 之类的 where avg（mark）》80
select SNATIVEPLACE,count(*)from STUDENT where SSEX='男' group by SNATIVEPLACE---各个地区的男生人数
select SNATIVEPLACE,count(*) from STUDENT group by SNATIVEPLACE,SSEX having SSEX='男'---各个地区的男生人数
select * from STUDENT order by SAGE desc--asc升序，desc降序（空属性第一大 根据年龄排序
select * from STUDENT order by SAGE desc,sid --二阶排序在年龄相同的情况下sid排序
select * from STUDENT order by SAGE asc
select sid,avg(cmark) amk from MARK group by SID order by amk desc--j降序均分
select trunc(CMARK/10)*10||'`'||(trunc(CMARK/10)*10+9),count(*)  from MARK where CID=2001--(学科
group by trunc(CMARK/10)

--分段，turnc取证
select  * from STUDENT,MARK where STUDENT.sid=mark.sid--俩个表合一
select  * from STUDENT s,MARK m where s.SID=m.SID
select  * from STUDENT,MARK,COURSE
where STUDENT.sid=mark.sid and mark.cid=COURSE.cid;

select SNATIVEPLACE,avg(cmark)  from STUDENT,mark,COURSE where STUDENT.sid=mark.sid and mark.cid=COURSE.cid and CNAME='数学'
group by SNATIVEPLACE---各个地区数学均分
select sname,avg(CMARK) from STUDENT s,MARK m where s.sid=m.sid and SSEX='男' group by s.sid,SNAME--找出每个男生平均分，显示其姓名，均分
select * from STU,DENT s,MARK m where s.sid=m.sid and SSEX='男'
select * from MARK a,MARK b where a.sid=10002 and b.sid=10003 and a.cid=b.cid  and a.cmark>b.cmark
select SNAME from MARK a,MARK b,STUDENT s  where a.sid=b.sid and a.cid=2001  and b.cid=2002  and a.cmark>b.cmark
--那些同学20001号课程成绩比自己的20002成绩高，找sid
--查询核心1.from：将表格从硬盘的文件进行提取，并形成实例 将内存的实例从上往下依次循环，每次提取一条记录，并交给后续的子句 在多表中会对多张表进行笛卡尔想成，生成巨大的笛卡尔乘积
-- 2.每个字句在执行顺序
--where 接受from 给自己的一一条记录，对记录进行合法判断 =  >=  <= != <>,这些是比大小 而且可以比字符串，而且比大小的过程中，式子两边必须一个是属性，一个是值 支持逻辑 and or not   not》and
》or 支持between连续空间 如果空间不连续 有in（） 模糊查询like %表示多个任意字母而__表示一个任意字母
-- 空的匹配使用 is null 或者is not null 不可以使用匹配null
 -- group by sage 含义是将整个表根据sage 进行分组，分组：sage值相同的记录合并至一个“小组”中
 解释器不会遍历记录，而是遍历小组
 因此select 语句可以查询的特征语句必须来自group by 的分组特征
--group by 之后可以跟随多个属性进行分组特征，先后顺序不重要
---可以没有group by 语句直接进行查询统计结果 这类语句默认分组，就是将整个结果当成一个组看待
select 语句的作用投影的信息至屏幕上，属性之间分隔符号为英文逗号，属性可以起别名，as 最好省，*是通配符，表示这张表所有的属性select
可以去重，distinct
order by 的作用排序，须指定属性
desc 降序
asc 升序
order by 表示一阶属性相同时，使用二节顺序的值

select  sname  from MARK a,MARK b,STUDENT s
where a.cid!=b.cid and   a.sid==10002 and a.sid=b.sid a.sname='数学' and b.sname='英语'
and a.cmark>b.cmark
那些学生2001号成绩大于2002号成绩
--1、与“张三”同乡的男生姓名
select * from STUDENT where SNAME='张三'
select SNAME from STUDENT where  SNATIVEPLACE='江苏'and SSEX='男'
select  * from STUDENT a,STUDENT b    where a.SNATIVEPLACE=b.SNATIVEPLACE;
select a.*from STUDENT a,STUDENT b
where a.SNATIVEPLACE=b.SNATIVEPLACE and b.SNAME='张三' and a.SSEX='男' and a.SNAME!='张三'
--统计每门课程的平均成绩,并按照成绩降序排序
select a.CNAME,avg(CMARK) from COURSE a,MARK b  where a.cid=b.CID group by CNAME order by avg(CMARK) desc
--查询“福建”地区学生所选修的全部课程名称
select  CNAME from STUDENT,MARK,COURSE
where STUDENT.sid=mark.sid and mark.cid=COURSE.cid and SNATIVEPLACE='福建'
--查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where  a.sclass=b.sclass and sage >20  group by SCLASS
select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where a.SAGE=b.SAGE and b.SAGE>20 group by a.SCLASS+
select SCLASS from STUDENT a,STUDENT b  where a. group by SCLASS
--5、查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)
select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where a.SAGE=b.SAGE and b.SAGE>20 group by a.SCLASS
男生与女生数学均分
select SSEX,trunc(avg(CMARK) ,2) from STUDENT s,MARK m,COURSE c
where s.sid=m.sid and m.cid=c.cid and CNAME='数学' and ssex is not null group by SSEX
数学每个分段多少人数
select trunc(CMARK/10)*10||'`'||(trunc(CMARK/10)*10+9),count(*) from STUDENT s,MARK m,COURSE c
where s.sid=m.sid and m.cid=c.cid and CNAME='数学' group by trunc(CMARK/10)
找出各科成绩及格与不及格的人数，要求表示课程名称0 不及格 1 及格
select (trunc(CMARk/60)||'`'||(trunc(CMARK/60)+1)) 1,    count(*) from STUDENT s,MARK m,COURSE c
where s.sid=m.sid and m.cid=c.cid and cmark/60>1 and CMARK/60<1 group by trunc(CMARk/60)
select cname,trunc(CMARK/60),count(*)
from  MARK m,COURSE c
where m.cid=c.cid
group by c.cid,CNAME,trunc(CMARK/60)
找出福建与安徽学生的数学均分
  select SNATIVEPLACE,avg(CMARK)
  from STUDENT s,MARK m,COURSE c
  where s.sid=m.sid and m.cid=c.cid and CNAME='数学'and  (SNATIVEPLACE='福建 'or SNATIVEPLACE='安徽')
  group by SNATIVEPLACE


找出福建与安徽的所有学生总分
 select SNATIVEPLACE,sum(CMARK) from STUDENT s,MARK m,COURSE c
  where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
  group by SNATIVEPLACE

找出福建与安徽的所有学生  各科总分
select CNAME,sum(CMARK) from STUDENT s,MARK m,COURSE c
  where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
  group by CNAME



 找出福建与安徽的所有学生每个学生总分
        select SNATIVEPLACE,SNAME,sum(CMARK)
        from STUDENT s,MARK m,COURSE c
  where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
  group by SNAME,SNATIVEPLACE



子查询
select sid from STUDENT
where SNAME='张三'
select * from STUDENT
where SNATIVEPLACE=(select SNATIVEPLACE from STUDENT where SNAME='张三' )
找出数学成绩比张三高的男生姓名
select  SNAME from STUDENT
where SSEX='男' and CMARK=(select cmark from COURSE where SNAME='张三' and cmark=()    )

select cid from COURSE
where CNAME='数学' and sid=
                     (select sid from STUDENT where  SNAME='张三' and cmark=
                                                                    (select CMARK from MARK where SNAME='张三' and sid=
                                                                                                                 (select sid from STUDENT where SSEX='男')) )
select cid from COURSE
where CNAME='数学' sid=(select sid from STUDENT where sname='张三' cmark=(select CMARK from MARK where sid= ))
stp1 在course表找数学cid
select cid from COURSE where CNAME='数学'
2 在student中找张三的sid
select sid from STUDENT where SNAME='张三'
3 在mark中找出张三数学成绩
select CMARK from MARK where sid=(2) and cid=(1)
4 在mark中找出比张三高的人sdi
select  sid from MARK where CMARK>(3) and cid=(1)
5在student中找出姓名，男性
select SNAME from STUDENT where sid in(4) and ssex='男'
 select SNAME from STUDENT where sid in(
   select  sid from MARK where CMARK>(
     select CMARK from MARK where sid=(
       select sid from STUDENT where SNAME='张三') and cid=(
         select cid from COURSE where CNAME='数学')) and cid=(
          select cid from COURSE where CNAME='数学')
  ) and ssex='男'
找出均分比张三高的男生姓名
 select sid from STUDENT where SNAME='张三'
均分
 select avg(CMARK) from MARK where



找出最大年龄姓名
select max(sage) from STUDENT
select sname from select where age=(select max(sage) from STUDENT )

select * from mark group by sid


找出六老师学生数学最高分姓名

select TID from TEACHER where TNAME='马六'
数学
select CID from COURSE where  tid='30001' and CNAME='数学'
最高分数

select max(CMARK) from MARK where cid=2001
select sid from MARK where CMARK=97
姓名

select SNAME from STUDENT where sid=10008
select SNAME from STUDENT where sid in(
  select sid from MARK where CMARK=(
    select max(CMARK) from MARK where cid=(
      select CID from COURSE where  CNAME='数学' and tid=(
        select TID from TEACHER where TNAME='马六'
       )
     )
   )

 )
张三地区
 SELECT SNATIVEPLACE FROM STUDENT WHERE SNAME='张三'

同学
 select sid from STUDENT where SNATIVEPLACE in (1)
select cid from COURSE where CNAME='数学'

select max(CMARK) from MARK where CID=() SID in (2)
select sid from MARK where  CMARK=(3)
select  SNAME from STUDENT where SID=(4)
数学成绩
select  SNAME from STUDENT where SID=(
 select sid
 from MARK
 where CMARK = (
   select max(CMARK) from MARK where CID=(
     select cid from COURSE where CNAME='数学'
     ) and SID in (
                                     select sid from STUDENT where SNATIVEPLACE in (
                                     SELECT SNATIVEPLACE FROM STUDENT WHERE SNAME='张三'
                                     )
                                     )

  )
)


找出均分最高的姓名
1 找出均分表
select sid ,avg(CMARK) amk from MARK group by SID
2 找出最高
select max(amk) from (1)
3 在均分表中，谁的均分等于最高分
select sid from (1) where amk=(2)
select name from STUDENT

select sid from (select sid ,avg(CMARK) amk from MARK group by SID) where amk=(select max(amk) from (select sid ,avg(CMARK) amk from MARK group by SID))

select sid ,avg(cmark) m from mark  where group by SID
select min(m) from (1)
select  sid from (1) where m=(2)
select SNAME from STUDENT where sid=(3)

select ROWNUM,s.*  from STUDENT s where ROWNUM<=10
select SAGE,max()
select SAGE from STUDENT order by desc
select  * from () where ROWNUM=1

select  * from (select * from STUDENT order by desc ) where ROWNUM=1
找出年龄从大到小第5至8 学生信息
select * from STUDENT where ROWNUM<=8 order by SAGE  desc
select  * from STUDENT where ROWNUM <=4 order by SAGE

select  * from (select * from STUDENT where ROWNUM<=8 order by SAGE  desc) where ROWNUM <=4 order by SAGE asc
找出均分高到低5到8 学生信息
select
1　 与“张三”同乡的男生姓名
select SNATIVEPLACE from STUDENT where SNAME='张三'
select sid from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from STUDENT where SNAME='张三')
select SNAME from STUDENT where sid (2).
select SNAME from STUDENT where sid in (
select sid from STUDENT where SNATIVEPLACE=(
select SNATIVEPLACE from STUDENT where SNAME='张三'
 )
 )
选修了马六老师所讲课程的学生人数
1.赵露老师techer 求tid
 select tid from TEACHER where TNAME='马六'

 2.tid cid
   select cid from COURSE where tid in (select tid from TEACHER where TNAME='马六')
   cid sid

   select sid   from MARK where cid= 2001
    sid count
   select count(*) from STUDENT where sid in (select sid   from MARK where cid= 2001)



查询没学过“胡”姓老师课的同学的学号、姓名
模糊查询胡老师，tid
select tid from TEACHER where TNAME like '胡%'
tid cid
 select  cid from COURSE where tid  in ()

cid sid
select sid from MARK where  cid in ()

sid 名字，学号
select SNAME,sid from STUDENT where sid in ()

select SNAME,sid from STUDENT where sid not in (
  select sid from MARK where cid in  (
    select  cid from COURSE where tid in (
      select tid from TEACHER where TNAME like '胡%'
     )
   )
 )

“数学”课程得最高分的学生姓名、性别
数学cid
  select cid from COURSE where CNAME='数学'


最高分
  select max(CMARK) from MARK where cid in ()

姓名
   select sid from MARK where CMARK in ()
  select sid,SNAME from STUDENT where sid=()

select sid,SNAME from STUDENT where sid=(
  select sid from MARK where CMARK in (
    select max(CMARK) from MARK where cid in (
      select cid from COURSE where CNAME='数学'
     )
   )
 )

统计每门课程的平均成绩,并按照成绩降序排序
select cid,avg(cmark)   from mark group by cid
select czg  from (select cid,avg(cmark) czg  from mark group by cid) order by czg desc

select * from czg order by desc

子查询实现查询‘3-2班’"张三"同学的"英语"成绩
select sid from STUDENT where SNAME='张三'
select cid from COURSE where CNAME='英语'
select CMARK from MARK where sid =() and cid=()

select CMARK from MARK where sid =(
  select sid from STUDENT where SNAME='张三'
 ) and cid=(
   select cid from COURSE where CNAME='英语'
 )

查询“福建”地区学生所选修的全部课程名称
select SNATIVEPLACE from STUDENT where SNATIVEPLACE='福建'
select sid from STUDENT where SNATIVEPLACE='福建'

select cid from MARK where sid in ()
select CNAME from COURSE where cid in (
  select cid from MARK where sid in (
  select sid from STUDENT where SNATIVEPLACE='福建'
 )
 )

用子查询实现查询选修“数学”课的全部学生的数学总成绩
select cid from COURSE where CNAME='数学'
select sid from MARK where cid in ()
select sum(CMARK) from MARK where sid in()
select sum(CMARK) from MARK where sid in(
  select sid from MARK where cid in (
    select cid from COURSE where CNAME='数学'
   )
 ) and cid =(select cid from COURSE where CNAME='数学')
用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩高等数学总成绩
select cid from COURSE where CNAME='数学'
select sid from MARK where cid in (2001)
--select cid from COURSE where sid in ()
select sum(CMARK) from MARK where cid in ()

select sum(CMARK) from MARK where sid in (
    select sid from MARK where cid in (
      select cid from COURSE where CNAME='数学'
     )
   )



请用两种方法实现:查找所有成绩都在68分以上的学生姓名
select sid,min(CMARK)  from MARK  group by sid having min(CMARK)<68
select min(CMARK) from MARK group by  sid
select sid from (select min(CMARK) from MARK group by  sid

                 ) where cmark>68
查找至少2门成绩在80分以上的学生姓名
select sid from MARK group by SID
select cid from MARK where CMARK>80
select  sid from MARK where cid=()
select sid,count(*) c from MARK where CMARK> 80 group by sid
select sid from (select sid,count(*) c from MARK where CMARK> 80 group by sid ) where c>2
select SNAME from STUDENT where sid in ()


查询个人平均总成绩小于平均总成绩的学生姓名
select avg(CMARK) c from MARK m
select sid,avg(CMARK) z from MARK m group by sid;
select sid from (select avg(CMARK) c from MARK m),(select sid,avg(CMARK) z from MARK m group by sid) WHERE Z>C

用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
select SID from STUDENT where SNAME='张三'

select cid,CMARK c from MARK x where sid=(select SID from STUDENT where SNAME='张三')
select max(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
select CID,CMARK from MARK where sid=(select SID from STUDENT where SNAME='张三')
        and cmark=(select max(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三')))

8　找出张三的最高分和最低分以及对应的课程名

select max(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
select min(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
select cid from MARK
where sid=(select SID from STUDENT where SNAME='张三')
and
 (CMARK=(select max(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三')))OR
  CMARK=(select min(CMARK) from (select cid,CMARK  from MARK x where sid=(select SID from STUDENT where SNAME='张三'))))

分页查询
找出没有数学课的学生姓名
select cid from COURSE where CNAME='数学'
select sid from MARK where cid  in ()
select SNAME from STUDENT where sid not in ()
select SNAME from STUDENT where   sid not   in (
  select sid from MARK where cid  in (
    select cid from COURSE where CNAME='数学'
    )
  )

找出各科成绩均比张三高的学生姓名
组合表
 select * from MARK a,MARK b where a.cid=b.cid  and b.sid=10002
 select max(CMARK) c from MARK where sid=10002
张三 选修的cid
select sid,min(CMARK)z  from ( select * from MARK a,MARK b where a.cid=b.cid  and b.sid=10002 )
where c.max(CMARK)<z.min(CMARK)
  group by SID

cid sid

select sid,min(CMARK) from MARK group by sid
组合表格，选课有重复
s1  select a.sid sid1 ,b.sid sid2,a.cid cid1 ,a.CMARK cmarka,b.CMARK cmarkb
           from MARK a,MARK b where a.cid=b.cid  and b.sid=10002
在表找至少有一门成绩低于张三的
s2   select sid1 from (s1) where cmarka<cmarkb

select sid from (1) where b.sid<cmark
在排除低于的
s3 select sid1  from (s1) where cid1 not in (2)

找年龄最大的
年龄排序
 select * from STUDENT order by sage desc

取第一个
select * from (1) where  ROWNUM=1

找出最大，在问谁等于最大

select max(SAGE) from STUDENT
select * from student where sage =()

我的年龄比所有人大包括自己
1 找出所有人
select SAGE from STUDENT where SAGE is not null
 2扫描学生表，和所有人年龄进行比对
select * from STUDENT where  not SAGE<any(1) and  SAGE is not null


找出张三选课内容完全相同的
1 找出张三sid

2找出cid
  select sid from MARK where sid=(1)

3张三没选cid
select cid from MARK where cid not in (2)
4
谁选了张三未选的si
select sid from MARK where cid in (3)
5找出张三课程数量
 select  * from MARK where sid =(1)
6比课程
select sid from MARK where sid not in (4)
group by sid having count(*) = (5)
7 替换姓名
select SNAME from STUDENT where SID in (6)


找出张三数学英语成绩 显示数学英语姓名三列
select SNAME,(
  select CMARK from MARK where sid=10002 and cid=2001
  )数学成绩,(
     select CMARK from MARK where sid=10002 and cid=2002
  )英语成绩
from STUDENT
where SNAME='张三'
查询所有人数学英语成绩
select SNAME,(
  select CMARK from MARK where sid=s.sid and cid=2001
  )数学成绩,(
     select CMARK from MARK where sid=s.sid and cid=2002
  )英语成绩
from STUDENT s


exists存在
where  exists (select ..) 存在一个就是ture
where  not exists (select ..)
      如果我是年龄最大的，那么一定不存在有人比我大
      select * from STUDENT s where not exists(
        select * from STUDENT where sage>s.SAGE
        )

找出选修所有的课程
select * from STUDENT s where not exists(
  select * from COURSE c where not exists(
    select * from MARK where sid=s.sid and c=c.cid
    )
  )

集合运算
集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括:
INTERSECT(交集)，返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNO(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
当使用集合运算时，要注意:查询所返回的列数以及列的类型必须匹配，列名可以不同
并集
select SNAME,SAGE from STUDENT where SAGE in(19,21)
union
select SNAME,SAGE from STUDENT where SAGE in(20,22)

并集
select SNAME,SAGE from STUDENT where SAGE in(19,21)
union all
select SNAME,SAGE from STUDENT where SAGE in(20,22)
交集
  select SNAME,SAGE from STUDENT where SAGE in(19,21)
intersect
select SNAME,SAGE from STUDENT where SAGE in(20,22)

补集
 select SNAME,SAGE from STUDENT where SAGE in(19,21)
minus
select SNAME,SAGE from STUDENT where SAGE in(20,22)

找出福建除最高和最低的平均年龄
select sid from STUDENT where SNATIVEPLACE='福建'
select max(SAGE) from STUDENT where sid in (select sid from STUDENT where SNATIVEPLACE='福建')
union
select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
select sid,avg(SAGE) from STUDENT where sid in ()

select sid,avg(SAGE) from STUDENT where sid in (
  select max(SAGE) from STUDENT where sid not in (
    select sid from STUDENT where SNATIVEPLACE='福建'
    )
union
select min(SAGE) from STUDENT where sid in (
  select sid from STUDENT where SNATIVEPLACE='福建'
    )

  )
1找出福建最高年龄
 select max(SAGE) from STUDENT where SNATIVEPLACE='福建'
 select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
2找出福建最低年龄
select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
3union连接
 select max(SAGE) from STUDENT where SNATIVEPLACE='福建'
 union
 select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
4 找出福建地区学生，并去除3中学生，剩下的平均年龄
select sid from STUDENT where sid not in (
  select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
  union
  select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
  )
select avg(SAGE) from STUDENT where sid in (
  select sid from STUDENT where sid not in (
  select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
  union
  select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
  )
  )

按平均成绩从高到低显示所有学生的“数学”、“英语”、“语文”三门的课程成绩(按如下形式显示:学生ID,高等数学,计算机数学,英语,有效课程数,有效平均分 )
select SNAME,(
  select CMARK from MARK where sid=s.sid and cid=2001
  )数学成绩,(
     select CMARK from MARK where sid=s.sid and cid=2002
  )英语成绩,
       (select cmark from MARK where sid =s.sid and cid =
from STUDENT s
查询各科成绩前三名的记录:(不考虑成绩并列情况)
课程名   第一名   第二名   第三名;
select * from MARK where  ROWNUM=1 order by CMARK desc
select * from MARK where  ROWNUM=1   order by CMARK desc
查找至少2门成绩在80分以上的学生姓名
select





--34谁的各科成绩均高于各科均分
select CID,avg(CMARK)  amk from MARK m group by CID;
--S2
select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK)  amk from MARK m group by CID) m2
where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid;
--s3
select sid from (select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK)  amk from MARK m group by CID) m2
where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid) where c=7;
--s4
select SNAME from STUDENT where SID in (select sid from (select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK)  amk from MARK m group by CID) m2
where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid) where c=7)

-33求各门课程去掉一个最高分和最低分后的平均分
--s1
select CID,MAX(CMARK) 最高分 ,MIN(CMARK) 最低分 from MARK group by CID;
--s2
select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) 最高分 ,MIN(CMARK) 最低分 from MARK group by CID) m2
where m1.CID=m2.CID and m1.CMARK!=m2.最高分 and CMARK!=m2.最低分;
--s3
select CID,AVG(CMARK) from (select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b)
group by CID;
--32上海地区哪门课的均分比福建差
--s1
select sid from STUDENT where SNATIVEPLACE='上海';
--s2
select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID;
--s3
select sid from STUDENT where SNATIVEPLACE='福建';
--s4
select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID;
--s5
select  a.CID
from (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID) a,
(select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID) b
where a.CID=b.CID and a.amk<b.amk;
--s6
select CNAME FROM COURSE where CID in (select  a.CID
from (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID) a,
(select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID) b
where a.CID=b.CID and a.amk<b.amk)
--30找出数学和英语均分最高的男生姓名
--S1
select CID 科目 from COURSE where CNAME='数学'
union
select CID from COURSE where CNAME='英语';
--s2
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID;
--s3
select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID;
--s4
select max(am) from (select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID);
--s5
select sid from (select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID) where am=(select max(am) from (select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID));
--s6
select SNAME from STUDENT
where Sid in (select sid from (select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID) where am=(select max(am) from (select sid,avg(amk) am
from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
union
select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
group by SID)));
--29　请显示张三同学的数学和英语成绩,要求显示姓名，数学成绩，英语成绩
select SNAME 学生姓名,(select CMARK from MARK where cid=2001 and sid=10001) 数学成绩,(select CMARK from MARK where cid=2002 and sid=10001) 英语成绩
from STUDENT where sname='张三';
--28数学状元的同班同学中男生均分最高的学生姓名
--s1
select cid from COURSE where CNAME='数学';
--s2
select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学');
--s3
select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'));
--s4
select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学')));
--s5
select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))));
--s6
select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))));
--s7
select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid;
--s8
select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid);
 --s9
 select SID from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)
 where am=(select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid));
--s10
select SNAME from STUDENT where SID in (select SID from
 (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT
 where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)
 where am=(select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS
 from STUDENT where SID=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
sid!=(select sid  from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)));
--27　男生人数最多的地区中哪个学生年龄最大
--s1
select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE;
--s2
select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE);
--s3
select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE));
--s4
select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)));
--s5
select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))));
--s6
select sid from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))
where SAGE=(select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)))));
--s6
select SNAME from STUDENT where SID in (select sid from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))
where SAGE=(select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))));
select sid 学生id ,(select cmark from mark where cid=
(select cid from course where cname='数学') and sid=sc.sid) 数学 ,(select cmark from mark where cid=
(select cid from course where cname='英语')and sid=sc.sid) 英语,count(*) 有效课程数,avg(cmark) 有效均分
from mark sc group by sid
--1.与“张三”同乡的男生姓名
1.
select SNATIVEPLACE from STUDENT where SNAME='张三'
2.
select sid from STUDENT where SNATIVEPLACE=()
3.
select SNAME from STUDENT where sid in ()
--
select SNAME from STUDENT where sid in (
  select sid from STUDENT where SNATIVEPLACE=(
    select SNATIVEPLACE from STUDENT where SNAME='张三'
    )

  )
--2.选修了胡适老师所讲课程的学生人数
1.
select tid from TEACHER where TNAME='胡适'
2.
select cid from COURSE where tid in ()
3;
select sid from MARK where cid in ()
4.
select SNAME from STUDENT where sid in ()

5
select SNAME from STUDENT where sid in (
  select sid from MARK where cid in (
    select cid from COURSE where tid in (
      select tid from TEACHER where TNAME='胡适'

      )
    )
  )
--3.查询没学过“胡”姓老师课的同学的学号、姓名
1.
select tid from TEACHER where TNAME like '胡%'
2.
select cid from COURSE where tid in (select tid from TEACHER where TNAME like '胡%')
3.
select sid from MARK where cid  in (2002,2004)
4.
select SNAME from STUDENT where sid in ()
5.
select SNAME from STUDENT where sid not  in (
  select sid from MARK where cid in (
    select cid from COURSE where tid in (
      select tid from TEACHER where TNAME like '胡%'
      )
    )
  )
--4“数学”课程得最高分的学生姓名、性别
1
select cid from COURSE where CNAME='数学'
2
select max(CMARK) from MARK where cid=()
3.
select sid from MARK where CMARK in ()
4.
select SNAME,SSEX from STUDENT where  sid in ()
5.
select SNAME,SSEX from STUDENT where  sid in (
  select sid from MARK where CMARK in (
    select max(CMARK) from MARK where cid=(
      select cid from COURSE where CNAME='数学'
      )
    )
  )
--5统计每门课程的平均成绩,并按照成绩降序排序
select avg(CMARK) from MARK group by cid order by avg(CMARK) desc

-17　用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
1.
select sid from STUDENT where SNAME='张三'
2.
select max(CMARK) from MARK where sid = ()
3.
select cid from MARK where sid =()
and CMARK=()
4

select cid,CMARK from MARK where sid =(
  select sid from STUDENT where SNAME='张三'
  )
and CMARK=(
  select max(CMARK) from MARK where sid = (
    select sid from STUDENT where SNAME='张三'
    )
    )

--33求各门课程去掉一个最高分和最低分后的平均分
--s1
select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID;
--s2
select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b;
--s3
select CID,AVG(CMARK) from (select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b)
group by CID;


select sid
from STUDENT where SNAME='张三'
select a.sid from MARK a,MARK b where a.cid=b.sid and b.sid=() and a.CMARK<=b.CMARK
select sid from MARK where sid not in ()
select SNAME from STUDENT where sid in ()

select sid 学生id ,(select cmark from mark where cid=
(select cid from course where cname='数学') and sid=sc.sid) 数学 ,(select cmark from mark where cid=
(select cid from course where cname='英语')and sid=sc.sid) 英语,count(*) 有效课程数,avg(cmark) 有效均分
from mark sc group by sid

select sid from STUDENT where SNATIVEPLACE='上海'
select cid,avg(CMARK) amk  from MARK where sid in ()

select sid from STUDENT where SNATIVEPLACE='福建'
select cid,avg(CMARK) amk  from MARK where sid in ()

select a.cid from () where a.cid=b.cid and a.cmk<b.cmk

2019/7/22
---将每个同学的成绩降序排序 开窗
select m.*,rank() over (partition by SID order by CMARK desc ) from MARK m
select m.*,dense_rank() over (partition by SID order by CMARK desc ) id1,
       rank() over (partition by SID order by CMARK desc) id2 from MARK m

找出各个地区各门课均分，各个地区集中显示，均分降序排序
1.
select SNATIVEPLACE from STUDENT group by SNATIVEPLACE
select sid from STUDENT where SNATIVEPLACE in (select SNATIVEPLACE from STUDENT group by SNATIVEPLACE)
select avg(CMARK)  from ()

2,
select avg(CMARK) from MARK where
select SNATIVEPLACE,CNAME,avg(CMARK) amk,row_number() over (
  partition by SNATIVEPLACE order by avg(CMARK) desc )
  id1_row_number,
rank() over (
  partition by SNATIVEPLACE order by avg(CMARK) desc
  )id2_rank,dense_rank() over (
    partition by SNATIVEPLACE order by avg(CMARK) desc
  )

from STUDENT s,MARK m,COURSE c
where s.sid=m.sid and m.sid=c.sid
group by snativeplace, cname

select SNATIVEPLACE,CNAME,amk,max(amk) over (partition by SNATIVEPLACE),
       min(amk) over ( partition by SNATIVEPLACE)
 first_value() over
from(
    select SNATIVEPLACE,CNAME,avg(CMARK) amk,
           row_number() over (
             partition by SNATIVEPLACE order by avg(CMARK) desc
             ) id_row_number,
           rank() over (
             partition by SNATIVEPLACE order by avg(amk)  desc
             ) id2_rank,
           dense_rank() over (
             partition by SNATIVEPLACE order by avg(amk)  desc
             ) id3_dense_rank
  from STUDENT s,MARK m,COURSE c
where s.sid=m.sid and m.sid=c.sid
group by snativeplace, cname

      )格式，连接查询
      select  * from STUDENT s,MARK m where s.sid=m.sid

select * from STUDENT s inner join MARK m on s.sid=m.sid--内连，交集
select * from STUDENT s left outer join MARK m on s.sid=m.sid--外联


select SNAME,SSEX,amk from STUDENT s left outer join (
  select sid,avg(CMARK) amk from MARK group by sid
  ) m on s.sid=m.sid
where SSEX='男'


select SNAME,SSEX,amk from STUDENT s full outer join (
  select sid,avg(CMARK) amk from MARK group by sid
  ) m on s.sid=m.sid and SSEX='男'
--sql 语言
DDL 对结构进行操作 create drop alter  drop database Data Define Language
DML 对数据进行操作 insert delete update             Data MANAGEMENT Language
DQL 对数据查询                                      Data Query Language

DCL                                                  Data control Language
TCL                                                   Transaction Control Languc

create table STUDENTq(sid number(5,0) , ssex char(2),sage number(10)
                    )
insert into STUDENTq(sid,ssex,ssage) values (10005222,'男',20);

select * from STUDENT
drop table studentq
insert into STUDENT(SNAME,SAGE) values ('张伟',21)---添加
commit --确当数据，提交

create table stu33 as------复制表
  select * from STUDENT where ssex='男'

select * from stu33


  drop table newstu2



insert into stu1
select * from STUDENT where sage>=20

select * from newwtu2


数据的删除
delete from STUDENT where 1=1

select ROWID,s.* from STUDENT s---内存地址
--删除重复数据
delete from STUDENT
where ROWID not in (
  select min(ROWID) from STUDENT
  group by sid
  )

commit

select sid from STUDENT where SNATIVEPLACE='安徽'
select cid from COURSE where CNAME='数学'
修改，多个属性，加，

来自安徽的均分低于70，数学成绩+5
update  MARK
set CMARK=CMARK+5
where cid=(
  select cid from COURSE where CNAME='数学') and sid in(
    select sid from MARK
    where sid in (
      select sid from STUDENT where SNATIVEPLACE='安徽'
      ) group by sid having avg(CMARK) <70
  )


select  mod(12,5)from DUAL
create table stu2 ( sname
                      varchar2(80),
                      birth date

  )
select * from STU2
insert into stu2 (sname ,birth) values('张三',to_date('2019-5-5','YYYY-MM-DD'))

select sysdate,
       extract(YEAR FROM sysdate),
       extract(MONTH FROM sysdate),
       extract( DAY sysdate)
FROM DUAL
create table reader(rname number(100),rsex char(3),
                    )
insert into STUDENTq(sid,ssex,ssage) values (10005222,'男',20);
--to_date插入日期和时间用的
 insert into stu2(sname birth)
   values('张三',to_date('2019/5/21 16:55:56','YYYY/MM/DD HH24:MI:SS'))
--sysdate sysadte+1加一天的时间 sysdate+1/24加一小时 其他以此类推
select sysdate from dual
--用现在的时间减去你的生日得到你从出生到现在过了多久
select s.*,sysdate-birth from stu2 s


--extract 单取出年月日
  select sysdate(
    extract(year from sysdate)
    extract(month from sysdate)
    extract(day from sysdate)
  )
from dual

--算岁数
 select sysdate(
    extract(year from sysdate)-extract(year from birth)+1
  )
from dual

select last_day(sysdate) from dual



作者表
create table writer (
 wsid char(100),wname  char(50),wsex char(3),warea varchar2(100) ,wtelephone char(100),wid char(100),wtotal varchar2(100),wword varchar2 (100),wnote varchar2(100)


  )


select * from WRITER


insert into writer values ('001','唐三','男','徐州','18851923638','3203821958265805456','107万','174万','18444')
insert into writer values ('002','土豆','男','南京','18851923635','320382195826580486','105万','141万','1845')
insert into writer values ('003','番茄','女','上海','18851923635','320382195826585586','104万','147万','184844')
insert into writer values ('004','五一','女','南京','18851923634','320382195826580576','108万','177万','18445')
insert into writer values ('005','胡歌','男','长春','188519236378','320382195826580786','152万','152万','1875')
insert into writer values ('006','西瓜','男','徐州','18851923638','3203821958265804456','1474万','14万','1844')
insert into writer values ('007','独角','男','南京','18851923635','3203821958265450486','14万','151万','184')
insert into writer values ('008','南瓜','女','西藏','18851923635','3203821958885586','17万','14万','1848')
insert into writer values ('009','东瓜','女','香港','18851923634','320382198826580576','18万','147万','185')
insert into writer values ('0010','','男','长春','188519236378','3203821957826580786','15万','14万','185')

drop table writer
打赏表
mony
create table mony(
  wsid char(100),mbook char(100),mmony char(100)
  )
select * from mony

insert into mony valus('001','斗破苍穹','100万')
insert into mony values('002','圣墟','125万')
insert into mony values('003','霸道总裁爱上我','245万')
insert into mony values('004','高冷男神','152万')
insert into mony values('005','我是流氓','1424万')
insert into mony valu('006','斗破苍穹2','100万')
insert into mony values('007','明朝','125万')
insert into mony values('008','王子','245万')
insert into mony values('009','汉朝','152万')
insert into mony values('0010','981','1424万')
作者等级
grade
create table grade (
wsid char(100),ggrade char(100)
)
insert into grade values ('001','大神')
insert into grade values ('002','大神')
insert into grade values ('003','封圣')
insert into grade values ('004','大神')
insert into grade values ('005','小白')
select * from grade

排行榜
list
create table wlist (
  wsid char(100),wname char(100),lrank char(100)

)
select * from wlist
insert into wlist values('001','斗破苍穹','1')
insert into wlist values('002','圣墟','2')
insert into wlist values('003','霸道总裁爱上我','3')
insert into wlist values('004','高冷男神','4')
insert into wlist values('005','我是流氓','5')
insert into wlist values('006','斗破苍穹2','6')
insert into wlist values('007','明朝','7')
insert into wlist values('008','王子','8')
insert into wlist values('009','汉朝','9')
insert into wlist values('0010','981','10')

drop table list


随机0到100的整数
select trunc(DBMS_random. value*100) from dual

select systimestamp from dual
select to_date('2018-7-5','yyy-mm-dd'), to_date ('2018-7-5','yyy-mm-dd') + interval '7' hour from dual

CREATE SEQUENCE stuq
MINVALUE 10005
START WITH 10005
MAXVALUE  100000
INCREMENT BY 1
NOCYCLE
CACHE 30

create table stua(
  sid number primary key,
  sname varchar2(20)
)
insert into stua values(stuq.NEXTVAL,'小明');
insert into stua values(stuq.NEXTVAL,'小张');
insert into stua values(stuq.NEXTVAL,'小明');
insert into stua values(stuq.NEXTVAL,'小张');
insert into stua values(stuq.NEXTVAL,'小明');
insert into stua values(stuq.NEXTVAL,'小张');
insert into stua values(stuq.NEXTVAL,'小明');
insert into stua values(stuq.NEXTVAL,'小张');
select * from stua
drop SEQUENCE stuq
--表格对数据进行判定，非法的改动拒绝
              五大约束
主建 primary，外键 foreign，唯一 Unique，非空 not null，检验 check
主键（灵魂）
不能为空
不能重复（唯一）
--1简写
create table stu2(
  sid number primary key ,
  sname varchar2(100)
)
--2（完整写法
create table stu2(
  sid number,
  sname varchar2(100) not null  unique,--不为空，不重名
  constraint pk_sid primary key (sid)
)
成绩表主键
create table mark2(
  sid number,
  cid number,
  cmark number,
  constraint pk_sid primary key (sid,cid)
)
--check = where 时间除外
create table stu4(
  sid number primary key ,
  sname varchar2(100) not null ,
  ssex char(3) check ( ssex in ('男','女') )
  --amk number check (amk>=0 and amk<=100)     ,--均分

)
--外键，指向的是主键，比如marksid cid 是外建，

select * from STUDENT
drop table student;
drop table mark;
drop table COURSE;
drop table TEACHER;
create table student(
   sid int   ,
  sname varchar2(100) ,
  sage int not null ,
 ssex char(3) check ( ssex in ('男','女') ),
  snativeplace varchar2(100)  ,
  smajor varchar2(100) ,
  sclass varchar2(100) ,
  snative varchar2(100) ,
  brith varchar2(100) ,
    constraint pk_sid primary key (sid)

)

select * from student
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10001,'廖官忠',21,'男','福建','信计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10002,'张三',20,'男','江苏','信计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10003,'李四',19,'男','福建','信计','2班','傣族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10004,'萧瑾',21,'女','福建','统计','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10005,'叶晶',21,'女','上海','传煤','1班','朝鲜族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10006,'萧瑾',19,'女','北京','统计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10007,'李宁',21,'男','云南','信控','1班','傣族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10008,'唐品',18,'男','江苏','信计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10009,'吴强',20,'男','山东','统计','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10010,'欧阳锋',22,'男','四川','应数','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10011,'王充样',23,'男','黑龙江','统计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10012,'卫小宝',17,'女','福建','统计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10013,'李绍',21,'女','福建','信计','2班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10014,'黄马华',22,'男','浙江','心理学','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10015,'艾蔚儿',19,'女','福建','计算机','1班','黎族');

insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10016,'赵若辰',21,'男','江苏','心理学','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10017,'徐扬',22,'男','青海','计算机','1班','汉族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10018,'徐静静',19,'女','安徽','计算机','1班','黎族');
insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative,brith) values (10019,'徐静静',19,'女','安徽','计算机','1班','黎族',to_date('2018-2-9','yyy-mm-dd'));

create table teacher (
  tid int,p
  tname varchar2(100),
 tsex char(3) check ( tsex in ('男','女') ),
  tage int,
  tlvl varchar2(100),
  constraint te_tid primary key (tid)
)
select * from teacher
insert into teacher (tid,tname,tsex,tage,tlvl) values (30001,'马六','男',45,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30002,'胡美丽','女',32,'中级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30003,'李强','男',40,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30004,'胡适','男',55,'教授');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30005,'钱枫','男',37,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30006,'戴安安','女',27,'中级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30007,'张伯伦','男',47,'教授');

create table mark(
  sid number,
  cid number,
  cmark number,
constraint pk_sid_cid primary key (sid,cid),
constraint fk_sid foreign key (sid) references student(sid),
constraint fk_cid foreign key (cid) references student(cid)
)
insert into mark (sid,cid,cmark) values (10001,2001,85);
insert into mark (sid,cid,cmark) values (10001,2002,75);
insert into mark (sid,cid,cmark) values (10001,2003,80);
insert into mark (sid,cid,cmark) values (10001,2004,70);
insert into mark (sid,cid,cmark) values (10001,2005,60);
insert into mark (sid,cid,cmark) values (10001,2006,95);
insert into mark (sid,cid,cmark) values (10001,2007,70);
insert into mark (sid,cid,cmark) values (10002,2001,80);
insert into mark (sid,cid,cmark) values (10002,2002,65);
insert into mark (sid,cid,cmark) values (10002,2003,70);
insert into mark (sid,cid,cmark) values (10002,2004,80);
insert into mark (sid,cid,cmark) values (10002,2005,55);
insert into mark (sid,cid,cmark) values (10002,2006,78);
insert into mark (sid,cid,cmark) values (10002,2007,82);
insert into mark (sid,cid,cmark) values (10003,2001,69);
insert into mark (sid,cid,cmark) values (10003,2002,57);
insert into mark (sid,cid,cmark) values (10003,2003,90);
insert into mark (sid,cid,cmark) values (10003,2004,80);
insert into mark (sid,cid,cmark) values (10003,2005,77);
insert into mark (sid,cid,cmark) values (10003,2006,92);
insert into mark (sid,cid,cmark) values (10003,2007,80);
insert into mark (sid,cid,cmark) values (10004,2001,85);
insert into mark (sid,cid,cmark) values (10004,2002,76);
insert into mark (sid,cid,cmark) values (10004,2003,66);
insert into mark (sid,cid,cmark) values (10004,2004,54);
insert into mark (sid,cid,cmark) values (10004,2005,80);
insert into mark (sid,cid,cmark) values (10004,2006,73);
insert into mark (sid,cid,cmark) values (10004,2007,80);
insert into mark (sid,cid,cmark) values (10005,2001,93);
insert into mark (sid,cid,cmark) values (10005,2002,82);
insert into mark (sid,cid,cmark) values (10005,2003,71);
insert into mark (sid,cid,cmark) values (10005,2004,68);
insert into mark (sid,cid,cmark) values (10005,2005,70);
insert into mark (sid,cid,cmark) values (10005,2006,86);
insert into mark (sid,cid,cmark) values (10005,2007,90);
insert into mark (sid,cid,cmark) values (10006,2001,69);
insert into mark (sid,cid,cmark) values (10006,2002,48);
insert into mark (sid,cid,cmark) values (10006,2003,90);
insert into mark (sid,cid,cmark) values (10006,2004,68);
insert into mark (sid,cid,cmark) values (10006,2005,80);
insert into mark (sid,cid,cmark) values (10006,2006,88);
insert into mark (sid,cid,cmark) values (10006,2007,70);
insert into mark (sid,cid,cmark) values (10007,2001,77);
insert into mark (sid,cid,cmark) values (10007,2002,75);
insert into mark (sid,cid,cmark) values (10007,2003,82);
insert into mark (sid,cid,cmark) values (10007,2004,67);
insert into mark (sid,cid,cmark) values (10007,2005,84);
insert into mark (sid,cid,cmark) values (10007,2006,95);
insert into mark (sid,cid,cmark) values (10007,2007,73);
insert into mark (sid,cid,cmark) values (10008,2001,97);
insert into mark (sid,cid,cmark) values (10008,2002,86);
insert into mark (sid,cid,cmark) values (10008,2003,68);
insert into mark (sid,cid,cmark) values (10008,2004,81);
insert into mark (sid,cid,cmark) values (10008,2005,71);
insert into mark (sid,cid,cmark) values (10008,2006,78);
insert into mark (sid,cid,cmark) values (10008,2007,64);
insert into mark (sid,cid,cmark) values (10009,2001,90);
insert into mark (sid,cid,cmark) values (10009,2002,75);
insert into mark (sid,cid,cmark) values (10009,2003,60);
insert into mark (sid,cid,cmark) values (10009,2004,57);
insert into mark (sid,cid,cmark) values (10009,2005,90);
insert into mark (sid,cid,cmark) values (10009,2006,85);
insert into mark (sid,cid,cmark) values (10009,2007,91);
insert into mark (sid,cid,cmark) values (10010,2001,85);
insert into mark (sid,cid,cmark) values (10010,2002,62);
insert into mark (sid,cid,cmark) values (10010,2003,72);
insert into mark (sid,cid,cmark) values (10010,2004,81);
insert into mark (sid,cid,cmark) values (10010,2005,91);
insert into mark (sid,cid,cmark) values (10010,2006,90);
insert into mark (sid,cid,cmark) values (10010,2007,66);
insert into mark (sid,cid,cmark) values (10011,2001,55);
insert into mark (sid,cid,cmark) values (10011,2002,75);
insert into mark (sid,cid,cmark) values (10011,2003,67);
insert into mark (sid,cid,cmark) values (10011,2004,87);
insert into mark (sid,cid,cmark) values (10011,2005,88);
insert into mark (sid,cid,cmark) values (10011,2006,77);
insert into mark (sid,cid,cmark) values (10011,2007,61);
insert into mark (sid,cid,cmark) values (10012,2001,77);
insert into mark (sid,cid,cmark) values (10012,2002,81);
insert into mark (sid,cid,cmark) values (10012,2003,91);
insert into mark (sid,cid,cmark) values (10012,2004,67);
insert into mark (sid,cid,cmark) values (10012,2005,60);
insert into mark (sid,cid,cmark) values (10012,2006,80);
insert into mark (sid,cid,cmark) values (10012,2007,74);
insert into mark (sid,cid,cmark) values (10013,2001,88);
insert into mark (sid,cid,cmark) values (10013,2002,75);
insert into mark (sid,cid,cmark) values (10013,2003,90);
insert into mark (sid,cid,cmark) values (10013,2004,60);
insert into mark (sid,cid,cmark) values (10013,2005,71);
insert into mark (sid,cid,cmark) values (10013,2006,95);
insert into mark (sid,cid,cmark) values (10013,2007,89);
insert into mark (sid,cid,cmark) values (10014,2001,65);
insert into mark (sid,cid,cmark) values (10014,2002,72);
insert into mark (sid,cid,cmark) values (10014,2003,68);
insert into mark (sid,cid,cmark) values (10014,2004,67);
insert into mark (sid,cid,cmark) values (10014,2005,60);
insert into mark (sid,cid,cmark) values (10014,2006,75);
insert into mark (sid,cid,cmark) values (10014,2007,81);
insert into mark (sid,cid,cmark) values (10015,2001,55);
insert into mark (sid,cid,cmark) values (10015,2002,67);
insert into mark (sid,cid,cmark) values (10015,2003,61);
insert into mark (sid,cid,cmark) values (10015,2004,71);
insert into mark (sid,cid,cmark) values (10015,2005,81);
insert into mark (sid,cid,cmark) values (10015,2006,88);
insert into mark (sid,cid,cmark) values (10015,2007,90);
insert into mark (sid,cid,cmark) values (10016,2001,72);
insert into mark (sid,cid,cmark) values (10016,2002,82);
insert into mark (sid,cid,cmark) values (10016,2003,92);
insert into mark (sid,cid,cmark) values (10016,2004,62);
insert into mark (sid,cid,cmark) values (10016,2005,63);
insert into mark (sid,cid,cmark) values (10016,2006,73);
insert into mark (sid,cid,cmark) values (10016,2007,83);
insert into mark (sid,cid,cmark) values (10017,2001,85);
insert into mark (sid,cid,cmark) values (10017,2002,77);
insert into mark (sid,cid,cmark) values (10017,2003,87);
insert into mark (sid,cid,cmark) values (10017,2004,79);
insert into mark (sid,cid,cmark) values (10017,2005,69);
insert into mark (sid,cid,cmark) values (10017,2006,58);
insert into mark (sid,cid,cmark) values (10017,2007,74);
insert into mark (sid,cid,cmark) values (10018,2001,88);
insert into mark (sid,cid,cmark) values (10018,2002,78);
insert into mark (sid,cid,cmark) values (10018,2003,88);
insert into mark (sid,cid,cmark) values (10018,2004,78);
insert into mark (sid,cid,cmark) values (10018,2005,68);
insert into mark (sid,cid,cmark) values (10018,2006,98);
insert into mark (sid,cid,cmark) values (10018,2007,78);

create table course(
cid number,
  cname varchar2(100),
  cval number,
  ctime number,
  tid number,
  constraint pk_cid primary key (cid),
  constraint  te_tid foreign key (tid) reference teacher(tid)
);
select * from course
insert into course (cid,cname,cval,ctime,tid) values (2001,'数学',6,64,30001);
insert into course (cid,cname,cval,ctime,tid) values (2002,'英语',4,64,30002);
insert into course (cid,cname,cval,ctime,tid) values (2003,'体育',2,32,30003);
insert into course (cid,cname,cval,ctime,tid) values (2004,'马克思主义',6,64,30004);
insert into course (cid,cname,cval,ctime,tid) values (2005,'计算机基础',3,48,30005);
insert into course (cid,cname,cval,ctime,tid) values (2006,'心理学',4,48,30006);
insert into course (cid,cname,cval,ctime,tid) values (2007,'空间天气学',6,64,30007);
insert into teacher (tid,tname,tsex,tage,tlvl) values (30001,'马六','男',45,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30002,'胡美丽','女',32,'中级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30003,'李强','男',40,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30004,'胡适','男',55,'教授');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30005,'钱枫','男',37,'高级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30006,'戴安安','女',27,'中级教师');
insert into teacher (tid,tname,tsex,tage,tlvl) values (30007,'张伯伦','男',47,'教授');



drop table writer;
create table writer (
 wsid char(100),
 wname  char(50),
 wsex char(3),
 warea varchar2(100) ,
 wtelephone char(100) not null ,
 wid char(100) not null ,
 wtotal varchar2(100),
 wword varchar2 (100),
 wnote varchar2(100),

constraint w_wsid primary key (wsid)

  );
drop table wmony;
  --打赏表
--mony
create table wmony(
  wsid char(100),
  mbook char(100),
  mmony char(100),
    constraint e_wsid primary key (wsid)
  );


  --作者等级
--grade
drop table wgrade;
create table wgrade (
wsid char(100),
ggrade char(100),
 constraint g_wsid primary key (wsid)
);

--排行榜
--list
drop table wqlist;
create table wqlist (
  wsid char(100),
  wname char(100),
  lrank char(100),
  constraint l_wsid primary key (wsid)

);

insert into writer values ('001','唐三','男','徐州','18851923638','32038219565805456','107万','174万','18444');
insert into writer values ('002','土豆','男','南京','18851923635','32038219582658048','105万','141万','1845');
insert into writer values ('003','番茄','女','上海','18851923675','320382195826585586','104万','147万','184844');
insert into writer values ('004','五一','女','南京','18851923634','320382195826587','488万','177万','18445');
insert into writer values ('005','胡歌','男','长春','18851923678','32038219582658071','152万','152万','1875');
insert into writer values ('006','西瓜','男','徐州','18851923688','3203821958265804452','1474万','14万','1844');
insert into writer values ('007','独角','男','南京','18851023635','3203821958265450483','14万','151万','184');
insert into writer values ('008','南瓜','女','西藏','18851927635','3203821958885584','17万','14万','1848');
insert into writer values ('009','东瓜','女','香港','18851973634','320382198826580575','18万','147万','185');
insert into writer values ('0010','北瓜','男','长春','18851923678','3203821957826580786','15万','14万','185');
commit;



insert into wmony values('001','斗破苍穹','100万');
insert into wmony values('002','圣墟','125万');
insert into wmony values('003','霸道总裁爱上我','245万');
insert into wmony values('004','高冷男神','152万');
insert into wmony values('005','我是流氓','1424万');
insert into wmony values('006','三体','100万');
insert into wmony values('007','明朝','125万');
insert into wmony values('008','王子','245万');
insert into wmony values('009','汉朝','152万');
insert into wmony values('0010','981','1424万');
commit;
insert into wgrade values ('001','大神');
insert into wgrade values ('002','大神');
insert into wgrade values ('003','封圣');
insert into wgrade values ('004','大神');
insert into wgrade values ('005','小白');

commit;
insert into wqlist values('001','斗破苍穹','1');
insert into wqlist values('002','圣墟','2');
insert into wqlist values('003','霸道总裁爱上我','3');
insert into wqlist values('004','高冷男神','4');
insert into wqlist values('005','我是流氓','5');
insert into wqlist values('006','斗破苍穹2','6');
insert into wqlist values('007','明朝','7');
insert into wqlist values('008','王子','8');
insert into wqlist values('009','汉朝','9');
insert into wqlist values('0010','981','10');

commit;
select * from writer;
select * from wmony;
select * from wgrade;
select * from wqlist;



索引
1.什么时候会用到索引

2.索引的作用
节约时间，加快查询速度
3.索引缺点
占空间

create index idx_sage on student (sage) --创建
  drop index  idx_sage --删除

--视图 view
超链接 指定数据
知保存命令
只有select是才执行
在不违法规则的情况下，可以对数据进行修改

create view myv_stu as
  select sid,sname from student where ssex='男'

select * from myv_stu
select * rowid.s,* from student s；


create or replace view avgMark as
  select s.sid,sname ,avg(cmark) amk from student s left outer join mark m on s.sid=m.sid group by s.sid,sname;
select * from avgMark

  select sid,avg(cmark) amk from mark group by sid  ;

select sid
from (select sid,avg(cmark) amk from mark group by sid) where amk=(select sid,avg(cmark) amk from mark group by sid  )
---删除
truncate table student--永久性删除  DDL 不删除结构，删除数据
delete from student where 1=1--在roolback中回到初始值



数据库编程

plsql结构
【declare】可选项

  变量定义区
  begin
代码区
  【exception】

  异常处理区
end;


--赋值    i  NUMBER:=100;
DECLARE
  i  NUMBER:=100;

  BEGIN
  DBMS_OUTLN.PUT_LINE(i);



end;
--赋值
DECLARE
  i  number:=10002;
  s  varchar2(100);

  BEGIN
    select sname into s from student
    where sid=i;
  DBMS_OUTput.PUT_LINE(s);



end;

DECLARE
  i  number:=10002;
  age number;
  s  varchar2(100);

  BEGIN
    select sname,sage into s,age from student
    where sid=i;
  DBMS_OUTput.PUT_LINE(s||';'||age);










end;




select sname
   from student s,(select sid,avg(cmark) from mark group by sid) m
   where s.sid=m.sid



create view am as

select  sname,amk from student s,(select sid,avg(cmark) amk from mark group by sid) m
where s.sid=m.sid;

 select sid,avg(cmark) amk from mark group by sid m

select * from am

select sname, case
when amk>=90 then '优秀'
           when amk>=80 then '良好'
           when amk>=60 then '一般'

           else '不及格'
           end
from am;




drop view am


declare
  sno student.sid %type ;
  begin
  select sid from student where sname='&此人的学号为||sno'
  exception
  when too_many_rows then
  dbns_output.put_line('异常')

end;

declare
snm student.snm %type ;
  cnt number;

  begin
  select sname into snm from student where sid='&此人为';
update student set scmark=scmark+5
where sid=(
select sid from student where snativeplace='安徽'
    ) and (select avg(cmark) from mark where sid in (select sid from student where snativeplace='安徽') group by sid)<70
    and cname='数学'


  update student set scmark=scmark+2
where sid=(
select sid from student where snativeplace='北京'
    ) and (select avg(cmark) from mark where sid in (select sid from student where snativeplace='北京'))<75

cnt:=sql%rowcount;

dbms_output.put_line('更新'||cnt||'门')；
exception
  when too_many_rows then
  dbms_output.put_line('异常');
  when no_data_found then
  dbms_output.put_line('qw异常');
 when others then
  dbms_output.put_line('qw异常');
  end;



declare
 snm student.sname%type:='请输入姓名';sno student.sid%type;
amk mark.cmark%type;
  score mark.cmark%type;snt student.snativeplace%type;
  cnt number;
  cno course.cid%type;
  begin
  --取出所有数据，保存变量中
  select sid,snativeplace into sno,snt
    from student where sname=snm;
    select avg(cmark) into  amk from mark where sid=sno;
          select cid into cno from course where cname='数学';

    select cmark into score from mark where cid=cno and sid=sno;

  --条件判断
  if snt='安徽' and amk<70
then
  score:=score+5;
    elsif
    snt='北京' and amk<75
    then
     update mark set cmark=cmark+2


    cnt:=sql%rowcount;
    dbms_output.put_line('更新'||cnt||'门')
  end if;
  --在条件骨架插入
  exception
  when too_many_rows then
  dbms_output.put_line('重复名字');
  when no_data_found then
  dbms_output.put_line('名字为口');
 when others then
  dbms_output.put_line('未知异常');
  --异常

end;


--第二题：
--扫描每个学生：
--如果这个学生来自壮族或者苗族，那么其各科成绩+3分，
--且不进行后续判断
--如果这个学生来自安徽，且均分低于70
--那么将其数学成绩+5分
--如果这个学生来自北京，且均分低于75分，那么将其各课
--成绩低于70分的课程成绩+2分，并打印共更新了几门课。
--当各学生成绩更新完毕后打印这个学生的各科成绩
--要求显示  XXX的数学成绩是。。。分，成绩优秀
    --      XXX的英语成绩是。。。分，成绩良好-

    select * from student;
declare
snm student.sname%type;sno student.sid%type;
amk mark.cmark%type;
  score mark.cmark%type;snt student.snativeplace%type;
  cnt number;
  cno course.cid%type;
sna student.snative%type;
line student%rowtype;
  line2 course%rowtype;
   cnm course.cname%type;
   cM  course.cid%type;
 begin
  for line in (select * from student) loop
  select sid into sno
  from student
  where sid = line.sid;
select sname into snm
  from student
  where sid = line.sid;
  select snativeplace into snt
  from student
  where sid = sno;
select snative into sna
  from student
  where sid = sno;

  select avg(cmark) into amk
  from mark
  where sid = line.sid;

select cid into cno from course where cname='数学';

select cmark into score from mark where sid=sno and cid=cno;
 if sna='壮族' or sna='苗族'
 then
   update mark set cmark=cmark+3
   where sid=sno;
   elsif
   snt='安徽' and amk<70
then
update mark set cmark=cmark+5
   where cid=score and sid=sno;
    elsif
    snt='北京' and amk<75
    then
     update mark set cmark=cmark+2
where sid = sno and cmark<70;

    cnt:=sql%rowcount;
    dbms_output.put_line('更新'||cnt||'门')
end if;
for line2 in (select * from course) loop
  select cid into cno
  from course
    where cid = line2.cid;
    select cname into cnm
  from course
    where cid = line2.cid;
    select cname into cnm
  from course
    where cid = cno;
    select cmark into cmk
  from mark
    where cid = cno and sid =sno;
    if cmk > 90
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩优秀');
      elsif cmk > 80
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩良好');
      elsif cmk > 60
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩一般');
      else
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',不及格');
      end if;

end loop;
  end loop;

  exception
  when too_many_rows then
  dbms_output.put_line('重复名字');
  when no_data_found then
  dbms_output.put_line('名字为空');
 when others then
  dbms_output.put_line('未知异常');
end;




declare
  line student%rowtype;
  snm student.sname%type;
  snv student.snative%type;
  snp student.snativeplace%type;
  sno student.sid%type;
  amk mark.cmark%type;
  cM  course.cid%type;
  line2 course%rowtype;
  cnm course.cname%type;
  cno course.cid%type;
  cmk mark.cmark%type;
  cnt number;

begin
  for line in (select * from student) loop
  select sid into sno
  from  student
  where sid = line.sid;
  select sname into snm
  from student
  where sid = sno;
  select snativeplace into snp
  from student
  where sid = sno;
  select snative into snv
  from student
  where sid = sno;
  select avg(cmark) into amk
  from mark
  where sid = sno;
  select cid into cM
  from course
  where cname = '数学';
-----------------------------------------

  if snv = '壮族' or snv = '苗族'
    then
      update mark set cmark = cmark + 3
      where sid = sno;
  elsif snp = '安徽' and amk < 70
  then
    update mark set cmark = cmark + 5
    where cid = cM and sid = sno;
  elsif snp = '北京' and amk < 75
  then
    update mark set cmark = cmark + 2
    where sid = sno and cmark < 70;
    cnt := SQL%rowcount;
    DBMS_OUTPUT.put_line('共更新了'||cnt||'门课');
  end if;
      for line2 in (select * from course) loop
      select cid into cno
      from course
      where cid = line2.cid;
      select cname into cnm
      from course
      where cid = cno;
      select cmark into cmk
      from mark
      where cid = cno and sid = sno;
      if cmk > 90
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩优秀');
      elsif cmk > 80
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩良好');
      elsif cmk > 60
        then
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩一般');
      else
          DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',不及格');
      end if;
    end loop;
  end loop;
end;
S
--第二题：
--扫描每个学生：
--如果这个学生来自壮族或者苗族，那么其各科成绩+3分，
--且不进行后续判断
--如果这个学生来自安徽，且均分低于70
--那么将其数学成绩+5分
--如果这个学生来自北京，且均分低于75分，那么将其各课
--成绩低于70分的课程成绩+2分，并打印共更新了几门课。
--当各学生成绩更新完毕后打印这个学生的各科成绩
--要求显示  XXX的数学成绩是。。。分，成绩优秀
    --      XXX的英语成绩是。。。分，成绩良好-
  select * from course
      select * from student

declare
  line student%rowtype;
  line2 course%rowtype;
  snm student.sname%type;
  sna student.snative%type;
snt student.snativeplace%type;
sno student.sid%type;
  cmk mark.cmark%type;
amk mark.cmark%type;
cno course.cid%type;
cna course.cname%type;
  cm course.cid%type;



  begin

end;




select CID,cmark from MARK where SID=(select SID from STUDENT where SNAME='张三')
and CMARK=(select MAX(CMARK) from MARK m where SID=(select SID from STUDENT where SNAME='张三'));
--编写存储过程，modMK，要求输入学号，课程号，成绩
--如果学号不存在，课程号在成绩表中不存在，抛出异常
--如果sid和cid指定的记录不存在测向mark插入新的记录
--sid和cid在这条记录修改为新的marmark已经存在则如果
--如果输入的成绩参数小于0，和大于100，异常
 create or replace  procedure qa(
 sno in student.sid%type,
cna course.cid%type,
cm mark.cmark%type
)
as

  nsno student.sid%type;
  ncna course.cid%type,
ncm mark.cmark%type

  begin
   when cm<0 and cm>100
     then
  dbms_output.put_line('输入值不正确');
  when sid!=sno then
  dbms_output.put_line('学号不存在');
   when cid!=cno then
  dbms_output.put_line('课程号不存在');

    select cid into ncno from course where cid=cna;
    select sid into nsno from student where sid=sno;
    select cmark into ncm from mark where sid=sno and cid=cna;
    select * from student s,mark m,course c where s.sid=m.sid and c.cid=m.cid  m;
     if sid!=sno or cid!=cna
       then
       insert into m values (sno,cna,cm);
     end if;
     end;


select * from mark
exception
  when too_many_rows then
  dbms_output.put_line('重复名字');
  when no_data_found then
  dbms_output.put_line('名字为空');
 when others then
  dbms_output.put_line('未知异常');



drop procedure qa
 create or replace  procedure qa(
 sno in student.sid%type,
cna in course.cid%type,
cm  in mark.cmark%type
)
as

  nsno student.sid%type;
  ncna course.cid%type;
ncm mark.cmark%type;
numerro exception;
  begin
   if cm<0 or cm>100
     then
       raise numerro;
       end if;

    select cid into ncna from course where cid=cna;
    select sid into nsno from student where sid=sno;
    select cmark into ncm from mark where sid=sno and cid=cna;


     if sno!=nsno and cm !=ncm
       then
         insert into mark(sid,cid,cmark)values(sno,cna,cm);
         elsif
         sno = nsno and cm=ncm
         then
           update mark set cmark=cm
           where sid=sno and cid=cna;
           end if;

exception
  when NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('此用户不存在');
  when OTHERS THEN
    DBMS_OUTPUT.put_line('其他未知异常');


end;
   begin
     qa(10001,2001,45)
   end;

  create table stubak
  as select * from student where 1=2
  select * from stubak
  create or replace trigger stug
  before delete on student--删除
  begin
  insert into stubak values(
  :old.sid,:old.sname,:old.sage,:old.ssex,
  :old.snativeplace,
  :old.smajor,
  :old.sclass,
  :old.sname
  ) ;
 end;




 create or replace trigger jf
  after update on student
  for each row
   as
  snm student.sname%type;
  sno student.sid%type:='&请输入学生学号';
  amk mark.cmark%type;



  begin
  select sname into snm from student where sid =sno;
select avg(cmark) into amk from mark where sid=sno;
dbms_output.put_line(snm||'的均分是'||amk);
if amk>80
  then
  update student set
    sclass='1班' ;
   else
  sclass='2班';


end if;
end;




-- 从账户一向账户二转账DECLARE
  v_money NUMBER(8, 2); -- 转账金额
  v_balance account.balance%TYPE; -- 账户余额
BEGIN
  v_money := &转账金额; -- 输入转账金额
  -- 从账户一减钱
  UPDATE account SET balance = balance - v_money WHERE id=&转出账户
  RETURNING balance INTO v_balance;
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001, '没有该账户：'||&转出账户);
  END IF;
  IF v_balance < 0 THEN
    RAISE_APPLICATION_ERROR(-20002, '账户余额不足');
  END IF;

  -- 向账户二加钱
  UPDATE account SET balance = balance + v_money WHERE id=&转入账户;
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001, '没有该账户：'||&转入账户);
  END IF;

  -- 如果没有异常，则提交事务
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('转账成功');

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK; -- 出现异常则回滚事务
      DBMS_OUTPUT.PUT_LINE('转账失败：');
      DBMS_OUTPUT.PUT_LINE(SQLERRM);END;



---

Oracle 用户管理权限


CRESTE PROFILE LIM
failed_login_attempts 3;


ldentified

select s.sid,avg(),from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid  group by s.sid,m.cid;
select avg() from (select * from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid )


select sid,avg(cmark) from (
                           select * from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid s)
group by sid
select sid,avg(cmark)  from STUDENT,mark,COURSE where STUDENT.sid=mark.sid and mark.cid=COURSE.cid
group by sid---各个地区数学均分


create view avg_w as
  select s.sid,s.sname,avg(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid , c.sid;


delimiter//
  create procedure add_pro
  (a int,b int,out max int,out min int)
  begin
    set min = (select s.sid,min(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid);
     set max= (select s.sid,max(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid);


select max(cmark) from mark

select * from avgMark




delimiter//
  create procedure add_pro
  (a String,out max int,out min int)
  begin
    max=select s.sid,max(cmark)
    from student s,mark m,course c
    where s.sid=m.sid and m.cid= c.cid and s.sname = a group by s.sid;

min=select s.sid,min(cmark)
    from student s,mark m,course c
    where s.sid=m.sid and m.cid= c.cid and s.sname = a group by s.sid;
    end;












select s.sid,max(cmark)
    from student s,mark m,course c
    where s.sid=m.sid and m.cid= c.cid group by s.sid;