简单常用的SQL语句

1   删除AB之间的数据

             delete from 表名   where 列名  between A and B 

2 在原有数据上修改

         带锅炉
select bianhao,substr(bianhao, 1,length(bianhao)-2) || 'XG'  from gongchengxx WHERE  bianhao like '%G.&'

update gongchengxx set bianhao = substr(bianhao, 1,length(bianhao)-2) || 'XG'  WHERE  bianhao like '%G.&'

不带锅炉
        select bianhao,substr(bianhao, 1,length(bianhao)-1)  ||  'X'  from gongchengxx  WHERE  bianhao like '%G.'

                update gongchengxxset bianhao = substr(bianhao,1,length(bianhao)-1) || 'X'  WHERE  bianhao like '%G.'

  查询当天数据
         SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
String time= sm.format(date);
String sql = "select * from alarms where to_char(time,'yyyy-MM-dd') ='"+time+"' order by time desc";
alarmsList = session.createSQLQuery(sql).addEntity(Alarms.class).list();
  条件查询
  alarmsList = session.createCriteria(Alarms.class)
.add(Example.create(alarms))
.addOrder(Order.desc("alarmid"))
.list();
  ID查询一条数据
alarms = (Alarms) session.get(Alarms.class,id);
  添加 修改 删除
session.save(alarms);    session.update(alarms);     session.delete(alarm);
  查询3个结果集
String sql = "select * from (select count(*) from messagenews)," +
    "(select count(state) from messagenews  where state = 'A'and gtime >"+"'"+time2+"')," +
    "(select count(velocityfri) from messagenews where velocityfri <> '00110'  and gtime >"+"'"+time2+"')";
     list = session.createSQLQuery(sql).list();
      num = (Object[]) list.get(0); //carNum为实体类
carNum.setSum(Integer.valueOf(String.valueOf(num[0])).intValue());
carNum.setOnNum(Integer.valueOf(String.valueOf(num[1])).intValue());
carNum.setRunNum(Integer.valueOf(String.valueOf(num[2])).intValue());


  内连接
      A:  select d.departmentid,d.departname ,c.carid ,c.departmentid
 from department d,car c
 where d.departmentid = c.departmentid
 and d.departmentid >10;
      B;
select i.imtime,i.carid,i.overload,d.departname
from department d,images i,car c
where i.carid = c.carid and c.departmentid = d.departmentid;


      C:
select i.imtime,i.carid,c.carnum,i.overload,d.departname,i.imageid from department d,images i,car c 
where i.carid = c.carid and c.departmentid = d.departmentid 
and substr(i.imtime,0,10)  >='2012-11-10' and substr(i.imtime,0,10) <='2012-11-15';

















 左连接
      select  d.departmentid,d.departname ,c.carid ,c.departmentid
        from department d
        left outer join car c
        on  d.departmentid = c.departmentid
        order by d.departmentid;
 右连接  
select  d.departmentid,d.departname ,c.carid ,c.departmentid
from department d
right join car c
on  d.departmentid = c.departmentid
order by d.departmentid;








select c.carid,c.owner,c.telnub, m.ldate,m.velocityfri,m.meter,m.direction,m.licheng,m.io,m.state




//查询一个表里的各个商品的平均价低于2元的商品名称
select * from(
select name,avg(进价) 平均价
from 商品表 
group by 名称 having count(名称) > 1 
) where 平均价 < 2






   触发器


A：
 在执行sql语句之前触发，
create or replace trigger tr_before_insert_employee
  before insert
  on t_employees
  for each row
  begin
     :new.work_years :=0;
  end;