常用SQL操作技巧

《常用SQL操作技巧》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、说明：复制表(只复制结构,源表名：a新表名：b) select*intobfromawhere1<>1说明：拷贝表(拷贝数据,源表名：a目标表名：b)insertintob(a,b,c)selectd,e,ffromb;说明：显示文章、提交人和最后回复时间selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b说明：外连接查询(表名1：a表名2：b)selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINb

2、ONa.a=b.c说明：日程安排提前五分钟提醒select*from日程安排wheredatediff('minute',f开始时间,getdate())>5说明：两张关联表，删除主表中已经在副表中没有的信息deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)说明：-- SQL: SelectA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE FROMTABLE1, (SelectX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE FROM

3、(SelectNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND FROMTABLE2 WhereTO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X, (SelectNUM,UPD_DATE,STOCK_ONHAND FROMTABLE2 WhereTO_CHAR(UPD_DATE,'YYYY/MM')= TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY/MM')

4、

5、'/01','YYYY/MM/DD')-1,'YYYY/MM'))Y, WhereX.NUM=Y.NUM（+） AND

6、X.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)B WhereA.NUM=B.NUM说明：-- select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名称='"&strdepartmentname&"'and专业名称='"&strprofessionname&"'orderby性别,生源地,高考总成绩从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源） Selecta.userper,a.tel,

7、a.standfee,TO_CHAR(a.telfeedate,'yyyy')AStelyear, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'01',a.factration))ASJAN, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'02',a.factration))ASFRI, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'03',a.factration))ASMAR, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'04',a.factration))A

8、SAPR, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'05',a.factration))ASMAY, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'06',a.factration))ASJUE, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'07',a.factration))ASJUL, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'08',a.factration))ASAGU, SUM(decode(TO_CHAR(a.telfeedate,'mm'),

9、'09',a.factration))ASSEP, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'10',a.factration))ASOCT, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'11',a.factration))ASNOV, SUM(decode(TO_CHAR(a.telfeedate,'mm'),'12',a.f