目录
5)在where条件中使用in,这个功能与or相似,在某些情况下比or好用。
6)使用 is null 的操作符,在确定条件为null时使用。
1)数据分组:max(最大值),min(最小值),avg(平均值),sum(求和),count(统计)
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;
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;
2)union all
该操作赋予union相似,但是它不会取消重复行,而且不会排序。
3)intersect——有集合的交集的味道
使用该操作符用于取得两个结果集的交集。
4)minus——有集合的差集的味道
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据。