Oracle数据库 基本查询

最新推荐文章于 2024-05-11 16:05:29 发布
最新推荐文章于 2024-05-11 16:05:29 发布
阅读量499 收藏
点赞数
文章标签： oracle 数据库 oracle子查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wshiweng/article/details/101353943
版权

1子查询
1.1子查询语法
select select_list from table
where expr operator(select select_list from table)

1.2子查询注意的问题：

  1. 子查询需要添加括号
  2. 合理的书写风格
  3. 可以在where select having from后面 都可以使用子查询
  4. 不可以在group by后面使用子查询
  5. 强调from后面的子查询
  6. 主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可
  7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
  8. 一般先执行子查询，再执行主查询；但相关子查询例外
  9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
    10.子查询中的null
    1.3 举例子
    了查询需要括号 查询工资比SCOTT高的员工信息
    SQL>select * from emp where sal > (select sal from emp where ename=‘SCOTT’)
    select后可以添加子查询 查询员工记录后，每一行后面显示总裁
    SQL> select empno,ename,sal,(select ename from emp where empno=7839) 总裁 from emp;
    from后面的子查询 查询员工信息：员工号 姓名 月薪
    SQL> select * from (select empno,ename,sal from emp);
    查询员工信息：员工号 姓名 月薪 年薪
    SQL> select * from (select empno,ename,sal,sal12 annsal from emp);
    上面的语句跟select empno,ename,sal,sal    12 annsal from emp性能一样的，oracle内部会重构
    主查询和子查询可以不是同一张表；只要子查询返回的结果 主查询可以使用 即可 查询部门名称是SALES的员工，实现方法有两种
    SQL>select * from emp where deptno=(select deptno from dept where dname=‘SALES’);
    SQL>select e.* from emp e,dept d where e.deptno=d.deptno and d.dname=‘SALES’;
    in 在集合中 查询部门名称是SALES和ACCOUNTING的员工
    SQL> select * from emp where deptno in (select deptno from dept where dname=‘SALES’ or dname=‘ACCOUNTING’);
    any: 和集合中的任意一个值比较 查询工资比30号部门到少一个员工高的员工信息【下面两行是等价】
    SQL> select * from emp where sal > any (select sal from emp where deptno = 30);
    SQL> select * from emp where sal > (select min(sal) from emp where deptno = 30);
    all 和集合中的所有值比较 查询工资比30号部门所有员工高的员工信息【下面两行是等价】
    SQL> select * from emp where sal > all (select sal from emp where deptno = 30);
    SQL> select * from emp where sal > (select max(sal) from emp where deptno = 30);
    not in(a,b,null)
    not in中不能有null
    in 中可以有null
    多行子查询中的null 查询是老板（管理层）的员工
    SQL> select * from emp where empno in(select mgr from emp);
    查询不是老板(管理层)的员工
    SQL> select * from emp where empno not in(select mgr from emp);【not in中不能有null】
    SQL> select * from emp where empno not in(select mgr from emp where mgr is not null);【这个写法才正确】

2 集合运算

举例子
这里了解union就可以,intersect和minus原理一样
Union
注意的问题：
1.参与运算的各个集合必须列数相同且类型一致
2. 采用第一个集合作为最后的表头
3. order by 永远在最后
排序是不会影响总结果集， 只会影响结构。
4. 括号 查询10和20号部门的员工
SQL> select * from emp where deptno in (10,20);
SQL> select * from emp where deptno=10 or deptno=20;
上面的代码等价于下面的代码
select * from emp where deptno=10
加上
select * from emp where deptno=20
不推荐
SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);
等价
select deptno,job,sum(sal) from emp group by deptno,job
+
select deptno,sum(sal) from emp group by deptno
+
select sum(sal) from emp
等价
select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp
开发中少用union,性能比较差
Set timing on/off 可以查时间

3 课堂练习
3.1 rownum行号 （ 数据初始化的时候就已经存在 ）
SQL> select rownum,e.* from emp e;
rownum永远按照默认的顺序生成
SQL> select rownum,e.ename,e.sal from emp e order by sal desc;
rownum只能使用 < <=; 不能使用> >=

3.2 rowid行地址
SQL> select rowid,empno,ename,sal from emp;

SQL> select * from emp where rowid=‘AAAMfPAAEAAAAAgAAA’;

3.3 临时表

  1. create global temporary table *****
  2. 自动创建: order by
    特点：当事务或者会话结束的时候，表中的数据自动删除
    SQL> create global temporary table tmp_table1 (tid number,tname varchar2(20)) on commit delete rows;
    SQL> insert into tmp_table1 values (1,‘Tom’);
    SQL> select * from tmp_table1;
    SQL> commit;【commit后，数据会消失】
    SQL> desc tmp_table1;

3.4行转列
wm_concat(varchar2) 组函数

SQL> select deptno,wm_concat(ename) namelist from emp group by deptno;

第一题：【oracle的分页】 2 每页3行
Select * from emp where limit (3,3)
找到员工表中工资最高的前三名
select e.ename,e.sal from (select * from emp order by sal desc) e where rownum <=3
分页【这样写是不对的】，rownum不能使用> >=
select e.ename,e.sal
from (select * from emp order by sal desc) e
where rownum >=3 and rownum <=6
分页的一种正确写法
select * from (select rownum r,e.* from (select * from emp order by sal) e where rownum <=6) where r>3

第二题：
找到员工表中薪水大于本部门平均薪水的员工
1.求出部门的平均工资
2.判断员工是否工资大于本部门的平均工资
第一种写法
select e.empno,e.ename,e.sal,d.avgsal
from
emp e, (select deptno,avg(sal) avgsal from emp group by deptno) d
where e.deptno=d.deptno and e.sal > d.avgsal

第二种写法：【相关子查询：将主查询中的值 作为参数传递给子查询】
select e.empno,e.ename,e.sal,(select avg(sal) from emp where deptno = e.deptno) avgsal
from emp e
where e.sal > (select avg(sal) from emp where deptno = e.deptno)
第三种写法：
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno group by deptno);

第三题：
计算每年入职的员工数

select count(*) Total,
sum(decode(to_char(hiredate,‘yyyy’),‘1980’,1,0)) “1980”,
sum(decode(to_char(hiredate,‘yyyy’),‘1981’,1,0)) “1981”,
sum(decode(to_char(hiredate,‘yyyy’),‘1982’,1,0)) “1982”,
sum(decode(to_char(hiredate,‘yyyy’),‘1987’,1,0)) “1987”
from emp

4数据处理
4.1SQL 的类型
1. DML（data manipulation Language 数据操作语言）: insert update delete select
2. DDL(Data Definition Language 数据定义语言):
create table,alter table,drop table,truncate table
create/drop view,sequence(序列),index,synonym(同义词)
3. DCL(Data Control Language 数据控制语言): grant(授权) revoke（撤销权限）
4.2 例子

PreparedStatement
预处理 insert into emp (empno,ename,sal,deptno) values (?,?,?,?);
地址符 & SQL>insert into emp (empno,ename,sal,deptno) values (&empno,&ename,&sal,&deptno)

SQL> select empno,ename,sal,&t from emp;

SQL> select * from &t;

批处理 复制表结构
SQL> create table emp10 as select * from emp where 1=2;
一次性将emp中，所有10号部门的员工插入到emp10中
SQL> insert into emp10 select * from emp where deptno=10;
delete和truncate的区别: 1. delete逐条删除；truncate先摧毁表，再重建
2.(根本)delete是DML（可以回滚），truncate是DDL（不可以回滚）
3. delete不会释放空间 truncate会
4. delete可以闪回 (flashback), truncate不可以
5. delete会产生碎片；truncate不会
SQL> set feedback off
SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql
SQL> select count(*) from testdelete;
SQL> set timing on
SQL> delete from testdelete;
SQL> drop table testdelete purge;
SQL> @C:\课程\JavaEE\oracle【3天】\资料\testdelete.sql
SQL> truncate table testdelete;
SQL> set feedback on
SQL> set timing off

事务的标致 1. 起始标志：事务中第一条DML语句
2. 结束标志：提交： 显式 commit
隐式 正常退出（exit），DDL，DCL
回滚: 显式 rollback
隐式 非正常退出，掉电，宕机
保存点 SQL> create table testsavepoint (tid number, tname varchar2(20));
SQL> insert into testsavepoint values(1,‘Tom’);
SQL> insert into testsavepoint values(2,‘Mary’);
SQL> savepoint a;
SQL> insert into testsavepoint values(3,‘Maake’);
SQL> select * from testsavepoint;
SQL> rollback to savepoint a;
SQL> select * from testsavepoint;
SQL> commit;
SQL> set transaction read only;
SQL> select * from testsavepoint;
SQL>insert into testsavepoint values(3,‘Maake’);【不能在 READ ONLY 事务处理中执行插入/删除/更新操作
SQL> rollback;

5创建和管理表 （int char dou bigint（long））
表创建 SQL> create table test3(tid number,tname varchar2(20),hiredate date default sysdate);
SQL> insert into test3(tid,tname) values(1,‘Tom’);
SQL> select * from test3;
创建表，保存20号部门的员工
SQL> create table emp20 as select * from emp where deptno=20;
创建表，员工号 姓名 月薪 年薪 部门名称
SQL> create table empinfo
as
select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno
添加表字段
SQL> alter table test3 add photo blob;
修改表字段
SQL> alter table test3 modify tname varchar2(40);
删除表字段
SQL> alter table test3 drop column photo;
重命名表字段
SQL> alter table test3 rename column tname to username;
改表名
SQL> rename test3 to test5;
删除表
SQL> drop table test5

显示回收站
SQL> show recyclebin

清空回收站
SQL> purge recyclebin;
删除的表，可以从回收站查询
SQL> drop table TESTSAVEPOINT;
SQL> select * from “BIN$ULtYrrqvTlqfr+qeSXIHNA==$0”;
闪回删除
SQL> flashback table TESTSAVEPOINT to before drop;
注意：管理员没有回收站sys
创建约束表字段
SQL>create table test5
(tid number,
tname varchar2(20),
gender varchar2(2) check (gender in (‘男’,‘女’)),
sal number check(sal > 0)
)
SQL> insert into test5 values(1,‘Jone’,‘男’,1000);
SQL> insert into test5 values(1,‘Jone’,‘A’,1000);【违反检查约束条件 (SCOTT.SYS_C005420)】
SQL> insert into test5 values(1,‘Jone’,‘男’,-10);
外键关联
SQL>create table student
(sid number constraint student_pk primary key,
sname varchar2(20) not null,
deptno number constraint student_fk references dept(deptno))
SQL> insert into student values (1,‘Jone’,10);

6视图
6.1常见的数据库对象
对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的逻辑上的相关的数据集合
序列 提供有规律的数值
索引 提搞查询的效率
同义词 给对象起别名

6.2视图
视图是一张虚表
视图建立在已有表的基础，视图赖以建立的这些表称为基表
向视图提供数据的内容的语句为select语句，可以将视图理解为存储起来的select语句
视图是向用户提供基表数据的另一种形式

6.3视图的优点
限制数据访问
简化复杂查询
提供数据的相互独立
同样的数据可以有不同的显示方式
但视图不能提高性能

ora-01034 oracle not acailable

添加权限 在虚拟机里以管理员登录，并授权scott的视图权限
SQL> sqlplus sys/manager as sysdba
SQL> grant create view to scott
创建视图 SQL>create view empinfoview
as
select e.empno,e.ename,e.sal,e.sal12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno
查看视图结构 SQL> desc empinfoview;
查询视图 SQL> select * from empinfoview;
只读视图 SQL>create or replace view empinfoview
as
select e.empno,e.ename,e.sal,e.sal12 annsal,d.dname
from emp e,dept d
where e.deptno=d.deptno
with read only

7 序列
7.1 序列
可供多个用户用来产生唯一数值的数据库对象
自动提供惟一的数值
共享对象
主要用于提供主键
将序列值装入内存可以提高访问效率

7.2创建序列
SQL> create sequence myseq;
SQL> create table testseq(tid number,tname varchar2(20));
SQL> select myseq.nextval from dual;
SQL> select myseq.currval from dual;
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL> slect * from testseq;
SQL> commit;
SQL> insert into testseq values(myseq.nextval,‘aa’);
SQL>rollback

8 索引
8.1 索引
一种独立于表的模式对象，可以存储在与表不同的磁盘或者表空间
索引被删除或者损坏，不会对表产生影响，其影响只是查表的速度
索引一旦被建立，oracle管理系统会对其进行自动维护，而且由oracle管理系统决定何时使用索引，用户不用在查询查询语句中指定使用哪个索引
在删除一个表时，所有基于该表的索引会自动被删除
通过指针加速oracle服务器的查询速度
通过快速定位的方法，减少磁盘的I/O

索引可以提高查询速度，我们只需要创建索引，至于怎么使用索引，是oracle系统内部的事情

8.2 什么情况下创建索引(经常查询并且不是使用like查询，一般使用在外键。)
列中数据值分布很广
列经常在where语句（查询条件 = > >= like(不能使用索引)）或连接条件中出现 内，外，左外，右外 可以使用索引
表经常被访问，而且量很大，访问的数据大概占总量的2%~4%

8.3 什么情况下不要创建索引 (数据量小，经常更新操作的数据，经常使用like模糊查询的数据，不使用条件查询的)
表很小
经常更新
列不经常在where语句或连接条件中出现

8.4 创建索引

SQL> create index myindex on emp();

9 同义词
9.1 同义词 取别名
使用同义词访问其它对象
方便访问其它用户的对象
缩短对象名字的长度
创建同义词需要授权grant create synonym to scott

9.2 使用
SQL> create synonym myemp for emp;
SQL> select * from myemp;
SQL> create synonym hremp for hr.employees;
SQL> grant select on hr.employees to scott【需要授权才能访问其它用户的表】
SQL> select count() from hr.employees;
SQL> select count() from hremp;

菜鸟王大哼
关注
ORACLE - 数据查询
qq_45759288的博客
06-23 386
Oracle数据查询
Oracle数据库基本信息查询命令
weixin_38618988的博客
04-26 7794
Oracle数据库基本信息查询命令
Oracle数据库查询
zhangshengqiang168的博客
09-14 330
/*取得该用户下所有的表*/ select * from user_tables; /*取得表名为Sysuser的注释信息*/ select * from user_tab_comments where table_name = 'CLASSINFO'; /*取得该用户下表名为Sysuser表的结构*/ select * from user_tab_columns where table_name='CLASSINFO'; /*取得该用户下表名为sysuser表中字段的注释信息*/ select *
【ORACLE】数据库的查询
2301_76210107的博客
05-06 1633
【例1】SELECT 姓名 AS NAME,地址 AS ADDRESS,电话号码 AS PHONENUMBER FROM EMPLOYEES EHERE SEX=1;完全外连接（FULL OUTER JOIN）：结果表中除了满足连接条件的行外，还包括两个表所有行。左外链接（LEFT OUTER JOIN）：结果表中除了满足连接条件的行外，还包括左表的所有行。外连接的结果不但包含满足连接条件的行，还包括相应表中的所有行。结果表中包含编号相同的EMPLOYEES表中的姓名列和SALARY表中的所有列。
oracle数据库信息查询,oracle 数据库信息查询
weixin_26795617的博客
04-13 221
/*查询当前用户表信息/select A.column_name 字段名,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,A.Data_Scale 小数位,A.nullable 允许空值,A.Data_default 缺省值,B.comments 备注from user_tab_...
查看ORACLE数据库信息的一些SQL
domino的专栏
03-19 2158
1、查看表空间的名称及大小set linesize 140;set pages 200;column tablespace_name format a30;select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;select tablespace_name
Oracle数据库.pdf
06-16
Oracle数据库管理是数据库管理员（DBA）日常工作中不可或缺的...这些是Oracle数据库管理的基本操作，对于监控和维护数据库健康状况至关重要。理解并熟练运用这些命令和操作，可以帮助DBA更有效地管理和优化数据库系统。
资源专区-小白必看-oracle数据库基本使用
05-09
本文将针对"资源专区-小白必看-oracle数据库基本使用"这一主题，深入讲解Oracle数据库的安装以及PL/SQL Developer客户端的安装与连接。 首先，我们来看Oracle数据库的安装。在安装Oracle之前，确保你的系统满足...
oracle数据库基本操作.doc
06-03
Oracle 数据库基本操作 Oracle 数据库是一种关系型数据库管理系统，广泛应用于各种 industries 中。在本文档中，我们将对 Oracle 数据库的基本操作进行详细介绍，从表空间操作、用户、角色、权限到表操作和视图操作...
Shell脚本连接oracle数据库的实现代码
01-09
在Linux环境中，使用Shell脚本连接Oracle数据库是自动化任务中常见的一种方式，特别是当需要定期执行查询或数据处理时。本文将详细介绍如何通过Shell脚本实现这一目标，并讲解相关的关键知识点。 首先，确保你的...
oracle数据库查询语句大全
10-12
今天想查询一下Oracle数据库下所有的表名或某个用户下的所有表,半天没想起来.还是在网上找到了答案
查看ORACLE 数据库及表信息
09-26
查看ORACLE 数据库及表信息
【oracle数据库】最全最详细的数据库查询
打不倒的程序猿
07-05 1万+
必读在前言这我们先了解下查询的基本情况，这是一篇关于查询的文章，你想要知道的查询知识都在这！如果嫌文章字多，可以根据目录跳转快速阅读！！！ 查询是数据的一个重要操作。用户发送查询请求，经编译软件变异成二进制文件供服务器查询，后返回查询结果集给用户，查询会产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正的存储，每次执行查询只是从数据表中提取数据，并按照表的形式显示出来。 **执行过程**：from--where -- group by– having– select-- order by
Oracle数据库简单查询
smcc11的博客
06-28 1567
Oracle数据库 一、导入scott.sql文件 导入scott.sql文件，使用用户表（emp表，dept表，salgrade表，bonus表） 步骤： 1.使用管理员进入dos(SQL) conn sys as sysdba; 2.查找scott.sql 文件位置（默认下载路径） C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql 3.执行脚本文件 @C:\oraclexe\app\oracle\product\11.
Oracle 数据库常用查询
一览众山小
03-21 224
1.查看当前数据库的所有用户 select username from dba_users;   2、显示当前用户 show user;   3、查看当前数据库的所有表 select * from tab/dba_tables/dba_objects/cat   4、查看用户建的所有表 select table_name from user_tables; //当前用户的...
Oracle数据库之基本查询详解(三)
最新发布
pursue_mony的博客
05-11 1039
结构化查询语言（Structured Query Language）简称SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统：同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存取方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以实用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言可以嵌套，这使得它具有极大的灵活性和强大的功能。
Oracle查询数据
jiangjiangyy的博客
08-09 780
一、查询数据SELECT SELECT关键字表示数据的检索，它由一系列子句组成，最终检索出来的数据是由子句决定的，即检索出来的数据必须满足所有子句的限制。 SELECT语句按照复杂程度可分为简单查询、WHERE条件查询、多表查询、子查询等。 1、SELECT （1）语句语法 SELECT [DISTINCT|ALL] select_list FROM table_list [where_...
Oracle数据库的状态查询
热门推荐
清风小阁
02-07 5万+
Oracle数据库的状态查询（nomount, mount, open）
oracle数据查询
Java_Fly1的博客
05-29 715
oracle数据查询
Oracle数据库统计查询与基本原理
在Oracle数据库中，统计查询是数据分析的关键环节，它能够帮助用户从海量数据中提取出有价值的信息。Oracle提供了丰富的统计函数，如SUM, AVG, MAX, MIN, COUNT等，这些被称为组函数或统计函数。组函数的主要特点是...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值