1.dual 虚拟表 如:select 8*4 from dual ; 结果:32
2.distinct 重复的不显示 如:select distince * from tablename;
3.like 关键字中 %表示多个 _表示一个
4.分组条件查询select avg(abc),def from table group by def having avg(abc)>1; 选择def的平均abc having 限制组的条件
5. create table tablename(col_name varchar2(32) constraint constraint_name primary key(col_name)); 创建表时添加约束
alter table table_name add constraint constraint_name primary key(col_name); 创建表后添加约束
alter table table_name drop constraint constraint_name; 创建表后删除约束
6.分页
SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM table where 1=1 ORDER BY name ASC) tt WHERE ROWNUM <=20) WHERE ro > 10
--where 1=1 利于条件的拼接。
7.存储过程
create or replace procedure m_procedure (
i_SendTime in varchar2,
O_STATE out varchar2 --输出状态
)is
v_serviceId varchar2(50);
begin
select net_name into v_serviceId from tp_net_flag where net_flag = i_SendTime
if (instr(v_serviceId ,'短信')>0) then
....
end if;
end m_procedure ;
8.触发器,一般监听表的变更
create or replace trigger spinfo_tri after insert or update or delete//监听表的变动 ,记录变动日志
on spinfo for each row
declare
async nchar(6);
mid integer;
begin
if inserting then
async:='insert';
mid:=:NEW.id;
elsif updating then
async:='update';
mid:=:OLD.id;
elsif deleting then
async:='delete';
mid :=:OLD.id;
end if;
insert into spinfo_async(id,ASYNC,TIME) values(mid,async,sysdate);
end;
1.dual 是一张虚拟表 如:select 8*4 from dual ; 结果:32 2.distinct 重复的不显示 如:select distince * from tablename; 3.like 关键字中 %表示多个 _表示一个 4.select avg(abc),def from table group by def having avg(a