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

sql server ÖеÄһЩʵÓõÄsqlÓï¾ä

¼ò½é
ÔÚÕâƪÎÄÕÂÖУ¬ÎÒÁоÙһЩsqlÓï¾äÀ´½éÉÜÊý¾Ý¿â£¬Êý¾Ý±í£¬ÊÓͼµÈµÈ¡£µ±ÎÒÃÇÔÚʹÓòéѯ²éѯ²Ù×÷ʱÕâЩsqlÓï¾ä¶¼ÊǷdz£ÓÐÓõġ£ËäÈ»ÔÚsql server¶ÔÏóä¯ÀÀÆ÷ÖÐÎÒÃÇÒ²¿ÉÒÔ»ñµÃÕâЩÓï¾ä£¬µ«ÊÇÈç¹ûÎÒÃÇдÕâЩÓï¾äʱÎÒÃÇ¿ÉÒÔ½«Ëü×Ô¶¨Òå¡£Õâ¾ÍÒâζ×ÅÎÒÃÇ¿ÉÒÔ¸øÓè×Ô¼ºµÄÐèÇóÀ´¹ýÂ˽á¹û¡£
sqlÓï¾äÁбí
ÈçºÎÁоÙsql serverµ±Ç°Á¬½ÓµÄ¿ÉÓÃÊý¾Ý¿â
Method 1 : SP_DATABASES 
Method 2 : SELECT name from SYS.DATABASES 
Method 3 : SELECT name from SYS.MASTER_FILES 
Method 4 : SELECT * from SYS.MASTER_FILES -- Type=0 for .mdf and type=1 for .ldf
SP_DATABASESÊÇÒ»¸ö¿ÉÒÔÁоÙÊý¾Ý¿â¼°Æä´óСµÄ´æ´¢¹ý³Ì
sys.databasesÓï¾äÖпÉÒÔÁоÙÊý¾Ý¿âÃû³Æ£¬´´½¨ÈÕÆÚ£¬ÐÞ¸ÄÈÕÆÚ£¬ÒѾ­Êý¾Ý¿âidºÍÆäËûһЩÐÅÏ¢¡£
SYS.MASTER_FILESÓï¾ä¿ÉÒÔ²éѯÊý¾ÝµÄÏêϸÇé¿ö£¬±ÈÈçÊý¾Ý¿âid£¬´óС£¬ÎïÀí´æ´¢Â·¾¶ÒÔ¼°ÁоÙÊý¾Ý¿âmdfºÍldf.
 
ÈçºÎÁоÙÊý¾Ý¿âÖеÄÊý¾Ý±í
ÒÔϵÄsqlÓï¾ä¶¼¿ÉÒÔÁбísql serverÊý¾Ý¿âÖеÄÓû§±í.
Method 1 : SELECT name from SYS.OBJECTS WHERE type='U' 
Method 2 : SELECT NAME from SYSOBJECTS WHERE xtype='U' 
Method 3 : SELECT name from SYS.TABLES 
Method 4 : SELECT name from SYS.ALL_OBJECTS WHERE type='U' 
Method 5 : SELECT table_name from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' 
Method 6 : SP_TABLES
ÈçºÎÁоÙÊý¾Ý¿âÖеĴ洢¹ý³Ì
Method 1 : SELECT name from SYS.OBJECTS WHERE type='P' 
Method 2 : SELECT name from SYS.PROCEDURES 
Method 3 : SELECT name from SYS.ALL_OBJECTS WHERE type='P' 
Method 4 : SELECT NAME from SYSOBJECTS WHERE xtype='P' 
Method 5 : SELECT Routine_name from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE'
SYS.OBJECTSÊý¾Ý±í°üº¬ÁËÈ«²¿µÄ´æ´¢¹ý³Ì£¬Êý¾Ý±í£¬´¥·¢Æ÷£¬ÊÓͼµÈµÄÐÅÏ¢£¬ÕâÀïʹÓÃtype=’p'À´²éѯ´æ´¢¹ý³Ì.
Information_schema.routinesÔÚsql server 7.0ÊÇÒ»¸öÊý¾ÝÊÓͼ£¬ÔÚÆäºóµÄ°æ±¾ÖÐÒѾ­±ä³É´æ´¢¹ý³ÌרÓеıí.
ÈçºÎÁоÙÊý¾Ý¿âÖеÄÊÓͼ
Method 1 : SELECT name from SYS.OBJECTS WHERE type='V' 
Method 2 : SELECT name from SYS.ALL_OBJECTS WHERE type='V' 


Ïà¹ØÎĵµ£º

sql Óï¾ä

select * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','1984-04-18',null);
UPDATE pet set birth='1989-08-31' WHERE name='Slim';
select * from pet WHERE birth>'1998-1-1';
SELECT * from pet WHEREselect * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','198 ......

SQL²éѯʱ¼ä·µ»Ø¸ñʽ

Select CONVERT(varchar(100), GETDATE(), 23)£»
·µ»ØÐÎʽ£º2008-11-29
Select CONVERT(varchar(100), GETDATE(), 102)  
·µ»ØÐÎʽ£º2008.11.29
Select CONVERT(varchar(100), GETDATE(), 101)
·µ»ØÐÎʽ£º11/29/2008
¸ü¶àÏêÇéÇë²Î¼ûÈçÏÂÁÐ±í£º
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 ......

sql²éѯ±í½á¹¹£¬¹ý³Ì£¬ÊÓͼ£¬Ö÷¼ü£¬Íâ¼ü£¬Ô¼Êø

 Ò»¡¢±í½á¹¹²éѯ
SELECT TOP (100) PERCENT a.name AS zdm,COLUMNPROPERTY(a.id, a.name, 'IsIdentity') AS bs ,
CASE WHEN EXISTS (SELECT 1 from dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
 AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc. ......

SQL²éѯÓï¾äʹÓÃ

Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡ ......

SQLÓï¾ä´´½¨£¨Êý¾Ý¿â|Êý¾Ý±í|Ë÷Òý£©

CREATE Table <±íÃû>
   £¨[<ÁÐÃû1>] ÀàÐÍ (³¤¶È) [ȱʡֵ][Áм¶Ô¼Êø]
     [£¬<ÁÐÃû2> Êý¾ÝÀàÐÍ[ȱʡֵ][Áм¶Ô¼Êø]]….
     [£¬UNIQUE£¨ÁÐÃû[£¬ÁÐÃû]….£©]
     [£¬PRIMARY KEY£¨ÁÐÃû[£¬ÁÐÃû]…£©]
&n ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ