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
Ïà¹ØÎĵµ£º
OracleÊý¾Ý¿â
SELECT
*
from
(
SELECT
ROWNUM AS NO_ROW
,row_.*
from
(
SELECT
&nb ......
1ÓÃÓÚÅÅÐòµÄº¯Êý
row_number()
rank()
dense_rank()
ntile(group_number)
ÏÂÃæÁоÙÕâ¸öº¯ÊýµÄÓ÷¨£º
row_number()º¯ÊýÒ»°ãÓÃÓÚ×éÄÚÅÅÐò£¬¶øÆäËûÈý¸öº¯ÊýÊǶԽá¹û¼¯ÅÅÐò
Àý×Ó£º·ÖÒ³ÅÅÐò
<!--×¢ÒâÈ«¾Ö±äÁ¿Ò²ÔÚÕâÀïÉùÃ÷£¬²¢ÓöººÅ¸ô¿ª-->
create proc MyDividePageSort @iRowCount int ,@iPageNo int
AS
< ......
Ê×ÏÈÒªÓÐjxlÀà¿â¡£
³ÌÐòÈçÏ£º
import java.io.*;
import jxl.*;
public class ExcelToSql {
public static void main(String args[])
{
try
{
Workbook book= Workbook.getWorkbook(new File("D:\\workspaceuse6\\foundation\\test\\com\\gres ......
SQL Server 2005ΪXMLÌṩ±¾µØÖ§³Ö;ÏÖÔÚ¿ÉÒÔÔÚOPENXML½ÚµãÖÐʹÓÃÒ»¸ö´øÓÐnodes()º¯ÊýµÄXML×Ö¶ÎÀàÐͰÑÒ»¸öXMLÎļþת»¯ÎªÒ»¸öÐм¯¡£ÈÃÎÒÃÇÀ´¿´Ò»¸öʹÓÃOPENXMLµÄ¼òµ¥Àý×Ó£¬²¢ÏÔʾÈçºÎ°ÑËüת»¯ÎªÔÚSQL Server 2005ÖÐʹÓÃXML×Ö¶ÎÀàÐͺÍnodes()º¯Êý¡£
¡¡¡¡ÎªÁËʹÎÒÃǵÄÀý×Ó¼òµ¥»¯£¬ÎÒÃǽ«¼ÙÉèÎÒÃÇÐèÒªÖ´ÐÐijÖÖ»ùÓڲɹº¶©µ¥ÁÐ±íµ ......
¡¡--Óï ¾ä ¹¦ ÄÜ¡¡¡¡--Êý¾Ý²Ù×÷¡¡¡¡SELECT --´ÓÊý¾Ý¿â±íÖмìË÷Êý¾ÝÐкÍÁС¡¡¡INSERT --ÏòÊý¾Ý¿â±íÌí¼ÓÐÂÊý¾ÝÐС¡¡¡DELETE --´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾ÝÐС¡¡¡UPDATE --¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý¡¡¡¡--Êý¾Ý¶¨Òå¡¡¡¡CREATE TABLE --´´½¨Ò»¸öÊý¾Ý¿â±í¡¡¡¡DROP TABLE --´ÓÊý¾Ý¿âÖÐɾ³ý±í¡¡¡¡ALTER TABLE --ÐÞ¸ÄÊý¾Ý¿â±í½á¹¹¡¡¡¡CREATE VI ......