玩转Oracle（3）

//oracle的数据类型
字符型
char  定长  最大2000字符
varchar2 变长  最大4000字符
clob  字符型大对象 最大4g

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

日期类型
date  包含年月日和时分秒
timestamp 对date类型的扩展

图片类型
blob  二进制数据 可以存放图片/声音 最大4g

||-------  oracle表的管理  -------||

表名必须以字母开头，长度不能超过30个字符，不能使用oracle的保留字，只能使用A-Z，a-z，0-9，$,#等

//学生表
create table student( --表名
 xh  number(4), --学号
 xm  varchar2(20), --姓名
 sex  char(2), --性别
 birthday date,  --出生日期
 sal  number(7,2) --奖学金
);

//班级表
create table class(
 classid  number(2), --班级id
 cname  varchar2(20) --班级名称
);

//添加一个字段
alter table students add(
 classid  number(2)
);

//修改字段的长度
alter table student modify(
 xm varchar2(30)
);

//修改字段的类型/或是名字（不能有数据）
alter table student modify(
 xm char(30)
);

//删除一个字段
alter table student drop column sal;

//修改表的名字
rename student to stu;

//删除表
drop table student;

||-------  数据的管理  -------||

//插入数据
默认日期格式：dd-MON-yy
insert into student values(1,'张三','男','13-5月-2010',6070.43,0512);
修改日期默认格式：yyyy-mm-dd
alter session set nls_date_format='yyyy-mm-dd'

//插入部分值
insert into student (xh,xm,sex) values (1,'丽丽','女');

//插入控制
insert into student(xh,xm,sex,birthday) values (1,'丽丽','女',null);

//查询生日为空的学生
select * from student where birthday is null //不为空is not null

//修改一个字段
update student set sex='女' where xh=1;

//删除数据
delete from student; //删除所有记录，表结构还在，写日志，可以恢复

//回滚命令
save point a; //存储回滚点
rollback to a; //回滚到存储点

//删除表的结构和数据
drop table student;

//删除表中所有记录，表结构还在，不写日志
truncate table student;

||-------  oracle表的各种查询技巧  -------||

//查询表结构
desc emp;

//显示操作时间
set timing on;

//如何取消重复行
select dictinct deptno,job from emp;

//显示雇员的年工资
select ename as "姓名",sal*12 as "年收入" from emp;

//如果运算符中有一个null值，如果做运算，则为null
使用nvl函数来判断该值是否为null
nvl(comm,0) //如果comm的值为null时，则用0来替代

//如何连接字符串
select ename || '  is a  ' || job from emp; //显示结果：SMITH  is a  CLERK

//如何显示工资高于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 between 2000 and 2500;

//如何使用like操作符
%:代表任意0到多个字符
_:表示任意单个字符

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

//如何显示第三个字符为大写0的所有员工的姓名和工资
select ename,sal from emp where ename like '__O%';

//如何显示empno为123，345，800的雇员情况
select * from emp where empno in (7844,123,345,800);  //批量处理方式

//如何查询没有上级的雇员信息
select * from emp where mgr is null;

//查询工资高于500或岗位为manager的雇员，同时要满足他们的姓名首字母为大写J
select * from emp where (job='MANAGER' or sal>500) and ename like 'J%';

//如何按照工资的从低到高的顺序显示雇员信息
select * from emp order by sal asc; //desc

//按照部门号升序而雇员的工资降序排列
select * from emp order by deptno,sal desc;

//使用列的别名排序
select ename,sal*12 as "年薪" from emp order by "年薪" 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);

//显示所有员工的平均工资和工资总和
select avg(sal) as "平均工资",sum(sal) as "工资总和" from emp;

//计算共有多少员工
select count(*) from emp;

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

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

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

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

//
//多表查询 
//

笛卡尔集：多表查询的条件至少不能少于表的个数-1

//显示雇员名，雇员工资及所在部门的名字
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

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

//显示各个员工的姓名，工资，及其工资的级别
select e.ename,e.sal,s.grade from salgrade s,emp e where e.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.dname;

//自连接查询
在同一张表的连接查询

//显示某个员工的上级领导的姓名
select work.ename,boss.ename from emp work,emp boss where work.mgr=boss.empno and work.ename='FORD';

//
//子查询
//

//如何显示与smith同一个部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');

//如何查询和部门10的工作相同的雇员名字，岗位，工资，部门号
select ename,sal,job,deptno from emp where job in(select distinct job from emp where deptno=10);

//如何显示工资比部门30的所有员工工资高的员工姓名，工资，部门号
select * from emp where sal>all(select sal from emp where deptno=30);

//如何显示工资比部门30的任意一个员工的工资高的员工信息
select * from emp where sal>any(select sal from emp where deptno=30);

//如何查询与smith的部门和岗位完全相同的雇员信息
select * from emp where (deptno,job)=(select deptno,job from emp e where e.ename='SMITH');

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

//1、根据rownum分页
select e1.* ,rownum rn from (select * from emp) e1; //rownum给每1行分配的行号
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,sal from emp) e1 where rownum<=10) where rn>=6;

//2、根据分析函数分页

//3、根据rowid分页

//快速创建表，将查询数据存放该表
create table testTable (ename,sal) as select ename,sal from emp;

//
//合并查询
//

//两张表合并查询，使用union则可以自动去掉重复行
select * from emp where sal>2500 union select * from emp where job='MANAGER';

//union all 不会重复行，不会自动排序
select * from emp where sal>2500 union all select * from emp where job='MANAGER';

//intersect取交集的部分
select * from emp where sal>2500 intersect select * from emp where job='MANAGER';

//minus取差集
select * from emp where sal>2500 minus select * from emp where job='MANAGER';

||-------  创建新的oracle数据库  -------||

1、使用向导工具
2、使用Database Configuration Assisant //数据库配置助手