sql记录

1.一个表中的列在另外的表中存在

SELECT a.WNAME,a.WCARDNO FROM WOMENINFOMASTER  a
WHERE  
EXISTS(SELECT * FROM WOMENCHILDREN b where b.WCARDNO =a.WCARDNO )
and a.CONTRACEPTIONTYPEID !=169

 2.判断是否为空

select * from region where birthday is null

 3.left join   选择第一个   order by

select * from dagl_bodycheck  a left join dagl_bodaycheck_second b on a.personid = b.personid 
where a.personid =13563and isdeleted='0'  and rownum = 1 order by checkdate desc

 4.判断年龄等的时间的条件

AND (TO_CHAR(sysdate,'yyyy')-to_char(BIRTHDAY,'yyyy'))>=65

4.1 时间判断

select * from up_date where update < to_date('2007-09-07 00:00:00','yyyy-mm-dd hh24:mi:ss')

 

5.插入一条记录

INSERT INTO JF_MID_BASICINFO (
    ID,
    NAME,
    IDCARD,
    SEX,
    DataFrom,
    PHONENO,
    DEPTID,
    ISDELETED,
    OPERATIONID,
    OPERATIONNAME,
    OPERATIONDATE,
    HUKOUTYPE,
    HUJIDI,
    BIRTHDAY
)
VALUES
    (
        '261616165464564',
        '李丽丽',
        '370783195601125639',
        '男',
        '1',
        '13659874523',
        '2161',
        '0',
        '515',
        '管理员',
        to_date('2018-05-01','YYYY-MM-DD'),
        '1',
        '寿光',
         to_date('2001-02-12','yyyy-mm-dd')
    )

 6.删除表中数据

delete from JF_MID_BASICINFO

 7.数据取父级

   var townsql = "select deptcode,deptname from sys_department b where b.deptid= (select parentid from sys_department a  where deptcode ='" + ep.MANAGEDEPTID + "')";

 8.oracle中取startnum到endnum

SELECT
    *
FROM
    (
        SELECT
            PERSONID,
            PERSONCODE,
            PNAME,
            GENDERNAME,
            TOWNNAME,
            VILLAGENAME,
            BIRTHDAY,
            RESIDENCEADDRESS,
            IDCARDNO,
            COMPLETEFLAG,
            DALX,
            DISEASECODE,
            DISABILITYCODE,
            DEPTCODE,
            ROWNUM AS rn
        FROM
            DAGL_PERSON T
        WHERE
            T .isdeleted = '0'
        AND T .Status = '正常'
        AND (T .villagecode LIKE '370783%')
        AND ROWNUM <= 12
        ORDER BY
            PERSONCODE DESC
    )
WHERE
    rn > 0

9. sql中取startnum到endnum

"select * from( select top {0}  ROW_NUMBER() OVER (ORDER BY VALUECODE) AS RowNumber,  *  from DataBasic where  GROUPCODE='{2}') t  where t.RowNumber >={1}"

 10.表创建时设置

tablespace TBSL_SDDQ --表段X_SMALL_AREA放在表空间TBSL_SDDQ中
  pctfree 10 --块保留10%的空间留给更新该块数据使用
  initrans 1 --初始化事务槽的个数
  maxtrans 255 --最大事务槽的个数
  storage --存储参数
  ( 
    initial 64k --区段(extent)一次扩展64k
    minextents 1 --最小区段数
    maxextents unlimited --最大区段无限制 
  ); 

 11.sqlite 触发器实例

CREATE TRIGGER "t_raw_daer_insert_notexist" AFTER INSERT ON "PreCheckDoc" FOR EACH ROW
BEGIN  
    
  UPDATE PreCheckDoc SET BarCode= 1000000+(select count(*)from PreCheckDoc),IsDeleted='0'  WHERE IdCard=NEW.IdCard;  

END