SQLÓï·¨ÓëÃüÁî
SQLÓï·¨ÓëÃüÁî
SELECT Óï¾ä
SELECT[predicate]{*|table.*|[table.]field [,[table.]field2[,...]]} [AS alias1
[,alias2[,...]]]
from tableexpression [,...][IN externaldatabase]
[WHERE...]
[GROUP BY...]
[HAVING...]
[ORDER BY...]
[WITH OWNERACCESS OPTION]
SELECT Óï¾ä°üÀ¨ÏÂÃæ¼¸¸ö²¿·Ö
predicate
ÈçÇ°ÃæËùÊö£¬°üÀ¨ÁËALL£¬DISTINCT,DISTINCTROW,ÓëTOP
ÎÒÃÇ¿ÉÒÔÀûÓÃÕâÑùµÄÓï¾äÈ¥ÏÞÖÆ²éѯºóËùµÃµÄ½á¹û¡£
*
´ÓÖ¸¶¨±í¸ñÖÐÖ¸¶¨ËùÓеÄ×ֶΡ£
table
Õë¶Ô±»Ñ¡Ôñ³öµÄ¼Ç¼µÄ×ֶΣ¬ËùÖ¸¶¨±í¸ñµÄÃû³Æ¡£
field1,field2
ÏëÒª¶ÁÈ¡Êý¾ÝµÄ×Ö¶ÎÃû³Æ£¬Èç¹û°üº¬ÁËÒ»¸öÒÔÉϵÄ×ֶΣ¬»áÒÀÕÕÁгöµÄ˳ÐòÀ´¶ÁÈ¡Êý¾Ý¡£
alias1,alias2
ÓÃÀ´Ìæ´úÔÚ±í¸ñʵ¼Ê×Ö¶ÎÃû³ÆµÄ»¯Ãû¡£
tableexpression
±í¸ñÃû³Æ»ò°üº¬ÎÒÃÇËùÏëÒªµÄÊý¾ÝµÄ±í¸ñ¡£
externaldatabase
ÈôʹÓõ½²»ÊÇĿǰµÄÊý¾Ý¿âÔò½«ÆäÃû×Ö¶¨ÒåÔÚexternaldatabaseµ±ÖС£
ALL,DISTINCT,DISTINCTROW,TOPÊôÐÔ´ÊÓ÷¨
SELECT [ALL|DISTINCT|DISTINCTROW|[TOP n[PERCENT]]] from table
ALL
ÈôÊÇÄú²»Ö¸¶¨ÈκεÄ×Ö¶ÎÊý¾Ý£¬ÔòMicrosoft JetÊý¾Ý¿âÒýÇæ£¨database
engine£©½«»áÑ¡ÔñËùÓеÄ×ֶΣ¬²¢ÒÀ¾ÝËù¶¨µÄÌõ¼þ²éѯ³öÐèÇóÊý¾Ý¼¯¡£
ÀýÈçÏÂÃæÕâÁ½¸öÀý×Ó½«»á¾ßÓÐÏàͬµÄЧ¹û£¬¶¼»á´ÓÖ°Ô±±í¸ñÖзµ»ØËùÓÐ×ֶεÄÊý¾Ý¡£
ÀýÈ磺
ÈôÊÇÎÒÃÇÒª²éѯ³öÖ°Ô±±í¸ñÖеÄËùÓмǼ£¬¿ÉÒÔͨ¹ýÏÂÃæµÄÓï¾äÀ´Íê³É¡£
SELECT ALL*
from Ö°Ô±±í¸ñ;
DISTINCT
¶Ôij¸ö±í¸ñËùÑ¡ÔñµÄ×Ö¶ÎÊý¾Ý£¬ÂÔ¹ýÖØ¸´µÄÇé¿ö£¬Ò²¾ÍÊÇ˵£¬Õë¶Ôij¸ö×ֶβéѯ³öÀ´µÄ¼Ç¼½á¹ûÊÇΨһµÄ¡£ÀýÈçÓÐÐí¶à´æ·ÅÔÚÖ°Ô±±í¸ñµÄÖ°Ô±
Êý¾Ý£¬Ò²Ðí»á¾ßÓÐÏàͬµÄÐÕÃû£¬ËùÒÔÈôÊÇÎÒÃÇÓÃSQLÓï¾äÖеÄSELECT
DISTINCT£¬Ôò²éѯ³öÀ´µÄ½á¹û½«»áÕë¶Ô²»Ò»ÑùµÄÐÕÃû¼ÓÒÔɸѡ¡£ÈôÊÇÄú°Ñ
DISTINCT ¼ÓÒÔÊ¡ÂÔ£¬ÔòÕâÑùµÄ²éѯ»áÏÔʾËùÓеļǼ¡£
DISTINCTROW
½«ÕûÌõ¼ÇÂ¼ÖØ¸´µÄ¼Ç¼ºöÂÔµô£¬¶ø²»ÊÇÖ»ÓÐÕë¶Ôijһ¸ö×ֶεÄÊý¾Ý¡£
table
Ö¸¶¨²éѯ¼Ç¼ËùÐèÒªµÄ±í¸ñ¡£
ÀýÈ磺
SELECT DISTINCTROW ¹«Ë¾Ãû³Æ
from ¹Ë¿Í±í¸ñ INNER JOIN ¶©µ¥±í¸ñ
ON ¹Ë¿Í±í¸ñ.¹Ë¿ÍID£½¶©µ¥±í¸ñ.¹Ë¿ÍID
ORDER BY ¹«Ë¾Ãû³Æ;
Èç¹ûÄúºöÂÔ DISTINCTROW
Ôò»á¶Ôÿ¸ö¹«Ë¾²úÉúÒ»ÐÐÒÔÏµĶ©µ¥Êý¾Ý¡£´ËÍ⣬ÈôÊÇDISTINCTROWÖ»ÓÐÓÃÔÚÒ»¸ö±í¸ñµ±ÖУ¬Ôò»á±»Ê¡ÂÔµô¡£
TOP
´ÓµÚÒ»Ìõ»ò×îºóÒ»Ìõ¿ªÊ¼£¨ÀûÓÃORDER BYÌõ¼þ×Ӿ䣩£¬·µ»ØÌض¨ÌõÊýµÄÊý¾Ý¡£
ÀýÈ磺
µ±ÄúÏëÒªÖªµÀÔÚ2000Ä꣬°àÉÏǰ25ÃûµÄѧÉúÐÕ
Ïà¹ØÎĵµ£º
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace ConsoleApplication4
{
class Program
{
static void Main(string[] args)
{
//sqlserverÉí·ÝÑéÖ¤
//string sqlconn = "ser ......
´¦ÀíSQL ServerÊý¾Ý¿âÖеĹÂÁ¢Óû§(ͼ)
°ÑÊý¾Ý¿â´ÓÒ»¸ö·þÎñÆ÷ʵÀý¸½¼ÓºÍ»Ö¸´µ½ÁíÒ»¸öʵÀýÖÐÊÇÊý¾Ý¿â¹ÜÀíÔ±Ö´Ðеij£¼ûµÄÈÎÎñ¡£¸½¼Ó»òÕ߻ָ´Ò»¸öÊý¾Ý¿âÖ®ºó£¬Ö®Ç°ÔÚÊý¾Ý¿âÖд´½¨ºÍÅäÖõĵǼÃûÒѾ²»ÄÜ·ÃÎÊÁË¡£Õâ¸öÎÊÌâ×î³£¼ûµÄÖ¢×´ÊÇÓ¦ÓóÌÐò»áÓöµ½µÇ¼ʧ°ÜµÄ´íÎ󣬻òÕßÊǵ±ÄãÊÔ×ŰѵǼÃûÌí¼Óµ½Êý¾Ý¿âÖÐʱ£¬Äã¿ÉÄÜ»áµÃµ½Ò» ......
select *
from
(select [id]=row_number() over (order by getdate()),
date=convert(varchar(8),dateadd(dd,number,'2010-01-01'),112)
from
(select number from master..spt ......
Sql Server ÓÐÈçϼ¸Ö־ۺϺ¯ÊýSUM¡¢AVG¡¢COUNT¡¢COUNT(*)¡¢MAX ºÍ MIN£¬µ«ÊÇÕâЩº¯Êý¶¼Ö»ÄܾۺÏÊýÖµÀàÐÍ£¬ÎÞ·¨¾ÛºÏ×Ö·û´®¡£Èçϱí:AggregationTable
Id Name
1 ÕÔ
2 Ç®
1 Ëï
1 Àî
2 ÖÜ
Èç¹ûÏëµÃµ½ÏÂͼµÄ¾ÛºÏ½á¹û
Id Name
1 ÕÔËïÀî
2 Ç®ÖÜ
ÀûÓÃSUM¡¢AVG¡¢COUNT¡¢COUNT(*)¡¢MAX ºÍ MINÊÇÎÞ·¨×öµ½µ ......
ÈÕÖ¾ÐòÁбàºÅ(LSN)ÊÇÊÂÎñÈÕÖ¾ÀïÃæÃ¿Ìõ¼Ç¼µÄ±àºÅ¡£
µ±ÄãÖ´ÐÐÒ»´Î±¸·Ýʱ£¬Ò»Ð©LSNÖµ¾Í±»Í¬Ê±´æ´¢ÔÚÎļþ±¾Éí¼°msdb..backupset±íÖС£Äã¿ÉÒÔʹÓÃRESTORE HEADERONLYÓï·¨À´´Ó±¸·ÝÎļþÖлñÈ¡LSNÖµ¡£
×¢Ò⣺ÔÚSQL Server 2000ÖУ¬ÓÐÒ»ÁнÐ×öDifferentialBaseLSN¡£µ«ÔÚSQL Server 2005ÖУ¬ÏàͬµÄÁÐÃû³Æ±ä³ÉÁËData ......