ORACLE常用命令学习

二、sys用户和system用户

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

默认密码是manager

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

(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>editd:\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>showlinesize

sql>setlinesize 120

(2)pagesize

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

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

 

 

一、Oracle用户管理

创建用户,需要DBA权限

命令： create user 【用户名】identifiedby 【密码】

修改密码

命令： 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 grantoption

grant select on emp tostu with grant option

如果是 系统权限：就加上with adminoption

grant connect to stuwith admin option

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

做个实验来验证下

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

create user hou identified by hou;

create user jin identified by jin;

并为hou分配connect角色

grant connect to houwith admin option;

2、登录到scott下个hou授权

grant select on emp tohou with grant option;

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

grant select onscott.emp to jin;

grant connect to jin;

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

select * fromscott.emp;

到目前位置都正常，问题来了！

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

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

revoke select on empfrom hou;

revoke connect fromhou;

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>createtable student(--表名

Idnumber(4), --学号

Namevarchar(20), --姓名

Sex char(2),--性别

birthday date);--出生日期

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

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

sql>altertable student add(ClassId number(2));

3)修改字段的长度

sql>alter table studentmodify(Namevarchar2(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）首先要设置一个保存点

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

2）删除数据

delete from student ;

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

select * from student；

结果是肯定的，没有数据

4)回滚数据

rollbackto sp；

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

select * from student；

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

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

10、取消重复行

select distinct deptno,job from emp;

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

 

 

Oracle的基本查询

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

1.清屏命令

  clear

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

    set timingoff/on

其次,是两个sql技巧

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

insert intousers(userid,username,userpass)

      select * from user;

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

2.查询所有记录数

select count(*) fromuser;

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

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

一、使用算数表达式

? 显示每个雇员的年工资

可以使用列的别名

select ename "姓名",sal*12as "年收入" 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 fromemp where sal >3000;

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

select ename from empwhere hiredate>'1-1月-1982';

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

select ename from empwhere sal>=2000 and sal <=2500;

三、如何使用like操作符

%：表示0到多个字符

_：表示任意单个字符

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

select ename from empwhere ename like ‘s%’;

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

select ename, sal fromemp where ename like '__O%';

四、在where条件中使用in

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

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

这种查询效率很高

五、使用is null操作符

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

select * from empwhere mgr is null;

六、使用逻辑操作符号

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

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

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

七、使用order by 子句

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

select ename from emporder by sal desc;

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

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

select * from emporder by deptno asc,sal desc;

八、使用列的别名排序

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

 

Oracle的复杂查询

在实际应用中经常需要执行复杂的数据统计,经常需要现实多张表的数据

所以经常要用到数据分组函数如 max(),min(),avg(),sum(),count()等

一、分组函数

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

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

?如何查询最高,最低工资的人是谁

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

这里利用了子查询

?请显示工资最高的员工的名字,工作岗位

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

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

select * from empwhere sal> (select avg(sal) from emp) select avg(sal) from emp ;

如果这里不是*号而是ename,sal等字段与分组函数那么在语句的最后要加上 group byename,sal..(与select后的字段一致)

二、group by 和having 子句

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

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

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

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

分组字段依据必须出现在查询结果中，否则结果可读性太差

？如何显示每个部门的每种岗位的平均工资和最低工资

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

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

select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

三、对分组函数的总结

1.分组函数只能出现在选择列表,having,group by,order by子句中

2.如果在select语句中同时包含有group by,having, order by,那么顺序为group by,having,order by

3.在选择列中如果有列,表达式,和分组函数,那么这些列表达式必须有一个出现在group by 子句中,否则出错 如:selectdeptno,avg(sal),max(sal),max(sal) from emp group by deptno havingavg(sal)>2000; 这里的deptno就一定要出现在group by中

四、多表查询

基于两个或两个以上的表或是视图的查询

查单表满足不了要求如部门和员工的关系

?显示雇员名,雇员工资及所在部门的名称

select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno;

如果不加where子句,就会产生笛卡尔集

所谓笛卡尔集,就是不加筛选,将所有的都查询出来

两张表关联,至少需要一个条件排除笛卡尔集

三张表关联,至少需要二个条件排除笛卡尔集

以此类推:多表查询中,判断条件至少是表的个数-1

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

select b.dname,a.ename,a.sal from emp a,dept b where a.deptno=b.deptno   and b.deptno=10;

?显示各个员工的姓名,工资及工资的级别

select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between losal andhisal;

这里用到了between ... and 子句. 表示在losal和hisal之间(很好理解)

?显示雇员名,雇员工资及所在部门的名字,并部门排序

select a.ename,a.sal,b.dname,b.deptno   

from emp a,dept b

wherea.deptno=b.deptno  

order by b.deptno;

五、自连接

自连接:指在同一张表内的连接查询

?显示某个员工的上级领导的姓名

select worker.ename,boss.ename

  from empworker,emp boss

where worker.mgr=boss.empno   

and worker.ename='FORD';

根据FORD的名字找到ford的mgr编号再根据这个编号找到boss的empno,最后显示出来

六、子查询

子查询:指嵌入在其他sql语句中的select语句,也叫做嵌套查询

(1)单行子查询:是指只返回一行数据的子查询语句

?如何显示与SMITH同一部门的所有员工 分两步:

1.查出SMITH所在部门

2.根据部门查出所有员工

1.select deptno fromemp where ename='SMITH';

2.select ename fromemp where deptno=(select deptno from emp where ename='SMITH');

(2)多行子查询:返回多行数据的子查询

?如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号

1.首先查出部门10的工作种类

select job from empwhere deptno=10;

发现有重复结果.所以在job前加上distinct

select distinct jobfrom emp where deptno=10;

2.根据工作的种类查询

select ename,job,sal,deptno   

from emp where job in

(select distinct jobfrom emp where deptno=10);

注意这里job之后用的是"in"而非"=".

(3)all操作符

?如何显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号

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

可以使用max方法

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

max方法的效率较高,原因是使用all操作符时,要和子查询所得结果逐一比较而使用max只需和sal中的最大值比较,减少了比较的次数,缩短了时间. 数据量较大是会比较明显,数据量较小基本看不出来

(4)any操作符

?如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号

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

可以使用min方法

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

原理同上

(5)多列子查询

多列子查询:指子查询返回多列数据 ?如何查询与SMITH部门和岗位完全相同的所有雇员

1.查询出SMITH的部门号,岗位

select deptno,job fromemp where ename='SMITH';

2.显示结果

select * from empwhere (deptno,job)=  

(select deptno,job   

from emp   

where ename='SMITH'

);

(6)在from子句中使用子查询

?如何显示高于自己部门的平均工资员工信息

1.查询各个部门的平均工资和部门号

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

这里group by deptno是必须要有的.用来对分组结果进行统计

2.把上面的查询看作是一张子表

select a.ename,a.sal,a.deptno,b.avgsal  

from emp a,  (select avg(sal) avgsal,deptno from emp group by deptno) b

where a.deptno=b.deptno and a.sal>b.avgsal;

这个比较难想到,将这两个表做关联查询 当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图挡在from子句中使用子查询时,必须为子查询指定别名

 

 

 

一、oracle的分页

oracle的分页一共有三中方式（这里只有一种）

1.rownum 分页

select * from (selecta.*,rownum rn from    (select * from emp) a   whererownum<=15 ) where rn>10;

这条语句是用来将11-15条记录提取出来,拆分这条语句

1.将所有想要的结果查询出来

select * from emp;

2.将上一步的结果作为一个视图,给每一条记录加上一个rn编号并将前15条记录查询出来.其中rownum为oracle的关键字,

且在第一次在查询字段中使用rownum时,如果有条件限制在where子句中也要用rownum,不可以用rn

select a.*,rownum rnfrom (select * from emp) a where rownum<=15;

3.将前15条数据作为一个视图,提取出11-15条

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

在java程序中只需要替换15和10这两个数字就可以实现分页了

二、用查询结果创建新表

这个命令是一种快捷的建表方法

create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

三、合并查询

为了合并多个select语句的结果，可以使用集合操作符号, union,union all,intersect(交集),minus(差集)

(1)union

该操作符,用于取得两个结果集的并集,使用时自动去掉相同部分结果集

select ename,sal,jobfrom emp where job='MANAGER' union select ename,sal,job fromemp where sal>2500;

(2)union all

与union相似,但是不取消重复行，而且不排序

(3)intersect

取交集

(4)minus

取差集 只会显示存在第一个集合中,而不存在在第二个集合中的数据

集合操作要比and，or效率高很多

四、操作数据

使用特定的格式插入日期值

(1)使用to_date函数

？如何插入带有日期的表,并按照年月日格式插入

insert into empvalues(9994,'hou','PRESIDENT',null,to_date('1990-10-10','YYYY-MM-DD'),800,1000,10);

使用to_date函数可以插入任意形式的日期

(2)使用子查询插入数据一条insert语句可以插入大量的数据，当处理行迁移或者装载外部表的数据到数据库时, 可以使用子查询插入数据

insert into mytable(id,name,deptno) select empno,ename,deptno from emp where deptno=10;

(3)使用子查询更新数据？希望员工scott的岗位、工资、补助与SMITH一样

update emp set(job,sal,comm)= (select job,sal,comm from emp where ename='SMITH' ) whereename='SCOTT';ename的值要用大写,oracle对值的大小写是敏感的