学习写第一篇博客--测试

最新推荐文章于 2024-06-05 14:49:48 发布

-＞ān＜-

最新推荐文章于 2024-06-05 14:49:48 发布

阅读量196

点赞数 1

分类专栏： Oracle 文章标签： oracle

Oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

第一章 Oracle 简介及安装

1.1 Oracle下载

Oracle11g32位下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0817/4787.html

Oracle11g64位下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0817/4786.html

HelloWorld.java

public class HelloWorld{
    public static void main(String[] agrs) {
        System.out.prinln("HelloWorld!");
    }
}

1.2 PLSQLDeveloper 安装

Plsqldeveloper10下载：http://www.java1234.com/a/javaziliao/kfgj/2015/0815/4776.html

Plsqldeveloper 没有开发64位版本，所以导致了64位用户没法直接连接oracle数据库

解决办法：

第一步：下载32位oracle客户端instantclient-basic-win32-10.2.0.5

下载地址：http://pan.baidu.com/s/1o6sx7n0 解压

第二步：安装plsql developer

第三步：环境变量：

① TNS_ADMIN：oracle根目录\product\11.2.0\dbhome_1\NETWORK\ADMIN

② NLS_LANG：SIMPLIFIED CHINESE_CHINA.ZHS16GBK

③ 配置plsql developer首选项；

1.3 Oracle11g卸载

Oracle 卸载：http://jingyan.baidu.com/article/922554468d4e6b851648f4e3.html（百度经验）

第二章 Oracle表及表空间

2.1 Oracle表空间

一个数据库可以有多个表空间，一个表空间里可以有多个表。表空间就是存多个表的物理空间；

可以指定表空间的大小位置等。

创建表空间：create tablespace ts1 datafile 'C:\tablespace\ts1.dbf' size 50M;

自动扩展大小((100M))：

create tablespace ts2 datafile 'C:\tablespace\ts2.dbf' size 1M autoextend on next 100M;

设置最大空间(1024M)：

create tablespace ts3 datafile 'C:\tablespace\ts3.dbf' size 50M autoextend on next 10M maxsize 1024M;

修改用户默认表空间：alter database default tablespace ts1;

表空间改名：alter tablespace ts1 rename to ts2;

删除表空间：drop tablespace ts2 including contents and datafiles;

查看所有表空间及表空间物理存放路径：select * from dba_data_files;

2.2 Oracle数据库表

--创建Student表

create table Student(

id varchar2(32) primary key, --主键

name varchar2(8) not null,

age number,

)

--添加表注释：

comment on table Student is '学生信息表';

--添加字段注释：

comment on column Student.id is 'id';

comment on column Student.name is '姓名';

comment on column Student.age is '年龄';

2.3 Oracle虚拟表dual表

dual表是sys用户下的一张虚表；提供一些运算和日期操作时候用到。

dual表作用：

1、查看当前用户

select user from dual;

2、用来调用系统函数

select to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

select sys_context('userenv','terminal') from dual;--获得主机名

select sys_context('userenv','language') from dual;--获得当前locale

select DBMS_RANDOM.random from dual;--获得一个随机数

3、可以用做计算器

select 7*9*10-10 from dual;

4、查看系统时间

select sysdate from dual;

第三章 Oracle序列

3.1 序列简介

序列作为数据库里的对象，主要作用是生成唯一的主键值；

3.2 创建序列

create sequence序列名称；

重要属性：序列名称.currval 当前值 nextval 下一个值

指定初始值：start with xx

3.3 序列属性

Minvalue maxvalue 最大值和最小值默认最小值1最大值 10的27次方；

Increment by 属性序列增长步长默认值1

Cycle 设置循环；（了解即可，不使用）；

Cache 缓存设置；一次获取序列的多个连续值，默认20 ，放到内存中，方便下次快速获取；

3.4案例：

create sequence CSG_S_ID --序列名

minvalue 1 --最小值

maxvalue 999999999 --最大值

start with 1 --开始值

increment by 1 --序列增长步长

cache 3000 --缓存一次获取序列的多个连续值

第四章 Oracle表操作

4.1 scott用户默认表介绍

① 部门表：dept

② 雇员表：emp

③ 工资等级表：salgrade

④ 工资表：bonus

4.2 插入insert语句

给指定列插入数据：insert into dept(deptno,dname) values(50,'xx');

插入全部列数据：

insert into dept(deptno,dname,loc) values(60,'xx','lll'); 简写 insert into dept values(70,'xxx','llll');

4.3 更新update语句

更新指定数据：update dept set dname='司法部' where deptno=50;

update dept set dname='司法部' ,loc='china' where deptno=50;

4.4 删除delete语句

删除指定数据：delete from dept where deptno=70;

删除指定条件的数据：delete from dept where deptno>40;

4.5 查询select语句

查询所有：select * from emp;

指定字段查询：select ename,sal from emp;

加where条件：

select * from emp where sal>=800;

select * from emp where sal>=1500 and job='SALESMAN';

Distinct去重复记录；

Group by分组查询：select job,count(ename) as num from EMP t group by job;

Having过滤分组：select job,count(ename) as num from EMP t group by job having count(ename)>=2;

Order by 排序：select * from emp order by sal desc;

子查询：查询出基本工资大于平均工资的员工：select * from emp where sal>(select avg(sal) from emp)

联合查询：

并集（合并两个表的数据，去除重复记录）：

Select * from t_user1

union

select * from t_user2;

并集：(合并两个表的数据，不去除重复记录)

Select * from t_user1

union all

select * from t_user2;

交集：

Select * from t_user1

intersect

select * from t_user2;

差集：

Select * from t_user1

minus

select * from t_user2;

内连接：

select * from emp t,dept d where t.deptno=d.deptno;

类似：select * from emp e inner join dept d on e.deptno=d.deptno; inner 可以省略；

外连接：

左外连接：select * from emp e left join dept d on e.deptno=d.deptno;

右外连接：select * from emp e right join dept d on e.deptno=d.deptno;

第五章 Oracle数据类型及函数

5.1 字符串类型及函数

字符类型分3种，char(n) 、varchar(n)、varchar2(n) ；

char(n)固定长度字符串，假如长度不足n，右边空格补齐；

varchar(n)可变长度字符串，假如长度不足n，右边不会补齐；

varchar2(n)可变长度字符串，Oracle官方推荐使用，向后兼容性好；

char(n)和varchar2(n)区别：char(n)查询效率相对较高，varchar2(n)存储空间相对较小；

select length(userName) as charlength,length(password) as varchar2length from t_user3;

lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user3;

rpad() 向右补全字符串：select rpad(stuno,6,'0') from t_user3;

lower() 返回字符串小写：select lower(userName) from t_user3;

upper() 返回字符串大写：select upper(userName) from t_user3;

initcap() 单词首字符大写：select initcap(userName) from t_user3;

length() 返回字符串长度：select length(password) from t_user3;

substr()截取字符串：select substr(userName,1,2) from t_user3;

instr() 获取字符串出现的位置：select instr(password,'23',2,2) from t_user3;

ltrim() 删除左侧空格：select ltrim(userName) from t_user3;

rtrim() 删除右侧空格：select rtrim(userName) from t_user3;

trim() 删除两侧空格：select trim(userName) from t_user3;

concat() 串联字符串：select concat(userName,password) from t_user3;

reverse() 反转字符串：select reverse(userName) from t_user3;

5.2 数值类型及函数

number是oracle中的数据类型；number(precision,scale)；

Precision，scale均可选；

Precision代表精度，sacle代表小数位的位数；Precision范围[1,38]scale范围[-84,127]

举例： 12345.678 Precision是8 scale是3；

常用方法：

abs() 求绝对值；select abs(n1) from t_number where id=1;

round() 四舍五入：select round(n1,2) from t_number where id=1;

ceil() 向上取整：select ceil(n1) from t_number where id=2;

floor 向下取整：select floor(n1) from t_number where id=2;

Mod() 取模：select mod(5,3) from dual;

Sign() 正负性：select sign(n1) from t_number where id=1;

Sqrt() 求平方根：select sqrt(9) from dual;

Power() 求乘方：select power(2,3) from dual;

Trunc() 截取：select trunc(123.456,2) from dual;

to_char() 格式化数值：常见的字符匹配有 0、9、，、$、FM、L、C

select to_char(123.45,'0000.000') from dual;

select to_char(123.45,'9999.999') from dual;

select to_char(123123,'99,999,999.99') from dual;

select to_char(123123.3,'FM99,999,999.99') from dual;

select to_char(123123.3,'$99,999,999.99') from dual;

select to_char(123123.3,'L99,999,999.99') from dual;

select to_char(123123.3,'99,999,999.99C') from dual;

5.3 日期类型及函数

Date和timestamp （时间戳）

Date包含信息century （世纪信息）year 年month 月day 日hour 小时minute 分钟 second秒

Timestamp 一般用于日期时间要求非常精确的情况，精确到毫秒级；

insert into t_date values(1,sysdate,systimestamp);

下面重点讲date类型的常用函数：

select sysdate from dual;

select systimestamp from dual;

Add_months 添加月份 select add_months(d1,2) from t_date where id=1;

Last_day 返回指定日期月份的最后一天 select last_day(d1) from t_date where id=1;

update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1;

update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1;

Months_between 返回两个日期的相差月数 select months_between(d1,d3) from t_date where id=1;

next_day 返回特定日期之后的一周内的日期：select next_day(d1,2) from t_date where id=1;

Trunc 截取日期：

select trunc(d1,'YYYY') from t_date where id=1;

select trunc(d1,'MM') from t_date where id=1;

select trunc(d1,'DD') from t_date where id=1;

select trunc(d1,'HH') from t_date where id=1;

select trunc(d1,'MI') from t_date where id=1;

Extract 返回日期的某个域：

select extract(year from sysdate) from dual;

select extract(month from sysdate) from dual;

select extract(day from sysdate) from dual;

select extract(Hour from systimestamp) from dual;

select extract(minute from systimestamp) from dual;

select extract(second from systimestamp) from dual;

To_char将日期转换成字符串：

select to_char(d1,'YYYY-MM-DD') from t_date where id=1;

select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;

5.4 其他常用处理函数

常用的聚合函数：

Max 求最大值：select max(sal) from emp ;

Min 求最小值：select min(sal) from emp ;

Avg 求平均值：select avg(sal) from emp ;

Sum 求和：select sum(sal) from emp ;

Count 统计记录数：select count(ename) from emp ;

nvl 空值处理：select ename,nvl(e.sal, 0) from emp e;

decode 判断函数：如果t.dlzh=’nap’ 返回系统管理员，否则返回普通用户

select decode(t.dlzh, 'nap', '系统管理员', '普通用户'), t.* from xt_ry t;

replace 替换函数：将t.username列，包含有a的值替换成BB

select replace(t.username, 'a', 'BB') from test t;

--exists

select * from kh_ydkh a where exists(select 1 from kh_jld b where b.yhbh=a.yhbh)

rownum

Oracle 分页：

select * from (select a.*,rownum rn from (select * from emp) A where rownum<=10) where rn>5;

Oracle 中的运算：

select 2+1 from dual;

select 2-1 from dual;

select 2*1 from dual;

select 2/1 from dual;

条件判断式：

Between and 范围查询：

select * from emp where sal between 900 and 1500;

select * from emp where sal>=900 and sal<=1500;

In 集合范围：

select ename,hiredate from emp where ename in (select distinct ename from bonus)

Like 模糊查询：

select * from emp where ename like '%M%'

select * from emp where ename like 'M%'

select * from emp where ename like '_M%''

第六章 Oracle视图

6.1 视图简介

视图是虚表，没有具体物理数据，是通过实体表的一种计算映射逻辑。主要就是为了方便和数据安全；

6.2 创建视图

Create view 视图名称 as 查询语句

Create view v_emp1 as select ename,job from emp;

6.3 修改视图

6.4 删除视图

6.5 使用视图

查询：select * from v_emp1; select * from v_emp1 where ename like '%M%';

修改：update v_emp1 set job='销售' where ename='sb';

添加：insert into v_emp2 values('2222','sb2','技术');

删除：delete from v_emp2 where empno=2222;

6.6 只读视图

Create view 视图名称 as 查询语句 with read only ;

第七章 Oracle约束

7.1 主键约束

7.2 外键约束

7.3 唯一性约束

7.4 检查约束

7.5 默认值约束

7.6 非空约束

第八章 Oracle控制语句

8.1 IF条件语句

8.2 CASEWHEN流程控制语句

8.3 循环语句

① 无条件循环loop

② while 循环

③ for 循环

第九章 Oracle游标

9.1 游标简介

使用游标，我们可以对具体操作数据，比如查询的结果，对行，列数据进行更加细致的处理。以及对其他DML操作进行判断等操作；

9.2 显示游标

9.3 隐式游标

隐式游标的属性返回值类型意义

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数

SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反

SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

9.4 动态游标

强类型动态游标

弱类型动态游标

第十章 Oracle触发器

10.1 触发器简介

具备某些条件，由数据库自动执行的一些DML操作行为。

10.2 语句触发器

语句触发器针对整个表，作用整个表操作；

语法结构：

Create trigger 触发器名称

Before/after 触发动作

On 作用对象

触发器谓词：INSERTING、UPDATING、DELETING

10.3 行触发器

行触发器针对行记录。

语法结构：

Create trigger 触发器名称

Before/after 触发动作

For each row

On 作用对象

触发器内置变量 :old :new

10.4 触发器禁用和开启

禁用触发器： alter trigger 触发器名称disable

启用触发器： alter trigger 触发器名称enable

第十一章 Oracle函数与存储过程

11.1 Oracle自定义函数

语法：

Create function 函数名称return 返回值类型as

Begin

…

End 函数名称;

11.2 Oracle存储过程

存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure。

语法：

示例一：无参无返

create or replace procedure p1

--or replace代表创建该存储过程时，若存储名存在，则替换原存储过程，重新创建

--无参数列表时，不需要写()

begin

dbms_output.put_line('hello world');

end;

--执行存储过程方式1

set serveroutput on;

begin

p1();

end;

--执行存储过程方式2

set serveroutput on;

execute p1();

示例二：有参有返

create or replace procedure p2

(name in varchar2,age int,msg out varchar2)

--参数列表中，声明变量类型时切记不能写大小，只写类型名即可，例如参数列表中的name变量的声明

--参数列表中，输入参数用in表示，输出参数用out表示，不写时默认为输入参数。

------------输入参数不能携带值出去，输出参数不能携带值进来，当既想携带值进来，又想携带值出去，可以用in out

begin

msg:='姓名'||name||',年龄'||age;

--赋值时除了可以使用：=，还可以用into来实现

--上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;

end;

--执行存储过程

set serveroutput on;

declare

msg varchar2(100);

begin

p2('张三',23,msg);

dbms_output.put_line(msg);

end;

示例三：参数列表中有in out参数

create or replace procedure p3

(msg in out varchar2)

--当既想携带值进来，又想携带值出去，可以用in out

begin

dbms_output.put_line(msg); --输出的为携带进来的值

msg:='我是从存储过程中携带出来的值';

end;

--执行存储过程

set serveroutput on;

declare

msg varchar2(100):='我是从携带进去的值';

begin

p3(msg);

dbms_output.put_line(msg);

end;

11.3 程序包

引入的目的，是为了有效的管理函数和存储过程，当项目模块很多的时候，用程序包管理就很有效了。

语法：

Create or replace package 包名 as

变量名称1数据类型1；

变量名称2 数据类型2；

...

Function 函数名称1(参数列表) return 数据类型1；

Function 函数名称2(参数列表) return 数据类型2；

...

Procedure 存储过程名称1(参数列表)；

Procedure 存储过程名称2(参数列表)；

...

End 包名；

第十二章 Oracle用户，角色和权限

12.1 Oracle用户

Oracle用户分两种，一种是系统用户 sys、system ；另外一种是普通用户；

视图dba_users 存储着所有用户信息；

--创建用户：create user 用户名 identified by 密码 default tablespace 表空间

create user user1 identified by 123 default tablespace USERS

--授予 session 权限：

grant create session to user1

--锁定帐号：

alter user scott account lock;

--解锁账号：

alter user scott account unlock;

--修改密码：

alter user scott identified by 123;

--删除用户：

drop user user1 cascade;（删除用户，并且把用户下的对象删除，比如表，视图，触发器等）

--查看用户所属的默认表空间、用户名的状态

select u.username, u.default_tablespace, u.account_status from dba_users u

12.2 Oracle权限

Oracle权限分为系统权限和对象权限；

系统权限是Oracle内置的，与具体对象无关的权限，比如创建表的权限，连接数据库权限；

对象权限就是对具体对象，比如表，视图，触发器等的操作权限；

--系统权限表：system_privilege_map

select * from system_privilege_map

--权限分配视图：dba_sys_privs

select * from dba_sys_privs

--查看DBA角色拥有哪些权限

select * from dba_sys_privs where grantee = 'DBA'

回收系统权限：revoke 权限 from 用户

对象权限分配

用户表权限视图：dba_tab_privs

给对象授权： grant 权限 on 对象 to 用户 with grant option;

回收权限：revoke 对象权限 on 对象 from 用户;

12.3 Oracle角色

角色是权限的集合；可以给用户直接分配角色，不需要一个一个分配权限；

语法：

--创建角色

create role 角色名称;

--查看所有角色信息

select * from dba_roles;

--查看用户的角色信息

select * from dba_role_privs;

--查看 Scott 用户拥有哪些角色

select * from dba_role_privs rp where rp.grantee='SCOTT';

--查看 scott 用户拥有哪些表(通过所有者owner查询)

select * from dba_tables t where t.owner='SCOTT'

--查看当前登录的用户拥有哪些表

select * from user_tables;

第十三章 Oracle备份与恢复

一、创建逻辑目录

该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory exp_dir as 'e:\dmp';

删除逻辑目录：drop directory exp_dir;

二、查看管理理员目录

同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;

三、给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予。

grant read,write on directory expdir to scott;

四、导出数据(备份)

1)按用户导

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;

2)并行进程parallel

expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3)按表名导

expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;

4)按查询条件导

expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20';

5)按表空间导

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;

6)导整个数据库

expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;

五、导入数据(还原)

1)导到指定用户下

impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;

2)改变表的owner

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;

3)导入表空间

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;

4)导入数据库

impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;

5)追加数据

impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system

TABLE_EXISTS_ACTION

-＞ān＜-

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录