Oracle学习及工作笔记整理

Oracle由Oracle数据库和Oracle实例组成
Oracle数据库是存储数据的物理存在
Oracle实例=进程+进程所使用的共享内存池（SGA SYSTEM GLOBAL AREA）系统全局区
一个或多个实例可以访问一个数据库，一个实例只能访问一个数据库
连接数据库：connect scott/H123 ,connect sys/Hyx960411 as sysdba，显示当前用户show user
1.&的作用：select * from table where name=’&name’; 从外部输入代替&name
2.start 运行脚本 edit 编辑脚本 使用：start/edit d:\a.sql;
3.spool 将sqlplus屏幕的内容输入到指定文件 使用：spool d:\a.sql;…spool off;
4.linesize 设置显示行的宽度 默认80； 使用：show linesize; set linesize 90;
5.pagesize 每页显示的行数目 默认14； 使用同上
6.新建用户：create user hhh identified by m123； 密码英文开头
给用户修改密码 自己：password 用户名 别人:alter user 用户名 identified by M123；
删除用户（不能自己删自己）：drop user 用户名
如果要删除的用户已经创建了表，就用级联删除：drop user 用户名 cascade
7.授权： grant/revoke 角色/权限 to/from 用户名(with grant/admin option)
grant/revoke select on 表名 to/from 用户名(with grant/admin option)
角色（系统权限）：connect, dba, resource(在任何表空间建表)–admin
对象权限：select,insert,update,delete,all --grant
谁授权谁收回，顶级回收权限最低级也被回收
8.查询表的结构 desc 表名；
9.profile文件，指定用户最多尝试3次登陆，锁定时间2天
先创建profile文件（规则）:create profile 规则名称 limit
failed_login_attempts 3 password_lock_time 2;整数，天为单位
指定用户遵守规则：alter user 用户名 profile 规则名;
3次后锁定用户，解锁：alter user 用户名 account unlock;
强制修改密码(每隔10天修改，宽限期2天)：
create profile 规则名 limit password_life_time 10 password_grace_time 2;
指定用户遵守规则：alter user 用户名 profile 规则名;
口令历史（用户在修改密码时不允许使用以前用过的密码）：
create profile 规则名 limit password_life_time 10 password_grace_time 2 password_reuse_time 10;(口令10天后可重新启用)
删除profile文件，用户被释放:drop profile 规则名
10.number 既可以表示整数也可以表示小数
number(5,2)表示一个小数有5位有效数，2位小数
number(5)表示一个5位整数
blob 二进制数据 存放图片/声音（保密性高，普通存在文件夹下，数据库存路径）
11.往表里添加一个字段：alter table 表名 add (class number(2));
修改字段的长度或类型/名字（类型名字必须没有数据）：alter table 表名 modify (class number(2));
删除字段：alter table 表名 drop column 字段名；
修改字段：update 表名 set 字段=’’,字段=‘’ where id=‘1’
修改表名：rename 旧表名 to 新表名
12.往表里添加完整数据：insert into 表名 values(‘’，‘’)
添加部分数据：insert into 表名（字段名，字段名） values (’’,’’)
添加空值：insert into 表名（字段名，字段名） values (’’,null)
查询为空值的数据：where id is null
删除所有数据：delete from 表名-------恢复数据（回滚）:删除数据之前 创建保存点（保存点唯一）：savepoint 保存点名; 然后删除，在回滚到保存点:rollback to 保存点名（工作经常做保存点）
删除表所有数据：truncate table 表名，表结构在，不写日志，没办法找回，速度极快
13.Oracle默认日期格式“日-月-年”而且月份必须为2月，修改日期默认格式：
alter session set nls_date_format = ‘yyyy-mm-dd’;
修改后可以添加为’1905-05-06’临时生效，不永久
14.select count() from 表名 查询表中数据总量
select distinct 字段名 from 表名 消除重复行
年工资：select sal13+nvl(comm,0)13 “年工资” ename from emp;
nvl处理null值，如果comm值为null用0代替
查询1982.1.1后入职的人：select ename from emp where date>‘1-1月-1982’；
%0到多个字符，_单个字符
查询多个条件:select * from emp where id in (13,456,789);
关键字：like, asc(升序)desc(降序):order by desc, all 全部 any 任何一个
max, min, avg,（平均）sum(总和),
count:count(distinct dept)消除重复行 列的别名：sal12 as“年薪”,max(sal) as xs给表取别名的时候不能+as
查询最高工资的人的名字：select name,sal from emp where sal=(select max(sal) from emp)
15.group by 用于对查询结果分组统计 having 限制分组显示结果
顺序:group by having order by
16.例子：显示高于自己部门平均工资的员工信息：
select a2.name,a2.sal,a2.dept,a1.mysal from emp a2,(select dept, avg(sal) as “mysal” from emp group by dept) a1 where a2.dept=a1.dept and a2.sal>a1.mysal
from 中的子查询可当作一个视图（内嵌视图）
17.oracle 分页查询有三种：（1）使用rownum :select a1.* ,rownum rn from (select * from emp) a1
oracle给每一行分配的行号；rownum 每个select只能用一下,
（2）图1
18.用查询结果创建一个新表:
create table mytable (id,name,sal,job,dept)as select empno,ename,sal,job,dept from emp
19.合并查询，为了合并多个select语句的集合，可以使用集合操作符号
union:取两个结果集的并集且去掉重复行:select ename,sal,job from emp where sal>2500 union
select ename,sal,job from emp where job=‘manager’
union all:不会取消重复行，而且不会排序：select ename,sal,job from emp where sal>2500 union all
select ename,sal,job from emp where job=‘manager’
intersect：取两个结果集的交集：select ename,sal,job from emp where sal>2500 intersect
select ename,sal,job from emp where job=‘manager’
minus:取两个结果集的差集（所有属于A但不属于B的元素的集合）：哪个在前面哪个是A
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job=‘manager’
20.使用java操作数据库：import java.sql.*
JDBC与ODBC桥链接(主函数里)：try{1.加载驱动:Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:odbc:数据源名称”,“用户名”,“密码”);
3.从下面开始和2000一样：Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){System.ou.println(“字段”+rs.getString(字段在表中位置2));}
4.关闭打开的资源：rs.close();sm.close();ct.close();}catch(){}
使用JDBC链接:try{1.加载驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.得到链接:Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@链接数据库的ip地址：oracle端口号：数据库实例”,“用户名”,“密码”);
3.从下面开始和2000一样：Statement sm=ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){System.ou.println(“字段”+rs.getString(字段在表中位置2));}
4.关闭打开的资源：rs.close();sm.close();ct.close();}catch(){}
21.使用to_date添加日期（指定格式）：insert into emp valuers(to_date(‘1999-01-21’,‘yyyy-mm-dd’));
或者：insert into emp valuers(to_date(‘1999/01/21’,‘yyyy/mm/dd’));
22.数据迁移，从一个表转移多行数据：insert into kkk (myid,name,dept) select empno,ename,deptno from emp where deptno=10;
更新多个数据:update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=‘smith’) where ename=‘jim’;希望jim的工作工资补助与simth的一样
23.事务：保证数据一致性，由DML语句（增删改）组成，要么全部成功要么全部失败,当执行事务操作时，会在被作用的表上加锁，防止其他用户修改表的结构
提交事务（事务提交了无法退回到保存点）：commit;
只读事务：set transaction read only 设置只读不会取得最新数据变化
24.sql函数：（1）字符函数：lower（char）:将字符串转化为小写：select lower(字段名) from 表
uuper（char）:将字符串转化为大写：select upper(字段名) from 表
length(char):返回字符串长度：显示有5个字符的员工姓名：select * from emp where lenth(name)=5;
substr（char,m,n）:取字符串的字串:显示姓名的前三个字符：select substr(name,1,3) from emp;从第一个字符取三个字符（包括第一个）
例：显示姓名第一个字母大写：select upper(substr(name,1,1))||lower(substr(name,2,length(name)-1)) from emp;
replace:替换字符：select replace(name,“a”,“b”) from emp;将名字中a换成b
（2）数学函数：round(n,[m]):四舍五入:select round(字段,1) from emp;到小数点后1位,如果M为-1，就四舍五入到小数点前1位，如果省略M则保留整数
trunc(n,[m]):截取数字：select trunc(字段,1) from emp;到小数点后1位,如果M为-1，就截取到小数点前1位（个位为0），如果省略M则保留整数
floor(n):返回小于或等于n的最大整数
ceil(n)：返回大于或等于n的最小整数

                       mod(m,n)取模(取余数)：select mod(10,2) from dual(oracle测试表)   0
         (3)日期函数：sysdate:返回系统时间:select sysdate from dual;
                      add_months(d,n):查找已经入职8个月的人：select * from emp where sysdate>add_months(rzdate,8)
                                      显示入职天数：select trunc(sysdate-rzdate) "入职天数",name from emp
                      last_day(n):返回指定日期所在月份的最后一天：显示入职时间为当月倒数第三天的人
                                                    select rzdate,name from emp where last_day(rzdate)-2=rzdate;
         (4)转换函数：将数据类型转换为另一种：