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

ʵÓÃSQL語¾ä

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_table where rownum < 1;
ÔÚSQL SERVERÖÐ:˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb)
¡¡¡¡SQL: select * into b from a where 1<>1 ¡¡
 
Óï¾ä
5¡¢Ò»´Î刪³ý¶à個×Ö¶Î
alter table TEST_A drop (TEST_num,test_name);
6¡¢實現Ò»個±íµÄ備·Ý
create table tableName_bak as select * from tableName;
7¡¢創½¨兩個±íµÄÖ÷Íâ鍵關聯
 
--ÏÈ創½¨Ö÷±í
create table t1(id int,stu_name varchar(50));
--為Ö÷±íÔö¼Ó³Ö¾ÃµÄÖ÷鍵約Êø
alter table t1 add(constraint t1_id primary key(id) deferrable);
--創½¨從±í£¬²¢將從±íµÄid設為Ö÷鍵
create table t2(id int primary key,sex varchar(10),address varchar(50));
--為從±íÔö¼ÓÍâ鍵約Êø£¬該約Êø來×ÔÓÚÖ÷±íËù創½¨µÄ約Êø×Ö¶Î
--當從Ö÷±íÖÐ刪³ý記錄時£¬×Ô動刪³ý從±íÖÐ與Ö®Ïà對µÄ¾ßÓÐÏàͬidµÄ記錄
--Ĭ


Ïà¹ØÎĵµ£º

ʹÓÃSQLServerÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾ä

Èç¹ûÄã¾­³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾­³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLÏ°¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ­¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......

SQLÊý¾Ý¿â

 CURSOR
==================================
l  SQL ÓαêCURSORµÄʹÓÃ
 
ʹÓÃÆðÀ´ºÜ¼òµ¥£¬Ïȶ¨Ò壬Ȼºó¸³¸öÖµ£¬´ò¿ª£¬Í¨¹ýWhile Loop Ò»¸öÒ»¸ö¶ÁÏÂÈ¥£¬×îºó¹Ø±Õ£¬ÊÍ·ÅÄÚ´æ¡£»ù±¾Ì×·ÈçÏ£º
DECLARE MyCursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪµ¥´¿ÏòÇ°µÄÓαꡣÈç¹ûÏëҪǰºóÌøÀ´ÌøÈ¥µÄ£¬Ð´³ÉScroll Cursor¼ ......

sql×ܽá

1.´´½¨Êý¾Ý¿â
    --exec xp_cmdshell 'mkdir d:\project'--µ÷ÓÃDOSÃüÁî´´½¨Îļþ¼Ð£¬Ê¹Óô˾äÐèÒªÆô¶¯SQLµÄÍâΧ¹¤¾ß
    if exists(select * from sysdatabases where name='Êý¾Ý¿âÃû')
     drop database Êý¾Ý¿âÃû
    set nocount on   ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ