ORACLE数据库查询语句、触发器积累

A：增、删、改、查
1. 复制表结构及其数据：
create table table_name_new as select * from table_name_old

2. 只复制表结构：
create table table_name_new as select * from table_name_old where 1=2;
或者：
create table table_name_new like table_name_old

3. 只复制表数据：
如果两个表结构一样：
insert into table_name_new select * from table_name_old
如果两个表结构不一样：
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

获取查询结果中的前5条记录
SELECT * FROM( SELECT candid FROM A WHERE themeid='2015004'AND roundid='1' ORDER BY ticketsnum DESC)WHERE ROWNUM <= 5

复制不同表结构的相同字段值
insert into B(candid,themeid,roundid,ticketsnum,appellationid) select candid,themeid,roundid,ticketsnum,appellationid from A where themeid = '2015004' and roundid = '1'"

值过多：插入的字段数量超过表的实际字段值

4.根据时间区间查询数据
查询出包含用户输入的时间范围的时间区间内的所有数据

SELECT * FROM A WHERE TimeStart < to_date('2015-08-09','yyyy-mm-dd') AND TimeEnd > to_date('2015-08-10','yyyy-mm-dd')  

B：触发器
1、实现如下多表间的触发器
//主题表
A表字段:themeId //主题编号
themeName //主题名称
userNumber //该主题下的用户人数
......

//用户表
B表字段:userId //用户编号
userName //用户姓名
themeId //所属主题
.....

关联关系：A.userNumber = select count(userid) from B where A.themeid = B.themeid;

触发器实现功能：B中新增或者删除数据时，根据关联关系更新A表中userNumber 字段的值

CREATE OR REPLACE TRIGGER trig_vtheme_sys_user
       AFTER INSERT OR DELETE ON sys_user
       REFERENCING OLD AS old_user NEW AS new_user
       FOR EACH ROW
BEGIN
       IF INSERTING THEN
          UPDATE vtheme SET raternumber=raternumber+1 WHERE themeid = :new_user.themeid;
       END IF;
       IF deleting THEN
          UPDATE vtheme SET raternumber=raternumber - 1 WHERE themeid = :old_user.themeid;
       END IF;
END trig_vtheme_sys_user;


2、实现单一表的id字段自增功能
首先创建一个序列:
create sequence SEQ_VROUNDS
minvalue 1
maxvalue 9999999999999999999999999
start with 978
increment by 1
cache 20;

然后创建如下触发器：
create or replace trigger trig_VROUNDS
before insert on VROUNDS
for each row
begin
select seq_VROUNDS.nextval into :new.Id from dual;
end ;