Oracle学习 1

博客内容主要来源：
视频：玩转oracle实战教程
书籍：Oracle实用教程+深入浅出Oracle
自己整理的一些笔记

数据操作（DML）
insert、update、delete
数据定义（DDL） 不允许rollback
create、alter、drop、rename、truncate
事务控制
commit、rollback、savepoint
安全（DCL)
grant、revoke

sql*plus常用命令

1 连接命令 (切换用户)
用法：conn[ect] 用户名/密码 当用特权用户身份连接时，必须带上as sysdba或者as sysoper
如：conn system/manager
注意：在连接命令中 conn 用户名/密码@网络服务名[as sysdba] 网络服务名指的是所要连接的数据库实例名
断开连接：disc[onnect]
2 修改密码
用法：password 需要用sys/system登录
3 显示当前用户
用法：show user 显示当前用户名
4 退出
用法：exit
5 文件操作命令
(1) start和@
说明：运行sql脚本
如：sql>@ d:\a.sql 或者start d:\a.sql
(2) edit
说明：编辑sql脚本
如：sql>edit d:\a.sql
(3) spool
说明：可以将sql*plus屏幕上的内容输出到制定文件中去
如：sql>spool d:\b.sql (开启了)
进行一些操作后
最后 输入sql>spool off (关闭了)
(4) &
说明：可以替代变量，而该变量在执行时，需要用户输入
如：sql> select * from emp where job=’&job’ 其实没什么用，还不如直接写job=’具体要查询的’
6 显示和设置环境变量
概述：可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
(1) linesize
说明：设置显示行的宽度，默认是80个字符
如：sql>show linesize
sql>set linesize 90
(2) pagesize
说明：设置每页显示的行数目，默认是14
用法与linesize一样

Oracle用户的管理

1 创建用户
概述：在oracle中要创建一个新的用户使用creat user语句，一般具有dba的权限才能使用
如:sql>creat user xiaoming identified by m123;
2 给用户修改密码
说明：如果给自己修改密码可以直接使用
sql>password 用户名(不写用户名都可以)
说明：如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限
sql>alter user 用户名 identified by 新密码
3 删除用户
说明：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的系统权限
如：drop user 用户名【cascade】
如果要删除的用户，已经创建了表，那么就需要带参数cascade，级联删除
4 新创建的用户是没有任何权限的，甚至连登录数据库的权限都没有
需要为其制定相应的权限，给一个用户赋予权限使用命令grant，回收权限命令revoke
如：sql>grant connect to xiaoming;
(1) 权限分为2种
系统权限：用户对数据库的相关权限，如建库，建表，建索引，建存储过程，登录数据库，修改密码
对象权限：用户对其它用户的数据对象(表、函数、包、视图、触发器、存储过程)操作的权限，如查询、更新、插入、删除、all某些表
如：sql>grant select on scott.emp to xiaoming
如：sql>revoke select on scott.emp from xiaoming
(2) 角色
事先定义一些角色，包含了一些权限，如connect角色包含creat session权限等7种权限,DBA角色权限最高啊，resource权限在表空间建表的权限
角色也分两种
预定义角色：
自定义角色：
(3) 权限的维护
用户收到新权限后可以赋予给其它用户这个权限
如果是对象权限就在后面加上with grant option
如：sql>grant select on scott.emp to xiaoming with grant option
xiaoming就可以继续把select权限传递给其它用户
如果是系统权限就在后面加上with admin option
如：sql>grant connect to xiaoming with admin option
xiaoming就可以继续把connect传递给其它用户

重点：
对象权限会级联删除
系统权限不会级联删除

5 使用profile管理用户口令
说明：profile是口令限制，资源限制的命令集合，oracle会自动建立名称为default的profile
(1) 账户锁定
说明：指定用户登录时最多可以输入密码的次数，用户锁定的时间
如：sql>create profile lock_account(规则名称，自己随便定义的) limit failed_login_attempts 3 password_lock_time 2;(锁定时间为2天)
sql>alter user xiaoming profile lock_account
(2) 账户解锁
如：sql>alter user xiaoming account unlock；
(3) 终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成
如：每隔10天修改自家的登录密码，宽限期为2天
sql>create profile myprofile limit password_life_time 10 password_grace_time 2;
sql>alter user xiaoming profile myprofile
(4) 口令历史
如果希望用户在修改密码时，不能使用以前使用过的密码，可使用口令历史
sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time
(指定口令可重用10天后即可以重用) 10;
sql>alter user xiaoming profile password_life_time
(5) 删除profile
sql>drop profile password_history 【cascade】

oracle表的管理

表名和列命名规则

必须以字母开头
长度不能超过30字符
(1) 字符型
char 定长 最长2000字符
如：char(10) ‘小韩’前4个字符放’小韩’，后添加6个空格补全
查询速度极快，查询时直接所有的位数一起匹配，身份证等定长的适合用char
varchar2 变长 最大4000字符
如：varchar2(10) ‘小韩’ 就只分配4个字符
??节省空间，比较时要一个个比较，先比第一个再比第二个
clob(character large object)
字符型大对象 最大4G
(2) 数字型
number 10的38次方取负到10的38次方
如：number(5,2) 表示一个数总共有5位有效数，2位小数
范围：-999.99—999.99
number(5) 表示一个5位整数 范围-99999到99999
(3) 日期类型
date 包含年月日和时分秒
timestamp 时间戳 精度更高
(4) 图片
blob 二进制数据 可以存放图片、声音

创建表

学生表
sql>create table student(
xh number(4), 学号
xm varchar2(20), 姓名
sex char(2),
birthday date,
sal number(7,2)
);
班级表
sql>create table class(
classid number(2),
cname varchar2(20)
);

修改表

(1) 添加一个字段
sql>alter table student add (classid number(2));
(2) 修改字段长度
sql>alter table student modify (classid number(5));
(3) 修改字段的类型 (空表时才能这样改)
sql>alter table student modify (classid char(5));
(4) 删除一个字段
sql>alter table student drop column sal;
(5) 修改表的名字
sql>rename student to stu;
(6) 删除表
sql>drop table student;
(7) 添加数据
1)所有字段都插入
insert into student values(‘A001’,’张三’，’男’，’06-5月-05’，10)；
oracle中默认的日期格式为’DD-MON-YY’
1999年6月9号的默认格式为’09-6月-99’
修改日期格式的命令如下：
alter session set nls_date_format=’yyyy-mm-dd’;
修改后可使用我们熟悉的日期格式：
insert into student values(‘A001’,’张三’，’男’，’1905-05-06’，10)；
2)插入部分字段
insert into student (xh,xm,sex) values (‘A002’,’张四’，’男’)
但插入部分字段时很容易出错，因为有些字段可能要求不能为空
3)插入空值
insert into student （xh,xm,sex,birthday
values (‘A004’,’MARTIN’,’男’,null);
查询为空的数据时，要用 is null 来查询
4)修改一个字段
sql>update student set sex=’女’ where xh=’A01’;
5)修改多个字段
sql>update student set sex=’女’ , birthday=’ 1980-04-01’ where xh=’A01’;
(7) 删除数据
delete from student；
删除所有记录，但是表结构还在，写日志，可以恢复的(rollback)，速度慢
drop table student;
删除表的结构和数据
truncate table student;
删除表的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快

查询表

(1) 简单的查询语句
查看表结构
sql>desc dept;
查询所有列
sql>select * from dept;
查询指定列
sql>select cname,sal,job from emp;
如何取消重复行
sql>select distinct deptno,job from emp;
注意：oracle对大小写是区分的(主要指引号里的)，查询时要注意
使用算数表达式
sql>select sal*12 “年薪”,ename from emp;
使用列的别名
sql>select cname “姓名” from emp;
如何处理null值
注意：只要某列中含有null值，那么对其进行运算得到的结果都是null值
如计算年薪时，可能要加上奖金，但是有些人可能没奖金为null，那么他的年薪也将为null
使用nvl函数
select sal*13+nvl(comm,0)*13 “年薪”,ename from emp;如果为空值 那么用0代替
如何连接字符串(||)
select ename || ‘is a’|| job from emp;
如何查找1982年1月1号后入职的员工
select ename,hiredate from emp where hiredate>’1-1月-1982’;注意日期格式
如何查找工资在2000到3000的员工
select ename from emp where sal>2000 and sal<3000;
如何使用like操作符
%:表示0到多个任意字符
_:表示任意单个字符
显示首字母为S的员工信息：select ename,sal from emp where ename like ‘S%’;
如何在where中使用in
显示显示empno为123,345,800..的雇员情况：select * from emp where empno in(123,345,800);
如何使用逻辑运算符
sql>select ename from emp where (sal>500 or job=’MANAGER’) and ename like ‘J%’;
如何使用order by 字句
sql>select * from emp order by sal;默认是升序（asc） desc 是从高到低
sql>select ename,sal*12 “年薪” from emp order by “年薪”;注意年薪要有引号
如：按照部门号升序而雇员的工资降序排列
sql>select * from emp order by deptno,sal desc;

(2) 复杂的查询语句
1）数据分组函数—max,min,avg,sum,count
如：显示所有员工中最高工资和最低工资(显示姓名)
sql>select max(sal) “最高工资”,min(sal) “最低工资” from emp;
sql>select ename,sal from emp where sal=(select max(sal) from emp)
注意不能使用select ename,sal from emp where sal=max(sal) 这样是错误的
2）group by 和having字句
group by用于对查询的结果分组统计
having子句用于限制分组显示结果
如：显示每个部门的平均工资和最高工资
sql>select max(sal),avg(sal),deptno from emp group by deptno; 注意：分组的字段一定要出现在查询里，如此中的deptno
如：显示每个部门的每种岗位的平均工资和最高工资
sql>select max(sal),avg(sal),deptno,job from emp group by deptno,job;
如：显示平均工资低于2000的部门号和它的平均工资
sql>select avg(sal),deptno from emp group by deptno having avg(sal)<2000;
总结：分组函数只能出现在选择列表、having、order by 子句中 决不能出现在where中
如果在select语句中同时包含group by，having，order by 那么他们的顺序是group by having order by
在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则会出错
(3) 复杂的多表查询语句
多表查询是基于两个和两个以上的表或是视图的查询
如：显示雇员名，雇员工资及所在部门的名字
sql>select emp.cname,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;
假如最后不加where任何条件，那么就会形成笛卡尔集，是表1行数*表2行数
为了避免形成笛卡尔集，多表查询的条件是 至少不能少于 表的个数-1
如：显示部门号为10的部门名,员工名和工资
sql>select a1.dname,a2.cname,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;
如：显示各个员工的姓名、工资及其工资的级别
sql>select a1.cname,a1.sal,and a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
如：显示各个员工的姓名、工资及其所在部门的名字，并按部门排序
sql>select a1.cname,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
自连接
自连接是指在同一张表的连接查询
如：显示某个员工的上级领导的姓名
sql>select worker.ename,boss.ename from emp worker,emp boss where worker.MGR=boss.empno and worker.ename=’FORD’;

好像也可以sql>select ename from emp where MGR=(select MGR from emp where ename=’FORD’);
(4) 子查询
子查询是嵌套在其它sql语句中的select语句，也叫嵌套查询
1)单行子查询
单行子查询是指只返回一行数据的子查询语句
如：如何显示与SMITH同一部门的所有员工
sql>select * from emp where deptno=(select deptno from emp where cname=’SMITH’);
2)多行子查询
多行子查询指返回多行数据的子查询
如：查询和部门号为10的工作相同的雇员的名字，岗位，工资，部门号
sql>select * from emp where job in (select distinct job from emp where deptno=10);

小细节：数据库在执行sql时是从右到左开始执行的，如where ename=’a’ and job=’b’,它会先找job等于b的数据，所以我们最好能把一下子筛选出数据的条件放在右边，这样能加快查询效率
在多行子查询中使用all、any操作符
如：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
sql>select cname,sal,deptno from emp where sal> all (select sal from emp where deptno=30)
也可以sql>select cname,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30)
如：显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号
sql>select cname,sal,deptno from emp where sal> any (select sal from emp where deptno=30)
也可以sql>select cname,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30)
3)多列子查询
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的
而多列子查询则是指查询返回多个列数据的子查询语句
如：显示与smith的部门和岗位完全相同的所有雇员
sql>select * from emp where (deptno,job)=(select deptno,job from emp where ename=’SMITH’);
4)在from子句中使用子查询
如：显示高于自己部门平均工资的员工的信息
select deptno,avg(sal) mysal from emp group by deptno 把这个查询结果看成一个子表
sql>select a2.ename,a2.sal,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
说明：在from子句中使用子查询时，该子查询会被作为一个视图来对待，因此叫内嵌视图，并且要给子查询指定别名 而给表取别名时不要加as
5)分页查询
//oracle分页分以下几步
1.rownum分页
select a1.,rownum rn from (select from emp) a1; rownum 是oracle分配的行号
2.挑选6到10行数据
select a1.,rownum rn from (select from emp) a1 where rownum<=10; 注意此时不能and rownum>=6,这样是不行的，oracle规定rownum只能用一次
再做一次子查询 select * from (select a1.,rownum rn from (select from emp) a1 where rownum<=10)a2 where rn>=6;
3.注意几个查询的变化
指定查询列，只需修改最里层的子查询
如何排序，只需修改最里层的子查询
6）用查询结果创建新表
这个命令是一个快捷的建表方法
create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
7）合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符
union：取得两个结果集的并集，自动去除结果集中的重复项
select ename,sal,job from emp where sal>2500 union
selece ename,sal,job from emp where job=’manager’;
union all：取得两个结果集的并集，包含结果集中的重复项，而且不会排序
intersect:使用该操作符用于取得两个结果集的交集
minus:使用该操作符用于取得两个结果集的差集，它只会显示存在第一个集合中，而不存在第二个集合中的数据
用第一个集合减去第二个集合

这种集合操作其实比and、or等操作符快很多

(5) 创建新的数据库
两种方法：
通过oracle提供的向导工具 DATABASE Configuration Assistant(dbca 数据库配置助手)
我们可以用手工步骤直接创建

Java操作oracle

1 Java连接oracle
举例说明：写一个showemp.java 分页显示emp表的用户信息

//演示如何使用jdbc_odbc桥连接方式 不能远程连接，只能本地连接
import java.sql.*;
try{
//1.加载驱动
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
//2.得到链接
Connection ct=DriverManager.getConnection(“jdbc:odbc:testsp(管理工具中的数据源管理器里配置的名字)”,”scott”,”m123”);
//3
Statement sm==ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){
//用户名
System.out.println(“用户名”+rs.getString(2)); //写2代表第二个字段
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}

//演示如何使用jdbc连接方式 可以远程连接 （事先要配置oracle驱动包，要自己下载、配置到Java里的）
import java.sql.*;
try{
//1.加载驱动
Class.forName(“oracle.jdbc.driver.OracleDriver”);
//2.得到链接
Connection ct=DriverManager.getConnection(“jdbc:oracle:thin:@127.0.0.1:1521:myoral(IP地址+端口号+数据库实例)”,”scott”,”m123”);
//3
Statement sm==ct.createStatement();
ResultSet rs=sm.executeQuery(“select * from emp”);
while(rs.next()){
//用户名
System.out.println(“用户名”+rs.getString(2)); //写2代表第二个字段
}
//关闭打开的资源
rs.close();
sm.close();
ct.close();
}