你的位置:首页 > 数据库

[数据库][转载]Oracle基础知识


一、oracle安装过程略

二、sys用户和system用户

(1)sys用户是超级用户,具有最高权限,具有sysdba角色,有create database的权限

默认密码是change_oninstall

(2)system用户是管理操作员,权限也很大,具有sysoper角色,但没有create database的权限,默认密码为manager

(3)一般对数据库的维护,使用system用户登录就足够了

三、oracle的启动

所谓的启动是指启动oracle的实例,即OracleServiceORCL,在使用java连接数据库时必须要启动监听器,否则就不能够使用JDBC。

四、oracle的数据对象

在oracle中表、视图、存储过程、触发器在oracle中被称为数据对象

五、oracle管理工具

sqlplus是oracle自带的工具软件,主要用来执行sql语句

 

 

 

一、以下是sqlplus常用命令

(1)conn[ect]

用法:conn 用户名/密码@网络服务名[as sysbda/sysoper]

当用户是特权用户时必须带上as sysbda/sysoper

例如:conn system/manager

(2)disc[onnect]

断开连接

(3)passw[ord]

修改密码,当想要修改其他用户密码用sys或system登录

(4)show user

显示当前用户

(5)exit

断开连接,并退出sqlplus

二、文件操作命令

(1)start、@

说明:运行sql脚本

如:在d: 下有a.sql这个文件,运行下面的命令即可执行a.sql中的内容

sql>@ d:a.sql 或者

sql>start d:a.sql

(2)edit

编辑指定的sql脚本

sql>edit d:a.sql

(3)spool

该命令可以将sqlplus屏幕上的内容输出到制定的文件中去

sql>spool d:b.sql

sql>select * from emp;

spl>spool off

说明:spool命令将select * from emp;的查询结果输出到指定位置的文件中 然后spool off类似于IO的开启/关闭

三、交互式命令

(1)&

可以替代变量,而该变量在执行时需要用户输入

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

oracle会提示用户输入值

四、显示和设置环境变量

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

(1)linesize

设置显示行的宽,默认是80个自己字符

sql>show linesize

sql>set linesize 120

(2)pagesize

设置每页显示的行数默认是14,用法同linesize

其他环境参数的使用也是大同小异

 

 

一、Oracle用户管理

创建用户,需要DBA权限

命令:create user 【用户名】identified by 【密码】

修改密码

命令:password 【用户名】 ( 在用户已经连接的情况下 )

注意:在给其他用户修改密码时  需要具有DBA的权限或拥有alter user的系统权限

命令:alter user 【用户名】 identified by 【新密码】

删除用户

一般以DBA身份去删除用户

如果要删除的用户,已经创建了表,要在删除时加上一个参数 cascade

命令:drop user 【用户名】 [cascade]

二、权限和角色

刚刚创建完的新用户是没有任何权限的,甚至连登录数据库的权限都没有。这是时候使用conn 【用户名】/【密码】会提示没有权限。

在新建一个用户之后还要对这个用户进行授权操作。当然了,要使用有能力授权的用户,如sys、system

权限包含系统权限和对象权限

系统权限:用户对数据库的相关权限

对象权限:用户对其他用户的数据对象操作的权限

角色

角色是指由系统权限集合。通常给某个用户授予权限时如果没有角色存在的话,那么需要一条一条的操作,角色的存在

就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个connect(7种权限)、dba、resource(在任何表空间建表)。

这里只是简单的提一下,在以后会作为一个专题进行研究。

使用grant命令给用户分配权限

grant 【权限名】 to 【用户名】

分配角色

grant 【角色名】 to 【用户名】

收回权限

revoke 【权限名】 from 【用户名】

举个例子来说明:

1、创建用户

create user stu identified by stu;

2、使stu能够被连接

grant create session to stu;

3、让stu能够在任何表空间下建表

grant resource to stu

3、创建一个简单的表

create table users(name varchar2(10),age number(2));

4、插入几条数据

insert into users values('houjinxin',22);

5、登录到scott给stu授权让stu可以查看scott下的emp表

grant select on emp to stu;

6、登录到stu下查看emp表

select * from scott.emp;

如果这时想要更新scott.emp中的数据

update scott.emp set ename='ok2' where ename='ok';

会提示ORA-01031: 权限不足 。因为scott只给了stu查看的权利,如果仍然想更新,要到scott下进行授权

7、登录到system下收回resource角色

revoke resource from stu;

8、登录scott下收回select 权限

revoke select on emp from stu;

这是stu就不能再查询scott.emp的数据了

权限的传递

当希望stu用户可以去查询scott的emp表时,还希望stu能够把这个权限继续传给其他用户时

如果要传递的是对象权限,就加入with grant option

grant select on emp to stu with grant option

如果是系统权限:就加上with admin option

grant connect to stu with admin option

当system给stu授权时,会给stu给其他用户授权的能力

做个实验来验证下

1、登录到system用户下,重新建立两个用户

create user hou identified by hou;

create user jin identified by jin;

并为hou分配connect角色

grant connect to hou with admin option;

2、登录到scott下个hou授权

grant select on emp to hou with grant option;

3、登录到hou下开始对jin授权

grant select on scott.emp to jin;

grant connect to jin;

4、登录到jin下查询scott.emp

select * from scott.emp;

到目前位置都正常,问题来了!

如果system收回分配给hou的权限,那么jin的权限会不会也被一起收回,继续实验。

5、登录到scott下收回hou的权限

revoke select on emp from hou;

revoke connect from hou;

6、登录到jin下看现象

发现仍然能够登录到jin上这说明connect角色并未被收回

而当查询scott.emp时却提示ORA-00942: 表或视图不存在

这说明系统权限和对象权限是不同的。对于系统权限,hou分配给jin之后不再收回,对象权限却随着hou的权限被收回也被同时收回了

 

 

使用profile 管理用户口令  

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

(1)帐号锁定  

指定登录时最多可以输入密码的次数,也可以指定用户锁定的时间,以天为单位。一般用dba的身份去执行命令例如:指定stu最多只能尝试三次登录,锁定时间为2天

sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

sql>alter user stu profile lock_account;  

(2)给账户解锁  

sql>alter user stu account unlock;

(3)终止口令  

为了让用户定期修改密码,可以使用终止口令的指令完成,同样这个命令也要dba身份来操作  给stu创建一个profile文件,要求该用户每隔10天要修改登录密码,宽限期2天

 sql>create profile stu limit password_life_time 10 password_grace_time 2;

 sql>alter user stu profile stu;

 解锁方式同上

 (4)口令历史  

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

  例如:

 sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10  

sql>alter user stu profile password_history;

password_reuse_time 10 表示10天后口令可重复使用

 (5)删除profile  

 drop profile password_history [cascade]

 cascade表示如果已经将profile分配给某个用户时,仍要删除profile,就要加上cascade

 

 

表的管理 

一、表名和列的命名规范

1.必须以字母开头

2.长度不能超过30个字符

3.不能使用oracle的保留字

4.只能使用如下字符串,A-->Z,a-->z,0-->9,$,#等

二、oracle支持的数据类型

1、字符型

char 定长,最长2000字符

例如:char(10) 存储内容为“小韩”时,前4个字符放‘小韩’,后六位由空格补齐

优点是:效率高,查询速率快。如身份证的字段可以设置成char(18)。

varchar2 变长 最大4000字符(oracle推荐使用)

varchar2(10) 存储内容为“小韩”时 oracle分配4个字符

clob(character large object) 字符型大对象

最大4G

2、数字类型

number范围-10的38次方到10的38次方

可以是整数,也可以是小数

number(5,2)表示一个小数有5位有效数字,2位是小数

例如:定义一个范围在-999.99-999.99的数字可以用number(5,2)

定义一个范围在-99999-99999可以用number(5)

3、日期类型

date 包含年月日和时分秒

timestamp oracle对date类型的扩展

4、图片类型

blob 二进制数据,可以存放图片,音频,视频最大4G

这个类型允许我们将大文件存储进数据库,但是一般在数据库里,存放的应该是这些文件的路径,如果对安全性有要求,可以将文件放入数据库

三、建表语句

1)建表

sql>create table student(--表名

Idnumber(4), --学号

Namevarchar(20), --姓名

Sex char(2),--性别

birthday date);--出生日期

上面的语句足以建立一个简单的学生表

2)向已经建立的表中添加字段

sql>alter table student add(ClassId number(2));

3)修改字段的长度

sql>alter table studentmodify(Name varchar2(50) );

4)修改字段的类型/或名字(不能有数据)

sql>alter table student modify(Name char(20));

sql>alter table student rename Name to Sname;

5)删除一个字段(慎重使用)

alter table student drop column Sex;

6)修改表的名字

sql>rename student to stu;

7)删除表

drop table student;

8)查看表结构

desc student;

四、操作表

1、添加数据,所有字段必须都插入

insert into student values(1,'张三','男','01-5月-05');

不要以为这里写错了,oracle中默认的日期格式‘DD-MON-YY’ (日-月-年)

想要修改日期的默认格式可以这样做

sql>alter session set nls_date_format='YYYY-MM-DD';

修改以后就可以用我们熟悉的格式来添加类型

insert into student values(1,'张三','男','2000-08-31');

但是这里的修改只是临时成立的,要想永久改变日期输入格式是需要改注册表的,还有一个方法是使用函数,暂且略过

2、插入部分字段,前提是未插入的字段允许为null

insert into student(Id,Name) values(1,'张三');

3、插入空值

insert into student(Id,Name,Sex) values('1',null,null);

4、查询Name为空的一条记录

按照正常的逻辑,许多人会这样做

select * from studentwhere Name=null;

但是这样的结果是什么都查不到,正确的方法如下

select * from student where Name is null;

查询所有非空的就在is后面加上not

5、修改一个字段

update student set sex=‘女’ where Id=‘1’;

6、修改多个字段

update student set sex=‘男’,Name='赵四' whereId=‘1’;

7、修改含有null值的字段

update student set Name=‘张三’ where Name is null;

8、删除数据(三中方式)

1)删除一条记录

delete from student where Id=‘1’;

2)删除所有记录,表结构还在,会记录日志,这种删除是可以恢复的,速度会稍慢

delete from student;

3)删除表的结构和数据

drop table student ;

4)删除所有记录,表结构还在,不记录日记,所有这种删除无法找回数据,但是速度很快

truncate table student;

9、恢复数据

用delete from student 时数据可恢

1)首先要设置一个保存点

savepoint sp;--sp是保存点名称,可以随意起名,作用是将数据保存在日志中

2)删除数据

delete from student ;

3)查询验证数据是否被删掉

select * from student;

结果是肯定的,没有数据

4)回滚数据

rollback to sp;

5)在查询验证数据回滚是否成功

select * from student;

结果还是肯定的数据回来了!

当然可以设置多个保存点,但是如果不做处理,新的保存点会默认覆盖前一个保存点

10、取消重复行

select distinct deptno,job from emp;

在查询时select后面加上distinct即可将重复数据略去

 

 

Oracle的基本查询

首先,介绍PL/SQL软件中两个命令

1.清屏命令

  clear

2.关闭/打开显示操作时间命令

    set timing off/on

其次,是两个sql技巧

1.快速向数据库中插入大量数据

insert into users(userid,username,userpass)

       select * from user;

使用这个语句的前提是表中至少要有一条数据

2.查询所有记录数

select count(*) from user;

需要注意的是在写SQL 语句时,要注意大小写问题

Orace的字段不区分大小写,实体却区分大小写

一、使用算数表达式

? 显示每个雇员的年工资

可以使用列的别名

select ename "姓名",sal*12 as "年收入" from emp;

这里的中文最好用引号引上,尽量不要用中文

?如果计算表达式中有一个null值那么计算结果就为null,如何处理null值?

使用nvl函数处理

select sal*13+nvl(comm,0) "年工资" ,ename from emp;

nvl(comm,0)的意思是如果comm为null,那么按0计算,不是0按本身计算

?如何连接字符串

用"||"

select ename || ' is a ' || job from emp;

二、使用where子句

?如何显示工资高于3000的员工

select ename,sal from emp where sal >3000;

?如何查找1982.1.1后入职的员工

select ename from emp where hiredate>'1-1月-1982';

?查找工资在2000-2500之间的员工

select ename from emp where sal>=2000 and sal <=2500;

三、如何使用like操作符

%: 表示0到多个字符

_:表示任意单个字符

?如何显示首字母为s的员工

select ename from emp where ename like ‘s%’;

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

select ename, sal from emp where ename like '__O%';

四、在where条件中使用in

?如何显示empno为123,456,234的雇员情况

select * from emp where empno in(123,234,456);

这种查询效率很高

五、使用is null操作符

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

select * from emp where mgr is null;

六、使用逻辑操作符号

?查询工资高于500或是岗位为MANAGER的雇员,同时还要满足他们的姓名首写字母为大写的T

select * from emp where (sal>500 or job='MANAGER') and ename like 'T%';

这里的括号不可以忘记,否则条件就变了,因为and的优先级高于or

七、使用order by 子句

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

select ename from emp order by sal desc;

desc 为逆序 asc为顺序(默认)

?按照部门号升序而雇员工资降序排列

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

八、使用列的别名排序

select ename ,sal*12 "年薪" from emp order by "年薪" asc;