oracle的基础整理_oracle gray-CSDN博客

本文链接：https://blog.csdn.net/GRAY_KEY/article/details/86763415

5.oracle表的基本查询（select）

5）在where条件中使用in，这个功能与or相似，在某些情况下比or好用。

6）使用 is null 的操作符，在确定条件为null时使用。

7）逻辑操作符 and 、or、not

8）使用 order by 字句，对筛选的结果进行排序。

7.oracle表的复杂查询

1）数据分组：max（最大值），min（最小值），avg（平均值），sum（求和），count（统计）

2）group by 和 having 字句

11.合并查询（比 and 和 or 效率更高）

1）union——有集合的并集的味道

2）union all

3）intersect——有集合的交集的味道

4）minus——有集合的差集的味道

1.oracle表名和列的命名规则

1)表名必须以字母开头；

2)长度不能超过30字符；

3)不能使用oracle的保留字；

4)只能使用字母（A-Z,a-z）、数字（0-9）、$、#等;

2.oracle的数据类型

1)字符型

char 定长（固定长度）最大2000字符

特点：因为定长，所以查询快，但是浪费空间。

varchar2（20）变长（长度可改变）最大4000字符

特点：因为变长，所以节省空间，但是查询慢（效率慢）。

clob（character large object）字符型大对象最大4G

2)数字型

number 范围：-10的38次方至10的38次方

可以表示整数，也可以表示小数。

number（5,2）范围：-999.99至999.99

表示一个小数有5位有效数字，2位小数。

number（5）范围：-99999至99999

表示一个五位整数。

3)日期类型

date 包含年月日和时分秒

timestamp 这是oracle9i对date数据类型的扩展，精度更高，毫秒级。时间戳

4)图片

blob 二进制数据可以存放图片/声音 4G

3.oracle表的管理

1）建表

create table 表名(字段名类型,字段名类型,......);

2）删表

drop table 表名;

注：有时，由于某些约束的存在，例如，当前表的主键被其他表用作外键，会导致无法成功删除。

利用cascade constraints选项可以将约束同时删除，从而保证drop table命令一定能够成功执行。例：drop table student cascade constraints;

3）表的修改

添加一个字段

alter table 表名 add (字段名类型);

修改字段的长度

alter table 表名 modify (要修改的字段名类型);

修改字段的类型/名字（不能有数据）

alter table 表名 modify (要修改的字段名类型);

删除一个字段

alter table 表名 drop column 字段名;

修改表的名字

rename 旧表名 to 新表名;

注：mysql 中是 rename table 旧表名 to 新表名;

4）查看表

desc 表名;

4.oracle表数据的增删改

1）添加数据

所有字段都插入

insert into 表名 values(字段(键)值......);

【

oracle中默认的日期格式是‘DD-MON-YY’ 例：09-6月-99

DD 日子（天） MON 月份 YY 2位的年

可以通过下面的指令改变日期的默认格式

alter session set nls_data_format='yyyy-mm-dd'; 例：1905-05-06

】

插入部分字段

insert into 表名(部分字段) values(部分字段的键值);

插入空值

insert into 表名(部分字段) values(部分字段的键值但其中有的键值为null);

2）修改数据

修改一个字段

update 表名 set 要修改字段=键值 where 条件字段=键值;

修改多个字段

update 表名 set 字段1=键值,字段2=键值... where 条件字段=键值;

修改含有null值的数据

update 表名 set 字段=键值 where 条件字段 is null;

【

使用update语句更新数据时，既可以使用表达式或者数值直接修改数据，也可以使用子查询修改数据。

问题：希望员工SCOTT的岗位、工资、补助与SMITH员工一样。

update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH')

where ename='SCOTT';

】

3）删除数据

delete from 表名; 删除所有记录，表结构还在，写日志，可以恢复的，速度慢。

【

使用回滚命令：rollback 回滚点名字;

要使用回滚命令之前，应先设置回滚点：savepoint 回滚点名字;

要delete删除之前，先设置回滚点。

不做任何设置，回滚点只能有一个(最近一次提交后的状态)。

月光宝盒：我要回去之前！！！！！！

】

delete from 表名 where 字段=键值; 删除一条记录。

truncate table 表名; 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。

drop table 表名; 删除表的结构和数据。

5.oracle表的基本查询（select）

查看表结构

desc 表名;

查询所有列

select * from 表名;

查询指定列

select 字段1,字段2,... from 表名;

如何取消重复行

select distinct 字段1,字段2... from 表名;

6.小知识点

1）使用列的别名（as）

例：select name "姓名",sal*12 as "年收入" from 表名;

注意：别名需要使用""双引号圈中。

2）如何处理null值？

使用nvl函数来处理。

例：select sal*13+nvl(comm,0) as "年收入" from 表名;

3）如何连接字符串（||）

例：select name || 'is a' || job from 表名;

4）如何使用like操作符

%：表示任意的0到多个字符；

_：表示任意的单个字符；

5）在where条件中使用in，这个功能与or相似，在某些情况下比or好用。

6）使用 is null 的操作符，在确定条件为null时使用。

使用 is not null 的操作符，在确定条件不为null时使用。

7）逻辑操作符 and 、or、not

8）使用 order by 字句，对筛选的结果进行排序。

order by asc 升序 (不过默认就是asc，因此asc可忽略不写)

order by desc 降序

例：select 字段1,字段2... form 表名 order by 条件字段 asc/desc;

7.oracle表的复杂查询

1）数据分组：max（最大值），min（最小值），avg（平均值），sum（求和），count（统计）

对数据分组的总结

(1)分组函数只能出现在选择列表、having、order by字句中；

(2)如果在select语句中同时包含有group by，having，order by ，那么它们的顺序是

group by，having，order by 。

例：select avg(字段1),max(字段2),字段3 from 表名 group by 字段3 having avg(字段)>2000 order by 字段 desc;

(3)在选择列中如果有列、表达式和分组函数（如avg（字段）），那么这些列和表达式必须有一个出现在group by字句中，否则就会出错。

例如select avg(字段1),max(字段2),字段3 from 表名 group by 字段3 having avg(字段)>2000;

这里的字段3就一定要出现在 group by 中。

2）group by 和 having 字句

group by ：用于对查询的结果分组统计；

having 字句：用于限制分组显示结果（即筛选）；

例：select avg(字段1),max(字段2),字段3 from 表名 group by 字段3 having avg(字段)>2000;

注：在oracle数据库中，having字句不能脱离group by 字句单独使用。

having子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.

having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。

having子句限制的是组，而不是行。

where子句中不能使用聚集函数，而having子句中可以。

【

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1.执行where xx对全表数据做筛选，返回第1个结果集。

2.针对第1个结果集使用group by分组，返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

4.针对第3个结集执行having xx进行筛选，返回第4个结果集。

5.针对第4个结果集排序。

】

8.oracle的多表查询

多表查询：是基于两个或两个以上的表或是视图的查询。【笛卡尔集】

注意：多表查询的条件是至少不能少于表的个数-1

1）自连接

自连接：指在同一张表的连接查询。

2）子查询

子查询：是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

单行子查询：是指只返回一行数据的子查询语句。

where时用 =

多行子查询：指返回多行数据的子查询。

where时用 in

【

在多行子查询中使用all操作符

例：select name,sal,class form students where sal>all (select sal from students where class=5);

此种情况也可以使用单行子查询

例select name,sal,class form students where sal>(select max(sal) from students where class=5);

在多行查询中使用any操作符

例：select name,sal,class form students where sal>any (select sal from students where class=5);

此种情况也可以使用单行子查询

例select name,sal,class form students where sal>(select min(sal) from students where class=5);

注：在使用单行子查询就能取得效果的情况下，使用all跟any的多行子查询效率更低。

】

多列子查询

单行子查询是子查询只返回单列、单行数据，

多行子查询：是指返回单列多行数据，都是针对单列而言的，而多列子查询：则是指查询返回多个列数据的子查询语句。

例：select * form students where (class,sal)=(select class,sal from students where name='xiaohua');

3）在from字句中使用子查询

当在from字句中使用子查询时，该子查询会被作为一个视图来对待，因此也叫做内嵌视图（子表），当在from字句中使用子查询时，必须给子查询指定别名。

在这里有一点需要注意：给列取别名时可以用 as，但在给内嵌视图（即子表）取别名时切勿用 as，用空格隔开即可。

9.分页查询

1）在ORACLE大数据量下的分页解决方法。一般用截取ID方法，还有是三层嵌套方法。

截取ID的方法

select * from user a,(select id,rownum as num from user) b where a.id=b.id and b.num between 5 and 9;

三层嵌套的方法

select * from (select a.*,rownum r from (select * from user) a where rownum <= 9) b where b.r > 5;

2）查询变化

a.指定查询列，只需修改最里层的子查询

b.如何排序，只需修改最里层的子查询

10.用查询结果创建新表

create table 新表名 (id,name,sal) as select stuid,stuname,stusal from students;

这个命令是一种快捷的建表方法。

11.合并查询（比 and 和 or 效率更高）

为了合并多个select语句的结果，可以使用集合操作符号：union，union all，intersect，minus。

1）union——有集合的并集的味道

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。（请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。）

例：select语句1 union select语句2;