SQL ´óÈ«
ÐÄÓêÖ®¼Ò
1.°´ÐÕÊϱʻÅÅÐò:
Select * from TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2.Êý¾Ý¿â¼ÓÃÜ:
select encrypt('ÔʼÃÜÂë')
select pwdencrypt('ÔʼÃÜÂë')
select pwdcompare('ÔʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ encrypt('ÔʼÃÜÂë')
select pwdencrypt('ÔʼÃÜÂë')
select pwdcompare('ÔʼÃÜÂë','¼ÓÃܺóÃÜÂë') = 1--Ïàͬ£»·ñÔò²»Ïàͬ
3.È¡»Ø±íÖÐ×Ö¶Î:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='±íA'
set @sql='select '+right(@list,len(@list)-1)+' from ±íA'
exec (@sql)
4.²é¿´Ó²ÅÌ·ÖÇø:
EXEC master..xp_fixeddrives
5.±È½ÏA,B±íÊÇ·ñÏàµÈ:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'ÏàµÈ'
else
print '²»ÏàµÈ'
6.ɱµôËùÓеÄʼþ̽²ìÆ÷½ø³Ì:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) from master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL ʼþ̽²éÆ÷')
EXEC sp_msforeach_worker '?'
7.¼Ç¼ËÑË÷:
¿ªÍ·µ½NÌõ¼Ç¼
Select Top N * from ±í
-------------------------------
Nµ½MÌõ¼Ç¼(ÒªÓÐÖ÷Ë÷ÒýID)
Select Top M-N * from ±í Where ID in (Select Top M ID from ±í) Order by ID Desc
----------------------------------
Nµ½½áβ¼Ç¼
Select Top N * from ±í Order by ID Desc
8.ÈçºÎÐÞ¸ÄÊý¾Ý¿âµÄÃû³Æ:
sp_renamedb 'old_name', 'new_name'
9£º»ñÈ¡µ±Ç°Êý¾Ý¿âÖеÄËùÓÐÓû§±í
select Name from sysobjects where xtype='u' and status>=0
10£º»ñȡijһ¸ö±íµÄËùÓÐ×Ö¶Î
select name from syscolumns where id=object_id('±íÃû')
11£º²é¿´Óëijһ¸ö±íÏà¹ØµÄÊÓͼ¡¢´æ´¢¹ý³Ì¡¢º¯Êý
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%±íÃû%'
12£º²é¿´µ±Ç°Êý¾Ý¿âÖÐËùÓд洢¹ý³Ì
select name as ´æ´¢¹ý³ÌÃû³Æ from sysobjects where xtype='P'
13£º²éѯÓû§´´½¨µÄËùÓÐÊý¾Ý¿â
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
»òÕß
select dbid, name AS DB_NAME from master..sysdatabase
Ïà¹ØÎĵµ£º
echo Windows Registry Editor Version 5.00>3389.reg
echo [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server]>>3389.reg
echo "fDenyTSConnections"=dword:00000000>>3389.reg
echo [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\Wds\rdpwd\Tds\tcp]> ......
1£©PL/SQLÊý¾ÝÀàÐÍ
Ãû³Æ
ÀàÐÍ
˵Ã÷
NUMBER
Êý×ÖÐÍ
ÄÜ´æ·ÅÕûÊýÖµºÍʵÊýÖµ£¬²¢ÇÒ¿ÉÒÔ¶¨Ò徫¶ÈºÍȡֵ·¶Î§
BINARY_INTEGER
Êý×ÖÐÍ
¿É´æ´¢´ø·ûºÅÕûÊý£¬ÎªÕûÊý¼ÆËãÓÅ»¯ÐÔÄÜ
DEC
Êý×ÖÐÍ
NUMBERµÄ×ÓÀàÐÍ£¬Ð¡Êý
DOUBLE PRECISION
Êý×ÖÐÍ
NUMBERµÄ×ÓÀàÐÍ£¬¸ß¾«¶ÈʵÊý
INTEGER
Êý×ÖÐÍ
NUMBERµÄ×ÓÀàÐÍ£¬ÕûÊý
INT
Êý ......
¹ØÓÚÁ½±í¹ØÁªµÄupdate£¬µ«Óï¾äÔõôд¶¼²»ÕýÈ·£¬ÀÏÊDZ¨´í£¬ÓÚÊÇÐľªÈâÌø£¨¾ÍŲ»Äܼ°Ê±Íê³É²Ù×÷£©È¥²éÁËһϣ¬NND£¬ÔÀ´°ÑSQLд³ÉÁËÔÚSQL ServerÏÂÃæµÄÌØÓÐÐÎʽ£¬ÕâÖÖÓï·¨ÔÚOracleÏÂÃæÊÇÐв»Í¨µÄ£¬¼±Ã¦¸Ä»ØÀ´£¬¼°Ê±Íê³ÉÁËÈÎÎñ¡£Ë³±ãÒ²°Ñ²éµ½µÄSQLÌû³öÀ´£¬ÄÄÌìÔÙÍü¼ÇÁË£¬Ò²ºÃÔÚÕâÀïÕÒ»ØÀ´£º
update customers a ......
selectÓï¾äÖÐÖ»ÄÜʹÓÃsqlº¯Êý¶Ô×ֶνøÐвÙ×÷£¨Á´½Ósql server£©£¬
select ×Ö¶Î1 from ±í1 where ×Ö¶Î1.IndexOf("ÔÆ")=1;
ÕâÌõÓï¾ä²»¶ÔµÄÔÒòÊÇindexof£¨£©º¯Êý²»ÊÇsqlº¯Êý£¬¸Ä³Ésql¶ÔÓ¦µÄº¯Êý¾Í¿ÉÒÔÁË¡£
left£¨£©ÊÇsqlº¯Êý¡£
select ×Ö¶Î1 from ±í1 where charindex£¨'ÔÆ',×Ö¶Î1£©=1;
×Ö·û´®º¯Êý¶Ô¶þ½øÖÆÊý¾Ý¡¢×Ö·û´®º ......
alter table ±íÃû
add constraint Ô¼ÊøÃû
foreign key(×Ö¶ÎÃû) references Ö÷±íÃû(×Ö¶ÎÃû)
on delete cascade
Óï·¨£º
Foreign Key
(column[,...n])
references referenced_table_name[(ref_column[,...n])]
[on delete cascade]
[on update cascade]
×¢ÊÍ£º
column:ÁÐÃû
referenced_table_name:Íâ¼ü²Î¿¼µÄÖ÷¼ü± ......