Oracle表管理

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

表名和列的命名规则 

必须以字母开头 

长度不能超过30个字符 

不能使用oracle的保留字 

只能使用如下字符 A-Z，a-z，0-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。从公元前4712年1月1日到公元4712年12月31日的所有合法日期

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

TIMESTAMP(n)

字符类 

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

二进制数据 

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

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

数字型

number范围 -10的38次方 到 10的38次方 
可以表示整数，也可以表示小数 
number(5,2) 
表示一位小数有5位有效数，2位小数 
范围：-999.99到999.99 
number(5) 
表示一个5位整数 
范围99999到-99999 

日期类型 

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

表结构创建，修改和删除

 创建表

--学生表 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’ 是 1999年6月9日 

修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表） 
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的员工，同时姓名首写字母是大写的Jselect * 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;

复杂查询

分组查询

数据分组 ——max，min， 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;

对分组查询的总结
1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中) 
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by 
3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在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(并集∪)

--查询薪水大于2500和job为MANAGER的员工

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

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

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

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

3). intersect(交集∩)

--查询薪水大于2500并且job为MANAGER的员工

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》