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(十)-------------------创建与管理表

常见的数据库对象 1、 表:基本数据存储集合,由行和列组成 2、 视图:从表中抽出的逻辑上相关的数据集合 3、 序列:提供有规律的数值 4、 索引:提高查询的效率 5、 同义词:给对象起别名 用户定义...
  • w_l_j
  • w_l_j
  • 2012-03-15 19:09
  • 749

Oracle学习----创建和管理表

数据类型 字符型 char()             1..2000 varchar()       ...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

oracle的表的数据管理3 -- 复杂查询

oracle的复杂查询,oracle的分页查询,oracle的子查询

Oracle基础知识笔记(9) 表的创建及管理

对于数据库而言实际上每一张表都表示的是一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、视图、索引、序列、约束等等,都属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分...

Oracle学习(十六)之建表和审计以及权限管理

1、创建表,要求如下: 1)以system用户登录,创建表,要求如下, 2)– 表名为“EMP_EDCG4”, 3)– 表的说明为“员工基本信息(普通表)”, ...

ORACLE分区表的使用和管理

ORACLE分区表的使用和管理 1创建和使用分区表 Oracle分区表分为四类:范围分区表;列表分区表;哈希分区表;组合分区表 范围分区表 创建一个按字段数据范围分区的表,分区置于指定的不...

ORACLE 操作和管理表

Sql代码   数据库对象:   :: Table:存储数据   :: View:来自一个或多个表的数据的子集   :: Sequence:数字值发生器 ...

oracle分区表的使用和管理2

1创建和使用分区表 Oracle分区表分为四类:范围分区表;列表分区表;哈希分区表;组合分区表 范围分区表 创建一个按字段数据范围分区的表,分区置于指定的不同表空间中   ...

ORACLE创建和管理表

表的命名规则 表名和列名: l  必须以字母开头 l  必须在 1–30个字符之间 l  必须只能包含 A–Z,a–z, 0–9, _, $, 和 # l  必须不能和用户定义的其他对象重名 l  ...

oracle表管理

一、用户管理1、创建用户 语法: CREATE USER 用户名 IDENTIFIED BY 密码;例: CREATE USER wfx IDENTIFIED BY tiger; 2、...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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