一篇文章搞定oracle数据库--oracle12c

一篇文章搞定oracle数据库--oracle12coracle 数据库的使用 什么是 SQL SQL Structured Query Language 结构化查询语言 用于存取 查询 更新数据和管理关系数据库系统 oracle 使用的是 PL SQL 语言 SQL 分为四类 DDL 数据定义语言

大家好,我是讯享网,很高兴认识大家。

oracle数据库的使用:

什么是SQL:

SQL(Structured Query Language),结构化查询语言,用于存取,查询,更新数据和管理关系数据库系统。

讯享网

oracle使用的是,PL/SQL语言。

SQL分为四类:
讯享网①DDL   数据定义语言 //用于完成对数据库对象(数据库,数据表,视图,索引)的创建,修改及删除。 ②DML   数据操纵语言 用于完成对数据库表中的数据插入,修改,删除操作。 ③DCL   数据控制语言     用于完成对数据库中用户创建删除,用户锁定解除,权限授予取消扽分系统管理员的权限操作。 ④DQL     数据查询语言 用于完成 对数据表中数据的查询操作

创建表空间:

PDB和CDB当前用户下的表空间必须一样

alter database open;Oracle数据库未打开的时候使用

1,查看当前用户下有哪些PDB。

select con_id,dbid,guid,name,open_mode from v$pdbs;

2,进入到PDB里面

讯享网alter session set container=PDBORCL; //PDBORCL是PDB的名称
在PDB里面创建表空间。
 create tablespace test_space --表空间名 logging datafile 'E:\app\orcl\oradata\orcl\test_space1.dbf' -- 表空间存储文件位置 size 50m autoextend ON next 50m maxsize 20480m extent management local;
讯享网 注意:① datafile(设置物理文件名称):路径是Oracle的安装路径 test_space1.dbf 这个文件名字自己起,后缀是dbf ② 创建的表空间的名字是:test_space ③ size 设置表空间的初始大小。 ④ autoextend ON 设置自动增长,如果存储量超过初始大小,则开始自动扩容。 ⑤ next 设置扩容的空间大小。 ⑥ maxsize 最大的扩容空间。

4,切换回CDB容器,将容器名换为CDB容器的名字即可,一个CDB只有一个根。

 alter session set container=CDB$ROOT; 
在CDB里面创建表空间。
讯享网 create tablespace test_space 注意这个test_space和上面的PDB里面的表空间一致 logging datafile 'E:\app\orcl\oradata\orcl\test_space3.dbf' --test_space路径与上面名字不一样 size 50m autoextend ON next 50m maxsize 20480m extent management local;
创建临时表空间:
 create temporary tablespace myTemp --临时表空间名   tempfile 'E:\app\orcl\oradata\orcl\test_spaceTemp.dbf' size 50m   autoextend on   next 50m maxsize 20480m   extent management local;

查询表空间中的信息:

语法1查询系统文件:

语法:

讯享网​

示例:

​
语法2:查询表空间信息:

语法:

讯享网select * from 表空间名称;

示例:

select * from test_space;
语法3:查询表空间里面的表:

语法:

讯享网select 表空间名 from 

示例:

​
讯享网select cdaoen from meitao; 

删除表空间:

语法:

 drop tablespace 表空间名 including contents; 

创建用户:

oracle内部有两个建好的用户:system和sys。用户可以直接登录到system用户,以创建其他用户。因为system用户具有创建别的用户的权限。

语法:[创建用户]

讯享网create user c用户名 identified by 密码 default tablespace 表空间名

注意:创建公共用户名 必须要以 c或者C 开头。

示例:

create user cdaoen identified by 123 default tablespace test_space2;

修改用户:

语法:[修改用户]

讯享网alter user c用户名 identified by 新密码

示例:

alter user cdaoen identified by 666;

删除用户:

语法:用户里面没有角色的时候,使用这个。

讯享网drop user C用户名

示例:

drop user cdaoen;

若用户拥有对象(角色),则不能直接删除,否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象,然后再删除用户。

语法:

讯享网drop user C用户名 cascade;

示例:

drop user cdaoen cascade;

授权角色(用户):

oracle为了兼容以前的版本,提供三种标准角色(role):connect/resource/和dba

1,connect role(连接角色)
讯享网①临时用户,特指不需要建表的用户,通常只赋予他们connect role。 ②connect是使用oracle简单权限,这种权限只对其他用户的表有访问权限。包括select/insert/update/delete。 ③拥有connect role 的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym)、回话(session)和其他 数据的链(link)。
2,resource role(资源角色)
①更可靠和正式的数据库用户可以授予resource role。 ②resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
3,dba role(数据库管理员角色)
讯享网①dba role拥有所有的系统权限。 ②包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有。

赋予DBA权限: grant dba to cSDE //就可以直接在PL/SQL输入用户名和密码进行登录了。

给用户授权:
语法:[添加权限]
grant connect,resource,dba to C用户 //三个权限可以单独添加,也可以全部添加上去。

示例:

讯享网grant connect,resource,dba to Cdaoen;
语法:[撤销权限]
revoke connect,resource,dba from c用户名 ​ 三个权限可以单独去掉,也可以同时去掉。

示例:

讯享网revoke connect,resource,dba from cdaoen;

创建/授权/删除角色:

除了前面提到的三种系统角色----connect、resource和dba,用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role,用户必须具有create role系统权限。

1,创建角色:

语法:

create role c角色名

示例:

讯享网create role testRole;
2,授权角色:

语法:

grant select on 表名 to 角色名;

示例:

讯享网grant select on admin to testRole;

现在,拥有testRole角色的所有用户都具有对class表的select查询权限。

3,删除用户:

语法:

drop role c角色

示例:

讯享网drop role ctestRole;

与testRole角色相关的权限将从数据库全部删除。

Oracle数据类型:

数值类型:
number(precision,scale); 数值类型 1,precision:指定数字的总位数。 2,scale:指定小数点后的位数。
讯享网类型 说明 number(5) 最大99999 number(5,2) 最大999.99 number(18) 默认长度18位
字符类型:
char(size)定长字符数据。 1,size:指定字符串的最大长度(必须是正整数,且不超过32767。
讯享网varchar2(size):可变长字符数据 1,size:指定字符串的最大长度(必须是正整数,且不超过32767)。
long:可变长字符数据,最大可达到 2G。 1,在其它的数据库系统中常被称为 备注类型。 2,主要用于存储大量的可以在稍后返回的文本内容。
讯享网clob:字符数据,最大可达到 4G。 blob:二进制数据,最大可达到 4G bfile:存储外部文件的二进制数据,最大可达到 4G
类型       说明 char 固定长度的字符类型,最多存储 2000 个字节 VARCHAR2 可变长度的字符类型,最多存储 4000 个字节 long     大文本类型,最大可以存储 2 个G clob 存储字符,最大可以存 4 个G blob 存储图像、声音、视频等二进制数据,最多可以存 4 个G
日期时间类型:
讯享网date:日期型数据 1,用于定义日期时间类型的数据。 2,数据长度为固定 7 个字节,分别描述:年、月、日、时、分、秒。
timestamp:时间戳 1,还可以 显示时间 和 上下午标记。 2,如:11-9月-2007 11:09:32.213 AM
讯享网类型 说明 date       日期时间型,精确到秒 timestamp   精确到秒的小数点后 9 位
datetime和timestamp的区别:
1,都是既有 日期 又有 时间 的日期类型。 2,DATETIME 需要使用外部传入的日期,如果没传这个值就是 NULL。 3,TIMESTAMP 会使用 系统当前的时间 作为这个值的 默认值。

Oracle函数:

字符函数:
讯享网函数 说明 concat()     拼接两个字符串,与 || 相同。 insert()     找出某个字符串位置。 length()     求字符串长度。 lower()     将字符串 转换成小写。 upper()       将字符串 转换成大写。 SUBSTR()     取字符串的 子串。

1,length() 求字符串的长度:

select length("abcd") from dual; //4

2,substr() 求字符串的子串:

讯享网select substr('abcd',1,2) from dual; //ab

3,concat() 字符串拼接:

select concat('abc','d') from dual; //abcd select concat(concat('abc','d'),'e') from dual; //abcde

Oracle可以是使用 || 对字符串进行拼接

讯享网 select 'abc' || 'd' from dual; //abcd select '序列=' || admin_a.nextval from dual;
数值函数:
函数 说明 abs(value) 绝对值 ceil(value) 大于或者等于value的最小整数(向上取整) floor(value) 小于或等于value的最大整数(向下取整) sign(value) value为证,返回 1,为负返回 -1,为0返回0 round(value) 四舍五入 trunc() 截取value mod() 取模 random() 随机数

1,round四舍五入:

讯享网select round(5.67) from dual; //6 select round(5.35) from dual; //5 select round(5.56,2) from dual; //5.56

2,trunc截取函数:

select trunc(100.46) from dual; //100 select trunc(100.5645,3) from dual; // 100.564 

3,mod() 取模:

讯享网select mod(10,3) from dual;

4,random() 随机数:

select dbms_random.value from dual; // 0.38125
日期函数:
讯享网函数 说明 add_months   在日期date上增加count个月 last_day(date) 返回日期date所在月的最后一天 ​

1,sysdate系统函数:

select sysdate from dual; //2023-08-24 17:08:07

2,add_months() 加指定个月:

讯享网当前日期 +2 个月 select add_months(sysdate,2) from dual; //2023-10-24 17:15:06 当前日期 -2 个月 select add_months(sysdate,-2) from dual; //2023-06-24 17:15:50

3,last_day() 当月最后一天:

select last_day(sysdate) from dual; //2023-08-31 17:17:16

4,trunc日期截取:

讯享网按日截取 select TRUNC(sysdate) from dual; //2023-08-24 00:00:00 当年第一天: select TRUNC(sysdate, 'yyyy') from dual; //2023-01-01 00:00:00 当月第一天 select TRUNC(sysdate, 'mm') from dual; //2023-08-01 00:00:00 按小时截取 select TRUNC(sysdate, 'hh') from dual; //2023-08-24 17:00:00 按分钟截取 select TRUNC(sysdate, 'mi') from dual; //2023-08-24 17:22:00

5,to_timestamp字符串日期:

select to_timestamp('2020-01-20 14:20:30', 'yyyy-MM-dd hh24:mi:ss:ff') from dual; //2020-01-20 14:20:30.000000000

6,to_date()字符串日期:

讯享网select TO_DATE('2017-01-01', 'yyyy-mm-dd') from dual; //2023-07-12 00:00:00

7,to_number字符串数字:

select to_number('100') from dual; //100 select to_char(1024) from dual; //1024 select '100'+10 from dual; //110 select '100' || 0 from dual; //1000

Oracle的索引:

索引:是用于加速数据存储的数据对象。

合理的使用索引,可以大大降低I/O次数,从而提高数据访问性能。索引是需要占用据存储空间的,也可以理解为是一种特殊的数据。

索引的分类:
普通索引:

语法:

讯享网create index 索引名 on 用户名.表名(列名);

示例:

CREATE INDEX ix_a on csde2.adminsys(adminid);
插入一百万条数据测试:

建表:

讯享网create table aaaa ( ID NUMBER, NAME VARCHAR2(30) );

添加数据:

BEGIN FOR i in 1.. loop insert into aaaa values(i,'aa'|| i); END loop; commit; END;
唯一索引:

需要在 某个表某个列 创建索引,但这列的值是 不会重复的。

语法:

讯享网create unique index 索引名 on 用户名.表名(列名);

示例:

create unique index name_ix on aaaa(name);

删除索引:

语法:

讯享网drop index 索引名;

示例:

drop index aaa_ix;

Oracle中创建序列:

序列的定义:
讯享网①序列用于产生唯一序号的数据库对象,可以为多个数据库用户,依次生成不重复的连续整数,通常使用序列自动生成表中的主键值。 ②序列产生的数字最大长度可达到38位十进制数。序列不占用实际的存储空间,在数据字典中只存储序列的定义描述。
序列的特点:
①为表中的记录,自动生成唯一的序号。 ②由此用户创建,多个用户共享使用。 ③典型应用是生成主键值,用于标识记录的唯一性。 ④允许同时生成多个序列号,而每一个序列号是唯一的 ⑤使用缓存可以加速序列的访问速度。
创建序列:

语法[自增序列]:

讯享网create sequence 此序列名 start with 数字 increment by int类型的数字 maxvalue int类型的数字 或者 nomaxvalue minvalue int类型的数字 nocycle 或者 cycle cache int类型的数字 或者nocache;

示例:

create sequence admin_a start with 1 increment by 1 maxvalue 99999 minvalue 1 nocycle cache 30;
使用序列:

示例:

讯享网insert into cSDE2.stus values(admin_a.nextval,'孙道恩','666'); insert into cSDE2.stus values(admin_a.nextval,'理发师','777');
删除序列:

语法:

drop sequence 序列名;

示例:

讯享网drop sequence admin_a;
查询自增序列的名称:

示例:

select sequence_name from user_sequences;

Oracle中创建数据表:

语法1在CDB里面创建:

要是没有进入到 pdb里面的自定义用户里面

讯享网create table c账号名.表名 ( 字段1 数据类型 约束, 字段2 数据类型 约束 );

实例:

CREATE TABLE cSDE2.stu ( id NUMBER primary key not null, stuName VARCHAR(20) not null, pwd VARCHAR(20) not NULL );

语法2:

在PDB里面,在指定用户里面创建:

语法:

讯享网create table 用户名.表名 ( 字段1 数据类型 约束, 字段2 数据类型 约束 )

示例:

CREATE table cSDE2.admin ( id NUMBER primary key not null, name VARCHAR(20) not null, pwd VARCHAR(20) not NULL );
oracle中添加数据:
语法1根据字段需求添加:
讯享网insert into 用户名.表名(字段1,字段2...) values(值1,值2...);

示例:

insert into cSDE2.stus(stuid,stuname,pwd) values(1,'都是','111');
语法2全部添加字段值:

语法:

讯享网insert into 用户名.表名 values(值);

示例:

insert into cSDE2.admin values(admin_a.nextval,'孙道恩','666'); insert into cSDE2.admin values(admin_a.nextval,'理发师','777'); //这个里面使用了序列
插入时间的时候,特殊处理:

语法:

讯享网insert into 用户名.表名 values(字段1,字段2,to_date('2023-08-23','yyyy-mm-dd'));

示例:

insert into csde2.book values(2,'随和',TO_DATE('2023-08-23','yyyy-mm-dd'));
一次添加多条数据:
讯享网①insert all 开头 ②select 1 from dual;结尾 不许含有, 否则会报错缺少 select 关键词. dual 可以替换不是 t_table 的任意一个表。 ③没有插入的一条语句中间没有,符号。

语法:

insert all into 用户名.表名 (字段1,字段2,字段3....) values(值1,值2,值3...) into 用户名.表名 (字段1,字段2,字段3....) values(值1,值2,值3...) into 用户名.表名 (字段1,字段2,字段3....) values(值1,值2,值3...) select 1 from dual;

示例:

讯享网INSERT ALL INTO csde2.book (bookid,bookname,bookdate) VALUES(4,'罗生门',TO_DATE('2012-09-12','yyyy-mm-dd')) INTO csde2.book (bookid,bookname,bookdate) VALUES(5,'Java程序设计',TO_DATE('2018-11-12', 'yyyy-mm-dd')) select 1 FROM dual;

Oracle中修改表中的数据:
全部修改:

语法:

update 用户名.表名 set 字段 = 新值,字段 = 新值;

示例:

讯享网UPDATE csde2.book SET bookname = '查理九世';
根据参数修改:

语法:

update 用户名.表名 set 字段 = 新值,字段 = 新值 where 字段 = 值;

示例:

讯享网UPDATE csde2.book SET bookname = '城南旧时',bookdate = TO_DATE('2022-10-12','yyyy-mm-dd') WHERE bookid = 1;
Oracle删除表中的数据:

语法:

delete from 用户民.表名 [where] 字段 = 值;

示例:

讯享网select from csde2.book where bookId = 3;

Oracle的分页:

注意:

rownum后面的符号,只能是 <,<=。

语法1简单的分页:
讯享网select * from 用户名.表名 where rownum 分页的条件 order by 表中的某个字段;

示例:

select * FROM csde2.book WHERE ROWNUM <= 2 ORDER by bookid;
语法2查询任意范围的分页:

语法:

讯享网select * from (select rownum 别名,别名.*|或者别名.字段  from 用户名.表名 别名);

示例:

select * FROM (select ROWNUM r,s.* FROM csde2.book s) WHERE r > 10 and r<= 20;
语法3基于排序的查询:

语法:

讯享网select * from (select rownum 别名,表别名.* from (select * from song 表别名 order by 表中的字段 desc或者 asc) 别名 ) where rownum的表名 > 某个值 and rownum的别名 < 某个值;

示例:

SELECT * FROM (SELECT ROWNUM r, s.* FROM (SELECT * FROM csde2.book b ORDER by bookid desc) t ) WHERE r > 10 and r <= 20;
Oracle计算年龄:

语法:

讯享网select trunc(months_between(sysdate,表中关于日期的字段) / 12) as 别名 from 用户名.表名;

示例:

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,bookdate)/12) AS 年龄 FROM csde2.book;

Oracle列的操作:

增加列名:

语法:

讯享网alter table 用户名.表名 add 新字段 数据类型;

示例:

ALTER TABLE csde2.ADMIN ADD tel VARCHAR2(20);
修改列名:

语法:

讯享网alter table 用户名.表名 rename column 要修改的列名 to 新列名;

示例:

ALTER TABLE csde2.ADMIN rename COLUMN tel to tels;
修改列对应的数据类型:

列下面没有数据时,才可以修改

语法:

讯享网alter table 用户名.表名 modify 要修改类型的列 新的类型;

示例:

ALTER TABLE csde2.ADMIN MODIFY tels NUMBER;
删除列名:

语法:

讯享网alter table 用户名.表名 drop column 列名;

示例:

ALTER TABLE csde2.ADMIN DROP COLUMN tels;

Oracle设置默认时间:

语法:

讯享网create table 用户名.表名 ( 字段1 数据类型, 字段2 date default systimestamp );

示例:

CREATE TABLE csde2.book ( bookId int NOT NULL primary key, bookName VARCHAR(20) NOT null, bookDate DATE DEFAULT SYSTIMESTAMP );

Oracle主键外键约束:

主键约束:(primary key Constraint):主键约束用于定义一个或多个列作为表的主键。主键用于唯一识别表中每一行数据的字段。

主键约束的特点:
讯享网①主键值必须唯一,且不能为空。 ②每个表只能有一个主键。 ③主键在表中创建后不能被修改或者删除。
创建主键约束:
语法1创建表之后添加:
alter table 用户名.表名 add constraint [约束名] primary key(列1,列2..);

示例:

讯享网ALTER TABLE csde2.adminsys ADD constraint adminsys_key primary key(ADMINID);
语法2创建表的时候创建:

语法:

create table 用户名.表名 ( 字段1 数据类型 primary key, 字段2 数据类型 );

示例:

讯享网CREATE TABLE csde2.AdminSys( adminID number primary key NOT NULL, adminName varchar(20) NOT NULL, pwd varchar(20) NOT NULL );
外键约束:

外键约束(foreign key constraint):外键约束用于确保与其他表之间的引用完整性。外键约束定义一个或多列,这些列的值必须存在于另一个表的主键中。

外键约束的特点:
①外键约束必须存在于被引用表的主键中,或者为null。 ②外键可以引用同一张表中的数据(自我引用)。 ③外键在表中创建后可以被修改或删除。

创建外键约束:

语法:

讯享网alter tabe 需要添加外键约束的表名 add constraint [约束名] foreign key(列1) references 用户名.外键表名(列1);

示例1:客户和订单建立主外键关系

ALTER TABLE csde2.orders ADD constraint c_o_key foreign key(customerid) references csde2.customer(customerid);

示例2:商店和商品建立主外键关系

讯享网ALTER TABLE csde2.product ADD constraint s_p_key foreign key(shopid) references csde2.shop(shopid);

Oracle查询:

精确查询:

语法:

select * from 用户名.表名 where 字段 = 值;

示例:

讯享网查询编号为2的图书信息 select * from csde2.book WHERE bookid = 2;
模糊查询:

语法:

select * from 用户名.表名 where 字段 like '%值_';

示例:

讯享网select * from csde2.book where bookname like '%理%';
and和or运算符:

and

语法:

select * from 用户名.表名 where 字段 like '_值%' and 字段 = 值;

示例:

讯享网select * from csde2.book where bookname like '%九%' and bookid = 2;

or

语法:

select * from 用户名.表名 where 字段 = 值 or 字段 = 值;

示例:

讯享网select * from csde2.book where bookname like '%生%' or bookname like '%理%';
范围查询:

语法:

select * from 用户名.表名 where 字段 >= 值 and 字段 <= 值;

示例:

讯享网select * from csde2.product where currentPrice >= 100 and currentPrice <= 200; 
空值查询:

is null

语法:

select * from 用户名.表名 where 字段 is null;

示例:

讯享网查询book表中 bookname为空的记录 select * from csde2.book where bookname is null;

is not null

语法:

select * from 用户名.表名 where 字段 is not null;

示例:

讯享网查询book表中 bookname不为空的记录 select * from csde2.book where bookname is not null;
去掉重复记录:

语法:

select distinct 要去重的字段 from 用户名.表名;

示例:

讯享网select distinct from csde2.book;
排序查询:

语法:

select * from 用户名.表名 order by 字段 asc 或者 desc;

示例:

讯享网select * from csde2.book order by bookid desc;
聚合统计:

Oracle 的聚合统计,是通过分组函数来实现的,与 MySQL 一致。

语法count()统计个数:

示例:

select count(*) from csde2.book;

语法:sum()求和

示例:

讯享网select sum(bookid) from csde2.book;

语法:avg()求平均值:

示例:

select avg(currentPrice) from csde2.product;

语法:max()求最大值:

示例:


讯享网

讯享网select max(currentPrice) from csde2.product;

语法:min()求最小值:

示例:

select min(currentPrice) from csde2.product;
语法:分组聚合

注意:select后一定是分组聚合,(group by)的条件或者是聚合函数。

示例:

讯享网select categoryid,avg(currentPrice) from 用户名.表名 group by categoryid;
语法:分组后条件查询:

having过滤:

示例:

select areaid,count(*) from 用户名.表名 group by areaid having count(*) > 10;
连接查询:

多表内连接查询: 示例:

讯享网select p.TITLE,p.CURRENTPRICE FROM csde2.PRODUCT p,csde2.ORDERSDETAIL od WHERE p.PRODUCTID  = od.PRODUCTID; 

示例:三表联查

 select p.TITLE 商品标题,p.CURRENTPRICE 商品团购价 FROM csde2.PRODUCT p,csde2.ORDERS o,csde2.ORDERSDETAIL od WHERE p.PRODUCTID = od.PRODUCTID AND o.ORDERSID = od.ORDERSID;
左外连接查询:

示例:

讯享网select o.CUSTOMERID as 顾客编号 FROM csde2.ORDERS o LEFT JOIN csde2.ORDERSDETAIL od on o.ORDERSID = od.ORDERSID;
右外连接查询:

示例:

select p.TITLE 商品标题,p.CURRENTPRICE 商品团购价 FROM csde2.ORDERSDETAIL od RIGHT JOIN csde2.PRODUCT p on od.PRODUCTID = p.PRODUCTID;
子查询:

where 子句中的子查询:

单行子查询:
讯享网运算符 说明 = 等于 > 大于 >=         大于等于 < 小于 <=         小于等于 <> 不等于

示例:

select p.TITLE 商品标题 FROM csde2.PRODUCT p where p.CATEGORYID = (select c.CATEGORYID from csde2.CATEGORY c WHERE c.CATEGORYid = 7);

示例:

讯享网SELECT p.title 商品标题 FROM csde2.PRODUCT p WHERE p.PRODUCTID = (select od.PRODUCTID FROM csde2.ORDERSDETAIL od WHERE od.ORDERSid = (select o.ORDERSid FROM csde2.ORDERS o WHERE o.CUSTOMERid = (select c.CUSTOMERID FROM csde2.CUSTOMER c WHERE c.CUSTOMERname = '孙恩'))); 
多行子查询:
运算符 说明 in 等于列表中的任何一个 any 和子查询返回的任意一个值比较 all 和子查询返回的所有值比较

示例:

讯享网select p.title 商品标题 FROM csde2.PRODUCT p WHERE p.AREAid in (select a.AREAID from csde2.AREA a WHERE a.areaname IN('河南','山东'));

示例:

select p.CURRENTPRICE 团购价 FROM csde2.PRODUCT p WHERE p.CATEGORYID not IN (select c.CATEGORYID FROM csde2.CATEGORY c WHERE c.CATEGORYid = 7 AND c.CATEGORYname LIKE '%火锅%');

from 子句中的子查询:

示例:

讯享网SELECT * FROM (select p.TITLE 商品标题,p.CURRENTPRICE 团购价 FROM csde2.PRODUCT p LEFT JOIN csde2.ORDERSDETAIL od on p.PRODUCTID = od.PRODUCTID) WHERE 商品标题 = '冰镇西瓜';

select子句中的子查询:

示例:

select p.AREAID 区域编号,p.CATEGORYID 类型编号,(select p.TITLE from csde2.PRODUCT p WHERE p.PRODUCTID = (select od.PRODUCTID FROM csde2.ORDERSDETAIL od WHERE od.PRODUCTid = 1)) 商品标题 FROM csde2.PRODUCT p;

Oracle中创建同义词:

Oracle 同义词有两种类型:私有(private)和公共(public)

讯享网私有的同义词是在指定的模式中创建并且只有创建者使用的模式访问。公共同义词是由public 指定的模式访问,所有数据库模式(用户)都可以访问它。
同义词的作用:
①多用户协同开发中,可以屏蔽对象的名字及其持有者。 ②化sql语句。 ③为分布式数据库的远程对象提供位置透明性。
同义词的创建:

在创建前,需要给用户赋予创建同义词的权限:

讯享网给予XXX用户,创建同义词的权限。 grant create synonym to 用户名;

示例:

GRANT CREATE SYNONYM to cSDE2; --给用户cSDE2赋予创建同义词的权限
创建同义词:

语法:

讯享网create [or replace] [public] synonym 同义词名 for 用户名.存在的表名;

示例:

CREATE PUBLIC SYNONYM sn_admin FOR cSDE2.admin;

使用同义词:

语法:

讯享网select * from 同义词名; 

示例:

SELECT * FROM p_syn;

删除同义词:

语法:

讯享网drop public syonoy, 同义词名;

示例:

DROP PUBLIC SYNONYM sn_admin;
查看所有同义词:

语法:

讯享网select * FROM dba_synonyms;

Oracle中创建视图:

视图:是一个封装了各种复杂查询的语句,就称为视图。

创建视图:
语法1没有起别名:
create [force 或 noforce 或 or replace] view 视图名称 as select语句;

语法解释:

讯享网force:表示要传概念视图的表,不存在也可以创建视图。 noforce:(默认)表示要创建视图的表必须存在,否则无法创建。 or replace:表示视图的替换。如果创建的视图不存在,则创建新的视图;如果视图已经存在,则将其替换。

示例1:

CREATE VIEW v_admin as select * FROM ADMIN;

示例2:

讯享网CREATE OR REPLACE VIEW v_admin as SELECT pwd FROM ADMIN;
语法2给视图的每一列起别名:
CREATE or REPLACE VIEW 视图名称 (字段1,字段2,字段3...) as select 子句;

示例:

讯享网CREATE or REPLACE VIEW view_admin (管理员编号,管理员名字,管理员密码) as select * FROM ADMIN;
创建视图时有with check option子句:

语法:使用该子句时,可以设置一个约束名称。

create [force 或 noforce 或 or force] view 视图名 as select子句 where 条件 with check option constraint 起个名字;

示例:

讯享网select title,currentprice FROM PRODUCT WHERE currentPrice > 10.0 WITH CHECK OPTION constraint v_admin_ck; 

插入currentprice > 10.0,才可以插入成功。

创建视图时有with read only子句:

视图添加该子句后,用户无法使用该视图,对表进行delete,update,insert操作。

语法:

create or replace view 视图名称 as select子句 with read only;

示例:

讯享网CREATE or REPLACE VIEW v_admin as select * FROM PRODUCT WHERE categoryid = 7 WITH read ONLY;

如果这个时候对视图进行更新操作,就会报错。

在视图上执行DML操作:

先创建视图,并向该视图插入一条数据。

对视图的添加操作:

语法:

insert into 视图名(字段1,字段2,...) values (值);

示例:

讯享网insert into v_admin (adminid,pwd,adminname) VALUES(4,'安抚发','123');
对视图的修改操作:

语法:

update 视图名 set 字段 = 值,... where 字段 = 值;

示例:

讯享网UPDATE v_admin SET pwd = '孙道恩最帅' ,adminname = '888' WHERE adminid = 3;

对视图的删除操作:

语法:

delete from 视图名 [where] 字段 = 值;

示例:

讯享网DELETE FROM v_admin WHERE ADMINid = 4;

使用和查看视图:
语法1直接使用视图:
select * from 视图名;

示例:

讯享网select * FROM v_admin;
语法2查看视图是否创建成功:

语法:

select * from tab where tabtype = 'VIEW';
语法3通过user_view数据字典:

语法:

讯享网SELECT view_name,text_length,text FROM USER_views;

Oracle中的PL/SQL:

基本语法结构:
--声明变量 [declare] begin 代码逻辑 [exception] end;

变量声明能够和赋值:

语法:

声明变量语法:
讯享网变量名 类型([长度]);

示例:

declare num number;
变量赋值语法:

语法:

讯享网变量名 := 变量值;

示例:

num := 100;

小案例:

讯享网--变量的用法 declare --水费单价 v_price number; --金额 v_monery number; --吨数 v_ton number; begin --水费单价 v_price := 2; --吨数 v_ton number = 50; --金额 v_monery := round(v_price*v_ton); --输出 dbms_output.put_line('单价:' || v_price || '总金额:' || v_monry); end; select into 方式赋值语法结构:

语法:

select 列名 into 变量名 from 用户名.表名 where 条件;

示例:

讯享网 DECLARE prices FLOAT; BEGIN SELECT CURRENTprice INTO prices FROM csde2.PRODUCT WHERE PRODUCTid = 1; end;

注意:结果必须是一条记录,有多条记录和没有记录会报错。

属性类型:

表名.列名%type 引用型。

作用:引用某个表,某列的字段类型。

DECLARE --商品标题 v_title csde2.PRODUCT.title%TYPE; --商品的区域id v_areaid csde2.PRODUCT.AREAid%TYPE; --商品id编号 v_id csde2.PRODUCT.PRODUCTid%type; BEGIN v_title := '罚款哈'; SELECT areaid, PRODUCTid INTO v_areaid,v_id FROM csde2.PRODUCT WHERE title = v_title; dbms_output.put_line('区域id=' || v_areaid || '商品编号=' || v_id ); END; 

表名%rowtype 记录型

作用:标识某个表的一行记录类型(一行数据)

讯享网DECLARE v_pros csde2.PRODUCT%rowtype; v_proid csde2.PRODUCT.PRODUCTid%TYPE; BEGIN v_proid := 1; SELECT * INTO v_pros FROM csde2.PRODUCT WHERE PRODUCTid = v_proid; dbms_output.put_line('产品标题='|| v_pros.title || ' 编号=' || v_pros.PRODUCTid); END;
条件判断:

基本语法1:

语法:

if 条件 then 业务逻辑 end if;

示例:

讯享网DECLARE v_proid csde2.PRODUCT.PRODUCTid%TYPE; v_price csde2.PRODUCT.currentPrice%TYPE; BEGIN v_proid := 2; SELECT CURRENTprice into v_price FROM csde2.PRODUCT WHERE PRODUCTid = v_proid; IF v_price >= 10 THEN dbms_output.put_line('不划算=' || v_price); END IF; END;

基本语法2:

语法:

if 条件 then 业务逻辑; else 业务逻辑; end if;

示例:

讯享网DECLARE v_proid csde2.PRODUCT.PRODUCTid%TYPE; v_price csde2.PRODUCT.currentPrice%TYPE; BEGIN v_proid := 4; SELECT CURRENTprice into v_price FROM csde2.PRODUCT WHERE PRODUCTid = v_proid; IF v_price >= 10 THEN dbms_output.put_line('不划算=' || v_price); ELSE dbms_output.put_line('划算=' || v_price); END IF; END;

基本语法3:

语法:

if 条件 then 业务逻辑; elsif 条件 then 业务逻辑; else 业务逻辑: end if;

示例:

讯享网DECLARE v_proid csde2.PRODUCT.PRODUCTid%TYPE; v_price csde2.PRODUCT.currentPrice%TYPE; BEGIN v_proid := 4; SELECT CURRENTprice into v_price FROM csde2.PRODUCT WHERE PRODUCTid = v_proid; IF v_price <= 5 THEN dbms_output.put_line('超级划算:' || v_price); ELSIF v_price > 5 AND v_price < 10 THEN dbms_output.put_line('一般划算:' || v_price); ELSE dbms_output.put_line('不划算:' || v_price); END IF; END;
循环:

无条件循环:

语法:

loop --循环条件 end loop; 

示例:

讯享网DECLARE v_num NUMBER := 1; BEGIN loop dbms_output.put_line('这是:'|| v_num); v_num := v_num + 1; --判断条件退出条件 exit WHEN v_num > 100; END loop; END;

有条件循环:

语法:

while 条件 loop --循环语句 end loop; 

示例:

讯享网DECLARE v_num NUMBER := 1; BEGIN --判断条件 while v_num <= 100 loop dbms_output.put_line('这是:' || v_num); v_num := v_num +1; END loop; END;
for循环:

语法:

for 变量 in 起始值 .. 终止值 loop --循环语句 end loop; 

示例:

讯享网BEGIN for v_num in 1..100 loop dbms_output.put_line('这是第'|| v_num || '个'); END loop; END;
游标:

游标是系统为用户开设的一个数据缓冲区。

1,存放sql语句的执行结果。 2,可以把 游标 理解为 PL/SQL 中的结果集。

声明游标:

讯享网cursor 游标名称  is SQL语句;

使用游标:

DECLARE --声明游标 cursor cur_pro is SELECT * FROM csde2.PRODUCT WHERE SHOPID = 1; --定义行对象 v_p csde2.PRODUCT%ROWtype; BEGIN --打开游标 open cur_pro; loop --提取游标到变量 FETCH cur_pro INTO v_p; --当游标到最后一行退出循环 exit WHEN cur_pro%NOTfound; --输出 dbms_output.put_line('标题:' || v_p.title || '价格:' || v_p.currentprice); END loop; --关闭游标 close cur_pro; END;

带参数的游标:

示例:

讯享网DECLARE --定义行对象 v_p csde2.PRODUCT%ROWtype; --定义游标 cursor cur_pro(sid NUMBER) is SELECT * FROM csde2.PRODUCT WHERE SHOPid = sid; BEGIN open cur_pro(3); loop FETCH cur_pro into v_p; exit WHEN cur_pro%NOTfound; dbms_output.put_line('商品标题:' || v_p.title || '团购价:' || v_p.currentprice); END loop; close cur_pro; END;

for循环提取游标值:

示例:

DECLARE cursor cur_pro(sid NUMBER) is select * FROM csde2.PRODUCT WHERE SHOPid = sid; BEGIN FOR v_p IN cur_pro(1) loop dbms_output.put_line('商品编号:' || v_p.title || '商品标题:' || v_p.title); END loop; END;

Oracle数据库存储函数和存储过程:

存储过程:

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

创建存储过程:
创建无参存储过程:

语法:

讯享网create [or replace] PROCEDURE 存储过程名 as begin 程序体 end; 

示例:

create or REPLACE PROCEDURE admin_proc as BEGIN dbms_output.put_line('你好世界'); end;
创建有参存储过程函数:

语法:

讯享网create or replace PROCEDURE 存储过程函数名(参数名 数据类型) as begin dbms_output.put_line(值); end; 

示例:

CREATE or REPLACE PROCEDURE proc_hh(num NUMBER) as begin dbms_output.put_line('num是' || num); END; 
创建有参存储过程,且返回多个值:

语法:

讯享网create or replace procedure 存储过程名(参数名 数据类型,res out 数据类型) as begin out后面的变量名 := 参数名 操作; end; 

示例:

CREATE OR REPLACE PROCEDURE proc_aa(num NUMBER,res out number) as BEGIN res := num *10; END;

调用存储过程函数:
调用无参存储过程函数

语法:

讯享网call 无参存储过程函数名;

示例:

call admin_proc();
调用有参存储过程函数:

语法:

讯享网call 存储过程名(参数值);

示例:

call proc_hh(21);
调用有参且有返回值的存储过程函数:

语法:

讯享网declare 变量名1 数据类型 := 值; 变量名2 数据类型 ; begin 带输入和返回值的存储过程函数(变量名1,变量名2); dbms_output.put_line("XXX=" || 变量名2); end; 

示例:

--调用方法二 declare n number:=9; r number; begin proc_aa(n,r); dbms_output.put_line('r='||r); end;
讯享网​
查看所有存储过程和函数:

示例:

select *From user_source;

在存储过程中实现判断语句和分支语句:
if语句:

语法:

讯享网create or replace procedure 存储过程函数名(参数名 参数类型,...,参数名 out 数据类型) as begin if判断 代码 else 代码 end if; end; 

示例:

CREATE or REPLACE PROCEDURE p_getMax(a NUMBER,b NUMBER,m out NUMBER) as BEGIN if a > b then m := a; ELSE m := b; END IF; end;
多分支while和for:

示例1:

讯享网CREATE or REPLACE procedure p_sum(a number,res out number) as i := 1; BEGIN res := 0; while i < a LOOP res := res + i; i := i +1; END LOOP; END;

示例2:

create or REPLACE procedure p_getsum2(a NUMBER,res out NUMBER) as BEGIN res := 0; FOR i IN 1..a loop res := res + i; END loop; END; 

案例,通过id获取商品的标题:

讯享网create or replace procedure getTitleById(pid number,ptitle out varchar(20))

删除存储过程:

语法:

drop procedure 存储过程函数名;

示例:

讯享网drop PROCEDURE proc_aa;

存储函数:

存储函数和系统内的函数类似,可以像调用系统函数一样调用存储函数。它与存储过程的唯一区别就是,存储过程没有return返回值,存储函数可以和存储过程互换,存储函数可以在存储过程中调用。

注意:存储函数是有返回值的。

创建存储函数:
语法1有参的存储函数:
create or replace function 函数名[(参数 in|out 参数类型)] return 返回值的数据类型 is 声明变量部分; begin return 结果变量; end [函数名]; 

示例:

讯享网CREATE OR REPLACE FUNCTION   get(aid number) return varchar IS apwd VARCHAR(20); BEGIN select pwd INTO apwd FROM csde2.admin WHERE id = aid; return apwd; END;
语法2无参的存储函数:

语法:

create or replace function 函数名 return 数据类型 is [变量] begin select 字段 into 变量 from 用户名.表名; return 变量名; end;   

示例:

是无参函数,但是因为结果有,多条数据,所以调用的时候会报错。

讯享网CREATE or REPLACE FUNCTION getAllName return varchar is aname VARCHAR(20); begin select name into aname FROM csde2.admin; return aname; END; 
语法3:返回多行数据的无参存储函数:

因为是返回多条数据,所以要定义游标。

语法:

create or replace function 存储函数名 return sys_refcursor; as 定义一个游标名 sys_refcursor; begin open 定义的游标名 for select 要查询的字段 from 用户名.表名; return 定义的游标名; end; 

示例:

此函数中,返回两个字段,多行数据。

讯享网 CREATE or replace FUNCTION getAllpwdAndName return SYS_REFCURSOR as pwd_cusor SYS_REFCURSOR; BEGIN open pwd_cusor FOR select pwd,name FROM csde2.admin; return pwd_cusor; END;
语法4:返回多行数据的有参存储函数:

语法:

create or replace function 存储函数名(参数名 in 数据类型) return sys_refcursor; as 定义一个游标名 sys_refcursor; begin open 定义的游标名 for select 要查询的字段 from 用户名.表名 where 字段 = 参数名; return 定义的游标名; end;

示例:

讯享网CREATE or REPLACE FUNCTION getpwdAndNameById(aid IN number) return sys_refcursor as admin_cursor sys_refcursor; BEGIN open admin_cursor FOR select name,pwd FROM csde2.ADMIN WHERE id = aid; return admin_cursor; END; 

调用存储函数:

调用有参存储函数:

语法:

select 存储函数名(参数) from dual;

示例:

讯享网SELECT getPwdById(1) FROM dual;
调用无参函数:

语法:

select 存储函数名 from from dual;

示例:

讯享网select getAllName from dual; 
调用带游标的无参函数:

语法:

declare 定义一个游标名 sys_refcursor; 定义接收字段1 数据类型; 定义接收字段2 数据类型; begin 定义一个游标 := 无参存储函数名(); loop fetch 定义的游标名 into 定义接收的字段1,定义接收字段2; exit when 定义的游标%notfound; DBMS_OUTPUT.PUT_LINE('结果字段1' || 定义接收字段1 || '结果字段2' || 定义接收字段2 ); end loop; close 定义的游标;  end;  

示例:

讯享网DECLARE a_data SYS_REFCURSOR; a_pwd varchar(20); a_name varchar(20); BEGIN a_data := getAllpwdAndName(); loop FETCH a_data INTO a_pwd,a_name; exit WHEN a_data%NOTfound; DBMS_OUTPUT.PUT_LINE('密码:' || a_pwd || ' 名字:' || a_name); END loop; close a_data; END; 
     // 密码:666 名字:孙道恩 密码:123 名字:发达地 密码:888 名字:范德萨 密码:1233 名字:鬼斧神
调用带游标的有参存储函数:

语法:

讯享网declare 定义一个游标名 sys_refcursor; 定义接收字段1 数据类型; 定义接收字段2 数据类型; begin 定义一个游标 := y有参存储函数名(参数); loop fetch 定义的游标名 into 定义接收的字段1,定义接收字段2; exit when 定义的游标%notfound; DBMS_OUTPUT.PUT_LINE('结果字段1' || 定义接收字段1 || '结果字段2' || 定义接收字段2 ); end loop; close 定义的游标; end;

示例:

DECLARE a_data sys_refcursor; a_name VARCHAR(20); a_pwd varchar(20); BEGIN a_data := getpwdAndNameById(1); loop FETCH a_data INTO a_name,a_pwd; exit when a_data%NOTfound; DBMS_OUTPUT.PUT_LINE('用户名:' || a_name || ' 密码:' || a_pwd); END loop; close a_data; END;

删除存储函数:

语法:

讯享网drop function 存储函数名;

示例:

DROP FUNCTION getpwdAndNameById; 

Oracle触发器:

概念:
讯享网1,数据库触发器,是一个表相关联的,存储的pl/sql程序。 2,每当一个特定的数据库操作语句(insert update delete)在指定的表上发出时,Oracle自定的执行触发器中定义的语句序列。
触发器用途:
1,数据确认。 2,实施复杂的安全检查。 3,做审计,跟踪表上所做的数据操作等。 4,数据的备份和同步。
触发器的分类:
讯享网1,前置触发器(before)。 2,后置触发器(after)。

数据验证:确保插入,更新或删除操作符合业务规则和完整性约束条件。

数据转换:将插入,更新或删除操作中的数据转换为其他格式或单位。

数据记录:记录插入,更新或删除操作的详细信息,

触发器的创建语法:
create or replace trigger 触发器的名字 [ before 或者 after] [insert 或者 update 或者 delete] [of 针对那一列] [on 表名] [referencing old as old new as new] [for each now] when (condition) declare --局部变量 begin --执行触发器的操作 end;
讯享网/

关键字的含义:

1,create or replace trigger:创建或替换一个触发器。 2,before或after:触发器在插入、更新或删除操作之前(BEFORE)还是之后(AFTER)触发。 3,INSERT | UPDATE | DELETE:指定触发器在哪种操作上触发。 4,[OF column_name]:指定只对某一列进行操作。 5,[ON table_name]:指定触发器所属的表名。 6,[REFERENCING OLD AS old NEW AS new]:指定使用OLD和NEW伪记录引用旧值和新值。 7,[FOR EACH ROW]:指定为每一行执行触发器操作。 8,DECLARE:声明局部变量和游标。 9,begin:开始执行触发器操作。 10,结束触发器代码块。 11,/:表示触发器定义结束。
Oracle触发器的三种类型:
讯享网1,行级触发器(row-level-triggers):在每次插入,更新或删除单个记录时触发。 2,语句级触发器:在SQL语句执行完毕后触发。 系统级触发器:在数据库整体运行时触发。
行级触发器:
--delete时触发 CREATE OR REPLACE TRIGGER del_pro after DELETE ON csde2.SHOP FOR each ROW BEGIN DELETE FROM csde2.PRODUCT WHERE SHOPID = :old.SHOPID; END;
讯享网​ --insert时触发 create or replace trigger insert_pro ​

oracle常用的命令:

查看数据库版本:
SELECT * FROM v$version;
查看表空间:
讯享网select tablespace_name from dba_tablespaces; 
查看表空间的所在路径:
select * from dba_data_files;
查看数据库是不是CDB:
讯享网select CDB from v$database; //结果为YES,那么就是CDB的数据库,否则,则不是。
查当前CDB有几个PDB:
select name , open_mode from v$pdbs; //;两个:一个是默认的PDBORCL,另一个是PDB的模板PDB$SEED(创建PDB的时候会用到PDB$SEED)
查询当前数据库:
讯享网select CDB from v$database;

小讯
上一篇 2025-02-17 14:12
下一篇 2025-01-13 16:56

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/130662.html