Oracle查询DQL脚本记录
时间：2021-07-01 10:21:17
帮助过：2人阅读
查询列
Select t.sname,t.ssex,t.class
from student t
--t 别名;
Select *from student t;
--* 代表查询表内所有数据
Select *from student t
where t.sclass
=‘95031‘;
--规定判断条件t.sclass=‘95031‘，用where连接
Select t.sname
as 姓名,t.ssex 性别,t.class 班级
from student t
where t.sclass
=‘95031‘;
--将列名翻译成汉文
Select t.sname
||t.ssex
||t.class
from student t
where t.sclass
=‘95031‘ or t.ssex
=‘女‘;
--||代表将查询数据连接
--升降续查询
select * from STUDENT t
order by sclass;
--order by 升序排列， 默认的加上asc
select * from STUDENT t
order by sclass
desc;
--order by sclass desc 降序排列，
select * from STUDENT t
order by sclass
desc,sno
asc;
--同时进行多个字段进行排序，先对sclass进行降序，在对sno升续排列
Select distinct t.depart t.prof
from teacher t;
--distinct使depart字段与Prof字段不重复列查询出来
--关系查询
Select * from student t
where t.sno
in (
select e.sno
from score e
where degree
between 60 and 80);
--degree between 60 and 80在degree列，取在60到80之间成绩学生信息，
Select * from student t
where t.sno
in (
select e.sno
from score e
where degree
in(
85,
86,
88));
--degree in(85,86,88) 在degree列取85,86,88的成绩学生信息 --in() 子查询
--模糊查询
select * from STUDENT t
where sname
like ‘王%‘;
--查询STUDENT表中性王的人的信息，%代表任意数量 关键字like
select * from STUDENT t
where sname
not like ‘王_‘;
--下划线_代表一个字符，查询姓王的两个字的名字，
--限制返回记录条件
select * from score
where rownum
=1;
--rownum=1 伪列，在列表中是隐藏的
select * from (
select * from score
order by degree
desc)
where rownum
=1;
--from后也可以加结果集，作为虚拟表
--分组查询 group by
--按照课程编号分组，在计算每组平均值
select cno,
avg(degree),
count(
1)
from score t
group by cno;
--连接查询
--内连接查询
select Sname,cno,degree
from score s,student t
where t.sno
=s.sno;
--将两个表score，student表创建练习。
select Sname,cno,degree
from score s
join student t
on t.sno
=s.sno;
--标准内连接格式
--外联接查询
select Sname,cno,degree
from score s
left join student t
on t.sno
=s.sno;
--left join 左外联，将左侧表内数据对不上的信息查询出来，
select Sname,cno,degree
from score s
left join student t
on t.sno
=s.sno;
--right join右外联，将右侧表内数据对不上的信息查询出来，
--全连接
select Sname,cno,degree
from score s
full join student t
on t.sno
=s.sno;
--full join全连接，将左，右两侧表内数据的信息全部查询出来，
Oracle查询DQL脚本记录
标签：
