Oracle知识点

SQL

分类	命令
数据定义语言（ DDL）	CREATE（创建）、ALTER（修改）、 DROP（删除）等
数据操纵语言（ DML）	INSERT（插入）、UPDATE（更新）、DELETE（删除）、SELECT（查询） 等
数据查询语言（ DQL）	基本查询语句、Order By 子句、Group By 子句等
事务控制语言（ TCL）	COMMIT（提交）、SAVEPOINT（保存点）、ROLLBACK（回滚）
数据控制语言（ DCL）	GRANT（授权）、REVOKE（撤销）

数据类型

Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

类型	描述
CHAR(length)	存储固定长度的字符串。参数 length 指定了长度，如果存储的字符串长度小于 length，用空格填充。默认长度是 1，最长不超过 2000 字节。
VARCHAR2(length)	存储可变长度的字符串。 length 指定了该字符串的最大长度。默认长度是 1，最长不超过 4000 字符。
NUMBER(p， s)	存储日期和时间， 存储纪元、 4 位年、月、日、时、分、秒，存储时间从公元前 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。
TIMESTAMP	不但存储日期的年月日，时分秒，以及秒后 6 位，同时包含时区。
CLOB	存储大的文本，比如存储非结构化的 XML 文档
BLOB	存储二进制对象，如图形、视频、声音等。

集合运算

集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

类型	描述
INTERSECT(交集)	返回两个查询共有的记录
UNION ALL(并集)	返回各个查询的所有记录，包括重复记录
UNION(并集)	返回各个查询的所有记录，不包括重复记录。
MINUS(补集)	返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录

序列

创建序列

CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]

语法解析：
① START WITH：从某一个整数开始，升序默认值是 1，降序默认值是-1。
② INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默
认值是 1，降序默认值是-1。
③ MAXVALUE：指最大值。
④ NOMAXVALUE：这是最大值的默认选项，升序的最大值是： 1027，降序默认值是-1。
⑤ MINVALUE：指最小值。
⑥ NOMINVALUE：这是默认值选项，升序默认值是 1，降序默认值是-1026。
⑦ CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最
小值后，从最大值重新开始。
⑧ NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默
认 NOCYCLE。
⑨ CACHE：使用 CACHE 选项时，该序列会根据序列规则预生成一组序列号。保留在内
存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统
再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。 Oracle
默认会生产 20 个序列号。
⑩ NOCACHE：不预先在内存中生成序列号

创建一个从 1 开始，默认最大值，每次增长 1 的序列，要求 NOCYCLE，缓存中
有 30 个预先分配好的序列号

CREATE SEQUENCE MYSEQ
MINVALUE 1
START WITH 1
NOMAXVALUE
INCREMENT BY 1
NOCYCLE
CACHE 30

视图

视图（ View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中
查询信息与从表中查询信息的方法完全相同。 只需要简单的 SELECT…FROM 即可。
视图具有以下优点：

1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基
   表。
2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定
   的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。
   语法结构：创建视图

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]

语法解析：

1. OR REPLACE：如果视图已经存在，则替换旧视图。
2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表
   创建成功后，视图才能正常使用。
3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上
   的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视
   图执行 insert 操作）， WITH READ ONLY 说明视图是只读视图，不能通过该视图进行
   增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

常用语句

复制表格和数据

CREATE TABLE maleStu AS SELECT * FROM student where ssex='男';

仅仅复制表格，但不填入数据

CREATE TABLE maleStu AS SELECT * FROM student where 1=2;

一次插入多条数据

INSERT INTO maleStu SELECT * FROM student;

内联，外联，并列查询

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

select sname,avg(cmark) 
from student s inner join mark sc on s.sid=sc.sid
group by s.sid,sname

select *
from student s inner join mark sc on s.sid=sc.sid

select * 
from student s left outer join mark sc on s.sid=sc.sid

select * 
from student s right outer join mark sc on s.sid=sc.sid

select * 
from student s full outer join mark sc on s.sid=sc.sid

以下是当初学习的一些练习
建表语句

drop table student;
create table student
(
  sid int,
  sname varchar2(100),
  sage int,
  ssex char(3),
  snativeplace varchar2(100),
  smajor varchar2(100),
  sclass varchar2(100),
  snative varchar2(100)
);
drop table course;
create table course
(
  cid int,
  cname varchar2(100),
  cval int,
  ctime int,
  tid int
);
drop table mark;
create table mark
(
  sid int,
  cid int,
  cmark int
);
drop table teacher;
create table teacher
(
  tid int,
  tname varchar2(100),
  tsex char(3),
  tage int,
  tlvl varchar2(100)
);
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班','黎族');
commit;
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 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,'教授');

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);
commit;

----------------------对应的练习-------------------------

select * from student;--学生表
select * from mark;--成绩表
select * from course;--课程表
select * from teacher;--教师表（注释是加俩横杠）
       --执行顺序
select    5
from      1  ----不可缺 
where     2
group by  3
having    4
order by  6


select sname,sage,sage from student; --可支持重复投影 (加分号可与下一行一起运行)
select * from student;  --投影全部 
select sname as 姓名,sage as 年龄 from student;--列名可以起别名但是不能打单引号,别名仅对当前名产生影响
select sname 姓名,sage sa from student;--起的别名介词as可以省略
select distinct sage from student;--加上distinct可以显示不重复的数据
select distinct sage,ssex from student;--distinct作用在后面所有数据的组合
select sname,sage
from student
where sage>19;--where可以对数据进行过滤
>大于   <小于   >=大于等于  <=小于等于    =等于（一个等号）     ！=/<>不等于（最好用后者）
select * from student where sage>19 and ssex='女';--年龄大于19的女生
select * from student where sage=19 or sage=20;
select * from student where ssex='女' and (snativeplace='福建' or snativeplace='上海')--找出来自福建或者上海的女生 与的优先级要比或高 所以要加括号 
select * from student where not ssex='女'and snativeplace='福建' or snativeplace= '上海'--（）优先级最高，其次not，and次之，最后or
where sage>=19 and <=21可以用 where sage between 19 and 21 --数据必须连续的
where sage=19 or sage=21 or sage=25 可以用 where sage in(19,21,25)
where sname like '王%' --模糊查询用like 找出王姓学生
% 表示任意多个任意字母
_ 表示任意一个字母
where sname like '王_'--找出王某
where sname like '王__'--找出王某某
select * from student where ssex is null --空的比对只能用is或者is not 其他都错




select ssex,min (sage),max (sage),avg(sage),sum(sage),count (*)--（求出分组后最大年龄，最小年龄，平均年龄，年龄总和，计数）
from student
group by ssex--(按性别分组,按什么分组其他属性就没法显示了，比如按ssex分组只能显示ssex的具体属性)


select sclass 班级,count(*) 男生人数
from student
where ssex = '男'
group by sclass--找出各个班级的男生人数方法1



where 面对的 from 交给他的一行进行筛选
having 针对 group 进行筛选


select ssex, avg(sage)
from student
group by ssex
having avg(sage)>20--筛选出平均年龄大于20的男生或者女生组

select sclass 班级,count(*) 男生人数
from student
group by sclass,ssex --分组顺序颠倒无所谓
having ssex='男'--找出各个班级的男生人数方法2

select *from student
order by sage asc--按年龄升序排序，可不写asc，默认升序，desc按照降序排序

select *from student
order by sage desc,sid --第一顺序如遇到相同年龄，第二顺序按sid默认升序排序



select sid,max(cmark),min(cmark),avg(cmark)
from mark
group by sid
having min(cmark)>=60
order by avg(cmark) desc--在没有不及格成绩真的学生中找出每个学生的最高分，最低分和均分，并且按照均分降序排序

select * from student where ssex='男'

select sclass,count(distinct snativeplace)
from student
where ssex='男'
group by sclass--找出各个班级的男生分别来自多少个不同的地区。


--多表查询=多表组合+子查询
select *
from student,mark
where student.sid=mark.sid

select sname,cname,cmark
from student,mark,course
where student.sid=mark.sid and course.cid=mark.cid--查询学生的姓名，课程名，以及对应的成绩

select sname,cname,cmark
from student s,mark m,course c
where s.sid=m.sid and c.cid=m.cid--对表起别名禁止用as只能空格加别名


select cname,max(cmark),min(cmark)
from mark m,course c
where c.cid=m.cid
group by cname,c.cid --找出各科成绩的最高分和最低分，要求显示课程名，最高分，最低分

select sname,avg(cmark)
from student s,mark m
where s.sid=m.sid
group by s.sid,sname --找出每个学生的均分，要求显示学生的姓名和均分



select sclass 班级,max(cmark) 数学最高分
from student s,mark m,course c
where s.sid=m.sid and c.cid=m.cid and cname='数学'
group by sclass --找出各个班级的数学最高分成绩，显示班级名称和最高数学成绩

select snativeplace 地区,avg(cmark) 数学均分
from student s,mark m,course c
where s.sid=m.sid and c.cid=m.cid and cname='数学' and ssex='男'
group by snativeplace
having avg(cmark)>75
order by avg(cmark) desc--哪些地区的男生数学均分大于75 要求显示这些地区和数学均分（由高到低显示）

select a.sid from mark a,mark b
where a.sid=b.sid and a.cid=2001 and b.cid =2002 and a.cmark>b.cmark--哪些学生的cid=2001课程的成绩比2002号课程成绩高？找出他们的sid
--数据相比要么一列和一个数比，要么同一行的某个数和某个数比，无法第一行的一个数和第二行的一个数比，所以需要生成2个表进行比对



select a.cid from mark a,mark b
where a.sid=10002 and b.sid=10003 and a.cid=b.cid and a.cmark>b.cmark--sid=10002号学生的哪些课程比10003号学生高？找出cid


select cname
from student s1,student s2,mark a,mark b,course c
where a.cid=b.cid and a.cmark>b.cmark and s1.sid=a.sid and s2.sid=b.sid and a.cid=c.cid and s1.sname='张三' and s2.sname='李四'
--张三的哪些课程比李四高？找出课程名  （需要5张表）


step1、找出张三的sid
 select sid from student where sname='张三'
step2、找出sid对应的均分
select avg(cmark) from mark where sid = ( select sid from student where sname='张三')

--找出张三的均分
  


step1、找出福建女生的sid
select sid from student where ssex='女' and snativeplace='福建'
step2、找出数学对应的cid
select cid from course where cname='数学'
step3、找出她们的均分
select avg(cmark) from mark where cid =(select cid from course where cname='数学')and sid in(select sid from student where ssex='女' and snativeplace='福建')

--找出福建女生的数学均分




step1、找出数学对应的cid
select cid from course where cname='数学'
step2、找出数学的均分
select avg(cmark) from mark where cid=(select cid from course where cname='数学')
step3、找出高于均分的学生sid
select sid from mark where cmark>(select avg(cmark) from mark where cid=(select cid from course where cname='数学')) and cid=(select cid from course where cname='数学')
step4、找出sid对应的学生姓名
select sname from student where sid in (select sid from mark where cmark>(select avg(cmark) from mark where cid=(select cid from course where cname='数学')) and cid=(select cid from course where cname='数学'))
--找出数学成绩高于均分的学生姓名


step1、找出数学的总均分
select avg(cmark) from mark where cid=(select cid from course where cname='数学')
step2、找出各个地区对应的平均数学成绩 并进行筛选平均成绩高于总均分的
select snativeplace,avg(cmark)
from student s,mark m
where s.sid=m.sid and m.cid=(select cid from course where cname='数学')
group by snativeplace
having avg(cmark)>(select avg(cmark) from mark where cid=(select cid from course where cname='数学'))
--哪些地区的数学均分高于总均分


--找出年龄最大的学生姓名4种方法
step1、找出最大的年龄
select max(sage) from student
step、找出有最大年龄的学生姓名
select sname from student where sage=(select max(sage) from student)



step1、找出所有年龄
select sage from student
step2、找出大于等于所有年龄的学生姓名
select sname from student where sage>=all(select sage from student)--all表示每一个


step1、找出所有年龄
select sage from student
step2、找出不是最大年龄的学生，取他的相反
select sname from student where not sage<any(select sage from student)--any表示存在一个



step1、先对年龄进行从大到小排列
select * from student order by sage desc
step2、取伪列第一个的名字
select sname from (select * from student order by sage desc) where rownum =1 --把求出的表给from用 rownum（伪列）一旦执行失败数据表将停止工作


--求出年龄由大到小排前10的名字
step1、先对年龄进行从大到小排列
select * from student order by sage desc
step2、取伪列前10的名字
select sname from (select * from student order by sage desc) where rownum <=10--注意运算的先后顺序，只有对整个数据先排序再取前10，如果和一起写就错了

--求出年龄由大到小排5-10的名字
step1、先对年龄进行从大到小排列
select * from student order by sage desc
step2、取伪列前10的名字有小到大排
select * from (select * from student order by sage desc) where rownum <=10 order by sage
step3、取伪列前5的名字
select sname from (select * from (select * from student order by sage desc) where rownum <=10 order by sage)
where rownum<=5 order by sage desc--先排序取伪列，颠倒顺序后再取伪列，此方法适用与分页查询


--均分由高到低第5-10名学生的姓名和均分
step1、先求出由高到低按sid分组的均分
select sid,avg(cmark) ark from mark group by sid order by ark desc
step2、 再取10的伪列并由小到大排列
select *from(select sid,avg(cmark) ark from mark group by sid order by ark desc) where rownum<=10 order by ark--上一个表的列如果带有函数式需要命名后给后面使用
step3、再取前5伪列由大到小排列
select *from(select *from(select sid,avg(cmark) ark from mark group by sid order by ark desc) where rownum<=10 order by ark)
where rownum<=5 order by ark desc
step4、将该表与学生表结合显示姓名和均分
select sname,ark from student s,(select *from(select *from(select sid,avg(cmark) ark from mark group by sid order by ark desc) where rownum<=10 order by ark)
where rownum<=5 order by ark desc) a where s.sid=a.sid


--找出女生均分最高的学生姓名（未考虑最高均分一样的一群学生）
step1、先找出女生对应的sid,sname
select sid,sname from student where ssex='女'
step2、合并女生sid表和mark表,并求出所有女生的均分并进行由大到小排序
select sname,avg(cmark) ark from(select sid,sname from student where ssex='女') a,mark m where a.sid=m.sid group by a.sid,sname order by ark desc
step3、找出均分最高的女生姓名
select sname from(select sname,avg(cmark) ark from(select sid,sname from student where ssex='女') a,mark m where a.sid=m.sid group by a.sid,sname order by ark desc)
where rownum=1


--找出女生均分最高的学生姓名
step1、找出女生的sid
select sid from student where ssex='女'
step2.1、找出女生均分表
select sid,avg(cmark) amk from mark where sid in (select sid from student where ssex='女') group by sid
step2.2、找出女生均分表中的最大均分
select max (amk) from (select sid,avg(cmark) amk from mark where sid in (select sid from student where ssex='女') group by sid)
step3、找出哪些女生sid的均分等于step2
select sid from (step 2.1) where amk=(step2.2)
step4、将这些sid在student表中兑换成姓名
select name from student from where sid in (step3)


--找出数学均分最高的地区女生的最高分是哪门课
step1、找出数学的cid
select cid from course where cname='数学'
step2.1、找出每个地区的数学均分
select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
step2.2、找出最高数学均分最高
select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
step3、找出均分等于最高均分的地区
select snativeplace from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
) where amk=(select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
)
step4、找出这些地区中的女生
select sid from student where snativeplace in (select snativeplace from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
) where amk=(select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
))and ssex='女'
step5、找出该sid的最高均分
select max (cmark) ark from mark where sid in (select sid from student where snativeplace in (select snativeplace from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
) where amk=(select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
))and ssex='女')
step6、最高均分等与最高均分的课程cid
select cid from mark where cmark in (select max (cmark) ark from mark where sid in (select sid from student where snativeplace in (select snativeplace from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
) where amk=(select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
))and ssex='女'))
step7、找出cid对应的课程名
select cname from course where cid = (select cid from mark where cmark in (select max (cmark) ark from mark where sid in (select sid from student where snativeplace in (select snativeplace from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace
) where amk=(select max(amk) from (select snativeplace,avg(cmark) amk from student s,mark m where cid=(select cid from course where cname='数学')and s.sid=m.sid group by snativeplace)
))and ssex='女')))



--哪些地区女生的数学均分比男生高
step1、找出数学cid
select cid from course where cname='数学'
step2、找出地区性别均分表
select snativeplace 地区,ssex 性别,avg(cmark)数学均分
from student sm= 



首先需要地区性别数学均分表
step3、select 地区 from (step2) a,(step2) b
where a.snativeplace=b.snativeplace and a.ssex='女'and b.ssex='男' and a.ark>b.ark


--哪些学生的各科成绩均比张三高
思路：先找出有一科成绩小于张三的学生
再找出选课与张三有交集的学生
排除第一类并且在第二类里的学生就是各科成绩均比张三高的


--找出张三的数学成绩，英语成绩
显示姓名，数学成绩，英语成绩
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 max(cmark) from mark where sid=s.sid)最高分,
(select min(cmark) from mark where sid=s.sid)最低分,
(select avg(cmark) from mark where sid=s.sid)均分
from student s


select *
from student s
where exists()--exists判断后面条件是否为空，空的话返回为f，非空返回t


select * from student s where not exists (-------------------这个括号条件期望为空时，筛选出的学生符合条件
       select * from course c where not exists(--当返回条件恒为真时，打印空
              select * from mark where sid=s.sid and cid=c.cid---只有这个条件恒成立时，返回为真
       )
)--找出选了所有课程的学生




rowid--对应了每一条数据在内存中的相对地址，是一条数据的固有属性
delete 
from student
where rowid not in (select min(rowid) from student group by sid)
--删除重复数据，delete后什么都不加


select sid,cid,cmark,max(cmark) over (partition by sid) 最高分,min(cmark) over (partition by sid) 最低分
from mark--显示一个人的所有科目成绩，并显示所有科目中的最高分最低分，用over （partition by）进行半分组


select sid,cid,cmark,sum(cmark) over (partition by sid order by cid) from mark 成绩之和 --对每个学生分组，按课程编号排序，累计求和



select sid,cid,cmark,
rank () over (partition by sid order by cmark desc) from mark--成绩内部排名，排名编号会重

select sid,cid,cmark,
row_number () over (partition by sid order by cmark desc) from mark--成绩内部排名，排名编号会重





select sn,sp,m,row_number()over(partition by sp order by m desc) 地区内排名
from (
select sid,sname sn,snativeplace sp,(select avg(cmark) from mark where sid=s.sid) m from student s --集中显示同一个地区的所有学生均分，显示学生在这个地区的排名
) 




连接查询
select * 
from student s,mark m
where s.sid=m.sid
--改写成如下
select * 
from student s inner join mark m on s.sid=m.sid--可以节约内存


--3表连接
select * 
from student s inner join mark m on s.sid=m.sid
inner join course c on m.cid=c.cid
--以上是内连


select *
from student s left outer join mark m on s.sid=m.sid--左外联  左表为主表
               right                                --右外联  右表为主表
               full                                 --全外联  俩表都为主



--集合运算





--1.与“张三”同乡的男生姓名
select sname from student where snativeplace=(select snativeplace from student where sname='张三')
--2.选修了赵露老师所讲课程的学生人数
step1、找出赵露老师对应的tid
select tid from teacher where tname='赵露'
step2、找出赵露老师tid对应的课程编号
select cid from course where tid=(step1)
step3、找出选了该课程的学生人数
select count(*) from mark where cid=(step2)
--3、查询没学过“胡”姓老师课的同学的学号、姓名
step1、查询胡姓老师的tid
select tid from teacher where tname like '胡%'
step2、找出胡姓老师tid对应的课程编号
select cid from course where tid  in (select tid from teacher where tname like '胡%')
step3、找出选了胡姓老师课的同学学号
select distinct sid from mark where cid  in (select cid from course where tid  in (select tid from teacher where tname like '胡%'))
step4、找出没选胡姓老师课的同学和姓名
select sid,sname from student where sid not in (step3)
--4、“数学”课程得最高分的学生姓名、性别
step1、找出数学课程编号
select cid from course where cname='数学'
step2、找出选了数学的最高分
select max(cmark) ma from mark where cid=(select cid from course where cname='数学')
step3、找出得分等于最高分的学生编号
select sid from mark where cmark=(select max(cmark) ma from mark where cid=(select cid from course where cname='数学'))
step4、找出该sid对应的学生姓名性别
select sname,ssex from student where sid in (step3)
--5、统计每门课程的平均成绩,并按照成绩降序排序
select cname,avg(cmark) from mark m inner join course c on m.cid=c.cid group by cname order by avg(cmark) desc
--6、子查询实现查询‘3-2班’"张立"同学的"英语"成绩 
step1、查询‘3-2班’‘张立’同学的sid
select sid from student where sclass='3-2班' and sname='张立'
step2、查询英语对应的cid
select cid from course where cname='英语'
step3、查询该sid cid下的成绩
select cmark from mark where sid=(step1) and cid=(step2)
--7、查询“福建”地区学生所选修的全部课程名称
step1、查询福建地区学生的sid
select sid from student where snativeplace='福建'
step2、查询该sid对应下的课程cid
select sid,cid from mark where sid in (select sid from student where snativeplace='福建')
step3、显示cid对应的课程名称
select sid,cname from course c inner join (select sid,cid from mark where sid in (select sid from student where snativeplace='福建')) s on 
c.cid=s.cid
--8、查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
select sid,sage,sclass,sum(sage) over (partition by sclass order by sage) from student 
where sage>20
--9、查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)
select sclass,sum(sage) from student group by sclass having min(sage)>20
--10、用子查询实现查询选修“高等数学”课的全部学生的高等数学总成绩 
step1、查询高等数学对应的cid
select cid from course where cname='高等数学'
step2、查询选了该cid的学生sid
select sid from mark where cid=(step1)
step3、查询高等数学总成绩
select sum(cmark) from mark where sid in (step2) and cid=(step1)
--11、用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩
step1、查询高等数学对应的cid
select cid from course where cname='高等数学'
step2、查询选了该cid的学生sid
select sid from mark where cid=(step1)
step3、查询总成绩
select cid,sum(cmark) from mark where sid in (step2) group by cid
--12、请用两种方法实现:查找所有成绩都在68分以上的学生姓名
方法1：
step1、找出每个学生的最低成绩大于68分的学生sid
select sid from mark group by sid having min(cmark)>68
step2、找出sid对应的姓名
select sname from student where sid in (step1)
方法2：
step1、找出至少有一门成绩低于68的学生sid
select distinct sid from mark where cmark<=68









create table newstu(
sid int,sname varchar2(40),ssex char(3),snativeplace varchar2(100),nmsl char(3))--表的创建

select * from newstu

drop table nStu--表的删除


insert into newstu values(100,'张三','男','南京','嗯')--插入数据 
insert into newstu (ssex,sname) values('女','王二蛋')
insert into newstu (sid,sname,ssex,snativeplace) select sid,sname,ssex,snativeplace from student where sage>20 --数据的批量插入，从一个表插到另一个表，列名可以不一样但是数据类型必须一样


create table nStu as
select sid,sname,sage,ssex from student where snative='汉族'--创建表并插入数据

select * from nStu



delete 
from student where sage=19--删除数据 delete后啥也不加


update nStu
set sage=sage+1
where 1=1   ---表的更新，对所有/张三年龄加一
where sname='张三'



select sid,sum(cmark)/count(*)--取均分
from mark
group by sid


select sid,trunc(sum(cmark)/count(*))--取均分取整
from mark
group by sid

select sid,trunc(sum(cmark)/count(*),2)--取均分取小数点后2位
from mark
group by sid

找出均分最高的学生姓名时进行均分比对时最好加trunc取整，不然运算过程中可能会出现精度丢失比对失败的情况




select sysdate from dual--查询系统当前时间，dual表无意义本身只有一行一列，适用于一些算法
select sysdate,systimestamp from dual--查询本地时间和格林威治时间
select 3+4*5 from dual
select mod(10,3) from dual--模运算要调用mod函数
select ascii('杜'),chr(45646546) from dual--ascii值的转化