侧边栏壁纸
博主头像
落叶人生博主等级

走进秋风,寻找秋天的落叶

  • 累计撰写 144676 篇文章
  • 累计创建 28 个标签
  • 累计收到 9 条评论
标签搜索

目 录CONTENT

文章目录

Oracle语法

2024-02-21 星期三 / 0 评论 / 0 点赞 / 3 阅读 / 18423 字

转:https://blog.csdn.net/qq_36537094/article/details/79619141 一、.DDL语言(data definition language)数据定义语

转:https://blog.csdn.net/qq_36537094/article/details/79619141

一、.DDL语言(data definition language)数据定义语言

1.建表 create table

2.操作表 alter table

a.添加字段:alter table 表名 add 要添加的字段名;

b.删除字段:alter table 表名 drop column 要删除的字段名;

c.修改字段:alter table 表名 rename 原字段名 to 新字段名;

d.修改字段类型:alter table 表名 modify 字段名 字段类型;

3.删除表 drop table

二、DML语言(Data Manipulation Language)

1.新增记录:insert into 表名(字段名...) values(信息...);

2.修改记录:update 表名 set 要修改的字段记录 where 判断条件;

3.删除记录:delete from 表名 where 判断条件;

4.查询记录:select 查询的字段 from 表名 where 判断条件;

三、约束

约束:保证数据库中数据的安全性

1.主键约束(唯一性、非空性):primary key

语法:alter table 表名 add constraint 约束名 primary key(约束的字段名)

2.唯一约束(唯一性,可以为空):unique

语法:alter table 表名 add constraint 约束名 unique(约束的字段名)

3.检查约束:check

语法:alter table 表名 add constraint 约束名 check(约束的字段名)

4.外键约束:foreign key

语法:alter table 表名 add constraint 约束名 foreign key(约束的字段名) references 主表 名(关联的字段名) 【on delete cascade(删除主表时,从表也删除)/on delete null(删除主表时,从表的关联设为空)】

四、查询

1.单表查询

语法:select [distinct] 字段名 from 表名 where 条件 order by 排序字段 [asc/desc]

a. 去除重复行:distinct

b. 模糊查询:like '%*%'(包含*的)、like '*%'(以*开头的)、like '%*'(以*结尾的)

c.除此之外的:not

d.排序(必须在sql最后):order by 排序依据 【 desc:降序/asc:升序(默认)】

2.分组查询

语法:select 分组的字段 ,查询的字段 from 表名 group by 分组的字段 having 查询条件

分组函数:

a.函数:count(统计函数)、max(最大值)、min(最小值)、avg(平均值)、sum(求和)

数字函数:dual

a. abs(绝对值)、sqrt(求平方根)、power(求幂)、round(四舍五入)

字符函数:dual

a.连接两个字符串:concat(字符1,字符2)

b.替换字符串:replace(字符串,被替换的字符,替换的字符)

c.截取字符串:substr(字符串,从第几个开始,截取几个)

d.转大写 upper(被转的字符串)

e.转小写 lower(被转的字符串)

转换函数:dual

a.将字符转串换为日期 to_date(字符串,日期格式)

b.将日期转换为cahr数据类型

to_char(日期,'yyyy')得到日期的年份、 to_char(日期,'mm')得到日期的月份

to_char(日期,'dd')得到日期的日(几号)、 to_char(日期,'day')得到日期的星期

日期函数:dual

a.返回在日期基础上再加3个月后新的日期 select sysdate,add_months(sysdate,3) hz from dual;

b.返.回日期所在月份最后一天的日期 select sysdate,last_day(sysdate) hz from dual;

c.截取日期的指定部分 select extract (year/month/day from date '2001-2-16' ) from dual;

3.内联查询:将一个查出来的结果当做条件来查询

4.多表关联查询

连接运算:连接运算是由一个笛卡尔积运算和一个选取运算构成的。首先用笛卡尔积完成对两个数据集合的乘运算,然后对生成的结果集合进行选取运算,确保只把分别来自两个数据集合并且具有重叠部分(符合选取运算)的行合并在一起

select 查询的字段 from 表1 left/right/inner join 表2 on 条件

a. left[outer] join(左连接) :返回包括左表(集合)中的所有记录和右表(集合)中联结字段相等的记录

b. right[outer] join(右连接) :返回包括右表中的所有记录和左表中联结字段相等的记录

c. inner join(等值连接) :只返回两个表中联结字段相等的行

五、视图

1.视图是一种数据库对象,是从一个或者多个数据表或视图中导出的虚表,视图所对应的数据并不真正地存储在视图中, 而是存储在所引用的数据表中,视图的结构和数据是对数据表进行查询的结果。

2.作用:

a.简化数据操作:视图可以简化用户处理数据的方式

b.着重于特定数据:不必要的数据或敏感数据可以不出现在视图中。

c.视图提供了一个简单而有效的安全机制,可以定制不同用户对数据的访问权限。

d.提供向后兼容性:视图使用户能够在表的架构更改时为表创建向后兼容接口。

e.自定义数据:视图允许用户以不同方式查看数据。

f.导出和导入数据:可使用视图将数据导出到其他应用程序。

3.语法:

创建: create 【or replace】 view 视图名(视图字段名) as 结果集 【 with read only】

or replace :若所创建的试图已经存在,ORACLE自动重建该视图;

force :不管基表是否存在,ORACLE都会自动创建该视图;

noforce :只有基表都存在,ORACLE才会创建该视图;

with read only :该视图上不能进行任何DML操作;

with check option :插入或者修改的数据必须满足视图定义的约束

删除: drop view 视图名;

删除视图不影响基表中的数据。

六、索引

1.类似书的目录结构,索引直接指向包含所查询值的行的位置,减少磁盘I/O,与所索引的表是相互独立的物理结构,Oracle 自动使用并维护索引,插入、删除、更新表后,自动更新索引。

2.作用:提高数据库查询语句的速度

3.类型:

a.唯一索引:当某列的值都不相同时(当建立主键约束或唯一约束时会自动建立唯一索引)

语法:create unique index 索引名 on 表名(表字段)

b.组合索引:当两个或多个列一起出现在where条件中时,则在这些列中同时创建组合索引

语法:create unique index 索引名 on 表名(表字段)

c.反向索引:

语法:create unique index 索引名 on 表名(表字段) reverse

d.位图索引:列中有非常多的重复的值时候。例如某列保存了 “性别”信息。Where 条件中包含了 很多OR操作符。较少的update操作,因为要相应的跟新所有的bitmap

语法:create bitmap index 索引名 on 表名(字段名)

结构:位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。

优点:位图以一种压缩格式存放,因此占用的磁盘空间比标准索引要小得多

e.基于函数索引:在WHERE条件语句中包含函数或者表达式时

语法:create index 索引名 on 表名(function(字段名))

七、PLSQL

1.PSQL:过程化编程语言,用来编写包含SQL语句的程序。可以向数据库应用程序中加入业务逻辑处理功能。PL/SQL程序由块结构组成,每一个块都包含有PL/SQL和SQL语句。

2.作用:将一些数据的处理过程放在数据库中,避免因网络阻塞造成时间的消耗

只能使用DML语言,要用DDL语言必须以动态方式使用。

3.基本语法:

declare 声明部分

begin 执行语句部分

exception 处理异常部分

end; 结束

变量的几种赋值方式:

a. 变量名 变量类型 := 值

b. select into (select 字段 into 变量 from 表名 字段 where 条件)

c. 变量名 用户名.表名.字段名%type (变量类型为表中列的类型)

d.变量名 用户名.表名%rowtype (变量类型为表的类型)

判断语法:

a. if 条件 then 执行语句

elsif 条件 then 执行语句

...

else 执行语句

end if;

b. case

when 表达式1 then 执行语句

...

else 执行语句

end case

循环语法:

a. loop

执行语句

if 条件 then 执行语句

exit (条件满足,跳出循环)

end if

end loop;

b. loop

执行语句

exit when 条件 (条件满足退出循环)

end loop;

c. while 条件 loop

执行语句

end loop;

d. for 计数器 in 下限..上限 loop

执行语句

end loop;

e. for 计数器 in reverse 上限..下限 loop

执行语句

end loop;

4.异常:exception

raise 异常名 (跳到异常代码块)

自定义错误代码:

pragma exception_init(异常名,异常代码)

raise_application_error(异常代码,提示信息)

自定义错误代码范围(-20000,-20999)

5.游标:cursor

a.由于数据库中不予许出现数组、集合,所以出现了游标

b.游标分类:

静态游标:显式游标、隐式游标

动态游标

c.显式游标:

①定义游标 cursor name is select * from emp

【可以带参数: cursor name(变量 类型)

is select * from emp where 字段名 = 变量

打开时:open name(变量)】

定义一个变量 c_emp

②打开游标 open name

③循环游标

loop

fetch name into c_emp

exit when name%notfound【当游标循环到最后一条记录时 跳出循环】

执行语句

end loop

if name%isopen then 【判断游标是否打开,如果打开,关闭游标】

close name

end if

d.隐式游标:当使用(update,insert,delete)这些DML语言时,我们需要知道到底出现了

什么情况,(是找到了记录并修改成功,还是没有找到记录)【SQL是所有隐式游标的统一名字】

e.动态游标:

① type ref_cur is ref cursor 【定义一个名为ref_cur 的动态游标类型】

② c_emp ref_cur 【定义一个动态游标类型的变量】

③打开动态游标:

open var_cur for 'select * from t_emp where deptno = :v_deptno_var' using v_deptno

【:v_deptno_var 设置参数; -- 通过using 替换参数】

八、存储过程、函数、包

1.函数 创将语法:

create 【or replace】 procedure 过程名 (参数1 方式 数据类型,...) is/as 过程体

2.方式: in(输入)、out(输出)、in out(输入输出)

3.游标类型:sys_refcursor

 

4.函数:create 【or replace】function 函数名 (参数 类型...)return 类型 is/as 过程体

5.包:create【or replace】package 包名 is/as 包内部(变量名/函数名/存储过程名)

create【or replace】package body 包名 is/as 实现包内部(变量/函数/存储过程)

九、序列、触发器

1.序列:就是按照一定的规则,不断增长(减少)的一个数字,用于我们数据库里数据的唯一标识。

2.语法:create sequence *** ----创建一个名为***的序列号

increment by 1 -----规定序列每次递增多少

start with 1 -----规定序列从多少开始

maxvalue 200 -----规定序列最大值

nocycle/cycle -----规定当序列到达最大值时,是继续循环还是不循环

cache 10 -----规定数据库每次缓存多少个序列号

取序列:select 序列名.nextval from sys.dual;

修改序列:alter sequence 序列名

[increment by n] --修改序列值的增量

[maxvalue n / nomaxvalue] --设置或撤销序列的最大值或者最小值

[minvalue n / nominvalue];

3.触发器:就是相当于js里的监听器,用来监视数据库的各种操作,当某个操作时引发触发器的回应,做相对应的其他数据库操作。不能被外界调用。

4.触发器的类型:

a.DML触发器:当进行DML操作时,就可以创建一个触发器来响应

b.替代触发器:为了操作视图

c.系统触发器:对于系统时间进行触发,比如打开或者关闭数据库。

5.语法:创建一个触发器(规定在执行哪个动作之前或之后要做什么事)

create or replace trigger *** ----创建一个名为***的触发器

before/after ----在动作之前/动作之后

insert/update/delete ----哪个动作或那几个动作

on 表名 ----在哪个表上

declare ----做什么事

begin

end;

十、用户管理(DCL)

1.解锁: conn / as sysdba --用sys登录

alter user 用户名 account unlock --给用户解锁

锁定:alter user 用户名 account lock

2.创建用户:create user 用户名 identified by 密码

修改用户密码:alter user 用户名 identified by 密码

3.删除用户:drop user 用户名

drop user 用户名 cascade 删除用户和用户创建的对象

4.常用角色:connect【修改会话的权限】

resource【创建table、view、procedure、trigger的·权限】

dba【拥有所有的系统权限,包括配置表空间,赋给其他用户权限】

5.权限:

a. 系统权限

赋予:grant create session to 用户名 [with admin option(权限可传递)]

撤回:revoke create session from 用户名

查看当前用户: select * from session_privs

b. 对象权限

赋予:grant 权限 on 对象 to 用户名 [with grant option(权限可传递)]

撤回:revoke 权限 on 对象 from 用户名

查看当前用户:select * from user_tab_privs

6.角色:

a. 常用的系统角色:

connect角色:该角色具有创建会话,修改会话等权限

resource角色: 具有创建表格(create table),创建视图(create view),创建存储(create procedure) 等 权限

dba角色:拥有所有的系统权限,包括无限制的空间选额和给其他用户授予各种权限的能力。用户SYSTEM 拥有DBA角色。

b. 赋予角色给某一个用户:grant 角色名... to 用户名

撤销用户的某个角色:revoke 角色名 from 用户名

查询当前用户拥有的角色:select * from user_role_privs

查询当前用户的角色的权限信息:select * from role_sys_privs

c. 创建自定义角色:create role 角色名

删除角色: drop role 角色名

赋予权限给角色:grant 权限名 to 角色名

撤销角色的权限:revoke 权限名 from 角色名

查看某个角色的具体权限: select * from dba_sys_privs where grantee =角色名; --注意: 当前登录用需要dba角色的权限才能查看

7. 表空间是数据库最大的逻辑单元,一个Oracle数据库至少包含一个表空间,就是名为SYSTEM的系统表空间。每个表空间是由一个或多个数据文件组成的,一个数据文件只能与一个表空间相关联。在oracle中所有的表都存储在表空间中。

a. 创建user1_tablespace表空间--------- 注意:创建表空间需要对应的(create tablespace)权限

CREATE TABLESPACE user1_tablespace --表空间名称

DATAFILE 'E:/user1.DBF' ----路径名称

SIZE 100M ----是指定该数据文件的大小,也就是表空间的大小。

AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED ----大小自动扩展,没有最大限制

LOGGING ----logging 表示在创建表空间时,将生成日志记录

EXTENT MANAGEMENT LOCAL ----表示创建的表空间采用"本地化"方式管理

SEGMENT SPACE MANAGEMENT AUTO; ----设置表空间中段的管理方式为自动;

b. 创建了表空间怎么样 (分配给用户) 如果创建用户没有设置默认表空间,则默认为USERS表空间

CREATE USER 用户名

IDENTIFIED BY 密码

DEFAULT TABLESPACE user1_tablespace

c. 查看用户对应的默认表空间

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS where USERNAME='user1';

d. 修改用户的默认表空间

alter user 用户名 default tablespace user1_tablespace;

e. 查看表空间的名称及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) "ts_size(M)"

FROM dba_tablespaces t, dba_data_files d

WHERE t.tablespace_name = d.tablespace_name

GROUP BY t.tablespace_name;

f. 查看表空间物理文件的名称及大小

SELECT tablespace_name,

file_id,

file_name,

round(bytes / (1024 * 1024), 0) total_space

FROM dba_data_files

ORDER BY tablespace_name;

g. 查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) as "free_space(M)", tablespace_name

FROM dba_free_space

GROUP BY tablespace_name;

h. 增加数据文件

ALTER TABLESPACE user1_tablespace

ADD DATAFILE 'E:/user1_add.DBF' --添加数据文件

SIZE 100M --大小100M

AUTOEXTEND ON -- 大小自动扩展

NEXT 10M --扩展的增量为10M

MAXSIZE 1024M; --最大扩展到1024M

i. 修改数据文件的大小

ALTER DATABASE DATAFILE 'E:/user1_add.DBF' -- 文件路径

RESIZE 50M;

j. 删除数据文件

ALTER TABLESPACE user1_tablespace

DROP DATAFILE 'E:/user1_add.DBF'

k. 删除表空间

删除user 只是删除了该user下的schema objects,是不会删除相应的tablespace的

drop user ×× cascade

删除tablespace

DROP TABLESPACE tablespace_xxx INCLUDING CONTENTS AND DATAFILES;

广告 广告

评论区