-- 查询出的记录放到一个新表中
CREATE TABLE NEW_BZ as SELECT b.* FROM BZ_STANDARD_SDZH b LEFT JOIN BZ_TYPE t ON b.ORGANIZATION = t.CODE WHERE t.IS_BUSSINESS != '1';
-- 查询出的记录插入到另一个表中
INSERT INTO NEW_BZ SELECT b.* FROM BZ_STANDARD_SDZH b LEFT JOIN BZ_TYPE t ON b.ORGANIZATION = t.CODE WHERE t.IS_BUSSINESS = '1';
-- 修改某个字段值为当前时间
UPDATE NEW_BZ n SET n.UPDATE_DATE=SYSDATE;
-- 根据查询条件修改某字段的值
UPDATE NEW_BZ n SET n.UPDATE_DATE = to_date('2020-06-16 00:00:00', 'yyyy-mm-dd,hh24:mi:ss') WHERE n.ID IN (SELECT b.ID FROM BZ_STANDARD_SDZH b LEFT JOIN BZ_TYPE t ON b.ORGANIZATION = t.CODE WHERE t.IS_BUSSINESS = '1' );
-- 去掉字段值中的空格
update BZ_STANDARD_SDZH set STD_NO_HAND =replace(STD_NO_HAND,' ',''),UPDATE_DATE = SYSDATE WHERE ID='1249935507237994496';
-- 查找数据库中包含指定字符串的表名
select table_name from user_tables where table_name LIKE 'BZ_STANDARD_%';
-- ESCAPE 用来定义转义字符
select table_name from user_tables where table_name LIKE 'BZ_STANDARD\_%' ESCAPE'\'
--查询仅存在于NEW_BZ表中的记录
select * from NEW_BZ n where not exists(select * from BZ_STANDARD b where b.STD_NO_HAND = n.STD_NO_HAND)
--将更新日期是今天的记录的del_flag字段设为1
update NEW_BZ set del_flag = '1' where To_char(UPDATE_DATE,'YYYY-MM-DD') = to_char(sysdate,'yyyy-mm-dd')
--查询既存在于bz_standard表也存在于new_bz表中的数据,且bz_standard表中存在多条的记录
SELECT DISTINCT b.STD_NO_HAND FROM BZ_STANDARD b,NEW_BZ n WHERE b.STD_NO_HAND = n.STD_NO_HAND GROUP BY b.STD_NO_HAND HAVING COUNT(*) > 1