Oracle数据库数据查询（单表查询，连接查询）

数据库查询语句

查询语句的执行过程

–1,执行from子句，计算笛卡儿积,得到的结果集v1
–2,执行where子句，对笛卡尔积的结果集进行筛选得到v2
–3,执行gronp by 语句，对结果集v2进行分组
–4，计算聚合函数,得到结果集v3
–5,执行having子句，对v3中满足条件的分组进行筛选，得到结果集v4
–6,计算目标表达式，得到目标结果集v5
–7,排序输出 order by 对结果集v5 排序输出

--例子：
select * from student ,sc
where student.sno = sc.sno
group by student.sno;

--查询学生表和选修表中的平均成绩大于等于88分的学生的学号，总成绩，平均成绩，并按照平均成绩排序
select '学号:  ' || student.sno,sum(grade),avg(grade) avggrade  -- 两个字符串 串接用 || 
from student ,sc 
where student.sno = sc.sno  
group by student.sno
having avg(grade) >= 80
order by avggrade desc; --order by 后不能跟聚合函数，但是可以用别名


--查询每个学生机及其选修课程的情况
select * from student,sc
where student.sno = sc.sno;   

--左外连接，输出包括没有选课的学生
select student.*,cno,grade
from student left outer join sc on student.sno = sc.sno;
commit;

drop table sc; --删除表
drop table student;
drop table course;
select user from dual; --列出当前用户

创建表

create table  Student
(
  Sno char(9) primary key,/*列级完整性约束条件*/ --设置学生学号为主码
  Sname char(20) unique, --列级约束条件，设置学生名唯一，出现重复不允许插入
  Ssex CHAR(3),  --定长字符串
  Sage smallint, 
  Sdept char(20)  --专业
);

--插入数据方式一
insert into Student(sname,ssex,sno, sage, sdept) values('李勇','男','201215121',20,'CS'); 
insert into Student(sname,ssex,sno, sage, sdept) values('刘晨','女','201215122',19,'CS');
insert into Student(sname,ssex,sno, sage, sdept) values('王敏','女','201215123',18,'MA');
insert into Student(sname,ssex,sno, sage, sdept) values('张立','男','201215125',19,'IS');

select * from student;  --列出学生表的所有记录

CREATE TABLE Course --创建课程表
(
  Cno CHAR(4) PRIMARY KEY,  --课程号
  Cname CHAR(40), --课程名
  Cpno CHAR(4), --先修课程号
  Ccredit SMALLINT, --学分
  foreign key (Cpno) references Course(Cno)/*表级完整性约束条件*，设置外码先修课程号Cpno,参照学生表的学生学号*/
);

--select * from course order by cno;

insert into course values('6','数据处理',null,2);
insert into course values('2','数学',null,2);
insert into course values('7','PASCAL语言','6',4);
insert into course values('5','数据结构','7',4);
insert into course values('1','数据库','5',4);
insert into course values('3','信息系统','1',4);
insert into course values('4','操作系统','6',3);

select * from course order by cno; --列出课程表的所有的记录，根据学号排序

CREATE TABLE SC  --创建选修表
(
  Sno CHAR(9), --学号
  Cno CHAR(4), --课程号
  Grade SMALLINT, --成绩
  PRIMARY KEY(Sno,Cno), --主码不唯一
  FOREIGN KEY (Sno) REFERENCES Student(Sno),  --外码
  FOREIGN KEY (Cno) REFERENCES Course(Cno)    --外码
);

select * from sc;
--插入数据方式二，要按照一定的属性顺序插入
insert into sc values('201215121','1',92);
insert into sc values('201215121','2',85);
insert into sc values('201215121','3',88);
insert into sc values('201215122','2',90);
insert into sc values('201215122','3',80);

rollback; --（回滚）撤销所做的操作
commit; --提交请求，数据写到磁盘，其他以用户才会看到数据改变

任务：单表查询

--查询全体学生的姓名，学号
select sno, sname from student;

--查询全体学生的姓名，学号，所在系
select sname,sno,sdept from student;

--查询学生的详细信息
select sno,sname,ssex,sage sdept from student;
select * from student;

--查询学生的姓名，出生年份（起别名）
select sname ,sage from student; 
select sname,2019-sage 出生年份 from student;

--查询学生的姓名，出生年份和所在系，要求用小写字母表示系名(lower(属性))
select sname, 2019-sage 出生年份,lower(sdept) from student;

--查询选修了课程的学生的学号（distinct关键字去掉重复的名字）
select sno from sc;
select distinct sno from sc; --distinct

--查询计算机科学系的全体学生名单
select sname  from student 
where sdept = 'CS'; --筛选条件

-- 查询年龄小于20岁的学生的姓名和年龄
select sname,sage from student
where sage < 20; --筛选条件

--查询20-23之间的学生的姓名，系别和年龄
select sname,sdept,sage
from student
where sage >=20 and sage <= 23; --多个筛选条件的并集用 and 

--使用关键字 between and (在某个范围之内）
select sname,sdept,sage 
from student 
where sage between 20 and 23;  -- 范围内用 between and

--查询年龄不在20-23之间的学生的姓名，系别和年龄
select sname,sdept,sage from student
where sage < 20 or sage > 23;   --多个筛选条件的或集用 or 

--使用关键字  not between and (不在某个范围之内）
select sname,sdept,sage from student
where sage not between 20 and 23;

--查询数学系“MA"系,计算机科学系”CS"系和信息系"IS"中的学生的姓名和性别
select sname,ssex from student
where sdept ='IS' or sdept = 'CS' or sdept = 'MA';  --多个筛选条件的或集用 or   

--用关键字 in 查询在某个集合内的信息
select sname,ssex from student
where sdept in ('IS','CS','MA'); --属性在集合内用 in 

--查询不是数学系“MA"系,计算机科学系”CS"系和信息系"IS"中的学生的姓名和性别
select sname,ssex from student      --用关键字 not in 查询不在某个集合内的信息
where sdept not in ('IS','CS','MA');

select sname,ssex from student   --not 放在 or 的并集括号外
where not(sdept ='IS or sdept = 'CS' or sdept = 'MA');

--查询姓名为‘李勇’的学生的详细情况 --查询成功 
select * from student where sname = '李勇'; --姓名是长度为20的定长字符串，存储名字的会将剩余的空间补空格，用等于号比较时会补充自动补充空格再比较

--查询失败
select * from student where sname like '李勇';  --姓名是长度为20的字符串，用like比较时是精确查询，不会自动补充空格，如果补上空格就查询成功

--查询学号是201215121的学生的信息 
select * from student where sno = '201215121';--查询成功

--补上空格查询成功
select * from student where sno like '201215121';  --学号是长度为9的字符串，用like比较时是精确查询，不会补充自动补充空格，但是学号固定了9个字符

/*模糊查询；
%:匹配任意个字符
_:匹配一个字符
*/

--查询所有姓张学生的姓名，学号和性别  --查询成功
select sname,sno,ssex
from student
where sname like '张%'; --查询成功

--查询姓张且全名是两个汉字的学生的名字 (   查询失败，因为张立是补充了空格的20个字节大小的字符串 ）
select sname
from student
where sname like '张_'; --只是两个汉字，匹配不上


--查询姓张且全名是两个汉字的学生的名字（查询不准确，只是两个汉字以上）
select sname
from student
where sname like '张_%'; --至少有两个字符

--查询名字第二个字为立字的学生的姓名和学号
select sname,sno 
from student
where sname like '_立%';

--查询不姓张的学生姓名
select sname from student
where sname not like '张%';

--查询DB_Design课程的课程号和学分
insert into course values('8','DB_Design',null,3);
select Cno,Ccredit from course where Cname = 'DB_Design';

--查询以’DB_‘开头，且倒数第三个字符为i的课程的详细信息(查询失败，课程名是40个字符，DB_Design后面还有很多字符,倒数第三个字符为i）
select * from course 
where Cname like 'DB_%i__';
--查询以’DB‘开头，且倒数第三个字符为i的课程的详细信息（先把末尾空格剪切（关键字 trim )，再比较，成功但是存在问题，第三个字符是通配符，第三个字符时其他字符也可以查询到）
select * from course 
where trim(Cname) like 'DB_%i__'; --以DB开头，第三个字符为任意字符，然后包含若干个字符，然后时是 i ，最后两个字符为任意字符

--定义转移字符，反斜杠\  把第三个通配符_转义为下划线 
select * from course 
where trim(Cname) like 'DB\_%i__' escape '\'; --escape 关键字 是解决转义

-- 查询缺少成绩的学生学号和课程号 失败
insert into sc values('201215122','4',null);
select sno,cno from sc where grade = null;--  失败，null是特殊字符，两个null不相等

-- 查询所有有成绩的学生学号和课程号 成功
select sno,cno from sc where grade is null;

--查询所有有成绩的学生学号和课程号；
select * from sc where not (grade is null);

--查询所有有成绩的学生学号和课程号；
select sno,cno from sc where grade is not null;

--查询计算机科学系，年龄在20岁一下并sage < 20 的学生的学生姓名
select sname from student where sdept = 'CS' and sage < 20;

--查询选修了3号课程的学生的学号及其成绩，查询结果用降序排列
select sno,grade from sc where cno = '3' order by grade desc;

--查询选修了3号课程的学生的学号及其成绩，查询结果用升序排列
select sno,grade from sc where cno = '3' order by grade ;  --默认是升序排列

--查询全体学生情况，查询结果按所在系的系号升序排序，同一个系的学生按年龄降序排序；
select * from student order by sdept asc,sage desc; --先系号升序，然后在系中按照年龄降序

--查询学生总人数（用聚集函数 count 计算人数）
select count(*) from student;

--查询选修了课程的学生人数
select distinct sno from sc; --学号不重复
select count(distinct sno) from sc;         

--查询2号课程的平均成绩，最高分，最低分；
select avg(grade) from sc where cno = '2';
select max(grade) from sc where cno = '2';
select min(grade) from sc where cno = '2';
select avg(grade) 平均成绩 ,max(grade) 最高分, min(grade) 最低分 from sc where cno = '2';

--求各个课程号及相应的选课人数
select * from sc order by cno; --按照课程号排序,列出所有的元组
select cno,count(*) --先按照课程号分组，再统计每个组元组的个数
from sc
group by cno;  

--选修课一门以上课程的学生学号
select sno, count(*) --
from sc 
group by sno 
 having count(*) >= 2;  --先对学号进行分组，再进行筛选

--选修课一门一以上课程的学生学号

select sno, count(cno) --根据课程号统计
from sc 
group by sno 
 having count(cno) >= 2; 

--查询学生学号以及平均成绩；
select sno,avg(grade)
from sc
group by sno ;

--查询平均成绩大于等于99的学生学号以及平均成绩；
select sno,avg(grade) -- 除了聚集函数外，只能写分组的属性的名字（即group by 后的属性）
from sc 
group by sno
 having avg(grade) >= 88; --分组后的选择用having子句

任务:连接查询

--查询每个学生及其选修课程的情况
select student.*, cno, grade
from student,sc
where student.sno = sc.sno;   

select student.*,cno,grade -- 左外连接 --可列出没有选课的学生的学号
from student left outer join sc on student.sno = sc.sno;

select * 
from student left outer join sc using(sno);  --using 子句会把属性sno去掉一列，等值连接后，sno只保留一列

select * from student natural join sc; --natural 自然连接会去掉相同的列
select * from student natural left outer join sc; -- 左外连接，保留选修成绩为null的学生选课信息

--查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
--方法1 通过在选修表中找出条件的学号的集合，学生表再根据学号是否在集合中来筛选元组
select sno,sname from student where sno in 
(select sno from sc where cno = '2' and grade >= 90); 

--方法2 先等值连接两个表，筛选笛卡儿积
select student.sno,sname 
from student,sc where student.sno = sc.sno
and cno = '2' and grade >=90;

--查询每一门课的间接先修课（即先修课的先修课）
    c1表1    c2表2 
cno cpno     cno cpno
1    5        1    5
5    7        5    7 ;
select c1.cno,c2.cpno
from course c1,course c2  
where c1.cpno = c2.cno;

--查询每个学生的学号，姓名，选修的课程名及成绩
select student.sno,sname,cname,grade
from student ,sc ,course --得到三个集合的笛卡儿积
where student.sno = sc.sno and sc.cno = course.cno; --对笛卡儿积进行筛选

--查询与“刘晨”在同一个系学习的学生

--方法1   先获取刘晨的系 ，再对学生表的元组进行筛选
select * from student where sdept in( select sdept from student where sname ='刘晨');

--方法2   先回去两个学生表的笛卡儿积，再对笛卡儿积进行筛选，取出表2 的符合条件的元组
select s2.*
from student s1 ,student s2  -- 获取笛卡儿积
where s1.sdept = s2.sdept and s1.sname = '刘晨'; --获取系相同的学生的笛卡儿积元组

--查询学生201215121选修课程的总学分数
--方法1 先求出学生的选修课程的集合，再在课程表中查询相关的课程，求总学分之和
select sum(ccredit) from course where cno in 
(select cno from sc where sno = '201215121');

方法2 先求课程表和选修表的笛卡儿积，再取学分之和
select sum(ccredit)
from sc,course where sc.cno =course.cno and sno='201215121';


--查询选修了课程名为“信息系统”的学生学号和姓名

--方法1 先得到信息系统课程名,在获取选修表中选修的该课程的学生学号的集合，然后在学生表中查询符合的元组
select sname 姓名,sno 学号 from student where sno in
(select sno from sc where cno in
(select cno from course where cname = '信息系统'));

方法2 先得到三个表的笛卡儿积，再对笛卡儿积筛选
select student.sno 学号,student.sname 姓名
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '信息系统';

--找出每个学生超过他自己选修课程平均成绩的课程号

select CNO from sc sc1   --相关子查询 ，逐条外层元组代入查询
where grade >(select avg(grade) from sc sc2 where sc2.sno = sc1.sno);

--查询非计算机科学系的中比计算机科学系的任意一个（比一个人年龄小就行）学生年龄都小的学生的姓名和年龄
select sname,sage
from student
where sdept<>'CS' and  -- <> == != 是不等于的符号
sage < any(select sage from student where sdept = 'CS'); --  <any 小于子查询结果的某个值

select sname,sage
from student 
where sdept<>'CS' and
sage <(select max(sage) from student where sdept='CS');

--查询非计算机科学系中比计算机学系所有学生年龄都小的学生姓名和年龄

select sname,sage 
from student where sdept<>'CS' and   -- <> ==  != 是不等于的意思
sage < all(select sage from student where sdept = 'CS'); -- < all 小于子查询的所有值

select sname,sage
from student where sdept<>'CS'and
sage <(select min(sage) from student where sdept = 'CS');

/*
相关嵌套查询：子查询依赖父查询
执行过程：
1.从外层父查询的表中取出一条记录
2.将记录的属性代入内层子查询中，如果外层查询的where子句返回真就将改记录添加到结果集
重复1，2步骤直到外层表中的所有元组都处理完毕
*/
/* 
 存在量词EXISTS谓词 
 ○ 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 “true”或逻辑假值“false”。
 ○ 若内层查询结果非空，则外层的WHERE子句返回真值 
 ○ 若内层查询结果为空，则外层的WHERE子句返回假值
 ○ 由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带 EXISTS的子查询只返回真值或假值，给出列名无实际意义
 not exists
 ○ 若内层查询结果非空，则外层的WHERE子句返回假值 
 ○ 若内层查询结果为空，则外层的WHERE子句返回真值
 */
 
--查询所有选修了1号课程的学生姓名

方法1 再选修表中查询选修了1号课程的学生的学号集合，再对学生表中的元组进行筛选
select sname from student where sno in
(select sno from sc where cno = '1');
方法2 先得到学生表和选修表的笛卡儿积，对笛卡儿积进行筛选
select sname
from student,sc
where student.sno = sc.sno and cno = '1';
方法3  exists 子句
select sname 
from student    --依次取每个元组的sno进入内查询，                                                                                                                                                                                                               
where exists (select * from sc where sc.sno = student.sno and cno = '1'); --如果存在这样的元组就返回一个真值，然后相应的sname 进入结果集，因为exists子句返回真值，select * 可以 变成 select null

--查询所有没有选修了1号课程的学生姓名
select sname  --依次从外层查询取一条元组
from student  
where not exists (select * from sc where sc.sno = student.sno and cno = '1'); --如果符合条件的元组不存在，返回真，否则返回假

--查询选修了全部课程的学生姓名(化全称量词为存在量词，没有一门课程是这个学生不选修的）

select sname  from student --依次取出每一条元组进入子查询（即取出一个学生记录）
where not exists --子结果为空则该学生记录进入父结果集
(select * from  course where not exists  --依次取出课程表中每一条元组（即取出一个课程记录）
(select * from sc where student.sno = sc.sno and sc.cno = course.cno)); --不存在这样一条记录，结果为空就返回真，该课程进入子结果集

--查询计算机科学系的学生及年龄不大于19的学生

select * from student 
where sdept = 'CS' or sage <=19;

--方法2 集合的并集
select * from student where sdept = 'CS' 
union all   --带all会输出重复的元组（即并集中重复的元素
select * from student where sage <=19;

--选修了课程1或者课程2的学生学号
select distinct sno from sc where cno = '1' or cno = '2';

select sno from  sc where cno = '1' ;
union 
select sno from sc where cno = '2';

--选修了课程1或者课程2的学生的详细信息
select  * from student 
where sno in 
(select sno from sc where cno = '1' or cno = '2');

--查询计算机科学系的学生与年龄不大于19岁的学生的交集
select * from student where sdept = 'CS'
intersect 
select * from student where sage <=19;

--查询选修了课程1的学生集合与选修课程2的学生集合的交集
select sno from sc where cno = '1';
intersect 
select sno from sc where cno = '2';
  
--查询计算机科学系的学生与年龄不大于19岁的学生的差集
select sno from student where sdept = 'CS';
minus 
select sno from student where sage <= 19;

--查询每个学生超过他选修课程平均成绩的选修记录
select * from SC x  --外层表的元组一条条代入内层查询
where grade >= 
(select avg(grade) from SC y where y.sno = x.sno); -- 取出与外层进入的元组的学号相同的选修表中的记录计算平均值