荆棘从死亡中来，死亡又包含着新生。
      •  0 回帖  •  992 浏览  •  5 年前
   

关系型数据库基础操作（三）

对空和非空的查询

查询空 is null 、查询非空 is not null

例：

select * from T_tempEncod_word where midencodingstatus is null;

select * from T_tempEncod_word where midencodingstatus is not null;

having 查询重复字段

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

例一：

select * from T_atypical_write_word t where t.word in( 
select a.word from  T_atypical_write_word a  group by a.word having count(*)>1);

例二：

select * from T_ATYPICAL_STRUCTURE_WORD t where t.word in( 
select a.word from  T_ATYPICAL_STRUCTURE_WORD a  group by a.word having count(*)>1);

根据时间段查询

例一：

select * from  T_OUTWORD_DED_BUSINESS  t where t.dedflag=3  
and  t.SUBMITTIME BETWEEN to_date( '2016-05-09 15:41:02 ' ,'yyyy-mm-dd hh24:mi:ss')  
AND  to_date( '2016-05-09 15:41:03 ' ,'yyyy-mm-dd hh24:mi:ss')  order by t.SUBMITTIME;

连表更新

例一：

UPDATE sale_order_item soi,  refresh_order_item roi
SET soi.receive_quantity = roi.receive_quantity,
    soi.on_way_quantity = 0,
    soi.delivery_quantity = roi.receive_quantity,
    soi.not_delivery_quantity = soi.quantity - roi.receive_quantity
WHERE soi.is_deleted = 0
  AND soi.order_number = roi.order_number
  AND soi.item_number = roi.item_number
  AND soi.is_close = '0'

例二：

UPDATE purchase_voucher_item pvi,
    purchase_order_item poi
SET pvi.order_id = poi.head_id,
    pvi.order_item_id = poi.id
WHERE
        pvi.order_number = poi.order_number
  AND pvi.order_item_number = poi.item_number
  AND pvi.is_deleted = 0
  AND poi.is_deleted = 0

查询建立的 sequence

select * from user_sequences;

删除用户的 sequence

删除用户 PCTESTF 创建的 sequence

declare owner_name varchar(20);  
    cursor cur1 is select sequence_name from user_sequences;  
    begin  
        owner_name := 'PCTESTF';   
        for cur2 in cur1 loop  
            execute immediate 'drop sequence '||owner_name||'.'||cur2.sequence_name;  
        end loop;  
    end;

删除用户的所有表

删除用户 PCTESTF 用户的所有表

declare owner_name varchar(20);  
    cursor cur1 is select table_name from dba_tables where owner=owner_name;  
    begin  
        owner_name := 'PCTESTF';   
        for cur2 in cur1 loop  
            execute immediate 'drop table '||owner_name||'.'||cur2.table_name;  
        end loop;  
    end;

使用 expdp 命令导出数据库

expdp jczyk/jczyk@sinofont schemas=jczyk dumpfile=jczyk0525.dmp directory=data0429

C:\Users\S20150320002>expdp jczykf/[JCZYKF@ORCL](mailto:JCZYKF@ORCL) schemas=jczykf dumpfile=jczyk0525
03.dmp directory=JCZYK_BACKUP

expdp jczyk/jczyk@192.168.100.21/sinofont DUMPFILE=jczyk.dmp DIRECTORY=DATA_PUMP_DIR  LOGFILE=tmp_dump.log

使用 impdp 命令导入数据

修改导入用户将其他用户导出的表导入到当前用户

C:\Users\S20150320002>impdp JCZYKF/JCZYKF@orcl remap_schema=JCZYK:JCZYKF directory=JCZYK_BACKUP dumpfile=jczyk0525.dmp

impdp JCZYKF/JCZYKF@localhost/orcl DUMPFILE=jczyk0525.dmp DIRECTORY=JCZYK_BACKUP REMAP_SCHEMA=JCZYK:JCZYKF

impdp JCZYKF/JCZYKF directory=JCZYK_BACKUP dumpfile=jczyk0525.dmp full=y

impdp test_jczyk0429/123456@192.168.1.59/brp DUMPFILE=jczyk1354.dmp DIRECTORY=DATA_PUMP_DIR REMAP_SCHEMA=JCZYK:test_jczyk0429

创建表空间和用户

创建表空间 JCZYK_DATA（需要对应的目录 E:\oradata\ 存在）

CREATE TABLESPACE JCZYK_DATA

DATAFILE 'E:\oradata\JCZYK_DATA.DBF' SIZE 1024M

AUTOEXTEND ON NEXT 126M MAXSIZE 4086M

EXTENT MANAGEMENT LOCAL;

创建用户

CREATE USER JCZYKF IDENTIFIED BY JCZYKF DEFAULT TABLESPACE JCZYK_DATA;

GRANT CONNECT,DBA TO JCZYKF;

--创建用户
create user TEST identified by orcl123456;
--设置用户权限
grant connect,dba to TEST;
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

查看 oracle 数据库的目录对象

select * from dba_directories;

删除用户和表空间

drop user zhongjian cascade;

drop tablespace ZHONGJIAN including contents and datafiles;

查询表对应的触发器

select trigger_name from all_triggers  where table_name='T_MIDENCOD_WORD';

查询触发器的详细信息

select text from all_source where type='TRIGGER' AND name=' T_MIDENCOD_WORD_TRIGGER';

在 Oracle 数据库中查询所有触发器、存储过程、视图、表的两种方法

方法一：

Select object_name From user_objects Where object_type='TRIGGER';  --所有触发器

Select object_name From user_objects Where object_type='PROCEDURE';  --所有存储过程

Select object_name From user_objects Where object_type='VIEW';  --所有视图

Select object_name From user_objects Where object_type='TABLE'; --所有表

方法二：

Select * From user_triggers; --所有触发器

Select * From user_procedures; --所有存储过程

Select * From user_views; --所有视图

Select * From user_tables; --所有表

更新 sequence 的值

更新 sequence

update user_sequences set last_number=520000 where sequence_name='SQU_T_MIDWORDFIGURE_GROUP';

--查询sequence

--select * from user_sequences;

--删除sequence

--DROP SEQUENCE SEQ_T_MIDENCOD_WORD;

--查询最大id

--select max(id) from T_MIDENCOD_WORD;

--创建sequence

create sequence SEQ_T_MIDENCOD_WORD

maxvalue 99999999999999999

start with 1264574

increment by 1

cache 20

order;

SQL 执行计划分析及执行方式

通过 Explain+ 我们自定义的 SQL 便可得出该 SQL 的执行计划，如下：

EXPLAIN update purchase_information_records set record_status='5' where record_status='6' and expiry_date < '2022-11-10' and is_deleted = 0;

图片.png

上一篇 关系型数据库基础操作（二）
下一篇 关系型数据库基础操作（四）