数据库oracle2

最新推荐文章于 2024-07-25 10:44:15 发布
最新推荐文章于 2024-07-25 10:44:15 发布
阅读量155 收藏
点赞数
分类专栏： 数据库的入门玩法 文章标签： oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yuan_Xiao_Mu/article/details/107561718
版权

数据库oracle2

99语法

笛卡尔积  cross join

select * from emp,dept;

select * from emp cross join dept;

查询员工以及这个员工所在的部门信息

等值连接

自然连接 natural join: 自动把同名列|主外键 做等值连接

注意:同名字段不能指明出处

select deptno ,dname,ename from emp natural join dept;

join…using(同名字段)使用指定的同名字段做等值连接

select deptno,dname,ename from emp inner join dept  using(deptno);

join…on 等值|非等值连接 连接条件由你自己指定 (先连接再过滤)

select e.deptno, dname, ename
  from emp e
  join dept d
    on e.deptno = d.deptno
 where e.deptno = 30;

查询员工以及员工的薪资等级信息

select * from emp e join salgrade s on e.sal between s.losal and s.hisal;

查询30部门的员工信息以及所在的部门信息 —先过滤再连接

select *
  from (select * from emp where deptno = 30)
 cross join (select * from dept where deptno = 30);

查询30部门的员工信息

select * from emp where deptno = 30;

查询30部门的部门信息

select * from dept where deptno = 30;

查询员工信息,所在的部门信息,薪资等级信息

select *
  from emp e
  join dept d
    on e.deptno = d.deptno
  join salgrade s
    on e.sal between s.losal and s.hisal;

–员工的信息 以及 上级经理信息 e1员工表 e2经理人表

select * from emp e1 inner join emp e2 on e1.mgr = e2.empno;

–以上都是内连接 (inner) join 内连接
–外连接
–确定主表: 主表中的数据无论是否满足条件都会显示
–左外连接 left join : 主表在左边
–右外连接 right join : 主表在右边
–全连接 : full join 两张表都作为主表

select * from emp e1 left join emp e2 on e1.mgr = e2.empno;

select * from emp e1 right join emp e2 on e1.mgr = e2.empno;

select * from emp e1 full join emp e2 on e1.mgr = e2.empno;

rowid

1、rowid是Oracle数据库中特有的，在其它数据库中没有。rowid【行的标识】

2、rowid是一张表中真正存在的一个字段。并且rowid是和表中记录存在绑定关系的。每一张表都有rowid字段。

3、rowid是：行的id，是一张表中某条记录的在硬盘上存储的真实物理地址。通过rowid查询表中的记录不需要对表进行扫描，直接从硬盘上读取该数据，因为有硬盘的真实物理地址。

作用：可以为表中的数据做去重，可以根据每条数据的rowid进行区分，如果表有主键,可以根据主键或者唯一字段去重

select deptno,dname,loc,rowid from dept;

示例：select* from tb_student;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dHLxaJeH-1595574657123)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\1595509502684.png)]

​ insert into tb_student values(1,‘张三’,‘语文’,81);
​ insert into tb_student values(2,‘张三’,‘数学’,75);
​ insert into tb_student values(3,‘李四’,‘语文’,81);
​ insert into tb_student values(4,‘李四’,‘数学’,90);
​ insert into tb_student values(5,‘王五’,‘语文’,81);

​ select* from tb_student;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JjHs5Z1u-1595574657129)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\1595509682971.png)]

表中多条重复数据只保留一条，其余删掉

找到那些要保留的数据

select min(rowid) from tb_student group by id,name,course,score;

找到那些要删除的数据

select rowid 

​	from tb_student

where not rowid in

​	(select min (rowid) from tb_student group by id;)

删除这些数据

delete from tb_student
 where not
        rowid in
        (select min(rowid) from tb_student group by id, name, course, score);

rownum

rownum是什么？
* Oracle数据库中特有的，其它数据库中没有这种机制。
* rownum是行号，行号从1开始，以1递增。
* rownum只在DQL语句当中存在
* rownum是专门为查询结果集生成的行号
* 查询结果集出来之后，再为查询结果集动态生成行号。

作用: 用来做分页,保证数据根据指定规则排序之后,rownum也要保证规律,从1开始,每次+1

查询员工信息, 根据薪资进行排序,降序排序, 每页显示num = 3条数据,显示第i = 2页数据

嵌套的select为了让rownum有顺序有规律,先排序再确定rownum

select empno, sal , rownum num
  from (select empno, ename, sal, rownum n from emp order by sal desc);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-COSr9bjM-1595574657132)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\1595510437609.png)]

又嵌套的select目的: 为了在where判断之前就确定rownum,因为执行流程,如果当前这次select的结果集还没确定,没办法做判断 先走from–where–select

select *
  from (select empno, sal, rownum num
          from (select empno, ename, sal, rownum n from emp order by sal desc))
 where num >= 4
   and num <= 6;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Uye93D2m-1595574657136)(C:\Users\dell\AppData\Roaming\Typora\typora-user-images\1595510901595.png)]

根据不同的字段排序,排序和rownum顺序可能不同,如果rownum没有规律,就在外面嵌套一个select,外部select的rownum就是有规律的

索引

索引: sql优化|调优
–作用: 加快查询效率, 大量的根据表中的某个字段做查询时候,可以为这个字段添加索引

​ --索引是透明的,使用完全没有区别

​ --相当于数据库的对象之一,不是自己维护的

​ --如果数据量小,或者经常做增删改,就不建议 为该字段添加索引,因为对象需要维护

​ --建议添加索引的字段不要出现数据值大量重复的情况,索引使用的效果就不明显

​ --oracle会自动为主键添加索引

​ --索引也是存储在硬盘当中的，也是需要维护的，所以字段上不能随便添加索引

​

创建索引

create index emp_ename_index on emp(ename); //给emp表中的ename字段添加一个索引，索引起名emp_ename_index
create index 索引名 on表名 (字段列表...) drop index 索引名 
create index index_emp_sal on emp(sal);

删除索引

drop index emp_ename_index;

索引也有一张系统表：user_indexes

​ user_tables 系统表描述用户表
​ user_constraints 系统表描述约束信息
​ user_indexes 系统表描述索引信息

视图

–create or replace view 视图名 as 结果集;
create or replace view vw_emp_haha as select empno,ename,sal from emp where deptno in(10,30);
create or replace view vw_student as select * from tb_student;

/*
、授权: grant dba to scott; 回收: revoke dba from scott;
*/
grant dba to SCOTT; 回收: revoke dba from SCOTT;

–从视图中查询数据
select * from tb_student;

select * from vw_student;

–删除原表
drop table tb_student;
–删除视图
drop view vw_student;

update vw_student set score = 100 where name=‘张三’;

表设计 及 定义 ddl

– 根据业务,通过项目组分析设计–>开发文档–>创建

– 博客网站
– 用户表(作者)
– userid,username,userpwd…
– 文章表
– id,userid,时间…

–创建表 (不添加约束)
create table 表名(
字段名 类型,
…
字段名 类型
)

create table haha(
hahaid number(3),
hname varchar2(15)
)

–插入测试数据
insert into haha values(123,‘张三风哈哈’);
insert into t_student values(112,‘abc’,18,‘男’,13000000000);

select * from haha;

–主键约束 非空约束 检查约束 唯一约束 默认约束 default(默认值) 外键约束

–创建表同时添加约束(1.默认名字 2.指定名字)

create table t_student(
       --主键约束
       sid number(3) primary key,
       --非空约束
       sname varchar2(12) not null,
       --检查约束 (0~150)
       sage number(3) check(sage between 0 and 150),
       --检查约束
       sgender char(1 char) check(sgender in ('男','女')),
       --唯一
       sphoneNum number(11) unique not null
)

–创建表 的同事指定约束名字,直接添加在字段的后面,不需要指定为哪一个字段添加约束

create table t_student(
       --主键约束
       sid number(3) constraints pk_student_sid primary key,
       --非空约束
       sname varchar2(12) constraints snameNotNull not null,
       --检查约束 (0~150)
       sage number(3) check(sage between 0 and 150),
       --检查约束
       sgender char(1 char) check(sgender in ('男','女')),
       --唯一
       sphoneNum number(11) unique not null
)

–创建表 的同事指定约束名字 ,需要指定为哪一个字段添加约束,表结构结束之前

create table t_student(
       --主键约束
       sid number(3) constraints pk_student_sid primary key,
       --非空约束
       sname varchar2(12) constraints snameNotNull not null,
       --检查约束 (0~150)
       sage number(3) check(sage between 0 and 150),
       --检查约束
       sgender char(1 char) check(sgender in ('男','女')),
       --唯一
       sphoneNum number(11),

   	constraints number_check check(sphoneNum is not null)   

)

–创建表 之后追加约束

alter table t_student add constraints name_check check(length(sname)>=3) ;

–删除约束

alter table t_student drop constraints name_check;

DML语句

insert into 表(指定列) select 查询列 from 源表 where 过滤数据;
insert into 表(指定列) values(值列表);
insert into 表名 select 查询列 from 源表 where 过滤数据;
insert into 表名 values(值列表 );

update 表名 set(字段列表)=(select 字段列表 from 源表 where 过滤源表记录 ) where 更新记录的条件 手动更改字段值：
update 表名 set 字段=值 [,…] where 过滤行记录;

–删除数据 一个 多个 所有
delete from 数据源 where 条件;

–delete 数据截断truncate 区别
–删除表中的所有数据
–1) delete 默认开启事务 删除主表中数据—>三种解决方案
–2) 数据截断truncate 不会开启事务 从表结构上检查,是否有被引用,存在被引用就不能数据截断
delete from t_class;
truncate table t_class;

序列

序列-sequence

1、序列是Oracle数据库当中特有的，专门用来维护一个自增的数字，默认情况下从1开始，以1递增。
类似于mysql当中的auto_increment

2、创建序列对象
create sequence myseq;

3、删除序列对象
drop sequence myseq;

4、序列对象也有一张系统表：user_sequences

规律：user_XXXs

5、序列对象都有两个属性：
* nextval：从序列当中获取下一个值
* currval：从序列对象当中获取当前值

6、案例：
create table t_customer(
id number(10) primary key,
name varchar2(32)
);

create sequence myseq;

insert into t_customer(id,name) values(myseq.nextval,'jack1');
insert into t_customer(id,name) values(myseq.nextval,'jack2');
insert into t_customer(id,name) values(myseq.nextval,'jack3');
insert into t_customer(id,name) values(myseq.nextval,'jack4');
insert into t_customer(id,name) values(myseq.nextval,'jacka');
insert into t_customer(id,name) values(myseq.nextval,'jackb');
insert into t_customer(id,name) values(myseq.nextval,'jackc');

commit;

select * from t_customer;

7、注意：Oracle中的sequence是一个共享的对象。所以有的时候会出现断号现象，
但是断号不是问题，因为主键只要不重复即可，没必要连续。

8、创建一个完整的序列对象：

create sequence my_seq_01
start with 100
minvalue 100
maxvalue 1000

主外键约束删除

--学生表 班级表
--班级表 -->主表
create table t_class(
       cid number(5) primary key,
       cname varchar2(10 char) not null     
);
--学生表 -->从表
create table t_student(
       sid number(3) primary key,
       sname varchar2(12) not null,
       --外键约束 这个学生所在的班级编号 班级表的主键
       cid number(5) ,--references t_class(cid)

  -- constraints 约束名 foreign key(字段) references t_class(cid)
   constraints fk_student_cid foreign key(cid) references t_class(cid) on delete set null

);

–在主从表关系下删除表
–删除表
–删除从表可以直接删除
drop table t_student;
drop table t_class;
–删除主表
–不能直接删除主表,默认要求先删除从表再删除主表
–删除主表的同时,删除主外键约束关系
drop table t_class cascade constraint;
–在主从表关系下删除数据
–删除主表中数据:
–删除没有被从表引用的主表数据
delete from t_class where cid = 41;
–删除被从表中数据所引用的主表数据时: 3种解决方案
delete from t_class where cid = 40;
–1) 默认先删除从表中引用了当前主表数据的从表数据
delete from t_student where cid = 40;
–2) 删除主表数据的同时,就把从表中引用了当前主表数据的从表数据一起删掉 on delete cascade
–2) 删除主表数据的同时,就把从表中引用了当前主表数据的从表数据外键字段设置为null on delete set null

–注释

comment on table t_class is '班级表'; 
comment on column t_class.cid is '班级编号,主键'; 
comment on column t_class.cname is '班级名称';

–约束开启和禁用

ALTER TABLE t_student disable constraint fk_student_cid; 
ALTER TABLE t_student enable constraint fk_student_cid;

事务

事务是指作为单个逻辑工作单元执行的一组相关操作。这些操作要求全部完成

或者全部不完成。使用事务是为了保证数据的安全有效。

事务的四个特点：

​ 1、原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。

​ 2、一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，

​ 换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。

​ 3、隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。

​ 读未提交（脏读）(READ UNCOMMITTED)

​ 读提交 (READ COMMITTED)

​ 可重复读 (REPEATABLE READ)

​ 序列化 (SERIALIZABLE)
​ Oracle 中只支持：READ COMMITTED 和 SERIALIZABLE

​ 4、持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，

​ 也不应该丢失。

事务控制语言（TCL）
事务中存在一些概念：
a) 事务（Transaction）：一批操作（一组 DML sql）
b) 开启事务（Begin Transaction）
c) 回滚事务（rollback Transaction）–Oracle—rollback
d) 提交事务（commit transaction）----Oracle–commit
当执行 DML 语句时其实就是开启一个事务
关于事务的回滚需要注意：只能回滚 insert、delete 和 update 语句，不能回滚 select（回滚
select 没有任何意义），对于 create、drop、alter 这些无法回滚
Delete 和 truncate 都可以删除表中的数据。
Delete 语句删除数据之后还可以回滚
Truncate 语句删除数据之后不可以回滚
Delete 语句是 DML 语句
Truncate 语句是 DDL 语句
Truncate 语句是将表截断。
Delete from emp_bak;
生系统故障，

​ 也不应该丢失。

事务控制语言（TCL）
事务中存在一些概念：
a) 事务（Transaction）：一批操作（一组 DML sql）
b) 开启事务（Begin Transaction）
c) 回滚事务（rollback Transaction）–Oracle—rollback
d) 提交事务（commit transaction）----Oracle–commit
当执行 DML 语句时其实就是开启一个事务
关于事务的回滚需要注意：只能回滚 insert、delete 和 update 语句，不能回滚 select（回滚
select 没有任何意义），对于 create、drop、alter 这些无法回滚
Delete 和 truncate 都可以删除表中的数据。
Delete 语句删除数据之后还可以回滚
Truncate 语句删除数据之后不可以回滚
Delete 语句是 DML 语句
Truncate 语句是 DDL 语句
Truncate 语句是将表截断。
Delete from emp_bak;
Truncate table emp_bak;

Yuan_Xiao_Mu
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle示例数据库
04-13
员工表包含：employees表，departments表，locations表，job_grades表，jobs表：
数据库 oracle 数据库基础ppt
04-19
数据库 oracle 数据库基础ppt
Oracle DBLINK 简单使用
weixin_30254435的博客
01-28 512
oracle在进行跨库访问时，可以通过创建dblink实现，今天就简单的介绍下如果创建dblink，以及通过dblink完成插入、修改、删除等操作 首先了解下环境：在tnsnames.ora中配置两个数据库别名：orcl(用户名：wangyong 密码：1988)、orcl2(用户名：wangyong 密码：123456)，在orcl中 创建datab...
oracle数据库怎么创建数据库 oracle数据库工作流程
xiaoweids的博客
09-20 5811
根据你所导入的数据，创建相应的表空间（切记要授予unlimited tablespace，否则当使用达到配额之后，无法往数据文件里再写入了），然后创建用户，导数之前建议这些用户都赋予dba的角色，然后就是导入数据，数据正确导入之后，再根据实际情况给予用户需要赋予的权限以及角色。imp system用户/system的密码@服务器名 file=oradial.dmp fromuser(用户名)=oradial touser(用户名)=abc。(2) owner用户名，通过sys用户的登录所创建的用户名。
oracle重启数据库
blood_Z的博客
08-12 1万+
oracle重启数据库； 启动数据库； 关闭数据库
Oracle数据库安装
qq_44275015的博客
09-15 8862
Oracle数据库安装
oracle怎样导入数据库
xiaoweids的博客
09-19 4084
PL/sQL developer是oracle数据库中用于导入或导出数据库的主要工具，本文主要介绍了利用PL/sQL developer导入和导出数据库的过程，并对导入或导出时的一些注意事项进行了说明，接下来我们就一一介绍。第三种是导出为.pde格式的，.pde为Pl/sql developer自有的文件格式，只能用Pl/sql developer自己导入导出，不能用编辑器查看。第一种是导出为.dmp的文件格式，.dmp文件是二进制的，可以跨平台，还能包含权限，效率也很不错，用得最广。
重启oracle数据库
qq_33968544的博客
08-24 9081
重启oracle数据库一、进入oracle数据库二、连接客户端三、执行连接数据库四、关闭与启动数据库注：记数据库连接异常ORA-28001: the password has expired 一、进入oracle数据库 执行命令进入oracle用户 su - oracle; 二、连接客户端 sqlplus /nolog; 三、执行连接数据库 conn sys / as sysdba; 四、关闭与启动数据库 //关闭数据库 shutdown immediate; //启动数据库 startup; 注：
Oracle数据库--重启
weixin_55282974的博客
03-07 5846
Oracle数据库--重启
Navicat连接Oracle数据库
热门推荐
liu_Leslie的博客
04-29 7万+
Navicat连接Oracle数据库 文章目录Navicat连接Oracle数据库1、所用软件版本2、连接步骤3、注意事项==1、Oracle library is not loaded 错误==解决方式==2、ORA-12514错误==解决方式 1、所用软件版本 Navicat：Navicat Premium 12.1.20 Oracle：Oracle Database 10g Express Edition Release 10.2.0.1.0 windows：Windows 10 企业版 G 2、
Oracle数据库完整卸载
qq_44275015的博客
09-07 1万+
Oracle数据库完整卸载
Oracle数据库scott数据库
02-26
Oracle数据库scott数据库表结构与数据
Oracle数据库基础知识
07-26
Oracle数据库基础知识 Oracle数据库基础知识是指对Oracle数据库的基本概念、结构、管理和操作的了解。Oracle数据库是一种关系型数据库管理系统，由 Oracle Corporation 开发和维护。 课程目标： * 了解Oracle...
ORACLE数据库安装配置
最新发布
07-29
ORACLE数据库安装配置
Oracle创建数据库以及使用
qq_42859722的博客
10-26 2702
任意输入一个用户名以及密码，选择创建好的数据库名称，连接选择SYSDBA的方式登录。登录后PLSQLDeveloper界面如下图所示。
Oracle对数据库行和数据库的监控
奋斗的阿杰的博客
07-25 805
本文是实现Oracle对数据行、数据库的监控，仅供参考
Oracle配置TCPS加密协议测试
the_coco的博客
07-25 589
编辑“$ORACLE_HOME/network/admin/listener.ora”文件，添加wallet信息以及TCPS内容。参考文档：https://www.cnblogs.com/zhj5418/p/14957264.html。将以下内容加入到“$ORACLE_HOME/network/admin/tnsnames.ora”文件。将以下内容添加到“$ORACLE_HOME/network/admin/sqlnet.ora”文件中。Oracle配置tcps加密连接已经配置成功。操作系统：Linux。
mysql常用命令
p_s_p的博客
07-22 1214
mysql常用命令
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值