Oracle表管理

原创 2015年11月21日 13:59:19

Oracle表管理

oracle表的管理(数据类型,表结构创建,修改和删除,表数据CRUD操作)

表名和列的命名规则 

必须以字母开头 

长度不能超过30个字符 

不能使用oracle的保留字 

只能使用如下字符 A-Za-z0-9$,#等 

 

oracle支持的数据类型

分类

数据类型

说明

文本、二进制类型

char(size) char(20)

定长 最大2000字符

varchar(size)  varchar(20)

变长 最大4000字符

nchar(n)

Unicode数据类型 ,定长 最大2000字符

nvarchar2(n)

Unicode数据类型 ,变长 最大4000字符

clob(character large object)

字符型大对象 ,最大4G

 

blob(binary large object)

二进制大对象, 可以存放图片/声音,最大4GB

数值类型

 

number(p,s)

 

p为整数位,s为小数位.范围: 1 <= p <=38, -84 <= s <= 127

保存数据范围:-1.0e-130 <= number value < 1.0e+126

保存在机器内部的范围: 1 ~ 22 bytes

时间日期

date

包含年月日,时分秒。默认格式:DD-MON-YYYY。从公元前471211日到公元47121231日的所有合法日期

n的取值为0~9.表示指定TIMESTAMP中秒的小数位数。N为可选。如果n0timestampdate等价[不推荐]

TIMESTAMP(n)


字符类 

char    定长 最大2000个字符。 
例子:char(10)  ‘switch’5个字符放switch’,后添5个空格补全。
varchar2(20)  变长  最大4000个字符。 
例子:varchar210) switch’ oracle分配5个字符。
clob(character large object) 字符型大对象 最大4G 
char 查询的速度极快浪费空间,查询比较多的数据用。 
varchar 节省空间 

二进制数据 

blob 可以存放图片/声音  最大4G   

一般来讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。 


数字型

number范围 -1038次方 到 1038次方 
可以表示整数,也可以表示小数 
number(5,2) 
表示一位小数有5位有效数,2位小数 
范围:-999.99999.99 
number(5) 
表示一个5位整数 
范围99999-99999 

日期类型 

date 包含年月日和时分秒   oracle默认格式  1-1-1999 
timestamp 这是oracle9idate数据类型的扩展。可以精确到毫秒。 


表结构创建,修改和删除

 创建表

--学生表 
create table student (    ---表名 
          xh       number(4),   --学号 
          xm    varchar2(20),   --姓名 
          sex      char(2),     --性别 
          birthday date,         --出生日期 
          sal      number(7,2)   --奖学金 
); 
--班级表 
CREATE TABLE class( 
    classId NUMBER(2), 
    cName VARCHAR2(40) 
); 

修改表 

 添加一个字段

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

SQL>ALTER TABLE student add (classId NUMBER(2)); 
修改一个字段的长度 

sql> alter table 表名  modify (字段名 字段类型);
SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30)); 
修改字段的类型/或是名字(不能有数据) 不建议做 

sql>alter table 表名 modify (字段名 字段类型);
SQL>ALTER TABLE student modify (xm CHAR(30)); 
删除一个字段  不建议做(删了之后,顺序就变了。加就没问题,因为是加在最后面)

sql>alter table 表名 drop column 字段名;
SQL>ALTER TABLE student DROP COLUMN sal; 
 修改表的名字   很少有这种需求

sql>rename 原来表名 to 新表名;
SQL>RENAME student TO stu; 

删除表 

sql>drop table 表名;
SQL>DROP TABLE student; 


表数据CRUD(create retrieve update delete)操作

添加数据 

INSERT INTO table [(column [, column...])]

VALUES(value [, value...]);
所有字段都插入数据 
INSERT INTO student VALUES ('A001', '张三', '', '01-5-05', 10); 
oracle中默认的日期格式‘dd-mon-yy’ 

dd日子(天) mon 月份  yy  2位的年  

例如:‘09-6-99’ 是 199969日 


修改日期的默认格式(临时修改,数据库重启后仍为默认;如要修改需要修改注册表) 
ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd'; 
修改后,可以用我们熟悉的格式添加日期类型: 
INSERT INTO student VALUES ('A002', 'MIKE', '', '1905-05-06', 10); 


 插入部分字段
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', ''); 


 插入空值
INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '', null); 


修改数据 

UPDATE  tbl_name    

SET col_name1=expr1 [, col_name2=expr2 ...]    

[WHERE where_definition]   

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行。
修改一个字段

UPDATE student SET sex = '' WHERE xh = 'A001'; 
修改多个字段
UPDATE student SET sex = '', birthday = '1984-04-01' WHERE xh = 'A001'; 


修改含有null值的数据 
SELECT * FROM student WHERE birthday IS null; 


删除数据

delete from tbl_name       

[WHERE where_definition] 

  
DELETE FROM student; 
删除所有记录,表结构还在,写日志,可以恢复的,速度慢。 
Delete 的数据可以恢复。 


savepoint a; --创建保存点 
DELETE FROM student; 
rollback to a;  --恢复到保存点 
一个有经验的DBA,在确保完成无误的情况下要定期创建还原点。 


DROP TABLE student; --删除表的结构和数据; 
delete from student WHERE xh = 'A001'; --删除一条记录; 
truncate TABLE student; --删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。


查询数据

查询所用的表是Oracle自带的用户Scott下的几张表(emp,dept,salgrade),如果用的是11g及以上要先解锁scott用户。

SELECT [DISTINCT] *|{column1, column2. column3..}

FROM table [where condition] [group by column1[,column2...] [having condition]] [order by column1[,column2...] ASC |[DESC]];

 

Select 指定查询哪些列的数据。

column指定列名。

*号代表查询所有列。

From指定查询哪张表。

DISTINCT可选,指显示结果时,是否剔除重复数据

where代表查询要满足什么条件。

group by表示按什么分组,PS:如果在不用子查询的情况下,想在select中显示某列,则必须要放在group by 后。

having 表示分组后要满足什么条件。

order by 表示按什么分组,默认ASC升序,可以使用DECS降序。

 

查看表结构

sql>desc 表名;

PS:非SQL语句,但是却是Oracle一个好用的表述表结构的命令。


简单查询

--查询所有员工

select * from emp;

 

使用算术表达式 nvl  null

--查询所有员工的年工资
select sal * 13 + nvl(comm * 13, 0) "年工资", ename from emp;

--查询工资高于3000的员工
select * from emp where sal > 3000;

--查询1982年1月1号以后入职的员工
select * from emp where hiredate > '1-1月-1982';

--显示工资在2000-2500的员工
select * from emp where sal > 2000 and sal < 2500;
select * from emp where sal between 2001 and 2499;

使用like操作符 

%:表示0到多个字符  _:表示任意单个字符 

--显示首字母为S的员工姓名和工资
select ename, sal from emp where ename like 'S%';

--显示第三个字母为O的员工信息
select * from emp where ename like '__O%';
where条件中使用in
--显示empno为123,345,800的员工信息
select * from emp where empno = 123 or empno = 345 or empno = 800;
select * from emp where empno in (123, 345, 800);

使用is null的操作符
--显示没有上级的人的员工信息
select * from emp where mgr is null;
使用逻辑操作符号
--查询工资高于500或者岗位是MANAGER的员工,同时姓名首写字母是大写的J
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
使用order by 字句   默认ASC 
--按照工资从低到高的顺序显示员工的信息
select * from emp order by sal ASC;

--按照工资从高到低的顺序显示员工信息
select * from emp order by sal DESC;

--按照部门号升序而员工工资降序排列
select * from emp order by deptno ASC, sal DESC;

--按照部门号升序而入职时间降序排列
select * from emp order by deptno ASC, hiredate DESC;

复杂查询
分组查询

数据分组 ——maxmin, avg, sum, count 

--显示所有员工中的最高工资和最低工资
select max(sal) 最高工资, min(sal) 最低工资 from emp; 

--显示所有员工中的最高工资和最低工资并显示它们的名字
select ename,sal from emp where sal = (select max(sal) from emp) or sal = (select min(sal) from emp);

--显示工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);

group by 和 having子句

--显示每个部门的平均工资和最高工资
select deptno, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno order by deptno;

--显示每个部门的每种岗位的平均工资和最高工资
select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job;

--显示每个部门的每种岗位的平均工资和最高工资,并按部门号升序,平均工资降序排列
select deptno, job, avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno, job order by deptno ASC,avg(sal) DESC;

--显示平均工资低于2000的部门号和他的平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) < 2000;

对分组查询的总结
分组函数只能出现在选择列表、havingorder by子句中(不能出现在where
如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by 
在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by 子句中,否则就会出错。 
SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 
这里deptno就一定要出现在group by 中 


多表查询
--显示员工名,员工工资以及所在部门名
select ename, sal, dname from emp,dept where emp.deptno = dept.deptno;

--显示部门号为10的部门名、员工名和工资
select dname,ename,sal from emp e, dept d where e.deptno = d.deptno and d.deptno = 10;

--显示各个员工的姓名、工资、工资级别
select e.ename,e.sal,s.grade from emp e,salgrade s where sal between s.losal and s.hisal;

--显示员工名,员工工资及所在部门名字,并按部门排序
select e.ename,e.sal,d.dname from emp e, dept d where e.deptno = d.deptno order by d.deptno;
自连接
--显示某个员工的姓名及其上级领导的姓名
select e1.ename 下级,e2.ename 上级 from emp e1, emp e2 where e1.mgr = e2.empno;
--内连接
select e1.ename 下级,e2.ename 上级 from emp e1 inner join emp e2 on e1.mgr = e2.empno;
子查询 

单行子查询

--显示与SMITH同一部门的所有员工
select * from emp where deptno = (select deptno from emp where ename='SMITH') and ename != 'SMITH';

多行子查询

--查询和部门10工作相同的雇员的名字、岗位、工资、部门号
select ename,job,sal,deptno from emp where job in(select job from emp where deptno = 10) and deptno != 10;
select ename,job,sal,deptno from emp where job = any(select job from emp where deptno = 10) and deptno != 10;

--显示工资比部门30的所有员工的工资还要高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno=30);

--显示工资比部门30的任意一个员工的工资还要高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno=30);
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);


补充:带有ANY或ALL谓词的子查询

子查询返回单值时可以用比较运算符,返回单列多行值时可以使用ANY或ALL谓词,使用ANY或ALL谓词时则必须同时使用比较运算符,其语义为:

>ANY 大于子查询结果的某个值
>ALL 大于子查询结果中的所有值
<ANY 小于子查询结果中的某个值
<ALL 小于子查询结果中的所有值
>=ANY 大于等于子查询结果中的某个值
>=ALL 大于等于子查询结果中的所有值
<=ANY 小于等于子查询结果中的某个值
<=ALL 小于等于子查询结果中的所有值
=ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值(通常没有实际意义)
!=(或<>)ANY 不等于子查询结果中的某个值
!=(或<>)ALL 不等于子查询结果中的任何一个值

事实上,用聚合函数来实现子查询通常比直接用ANY或ALL查询效率要高,ANY与ALL与聚合函数的对应关系如下所示:

 

=

<>或!=

<=

>=

ANY

IN

-

<MAX

<=MAX

>MIN

>=MIN

ALL

--

NOT IN

<MIN

<=MIN

>MAX

>=MAX



多列子查询

--查询与SMITH的部门和岗位完全相同的所有员工
select e1.* from emp e1,(select deptno,job from emp where ename='SMITH') e2 where e1.deptno = e2.deptno and e1.job = e2.job and ename != 'SMITH';

from子句中使用子查询

--显示高于自己部门平均工资的员工信息
select e1.*,e2.avgsal from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal > e2.avgsal;

对子查询的总结: 
在这里需要说明的当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。 
注意:别名不能用as,如:SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 

红字部分的as应该去掉,因为表指定别名时,不能加as
PS给表取别名的时候,不能加as;但是给列取别名,是可以加as

分页查询

1.根据rowid来分 执行时间0.03

2.按分析函数来分 执行时间1.01

3.按rownum来分  执行时间0.1秒 

同一语句1执行效率最高,但因为过于麻烦,所以还是掌握3最实在。

select * from (select e1.*,rownum rn from (select * from emp) e1 where rownum <= 10) where rn >= 6;

select * from (select e1.*,rownum rn from (select ename,job,sal from emp order by sal DESC) e1 where rownum <= 10) where rn >=6;

PS:红色字体是可以更改的,其余部分可以当做公式用。想要修改查询范围,按什么排序。。。都是在最内层的select中修改的。那两个数值是获取范围的,

集合查询

1). union(并集∪)

--查询薪水大于2500jobMANAGER的员工

select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job='MANAGER';


2).union all(并集∪)(不会取消重复行,而且不会排序)

--查询薪水大于2500jobMANAGER的员工(重复数据不能除去)

select ename,sal,job from emp where sal > 2500 union all select ename,sal,job from emp where job='MANAGER';


3). intersect(交集∩)

--查询薪水大于2500并且jobMANAGER的员工

select ename,sal,job from emp where sal > 2500 intersect select ename,sal,job from emp where job='MANAGER';

 

4). minus(差集-)

--查询薪水大于2500但是job不为MANAGER的员工

select ename,sal,job from emp where sal > 2500 minus select ename,sal,job from emp where job='MANAGER';

 

----------参考《韩顺平玩转Oracle》

 

版权声明:如需转载,请联系本人获取许可且必须注明出处,详见联系方式。

相关文章推荐

Oracle函数

Oracle函数 函数的分类: 单行函数:对每行输入值进行计算,得到相应的计算结果,返回给用户,也就是说,每行作为一个输入参数,经过函数计算得到每行的计算结果。比如length   ...

Oracle管理权限和角色

oracle管理权限和角色 基本介绍      当刚刚建立用户时,用户没有任何权限,也不能执行任何操作。如果要执行某种特定的数据库操作,则必需为其授予系统的权限;如果用户要访问其它方案的对象,则...

Oracle DB 使用DDL语句创建和管理表

• 对主要的数据库对象进行分类 • 查看表结构 • 列举列可以使用的数据类型 • 创建简单的表 • 说明创建表时如何创建约束条件 • 描述方案对象如何工作 • 数据库对象 – 命名规则 • CRE...
  • rlhua
  • rlhua
  • 2013年10月21日 10:02
  • 10566

Oracle学习笔记(9)----------- 表的创建及管理

Oracle笔记(九) 表的创建及管理 对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,都属于...

Oracle起步学习(四)--表的管理,操作

一、Oracle表的管理 1.表名和列队

oracle基础知识1----用户(创建用户)和表空间、管理表

sys: Oracle的一个超级用户,主要用来维护系统信息和管理实例,只能以sysdba或sysoper角色登录     system: Oracle默认的系统管理员,拥有DBA权限,通常用来管理Or...

Oracle 11g 学习六:表的创建及管理

一、表的创建及管理

SQL Server与Oracle对比学习:表的管理和组织

我们知道数据库,顾名思义.最重要的东东就是管理数据,而数据在系统中主要是保存在表(table)中,所以数据库差不多所有的机制和操作都是围绕着table在打转转了.表的重要性可想而知.在表在数据库是怎么...

ORACLE数据库的表管理(自学笔记)

该文章为本人自学oracle时的部分笔记,面向oracle初学者,内容为oracle数据库表结构及表内容的管理知识(增删改查),注:不含查询部分(select)。...

【Oracle学习】 之 管理表

一、数据类型
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle表管理
举报原因:
原因补充:

(最多只允许输入30个字)