¡¾SQL¡¿Ê¹ÓÃÒ»ÌõINSERTÓï¾äÍê³É¶à±í²åÈë
ÕâÊÇÒ»Ìõµß¸²³£¹æµÄ²åÈë·½·¨£¬Ò»ÌõINSERTÓï¾ä¿ÉÒÔÍê³ÉÏò¶àÕűíµÄ²åÈëÈÎÎñ¡£Ð¡Ð¡µØչʾһÏÂÕâÖÖ²åÈë·½·¨¡£
1.´´½¨±íT²¢³õʼ»¯²âÊÔÊý¾Ý£¬´Ë±í×÷ΪÊý¾ÝÔ´¡£
sec@ora10g> create table t (x number(10), y varchar2(10));
sec@ora10g> insert into t values (1,'a');
sec@ora10g> insert into t values (2,'b');
sec@ora10g> insert into t values (3,'c');
sec@ora10g> insert into t values (4,'d');
sec@ora10g> insert into t values (5,'e');
sec@ora10g> insert into t values (6,'f');
sec@ora10g> commit;
2.²é¿´±íTµÄÊý¾Ý
sec@ora10g> select * from t;
X Y
---------- ----------
1 a
2 b
3 c
4 d
5 e
6 f
6 rows selected.
3.´´½¨±íT1ºÍT2£¬×÷ΪÎÒÃÇÒª²åÈëµÄÄ¿±ê±í¡£
sec@ora10g> create table t1 as select * from t where 0=1;
Table created.
sec@ora10g> create table t2 as select * from t where 0=1;
Table created.
4.µÚÒ»ÖÖ¶à±í²åÈë·½·¨INSERT ALL
1£©Íê³ÉINSERT ALL²åÈë
sec@ora10g> insert all into t1 into t2 select * from t;
12 rows created.
ÕâÀïÖ®ËùÒÔÏÔʾ²åÈëÁË12ÌõÊý¾Ý£¬Êµ¼ÊÉϱíʾÔÚT1±íÖвåÈëÁË6Ìõ£¬T2±í²åÈëÁË6Ìõ£¬Ò»¹²ÊÇ12ÌõÊý¾Ý¡£
2£©ÑéÖ¤T1±íÖб»²åÈëµÄÊý¾Ý¡£
sec@ora10g> select * from t1;
X Y
---------- ----------
1 a
2 b
3 c
4 d
5 e
6 f
6 rows selected.
3£©ÑéÖ¤T2±íÖб»²åÈëµÄÊý¾Ý¡£
sec@ora10g> select * from t2;
X Y
-
Ïà¹ØÎĵµ£º
¡¡¡¡Á½Ì¨²»Í¬µÄÊý¾Ý¿â·þÎñÆ÷£¬´Óһ̨Êý¾Ý¿â·þÎñÆ÷µÄÒ»¸öÓû§¶ÁÈ¡Áíһ̨Êý¾Ý¿â·þÎñÆ÷ϵÄij¸öÓû§µÄÊý¾Ý£¬Õâ¸öʱºò¿ÉÒÔʹÓÃdblink¡£
¡¡¡¡ÆäʵdblinkºÍÊý¾Ý¿âÖеÄview²î²»¶à£¬½¨dblinkµÄʱºòÐèÒªÖªµÀ´ý¶ÁÈ¡Êý¾Ý¿âµÄipµØÖ·£¬ssidÒÔ¼°Êý¾Ý¿âÓû§ÃûºÍÃÜÂë¡£
¡¡¡¡´´½¨¿ÉÒÔ²ÉÓÃÁ½ÖÖ·½Ê½£º
¡¡¡¡1¡¢ÒѾÅäÖñ¾µØ·þÎñ
ÒÔÏ ......
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE da ......
·µ»Ø´ú±íÖ¸¶¨ÈÕÆÚµÄÖ¸¶¨ÈÕÆÚ²¿·ÖµÄÕûÊý¡£
Óï·¨
DATEPART ( datepart ,date )
²ÎÊý
datepart
ÊÇÖ¸¶¨Ó¦·µ»ØµÄÈÕÆÚ²¿·ÖµÄ²ÎÊý¡£Ï±íÁгöÁË Microsoft® SQL Server™ ʶ±ðµÄÈÕÆÚ²¿·ÖºÍËõд¡£
ÈÕÆÚ²¿·ÖËõд
year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
weekda ......
Ϊÿ¸ö±í¶ÔÏó·µ»ØÒ»ÐС£µ±Ç°½öÓÃÓÚ sys.objects.type = U µÄ±í¶ÔÏó¡£
ÁÐÃû Êý¾ÝÀàÐÍ ËµÃ÷
<¼Ì³ÐµÄÁÐ>
ÓйشËÊÓͼËù¼Ì³ÐµÄÁеÄÁÐ±í£¬Çë²ÎÔÄ sys.objects
lob_data_space_id
int
Ò»¸ö·ÇÁãÖµ£¬ÊDZ£´æ´Ë±íµÄ text¡¢ntext ºÍ image Êý¾ÝµÄ´ÅÅ̿ռ䣨Îļþ×é»ò·ÖÇø¼Ü¹¹£©µÄ ID¡£
0 = ±í²»°üº¬ text¡¢ntext ......
sys.identity_columns
ÓÃ×÷±êʶÁеÄÿÁж¼ÔÚ±íÖÐÕ¼Ò»ÐС£
ÁÐÃû Êý¾ÝÀàÐÍ ËµÃ÷
<´Ó sys.columns ¼Ì³ÐµÄÁÐ>
ÓйشËÊÓͼËù¼Ì³ÐÁеÄÁÐ±í£¬Çë²ÎÔÄ sys.columns
seed_value
sql_variant
¸Ã±êʶÁеÄÖÖ×ÓÖµ¡£ÖÖ×ÓÖµµÄÊý¾ÝÀàÐÍÓëÁб¾ÉíµÄÊý¾ÝÀàÐÍÏàͬ¡£
increment_value
sql_variant
¸Ã±êʶÁеÄÔöÁ¿Öµ¡£ÖÖ×ÓÖµµÄÊ ......