导图社区 oracle数据库
- 18
- 举报
oracle数据库
这是一个关于Oracle数据库的思维导图，全面覆盖了数据库架构、SQL语言、PL/SQL编程、数据库管理、性能优化及安全策略等关键领域。对部分数据库的内容进行摘录，希望对大家有所帮助。
编辑于2024-08-25 14:08:34- 数据库
- 总结
- 相似推荐
- 大纲
oracle数据库
数据库介绍
数据库：屋里操作系统文件或者磁盘的集合
数据库+数据库操作系统
oracle实例
位于物理内存的数据结构，是由操作系统的多个后台进程和一个共享的内存池锁组成，共享的内存可以被所有进程访问
存储结构
物理存储
逻辑存储
数据库
表空间
段
区
oracle数据块
默认账户
system
sys
创建用户和表空间
sys登录
conn sys/root as sysdba
select instance_name from v$instance;
创建表空间
create tablespace 表空间名 datafile ' 路径\文件名.dbf' size 200m;
创建了一个tds文件
创建用户
create user scott identified by tiger default tablespace scott_tb_space;
create user 用户名 identified by 密码 default tablespace 表空间;
创建用户并指定表空间
授权权限
grand dba to scott;
grand dba to 用户名;
普通用户登录
conn scott/tiger@xe
select * from dual;
表设计
表
数据库中以表位组织单位存储数据，用来存储一些事物的信息；
表名+存储信息
设计原则
约束
主键约束
primary key
用于定位表中单个行的方式，可唯一确定表的一行
非空且唯一
一个表只能一个主键
唯一性约束
unique
保证每一行的唯一性，但允许多个空值
非空约束
not null
非空
外键约束
foreign key
主表
被参考的表
从表
参考的表，
参考的主表必须按照主表要求来
检查约束
check
客户端安装和准备表和数据
客户端安装
acott.dbs
SQL语言介绍 structured Query Language
select * from t1
DDL data definition languages
数据定义语言
定义了不同数据段，数据库，表，列，索引等数据库对象，
create、drop、alter、rename、truncate
常常有数据库管理员（DBA)使用
用在定义或改变表的结构、数据类型、表之间的连接和约束等初始化工作上，大多数在表的建立时使用
操作
创建表
创建一张表
create table name( 字段名 类型（长度），.....);
从其他表拷贝结构
create table name1 as select 字段列表 from 已有表 where 1!=1;
修改表结构
表名修改
rename 原表名 to 新表明
修改列名
alter table 表名 rename column 列名 to 新列名
修改字段类型
alter table 表名 modify(字段 类型）
添加列
alter table 表名 add 字段 类型
删除列
alter table 表名 drop column 字段
删除表
drop table 表名;
DML data manipulation language
数据操纵语句
用于添加、删除、更新和查询数据库记录，并检查数据完整性
insert、delet、update、select
对标内部进行操作
DCL data control language
数据控制语句
用于控制不同数据段直接的许可和访问级别的局域
定义了数据库、表、字段、用户访问权限和安全级别
grant、revoke回收权利、commit提交事务、rollback回滚事务
select
表示查询
select *|colname[,...] from table [alians]
*可以用所有内容的具体信息进行替代
select deptno,dname from dept;
去重
instinct
select distinct deptno from emp
别名
方法1
不需要as
select ename,sal from emp;
select ename 姓名，sal 工资 from emp;
方法2
as
select ename as 姓名， sal as 工资 from emp；
排序
order by
select ename,sal from emp order by sal desc;
select ename , sal , deptno from emp order by deptno asc, sal desc;
执行顺序
处理空值
nvl（参数1，参数2）
第一个参数不为空时返回的数，第二个参数，为空时返回的数
select ename, sal, comm, sal+somm as 月收入, from emp;
当comm 为空时，sal也为空，影响数据结果
select ename, sal, comm, sal+somm + nvl(comm,0) as 月收入, from emp;
select nl(1,100) from dual;
select nvl(null,100) from dual;
select * from emp where vvl(comm,0)<=0;
nulls first/nulls last
用于排序
select * from emp order by comm desc nulls first/nulls last;
控制判断
is null
is not null
not .. is null
伪列和虚表
伪列
本不存在一列，然后根据需求再临时添加一列
查询年薪
select ename, sal, 1,from emp; select ename , sal, sal*12 as 年薪 from emp;
字符串的拼接
||
select ename from emp;
select ename, ename ||"a" 别名 from emp;
select ename ,comm,ename || comm as test from emp
当有null时，会不拼接
虚表
dual 虚拟表
用于构成select的语法规则，oracle保证dual里面永远只有一条数据
该表只有一行一列，
可以执行插入、更新、删除、drop操作
但如果执行drop的话，会造成系统崩溃
用于选择系统变量或者求一个表达式的值，
select 完成一些信息，需要借助一个对象时，可以用这个表
应用
计算999*666
select 999*666 from dual；
查询条件
条件运算
select * from table where
分类
=
>
<
>=
<=
!= 或 <> 或 ^=
between .. and
闭区间
not
select * from emp where deptno =10
select ename, deptno from emp where deptno !=10
select * from emp where ename ='SMITH'
条件连接运算符
and
or
not
模糊查询
like
%
位数不定的模糊字符
_
一位模糊字符
select * from emp where like ’%S%'
特殊处理%和_
select * from emp where ename like '%a%%' escape ('a');
select * from emp where ename like '%aaa%%' escape('a')
select * from emp where ename like '%a%%a_%' escape('a'）
where 子句
select * from where group by ... having ... order by ...
select * from emp where deptno = (select deptno from dep where dname = 'SALES')
select * from salgrade where sal between (select losal from salgrade where grade = 2) and (select hisal from salgrad where grade =2)
group by 分组
select ... from .... where ... by ...
select avg(sal) from emp group by deptno;
select count(*) from emp group by deptno;
having 过滤
select deptno. coumt(*), from emp where sal >2000 group by deptno having count(*) >=2;
函数
介绍
单行函数
在对应表记录时，一条记录返回一个结果
字符串函数
concat(x,y)
连接字符串x和y
select ename||job as namejob from emp;
select concat(ename,job) from emp;
instr（x,str,start,n)
在x中寻找str，可以指定从start开始，可可以从指定从第n次开始
返回字符串的位置
select instr('helloworld' ,'e') from dual
select instr('helloworld' ,'a') from dual
select ename, instr(ename,'A') from emp;
length(x)
返回x的长度
lower（x）
x转化为小写
upper（x）
x转化为大写
ltrim（x, trim_str)
把x左边截去strim_str字符串，缺省截去空格
select ltrim(' abc abc ') from dual
rtrim（x, trim_str)
把x右边截去strim_str字符串，缺省截去空格
select rstrim(' abc abc ')||'a' from dua;
select rstrim(lstrim(' abc abc ')) from dual
select concat(rstrim(lstrim()),'a') from dual;
replace(x,old ,new)
从x中查找old ，并替换为new
substr( x, start,length)
返回x字符串，从start开始，截取length个字符，缺省length，默认到结尾
数学函数
abs（x)
取绝对值
ceil(x)
向上取整
floor
mod(x,y)
日期函数
sysdate
当前系统时间
无括号
select sysdate from dual;
select sysdate+10 from dual;
current_date
返回当前系统日期
无括号
add_months(d1,n1)
返回在di基础上再加上n1个月以后的新日期
selct empno, ename, hiredate, add_months(hiredate,3) from emp;
selct empno, ename, hiredate, add_months(hiredate,-3) from emp;
last_day(d1)
返回日期d1坐在月份最后一天的日期
select last_day(hireday) ,hiredauy from emp;
months_between(d1,d2)
返回日期d1和日期d2之间的月数
select sysdate, hiredate, months_betweem( sysdate, hiredate)from emp;
select sysdate, hiredate, months_betweem(hiredate, sysdate )from emp;
复数
next_day(d1,[,c1])
返回日期d1在下周，星期几c1，的日期
select next_day(sysdate,'星期一') as 入职时间 from dual;
转换函数
to_char(x,c)
将日期或数字x按照c的格式转化成char数据类型
select hiredate ,to_char(hiredate, 'mm'-'dd'-'yyyy') from emp;
select hiredae, to_char(hiredate, 'mm"月"dd"日" yyyy "年"') from emp;
co_date(x,c)
将字符串x按照c的格式转化成日期
select to_date('1900/1/1', 'yyyy//mm/dd')
co_number(x)
将字符串x转化成数字型
select to_number('11') +1 from dual;
多行函数
也称组合函数或聚合函数
可同时对多条记录进行操作，并返回一个结果
avg()
sum()
min()
max()
count()
select count(nvl(comm,0) ) from emp;
注： null 不参与运算
分页
列表内容太多，用分页进行显示
方案
一次查询出数据库的所有记录，然后在每页汇总显示指定的记录
假分页
对数据库进行多次查询，每次获得本页的数据并显示
rownum
对每一个结果集中的每一条记录的编号，从1开始
select ename , sal, deptno ,rownum from emp;
select ename, sal,deptno, rownum from emp where deptno = 30;
select ename, sal,deptno from emp where rownum <=5;
查询第一页数据，每页5条数据
select ename , sal, deptno from emp where row <= 5;
查询第二页，
rownum 永远从1 开始
select ename, sal, deptno, rownum from emp;
产生了一个伪列
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >1;
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >5 and rw <=10;
select ename, sal, deptno, r1 r2 from) select ename, sal, deptno r1, rownum r2 from( select ename, sal, deptno, rownum r1 from emp order by sal desc )) where r2 <=3;
去重
rowid
用于定位数据库中一条记录的一个相对位移的地址值
通常情况下，该值在该行数据插入到数据库表是即被确定却唯一
是一个伪列，并不存在于表中
在oracle在读取表中数据行是，根据每一行数据的物理地址信息编码而成的一个伪列
根据一行数据的rowid 能找到一行数据的物理地址信息，从而快速定位到数据行
rowid是进行单记录定位速度最快的
重复记录的查找
create table copy as select * from dept;
select * from copy;
select deptno, dname, loc, rowid from copy order by deptno;
insert into copy select * from dept;
commit;
select min(rowid) from copy group bu deptno,dname,loc;
select * from copy where row not in (select min(rowid) from copy group by deptno,dname,loc);
delete from copy where rowid not in ( select min(rowid) from copy group bu deptno,dname,loc; )
commit;
表链接
一个表的行根据指定的条件跟另一个表的行连接起来形成新的行的过程
92语法
介绍
select .. from t1,t2,t3,... where ...
简化表明
可能存在自连接的情况
原理： 按照from 后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外城的for循环
笛卡尔积
等值连接
非等值连接
!= 、>、 <、 <>、 between and
select ename, sal, hiredate, grade from emp e, salgrade s where e.sal between losal and hisal;
自连接
使用比价运算符根据每个表共有的列匹配两个表中的行
select * from emp as e,emp as m where e.mgr = m.empno;
外连接
可以左向外连接、右向外连接或完整外部链接
只要是主表中出现的记录，必须出现在结果中
select * from dept as d, (select count(*), deptno from emp group by deptno) as c where d.deptno = c.deptno(+);
带➕的不是主表
select d.deptno, dname, loc, nul(cc,0) from dept d, (select count(*) cc, deptno from emp group by deptno) c where d.deptno = c.deptno
主表
select * from emp e, emp m where e.mgr = m.empno(+);
99 语法
cross join
交叉连接，实现笛卡尔积
select * from dept cross join emp;
natural join
要求： 同名列或主外键
自然连接，做等值连接
查询所有员工姓名及所在部门的名称
select ename, empno, deptno, dname from emp natural join dept;
select ename,deptno, dname from emp natural join dept where deptno = 10;
join using
using 连接
必须有同名列 进行连接
等值连接
select enmae,empno,deptno,dname from emp join dept using (deptnu);
select ename,deptno, dname from emp join using (dept no) where deptno = 10;
join on
on 连接，
可做等值连接、非等值连接、自连接、解决一切连接，关系列必须要区分
select ename, empno, e.deptno, dname from emp e join dept d on e.deptno = d.deptno
select ename, sal, e.deptno, grade, dname from emp e join dept d on e.deptno = d.deptno join salgrade on e.sal between losal and hisal where e,deptno = 30;
outer join
外连接，有主表和从表
left [outer] join on
left [outer] join using
right [outer] join on
right [outer] join using
full join on | using
92连接和99连接的区别
内连接
92:
select ... from t1, t2 where t1.a = t2.b and t1.c = 1
99
select ... from t1 cross join t2 where...
select ... from t1 natural join t2 where...
select ... from t1 join t2 using (同名字段） where...
select ... from t1 join t2 on 连接条件 where ...
外连接
92
select ... from t1, t2 where t1.a= t2.b(+)
99
select ... f0om t1 left/ right [outer] join t2 on/using 连接条件
全连接
99
两个表都是主表
select t1 full join t2 on 连接条件 where
资源
https://blog.csdn.net/qq_41128049/article/details/104205831
集合操作
集合分类
Union
并集
去重
对两个结果集进行并集操作，
不包括重复行，默认规则的排序
union all
全集
不去重
对两个结果集合进行并集操作，
包括重复行，不排序
interset
交集
找到重复
对两个结果集合进行交集操作，
不包括重复行，默认规则排序
minus
差集
减去重复
对两个结果集合进行差朝族
不包括重复行，默认规则排序
要求
两个结果集，字段个数和字段类型一一对应
select 'a','b' from dual; select 'c','d' from dual;
select 'a','b' from dual union select 'c','d' from dual select 'a','b' from dual;
a,b 有两个 去重
select 'a','b' from dual union select 'c','d' from dual union all select 'a' , 'b' from dual;
全集不去重
（select 'a','b' from dual union select 'c','d' from dual） intersect （ select 'a','b' from dual union select 'e','f' from dual）
a,b
（select 'a','b' from dual union select 'c','d' from dual） minus （ select 'a','b' from dual union select 'e','f' from dual）
差集
c,d
数据类型
VARCHAR2(size)
可变长度字符串
size：1：4000
NVARCHAR2(size)
可变长度的字符串
根据所选国家字符集来定义最大长度，
必须指定长度
NUMBER
LONG
DATA
RAW(size)
LONG RAW
CHAR(size)
NCHAR(size)
CLOB
NCLOB
BLOB
BFILE
创建带有约束的表
create table t1( userid number(5) primary key, username varchar2(30) check(length(username between 4 and 20) not null, userpwd varchar2(20) not null check(length(userpwd) between 4 and 18), age number(3) default(18) check(age> =18), gender char(3) default('男') check (gender in ('男','女')), email varchar2(30) unique, regtime date default(sysdate) );
create table t2 ( txtid number(5) primary key, title varchar2(32) not null check(length(title)>=4 and length(title) <= 30, txt varchar2(1024), pubtime date default(sysdate), userid number(5) reference t1(userid) on delete set null )
创建带有名字的约束
create table t1( userid number(5) , username varchar2(30) contraint user_name not null , userpwd varchar2(20) constraint not null , age number(3) default(18) , gender char(3) default('男') , email varchar2(30) , regtime date default(sysdate) constraint ke_uyser_id primary key(userid), constraint ck_user_name check(length(username) between 4 and 20) constraint ck_user_pwd check(length(userpwd) between 4 and 18), constraint ck_user_age check(age> =18), constraint ck_user_gender check (gender in ('男','女')), constraint ck_user_email unique(email) );
create table t2 ( txtid number(5) , title varchar2(32) nn_txt_title not null, txt varchar2(1024), pubtime date default(sysdate), userid number(5), constraint pt_txt_id primary key(txid), constraint ck_txt_title check (length(title)>=4 and length(title) <= 30 constraint fk_txt_user_id foreign key(userid) references tb_user(userid ) on delete set null )
创建并追加约束
例1
create table t1( userid number(5)， username varchar2(30), userpassword varhcar2(20), age number(3), gender char(2), email varchar2(30), regtime time default(sysdate) );
alter table t1 add constraint pk_us_id primary key(userid);
alter table t1 add constraint ck_user_name check(length(username) between 4 and 28);
alter table t1 add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table t1 add constraint ck_user_age check(age>=18);
alter table t1 add constraint ck_user_gender check(gender in(‘男','女'));
alter table t1 add constraint uq_user_email unique(email);
alter table t1 modify(username constraint nn_user_name not null);
alter table t1 modify(userpawd constraint nn_user_pwd not null);
alter table t1 modify (age default(18)
alter table t1 modify
例2
create table t2 ( txtid number(10), title varchar2(32), txt varchar2(1024), pubtime date, userid number(5) );
alter table t2 add constraint pk_txt_id primary key(txtid);
later table t2 add constraint ck_txt_id check(length(title) >=4 and length(title)<=30);
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid);
强制不让删
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete set null;
自动设为空
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete cascade;
级联删除
禁用和启动约束
启用，禁用（enable，disable）
是否对新变更的数据启动约束验证
验证\非验证（validate\novalidate）
是否对表中已客观存在的数据进行约束验证
enable validate
默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行
enable novalidate
无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
disable validate
可以添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
disable novalidate
可以添加违反约束的数据行，对已经存在的违反约束的数据行也不做验证
修改表结构
修改表名
rename old to new
修改列名
alter table 表名 rename column 列名 to 新列名
修改字段类型
alter table 表名modify（字段 类型）
添加列
alter table 表名 add 字段 类型
删除列
alter table 表名 drop column
删除表
drop table