Oracle 11 g 数据库使用教程以及使用过程中的问题

oracle 同时被 3 个专栏收录
4 篇文章 0 订阅
6 篇文章 0 订阅
29 篇文章 0 订阅

2020-12-21 更新bug

PLSQL 登录oracle数据库出现 “无法解析指定的连接标识符”错误

解决办法:
问题就是安装完client后,出在tnsnames.oRA,你需要把tnsnames.oRA删掉,然后复制oracle服务器安装里的tnsnames.ora。

F:\Oracle\Client\product\11.2.0\client_1\network\admin\sample

下的 tnsnames.oRA 删除后,将

F:\Oracle\Server\product\11.2.0\dbhome_1\NETWORK\ADMIN

下的 tnsnames.oRA 复制到

F:\Oracle\Client\product\11.2.0\client_1\network\admin

里面,重新登陆plsql即可。

一、创建表空间

在SQL窗口中执行以下SQL代码。
在这里插入图片描述
1、创建表空间

-- Create the TABLESPACE
CREATE TABLESPACE FJFEDW
    LOGGING 
    DATAFILE 'F:\app\administrator\oradata\orcl\FJFEDW.DBF' SIZE 32M 
    AUTOEXTEND 
    ON NEXT  2000K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT  AUTO;

2、创建临时表空间,这里也可不创建临时表空间,使用默认的temp也可以。

CREATE TEMPORARY TABLESPACE FJFEDW_TEMP
TEMPFILE 'F:\app\Administrator\oradata\orcl\FJFEDW_TEMP.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

二、创建用户

方法一:执行sql语句
1、创建用户

-- Create the user 
  create user test-- 用户名
  identified by test-- 口令密码
  default tablespace FJFEDW--默认表空间
  temporary tablespace TEMP  --临时表空间
 
  (没有创建临时表空间的执行上一句,创建的执行此句:
  temporary tablespace FJFEDW_TEMP)
  
  profile DEFAULT
  password expire;

2、给用户赋予权限

-- Grant/Revoke role privileges 
grant connect to test with admin option;
grant dba to test with admin option;

-- Grant/Revoke system privileges 
grant unlimited tablespace to test with admin option;

方法二:图形界面设置
1、在新建中找到用户选项。
在这里插入图片描述
2、填写用户名密码,选择之前创建的表空间。概要文件选择默认的。
在这里插入图片描述
3、设置角色权限。在这里插入图片描述
4、设置系统权限。在这里插入图片描述
5、限额设置。
在这里插入图片描述

用户口令管理

1 使用profile管理用户口令
概述:profile是口令限制,资源限制的命令集合,当建立数据库时,oracle会自动建立名称为default的profile。
当建立用户没有指定profile选项时,那么oracle就会将default分配给用户。

2 账户锁定
概述:指定该账户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令。

例子:指定scott这个用户最多只能尝试3次登陆,锁定时间为2天,创建profile文件

create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

alter user scott profile lock_account;

3 给账户(用户)解锁

alter user scott account unlock;

4 终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba的身份来操作。

例子:给前面创建的用户test创建一个profile文件,要求该用户每隔10天要修改自己的登陆密码,宽限期为2天。看看怎么做。

create profile myprofile limit password_life_time 10 password_grace_time 2;

alter user test profile myprofile;

5 口令历史
概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码。
例子:

  1. 建立profile
create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 

//password_reuse_time指定口令可重用时间即10天后就可以重用
  1. 分配给某个用户
alter user test profile password_history;
  1. 删除profile
    概述:当不需要某个profile文件时,可以删除该文件。
 drop profile password_history 【casade】

注意:文件删除后,用这个文件去约束的那些用户通通也都被释放了。
加了casade,就会把级联的相关东西也给删除掉。

6 Oracle数据类型
常用的数据类型
在这里插入图片描述

  1. 字符类 char 长度固定,最多容纳2000个字符。
    例子:char(10) ‘小韩’前四个字符放‘小韩’,后添6个空格补全,如‘小韩 ’ varchar2(20) 长度可变,最多容纳4000个字符。
    例子:varchar2(10) ‘小韩’ oracle分配四个字符。
    这样可以节省空间。clob(character large object) 字符型大对象,最多容纳4g char 查询的速度极快浪费空间,适合查询比较频繁的数据字段。varchar 节省空间

  2. 数字型 number范围-10的38次方到10的38次方,可以表示整数,也可以表示小数 number(5,2)表示一位小数有5位有效数,2位小数;

范围:-999.99 到999.99 number(5)表示一个5位整数;范围99999到-99999

  1. 日期类型 date 包含年月日和时分秒 oracle默认格式1-1月-1999 timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。

  2. 图片 blob 二进制数据,可以存放图片/声音4g;
    一般来讲,在真实项目中是不会把图片和声音真的往数据库里存放,一般存放图片、视频的路径,如果安全需要比较高的话,则放入数据库。

三、登录

在菜单栏选择session下拉选项选中log on 进行登录。
在这里插入图片描述

四、数据库导入导出

oracle11g数据库导入导出

oracle11g数据库的导入/导出,就是我们通常所说的oracle数据的还原/备份。

  • 数据库导入:把.dmp 格式文件从本地导入到数据库服务器中(本地oracle测试数据库中);
  • 数据库导出:把数据库服务器中的数据(本地oracle测试数据库中的数据),导出到本地生成.dmp格式文件。
  • .dmp 格式文件:就是oracle数据的文件格式(比如视频是.mp4 格式,音乐是.mp3 格式);

方法:

  • 传统方式——exp(导出)和(imp)导入:
  • 数据泵方式——expdp导出和(impdp)导入;
  • 第三方工具——PL/sql Develpoer;

一、传统方法:
通用命令:

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;

exp:导出命令,导出时必写。
imp:导入命令,导入时必写,每次操作,二者只能选择一个执行。
username:导出数据的用户名,必写;
password:导出数据的密码,必写;
@:地址符号,必写;
SERVICENAME : Oracle的服务名,必写;
1521:端口号,1521是默认的可以不写,非默认要写;
file=“e:\temp.dmp” : 文件存放路径地址,必写;
full=y :表示全库导出。可以不写,则默认为no,则只导出用户下的对象;
数据库导出举例:

exp test/123456@orcl file="e:\temp.dmp" full = y;

1.完全导入导出:

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" full = y;

2.部分用户表table导入导出:

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" tabels=                                                                  (table1,table2,table3,...);

3.表空间tablespaces导入导出:
(一个数据库实例可以有N个表空间(tablespace),一个表空间下可以有N张表(table)。)

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp" tablespaces=                                                         (tablespace1,tablespace2,tablespace3,...);

4.用户名username对象导入导出:

exp(imp)  username/password@SERVICENAME:1521 file="e:\temp.dmp"                                                                                owner(username1,username2,username3);

二、数据泵方法:

创建directory:

expdp(impdp) username/password@SERVICENAME:1521 schemas=username                                                                       dumpfile=file1.dmp logfile=file1.log directory=testdata1 remap_schema=test:test;

expdp: 导出命令,导出时必写。
impdp: 导入命令,导入时必写,每次操作,二者只能选择一个执行。
username:导出数据的用户名,必写;
password:导出数据的密码,必写;
@:地址符号,必写;
SERVICENAME:Oracle的服务名,必写;
1521:端口号,1521是默认的可以不写,非默认要写;
schemas:导出操作的用户名;
dumpfile:导出的文件;
logfile:导出的日志文件,可以不写;
directory:创建的文件夹名称;
remap_schema=源数据库用户名:目标数据库用户名,二者不同时必写,相同可以省略;

数据库导出举例:

expdp test/123456@orcl:1521 schemas=test dumpfile=test.dmp 
logfile=test.log directory=testdata1;

1.查看表空间:

select  from dba_tablespaces;

2.查看管理理员目录(同时查看操作系统是否存在,因为Oracle并不关心该目录是否存在,如果不存在,则出错)。

select  from dba_directories;

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

create directory testdata1 as 'd:\test\dump';

4.给test用户赋予在指定目录的操作权限,最好以system等管理员赋予。
//test是用户名(123456是用户密码)

grant read,write on directory testdata1 to test;

5.导出数据

1)按用户导

expdp test/123456@orcl schemas=test dumpfile=expdp.dmp directory=testdata1;

2)并行进程

parallel expdp test/123456@orcl directory=testdata1                                                                                    dumpfile=test.dmp parallel=40 job_name=test

3)按表名导

expdp test/123456@orcl tables=emp,dept dumpfile=expdp.dmp    directory=testdata1;

4)按查询条件导

expdp test/123456@orcl directory=testdata1                                                                                            dumpfile=expdp.dmp tables=emp query='WHERE deptno=20';

5)按表空间导

expdp system/manager directory=testdata1 dumpfile=tablespace.dmp   tablespaces=temp,example;

6)导整个数据库

 expdp system/manager directory=testdata1 dumpfile=full.dmp FULL=y;

6.还原数据
1)导到指定用户下

impdp test/123456 directory=testdata1 dumpfile=expdp.dmp                                                              schemas=test;

2)改变表的owner

impdp system/manager directory=testdata1 dumpfile=expdp.dmp                                                               tables=test.dept remap_schema =test:system;

3)导入表空间

impdp system/manager directory=testdata1 dumpfile=tablespace.dmp  tablespaces=example;

4)导入数据库

impdb system/manager directory=dump_dir dumpfile=full.dmp FULL=y;

5)追加数据

impdp system/manager directory=testdata1 dumpfile=expdp.dmp                                                         schemas=system  table_exists_action;

三、PLSQL方法:
登录plsql工具,所使用用户为源数据库有导出权限(exp_full_database,dba等)的用户。

1.导出建表语句(包括存储结构)
  导出步骤tools ->export user object,选择要导出的对象.
  导出.sql格式文件并等待导出完成:
在这里插入图片描述
  2.导出数据文件 ;
 1)导出为dmp格式
 导出步骤tools ->export tables,选择要导出的表及导出的格式进行导出。

在这里插入图片描述
 2)导出为sql格式
在这里插入图片描述
 3)导出为pde格式
在这里插入图片描述
提示说明:采用第三方工具导出导入整个数据库的话,耗时较长,一定要有足够的时间来操作(数据量大的话需要好几个小时)

3.导入建表语句
   导入步骤tools->import tables->SQL Inserts 导入.sql文件
  
4.导入数据;
   tools->import talbes,然后再根据导出的数据格式选择导入dmp文件,或者sql文件,或者pde文件。
  
  【注】导入之前最好把以前的表删除,当然导入另外数据库除外。
  另外导入时当发现进度条一直卡在一个点,而且导出的文件不再增大时,甚至是提示程序未响应,千万不要以为程序卡死了,这个导入导出就是比较缓慢,只要没有提示报错,或者导入完成就不要停止程序。

五、查看ORACLE数据库及表信息的方法

  • 查看ORACLE 数据库中本用户下的所有表
SELECT table_name FROM user_tables;
  • 查看ORACLE 数据库中所有用户下的所有表
  select user,table_name from all_tables;
  • 查看ORACLE 数据库中本用户下的所有列
select table_name,column_name from user_tab_columns;
  • 查看ORACLE 数据库中本用户下的所有列
select user,table_name,column_name from all_tab_columns;
  • 查看ORACLE 数据库中的序列号
select * from user_sequences;
  • 上面的所有对象,都可以通过下面的SQL语句查询得到
  • 查询所有的用户生成的ORACLE对象
SELECT * FROM user_objects;
  • 查看ORACLE 数据库中所有表的注释
select table_name,comments from user_tab_comments;
  • 查看ORACLE 数据库中所有列的注释
select table_name,column_name,comments from user_col_comments;
  • 给表加ORACLE的注释
COMMENT ON TABLE aa10 IS '系统参数表';
  • 给列加ORACLE的注释
COMMENT ON COLUMN aa10.aaa100 IS '参数类别';
  • 查看表中列的属性,包括 数据类型,是否非空等
DESC aa10;
  • 通过系统表,查看表中列的属性,包括 数据类型,是否非空等
SELECT table_name,COLUMN_ID,column_name,data_type,data_length,DATA_PRECISION,NULLABLE

FROM user_tab_columns

ORDER BY table_name,COLUMN_ID;
  • 查看所有表空间
selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name
  • 查看未使用表空间大小
selecttablespace_name,sum(bytes)/1024/1024 from dba_free_space group bytablespace_name;
  • 查看数据库中表、索引占用的数据库空间大小
SELECT * FROM user_segments;
  • 查看所有表的记录数
CREATE TABLE table_count(table_name VARCHAR2(50),columns NUMBER(20));
  • 通过PB运行下面的语句,得到结果集,将结果集在PB下执行,最后提交
select 
'insert into table_count values('''||table_name||''', 
(select count(1)from '||table_name||')
);//'  ||comments from user_tab_comments;
  • 所有表的记录都在table_count了
SELECT * FROM table_count;

六、新建、修改及删除ORACLE数据库表

1 、建表

Create table 表名(
字段1   数据类型     [default 默认值],
字段2   数据类型     [default 默认值],
...
字段n   数据类型     [default 默认值]
;

【例】
创建person表

create table person(
       pid      number(10),
       name     varchar2(10),
       gender   number(1)  default 1,
       birthday date
);

insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

2、删除表

DROP TABLE 表名;

3、修改表
添加语法:

ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值],列名1 类型 [DEFAULT 默认值])

修改语法:

ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值],列名1 类型 [DEFAULT 默认值])

修改列名:

ALTER TABLE 表名称 RENAME COLUMN 列名1 TO 列名2

添加一个字段

alter table student add (classid number(2));

修改一个字段的长度

alter table student modify (xm varchar2(30));

修改字段的类型或是名字(不能有数据) 不建议做

alter table student modify (xm char(30));

删除一个字段 不建议做(删了之后,顺序就变了。加就没问题,应该是加在后面)

alter table student drop column sal;

修改表的名字 很少有这种需求

rename student to stu;

【例】
在person表中增加列address

alter table person add(address varchar2(10));

把person表的address列的长度修改成20长度

alter table person modify(address varchar2(20));

4、 截断表
在person表中使用delete语句删除数据,则可以使用rollback来回滚,如果想要清空一张表的数据,同时不想回滚可以立即释放资源需要使用截断表的语法

TRUNCATE TABLE 表名

【例】
截断person表

truncate table person;

5、表数据更新
1 INSERT

INSERT INTO表名[(列名1,列名2,…)]VALUES(1,值2,…)

insert into person(pid,name,gender,birthday,addrress)
values(1,'张三',1,'9-5月-1981','北京');

想要将从其它表中选择数据并将其插入另一个表中。要做到这一点,可使用Oracle INSERT INTO SELECT语句,如下所示:

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;

Oracle INSERT INTO SELECT语句要求源表和目标表匹配,有时我们可能希望将多行插入到一个或多个表中。
在这种情况下,就可以使用Oracle INSERT ALL语句,该语句也被称为多项式插入语句。

Oracle提供了两种类型的多项式插入语句:无条件的和有条件的。

无条件的Oracle INSERT ALL语句

将多行插入到表中,要将多行插入到表中,请使用以下Oracle INSERT ALL语句:

INSERT ALL
    INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
    INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
    INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;

在这个语句中,每个值表达式值:val1,val2或val3必须引用由子查询的选择列表返回的列对应的值。

如果要使用文字值而不是子查询返回的值,请使用以下子查询:

SELECT * FROM dual;

条件多项插入语句允许根据指定的条件将行插入到表中。

以下显示了条件多项插入语句的语法:

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery

如果指定了ALL关键字,则Oracle将在WHEN子句中评估每个条件。如果条件评估/计算为true,则Oracle执行相应的INTO子句。

但是,当指定FIRST关键字时,对于由子查询返回的每一行,Oracle都会从WHEN子句的上下方向评估每个条件。

如果Oracle发现条件的计算结果为true,则执行相应的INTO子句并跳过给定行的后续WHEN子句。

请注意,单条件多项式插入语句最多可以有127个WHEN子句。

Oracle多表插入语句受以下主要限制:

  • 它可以用来将数据只插入到表中,而不是视图或物化视图。
  • 它不能用来将数据插入到远程表中。
  • 所有INSERT INTO子句中的列总和不得超过999。
  • 一个表集合表达式不能在一个多表插入语句中使用。
  • 多表插入语句的子查询不能使用序列。

2 事务
因为oracle的事务对数据库的变更的处理,我们必须做提交事务才能让数据真正的插入到数据库中,在同样在执行完数据库变更的操作后还可以把事务进行回滚,这样就不会插入到数据库。
如果事务提交后则不可以再回滚。

提交:commit
回滚:rollback

  1. 事务保存点与回滚:
savepoint 保存点名字

示例:

savepoint a1;       --设置一个保存点 a1;
update person set name='张三' where pid='101'  --修改一条数据
savepoint a2;       --设置一个保存点 a1;
update tmp set name='李四' where id='102  --再修改一条数据

rollback to a2;     --回滚到a2 保存点。 此时在 a2 保存点之后的所有数据修改视为无效。
rollback to a1;     --这里可以再从a2保存点再回滚到a1 保存点。 此时在 a1 保存点之后的所有数据修改视为无效。
rollback;           --回滚全部。即撤消至上一次提交事务后的所有数据修改。
commit;             --提交事务 将回滚后的事务提交,并会删除所有保存点。
  1. 我们可以从a2向前再回滚到a1 ,但无法从a1回滚到a2。也就是只能向前回滚,不能从前面的点再向后回滚!

3 UPDATE
全部修改:

UPDATE 表名 SET 列名1=1,列名2=2,…

局部修改:

UPDATE 表名 SET 列名1=1,列名2=2,…WHERE 修改条件

4 DELETE

DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据

现实应用中,经常从与其他表中的行具有外键关系的表中来删除行记录。

例如,想要从orders表中删除id为1的销售订单,并从order_items表中删除与订单id是1关联的所有行项目。
通常可以发出两个DELETE语句,如下所示:

-- 第一条
DELETE FROM
    orders
WHERE
    order_id = 1;

-- 第二条
DELETE FROM
    order_items
WHERE
    order_id = 1;

COMMIT WORK;

COMMIT WORK语句确保两个DELETE语句以全部或全部方式执行,以防第一条语句成功,但第二个DELETE语句失败时在order_items表中的孤行。

但是,如果知道如何正确设置表的约束,那么这是不必要的。

在这种情况下,当创建order_items表时,可以使用DELETE CASCADE选项定义一个外键约束

CREATE TABLE order_items 
(
    order_id   NUMBER( 12, 0 )                                , 
    -- other columns
    -- ...
    CONSTRAINT fk_order_items_orders 
    FOREIGN KEY( order_id ) 
    REFERENCES orders( order_id ) 
    ON DELETE CASCADE
);

通过这样做,每当从orders表中删除一行时

DELETE
FROM
    orders
WHERE
    order_id = 1;

order_items表中order_id为1的所有行也被数据库系统自动删除。

Truncate table实现数据删除

比较truncat与delete实现数据删除?

delete删除的数据可以rollback,也可以闪回

delete删除可能产生碎片,并且不释放空间

truncate是先摧毁表结构,再重构表结构

5 Merge语句
Oracle MERGE语句从一个或多个源表中选择数据并更新或将其插入到目标表中。
MERGE语句可指定一个条件来确定是更新数据还是将数据插入到目标表中。

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

首先,指定要在INTO子句中更新或插入的目标表(target_table)。
其次,指定要更新或插入USING子句中的数据源(source_table)。
第三,指定合并操作在ON子句中更新或插入的搜索条件。

对于目标表中的每一行,Oracle都会评估搜索条件:

如果结果为true,则Oracle使用源表(source_table)中的相应数据更新该行。
如果任何行的结果为false,则Oracle将源表(source_table)中相应的行插入到目标表(target_table)中。
当想要在单个操作中组合多个INSERT,UPDATE和DELETE语句时,MERGE语句变得很方便。

因为MERGE是确定性语句,所以不能在同一个MERGE语句中多次更新目标表的同一行。

可以将一个可选的DELETE WHERE子句添加到MATCHED子句中,以在合并操作之后进行清理。 DELETE子句只删除目标表中与ON和DELETE WHERE子句匹配的行。

要执行MERGE语句,必须在源表上具有INSERT和UPDATE对象权限。 如果使用DELETE子句,则还必须在目标表上具有DELETE对象特权。

【扩展】

DDL : 数据定义语言 create alter drop truncate
DML : 数据操纵语言 insert update delete
DCL : 数据控制语言 安全 授权 grant revoke
DQL : 数据查询语言 select from子句 where子句

欢迎点赞、关注和评论、一键三连哦!
如果你也是技术宅,如果你也对技术渴望提升,那么我们是同一类人,欢迎大家关注我的个人公众号,我们一起进步吧!在这里插入图片描述
如果对你有帮助,赞赏也是对我最大的支持。
在这里插入图片描述

  • 1
    点赞
  • 1
    评论
  • 10
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值