SQL ²éѯÓï¾äʹÓÃ
Ò»¡¢¼òµ¥²éѯ
¡¡¡¡ ¼òµ¥µÄTransact-SQL²éѯֻ°üÀ¨Ñ¡ÔñÁÐ±í¡¢from×Ó¾äºÍWHERE×Ӿ䡣
ËüÃÇ·Ö±ð˵Ã÷Ëù²éѯÁС¢²éѯµÄ
±í»òÊÓͼ¡¢ÒÔ¼°ËÑË÷Ìõ¼þµÈ¡£
ÀýÈ磬ÏÂÃæµÄÓï¾ä²éѯtesttable±íÖÐÐÕÃûΪ“ÕÅÈý”µÄnickname×ֶκÍemail×ֶΡ£
SELECT nickname,email
from testtable WHERE name='ÕÅÈý'
(Ò»)Ñ¡ÔñÁбí
¡¡¡¡Ñ¡ÔñÁбí(select_list)Ö¸³öËù²éѯÁУ¬Ëü¿ÉÒÔÊÇÒ»×éÁÐÃûÁÐ±í¡¢ÐǺš¢±í´ïʽ¡¢±äÁ¿(°üÀ¨¾Ö²¿±ä Á¿ºÍÈ«¾Ö±äÁ¿)µÈ¹¹³É¡£
¡¡¡¡ 1¡¢Ñ¡ÔñËùÓÐÁÐ ÀýÈ磬ÏÂÃæÓï¾äÏÔʾtesttable±íÖÐËùÓÐÁеÄÊý¾Ý£º SELECT * from testtable
¡¡¡¡ 2¡¢Ñ¡Ôñ²¿·ÖÁв¢Ö¸¶¨ËüÃǵÄÏÔʾ´ÎÐò ²éѯ½á¹û¼¯ºÏÖÐÊý¾ÝµÄÅÅÁÐ˳ÐòÓëÑ¡ÔñÁбíÖÐËùÖ¸¶¨µÄÁÐÃûÅÅÁÐ˳ÐòÏàͬ¡£
ÀýÈ磺 SELECT nickname,email from testtable
¡¡¡¡ 3¡¢¸ü¸ÄÁбêÌâ ÔÚÑ¡ÔñÁбíÖУ¬¿ÉÖØÐÂÖ¸¶¨ÁбêÌâ¡£¶¨Òå¸ñʽΪ£º ÁбêÌâ=ÁÐÃû ÁÐÃû ÁбêÌâ Èç¹ûÖ¸¶¨µÄÁбêÌâ²»ÊDZê×¼µÄ±êʶ·û¸ñʽʱ£¬Ó¦Ê¹ÓÃÒýºÅ¶¨½ç·û¡£
ÀýÈ磬ÏÂÁÐÓï¾äʹÓúº×ÖÏÔʾÁÐ ±êÌ⣺ SELECT êdzÆ=nickname,µç×ÓÓʼþ=email from testtable
¡¡¡¡ 4¡¢É¾³ýÖظ´ÐÐ SELECTÓï¾äÖÐʹÓÃALL»òDISTINCTÑ¡ÏîÀ´ÏÔʾ±íÖзûºÏÌõ¼þµÄËùÓÐÐлòɾ³ýÆäÖÐÖظ´µÄÊý¾ÝÐУ¬Ä¬ÈÏ ÎªALL¡£Ê¹ÓÃDISTINCTÑ¡Ïîʱ£¬¶ÔÓÚËùÓÐÖظ´µÄÊý¾ÝÐÐÔÚSELECT·µ»ØµÄ½á¹û¼¯ºÏÖÐÖ»±£ÁôÒ»ÐС£
¡¡¡¡ 5¡¢ÏÞÖÆ·µ»ØµÄÐÐÊý ʹÓÃTOP n [PERCENT]Ñ¡ÏîÏÞÖÆ·µ»ØµÄÊý¾ÝÐÐÊý£¬TOP n˵Ã÷·µ»ØnÐУ¬¶øTOP n PERCENTʱ£¬ËµÃ÷nÊÇ ±íʾһ°Ù·ÖÊý£¬Ö¸¶¨·µ»ØµÄÐÐÊýµÈÓÚ×ÜÐÐÊýµÄ°Ù·ÖÖ®¼¸¡£
ÀýÈ磺 SELECT TOP 2 * from testtable SELECT TOP 20 PERCENT * from testtable
(¶þ)from×Ó¾ä
¡¡¡¡from×Ó¾äÖ¸¶¨SELECTÓï¾ä²éѯ¼°Óë²éѯÏà¹ØµÄ±í»òÊÓͼ¡£ÔÚfrom×Ó¾äÖÐ×î¶à¿ÉÖ¸¶¨256¸ö±í»òÊÓͼ£¬ ËüÃÇÖ®¼äÓöººÅ·Ö¸ô¡£ ÔÚfrom×Ó¾äͬʱָ¶¨¶à¸ö±í»òÊÓͼʱ£¬Èç¹ûÑ¡ÔñÁбíÖдæÔÚͬÃûÁУ¬ÕâʱӦʹÓöÔÏóÃûÏÞ¶¨ÕâЩÁÐ ËùÊôµÄ±í»òÊÓͼ¡£
ÀýÈçÔÚusertableºÍcitytable±íÖÐͬʱ´æÔÚcityidÁУ¬ÔÚ²éѯÁ½¸ö±íÖеÄcityidʱӦʹÓÃÏÂÃæÓï¾ä¸ñʽ¼ÓÒÔÏÞ¶¨£º
¡¡SELECT username,citytable.cityid
¡¡from usertable,citytable
¡¡WHERE usertable.cityid=citytable.cityid
ÔÚfrom×Ó¾äÖпÉÓÃÒÔÏÂÁ½ÖÖ¸ñʽΪ±í»òÊÓͼָ¶¨±ðÃû£º ±íÃû as ±ðÃû ±íÃû ±ðÃû
ÀýÈçÉÏÃæÓï¾ä¿ÉÓñíµÄ±ðÃû¸ñʽ±íʾΪ£º
SELECT username,b.cityid
from usertable a,citytable b
WHERE a.cityid=b.cityid
SELECT²»½öÄÜ´Ó±í»òÊÓͼÖмìË÷Êý¾Ý£¬Ë
Ïà¹ØÎĵµ£º
Ò»¡¢´´½¨Ò»ÕÅ¿Õ±í£º
Sql="Create TABLE [±íÃû]"
¶þ¡¢´´½¨Ò»ÕÅÓÐ×Ö¶ÎµÄ±í£º
Sql="Create TABLE [±íÃû]([×Ö¶ÎÃû1] MEMO NOT NULL, [×Ö¶ÎÃû2] MEMO, [×Ö¶ÎÃû3] COUNTER NOT NULL, [×Ö¶ÎÃû4] DATETIME, [×Ö¶ÎÃû5] TEXT(200), [×Ö¶ÎÃû6] TEXT(200))
×Ö¶ÎÀàÐÍ£º
2 : "SmallInt", // ÕûÐÍ
3 : "Int", ......
select *
from (
select soft.NETMODEL,
soft.softname,
soft.softid ......
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)
¡¡¡¡7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐ ......
1¡£ÆóÒµ¹ÜÀíÆ÷
´ò¿ªÄ㽨ÓÐÍâ¼üµÄ±í££ÓÒ»÷±í££Éè¼Æ±í££ÔÚÉÏ·½µã¿ª’¹ÜÀíÔ¼Êø‘££½«¼¶Á¬É¾³ýºÍ¼¶Á¬¸üÐµĹµ´òÉϾͿÉÒÔÁË
2¡£²éѯ·ÖÎöÆ÷
alter table sc
add
constraint forei ......
select upper(name) from syscolumns
where id =any
(
select Id from sysobjects where name='tb_city'
)
lower
select row_number() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É ×ÔÔö 123
select rank() over(order by firmid) from dbo.tb_BuildingMaterial
Éú³É×ÔÔö123 ¸ù¾ÝÅ ......