1、确定某年某月有多少天
实现原理：先利用DATE DIFF取得当前月的第一天，再将月份加一取得下月第一天，然后减去1分钟，再取日期的
天数部分，即为当月最大日期，也即当月天数
C REATE FUNC TIONDaysI nMont h ( @datedatet ime ) Retu rns i nt
AS
BEGI N
RE TURNDay(d atead d(mi,-3,DA TEADD(m, D ATEDI FF(m,0,@da te)+1,0)))
END
调用示例：
se lectdbo.D aysIn Month ('2006-02-03')
(2)计算哪一天是本周的星期一
S ELECT DATE ADD(w eek,DATED IFF(w eek,'1900-01-01',get date()), '1900-01-01') --返回2006-11-06 00:00:00.000
或
S ELECT DATE ADD(w eek,DATED IFF(w eek,0,getd ate()),0)
(3)当前季度的第一天
S ELECT DATE ADD(q uarte r, DA TEDIF F(qua rter,0,get date()), 0)—返回2006-10-0100:00:00.000
(4)一个季度多少天
decl are @m tin yint,@time smal ldate time
sele ct @m=mont h(get date())
se lect@m=ca se wh en @m betw een 1 and3 the n 1
whe n @mbetwe en 4and 6 then 4
when @m b etwee n 7 and 9 then 7
else 10 e nd
s elect @tim e=dat ename(year,getd ate())+'-'+conv ert(v archa r(10),@m)+'-01'
sele ct da tedif f(day,@tim e,dat eadd(mm,3,@time)) —返回92
1.按姓氏笔画排序:
Sel ect * From Tabl eName Orde r ByCusto merNa me Co llate Chin ese_P RC_St roke_ci_as
2.数据库加密:
selec t enc rypt('原始密码')
se lectpwden crypt('原始密码')
s elect pwdc ompar e('原始密码','加密后密码') =1--相同；否则不相同 enc rypt('原始密码')
se lectpwden crypt('原始密码')
s elect pwdc ompar e('原始密码','加密后密码') =1--相同；否则不相同
3.取回表中字段:
dec lare@list varc har(1000),@sqlnvarc har(1000)
selec t @li st=@l ist+','+b.namefromsysob jects a,sy scolu mns b wher e a.i d=b.i d and
a.na me='表A'
se t @sq l='se lect'+rig ht(@l ist,l en(@l ist)-1)+'from表A'
e xec (@sql)
4.查看硬盘分区:
EXEC mast er..x p_fix eddri ves
5.比较A,B表是否相等:
if (sel ect c hecks um_ag g(bin ary_c hecks um(*)) fro m A)
=
(selec t che cksum_agg(binar y_che cksum(*))fromB)
pr int '相等'
e lse
p rint'不相等'
6.杀掉所有的事件探察器进程:
DEC LAREhcfor eachCURSO R GLO BAL F OR SE LECT'kill '+RT RIM(s pid)FROMmaste r.dbo.sysp roces ses
W HEREprogr am_na me IN('SQL prof iler',N'SQ L 事件探查器')
EXECsp_ms forea ch_wo rker'?'
7.记录搜索:
开头到N条记录
Selec t Top N *From表
-------------------------------
N到M条记录(要有主索引ID)
Selec t Top M-N* Fro m 表W hereID in (Sel ect T op MID Fr om 表) Orde r byID D esc ----------------------------------
N到结尾记录
Sele ct To p N * From表 Or der b y IDDesc
8.如何修改数据库的名称:
s p_ren amedb 'old_name', 'n ew_na me'
9：获取当前数据库中的所有用户表
sele ct Na me fr om sy sobje cts w herextype='u'and s tatus>=0
10：获取某一个表的所有字段
s elect name from sysc olumn s whe re id=obje ct_id('表名')
trunc函数ex11：查看与某一个表相关的视图、存储过程、函数
sele ct a.* fro m sys objec ts a, sysc ommen ts bwhere a.id = b.id an d b.t ext l ike '%表名%' 12：查看当前数据库中所有存储过程
s elect name as 存储过程名称 from syso bject s whe re xt ype='P'
13：查询用户创建的所有数据库
s elect * fr om ma ster..sysd ataba ses D wher e sid notin(se lectsid f rom m aster..sys login s whe re na me='s a')
或者
sel ect d bid,nameAS DB_NAME from mast er..s ysdat abase s whe re si d <>0x01
14：查询某一个表的字段和数据类型
se lectcolum n_nam e,dat a_typ e fro m inf ormat ion_s chema.colu mns
w heretable_name = '表名'
[n].[标题]:
Se lect* Fro m Tab leNam e Ord er By Cust omerN ame
[n].[标题]:
S elect * Fr om Ta bleNa me Or der B y Cus tomer Name
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
S ql优化是一项复杂的工作，以下的一些基本原则是本人看书时所记录下来的，很明确且没什么废话：
1．索引的使用：
（1）.当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。
（2）.避免在索引列上使用函数或计算，在w here子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：
低效：s elect * fr om de pt wh ere s al*12 >2500;
高效：sele ct *fromdeptwhere sal>2500/12;
(3).避免在索引列上使用not和“!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not和“!=”时，就会停止使用索引而去执行全表扫描。
(4).索引列上>=代替>
低效：s elect * fr om em p whe re de ptno> 3
高效：se lect* fro m emp wher e dep tno >=4
两者的区别在于，前者db ms将直接跳到第一个deptn o等于4的记录，而后者将首先定位到dep tno等于3的记录并且向前扫描到第一个d eptno大于3的。
(5).非要对一个使用函数的列启用索引，基于函数的索引是一个较好的方案。
2.游标的使用：
当在海量的数据表中进行数据的删除、更新、插入操作时，用游标处理的效率是最慢的，但是游标又是必不可少的，所以正确使用游标十分重要：
(1). 在数据抽取的源表中使用时间戳，这样每天的维表数据维护只针对更新日期为最新时间的数据来进行，大大减少需要维护的数据记录数。
(2). 在i nsert和upda te维表时都加上一个条件来过滤维表中已经存在的记录，例如：
inser t int o dim_cust omerselec t * f rom o ds_cu stome r whe re od s_cus tomer.code notexist s (di m_cus tomer.code)
od s_cus tomer为数据源表。dim_custo mer为维表。
(3).使用显式的游标，因为隐式的游标将会执行两次操作，第一次检索记录，第二次检查to o man y row s这个ex cepti on，而显式游标不执行第二次操作。
3．据抽取和上载时的s ql优化：
(1). Wher e 子句中的连接顺序：
ora cle采用自下而上的顺序解析w here子句，根据这个原理，表之间的连接必须写在其他wher e条件之前，那些可以过滤掉大量记录的条件必须写在w here子句的末尾。如：
低效：sele ct *fromemp e wher e sal>5000 andjob = …man ager‟ and25<(s elect coun t (*) from empwhere mgr=e.emp no);
高效：se lect* fro m emp e wh ere 25<(se lectcount(*) f rom e mp wh ere m gr=e.empno) and sal>5000and j ob=‟m anage r‟;
(2). 删除全表时，用t runca te 替代 dele te，同时注意tru ncate只能在删除全表时适用，因为tr uncat e是ddl而不是dm l。
(3).尽量多使用comm it
只要有可能就在程序中对每个dele te,in sert,updat e操作尽量多使用co mmit，这样系统性能会因为c ommit所释放的资源而大大提高。
(4).用exi sts替代in ，可以提高查询的效率。
(5). 用n ot ex ists替代 no t in
(6). 优化group by
提高grou p by语句的效率，可以将不需要的记录在group by之前过滤掉。如：
低效：selec t job, avg(sal) from empgroup by j ob ha vingjob = …pre siden t‟ or
job=‟mana ger‟;
高效：selec t job, avg(sal) from emphavin g job=‟pre siden t‟ or job=‟mana ger‟group
by j ob;
(7). 有条件的使用un ion-a ll 替代 unio n：这样做排序就不必要了，效率会提高3到5倍。
(8). 分离表和索引
总是将你的表和索引建立在不同的表空间内，决不要将不属于or acle内部系统的对象存放到s ystem表空间内。同时确保数据表空间和索引表空间置于不同的硬盘控制卡控制的硬盘上
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论