掌握Oracle数据库常用命令!!!

分手后的思念是犯贱 2024-02-19 17:26 62阅读 0赞

一、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;  
/