ʵÓÃ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µÄ記錄
--Ĭ