oracle表管理及常用命令

第三章 表的管理

一、表名命名规则

1.必须以字母开头
2.长度不能超过30字符
3.不能使用oracle保留字
4.只能使用如下字符A-Z，a-z,0-9,$,#等

二、oracle支持的数据类型

1.字符型
char 定长，最长为2000 例：create table users(userName char(10),...)
varchar2(),变长，最大4000字符
clob(character lager objiect)  字符型大对象，最大4G
2.数字型
number 范围10的38次方，可以表示整数，也可以表示小数
例：number(5,2) 表示一个小数有5位有效数据，2为小数。 范围-999.99-999.99
例：number(5) 表示一个五位整数，范围-99999-99999
3.日期类型
date 包含年月日和时分秒
timestamp 这是orcale9i对date数据类型的扩展
4.图片
blob  二进制数据，可以存放图片/声音   4G
一般不会再数据库中直接图片视频，一般存连接，若需要安全性非常高，则直接存入

三、表管理

1.创建表

SQL>create table student( --表名
xh number(4),  --学号
xm varchar2(20),--姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
)；
SQL>creat table calsses(
classId number(2),
cname varchar2(40)
);

2.修改表

(1).添加一个字段
sql>alter table student add (calssid number(2));
(2).修改字段的长度
sql>alter table student modify (vm varchar2(30));
(3).修改字段的类型/或是名字（不能有数据）
sql>alter table student modify (xm char(30));
(4).删除一个字段
sql>alter table student drop column sal;
(5).修改表名字
sql>rename student to stu
(6).删除表
sql>drop table student;

3.添加数据

(1).sql>insert into student values('a001,'张三'，'man','1-10月-1999','2222');
注：oracle中默认的日期格式是：'DD-MON-YY'
(2).修改默认格式
sql>alter session set nls_date_format ='yyyy-mm-dd';
(3).添加空值
sql>insert into student(xh,xm,sex,birthday) values (3,'aa','女','null')
(4).查询空值的数据
sql> select * from student where birthday is null
(5).改一个字段
sql>update student set sex='女' where xh='A001'；
sql>update student set sal=sal/2 where sex='man';
sql>update student set sal=sal/2,calssId=3 where sex='man';
(6).修改含有null值的数据
is null

4.删除数据

sql>delete from student ; 删除所有记录，表结构还在，写日志，可以恢复，速度慢
使用rollback恢复数据,前提是在delete之前有savepoint
sql>savepoint aa(保存点名称)
sql>rollbock to aa;
sql>truncate table student; 删除表中的所有记录，表结构还在，不写日志，无法找回，速度快
sql>drop table student;  删除表的结构和数据

5.查询

(1). 查看表机构
sql> desc 表名
(2).查询指定列的速度>查询所有列
sql>set timing on; 打开显示操作时间的开关
(3).取消重复行
select distinct deptno,job from emp;
(4).使用算术表达式
sql>select sal*13 "年工资"，ename from emp;
sql>select sal*13+nvl(comm,0)*13 "年工资"，ename from emp;
(5).使用where子句
sql>select ename,sal from emp where sal>3000;
sql>select ename,hiredate from emp where hiredate>'1-1月-1982'；
sql>select ename,sal from emp where sal>=2000 and sal<=2500;
(6).使用like操作符
%：表示任意0到多个字符
sql>select ename sal from emp where ename like 'S%';
_:表示任意单个字符
sql>select ename sal from emp where ename like '__O%';
(7)在where中使用in
sql>select * from emp where empno in (7844,234,456);
(8)使用 is null
sql>select * from emp where mgr is null;
(9)使用逻辑运算符
sql>select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
(10)使用order by
sql>select * from emp order by sal (asc);
sql>select * from emp order by sal desc;
sql>select * from emp order by deptno asc,sal desc; 部门升序，工资降序
SQL>select ename ,sal*13 as "年薪" from emp order by "年薪";

6.oracle表复杂查询

(1)数据分组-max，min,avg,sum,count
sql>select min(sal),max(sal) from emp ;
sql>select ename,sal from emp where sal =(select max(sal) from emp);
sql>select *from emp where sal<(select avg(sal) from emp);
(2)group by 和 having子句
sql>select avg(sal),max(sal),deptno from group by deptno;
sql>select avg(sal),min(sal),deptno,job from emp group by deptno,job;
sql>select deptno, avg(sal),max(sal)from emp group by deptno;
sql>select deptno, avg(sal),max(sal)from emp group by deptno having avg(sal)>2000;
注：1.分组函数只能出现在选择列表、having、order by子句中
    2.如果在select语句中同时包含有group by,having,order by 那么他们的顺序是group by ,having, order by
    3.在选择列中如果有列，表达式、分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则就会出错

7.多表查询和子查询

1).
sql>select a1.ename,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;(笛卡尔积，条件>=表的数量-1)
sql>select a1.ename, a2.dname,a1.sal, from emp a1, dept a2 where a1.deptno=a2.deptno order by a2.dname;
2).自连接
sql>select worker.ename,boss.ename from emp worker,emp boss where worker.empno = boss.mgr;
sql>select worker.ename,boss.ename from emp worker,emp boss where worker.empno = boss.mgr and worker.ename = 'ford';
3).子查询也叫嵌套查询
sql>select ename from emp where deptno = (select deptno from emp where ename ='SMITH');
//数据库在执行sql时从左向右，where条件中尽可能将能把筛掉大部分的数据放在左面
4).单行子查询
如上
5).多行子查询
sql>select * from emp where job in (select distinct job from emp where deptno =10 )
6).在多行子查询中使用all操作符
sql>select ename,sal,deptno from emp where sal >all(select sal from emp where deptno =30);
sql>select ename,sal,deptno from emp where sal >(select max(sal) from emp where deptno=30);
第二句效率高
7).在多行子查询中使用any操作符
sql>select ename,sal,deptno from emp where sal > any(select sal from emp where deptno =30);
8).多列子查询
sql>select * from emp where (deptno,job)=(select deptno,job from emp where ename ='SMITH');
9).在from子句中使用子查询
问题：显示出高于自己部门平均工资的员工的信息
sql>select deptno, avg(sal) as mysal from emp group by deptno;
sql>select * from emp a2 ,(select deptno,avg(sal) as mysal from emp group by deptno) a1 where a1.deptno = a2.deptno and a1.mysal < a2.sal;
给表取别名时不要加as
10).使用子查询插入数据
当处理行迁移或者装载外部表的数据到数据库中时
首先建表：
sql>create table kkk(myId number(4),myname varchar2(50).myDept number(5));
然后导数：
sql>insert into kkk(MyId,myname,mydept) select empno,ename,deptno from emp where deptno =10;
11).使用子查询更新数据
sql>update emp set (job,sal,comm) =(select job,sal,comm from emp where ename ='SMITH') where ename='SCOTT'

8.分页

oracle分页一共三种方式
(1).rownum分页
sql>select a1.*,rownum rn from (select * from emp) a1;
sql>select a1.*,rownum rn from (select * from emp) a1 where rownum<=10;
sql>select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10)where rownum>=6;
sql>select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<=10)where rownum>=6;
(2).根据rowid来分
11.用查询结果创建表
sql>create table myemp2 (id,ename,sal) as select empno,ename,sal from emp;

9.合并查询

(1)union 去重复
sql>select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job ='MANAGER';
(2)union all 不去重复
(3)intersect 取交集
(4)minus 差集，显示存在第一个集合中，而不存在第二个集合中的数据

四、创建新的数据库

1.通过oracle工具向导

dbca(数据库配置助手)