SQL ×Ö·û´®º¯ÊýС×ܽá
Á·ÊÖ£¬Ã¿Ìì²é¿´±ðÈ˵Ķ«Î÷£¬²»Èç×Ô¼º×ܽáºÃ
1:replace º¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸ö²ÎÊýÄãÏëÌæ»»µÄ²¿·Ö£¬µÚÈý¸ö²ÎÊýÄãÒªÌæ»»³Éʲô
select replace('lihan','a','b')
-----------------------------
lihbn
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
=========================================================
2:substringº¯Êý
µÚÒ»¸ö²ÎÊýÄãµÄ×Ö·û´®£¬µÚ¶þ¸öÊÇ¿ªÊ¼Ì滻λÖ㬵ÚÈý¸ö½áÊøÌæ»»Î»ÖÃ
select substring('lihan',0,3);
-----
li
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
=========================================================
3:charindexº¯Êý
µÚÒ»¸ö²ÎÊýÄãÒª²éÕÒµÄchar£¬µÚ¶þ¸ö²ÎÊýÄã±»²éÕÒµÄ×Ö·û´® ·µ»Ø²ÎÊýÒ»ÔÚ²ÎÊý¶þµÄλÖÃ
select charindex('a','lihan')
-----------
4
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
===========================================================
4£ºASCIIº¯Êý
·µ»Ø×Ö·û±í´ïʽÖÐ×î×ó²àµÄ×Ö·ûµÄ ASCII ´úÂëÖµ¡£
select ASCII('lihan')
-----------
108
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
================================================================
5£ºncharº¯Êý
¸ù¾Ý Unicode ±ê×¼µÄ¶¨Ò壬·µ»Ø¾ßÓÐÖ¸¶¨µÄÕûÊý´úÂëµÄ Unicode ×Ö·û¡£
²ÎÊýÊǽéÓÚ 0 Óë 65535 Ö®¼äµÄÕýÕûÊý¡£Èç¹ûÖ¸¶¨Á˳¬³ö´Ë·¶Î§µÄÖµ£¬½«·µ»Ø NULL¡£
select nchar(3213)
----
unicode×Ö·û
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
=========================================================
6£ºsoundex
·µ»ØÒ»¸öÓÉËĸö×Ö·û×é³ÉµÄ´úÂë (SOUNDEX)£¬ÓÃÓÚÆÀ¹ÀÁ½¸ö×Ö·û´®µÄÏàËÆÐÔ¡£
SELECT SOUNDEX ('lihan'), SOUNDEX ('lihon');
----- -----
L546 L542
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
=========================================================
7£ºchar
²ÎÊýΪ½éÓÚ 0 ºÍ 255 Ö®¼äµÄÕûÊý¡£Èç¹û¸ÃÕûÊý±í´ïʽ²»ÔÚ´Ë·¶Î§ÄÚ£¬½«·µ»Ø NULL Öµ¡£
SELECT char(125)
----
}
£¨ËùÓ°ÏìµÄÐÐÊýΪ 1 ÐУ©
===============
Ïà¹ØÎĵµ£º
(ÊÊÓà SQL Server 2005 ÆäËû°æ±¾)
ÊÕ²Ø
SQL Server 2005 Express ÊÇ΢ÈíÌṩµÄÊý¾Ý¿âµÄµÍ¶Ë½â¾ö·½°¸,
¿ÉÃâ·ÑÈÎÒâËæ²úÆ··Ö·¢, ¿ÉÒÔÔÚXPµÈ·Çרҵ·þÎñÆ÷ϵͳϰ²×°, »¹¿ÉÒÔÔ¶³Ì·ÃÎÊ, ¶ÔÓÚСÐÍÊý¾ÝÓ¦ÓóÌÐòÒÑ×ã¹»Âú×ãÊý¾ÝʹÓÃÒªÇó.
ĬÈϰ²×°ÏÂ,SQL Server ......
1.Ôø¾²»Ð¡Ðİѿª·¢¿âµÄÊý¾Ý¿â±íÈ«²¿É¾³ý£¬µ±Ê±ÏŵÄÒªËÀ¡£½á¹ûÕÒµ½ÏÂÃæµÄÓï¾ä»Ö¸´µ½ÁË1¸öСʱ֮ǰµÄÊý¾Ý£¡ºÜ¼òµ¥¡£
×¢ÒâʹÓùÜÀíÔ±µÇ¼ϵͳ£º
select * from ±íÃû as of timestamp sysdate-1/12 //²éѯÁ½¸öСʱǰµÄij±íÊý¾Ý£¡¼ÈÈ»Á½Ð¡Ê±ÒÔǰµÄÊý¾Ý¶¼µÃµ½ÁË£¬¼ÌÐøÔõô×ö£¬ÖªµÀÁ˰ɡ£¡£
Èç¹ûdropÁË±í£¬Ôõô°ì£¿£¿¼ûÏ ......
SQL ServerÁ¬½ÓÔ¶³ÌÊý¾ÝÔ´µÄ»ù±¾·½·¨ÓÐÏÂÃæÈýÖÖ£º
OPENDATASOURCE
: The OPENDATASOURCE function is used to
specify connection information for a remote data source by specifying
the OLE DB provider and an initialization string. OPENDATASOURCE can be
used directly within a SELECT, INSERT, UPDATE, or ......
-- ˵Ã÷:
-- 1. Ҫɾ³ý±í table_name, ÐëÏÈÅжϸñíÊÇ·ñÕý±»Êý¾Ý¿âÖÐÆäËü±íËùÒýÓÃ.
-- 2. Èç¹ûδ±»ÒýÓÃ, ¿ÉÖ±½Ó DROP TABLE table_name; ·ñÔò±ØÐëÏÈɾ³ýÒýÓñíµÄÔ¼Êø, ÔÙ DROP TABLE table_name.
-- 3. ÁíÍâ, table_name ÊÇ·ñÕýÒýÓÃÆäËü±íµÄÇé¿öÎÞÐ뿼ÂÇ.
-- ´´½¨Ö÷±í
CREATE TABLE tParent
......
Ò»£®¸ÅÊö
´æ´¢¹ý³ÌÊÇÒ»×éÔ¤ÏȱàÒëºÃµÄT--SQL´úÂ룬ǿµ÷Ò»µã£ºÕâЩ´úÂëÊÇÔ¤±àÒëºÃµÄ£¬¼´ÔÚÖ´ÐÐʱ¾Í²»ÓÃÔÚ½øÐбàÒëÁË£¬ÕâÑù»áÌá¸ß³ÌÐòµÄÔËÐÐЧÂÊ¡£ËüÀàËÆÓÚÆäËû¸ß¼¶ÓïÑÔÀïµÄ¹ý³Ì£¬¼´Íê³ÉÌØ¶¨¹¦Äܵĺ¯Êý¡£´æ´¢¹ý³Ì·ÖÁ½ÀࣺϵͳÌṩµÄ´æ´¢¹ý³Ì£¬Óû§×Ô¼º¶¨ÒåµÄ´æ´¢¹ý³Ì£¬ÏµÍ³¶¨ÒåµÄÒÔ sp_Ϊǰ׺¡£
¶ ......