Ö±½Órestore»ò¸½¼ÓÓ¦¸ÃÊDz»ÐеÄ, Óýű¾+µ¼Êý¾Ý¿Ï¶¨Ã»ÓÐÎÊÌâ¡£
2005תµ½2000µÄ²½Öè
1. Éú³Éfor 2000°æ±¾µÄÊý¾Ý¿â½Å±¾
2005 µÄmanger studio
-- ´ò¿ª"¶ÔÏó×ÊÔ´¹ÜÀíÆ÷"(ûÓеĻ°°´F8), Á¬½Óµ½ÄãµÄʵÀý
-- ÓÒ¼üҪתµ½2000µÄ¿â
-- ÈÎÎñ
-- Éú³É½Å±¾
-- ÔÚ"½Å±¾Ïòµ¼"µÄ"Ñ¡ÔñÊý¾Ý¿â"ÖÐ, È·¶¨Ñ¡ÔñµÄÊÇҪתµ½2000µÄ¿â
-- ¹´Ñ¡"ΪËùÑ¡Êý¾Ý¿âÖеÄËùÓжÔÏó±àд½Å±¾"
-- ÔÚ½ÓÏÂÀ´µÄ"Ñ¡Ôñ½Å±¾Ñ¡Ïî"ÖÐ, ÕÒµ½"Ϊ·þÎñÆ÷°æ±¾±àд½Å±¾"Ïî, Ñ¡Ôñ"SQL Server 2000"
-- ÆäËûÑ¡Ïî¸ù¾ÝÐèÒªÉèÖÃ
-- ×îºó°Ñ½Å±¾±£´æµ½Ò»¸ö .sql ½Å±¾Îļþ
2. ÔÚ2000Öд´½¨Ä¿±êÊý¾Ý¿â
ÔÚ²éѯ·ÖÎöÆ÷(»ò2005µÄmanger studioÔÚ´ò¿ª½Å±¾Îļþ), Á¬½Óµ½SQL Server 2000,Ö´ÐÐÉÏÃæÉú³ÉµÄ½Å±¾.ÒÔ´´½¨Ò»¸öеÄÊý¾Ý¿â
3. ½«Êý¾Ý´Ó2005µ¼µ½2000
2005 µÄmanger studio
-- ´ò¿ª"¶ÔÏó×ÊÔ´¹ÜÀíÆ÷"(ûÓеĻ°°´F8), Á¬½Óµ½ÄãµÄʵÀý
-- ÓÒ¼üҪתµ½2000µÄ¿â
-- ÈÎÎñ
-- µ¼³öÊý¾Ý
-- ÔÚ"SQL Server µ¼ÈëºÍµ¼³öÏòµ¼"µÄ"Ñ¡ÔñÊý¾ÝÔ´"²½ÖèÖÐ, È·¶¨Ñ¡ÔñµÄÊÇÒªµ¼³öµÄÊý¾Ý¿â
-- ÔÚ"Ñ¡ÔñÄ¿±ê"²½ÖèÖÐ, Á¬½Óµ½ 2 ......
//È¡³öÔ´Êý¾Ý
select groupname,totalnum,inputdate,groupid into #temp from
(select count(*)as totalnum,p.groupid,g.groupname,convert(nvarchar(10),inputdate,120) as 'inputdate'
from person p left join admin_group g on p.groupid = g.groupid and deleteflag = '0'
where p.inactive='0'
group by p.groupid,g.groupname,convert(nvarchar(10),p.inputdate,120)) a
left join
(select a.groupid as areagroupid,b.groupname as area,b.areaid
from admin_group a inner join admin_group b on a.upperid = b.groupid) b on a.groupid=b.areagroupid
//µÚÒ»ÖÖ·½Ê½Äܹ»¿ØÖÆÏÔʾµÄÊý¾ÝµÄÑùʽ£¬ËٶȱȽϿì
declare @s nvarchar(max)
select @s = 'select groupname as ÉçÇøÃû,groupid'
select @s = @s + ',isnull(max(case when inputdate=''' + cast(inputdate as varchar(10)) + ''' then totalnum end),0) as ''' + cast(inputdate as varchar(10)) + '''' from #temp group by inputdate order by inputdate desc
select @s = @s + ' from #temp where groupid=71 group by groupname,g ......
http://blog.csdn.net/fenglibing/archive/2007/10/24/1841537.aspx
1¡¢½«Ò»¸ö±íÖеÄÄÚÈÝ¿½±´µ½ÁíÍâÒ»¸ö±íÖÐ
insert into testT1(a1,b1,c1) select a,b,c from test;
insert into testT select * from test; (Ç°ÌáÊÇ兩個±íµÄ結構ÍêÈ«Ïàͬ)
insert into notebook(id,title,content)
select notebook_sequence.NEXTVAL,first_name,last_name from students;
×¢:a1,b1,c1ÊÇ現ÔÚ±íÖеÄ×Ö¶ÎÃû¡£A,b,cÊÇÔ±íÖеÄ×Ö¶ÎÃû
¿½±´µ¥ÌõÊý¾Ý
insert into a (select '123' as id, temp,temp2 from b where id=10)
2¡¢²éÕÒÖظ´¼Ç¼
SELECT DRAWING,DSNO from EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) from EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
3¡¢É¾³ýÖظ´¼Ç¼
DELETE from EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) from EM5
_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
4¡¢復ÖƱí結構
ÔÚORACLEÖÐ: create table new_table as select * from old ......
. SQL ÃüÁî
Õⲿ·Ö°üº¬ÄÇЩ PostgreSQL Ö§³ÖµÄ SQL ÃüÁîµÄÐÅÏ¢£®ÕâÀïµÄ "SQL" ¾ÍÊǸÃÓïÑÔͨ³£µÄº¬Ò壻 ÿÌõÃüÁîµÄÓë±ê×¼ÓйصļæÈÝÐÔµÄÐÅÏ¢¿ÉÒÔÔÚÏà¹ØµÄ²Î¿¼Ò³ÖÐÕÒµ½£®
Table of Contents
ABORT -- Í˳öµ±Ç°ÊÂÎñ
ALTER GROUP -- Ïò×éÖÐÔö¼ÓÓû§»ò´Ó×éÖÐɾ³ýÓû§
ALTER USER -- ¸Ä±äÊý¾Ý¿âÓû§Õʺţ®
ANALYZE -- ÊÕ¼¯ÓëÊý¾Ý¿âÓйصÄͳ¼Æ
BEGIN -- ¿ªÊ¼Ò»¸öÊÂÎñ¿é
CHECKPOINT -- Ç¿ÖÆÒ»¸öÊÂÎñÈÕÖ¾¼ì²éµã
CLOSE -- ¹Ø±ÕÒ»¸öÓαê
CLUSTER -- ¸ù¾ÝÒ»¸öË÷Òý¶Ôij¸ö±í¼¯´Ø
COMMENT -- ¶¨Òå»òÕ߸ıäÒ»¸ö¶ÔÏóµÄÆÀ×¢
COMMIT -- Ìá½»µ±Ç°ÊÂÎñ
COPY -- ÔÚ±íºÍÎļþÖ®¼ä¿½±´Êý¾Ý
CREATE AGGREGATE -- ¶¨ÒåÒ»¸öеľۼ¯º¯Êý
CREATE CONSTRAINT TRIGGER -- ¶¨ÒåÒ»¸öеÄÔ¼Êø´¥·¢Æ÷
CREATE DATABASE -- ´´½¨ÐÂÊý¾Ý¿â
CREATE FUNCTION -- ¶¨ÒåÒ»¸öк¯Êý
CREATE GROUP -- ¶¨ÒåÒ»¸öеÄÓû§×é
CREATE INDEX -- ¶¨ÒåÒ»¸öÐÂË÷Òý
CREATE LANGUAGE -- ¶¨ÒåÒ»ÖÖеĹý³ÌÓïÑÔ
CREATE OPERATOR -- ¶¨ÒåÒ»¸öеIJÙ×÷·û
CREATE RULE -- ¶¨ÒåÒ»¸öеÄÖØд¹æÔ ......
ÎÊÌâ
ÈçºÎÈÃT-SQL²âÊÔÌ×¼þ°Ñ²âÊÔÓÃÀý½á¹ûÖ±½ÓдÈëÎı¾Îļþ
Éè¼Æ
ʹÓÃActiveX¼¼ÊõʵÀý»¯Ò»¸öFileSystemObject¶ÔÏó£¬È»ºóͨ¹ýOpenTextFile()ºÍWriteLine()·½·¨Ö±½Ó°Ñ²âÊÔ½á¹ûдÈëÎļþ¡£
·½°¸
declare @fsoHandle int,@fileID int
exec sp_OACreate 'Scripting.FileSystemObject',@fsoHandle out
exec sp_OAMethod @fsohandle,'OpenTextFile',@fileID out,
'C:\pathToResults\Results.txt',8,1
——Ö÷²âÊÔÑ»·
if(@result = @expected)
exec sp_OAMethod @fileID,'WriteLine',null,'Pass'
else
exec sp_OAMethod @fileID,'WriteLine',null,'FAIL'
——Ö÷²âÊÔÑ»·½áÊø
exec sp_OADestroy @fileID
exec sp_OADestroy @fsoHandle
ÎÒÃÇÐèÒªÓÐÒ»¸öÎļþ¾ä±úºÍÒ»¸öÎļþID£¬ËüÃǵÄÀàÐͶ¼ÊÇInt£¬SQL ServerÓÐÒ»¸ö×÷sp_OACreate()µÄ´æ´¢¹ý³Ì¿ÉÒÔʵÀý»¯ActiveX¶ÔÏó¡£sp_OACreate()½ÓÊÜÒ»¸ö×Ö·û´®×÷Ϊ´«ÈëµÄ²ÎÊý£¬Õâ¸ö×Ö·û´®¾ÍÊÇÒª´´½¨µÄActiveX¶ÔÏóµÄÃû×Ö£¬²¢ÇÒÒÔout²ÎÊýµÄÐÎʽ·µ»ØÒ»¸öÒÑ´´ ......
Ç°ÑÔ
SQL Server 2005¿ªÊ¼Ö§³Ö±í·ÖÇø£¬ÕâÖÖ¼¼ÊõÔÊÐíËùÓеıí·ÖÇø¶¼±£´æÔÚͬһ̨·þÎñÆ÷ÉÏ¡£Ã¿Ò»¸ö±í·ÖÇø¶¼ºÍÔÚij¸öÎļþ×é(filegroup)Öеĵ¥¸öÎļþ¹ØÁª¡£Í¬ÑùµÄÒ»¸öÎļþ/Îļþ×é¿ÉÒÔÈÝÄɶà¸ö·ÖÇø±í¡£ÔÚÕâÖÖÉè¼Æ¼Ü¹¹Ï£¬Êý¾Ý¿âÒýÇæÄܹ»Åж¨²éѯ¹ý³ÌÖÐÓ¦¸Ã·ÃÎÊÄĸö·ÖÇø£¬¶ø²»ÓÃɨÃèÕû¸ö±í¡£Èç¹û²éѯÐèÒªµÄÊý¾ÝÐзÖÉ¢ÔÚ¶à¸ö·ÖÇøÖУ¬SQL ServerʹÓöà¸ö´¦ÀíÆ÷¶Ô¶à¸ö·ÖÇø½øÐв¢Ðвéѯ¡£Äã¿ÉÒÔΪÔÚ´´½¨±íµÄʱºò¾Í¶¨Òå·ÖÇøµÄË÷Òý¡£ ¶ÔСË÷ÒýµÄËÑË÷»òÕßɨÃèÒª±ÈɨÃèÕû¸ö±í»òÕßÒ»ÕÅ´ó±íÉϵÄË÷ÒýÒª¿ìºÜ¶à¡£Òò´Ë£¬µ±¶Ô´ó±í½øÐвéѯ£¬±í·ÖÇø¿ÉÒÔ²úÉúÏ൱´óµÄÐÔÄÜÌáÉý
ͨ¹ý·Ö±ð¼ì²éͬһÌõ·µ»ØËùÓÐÐеġ¢¼òµ¥SELECTÓï¾äÔÚ·ÖÇø±íºÍ·Ç·ÖÇø±íÉϵÄÖ´Ðмƻ®£¬·µ»ØµÄÊý¾Ý·¶Î§Í¨¹ýWHEREÓï¾äÀ´Ö¸¶¨¡£Í¬Ò»ÌõÓï¾äÔÚÕâÁ½¸ö²»Í¬µÄ±íÉÏÓв»Í¬µÄÖ´Ðмƻ®¡£¶ÔÓÚ·ÖÇø±íµÄ²éѯÏÔʾ³öÒ»¸öǶÌ×µÄÑ»·ºÍË÷ÒýµÄɨÃè¡£´Ó±¾ÖÊÉÏÀ´Ëµ£¬SQL Server½«Á½¸ö·ÖÇøÊÓΪ¶ÀÁ¢µÄ±í£¬Òò´ËʹÓÃÒ»¸öǶÌ×Ñ»·½«ËüÃÇÁ¬½ÓÆðÀ´¡£¶Ô·Ç·ÖÇøµÄ±íµÄͬһ¸ö²éѯÔòʹÓÃË÷ÒýɨÃèÀ´·µ»ØͬÑùµÄÁС£µ±ÄãʹÓÃͬÑùµÄ·ÖÇø²ßÂÔ´´½¨¶à¸ö±í£¬Í¬Ê±ÔÚ²éѯÖÐÁ¬½ÓÕâЩ±í£¬ÄÇôÐÔÄÜÉϵÄÌáÉý»á¸ü¼ÓÃ÷ÏÔ
·ÖÇø請Èý˼
1.ËäÈ»·ÖÇø¿ÉÒÔ´øÀ´ÖÚ¶àµÄºÃ´¦£¬µ«ÊÇ ......