关系型数据库基础操作（三）
对空和非空的查询
查询空 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;
