oracle中的数据集合操作
发布时间：2020-12-12 13:31:25 所属栏目：百科 来源：网络整理
导读：一、数据更新操作 ?? ?? ? DML语法中主要包括两个内容：查询与更新，更新主要包括：增加数据、修改数据、删除数据。其中这些操作是离不开查询的。 ?? ?? ? 1、增加数据 ? ? ? ? ? ??语法：INSERT INTO 表名称 [(字段名称1,字段名称2，…)] VALUES(数据1，数
一、数据更新操作?? ?? ? DML语法中主要包括两个内容：查询与更新，更新主要包括：增加数据、修改数据、删除数据。其中这些操作是离不开查询的。 ?? ?? ? 1、增加数据 ? ? ? ? ? ??语法：INSERT INTO 表名称 [(字段名称1,字段名称2，…)] VALUES(数据1，数据2…..)； 注意： ????1、对于字符串需要使用""括起来 ????2、对于时间可以使用to_date（）进行转换 ????3、数字就直接写 ? 范例：向myemp表中插入一条数据 INSERT INTO myemp（empno,job,sal,hiredate,ename,deptno,mgr,comm） VALUES(6666,‘清洁工‘,2000,TO_DATE(‘1988-10-10‘,‘yyyy-mm-dd‘),‘王二‘,40,7369,null); ?? ?? ? 2、修改数据 ? ? ? ?语法：UPDATE 表名称 SET 字段1=内容1，字段2=内容2，..…[WHERE 更新条件(s)] ? 范例：要求将雇员编码为7369的雇员，工资修改为810、佣金改为100 UPDATE myemp SET sal=810,comm=100 WHERE empno=7369; ? 范例：将所有在81年雇佣的雇员的雇佣日期修改为今天，工资增长20% UPDATE myemp SET hiredate=SYSDATE,sal=sal*1.2 WHERE hiredate BETWEEN ‘01-1月-1081‘ AND ‘31-12月-1981‘; ? ? ?? ? 3、删除数据 语法：DELETE FROM 表名称??[WHERE 删除条件(s)]; 范例：删除雇员编号为7369的雇员信息 DELETE FROM myemp WHERE empno=7369; ? 范例：删除几个员工的信息（用IN） DELETE FROM myemp WHERE empno IN (7566,7788,7899); ?二、事物的处理?? ?? ? 1、事务的概念 ?? ??? ?? ??事物：是保证数据完整性的一种手段。事物具备ACID原则，保证一个人更新数据的时候，其他人不能更新。 ?? ?? ? 2、事务锁的概念 ?? ??? ?事务处理的两个核心命令： ? ? ? ? ?提交事物：commit； ? ? ? ? 回滚事物：rollback； ?? ??? ?真正使用了commit提交的时候才表示更新是可以正常完成的。所有的更新操作都需要被事务所保护。 ?? ??? ?事务锁：每一个session都有自己进行的事务处理。如果两个不同的session更新了同一个数据，在一个session未更新完成之前，另一个session是不能进行更新操作的。这个过程就叫做事务锁：在提交或回滚更新之前，只能有一个session能够对数据进行操作，实现了事物的隔离性 ?? ? ? ? 3、事务处理的思想 ?? ??? ??? ?案例：如果突然需要对所有用户的某一个字段的内容进行更新，在用户数据量足够大的情况下，那么这样就会出现一个问题： ??? ?如果直接更新，那么数据量足够大的情况下，需要的时间也是非常长的，且在更新的时间段，所有的用户不能登录系统做其他的操作，相当于系统瘫痪。这种做法是拿时间换空间 ???? 如果换一种做法：用空间来换时间：将客户分为活跃用户与非活跃用户，优先更新活跃用户的信息，或者在用户进行登录的时间进行用户操作，这是一个周期完成的事情，并不是一下子完成的。 ? 所谓的解决方法都是取决于时间复杂度或者空间复杂度，要不就是拿时间换空间，要不就是拿空间换时间，具体的做法还需结合具体的情况进行分析，但是这是一个最基本的想法。三、数据伪列?? ? ? ? 1、行号 ?? ?? ??行号是自动生成的，但是他是动态的，并不会特定的指定某些行，他是根据你查询出来的数据进行自动行生成的。 范例：SELECT ROWNUM,empno,job FROM emp; rownum的作用： 1、取得第一行的数据 2、取得前N行的数据 注意：rownum不能直接取得后几行或者中间部分的数据 ? 1、取得第一行的数据一般是用来取得表的数据结构，一般在实际的操作中，我们不会使用查询全部的语句进行操作：select * from 表名。 SELECT * FROM emp WHERE ROWNUM=1； ? 2、实现上下翻页显示 CurrentPage，表示的是当前所在页； LineSize，表示每页显示的数据行； 语法： SELECT * FROM( ????SELECT ROWNUM rn,列.... FROM 表名称 ????WHERE ROWNUM <= currentPage*lineSize) tempWHERE temp.rn>(currentPage-1)*lineSize； ?? ??3、行ID ?? ??? ?所谓的ROWID指的是针对每行数据提供的物理地址。 以”AAAR3qAAEAAAACHAAA"这个数据为例，ROWID组成： 数据对象编号：AAAR3q 数据文件编号：AAE 数据保存的块号：AAAACH 数据保存的行号：AAA ? 面试题：表中有许多完全重复的数据，要求将重复的数据删除掉（只剩最后一个）； 分析： 表中的数据列信息几乎都是一样的，所以如果按照已有的字段删除，那么最终的结果都会被删除掉。 那么现在即便数据重复了，在Oracle里面存在一个ROWID，它的物理保存地址也是不可能一样的。 在程序之中都会设计累加的操作，所以理论上来讲，最早保存数据的ROWID的内容应该是最小的。如果要想确认最小，利用MIN（）函数完成。 现在mydept表中的数据又重复，那么可以采用分组，按照重复内容分组之后统计出最小的ROWID（最早的ROWID）。 ? 第一步：查找出最小的rowid的数据 SELECT deptno,dname,loc,MIN(ROWID) FROM mydept GROUP BY deptno,loc; ? 第二步：删除rowid不在这其中的数据 DELETE??FROM mydept WHERE ROWID NOT IN( ????SELECT MIN(ROWID) ????FROM mydept ????GROUP BY deptno,loc); 四、表的创建与管理?? ??? ? 1、常用的数据类型 ?? ??? ??? ?1、字符串：使用VARCHAR2描述（其他数据库使用VARCHAR）,200个字以内的都使用此类型，例如：姓名、地址、邮政编码、电话、性别； 2、数字：在Oracle之中使用NUMBER来描述数字，如果描述小数使用“NUMBER（m,n）”，其中n位为小数位，而m-n为整数位，但是数据库也考虑了程序人员的习惯； -整数，使用INT； -小数，使用FLOAT。 3、日期：使用DATE是最为常见的做法，但是在Oracle里面DATE里面包含有日期时间，可是其他的数据库里面DATE可能只是日期，DATETIME才是表示日期时间； 4、大本文数据：使用CLOB描述，最多可以保持4G的文字信息； 5、大对象数据：使用BLOB，保持图片、音乐、电影，文字，最多可以保持4G。从实际的开发来说就使用VARCHAR2、NUMBER、DATE、CLOB。 ?? ?? ? ?2、表的创建? ??? ?? 语法： CREATE TABLE 表名称 ( ????列名称 类型 [DEFAULT 默认值], ????列名称 类型 [DEFAULT 默认值], ????.... ????列名称 类型 [DEFAULT 默认值] ); ? 范例：创建一张名为member的表，他有四个描述：mid、name、birthday、note CREATE TABLE member( ????mid NUMBER, ????name VARCHAR2(20) DEFAULT ‘无名氏‘, ????birethday DATE DEFAULT SYSDATE, ????note CLOB ); ? 向表中插入数据： INSERT INTO member(mid,name,birethday,note) VALUES (1,‘张三‘,TO_DATE(‘1890-10-10‘,‘网数据线‘); ? ?3、表的基本操作：表复制、表删除、闪回技术 ?? ??? ??? ? 复制表： CREATE TABLE 表名称AS 子查询； ? 可以将查询出来的子表作为一张表的形式复制给一个新的表格保存下来 范例：将所有30部门雇员信息保存在emp30表中 CREATE TABLE emp30 AS SELECT * FROM emp WHERE deptno=30; ? ? 删除表： 在正常的情况下，数据表一旦被删除了，是无法恢复的，请慎用： DROP TABLE 表名； 范例：删除数据表 DROP TABLE dept; ? 闪回技术： FlashBack的功能给予了用户的后悔机会，但是现在如果用户想要去操作这个回收站，那么用户而言具备查看、恢复、情况、彻底删除几项操作。 ? 1、删除数据表之后查看回收站： SHOW RECYCBLEBIN; ? 2、恢复删除的表 FLASHBACK TABLE 表名 TO BEFORE DROP; ? 彻底删除表格操作： 1、彻底删除person表 DROP TABLE person PURGE; 2、清空回收站 PURGE RECYCLEBIN； ? 4、恢复表的脚本 ?? 这个脚本可以对数据库进行快速的恢复。该脚本包含有如下的几个内容： 1、删除原有的数据表； 2、重新创建新的数据表； 3、创建测试数据； 4、进行提交。 ? 代码实现： --删除数据表 DROP TABLE member PURGE; --情况回收站 PURGE RECYCLEBIN; --创建数据表 CREATE TABLE member( ????mid NUMBER, ????name VARCHAR2(20) ); --测试数据 INSERT INTO member(mid,name) VALUES (1,‘张三‘); INSERT INTO member(mid,name) VALUES (2,‘李四‘); --提交事物 COMMIT; ? 五、约束的创建与管理? ? ?1、数据的条件 ? ? ? 为了保证数据表中的数据的完整性，数据需要满足若干条件后才可以进行操作，约束是一把双刃剑，约束的确会保证数据的合法性后才进行保存，如果在一张表中有过多的约束的话，那么更新的速度就一定会慢，在开发过程中，有些验证的操作（约束）可以在程序中完成： ? ? ?六种约束： ?? ??? ??? ??? ?数据类型 ?? ??? ??? ??? ?非空约束 ? ? ? ? ? ? ? ? 唯一约束 ? ? ? ? ? ? ? ? 主键约束 ? ? ? ? ? ? ? ? 检查约束 ? ? ? ? ? ? ? ? 外键约束 ? 2、非空约束 ? ? ? ? 非空约束：指表中的某一字段的内容不允许为空，如果使用非空的约束，只需要在某列的后面，利用NOT NULL 声明就可以了。 ? ? ? ?在创建表的时候，在该列的后面添加not null声明 ? ? ??create table member ( ????id??number, ????name varchar2(15) default ‘无名氏‘? ?NOT NULL, ????????sex??varchar2(5),?? ????????bithday date??default sysdate );? ? ---表示name的字段这块不允许为空? ? 3、唯一约束 ? ? ?唯一约束：在某一列的内容不能出现重复的情况，如身份证、邮箱等，设置唯一约束只需要在该列后面添加 unique? 字段即可 ? ?create table member ( ????id??number, ? ? ? ? email? varchar2(5)? UNIQUE,?? ????????bithday date??default sysdate );? ? ---表示email 字段的内容必须为唯一的，不可以出现重复的信息 ? 注意：在唯一性约束出错的时候，不会像非空约束那样直接给出是哪列的数据出现了问题，在实际开发中，可使用简写的方式，对于出错信息进行提示，以方便修改： 在创建约束的时候，使用简写 约束名称_字段? 的形式进行显示： CONSTRAINT uk_email? UNIQUE(email) ? create table member ( ????id??number, ? ? ? ? email? varchar2(5)?,?? ????????bithday date??default sysdate, ? ? ? ??CONSTRAINT uk_email? UNIQUE(email) );? ? ---使用uk_email 的简写方式，可以在错误的时候进行简单的提示，可以快速的捕捉到问题列 ? 注意2：null不在唯一约束的判断范围之内 ? ? 4、主键约束（primary key,pk） ? 主键约束= 非空约束+唯一约束；设置为主键的列，既不能为空也不能出现重复的数据 ? 定义主键约束： create table member ( ????id??number, ????name varchar2(15) default ‘无名氏‘? ?NOT NULL， ? CONSTRAINT pk_id? primary key(id)? ）；----实现主键约束，请使用简写的方式 ? ? ? 5、检查约束? ：check 、ck 在数据列生设置一些过滤条件，当过滤条件满足时才可以进行操作 如年龄的设置：年龄范围0-120 设置检查约束： create table member ( ????id??number, ????name varchar2(15) default ‘无名氏‘? ?NOT NULL， ? age number（3）， ? CONSTRAINT ck_age? check(age between 0? and 120)? ）；---实现列的条件约束 ? 6、外键约束? （foreign ，fk） ? ? ? 作用：在两张表有关联的时候，比如父表与字表的关联时，必须设置关联字段的关系，否则会导致在父表中不存在的数据，在子表中也是可以同样插入的 ? create table member（ ? ? mid? ?number， ? ? ?name? varchar2（20）， ? ? constraint pk_mid? primary key(mid)? ?----设置主键 ） ? ? create table book（ ? ? bid? ?number， ? ? ?title? varchar2（20）， ? ??mid? ?number ? ? constraint fk_mid? foreign? key(mid)? references? member（mid） on delete? cascade? ----设置book中的mid为外键，且数据来源于member表中的mid? ）----on delete cascad? 是设置级联删除 ? 注意：外键的限制 1、在删除父表之前，要先删除掉她的对应的全部的子表之后才可以进行删除 ? ?? ?强制删除父表的操作，不关心子表是否存在：drop table 表名称 cascade constraint? ?---尽量不要使用强制删除，尽量使用先删除子表，再删除父表 ? 2、如果要作为子表外键的父表列，那么这个列必须要设置为唯一约束或者主键约束 ? 3、如果现在主表中的某一行数据有对应的子表数据，那么需要先删除子表中的数据才可以删除父表中的数据,删除父表之后，子表的数据也会删除 ? 4、设置为外键的字段，在主表之中必须是主键或者是唯一约束。 级联删除：on delete cascad ：在删除父表的数据的时候，会自动删除子表关联的数据 级联更新： on delete? set null? ? 现在删除父表数据的时候，将对应的子表的数据的外键列设置为null 七、修改约束? ?100%禁止修改 约束必须在表创建的时候就应该要创建完整，不能进行约束修改。六、数据库的设计?? ?? ? 1、第一范式： ? ? ? ??数据表中的每一列的内容不可再分； ? ? ? ?注意点： ? ? ? 1、对于日期描述坚决不能拆分为：年、月、日 ? ? ? 2、对于姓名的字段不可分为fistname、lastname ?? ?1、第二范式（多对多关系） ? ? ? 数据表中不存在非关键字段对任意一候选关键字段的部分函数依赖。 ? ?2、第三范式：（一对多关系） 首要考虑 ? ? ? ? 数据表之中不存在非关键字段对任意一候选关键字段传递函数依赖。?七、其他的部分?? ??? ??一、数据库的备份 ?? ??? ?1、数据的导出与导入，以用户为单位 ?? ?? ? 数据表中的数据的导出与导入： ? ? ? ?需要先准备一个数据备份的目录，如：在cmd中输入? md? 文件夹名称创建文件夹 ? ? ? ?进入该目录下，以命令行的形式操作：输入exp命令，导出数据 ? ? ? ?进入该目录下，以命令行的形式操作：输入imp命令，导入数据 ?? ??? ? ?? ?2、数据库的冷备份，归档备份，指数据库需要关闭服务，所有的事务都需要提交了。 ? ? ? ? 备份文件： 1、控制文件：控制着整个oracle 的实例信息，可以使用‘v$controlfile’数据字典找到 2、重做日志文件：通过‘v$logfile’数据字典找到 3、数据文件：通过‘V$datefile’数据字典找到 4、核心配置文件（Pfile）：使用‘SHOWPARAMETE pflie’找到 数据库备份操作需要使用管理员进行操作： ?1、数据库管理员登陆： CONN sys/change_on_install? AS sysdba; 2、找到以上几份文件的路径： select * from?v$controlfile; select * from v$logfile; select * from V$datefile; SHOW PARAMETER?Pfile; 记录下以上文件的路径 ? 3、关闭oracle 服务： shutdown immediate; 4、拷贝出所有的备份文件 5、重启服务 startup； ? 2、数据库的其他的对象 ?? ??? ?一、同义词（oracle的专属功能） 同义词的定义：如果在sys用户下，去使用select * from emp；的语法查询emp表，他会报该表或者视图不存在的提示，原因是因为emp表是属于scott用户的，所以在其他用户下如果需要访问该表，就应该要添加scott.emp ，这样的过程就叫做同义词。 语法：create? 【public】 SYNONYM? 同义词名称? for? 模式.表名称 例：将scott.emp 映射为semp; create SYNONYM semp for scott.emp; ? 注意：在创建同义词的时候，如果没有将其创建为公共同义词的话，那么该同义词就只能在创建的用户下使用，不能在其他的用户下使用。 ? ? 创建公共的同义词： ? ?create PUBLIC SYNONYM semp for scott.emp; ? ? 使用账号密码登陆： ? ?CONN SYS/change_on_install as sysdba; ? ? 删除同义词： ? drop? SYNONYM SEMP; ? 二、索引来源与作用 ? ? ? 在数据库中进行数据查询的时候，我们可以打开追踪器对数据库的分析过程进行分析 ? ? ? 打开追踪器：set autotrace on;? 需要在sys用户下才可以查看 ? ? ? ?根据查看后的结果，分析到：在数据的执行过程中，比如简单的select * from emp where sal>1500;? 该语句的执行过程是对该表进行全盘扫描，如果在数据量足够大的情况下，如果后半部分的数据已经不满足条件了，但是该语句还是会对后续的数据进行查询，这样就会造成资源的浪费也会拉低性能； ? ? ? ? 因此我们采取先排序的方式对数据进行排序，这样就可以避免后续的资源被浪费了，但是不能使用order by ，因为order by 的执行顺序在最后，是对已经筛选好的数据进行排序； ? ? ? ?二叉数的排序方式： ? ? ? ? 选择一个数据作为根节点，比节点数大或者等于的数据放在右子数上，比节点小的数据放在左子数上。（右大左小） ?? ??? ?
DROP USER? ?用户名? CASCADE ? 3、序列 一、自动增长列? ?序列 ? ? 语法： ?? ?create? sequence 序列名称 ? ? 【MAXVALUE 最大值 | NOMAXVALUE】默认最大值：1.0000E+28 ? ? 【MINVALUE 最小值 | NOMINVALUE】默认最小值为1? ? ? 【INCREMENTBY 步长 】默认步长为1 ? ? 【START WITH 开始值】 ? ? 【CYCLE|NOCYCLE】默认为N，循环 ?? ?【CACHE 缓存个数|NOCACHE】默认为20个？这个好像是更改的，后续说 ? 序列属于数据库对象的创建过程，属于DDL的分类范畴，对于序列的而言，创建之后一定会在数据字典中保存 ? 例：CREATE? SEQUENCE?? myseq; ? ? ? nextval ：取得序列的下一个内容，每一次调用序列的值都会增长 ? ? ? currval：表示取得序列的当前内容，每次调用序列都不会增长，就相当于显示当前的序列 ? ?? ? ? ? 如果第一次没有使用nextval取得值，那么使用currval是不能显示当前的数据，就像相当于没有值 ? 二、定义特殊的序列 ? ? ? ?创建一个表： create table mytab（ ? ? id? ? number， ? ? name? ?varchar2(20), ? ? constraint pk_id primary key (id) ）; ? 创建表之后可以根据序列让id进行自动增长： insert into mytab? (id,name ) values (myseq.nextval,‘hello‘); ? 缓存的作用：缓存的作用是为了提高性能的，先准备数据，之后就可以直接使用了，但是在使用的过程中，会出现丢号的情况，该问题无法解决。 ? select myseq.nextval from dual; ---从虚拟表中查找序列 select sequence_name,cache_size,last_number from user_sequences;---在数据字典中查找相关的信息 ? drop sequence myseq ;----删除该序列 create sequence myseq；---创建该序列 INCREMENTBY 2；---设置步长为2 START WITH 1000；---改变序列的开始值 CYCLE；---设置循环 ? 注意：last_number的值是等于步长*缓存数量的 ? ? ? ? ? cache 的值必须小于cycle的值：缓存的个数必须小于循环的值 ? 4、视图的定义与使用 ? ?一、视图的创建 ? 概念：利用视图可以实现复杂的sql的语句的封装操作 视图属于DDL的定义范畴，语法如下： create【or replace】 view 视图名称 as 子查询 例： ---给scott用户创建视图的权限： CONN sys/change_on_install AS SYSDBA; GRANT CREATE VIEW TO scott; CONN SCOTT/TIGER; ? ---创建视图:将emp表中的部门为10的人员信息封装成视图 create view myview as select * from emp where deptno=10; ? 视图可以像普通的数据表那样进行直接的查询： select * from? myview；---查询的结果与之前的子查询的结果是一样的 ? 视图很少用drop语句进行删除，因为在删除与创建的过程中，会存在时间间隔，会导致存在问题，所以如果遇到视图存在的问题，我们采取替换掉的方式将其进行更新： ? create or replace? view myview as select * from emp where deptno=10;---如果视图不存在，则创建，如果存在，则替换更新 ? 二、视图的更新操作 ? ? 视图中只是包含查询语句的临时数据，并不是真实存在，但是默认情况下，视图是可以进行直接修改的 create or replace??view myview as select * from emp where deptno=10; 更新视图的部门编号： update myview set deptno=30 where empno=7369； ? 注意： 1、保护创建条件不被更改：在视图中更新的数据，同时也是会在原始的emp的数据同时更新：这样的操作是不合理的，所以为了防止更改视图导致数据的更新操作的情况，在创建视图的时候使用 with check option 的子句，防止视图的创建条件不被更改。 create or replace?view myview as select * from emp where deptno=10?with check option ;? ? 2、视图不可以修改除创建条件外的其他字段，在创建视图的时候，是属于数据的映射，那么本质上应该创建一个只读的视图，以保护数据不被修改 create or replace??view myview as select * from emp where deptno=10 with read only （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
