复杂查询-单表查询：
查询部门中各个岗位的平均工资和最高工资。
select  avg(sal),max(sal),deptno,job from emp group by deptno,job;
group by 用于对查询的结果分组统计；
select  avg(sal),max(sal),deptno,job from emp group by deptno,job having avg(sal)>2000; having 子句用于限制分组显示结果;
select  avg(sal),max(sal),deptno,job from emp group by deptno,job having avg(sal)>2000 order by deptno desc;
order by 用于排序;
复杂查询-多表查询：
select ?,? from emp a1,dept a2 where a1.deptno=a2.deptno;
自连接(同一张表的关联查询)：
select ?,? from emp worker,emp boss where woker.?=boss.?;
子查询：
1.单行子查询：返回单行数据
2.多行子查询：返回多行数据select * from emp where job in(select());
all=嵌套select(max());
any=嵌套select(min());
3.多列子查询：
select ?,? from emp where (?,?)=(select ?,?);  注意对应关系
4.from子句中使用子查询：
select ?,? from emp a1,(select deptno,avg(sal) mysal from emp group by deptno) a2
where a1.deptno=a2.deptno and a1.sal&sal;
(a2叫做内嵌视图) 表指定别名不可加as，列可以加as；
5.用查询结果创建表：
create m1(?,?,?) as select ?,?,? from emp;
复杂查询-分页查询：
(select * from emp)
2.显示rownum（oracle分配的）
select a1. * ,rownum rn from (select * from emp) a1;
3.指定范围：6到10条记录
select a1. * ,rownum rn from (select * from emp) a1 where rownum<=10; 前10条记录
select * from (select a1. * ,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6 ;
4.几个查询变化：
a.指定查询列，(只需要修改最里层的子查询)
select * from (select a1. * ,rownum rn from (select ?,? from emp) a1 where rownum<=10) where rn>=6 ;
b.如何排序,(只需要修改最里层的子查询)
select * from (select a1. * ,rownum rn from (select ?,? from emp order by ?) a1 where rownum<=10) where rn>=6 ;
复杂查询- 合并查询：
unoin:
并集且去除重复的
union all：
并集且不去除重复的，也不排序
intersect：
取交集
minus:
取差集
sql函数的使用：
lower(char),upper(char),length(char),substr(char,m,n)从m开始取n个字符。
select upper(substr(ename,1,1)) from emp;(首字母大写)
select lower(substr(ename,2,length(ename)-1)) from emp;(除首字母大写后面字母小写) replace(char,m,n) 选择字段名将m替换成n
round(n,[m]) 四舍五入m为小数点后面的的m位orcl 中trunc函数的使用方法
trunc(n,[m]) 截取数字m为小数点后面的的m位
mod(m,n)取模（余数）
floor(n)返回小于或者等于n的最大整数
ceil(n)返回大于或者等于n的最小整数
例：显示一个月（30天）的情况下所有员工的日薪，忽略余数：
select trunc(sal/30),ename from emp; 或者select floor(sal/30),ename from emp;
显示员工的入职天数：
Select trunc(sysdate-hiredate) "入职天数",ename from emp;
显示当月倒数第三天入职的员工：
last_day(d)：返回指定日期所在月份的最后一天
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
转换函数：
to_char(char, data)：将字符串转换成date类型的数据
to_date('1988-12-12','yyyy-mm-dd') 将日期格式强制转换
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
显示1980年入职的员工：
select * from emp where to_char(hiredate,'yyyy')=1980;
数据库的逻辑备份与恢复：
导出：表，方案，数据库(DOS下)（多个方案的导出就是导出数据库）
导出表
exp userid=scott/m123@orcl table=(emp,dept) file=d:\aa.dmp
导出表结构：
exp userid=scott/m123@orcl table=(emp,dept) file=d:\aa.dmp rows=n
快速导出：
exp userid=scott/m123@orcl table=(emp,dept) file=d:\aa.dmp direct=y
导出方案：
exp scott/m123@orcl owner=scott file=d:\aa.dmp
导出其他用户方案：需要dba权限或者exp_full_database权限
exp system/manager@orcl owner=(scott) file=d:\system.dmp
导出数据库：(增量全部)
exp system/manager@orcl  full=y inctype=complete file=d:\aa.dmp
导入：表，方案，数据库(DOS下)（多个方案的导出就是导出数据）
导入表：
imp scott/m123@orcl table=(emp) file=:d\aa.dmp
数据字典（存放静态信息）：
视图集合，sys用户只能查询
数据字典=基表+动态性能视图（记载经常变化）
user（当前用户自己的）_xxx（对象）,
all_xxx当前用户可以访问的所有
dba_xxx（dba权限）显示所有方案的数据库表三种类型
用户名：
权限（显示用户所拥有的系统权限dba_sys_privs，对象权限dba_tab_privs：）
1.查询一个角的所有权限:
a.系统权限：select * from dba_sys_privs where grantee='CONNECT';
b.对象权限：select * from dba_tab_privs where grantee='CONNECT';
select * from dba_roles;
3.用户包含的所有角：
select * from dba_role_privs where grantee='SCOTT';
表空间：
数据库逻辑组成部分
从物理上讲，数据库数据存放在数据文件中；从逻辑上讲，则存放在表空间上。表空间由一个或多个数据文件组成：oracle逻辑结构：表空间>段>区>块。
建立表空间：create tablespace  由dba执行
建立了data01表空间并且建立了data01.dbf数据文件，区的大小为128k：
create tablespace data01 datafile'd:\data01.dbf' size 20m uniform size 128k;
在表空间中建表：create table xx(?,?,?) tablespace data01;
alter tablespace data01 offline;(脱机)维护时数据库时
alter tablespace data01 online;(联机)
alter tablespace data01 read only;(只读表空间)只能select
alter tablespace data01 read write;(可读可写)
drop tablespace data01 including contents and datafiles;(彻底删除)
扩展，设置文件自动增长：
alter tablespace data01 'd:\data01.dbf' autoextend on next 10m maxsize 500m;
例：移动数据文件：(所在磁盘毁坏)
alter tablespace a01 offline；使表空间脱机
host move d:\test\a01.dbf c:\test\a01.dbf；移动数据文件(物理上)
alter tablespace a01 rename datafile 'd:\test\a01.dbf' to 'c:\test\a01.dbf';（逻辑上）alter tablespace a01 online；使表空间联机
数据的完整性：数据遵从一定的商业和逻辑规则
约束，触发器，（过程，函数）三种方法实现
约束：not null, unique, primary key, foreign key，check.
alter table emp modify deptno not null;
alter table emp add constraint aa unique(cardid)；将约束aa加到cardid字段上。alter table emp add constraint bb check(address in (?,?))
alter table emp drop constraint aa;
alter table emp drop primary key cascade；主键约束
列级定义：定义列的时候同时定义约束
表级定义：在列定义结束后再定义约束
索引：
单列索引：create index aa on emp(deptno);
复合索引：create index aa on emp(deptno,ename);
缺点：占用系统空间资源大（表的1.2倍）；降低系统性能，花费大量时间。
角：
预定义（connect ,resource ,dba），自定义角(dba或者create role的系统权限) create role myrole1 not identified;(不验证)
角授权：system
grant create session to 角with admin option;
grant 角to 用户with admin option;
删除角用户还能登陆吗？（否）
显示角信息：select * from dba_roles;
事务：
DML语句insert，update，delete。
锁：当执行事务操作时（dml语句），oracle会在被作用的表上加锁。commit；提交事务(之前的保存点无效)
savepoint a；创建保存点
rollback to a;
在java中使用事务:ct.setAutoCommit(false);
PLSQL块
ctmit();
只读事务(select)：
set transaction aa read only；设置只读事务
取得特定点时间点的数据信息。（订票系统）
plsql编程:
procedural language /sql
过程函数触发器在oracle中由plsql编写，非常强大的数据库过程语言，可以由java调用
缺点：移植性不好；
存储过程：
create or replace procedure sp_pro1(?,?) is
【可以定义变量】
begin--执行部分
insert into aa values(?,?,?);
end;
/
如何查看错误信息：show error;
如何调用：1.exec sp_pro1(参数值1...); 2.call sp_pro1(参数值1...);
过程：（多个返回值）
修改员工工资
create procedure sp_pro3(spname varchar2,newsal number) is
begin
---执行
update emp set sal=newsal where ename=spname;
end;
/
exec sp_pro3('SCOTT',4678);
函数：（返回值单一）
create function sp_fun1(spname varchar2) return
number yearsal is number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spname;
return yearsal;
end;
/
调用：var abc number;
call sp_fun2('SCOTT') into :abc;
块(block)是pl/sql基本程序单元
块的范例包含（定义，执行，异常处理）
declare --定义变量

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。