SqlÊý¾ÝÓÅ»¯·½°¸
1¡¢Ê¹ÓÃË÷ÒýÀ´¸ü¿ìµØ±éÀú±í¡£
ȱʡÇé¿öϽ¨Á¢µÄË÷ÒýÊÇ·ÇȺ¼¯Ë÷Òý£¬µ«ÓÐʱËü²¢²»ÊÇ×î¼ÑµÄ¡£ÔÚ·ÇȺ¼¯Ë÷ÒýÏ£¬Êý¾ÝÔÚÎïÀíÉÏËæ»ú´æ·ÅÔÚÊý¾ÝÒ³ÉÏ¡£ºÏÀíµÄË÷ÒýÉè¼ÆÒª½¨Á¢ÔÚ¶Ô¸÷ÖÖ²éѯµÄ·ÖÎöºÍÔ¤²âÉÏ¡£
Ò»°ãÀ´Ëµ£º
a.ÓдóÁ¿Öظ´Öµ¡¢ÇÒ¾³£Óз¶Î§²éѯ£¨ > ,< £¬> =,< =£©ºÍorder by¡¢group by·¢ÉúµÄÁУ¬¿É¿¼
Âǽ¨Á¢Èº¼¯Ë÷Òý£»
b.¾³£Í¬Ê±´æÈ¡¶àÁУ¬ÇÒÿÁж¼º¬ÓÐÖظ´Öµ¿É¿¼Âǽ¨Á¢×éºÏË÷Òý£»
c.×éºÏË÷ÒýÒª¾¡Á¿Ê¹¹Ø¼ü²éѯÐγÉË÷Òý¸²¸Ç£¬ÆäÇ°µ¼ÁÐÒ»¶¨ÊÇʹÓÃ×îƵ·±µÄÁС£Ë÷ÒýËäÓÐÖúÓÚÌá¸ßÐÔÄܵ«²»ÊÇË÷ÒýÔ½¶àÔ½ºÃ£¬Ç¡ºÃÏà·´¹ý¶àµÄË÷Òý»áµ¼ÖÂϵͳµÍЧ¡£
Óû§ÔÚ±íÖÐÿ¼Ó½øÒ»¸öË÷Òý£¬Î¬»¤Ë÷Òý¼¯ºÏ¾ÍÒª×öÏàÓ¦µÄ¸üй¤×÷¡£
2¡¢ÔÚº£Á¿²éѯʱ¾¡Á¿ÉÙÓøñʽת»»¡£
3¡¢ORDER BYºÍGROPU BYʹÓÃORDER BYºÍGROUP BY¶ÌÓÈκÎÒ»ÖÖË÷Òý¶¼ÓÐÖúÓÚSELECTµÄÐÔÄÜÌá¸ß¡£
4¡¢IN¡¢OR×Ӿ䳣»áʹÓù¤×÷±í£¬Ê¹Ë÷ÒýʧЧ¡£Èç¹û²»²úÉú´óÁ¿Öظ´Öµ£¬¿ÉÒÔ¿¼ÂÇ°Ñ×Ó¾ä²ð¿ª¡£²ð¿ªµÄ×Ó¾äÖÐÓ¦¸Ã°üº¬Ë÷Òý¡£
SqlµÄÓÅ»¯ÔÔò2£º
1¡¢Ö»ÒªÄÜÂú×ãÄãµÄÐèÇó£¬Ó¦¾¡¿ÉÄÜʹÓøüСµÄÊý¾ÝÀàÐÍ£ºÀýÈçʹÓÃMEDIUMINT´úÌæINT
2¡¢¾¡Á¿°ÑËùÓеÄÁÐÉèÖÃΪNOT NULL£¬Èç¹ûÄãÒª±£´æNULL£¬ÊÖ¶¯È¥ÉèÖÃËü£¬¶ø²»ÊÇ°ÑËüÉèΪĬÈÏÖµ¡£
3¡¢¾¡Á¿ÉÙÓÃVARCHAR¡¢TEXT¡¢BLOBÀàÐÍ
4¡¢Èç¹ûÄãµÄÊý¾ÝÖ»ÓÐÄãËùÖªµÄÉÙÁ¿µÄ¼¸¸ö¡£×îºÃʹÓÃENUMÀàÐÍ
5¡¢ÕýÈçgraymiceËù½²µÄÄÇÑù£¬½¨Á¢Ë÷Òý¡£
ÒÔÏÂÊÇÎÒ×öµÄÒ»¸öʵÑ飬¿ÉÒÔ·¢ÏÖË÷ÒýÄܼ«´óµØÌá¸ß²éѯµÄЧÂÊ£º
ÎÒÓÐÒ»¸ö»áÔ±ÐÅÏ¢±íusers£¬Àï±ßÓÐ37365ÌõÓû§¼Ç¼£º
ÔÚ²»¼ÓË÷ÒýµÄʱºò½øÐвéѯ£º
sqlÓï¾äA:
select * from users where username like ’%Ðí%’;
ÔÚMysql-FrontÖеÄ8´Î²éѯʱ³¤Îª£º1.40,0.54,0.54,0.54,0.53,0.55,0.54 ¹²ÕÒµ½960Ìõ¼Ç¼
sqlÓï¾äB:
select * from users where username like ’Ðí%’;
ÔÚMysql-FrontÖеÄ8´Î²éѯʱ³¤Îª£º0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 ¹²ÕÒµ½836Ìõ¼Ç¼
sqlÓï¾äC:
select * from users where username like ’%Ðí’;
ÔÚMysql-FrontÖеÄ8´Î²éѯʱ³¤Îª£º0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 ¹²ÕÒµ½7Ìõ¼Ç¼
ΪusernameÁÐÌí¼ÓË÷Òý:
create index usernameindex on users(username(6));
Ôٴβéѯ£º
sqlÓï¾äA:
select * from users where username like ’%Ðí%’;
ÔÚMysql-FrontÖеÄ8´Î²éѯʱ³¤Îª£º0.35,0.34,0.34,0.35,0.34,0.34,0.
Ïà¹ØÎĵµ£º
1.Ñ¡Ôñ²Ù×÷µÄÊý¾Ý¿â
Á¬½Óµ½·þÎñÆ÷Ö®ºó£¬Ê×ÏÈÐèҪѡÔñÒª½øÐвÙ×÷µÄÊý¾Ý¿â¡£Ê¹ÓÃPHPµÄMSSQLÀ©Õ¹¿âÖеÄmssql_select_db()º¯Êý¼´¿ÉʵÏÖÁ¬½Ó·þÎñÆ÷ÉϵÄÊý¾Ý¿â¡£¸Ãº¯Êý¸ñʽÈçÏ£º
bool mssql_select_db(string database_name[,resource link_identifier)
¸Ãº¯Êý¸÷²ÎÊýÒâÒ壺
a. ²ÎÊý database_nameΪָ¶¨µÄ·þÎñÆ÷ÉÏ¿ÉÓõÄÊý¾Ý ......
×Ó²éѯ£º
ʹÓÃ×Ó²éѯµÄÔÔò
1.Ò»¸ö×Ó²éѯ±ØÐë·ÅÔÚÔ²À¨ºÅÖС£
2.½«×Ó²éѯ·ÅÔڱȽÏÌõ¼þµÄÓÒ±ßÒÔÔö¼Ó¿É¶ÁÐÔ¡£
×Ó²éѯ²»°üº¬ ORDER BY ×Ӿ䡣¶ÔÒ»¸ö SELECT Óï¾äÖ»ÄÜÓÃÒ»¸ö ORDER BY ×Ӿ䣬
²¢ÇÒÈç¹ûÖ¸¶¨ÁËËü¾Í±ØÐë·ÅÔÚÖ÷ SELECT Óï¾äµÄ×îºó¡£
ORDER BY ×Ó¾ä¿ÉÒÔʹÓ㬲¢ÇÒÔÚ½øÐÐ Top-N ·ÖÎöʱÊDZØÐëµÄ¡£
3.ÔÚ×Ó² ......
SQL SERVERά»¤×ÅÒ»×é±í,´æ´¢ËùÓжÔÏó,Êý¾ÝÀàÐÍ,Ô¼Êø,ÅäÖÃÑ¡Ïî¼°SQLÄÜ·ÃÎʵÄ×ÊÔ´,ÕâЩ±í³ÆΪϵͳĿ¼(ϵͳ»ù±¾±í).
ÎÒÃÇÔÚSQL SERVER2005ÀïÓкܶàÖÖ·½·¨À´·ÃÎÊÔªÊý¾Ý.Ò»°ãÊÇʹÓÃĿ¼ÊÓͼºÍϵͳº¯Êý,ÐÅÏ¢¼Ü¹¹ÊÓͼֻÄÜ°üº¬ËüÃÇÈ«²¿µÄÒ»²¿·Ö.
×î¼Ñ½Ó¿Ú£ºÄ¿Â¼ÊÓͼ
Ŀ¼ÊÓͼ¶¼Î»ÓÚSYS¼Ü¹¹ÏÂ,°üÀ¨Á˶¯Ì¬¹ÜÀí¶ÔÏó(DMVºÍDM ......
ÏÐÀ´Ð´ÏÂwith cubeµÄÓ÷¨
cubeÔËËã·ûÔÚ SELECT Óï¾äµÄ GROUP BY ×Ó¾äÖÐÖ¸¶¨¡£¸ÃÓï¾äµÄÑ¡ÔñÁбíÓ¦°üº¬Î¬¶ÈÁк;ۺϺ¯Êý±í´ïʽ¡£GROUP BY Ó¦Ö¸¶¨Î¬¶ÈÁк͹ؼü×Ö WITH CUBE¡£½á¹û¼¯½«°üº¬Î¬¶ÈÁÐÖи÷ÖµµÄËùÓпÉÄÜ×éºÏ£¬ÒÔ¼°ÓëÕâЩά¶ÈÖµ×éºÏÏàÆ¥ÅäµÄ»ù´¡ÐÐÖеľۺÏÖµ¡£
ÏÈ¿´ÏÂ±í£º
ÎÒÃÇÒÔid¾ÛºÏ²éѯ³öƽ¾ù·Ö
ÕâÒ»ÌõSQLÓï¾äÓ ......
--1. ´´½¨±í£¬Ìí¼Ó²âÊÔÊý¾Ý
CREATE TABLE tb(id int, [value] varchar(10))
INSERT tb SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'
--SELECT * from tb
/**//*
id value
----------- ----------
1 aa
1 ......