掌握Oracle数据库常用命令

一、DDL操作：数据定义语言

-- 创建表空间

create tablespace 表空间名称

datafile '文件路径'

size 10m; -- 文件大小

-- 删除表空间

drop tablespace 表空间名称；

-- 创建用户

create user 用户名称 identified by 用户密码

default tablespace 表空间名称

tempporary tablespace temp； //临时的表空间

-- system用户进行用户授权

grant connect，resource to 用户名；

-- 修改用户口令

alter user 用户名 identified by 新的口令；

-- 切换用户

conn 用户名/密码 as 用户权限（sysdba：实际切换为sys）；

-- 撤销权限

revoke 权限|角色 from 用户名；

-- 创建表

create table 表名（ 列名 数据类型）；

-- 删除表中的记录而不删除表结构

truncate table 表名；

-- 创建序列

create sequence 序列名称

start with integer //指定要生成的第一个序列号

increment by integer //用于指定序列号之间的间隔，其默认值为1

maxvalue integer //指定序列可以生成的最大值

minvalue integer //指定序列的最小值

cycle|nocycle // cycle：继续从头开始生成值、nocycle：不能再继续生成值

cache integer|nocache; //cache:可以预先分配一组序号、nocache：不分配

例 ：

CREATE SEQUENCE SEQ_DEMO

INCREMENT BY 1

START WITH 1

NOMAXvalue

NOCYCLE

NOCACHE;

-- 访问序列

序列名.nextval:增加序列值，并返回

序列名.currval:返回当前的值

-- 更改序列

alter sequence 序列名

increment by integer

maxvalue integer|nomaxvalue

minvalue integer|nominvalue

cycle|nocycle

cache integer|nocache;

-- 删除序列

drop sequence 序列名；

-- 使用序列

currval 表示序列的当前值，新序列必须使用一次nextval 才能获取到值，否则会报错

nextval 表示序列的下一个值。新序列首次使用时获取的是该序列的初始值，从第二次使用时开始按照设置的步进递增

查询序列的值：select seq_name.[currval,nextval] from dual;

SQL语句中使用：insert into table (id) values (seq_name.nextval)

-- 创建同义词（私有同义词）

create or replace synonym 别名 for 对象.列名；

-- 创建同义词（公有同义词）

create or replace public synonym 别名 for 对象.列名；

-- 删除同义词

drop [public] synonym 同义词别名；

-- 创建索引

create [unique:唯一,bitmap:位图] index 索引名称 on 表名 [reverse]；

-- 删除索引

drop index 索引名称；

-- 显示错误

show errors;

二、DML操作：数据操作语言

-- 选择无重复的行

distinct

例：select distinct 列名 from 表名；

-- 带条件和排序的select命令

order by 列名 【asc：升序、desc：降序】

例：select 列名 from 表名 where order by 列名 asc，列名 desc；

-- 使用列别名

as --->别名中有特殊字符的情况，需要加上双引号“”

例：select 列名 as “别名”from 表名；

-- 利用现有的表创建新表

例：create table 新表 AS select 列名 from 旧表 [where....];

例：只创建新表：create table 新表 as select * from 旧表名 where 1=2；

-- 查看表中行数

例：select count(*) form 表名；-- 效率底 、select count(1) form 表名；-- 效率高

-- || 操作符为连接操作符

例：select stuName，stuAge

from stuInfo

group by stuName,stuAge

having（count(stuName || stuAge) < 2）;

-- oracle数据库分页：

三层嵌套：

最内层：用于过滤及排序

中间层：采用rownum指明上限

最外层：采用rownum的别名指明下限

例：1.select * from (select t.*,rownum rn from（select * from emp order by empId）t where rownum<=6) where rn>=3;

2.select t.* from (select stu.*,rownum rn from student where rownum<=6) t where rn>=3;

3.select t.* from (select stu.*,rownum rn from student) t where rn between 3 and 6;

4.select * from (select * from emp where rownum<=6) minus (select * from emp where rownum<=3); -- 效率低，不稳定

-- 批量添加数据

insert into 表名(列名，列名,...)

select 列名,列名,...

union

select 列名,列名,...;

-- 常量

select 常量 form dual；

三、TCL操作：事务控制语言

-- 事务提交

commit；

-- 事务回滚

rollback；

-- 存储点、保存点

savepoint 存储点名称；

-- 回滚到存储点

rollback to 存储点名称；

四、DCL：数据控制语言

-- 添加外键约束

alter table 表名 add contraint fk_外键名称 foreign key（列名） references 主表（列名）；

-- 添加列

alter table 表名 add (列名 数据类型 ，列名 数据类型);

五、SQL操作符

算术操作符：

+、-、*、/

比较操作符：

=、!=、<、>、<=、>=、BETWEEN...AND、IN、LIKE、IS NULL

逻辑操作符：

AND（与）、OR（或）和NOT（非）

集合操作符：

UNION（联合）、

UNION ALL（联合所有）、

INTERSECT（交集）、

MINUS（减集）

连接操作符：

||：用于将两个或多个字符串合并成一个字符串，或者将一个字符串与一个数值合并在一起。

六、SQL函数

转换函数：

to_char():转换成字符串类型

to_date():转换成日期类型

to_number():转换成数值类型

其他函数：

NVL（exp1，exp2）:exp1的值为null，返回exp2的值，否则exp1的值

NVL2（exp1,exp2,exp3）:exp1的值为null，返回exp3的值，否则exp2的值

DECODE（value,if1,then1,if2,then2,...,else）:value的值为if1，返回then1

分析函数：

函数名([参数]) OVER([分区子句][排序子句])

函数：rank：返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。、

dense_rank：返回一个唯一的值，当遇到相同数据时，所有相同数据的排名都是一样的。、

row_number：返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序一次递增。

七、PL/SQL

1.结构：

declare

声明变量、常量等等

begin

执行部分

exception

异常处理部分

end;

2.运算符和表达式

关系运算符： =：等于、<>，!=,~=,^=：不等于、<：小于、>:大于、<=:小于等于、>=:大于等于

一般运算符： +：加号、-：减号、*：乘号、/：除号、:=：赋值运算符、=>：关系号、..：范围运算符、||：字符连接符

逻辑运算符： is null：是空值、between and ：介于两者之间、in：在一列值中间、or ：逻辑或、 and：逻辑与、not：取反

3.常量和变量的声明

变量：

变量名 类型 := 初始值；

常量：

常量名 类型 := 初始值;

4.给变量赋值：

1.使用select into

select 列名 into 变量名 from 表名 where 条件；

2.使用:=赋值运算符

变量名 := 值；

5.数据类型

char、varchar2、binary_integer、number(p,s) long、date、boolean

lob：bfile、blob、clob、nclob

6.record 记录：相当与Java中的类

declare

type 记录名 is record(属性名 类型);

%type：数量可以不同

%rowtype：类型结构相同

6. 输出语句

set serveroutput on;

dbms_output.put_line();

7.PL/SQL控制语句

条件控制：

1）if 条件表达式 then

sql语句

else

sql语句

end if；

2）if 条件表达式 then

sql语句

elsif 条件表达式 then //elsif 没有e，和Java中不太一样

sql语句

else

sql语句

end if;

3) case 条件表达式

when 表达式结果 then

sql语句

...

else

sql语句

end case；

4）case

when 条件表达式 then

sql语句

...

else

sql语句

end case;

循环语句:

1) loop

sql语句

exit when 条件语句

end loop；

2）while 条件语句 loop

sql语句

end loop；

3）for 循环计数器（变量） in [reverse:反] 下限(最小值) ... 上限(最大值) loop

SQL语句

end loop；

8.游标 ****重点***** ： Java中resultSet底层就是游标实现的

游标分类：

一、静态游标：是再编译时知道明确的select语句的游标

1）隐式游标

2）显式游标

a.声明游标

cursor 游标名称 (参数 类型) is 查询语句；

b.打开游标

open 游标名称(参数)；

c.提取游标 （作用：1.进行游标的下一步指向，2.如果查询有结果，则游标 into 赋值给变量名，否则，游标保持原来的状态）

fetch 游标名称(参数) into 变量名；

d.关闭游标

close 游标名称;

显式游标属性： %found ：发现值，返回true、%notfound：没发现值，返回true、

%rowcount：返回DML语句影响的行数、%isopen：返回游标是否已打开

二、动态游标：

1）强类型动态游标：在游标使用之前，虽未指定游标的查询定义，但是游标的类型已经确定

type 游标类型 is ref cursor return 记录类型

例：

declare

type ref_deptrow is ref cursor return dept%rowtype; /*强类型动态游标声明*/

2）弱类型动态游标：在游标使用之前，游标的类型无法具体确定，要等到程序执行时，才能确定游标的类型。弱类型动态游标没有返回值。

type 游标类型 is ref cursor

例：

declare

type ref_deptrow is ref cursor; /*弱类型动态游标声明*/

触发器：级联删除（慎用）、自动的日志添加、模拟自增列的值

含义：当特定时间出现时自动执行的存储过程

语法：create or replace trigger 触发器名称

after|before|instead of

[insert] [[or] [update] [of column_list]]

[[or] delete]

on table_or_view_name

[referenceing{old [as] old/new [as] new}]

[for each row]

[when (condition)]

pl/sql_block;

例:

create or replace trigger trigger_dept_insert

before insert

on dept

for each row

begin

select seq_dept.nextval into NEW.deptno from dual;

end;

/