ÁÙʱ±ívs.±í±äÁ¿ÒÔ¼°ËüÃǶÔSQLServerÐÔÄܵÄÓ°Ïì
ÔÚÁÙʱ±í
create table #T (…)
ºÍ±í±äÁ¿
declare @T table (…)
Ö®¼äÖ÷ÒªÓÐ3¸öÀíÂÛÉϵIJ»Í¬¡£
µÚÒ»¸ö²»Í¬Ê¹ÊÂÎñÈÕÖ¾²»»á¼Ç¼±í±äÁ¿¡£Òò´Ë£¬ËüÃÇÍÑÀëÁËÊÂÎñ»úÖÆµÄ·¶Î§£¬´ÓÏÂÃæµÄÀý×Ó¿ÉÏÔ¶øÒ×¼û£º
create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T
s
---------------
old value #
s
---------------
new value @
ÔÚÉùÃ÷ÁÙʱ±í#TºÍ±í±äÁ¿@TÖ®ºó£¬¸øËüÃÇ·ÖÅäÒ»¸öÏàͬµÄֵΪold value×Ö·û´®¡£È»ºó£¬¿ªÊ¼Ò»¸öÊÂÎñÈ¥¸üÐÂËüÃÇ¡£´Ëʱ£¬ËüÃǶ¼½«ÓÐеÄÏàͬµÄÖµnew value×Ö·û´®¡£µ«µ±ÊÂÎñ»Ø¹öʱ£¬ÕýÈçÄãËù¿´µ½µÄ£¬±í±äÁ¿@T±£ÁôÁËÕâ¸öÐÂÖµ¶øÃ»Óзµ»Øold value×Ö·û´®¡£ÕâÊÇÒòΪ¼´Ê¹±í±äÁ¿ÔÚÊÂÎñÄÚ±»¸üÐÂÁË£¬Ëü±¾Éí²»ÊÇÊÂÎñµÄÒ»²¿·Ö¡£
µÚ¶þ¸öÖ÷ÒªµÄ²»Í¬ÊÇÈκÎÒ»¸öʹÓÃÁÙʱ±íµÄ´æ´¢¹ý³Ì¶¼²»»á±»Ô¤±àÒ룬Ȼ¶øÊ¹Óñí±äÁ¿µÄ´æ´¢¹ý³ÌµÄÖ´Ðмƻ®¿ÉÒÔÔ¤ÏȾ²Ì¬µÄ±àÒë¡£Ô¤±àÒëÒ»¸ö½Å±¾µÄÖ÷ÒªºÃ´¦ÔÚÓÚ¼Ó¿ìÁËÖ´ÐеÄËÙ¶È¡£Õâ¸öºÃ´¦¶ÔÓÚ³¤µÄ´æ´¢¹ý³Ì¸ü¼ÓÏÔÖø£¬ÒòΪ¶ÔËüÀ´ËµÖØÐ±àÒë´ú¼ÛÌ«¸ß¡£
×îºó£¬±í±äÁ¿½ö´æÔÚÓÚÄÇЩ±äÁ¿ÄÜ´æÔÚµÄÏàͬ·¶Î§ÄÚ¡£ºÍÁÙʱ±íÏà·´£¬ËüÃÇÔÚÄÚ²¿´æ´¢¹ý³ÌºÍexec£¨string£©Óï¾äÀïÊDz»¿É¼ûµÄ¡£ËüÃÇÒ²²»ÄÜÔÚinsert/execÓï¾äÀïʹÓá£
ÐÔÄܱȽÏ
Ê×ÏÈ£¬×¼±¸Ò»¸öÓÐ100Íò¼Ç¼µÄ²âÊÔ±í£º
create table NUM (n int primary key, s varchar(128))
GO
set nocount on
declare @n int
set @n=1000000
while @n>0 begin
insert into NUM
select @n,'Value: '+convert(varchar,@n)
set @n=@n-1
end
GO
×¼±¸²âÊÔ´æ´¢¹ý³ÌT1£º
create procedure T1
@total int
as
create table #T (n int, s varchar(128))
insert into #T select n,s from NUM
where n%100>0 and n<=@total
declare @res varchar(128)
select @res=max(s) from NUM
where n<=@total and
Ïà¹ØÎĵµ£º
×Ö·û´®º¯Êý£º
¡ñ¡¡ASCII('a')=97---·µ»Ø×Öĸa¶ÔÓ¦µÄASCIIÂë
¡ñ¡¡CHAR('48')=0---·µ»Ø48Õâ¸öASCIIÂë¶ÔÓ¦µÄ×Ö·û
¡ñ¡¡LCASE('ABcdE')="abced" »ò LOWER('ABcdE')="abced"£¨½«¸ø¶¨×Ö·û´®×ªÎªÐ¡Ð´£©
¡ñ¡¡UCASE('ABcdE')="ABCDE" »ò UPPER('ABcdE')="ABCDE"£¨½«¸ø¶¨×Ö·û´®×ªÎª´óд£©
¡ñ¡¡LTRIM(' fgf gh ')="fgf ......
sqlServer£º
Ò»´Î²éѯ£¬Êý¾Ý¿âÖ»·µ»ØÒ»Ò³µÄÊý¾Ý¡£¶ø²»ÊÇÈ¡³öËùÓеÄÊý¾Ý¡£
pagesize£º ÿҳÏÔʾ¼Ç¼Êý
cureentpage£ºµ±Ç°Ò³Êý
select * from ( select TOP pagesize * from ( SELECT TOP pagesize*cureentpage * from my_table ORDER BY id ASC ) as amyTable OR ......
//×ܼǼÊý
function sumRecord()
{
var conn= Server.CreateObject("ADODB.connection");
var rs= Server.CreateObject("adodb.recordset");
conn.open("PROVIDER=SQLOLEDB;DATA SOURCE=127.0.0.1;UID=sa;PWD=123456;DATABASE=test");
var sql = "select count(*) as RecordCount from baoming";
rs.open(sql, ......
sqlite¹Ù·½Õ¾
http://www.sqlite.org/
SQL Syntax
http://www.sqlite.org/lang.html
sqliteÖÐÎÄÕ¾
http://www.sqlite.com.cn/
http://www.sqlitechina.org/
½¨Á¢Êý¾Ý¿âµµ°¸
ÓÃsqlite3½¨Á¢Êý¾Ý¿âµÄ·½·¨ºÜ¼òµ¥£¬Ö»ÒªÔÚshellϼüÈ루ÒÔÏÂ$·ûºÅΪshellÌáʾºÅ£¬ÇëÎð¼üÈ룩£º
$ sqlite3 foo.db
Èç¹ûĿ¼ÏÂûÓÐfo ......
×òÌìÉÏ´«³ÌÐòûÓгɹ¦£¬·¢ÏÖÒ»¸öÎÊÌâ
½«SQLServerµÄ´æ´¢¹ý³Ì¿½±´µ½¼Çʱ¾Ê±£¬Èç¹û¼Çʱ¾ÊÇ×Ô¶¯»Ø³µµÄ£¬ÐèҪעÒâһϲÎÊýÊÇ·ñ»Ø³µ¡£
¾ÙÀý£º
alter Proc [dbo].[aaa]
@tCardNos nvarchar(500), --Ïû·Ñ¿¨¿¨ºÅ@tCardPwds nvarchar(1000),--Ïû·Ñ¿¨¿¨ºÅ--¿¨ÃÜÂë@ProductId nvarchar(15), --²úÆ·±àºÅ
ËùÓеIJÎÊý¶¼Ô ......