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

 

转载于:https://www.cnblogs.com/leolzi/p/8003850.html