oracle一基础查询：单表，多表查询（交叉，内联，外联），分组聚合，子查询语法。

最新推荐文章于 2023-07-22 09:04:25 发布

鱼雨羽

最新推荐文章于 2023-07-22 09:04:25 发布

阅读量1.3k

点赞数 3

分类专栏： Oracle数据库文章标签： oracle 查询聚合查询多表查询子查询

本文链接：https://blog.csdn.net/qq_31897241/article/details/85933627

版权

Oracle数据库专栏收录该内容

9 篇文章 1 订阅

订阅专栏

摘要：条件查询，别名，排序，聚合函数，where，having，联接查询，where in/exists/><=子查询，，rownum伪列，模糊查询。

查询所有信息
select *from table1；- - 查询table1表中的所有信息
条件查询（多列，多条件查询）
从学生表中检索出2班性别为女性的学生的姓名和电话
Select Sname,Stel from t_student Where Sclass = 2 and Ssex =‘f’;
选择无重复行 distinct关键字。
- 去除查询结果中多有的重复行。
  select distinct Sclass from t_student;- - 从t_表中查询Sclass并去重
  select distinct name, id from t_student；查询多个去重时，必须每一行所有字段全重复时才去。
- 链接: distinct不同情况下的详细用法.
给列指定别名
- Select CID AS 课程号 , Cname AS 课程名, Ctype AS 必修/选修 Chours AS 课时 from t_course; - - 多个同时改别名
- SELECT ID AS (学号) FROM STUDENT- - 一个改别名
- 其中as 可以省略，（）也可省略 as后面的为as前面的字段的别名。详见链接: 别名详解.
对查询结果进行排序
- order by 排序字段排序方式
- 降序：select * from t_score where sid = ‘10002’ order by score desc;- - score字段降序排序
- 升序：selectfrom 表名 order by 列名 asc；- - 升序排列，asc可省略
  selectfrom 表名 order by 列名；为升序排列
  - desc降序排列，asc 升序。
在列1升序的基础上排列列2
select*from 表名 order by 列名1 asc，列名2 desc；
模糊查询：字段 like 匹配表达式，关键字单引号包围
- 语法：xxx where 字段 like‘’；
- % :0个或多个字符，每个字符可以任意
- _ :有且只有一个字符，这个字符可以任意
  查询张某： select * from t_student where sname like ‘张%’;
- 字段 like ‘%关键字%’字段包含”关键字”的记录
  字段 like ‘关键字%’字段以”关键字”开始的记录
  字段 like ‘%关键字’字段以”关键字”结束的记录
- 字段1 like‘’ and 字段2 like ‘’ --like可以使用and并列使用
利用现有的表创建表
create table table_new as select * from table_old;复制结构和数据
create table table_new as select * from table_old where 1=0;只复制结构
修改用户密码：
alter user 用户名 identified by 新密码；

多表查询

内联接查询(等值联接)，查询结果形成一个新的表
SQL语句是先执行WHERE子句然后才执行SELECT子句的！
同时查询t_student，t_score，t_course三张表，表前接jion

SELECT Sname, Cname, Score FROM t_student join t_score On t_student.SID = t_score.SID Join t_course On t_course.CID = t_score.CID;

也可以用“，”隔开。其等同于

SELECT Sname, Cname, Score FROM t_student ,t_course ,t_score 
WHERE t_student.SID=t_score.SID and t_course.CID= t_score.CID;

查找的字段名不可以是表中的重名，若是则用表名.字段名区别，否则无法查找。
即若查询列出现共有字段则必须指定该字段取自哪个表格式是:表名.列名。
查询时可以给表指定别名

外联查询 语法同内联
左外联接： left join
左外联接就是将左表作为主表,结果集中除了满足联接条件的记录外,还有主表中不满足条件的记录
右外联接： right join
右外联接就是将右表作为主表,结果集中除了满足联接条件的记录外,还有主表中不满足条件的记录（和左外联接相对）
全联接：full join
全联接就是结果集中除了满足联接条件的记录外,还有左、右表中不满足条件的记录
交叉连接 cross jion
一个交叉联接(CROSS JOIN)接收两个分别有N行和M行的表T1和T2，然后返回一个包含交叉乘积N×M 条记录的联接表；
我们想知道学生和课程有多少可能的组合
select * from t_student cross join t_course;
或者
select from t_student , t_course;
cross jion可以省略
自联接
当我们需要的信息是来自一个表中的，并且需要将这个表跟它自己进行连接时用。
 注意：连接时要给表起别名
例如：要查询scott.emp 表中的员工编号、姓名、和上级领导名称

select p1.empno,p1.ename,p1.mgr,p2.ename from emp p1 
left join emp p2 on p1.mgr=p2.empno;

分组聚合

SQL中提供的聚合函数：
Min ：最小值 Max ：最大值 Sum ：求和 Avg ：求平均值 Count：计数
函数后要加()
查询课程表中最多（少）课时数

select max(chours) as max_chours from t_course;

查询当前的学生数（教师数）

Select count(*) as stutotal_amount from t_student;

分组聚合
对记录的分组是通过关键字GROUP BY实现的
Having子句
having子句主要用于聚集查询中，在group之后过滤掉那些不符合条件的组
如统计平均成绩超过75分的学生的学号和平均成绩

select sid,avg(score) as avgscore from t_score
group by sid
having avg(score)>=75;

having 与where的区别
“Where”是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用“聚合函数”；
“Having”是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用“聚合函数”。

子查询

查询比张老师年龄大的教师信息
 分析查询字段：教师表所有信息Select * from t_teacher;
 分析查询条件：年龄大于大山的年龄 Where tage > (张老师的年龄)
 解决问题：查询出张老师的年龄
 Select tage from t_teacher Where tname = ‘张老师’;
 结论：张老师的年龄查询作为中间结果提供给上一层查询而不输出，该类查询称为子查询
1.带有比较运算符的子查询：
子查询只能为外层查询提供一个单一值作为外层查询的比较对象(>,<,=,!=)

SELECT 列名
FROM 表名
WHERE 表达式 比较运算符 （子查询）

查询参加过课程编号为1的考试的学员

selecdt * from t_student where sid = (select sid  from t_score where cid=1);

2.带有ANY(some)或ALL谓词的子查询 [ANY | ALL | SOME]
3.带有谓词IN的子查询 [NOT] IN
这种情况适用于子查询中返回多个值时的情况

SELECT 列名
FROM 表名
WHERE 列名 [NOT] IN（子查询）

如查询所有已经安排教师上课的课程信息

select * from t_course
where cid in (select cid from t_teachcourse);

4.带有EXISTS谓词的子查询[NOT] EXISTS
存在查询，子查询不反回任何结果，只产生逻辑真TRUE或逻辑假FALSE

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
要查出 article 表中的数据，但要求 uid 必须在 user 表中存在。SQL 语句如下：

SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)

在from里面的子查询：
当子查询返回一个结果集时，那么它就相当于一个普通的表，因此，在FROM子句我们同样可以使用子查询
如：在成绩表中查询出所有学生中平均分最高的是多少

select max(avgscore) from
(select avg(score) as avgscore from t_score
group by sid);

分页查询rownum伪列

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
Oracle中最rownum的说明

永远按照默认生成；
永远只能用< or <= 不能使用 > or >=;

rownum 对于等于（=）某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
rownum对于大于（>）某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。
查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
- 即给rownum起别名，即可进行>查询

select * from(select rownum no ,id,name from student) where no>2;

     NO ID     NAME
     3 200003 李三
     4 200004 赵四

rownum对于小于某值的查询条件
rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。

  select rownum,id,name from student where rownum <3;

    ROWNUM ID     NAME
    1 200001 张一
    2 200002 王二

rownum对于区间内的数据
查询rownum在某区间的数据，必须使用子查询
例如要查询rownum在第二行到第三行之间的数据：

select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;

    NO ID     NAME
     2 200002 王二
     3 200003 李三

找了一个20W左右的数据表，以每页显示20条数据，查找第251页数据

SELECT UU.* FROM (
   SELECT ROWNUM rno, U.* FROM ITEM U ORDER BY ITEM_CODE DESC
) UU WHERE rno > 500 AND rno <= 520;
或
SELECT UU.* FROM (
SELECT ROWNUM rno, U.* FROM ITEM U WHERE ROWNUM <= 5020 ORDER BY ITEM_CODE DESC) UU 
WHERE rno > 5000;

rownum和排序

select rownum ,id,name from student order by name;

使用该语句是会发现rownum并不是有序的，是因为系统是按照记录插入时的顺序给记录排的号。

    ROWNUM   ID  NAME
     3 200003 李三
     2 200002 王二
     1 200001 张一
     4 200004 赵四

为了解决这个问题，必须使用子查询

select rownum ,id,name from (select * from student order by name);

---------- ------ ---------------------------------------------------
        ROWNUM ID     NAME
         1 200003 李三
         2 200002 王二
         3 200001 张一
         4 200004 赵四

每页显示20条
select * from (select * from tabname order by name) where rownum<20 或 select * from tabname where rownum<20 order by name
假如要返回前5条记录：

select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6) 
select * from tablename where rownum != 10;返回的是前９条记录。

取出第11-20条记录!(先选再排序再选)

select * from ( 
select rownum r,a from yourtable 
where rownum <= 20 
order by name ) 
where r > 10

排序伪列时
要先排序再选则须用select嵌套：内层排序外层选。
rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum 是在查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:
- 1：假如判定条件是常量，则：
  只能 rownum = 1, <= 大于1 的自然数， = 大于1 的数是没有结果的；大于一个数也是没有结果的
  即当出现一个 rownum 不满足条件的时候则查询结束 this is stop key（一个不满足，系统将该记录过滤掉，则下一条记录的rownum还是这个，所以后面的就不再有满足记录，this is stop key）；
- 2：假如判定值不是常量，则：若条件是 = var , 则只有当 var 为1 的时候才满足条件，这个时候不存在 stop key ,必须进行full scan ,对每个满足其他where条件的数据进行判定，选出一行后才能去选rownum=2的行……

若嫌麻烦则记下面两种格式
查询20-40数据，20-40为每页范围，查询效率一要比二高
1.

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
WHERE ROWNUM <= 40  
)  
WHERE RN >= 21

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 21 AND 40

下一步：
链接: Oracle二.

鱼雨羽

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
oracle一基础查询：单表，多表查询（交叉，内联，外联），分组聚合，子查询语法。

查询所有信息select *from table1；- - 查询table1表中的所有信息条件查询（多列，多条件查询）从学生表中检索出2班性别为女性的学生的姓名和电话Select Sname,Stel from t_student Where Sclass = 2 and Ssex =‘f’;选择无重复行 distinct关键字去除查询结果中多有的重复行select distin...
复制链接

扫一扫