Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

sql code

--½áºÏsys.indexesºÍsys.index_columns,sys.objects,sys.columns²éѯË÷ÒýËùÊôµÄ±í»òÊÓͼµÄÐÅÏ¢
select
  o.name as ±íÃû,
  i.name as Ë÷ÒýÃû,
  c.name as ÁÐÃû,
  i.type_desc as ÀàÐÍÃèÊö,
  is_primary_key as Ö÷¼üÔ¼Êø,
  is_unique_constraint as Î¨Ò»Ô¼Êø,
  is_disabled as ½ûÓÃ
from
  sys.objects o
inner join
  sys.indexes i
on
  i.object_id=o.object_id
inner join
  sys.index_columns ic
on
  ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
  sys.columns c
on
  ic.column_id=c.column_id and ic.object_id=c.object_id
go
--²éѯË÷ÒýµÄ¼üºÍÁÐÐÅÏ¢
select
  o.name as ±íÃû,
  i.name as Ë÷ÒýÃû,
  c.name as ×ֶαàºÅ,
from
  sysindexes i inner join sysobjects o
on
  i.id=o.id
inner join
  sysindexkeys k
on
  o.id=k.id and i.indid=k.indid
inner join
  syscolumns c
on
  c.id=i.id and k.colid=c.colid
where
  o.name='±íÃû'
--²éѯÊý¾Ý¿âdbÖбítbµÄËùÓÐË÷ÒýµÄËæÆ¬Çé¿ö
use db
go
select
  a.index_id,---Ë÷Òý±àºÅ
  b.name,---Ë÷ÒýÃû³Æ
  avg_fragmentation_in_percent---Ë÷ÒýµÄÂß¼­Ë鯬
from
  sys.dm_db_indx_physical_stats(db_id(),object_id(N'create.consume'),null,null,null) as a
join
  sys.indexes as b
on
  a.object_id=b.object_id
and
  a.index_id=b.index_id
go
---½âÊÍÏÂsys.dm_db_indx_physical_statsµÄ²ÎÊý
datebase_id: Êý¾Ý¿â±àºÅ£¬¿ÉÒÔʹÓÃdb_id()º¯Êý»ñȡָ¶¨Êý¾Ý¿âÃû¶ÔÓ¦µÄ±àºÅ¡£
object_id: ¸ÃË÷ÒýËùÊô±í»òÊÔͼµÄ±àºÅ
index_id: ¸ÃË÷ÒýµÄ±àºÅ
partition_number:¶ÔÏóÖзÖÇøµÄ±àºÅ
mode:ģʽÃû³Æ,ÓÃÓÚÖ¸¶¨»ñȡͳ¼ÆÐÅÏ¢µÄɨÃè¼¶±ð¡£
ÓйØsys.dm_db_indx_physical_statsµÄ½á¹û¼¯ÖеÄ×Ö¶ÎÃûÈ¥²éÏÂÁª»ú´ÔÊé¡£
---Ë÷ÒýÊÓͼ
Ë÷ÒýÊÓͼÊǾßÌ廯µÄÊÓͼ
--´´½¨Ë÷ÒýÊÓͼ
create view ÊÓͼÃû with schemabinding
as
select Óï¾ä
go
---´´½¨Ë÷ÒýÊÓͼÐèҪעÒâµÄ¼¸µã
1. ´´½¨Ë÷ÒýÊÓͼµÄʱºòÐèÒªÖ¸¶¨±íËùÊôµÄ¼Ü¹¹
--´íÎóд·¨
create view v_f with schemabinding
as
select
   a.a,a.b,b.a,b.b
from
 &nbs


Ïà¹ØÎĵµ£º

ijÍâÆóSQL ServerÃæ試題

--> Title  : ijÍâÆóSQL ServerÃæ試題
--> Author : wufeng4552
--> Date   : 2010-1-15
Question 1£ºCan you use a batch SQL or store procedure to calculating the Number of Days in a Month
Answer 1£ºÕÒ³öµ±ÔµÄÌìÊý
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast( ......

SQLÓÅ»¯¼°UNION ALLÌæ´úUNION

OracleÓкܶàÖµµÃѧϰµÄµØ·½£¬ÕâÀïÎÒÃÇÖ÷Òª½éÉÜOracle UNION ALL£¬°üÀ¨½éÉÜUNIONµÈ·½Ã档ͨ³£Çé¿öÏ£¬ÓÃUNIONÌæ»»WHERE×Ó¾äÖеÄOR½«»áÆðµ½½ÏºÃµÄЧ¹û¡£¶ÔË÷ÒýÁÐʹÓÃOR½«Ôì³ÉÈ«±íɨÃè¡£×¢Ò⣬ÒÔÉϹæÔòÖ»Õë¶Ô¶à¸öË÷ÒýÁÐÓÐЧ¡£¼ÙÈçÓÐcolumnûÓб»Ë÷Òý£¬²éѯЧÂÊ¿ÉÄÜ»áÒòΪÄúûÓÐÑ¡ÔñOR¶ø½µµÍ¡£ÔÚÏÂÃæµÄÀý×ÓÖУ¬LOC_ID ºÍREGION ......

Oracle ²éѯ²¢É¾³ýÖØ¸´¼Ç¼µÄSQLÓï¾ä


²éѯ¼°É¾³ýÖØ¸´¼Ç¼µÄSQLÓï¾ä
1¡¢²éÕÒ±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊǸù¾Ýµ¥¸ö×ֶΣ¨peopleId£©À´ÅжÏ
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)
 
2¡¢É¾³ý±íÖжàÓàµÄÖØ¸´¼Ç¼£¬Öظ´¼Ç¼ÊÇ ......

SQLÖ® case when then

 ÊÓͼ²éѯÖÐÔõÑù½«Ô­¶¨ÓÚÈçÐÔ±ðsex ÕâÑùµÄ×ֶΣ¬×Ö¶ÎֵΪ0£¬1ÕâÑùµÄintÀàÐÍÖµ£¬²éѯʱֱ½Ó·µ»Øvarchar
Ð͵Ä×Ö·û‘ÄÐ’£¬‘Å®’ÒÔ±ãÓÚÎÒÃǶÁÈ¡ÄØ£¿
      
ÓÐÈË»áÏëµ½if …else…ÕâÑùµÄÓï¾ä£¬¿ÉÊÇÔõô¼Ó£¬¶¼²»ÖªµÀ¼ÓÄÄÀÒòΪ×ÜÊÇ»á³ö´í¡£Æä ......

SQL´æ´¢¹ý³Ìѧϰ


¡¡¡¡±¾ÎÄ´ÓSQL´æ´¢¹ý³ÌµÄ¸ÅÄÓŵ㣬Óï·¨£¬´´½¨¼¼ÇÉ£¬µ÷ÓÃµÈ¶à·½Ãæ½éÉÜÁËSQL´æ´¢¹ý³Ì¡£
¡¡¡¡Ò»¡¢SQL´æ´¢¹ý³ÌµÄ¸ÅÄÓŵ㼰Óï·¨
¡¡¡¡¡¡¶¨Ò壺½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ÉÃüÁî¡ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ