公告

玩转Oracle

解锁步骤：

=============工具===============

========================网页登录(此服务一般设置为关闭状态)===================================

====================sqlplus常用命令===================================

show user 当前用户名是什么

disc[onnect]

exit

该命令用户断开和oracle连接，同时退出sqlplus窗口

password[ord]

　　例如：password 用户名

该命令用户修改用户密码,如何给自己修改密码，可以不带用户名，给别人修改密码(用户为：System或者是sys用户才可以修改别人的密码)，则需要带上用户名

交互式命令：

说明：可以代替变量，而改变量在执行时，需要用户输入。

例如：sql> select * from emp where job = '&job';

edit

说明：改命令可以编辑指定的SQL脚本

例如：sql> edit d:\a.sql

spool

说明：该命令可以将sql*plus屏幕上的内容输出到指定文件中

案例：sql> spool d:\b.sql 并输入 sql>spool off(关闭)

=====================显示和设置环境变量=====================

概述：可以用来控制输入的各种格式。

1.linesize

　　说明：设置显示行的宽度，默认为80个字符

　　sql> show linesize

　　sql>set inesize 90

2. pagesize

　　sql> show pagesize

　　sql>set pagesize 90

　　说明：设置每页显示的行数目，默认为14，用法和linesize一样，至于其他环境参数的使用也是大同小异

=======================Oracle用户管理=================================

创建用户(简单版)

概述：在Oracle中创建一个新用户使用 create user 语句，一般具有dba(数据库管理员)的权限才能使用

基本语法：create user 用户名 identified by 密码 [Oracle密码不能以数字开头]

给用户修改密码

　　概述：如果给自己修改密码可以直接使用：

　　sql>password 用户名

　　如果给别人修改密码则需要具有dba的权限，或者是 alter user的系统权限，也可以使用 password用户名

　　sql> alter user 用户名 identified by 新密码

==============================Oracle表管理，Oracle数据类型详解1========================================================

基本概念---数据库服务器、数据库和表的关系

基本概念：数据在数据库中的存储方式：

表的管理----创建表(基本语句)

创建表基本语法：

　create table u (
       id number,
       name varchar2(32),
       password varchar2(32),
       birthday date
);

工具创建表：

表管理----Oracle常用的数据类型：

分类

数据类型

说明

文本、二进制类型

char(size)\char(20)

varchar2(size)\varchar2(20)

nchar(n)

nvarchar2(n)e

clob(character large object)

blob(binary large object)

定长最大2000字符

变长最大4000字符

Unicode数据类型，定长，最大2000字符

Unicode数据类型，变长，最大4000字符

字符型大对象，最大8tb

二进制数据可以存放图片\声音 8tb

数值类型

number(p,s)

p为整数位，s为小数位，范围：1<=p <=38,-84<= s <=127

保存数据范围：-1.0e-130<=number value <1.0e+126 保存在机器内部的范围:1~22bytes

日期时间

date

timestamp(n)

包含年月日，时分秒。默认格式：DD-MON-YYYY。从公元前4712年1月1日到公元4712年12月31日的所有合法日期

n的取值为0-9 表示指定的timestamp中秒的小数位数，N为可选，如果N为0，timestamp与date等价[不推荐]

number 可以理解成是一个可变的数值类型，比如 number(12) 你放入一个小整数，它占用的字节数就少，你放一个大整数，它占用的字节数就多，很好！！！

========================================Oracle的crud操作=============================================================

创建--学生表：

create table students(
   id number,
   name varchar2(64),
   sex char(2),
   brithday date,
   fellowship number(10,2)，
   resume clob          
);

创建班级表：包含班级编号，班级名称：

create table class_(
  id number,
  name varchar2(32)
);

修改表：

使用 alter table 语句添加，修改，或删除列的语法。

alter table tableName add ( columnname新列名 datatype ); 添加字段

alter table table modify (columnname列名 datatype); 修改字段

alter table table drop column (column列名 ) 删除字段

修改表的名称：rename oldtableName旧表名 to 新表名

添加数据：

使用insert语句向表中插入数据。

insert into table [ (column [,column ...]) ]

values ( value [ ,value... ] );

1.插入的数据应与字段的数据类型相同。

2.数据的大小应在列的规定范围内，例如：不能将长度为80的字符串加入到长度为40的列中。

3.在values中列的数据位置必须与被加入的列的排列位置相对应。

4.字符和日期类型数据应包含在单引号中。

5.插入空值，不指定或 insert into table value(null);

修改数据：

使用update 语句修改表中的数据。

update tablName

set column1 = 值，[ column1 = 值\表达式 ]

where

is null ：判断字段是不是 null

1.update 语法可以用新值更新原来的表中的列的值

2.set子句指示要修改那些列和要赋值那些值。

3.where指定应该更新那些行，如果没有where，则更新所以的行(特别小心)

删除数据：

使用delete语句删除表中的数据。

delete from tableName

where ....

1.如果不使用where字句，将删除表中所有的数据

2.delete语句不能删除某一列的值，可以使用update语句

3.使用delete语句仅删除记录，不删除表本身。要删除表本身，使用 drop table语句。

4.同 insert 和update 一样，从一个表中删除记录将引起其他表的参照完整性，在修改数据库时，要小心。

删除的几种方法比较

delete from tableName;

删除所有的记录，表结构还在，写日志，可以恢复的，速度慢。

删除表数据之前一定要有一个保存点：savepoint aa

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

delete from students where xh = 'A001'; 删除一条记录；

truncate table tableName; 删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，删除数据速度快，但是不能roollback;

========================================= Oracle 基本查询 =============================================

特别注意：Oracle 的查询SQL不区分大小写，但是数据内容区分大小写

基本 select 语句

select [ distince ] * | { column1 ,column2, column3... } from table [ where {条件} ];

select 指定查询那些列的数据。

column 指定列名{必须指定的属性}

* 号代表查询所有的列

from 指定查询那张表

where 代表条件

distinct 可选，指显的结果时，是否剔除重复数据。

=======================================

1.如何取消重复的行( 什么才是重复行？指的是返回的数据完全一样 )

========================================================

查看表结构

sql > desc 表名;

========================== Oracle函数 =====================

nvl(comm,0)：用户处理数据为null的问题。如果comm字段为null，则这个字段就取0，如果comm这个字段不为null，则取本身的值。

---------------------------------------------------------------------------------------------------------------------------------------

to_char

你可以使用select ename,hierdate,sal from emp where deptno = 10;

显示信息，可是，在某些情况下，这个并不能满足你的需求。

？日期是否可以显示：时/分/秒

?薪水是否可以显示指定的货币符号

yy	两位数字的年份，2004->04
yyyy	四位数字的年份，2004年
mm	两位数字的月份,8月 -->08
dd	2位数字的天，30号-->30
hh24	8点 --->20
hh12	8点--->08
mi、ss	显示分钟\秒
day

9	显示数字，并忽略前面的0
0	显示数字，入位数不足，则用0补齐
.	在指定的位置显示小数点
,	在指定的位置显示逗号
$	在数字前加美元
L	在数字前加本地货币符号
C	在数字前加国际货币符号
G	在指定的位置显示组分隔符
D	在指定位置显示小数点符号(.)
select ename,to_char(sal,"L99G999D99") from emp;

查询1980年入职的员工：

select * from emp whre to_char(hiredate,'yyyy') = '1980';

查询4月份入职的员工：

select *from emp where to_char(hiredate,'mm') = '4';

如何显示工资在2000 到 2500 的员工情况

方法1：select * from emp where sal >= 2000 and sal <= 2500;

方法2：select * from emp sal between 2000 and 2500; ( between ... and ... 是闭区间，包含的意思)

|| 的使用：在查询的时候，如果希望把多列拼接起来，作为一列返回，可以使用 ||

------------------------------------------------------------------------------------------------------

使用system用户查看 scott用户的数据表：

--------------------------------------------------------

我们希望删除用户，同时保留该用户对象的，如何处理

1.锁定该用户。

alter user scott(scott为用户名) account lock; //锁定 scott 用户，这时候该用户不能登录了，但是System用户可以使用scott的数据表

------------------------------------------------------------------------

解锁 scott 用户

alert user scott(scott为用户名) account unlock;

----------------------------------------------------------------------------------

where 子句

like 操作符：

　　%：表示任意 0到多个字符。 _：表示任意单个字符

例如：

如何显示首字符为S的员工姓名和工资

select name,sal from emp where name like 'S%';

如何显示第三个字符为大写 O 的所有员工的姓名和工资

select name,sal from emp where name like '_ _O%'

where 条件中使用 in

如何显示empno 为 123,345,800...的员工情况

方法1：select * from emp where empno = 123 or empno = 345 or empno = 800;

方法2：select * from emp where empno in(123,345,800);

is null 的用法：

如何显示没有上级的雇员的情况

select * from emp where mgr is null;

========================================

使用逻辑操作符号

查询工资高于500或是岗位为 NANAGER的雇员，同时还满足他们的姓名首字母大写的J

select * from emp where (sal > 500 or job = 'NANAGER' ) and ( ename like ‘J%’);

=================================================================================================

order by 子句(对结果进行排序，前提是结果已经产生 )

如何按照工资的从低到高的顺序显示雇员的信息。

select * from emp order by sal asc;

按照部门号升序而雇员的入职时间降序排列

select * from emp order by deptno asc, HIREDATE desc;

使用列的别名排序

select ename,sal*13 +nvl(comm,0)*13 as '年薪' from emp order by '年薪' desc;

order by 后面也支持运算函数

============================================ 分页查询 =========================================

rownum：字段为任何表都存在的隐藏字段，任何表都有这个字段，作用是：隐藏的排序字段

select t2.* from ( select t1.*, rownum rn from ( select *from emp ) t1 where rownum <=6 ) t2 where rn >=4;

说明：上面的SQL是Oracle数据库效率比较快的查询方法；在百万级别都可以及时响应

Oracle 使用了三层过滤机制：

第一层：select * from emp;

第二层：select t1.*, rownum rn from ( select *from emp ) t1 where rownum <=6

第三层：select t2.* from ( select t1.*, rownum rn from ( select *from emp ) t1 where rownum <=6 ) t2 where rn >=4;

注意：

6：代表取到第几条

4：代表从第几条开始取

按照入职时间的先后顺序，查询从第7到第10个人是谁？

select t2.* from ( select t1.*, rownum rn from ( select *from emp order by hiredate ) t1 where rownum <=10 ) t2 where rn >=7;

============================================ 复杂查询多表查询 ==================================

聚合函数：max，min，avg，sum，count

如何显示所有员工中高工资和最低工资

select ename,max(sal),min(sal) from emp

查询该公司，最大年工资

select ename max(sal*12+nvl(comm,0)*13 ) from emp

=================================================================

显示所有员工的平均工资和工资总和

select avg(sal),sum(sal) from emp;

注意：avg()函数不会计算为null的列，所以数据中有null的时候求平均值应该为：

select sum(comm) / count(*) from emp;

======================================================

统计有多少员工

select count(*) from emp;

细节：count(*) 可以对一个字段进行统计比如：count(字段名); 注意：count(字段名)函数不会计算字段包含null的数据

扩展：请显示工资最高的员工的名字，工作岗位

思路：where类型的子查询

select ename, job from emp where sal = (select max(sal) from emp);

where子查询的原理：

========================================================================

显示工资高于平均工资的员工信息

思路：使用where子查询

select * from emp where sal>( select avg(sal) from emp )

===================================================

group by 和 having子句

group by用户对查询结果分组统计。

having子句用户限制分组显示结果。

如何显示每个部门的平均工资和最高工资

select avg(sal),max(sal) deptno from emp group by depno;

================================================

显示每个部门的每中岗位的平均子和最低工资

select avg(sal),max(sal) , deptno ,job from emp group by deptno,job order by deptno;

==============================================================================================================

显示部门平均工资低于 2000的部门号和它的平均工资

思路：1.查询出各个部门的平均工资

select avg(sal), deptno from emp group by deptno;

思路：2.挑选出部门平均工资低于2000

select avg(sal) , deptno from emp group by deptno having avg(sal) < 2000

============================== 多表查询 ===============================================================

多表查询的原理:

====================================================================================================================================================

==============================================================================================================================

多表查询的时候，不添加条件，则出现笛卡尔集，所以多表查询的条件，至少不能少于表的个数-1(例如：两个表用一个条件)

显示雇员名，雇员工资以及所在部门的名字。

select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno;(一个判断条件)

如何显示部门号为10的部门名，员工名和工资

select dept.ename,emp.ename,sal from emp,dept where dept.deptno =10

显示各个员工的姓名，工资，以及工资的级别

select emp.name,emp.sal,salgrade.grade from emp,salgrade between salgrade.losal and salgrade.hisal

显示雇员名，雇员工资，以及所在的部门的名字，并按部门排序。

select emp.ename,emp.sal,dept.dname from emp,dept where dept.deptno = emp.deptno group by dept.dname;

自连接(自连接指的是在同一个表的连接查询)：

显示"FORD"的上级

select mar from emp where ename = 'FODR';

显示上级的信息：

select * from emp where empno = ( select mar from emp where ename = 'FODR' )

===============================================================================================

显示各个员工的姓名和他的上级领导姓名

思路：把 emp 表看做两张表

select worker.ename, boss.ename from emp worker, emp boss where worker.mgr = boss.empno;

=====================================================================================

问题：KING 没有显示，因为KING没有上级，如果我们希望把没有上级的人也显示出来，则需要使用“外链接”

=============================================== 子查询 ====================================================================

什么是子查询：子查询就是指嵌入在其中的其他SQL语句中的select语句。也叫嵌套查询。

单行子查询：单行子查询是指只返回一行数据的子查询语句。

思考：如何显示与SMITH同一部门的所有员工？

select deptno from emp where ename = 'SMITH';

select * from emp where deptno = ( select deptno from emp where ename = 'SMITH' );

多行子查询：多行子查询指返回多行数据的子查询。

思考：如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select distinct job from emp where deptno = 10; // 10号部门有哪些岗位

select ename,job,sal,deption from emp where job in ( select distinct job from emp where deptno = 10 );

在多行子查询中使用 all 操作符

思考：如何显示工资比30号部门的所有员工的工资高的员工姓名、工资、部门号

select ename,sal,deptno from emp where sal > all ( select sal from emp where deptno = 30);

其他的查询方法：

select ename,sal,deptno,from emp where sal > ( select max (sal) from emp where deptno = 30 );

在多行查询中使用any(任何一个)操作符

思考：如何显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号

select ename,sal,deptno from emp where sal > any( select sal from emp where deptno = 30 )

其他查询方法：

select ename,sal,deptno,from emp where sal > ( select min(sal) from emp where deptno = 30 );

多列子查询：

单列子查询指的是子查询只返回单列，单行数据。多行子查询指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数的子查询语句。

思考：如何查询与smith的部门和岗位完全相同的所有雇员

思路：

先查询出Smith所在部门，和它的岗位

select deptno,job,from emp where ename = "SMITH";

select * from emp where ( deptno, job ) = ( select deptno,job,from emp where ename = "SMITH" ) #Oracle独有的查询方法

form 子句中使用子查询：

在form子句中使用子查询时，该子查询被作为一个临时表来对待，当在 from子句中使用子查询时，必须给子查询指定别名　　

====================================================================

思考：查找每个部门工资最高的人的详细资料

思路：先查询各个部门的最高工资

select max(sal),depthno from emp group by depnto;

将上面的语句作为一个临时表对待

select * from emp t1,( select max(sal) mymax,deptno from emp group by depnto ) t2 where t1.deptno = t2.deptno and t1.sal = t2.mymax;

显示各个部门的信息和人员的数量

============================= 合并查询 =====================================

1》 union 该操作符用于取两个结果集的并集，当使用该操作符时，会自动去掉结果集中重复的行。

2》union all 该操作符与union相似，但是它不会取消重复的行，而且不会排序。

3》intersect 该操作符用于取得两个结果集的交集。

4》minus 取结果集的差集

========================== 内连接 ====================

内连接：内连接实际上就是利用where子句对两张表形式的笛卡尔积进行筛选。特点：只有连个表同时匹配上才会被选中。

select * from tableName1 inner join tableName2 on 条件 ---两者相同---> select * from tableName1,tableName2 where 条件

========================= 外链接 ======================

1》左外连接　（左侧的表完全显示） left jion tableName2 on 条件

　　Oracle的写法：select stu.name，stu.id，exam.grade from stu，exam where stu.id = exam.id(+);对方的表为左表

2》右外连接　　（右侧的表完全显示）right join tableName2 on 条件

　　Oracle的写法：select stu.name，stu.id，exam.grade from stu，exam where stu.id(+) = exam.id;对方的表为右表，+号在=号的左边表示右外联

3》完全外链接　　（两张表完全显示，没有匹配的记录设置为空）

　　select * from t_a a full join t_b b on a.id=b.id;

=========================== 创建Oracle数据库实例 ==============================================

”数据库实例”配置助手：

第一步：

第二步；

第三步：

第四步：

第五步：

第六步：

第七步：

第八步：

第九步：

第十步：

第十一步：

第十二步：

第十三步：

------------------------------------------------------------------------------------------------------------------------------------------------

创建完成一个新的“数据库实例”后在服务中就会有新的服务创建。

一个“数据库实例”对应两个数据库服务。

在同一台机器上，可以同时启动多个数据库实例，在登录或者链接的时候，需要指定主机字符串，

==================================================================================

***************************************************************************************************************************

==================================================================================

==========================================　　java如何操作Oracle　　==========================================

java链接Oracle有两种方式：

1.jdbc直连

jdbc直连必须启动该服务：

----------------------------------------------------------------------------------------------------------------------------

java链接Oracle的jdbc链接：

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:myora1","scott","m123");

2.jdbc-odbc桥链接

Class.forName("sun.jdbc.odbc.jdbcOdbc.Driver");

Connection ct = DriverManager.getConnection("jdbc:odbc:testsp(数据源的名称)","scott","m123");

步骤：

1.配置数据源：控制面板--->管理工具--->数据源(ODBC)

-------------------------------------------------------------------------------------------------------------

什么时候使用 jdbc和jdbc-odbc

原则：如果java程序和db不在同一台机器上。使用jdbc。

如果java和db在同一台机器上，则连个都可以使用。

==============================　　函数　　==========================================

to_date(String，“format”) 将字符串转化为Oracle中的一个日期

插入时间列的时候。必须已默认格式的形式添加否则就会出错。

函数分为：

单行函数(对每一行处理完后，返回每一行的结果)：比如 length

多行函数(返回结果只有1行)：max()　　min()　　avg()　　

字符函数：

replace(char1,serch_string,replace_string)：替换函数

char1	原来的字符串
serch_string	查找的字符串
replace_string	替换的字符串

instr(C1,C2,I,J)：在一个字符串中搜索指定的字符，返回发现指定字符的位置

C1	被搜索的字符串
C2	希望搜索的字符串
I	搜索的开始位置，默认为1
J	出现的位置，默认为1

upper 例如：select upper('Abc') from tableName 输出：ABC

返回字符串，将所有的字符大写

lower 例如：select lower('ABC') from tableName 输出：abc

返回字符串，并将所要字符小写

initcap

concat 例：select concat('010-','8888') ||'转23'：输出：010-8888转23

连接两个字符串

substr

length 例如：select length(列名) from tableName; 输出：该列字段的长度。

返回字符串长度

lpad 例如：select lpad( 'Page 1',15, '*.' ) "LPAD example" from dual;

Page 1：显示的内容。

15：总共显示15个字符

*.：如果显示不够15个字符就在左面用 *来做占位符

rpad

trim

ascii 例：select ascii('a');

返回自定字符的十进制数

chr 例： select char(65); 返回 A

给出整数，返回对应的字符

initacp 例：select initcap( 'smith' ) from tableName:输出：Smith

返回字符串并将字符串的第一个字母变为大写

substr(string,start,count) 例如：select substr('1308',1,2) from tableName 输出：13

取字符串，从start开始，取出count个

===============================　　Oracle 事务　　 ===============================================================================

----------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------

在Oracle中没有赃读。

---------------------------------------------------------------------------------------------------------------------------

1.维护数据的完整性。三种方法：约束、触发器、应用程序(函数、过程)

约束包括：not null、unique(唯一，不能重复、可以为null)、primary key(不能重复、不能为null)、foreign key、check(检查)

primary key 和 unique 的区别：

一个表可以有多个unique，但是只能有一个primary key

每张表都应该有primary key

unique的值可以为null，但是primary key 不能为null

primary key 的所在列，会自动创建索引，但是unique不会自动创建索引

-----------------------------------------------------------------------------------------------------------------------------check：用于强制执行数据必须满足的条件，假定在sal列定义了check约束。并要求sal列值在1000~2000之间如果不在该区间，就会出错

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2.序列(Sequence)：自动增长

nocache：表示不缓存，[cache10：表示一次产生10个号供你使用，使用缓存产生号，优点是提高效率，缺点是可能产生跳号]

-----------------------------------------------------------------------------------------------

创建序列：

------------------------------------------------------------------------------------------------------------------------

使用序列：

---------------------------------------------------------------------------------------------------------------------------------------------------------

说明：mysql:表示序列名字，nextval:这是一个关键字。

删除序列： drop sequence 序列名

------------------------------------------------------------

序列的细节：

　　1可以为表中的列自动产生值

　　2.由用户创建数据库对象，并可由多个用户共享。例如：system 可以使用scott创建的序列。

　　问题：如果system使用scott的序列，从什么开始增长？答：接着增长。

　　3.一般用于主键列，或者唯一列(unique)

　　4.可以使用序列名.currval 来看当前序列到多少号例如：select 序列名.currval from dual;

　　5.如果希望去查看序列.currval 必须先使用序列.nextval 值，否则出错

-------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

3.管理索引

单列索引：基于单个列所建立的索引：语法：

create index index_name on tableName( column name )

复合索引：

create index index_name on tableName( colunm name, colunm name )

----------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

4.管理权限和角色(dba管理)

------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------

系统权限：

系统权限指的是执行特定类型SQL 命令的权限，常用的有：

create session 连接数据库 create table 建表

create view 建视图 create public synonym 建同义词

create procedure 建过程，函数、包 create trigger 建触发器

create cluster 建簇

如何使用select 来查询有哪些系统权限：

select * from system_privilege_map order by name;

------------------------------------------------------------------------------

创建用户，并且制定密码：

create user ken identified by m123 ( 用户名:ken 密码：m123)

给用户 ken 授权：

create session 和 create table 权限时带 with admin option

基本用法： grant 权限名称 to 用户名

grant create session to ken with admin option [ 带 with admin option 就表示 ken 可以把他得到的这两个权限，继续向别的用户转发]

----------------------------------------------------------------------------------------------------------------------------------------

==============================　　pl/sql编程　　===================================================

------------------------------------------------------------------------------------------------------------------------------

posted on 2018-05-03 22:45 樊聪阅读(593) 评论(0) 收藏举报

刷新页面返回顶部