Oracle 数据库(一)

最新推荐文章于 2023-06-09 13:14:11 发布

杀神lwz

最新推荐文章于 2023-06-09 13:14:11 发布

阅读量414

点赞数 1

分类专栏：数据库文章标签： oracle sql plsql

本文链接：https://blog.csdn.net/weixin_42472027/article/details/119491267

版权

数据库专栏收录该内容

14 篇文章 3 订阅

订阅专栏

一、数据库简介

数据库（Database）：指的是以一定的方式存储在一起，能为多个用户共享，具有尽可能小的数据冗余特点，与应用程序彼此独立的数据集合。

常见的数据库：

Oracle 美国Oracle公司适用于所有主流平台运行
DB2 美国IBM公司适用于所有主流平台运行
SQL Server 美国Microsoft公司只能在Windows上运行
My SQL 瑞典My SQL公司适用于所有主流平台运行 2008年被SUN公司收购 2010年被Oracle公司收购

Oracle版本

i--->代表Internet，支持Internet计算
g--->代表Grid，支持网格计算
c--->代表clound，支持云计算

常见的版本9i，10g，11g，12c

二、增删改查

SQL：结构化查询语句（Structured Query Language）简称SQL

1.添加语句

语法：insert into 表名（列名） values（值）

说明：当向表中添加所有列的记录时，列名可以省略；insert into 表名 values（值）

注意：有几个列，就要有几个值

批量增加语法：insert into 表名 select 值 from dual union select 值 from dual

例子：

insert into emp(no,name,job,manageno,salary,bonus,deptno,joindate) values('1234','张三丰','PG','1789',4778,'789',10,20180321);

2.修改语句

语法：update 表名 set 列 = 值 where 条件

说明：当需要修改的列为多个列时，那么set关键字只能出现一次，语法变为update 表名 set 列名1 = 值1 ,列名2 = 值2 ,...where 条件

注意：在进行修改操作时，where条件是必须写出来的，否则会造成整张表数据统一化。

例子：

update student set name='小王',class=2,score=90 where no =1001;

3.删除语句

语法一：delete from 表名 where 条件

说明：delete 删除的数据是可以通过回滚的方式，找回部分数据。

注意：在进行删除操作时，where条件是必须写出来的，否则会造成整张表数据全部删除。

语法二：truncate table 表名

说明：此删除语法同样是删除数据，但是，如果使用truncate语法删除数据后，数据是无法通过回滚的方式找回的，也就是说，数据被彻底删除

语法三：drop table 表名

说明：此删除语法是删除表结构的，会删除表中数据，删除表中的列，约束，索引...

例子：

delete from emp where name='张三丰' or name='张无忌';

4.查询语句

语法：select 列名 from 表名 where 条件 order by 列名排序规则

说明：将要查询的值写在select关键字之后，将要查询的表写在from关键字之后，where 条件查询

order by结构只能写在SQL语句最后进行排序，排序规则：①asc：升序，从小到大排序②desc：降序，从大到小排序。不写默认升序排序。

条件为空：where 列名 is null

条件不为空：where 列名 is not null

条件还可以是：>,<,=,>=,<=,<>不等于。

当有两个或两个以上的条件时，需要使用数据库的逻辑运算符来连接多个条件 and、or

通过||双竖线来拼接值：实例： select name || (score + 10) || '分' 哈哈哈 from student

起别名的三种方式

1.通过空格起别名

select score+50 score from student

2.通过双引号起别名区分大小写

select score+50 "Score" from student

3.通过as关键字起别名

select score+50 as score from student

去除重复数据 distinct：查询的所有字段数据都重复，才去重。

select distinct 列名 from 表名 where 条件

注意：当我们查询的数据为全部数据时，可以使用*号来代替查询的列名，但是效率低，不推荐使用。

between ... and ...：在两者之间，包括端点

select name, age from student where age >= 14 and age <= 19;
select name, age from student where age between 14 and 19;

in：等于括号里面任意一个值即可。not in 不等于括号里面的值

select name, age from student where age in(16, 17);
select name, age from student where age not in(16, 17);

any：是符合括号里面任意一个值即可。可以用于 > any(), < any(), = any()

select name, age from student where age > 16 or age > 19;
select name, age from student where age > any (16, 19);

all：满足括号里面所有条件

select name, age from student where age > 16 and age > 19;
select name, age from student where age > all(16, 19);

模糊查询like

% 占位符，自动匹配0到任意长度的字符

_ 占位符，自动匹配一个长度的字符

实例： --查询第一个字是小字的人的姓名

select name from student where name like '小%'

--查询名字里第二个字是 '大' 字的学生名

select name from student where name like '_大%'

合并结果集union & union all

union all --不去重，不排序

union --去重，排序

select no from student where no in(1001, 1004, 1003) union all  select no from student where no in(1003, 1002, 1005);
select no from student where no in(1001, 1004, 1003)  union  select no from student where no in(1003, 1002, 1005);

group by : 后接分组的字段

having：后接出现聚合函数的限制条件

order by +排序的字段 +排序规则

注意：having可以写在group by前面，但执行效率较低。

三、函数

单行函数对单行操作，每行返回一个结果。

1.dual：伪表

伪表是一个默认存在的表，里面只有一行一列数据，数据是空。

应用：查询的数据不来源于任何一个表，那么from子句后写dual。

例子：上面的批量增加语法。

2.字符

a)LOWER(列)：将大写或小写混合的字符转换成小写

b)UPPER(列)：将大写或小写混合的字符转换成大写

c)INITCAP(列)：将每个单词的第一个字母转换成大写，其余字母转换成小写

d)CONCAT(列1，列2)：连接两个值，相当于“||”

e)SUBSTR(列，n1[,n2])：

返回列中，从n1开始，长度为n2的字符串

如果n2省略，则取从n1开始之后的所有字符

如果n1为负数，表示从列|表达式的后面第abs(n1)位开始向右取长度为n2的字符串

f)LENGTH(列)：取字符串的长度

g)INSTR(s1,s2[,n1][,n2])：返回s1中子串从n1开始，第n2次出现的位置，n1，n2默认为1

h)LPAD(s1,n1,s2)：返回s1被s2从左面填充到n1长度后的字符串

i)RPAD(s1,n1,s2)：返回s1被s2从右面填充到n1长度后的字符串

j)TRIM(s1 FROM s2)：去除字符串头部或尾部的字符，将s1从s2中去掉

k)REPLACE(s1,s2,s3)：把s1中的s2用s3替换

3.数值函数

a)ROUND(列，n)：将列或表达式所表示的数值四舍五入到小数点后第n位

b)TRUNC(列，n)：将列或表达式所表示的数值截取到小数点后第n位

c)MOD(m，n)：M是除数，n是被除数，取m除n后的余数

nvl 将空值赋值为指定数据

查询学生成绩，并且将空值赋值为0
select nvl(score, 0) from student;

nvl2：有三个参数：（目标数据，将目标数据中的非空的值赋值，将目标数据中的空值赋值）

查询学生姓名和班级，并且将为空的班级改为'没有'，将不空的班级改为'有'
select name, nvl2(class, '有', '没有') from student;

decode：类似于java中的else if（）

查询出学生成绩，如果学生班级为1，那么成绩加十分，如果学生班级为2，那么 成绩减十分，否则，成绩乘以2.
select score, decode(class, 1, score + 10, 2, score - 10, score * 2) from student

case 条件语句

select score, decode(class, 1, score + 10, 2, score - 10, score * 2) from student
可替换成：
select score, case class when 1 then score + 10 when 2 then score – 10 else score * 2 end from student

4.聚合函数

聚合函数对一组值执行计算并返回单一的值。

max 最大值

min 最小值

count 计数（不计空值）select count(rowid) from emp

sum 求和

avg 平均值（常与nvl函数搭配使用）

注：聚合函数不识别空值，根据情况使用nvl函数去除空值

5.rowid(伪列)

伪列是默认存在于表中的一个字段，里面存放的数据是每条数据的物理储存地址。

应用：因其每条数据必定对应一个储存地址，所以永远非空，常与count函数搭配使用。

select rowid,no,name, rownum  from emp

6.rownum(伪字段)

rownum 伪字段，为生成的结果集编号

特点：调用时只能从1开始生成，不能从其他数字开始

如果不查询rownum，那么rownum是不存在的。

作用：可以为每一条数据进行编号，每次都从1开始编号。rownum编的号，进行分页查询。

只能用于=1，<,<=

7.日期函数

to_date(数据，格式)

insert into test(time) values(to_date('12340506','yyyymmdd'));

to_char(数据，格式)

select to_char(time,'yyyymmdd') from test;

四、存储过程(PL/SQL)

Oracle 数据库(二)

五、其他

笛卡尔积错误

由于连接条件缺失或者错误，造成的数据冗杂或无效，这种现象，就叫做笛卡尔积错误。

数据库对象：

表，约束，视图，索引，序列

1.约束

约束是保证数据完整性（一致性、正确性），使数据更加符合业务规则的一种方式。

保证数据完整性有三种方式：①约束②函数③触发器

约束因其便利及高效，常作为保证数据完整性的首选方式。

约束的种类：

主键约束（primary key）：限制字段下数据唯一且不为空，并默认生成该字段的索引。

外键约束（foreign key）：与主键或唯一约束进行关联。

检查约束（check）： 可以将所有写在where后的限制条件写在检查约束中，作为检查约束规定的内容。

唯一约束（unique）： 限制字段下的数据不能重复。

非空约束（not null）： 限制字段下数据不能出现空值。

默认约束（default）： 向字段添加空值时，将其改为默认值。

主键约束和外键约束的关系？

①主键约束有的值，外键可以有；但主键没有的值，外键不能有。

②删除主键的数据，外键中相应的数据一同删除（连锁）；删除外键的数据，主键不删除。

③删除主键约束时，要先将与之关联的外键约束先删除。

2.创建表格时手动创建约束

create table test(
	no number primary key,				--主键约束
	name varchar2(30) not null,			--非空约束
	score number default -1,			--默认约束
	tel number unique,					--唯一约束
	sex varchar2(3) check(sex=’男’ or sex=’女’)--检查约束
)
create table test5
(
    no number,
    id number,
    constraint fk_test5_no foreign key (no) references test4 (no) on delete cascade
)
--创建表格时，手动创建约束（带着约束名，工作常用此方法）
create table easy031.test4
(
    no number not null,			
    name varchar2(30) not null,
    score number default -1,
    tel number,
    sex varchar2(30),
    constraint pk_test4_no primary key (no),
    constraint ck_test4_sex check (sex in('男', '女')),
    constraint uk_test4_tel unique (tel)
)

3.追加约束

语法：alter table 表名 add constraint 约束名约束类型 (约束的字段)

--主键约束
alter table test7 add constraint pk_test7_no primary key(no) 
--唯一约束
alter table test7 add constraint uk_test7_tel unique (tel) 
--检查约束
alter table test7 add
	 constraint ck_test7_sex check (sex in('男', '女')) –>[check(sex=’男’ or sex=’女’)]
--外键约束
alter table test8 add constraint fk_test8_no foreign key (no)
	 references test7 (no) on delete cascade

4.删除约束

--删除主键、外键、唯一、检查
alter table 表名 drop constraint 约束名

5.修改约束

--修改非空约束
alter table test7 modify name null
--修改默认约束
alter table test7 modify score default ''

6.重命名约束

alter table 表名 rename constraint 原约束名 to 新约束名

7.启用约束&禁用约束

--禁用约束
alter table 表名 disable constraint 约束名
--启用约束
alter table 表名 enable constraint 约束名

2.序列

自动生成等间隔数字的工具，叫序列。

序列的命名规范： seq_表名_字段名

生成序列下一条值：序列名.nextval

生成序列当前值：序列名.currval

可以设置序列的开始值，设置序列每次增加的值

手动创建序列

create sequence 序列名
	start with 1001		--初始值（不写，默认1）
	minvalue 1000		--最小值（不写，默认没有最小值）
	maxvalue 50000		--最大值（不写，默认没有最大值）
	increment by 1		--自增区间（不写，默认1）
	cycle				--循环（不写，默认不循环(nocycle)）
	order				--顺序保证（不写，默认不保证顺序）
	cache 30				--缓冲（不写，默认20）

cache的优劣势

cache，缓冲，也称作预分配空间。

优点：降低系统性能压力。

缺点：如果cache值过大，在关闭数据库时，会清空缓存，缓存区中未使用的序列号丢失，造成数据浪费。
如果cache值过小，达不到降低性能压力的目的。

修改序列

已经创建好的序列，初始值无法更改。
		alter sequence 序列名
	minvalue 1000		--最小值（不写，默认没有最小值）
	maxvalue 20001		--最大值（不写，默认没有最大值）
	increment by 2		--自增区间（不写，默认1）
	nocycle				--循环（不写，默认不循环(nocycle)）
	order				--顺序保证（不写，默认不保证顺序）
	cache 30			    --缓冲（不写，默认20）

删除序列

drop sequence 序列名

3.事务处理

1.提交事务（commit）

是将缓存区中未提交的数据存入数据库中。

2.回滚事务（rollback）

将缓存区中未提交的数据清空。

3.设置回滚点（savepoint）

insert into test3 values(1003);

savepoint s1;

insert into test3 values(1004);

rollback to s1;

select 结果是 1003 ，没有1004

rollback;

select 结果1003也没了。

4.视图

将有用的数据提取出来，装入一个虚拟表，这个表就叫做视图。

特性：对视图的操作同对表的操作。

不存放数据，只存放视图的定义信息，修改视图中的数据相当于修改源表中的数据。

作用：

①简化SQL语句。

②限制访问，维护数据隐私。

③视图可以控制权限。

创建视图

create view 视图名 as
  		select 查询语句
	with read only  --只读（不写，默认可读可写）
删除视图的方法：
drop view 视图名;

授权指定用户

grant view 视图名 to 账户名；

5.索引

对表中数据按一定规则计算，大大加快查询速度的结构。

作用：大大的加快查询速度。

劣势：

建索引时，需要对现有数据进行计算，数据越多，创建越慢。索引占用数据库很大的物理储存空间。

数据库进行DML操作(增删改)时，会对变更的数据进行动态维护，降低DML操作的效率。

6.触发器

当对数据库进行DML操作时，为了简化操作，可以使用数据库提供的触发器机制完善。
   常用的触发器：
   增加时，可以通过触发器对主键列进行序列值的添加。
   删除时，可以将原表中的记录删除，同时备份到一张备份表中。

触发器类别：
   1.行级触发器
       当进行DML操作时，执行一句，触发器触发一次
   2.语句级触发器
       当进行DML操作时，一部分语句执行完毕后，触发器才执行

create [or replace] trigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 			pl/sql语句
end
触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before：表示在数据库动作之前触发器执行;
after：表示在数据库动作之后触发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库插入会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删除会触发此触发器。
表 名：数据库触发器所在的表。
for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

7.imp和exp

exp导出

	rows:		    导出数据行  		默认y
	indexes:	    导出索引 		(Y)
	compress:       是否压缩导出的文件	(Y)
	buffer          数据缓冲区的大小
	feedback        显示每 x 行 (0) 的进度
	full            导出整个文件 		(N)
	file            输出文件	 	(EXPDAT.DMP)
	log             屏幕输出的日志文件
	owner           导出指定的所有者用户名列表
	TABLES          导出指定的表名列表
	query           选定导出表子集的子句

1.将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
   exp system/manager@TEST   rows=y  indexes=y compress=n buffer=65536  
	feedback=100000 full=y  file=d:\daochu.dmp  log=d:\daochulog.txt   
	owner=(ECC_BIZ,ECC_CUSTOMER)
2.将数据库中system用户与sys用户的表导出
   exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3.将数据库中的表table1 、table2导出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)
4.将数据库中的表table1中的字段filed1以”00″打头的数据导出
   exp system/manager@TEST file=d:\daochu.dmp tables=(table1) 
	query=\” where filed1 like &apos;00%&apos;\”

imp导入

	ignore		忽略创建错误			默认(N) 
	file		输入文件	  		(EXPDAT.DMP)
	log		    屏幕输出的日志文件
	full 		导入整个文件	   		(N)
	fromuser	所有人用户名列表
	touser		用户名列表
	tables		表名列表

1.将D:\daochu.dmp 中的数据导入 TEST数据库中。
   imp system/manager@TEST   ignore=y  full=y   file=d:\daochu.dmp  log=d:\daoru.txt

2.导入一个完整数据库
imp system/manager file=bible_db log=dible_db full=y ignore=y

3.导入一个或一组指定用户所属的全部表、索引和其他对象
imp system/manager file=seapark log=seapark fromuser=seapark
imp system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)

4.将一个用户所属的数据导入另一个用户
imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=tank fromuser=(seapark,amy) touser=(seapark1, amy1)

5.导入一个表
imp system/manager file=tank log=tank fromuser=seapark tables=(a,b)

六、常见SQL语句

1.创建表

create table AFT_TASK
(
  TASK_ID   		VARCHAR2(10) not null,
  TASK_END_STS 		VARCHAR2(1),
  MONEY			    NUMBER(16,2),
  TX_DATE 		    VARCHAR2(8),
  UP_OP_NO      	VARCHAR2(10),
  UP_DATE 		    VARCHAR2(8),
  CONSTRAINT PK_AFT_TASK PRIMARY KEY (TASK_ID)
);

comment on table AFT_TASK is 'XX任务表';
comment on column AFT_TASK.TASK_ID is '任务编号';
comment on column AFT_TASK.TASK_END_STS is '任务完成状态';
comment on column AFT_TASK.MONEY is '收入金额';
comment on column AFT_TASK.TX_DATE is '登记日期';
COMMENT ON COLUMN AFT_TASK.UP_OP_NO IS '更新人';
COMMENT ON COLUMN AFT_TASK.UP_DATE IS '更新日期';

CREATE SEQUENCE AFT_TASK_SEQ 
INCREMENT BY 1 
START WITH 100000000 
NOMAXvalue 
NOCYCLE 
NOCACHE;

2.修改表

alter table  AFT_TASK add AFT_TASK_NAME VARCHAR2(80);
alter table AFT_TASK modify AFT_TASK_NAME VARCHAR2(200);

3.注释

comment on table AFT_TASK is 'XX任务表';
comment on column AFT_TASK.TASK_ID is '任务编号';

4.主键/联合主键

alter table AFT_TASK add constraint AFT_TASK_PK primary key (TASK_ID);//增加联合主键，(TASK_ID,字段2)

通过IP连接oracle数据库

后续补充

杀神lwz

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
Oracle 数据库(一)

一、数据库简介数据库（Database）：指的是以一定的方式存储在一起，能为多个用户共享，具有尽可能小的数据冗余特点，与应用程序彼此独立的数据集合。常见的数据库：Oracle 美国Oracle公司适用于所有主流平台运行 DB2 美国IBM公司适用于所有主流平台运行 SQL Server 美国Microsoft公司只能在Windows上运行 My SQL 瑞典My SQL公司...
复制链接

扫一扫