oracle学习

最新推荐文章于 2022-01-19 21:38:13 发布

weixin_30430169

最新推荐文章于 2022-01-19 21:38:13 发布

阅读量127

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/fengjian2016/p/7444388.html

版权

Oracle安装会自动的生成sys用户和system用户:

（1）sys用户是超级用户，具有最高权限，具有sysdba角色，有create database的权限

（2）system用户是管理操作员，权限也很大。具有sysoper角色，没有create database的权限

（3）scott，普通用户，默认时锁定状态(lock)

也就是说sys和system这两个用户最大的区别是在于有没有create database的权限。

如果安装的时候，忘记对某个用户解锁，比如,scott,我们通过system 来对用户进行解锁。

解锁：

　　1. 使用system登录 cmd ---> sqlplus- ---输入用户名:system ,密码：123456

2. SQL> alter user scott account unlock;

连接oracle 的第三方工具

pl/sql developer 属于第三方软件，主要用于开发，测试，优化oracle pl/sql的存储过程。比如：触发器。

2. Oracle的基本使用--基本命令

sql*plus的常用命令

n 连接命令

1.conn 从scott用户登录，oracle数据库实例，然后切换system 用户

登录后，

用法：conn 用户名/密码@网络服务名[as sysdba/sysoper]当用特权用户身份连接时，必须带上as sysdba或是as sysoper

远程连接：sqlplus usr/pwd@//host:port/sid 如：conn sys/admin@127.0.0.1:1521/orcl as sysdba;

简单使用： conn 用户名@密码 , 该命令经常用于切换当前用户。

显示用户名： show user;

2.disc[onnect]

说明: 该命令用来断开与当前数据库的连接

3.psssw[ord]

说明: 该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system登录。4.show user

说明: 显示当前用户名

5.exit

说明: 该命令会断开与数据库的连接，同时会退出sql*plus

n 文件操作命令

1.start和@

说明: 运行sql脚本

案例: sql>@ d:\a.sql或是sql>start d:\a.sql

2.edit

说明: 该命令可以编辑指定的sql脚本

案例: sql>edit d:\a.sql,这样会把d:\a.sql这个文件打开

3.spool

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

案例: sql>spool d:\b.sql 并输入 sql>spool off

n 交互式命令

1.&

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

select * from emp where job='&job'；

2.edit

说明：该命令可以编辑指定的sql脚本

案例：SQL>edit d:\a.sql

3.spool

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

spool d:\b.sql

输入 spool off

显示和设置环境变量

概述：可以用来控制输出的各种格式，set show如果希望永久的保存相关的设置，可以去修改glogin.sql脚本

1.linesize

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

show linesize

set linesize 90

2.pagesize说明：设置每页显示的行数目，默认是14

用法和linesize一样

至于其它环境参数的使用也是大同小异

3. oracle用户管理

创建用户

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

create user 用户名 identified by 密码; (oracle有个毛病，密码必须以字母开头，如果以字母开头，它不会创建用户)

SQL> create user fengjian identified by 123456;

给用户修改密码

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

password 用户名

如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限

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

SQL> alter user fengjian identified by fengjian;

SQL> create user fengjian identified by abcd default tablespace users temporary tablespace temp quota 3m on users;

identified by 表明该用户将数据库方式验证 default tablespace users //用户的表空间在users上

temporary tablespace temp // 用户的临时表建在temp空间

quota 3m on users 表明用户建立的数据对象（表、索引、视图、pl/sql ）最大只能是3M，刚刚创建的用户是没有任何权限的，需要dba给用户授权。

赋予connect 权限

sql > grant connect to fengjian

赋予resource 权限

sql> grant resource to fengjian;

赋予dba 权限

sql > grant dba to fengjian;

表空间：表存在的空间，一个表空间是指向不同的数据文件。

default tablespace users #user是默认表空间

temporary tablespace temp #temp 临时表空间

权限划分：

　　　　1. 系统权限

　　　　　　系统权限是数据库管理i相关的权限

　　　　　　(1). create session 创建连接

　　　　　　(2). create table

　　　　　　(3). create index

　　　　　　(4) create view

　　　　　　(5) create squence

　　　　　　(6) create trriger

　　　　2. 对象权限

　　　　　　对象权限是和用户操作数据对象相关的权限

　　　　　　(1) update

　　　　　　(2) insert

　　　　　　(3) delete

　　　　　　(4) select　

角色： 1. 预定义角色

　　　　　　把常用的权限集中起来，形成角色。

　　　　(1). dba,包含了许多权限

　　　　(2). connect

　　　　(3). resource

　　　 2. 自定义角色

方案：

　　当一个用户创建好后，如果该用户创建了任意一个数据对象，这时，我们的dmbs就会创建一个对应的方案与该用户对应，并且该方案的名字和用户名一致

删除用户

概述：一般以dba的身份去删除某个用户，如果用其它用户去删除用户则需要具有drop user的权限。

比如 drop user 用户名【cascade】

在删除用户时，注意：

如果要删除的用户，已经创建了表，那么就需要在删除的时候带一个参数cascade;

用户管理的综合案例

概述：创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限使用命令grant，回收权限使用命令revoke。

赋予权限基本语法： grant 权限/角色 to 用户

方案的使用使用：

　　　　　　　　让用户feng 可以查询scott 用户的emp表

操作步骤

　　　　　　1. 登录scott用户　　　　conn scott/123456

　　　　　　2. soctt赋予用户feng select权限 grant select|insert | delete | update | all(所有权限) on emp to feng;

　　　　　　3. 查询 scott用户的 emp表 conn feng/feng; select * from scott.emp;

综合案例：

创建一个用户 feng, 然后给他分配两个角色，可以让用户登录，创建表，操作（增删改查）自己创建的表，收回角色，最后删除用户。

1. 使用system 常见 feng;

sql > con system/123456

sql > create user feng identified by 123456

2. 分配角色

sql > grant connect to feng;

sql > grant resource to feng;

3. 用户登录,并且修改密码

sql> con feng/123456

sql> password

4.创建 abcd表，插入数据，删除一行

create table abcd (id int, name varchar(60));

insert into abcd values(2,'b');

delete from abcd where id = 1；

5. 使用system用户删除用户角色

sql> conn system/123456

sql > remove grant from feng;

sql> remove resource from feng;

6. 删除用户

sql > drop user feng CASCADE.

当我们删除一个用户的时候，如果这个用户自己已经创建过数据对象，那么在删除改用户的时候，需要添加CASCADE。

表示把该用户删除的同时，把用户创建的数据对象一起删除。

用户创建练习：

1 创建用户 tea ,stu, 并给这两个用户 resource ,connect 角色

conn system/123456

create user tea identified by 123456

grant connect to tea

gran resource to tea

create user stu identified by 123456

grant connect to stu

gran resource to stu

2. 使用scott 用户把对emp表的select 权限给tea

conn scott/123456

grant select on emp to tea

使用 tea查询soctt的emp表

conn scott/123456

grant select on emp to tea

使用scott 用户把对emp表的所有权限赋予给tea

conn tea/123456

select * from scott.emp;

使用tea更新/删除/插入 scott的emp表

conn scott/123456

grant all on emp to tea

conn scott/123456

grant all on emp to tea

conn tea/123456

SQL> insert into scott.emp values(8000,'TEA','IT',8000,'29-8月-17',8000,8000,10);

SQL> delete from scott.emp where JOB='IT';

　使用 soctt收回stu的权限

conn scott/12345

3. 想办法将让tea 把自己拥有的对scott.emp的权限转给stu；

使用stu 查询 scott用户的emp表

使用tea收回给stu 的权限

系统权限 with admin option 表示得到权限的用户，可以把权限继续分配。

对象权限 with grant option

使用sys 登录

sqlplus / as sysdba;caigoupeixun

show user;

连接 scott用户，并且查看改用户下有几张表。

conn scott/123456

select * from tab;

连接操作符：

SQL> select 'drop table '|| tname ||';' from tab;

'DROPTABLE'||TNAME||';'
------------------------------------------------------------
drop table BONUS;
drop table DEPT;
drop table EMP;
drop table SALGRADE;

select ename || ' is ' || JOB from emp;

ENAME||'IS'||JOB
--------------------------
SMITH is CLERK
ALLEN is SALESMAN
WARD is SALESMAN
JONES is MANAGER
MARTIN is SALESMAN
BLAKE is MANAGER
CLARK is MANAGER
SCOTT is ANALYST
KING is PRESIDENT
TURNER is SALESMAN

去除重复的行

SQL> select distinct deptno from emp;

SQL> select distinct deptno,job from emp;

DEPTNO JOB
---------- ------------------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
10 IT

服务器重启后，默认oracle 是关闭的，

1. 使用 sys用户连接到数据库下

conn / as sysdba

2. sql> startup #打开数据库

select * from emp where comm is null;