SQL 2005 xml ´¦ÀíµÄһЩsample
USE Test
--Create 2 tables as an example
CREATE TABLE ExampleTable
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
CREATE TABLE ExampleTable2
(
[ID] int PRIMARY KEY
,[Name] nvarchar(256)
)
----way1
SELECT *
from sys.objects [table]
WHERE
[Name] LIKE 'ExampleTable%'
FOR XML AUTO, ROOT('tables')
--way2
SELECT[name] AS [@name], object_id AS [@object_id],
* from sys.objects
WHERE [Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
--SELECT * from sys.objects WHERE [Name] LIKE 'ExampleTable%'
--Way3
-------------------------------------
--Nested Results using FOR XML PATH--
-------------------------------------
-------------------------------------
--Nested Results using FOR XML PATH--
-------------------------------------
SELECT
t.[name] AS [@name]
,t.object_id AS [@object_id]
,t.[create_date] AS [date/@cdate]
,t.[modify_date] AS [date/mdate]
,CAST('<mydata>some random xml</mydata>' AS xml) [*]
,(
SELECT
c.[name] AS [@name]
,c.[column_id] AS [@column_id]
,c.[is_nullable] AS [@allows_nulls]
,ty.[name] AS [@datatype]
from sys.columns c
INNER JOIN sys.types ty ON c.[user_type_id] = ty.[user_type_id]
WHERE t.[object_id] = c.[object_id]
FOR XML PATH('columns'), TYPE
)
from sys.objects t
WHERE [Name] LIKE 'ExampleTable%'
FOR XML PATH('table'), ROOT('tables')
SELECT cdate, * from syscolumns WHERE id=1077578877
SELECT * from sysobjects WHERE id=1077578877
SELECT * from sys.objects WHERE object_id=1077578877
--------------------
--Set Xml Variable--
--------------------
/*
In 2000, it wasn't possible to set any variable to the result of a query
*/
DECLARE @xml xml
SELECT @xml =
(
SELECT TOP 3
[Name]
from sys.objects
FOR XML AUTO
)
SELECT @x
Ïà¹ØÎĵµ£º
Ö£ÖØÉùÃ÷һϣ¬ÕâÆªÈÕÖ¾ÊÇÎÒCopyµÄ£¬²»ÊÇ×Ô¼ºÐ´µÄ£¬ÎÒ»¹Ã»ÄÇôǿ£¡(×Ðϸ¿´Ò»Ï£¬ÓÐÓá£)
¹ØÓÚSQLÓï¾äÖеÄË«ÒýºÅ¡¢µ¥ÒýºÅ£º
£¨1£©ÏÈÀ´¿´Ò»¸ö×î¼òµ¥µÄSQLÓï¾ä
Insert Into users(username) values("СÍõ")
´ó¼ÒÀ´¿ ......
sql2000º¯Êý´óÈ«1 µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈ ......
1 TOP
ÕâÊÇÒ»¸ö´ó¼Ò¾³£Îʵ½µÄÎÊÌ⣬ÀýÈçÔÚSQLSERVERÖпÉÒÔʹÓÃÈçÏÂÓï¾äÀ´È¡µÃ¼Ç¼¼¯ÖеÄǰʮÌõ¼Ç¼£º
SELECT TOP 10 * from [index] ORDER BY indexid DESC;
µ«ÊÇÕâÌõSQLÓï¾äÔÚSQLiteÖÐÊÇÎÞ·¨Ö´Ðеģ¬Ó¦¸Ã¸ÄΪ£º
SELECT * from [index] ORDER BY indexid DESC limit 0,10;
ÆäÖÐlimit 0,10±íʾ´ÓµÚ0Ìõ¼Ç¼¿ªÊ¼£¬Íùºó ......
.Çå¿ÕÈÕÖ¾
DUMP TRANSACTION ¿âÃû WITH NO_LOG
2.½Ø¶ÏÊÂÎñÈÕÖ¾£º
BACKUP LOG ¿âÃû WITH NO_LOG
3.ÊÕËõÊý¾Ý¿âÎļþ(Èç¹û²»Ñ¹Ëõ,Êý¾Ý¿âµÄÎļþ²»»á¼õС
ÆóÒµ¹ÜÀíÆ÷--ÓÒ¼üÄãҪѹËõµÄÊý¾Ý¿â--ËùÓÐÈÎÎñ--ÊÕËõÊý¾Ý¿â--ÊÕËõÎļþ--Ñ¡ÔñÈÕÖ¾Îļþ--ÔÚÊÕËõ·½Ê½ÀïÑ¡ÔñÊÕËõÖÁXXM,ÕâÀï»á¸ø³öÒ»¸öÔÊÐíÊÕËõµ½µÄ×îСMÊý,Ö±½Ó ......
CSDNÉÏÓÐÆªÌû×Óhttp://topic.csdn.net/u/20100223/15/644e6212-9fdc-42de-81ad-785d28ed71d3.html
ÌÖÂÛ²éѯ¼Æ»®¶ÁÈ¡Ë÷ÒýÒ³µÄÎÊÌâ¡£
Ö÷ÒªÎÊÌâÊÇ£º ÔÚÒ»ÕŽ¨Óоۼ¯Ë÷Òý£¨Ã»ÓÐÆäËü·Ç¾Û¼¯Ë÷Òý£©µÄ±íÖ´ÐÐselect count(*) ¡£²éѯÏÔʾɨÃèÁËËùÓеÄË÷ÒýÖмä²ãÒ¶¡£´ÓÀíÂÛÉϽ²£¬ÓÉÓÚÿ¸öÊý¾ÝÒ³¶¼¼Ç¼×ÅËüµÄÉÏÒ»¸öpageºÍÏÂÒ»¸öpage, ......