SQL ³õ¼¶½Ì³Ì
ÊÇÓÃÓÚ·ÃÎʺʹ¦ÀíÊý¾Ý¿âµÄ±ê×¼µÄ¼ÆËã»úÓïÑÔ¡£
ͨ¹ý SQL À´¹ÜÀíÊý¾Ý
½á¹¹»¯²éѯÓïÑÔ (SQL) ÊÇÓÃÓÚ·ÃÎÊÊý¾Ý¿âµÄ±ê×¼ÓïÑÔ£¬ÕâЩÊý¾Ý¿â°üÀ¨ SQL Server¡¢Oracle¡¢MySQL¡¢Sybase ÒÔ¼° Access µÈµÈ¡£
¶ÔÓÚÄÇЩϣÍûÔÚÊý¾Ý¿âÖд洢Êý¾Ý²¢´ÓÖлñÈ¡Êý¾ÝµÄÈËÀ´Ëµ£¬SQL µÄ֪ʶÊǼÛÖµÎÞ·¨ºâÁ¿µÄ¡£
ʲôÊÇ SQL?
SQL Ö¸½á¹¹»¯²éѯÓïÑÔ (Structured Query Language)
SQL ʹÎÒÃÇÓÐÄÜÁ¦·ÃÎÊÊý¾Ý¿â
SQL ÊÇÒ»ÖÖ ANSI µÄ±ê×¼¼ÆËã»úÓïÑÔ
SQL ÃæÏòÊý¾Ý¿âÖ´Ðвéѯ
SQL ¿É´ÓÊý¾Ý¿âÈ¡»ØÊý¾Ý
SQL ¿ÉÔÚÊý¾Ý¿âÖвåÈëеļͼ
SQL ¿É´ÓÊý¾Ý¿âɾ³ý¼Ç¼
SQL ºÜÈÝÒ×ѧϰ
±àÕß×¢£ºANSI£¬ÃÀ¹ú¹ú¼Ò±ê×¼»¯×éÖ¯
SQL ÊÇÒ»ÖÖ±ê×¼ - µ«ÊÇ...
SQL ÊÇÒ»ÃÅ ANSI µÄ±ê×¼¼ÆËã»úÓïÑÔ£¬ÓÃÀ´·ÃÎʺͲÙ×÷Êý¾Ý¿âϵͳ¡£SQL Óï¾äÓÃÓÚÈ¡»ØºÍ¸üÐÂÊý¾Ý¿âÖеÄÊý¾Ý¡£SQL ¿ÉÓëÊý¾Ý¿â³ÌÐòÐͬ¹¤×÷£¬±ÈÈç MS Access¡¢DB2¡¢Informix¡¢MS SQL Server¡¢Oracle¡¢Sybase ÒÔ¼°ÆäËûÊý¾Ý¿âϵͳ¡£
²»ÐÒµØÊÇ£¬´æÔÚןܶ಻ͬ°æ±¾µÄ SQL ÓïÑÔ£¬µ«ÊÇΪÁËÓë ANSI ±ê×¼Ïà¼æÈÝ£¬ËüÃDZØÐëÒÔÏàËƵķ½Ê½¹²Í¬µØÀ´Ö§³ÖһЩÖ÷ÒªµÄ¹Ø¼ü´Ê£¨±ÈÈç SELECT¡¢UPDATE¡¢DELETE¡¢INSERT¡¢WHERE µÈµÈ£©¡£
×¢ÊÍ£º³ýÁË SQL ±ê×¼Ö®Í⣬´ó²¿·Ö SQL Êý¾Ý¿â³ÌÐò¶¼ÓµÓÐËüÃÇ×Ô¼ºµÄ˽ÓÐÀ©Õ¹£¡
SQL Êý¾Ý¿â±í
Ò»¸öÊý¾Ý¿âͨ³£°üº¬Ò»¸ö»ò¶à¸ö±í¡£Ã¿¸ö±íÓÉÒ»¸öÃû×Ö±êʶ£¨ÀýÈç“¿Í»§”»òÕß“¶©µ¥”£©¡£±í°üº¬´øÓÐÊý¾ÝµÄ¼Ç¼£¨ÐУ©¡£
ÏÂÃæµÄÀý×ÓÊÇÒ»¸öÃûΪ "Persons" µÄ±í£º
LastNameFirstNameAddressCity
Hansen
Ola
Timoteivn 10
Sandnes
Svendson
Tove
Borgvn 23
Sandnes
Pettersen
Kari
Storgt 20
Stavanger
ÉÏÃæµÄ±í°üº¬ÈýÌõ¼Ç¼£¨Ã¿Ò»Ìõ¶ÔÓ¦Ò»¸öÈË£©ºÍËĸöÁУ¨ÐÕ¡¢Ãû¡¢µØÖ·ºÍ³ÇÊУ©¡£
SQL ²éѯ³ÌÐò
ͨ¹ý SQL£¬ÎÒÃÇ¿ÉÒÔ²éѯij¸öÊý¾Ý¿â£¬²¢»ñµÃ·µ»ØµÄÒ»¸ö½á¹û¼¯¡£
²éѯ³ÌÐòÀàËÆÕâÑù£º
SELECT LastName from Persons
½á¹û¼¯ÀàËÆÕâÑù£º
LastName
Hansen
Svendson
Pettersen
×¢ÊÍ£ºÄ³Ð©Êý¾Ý¿âϵͳҪÇóÔÚ SQL ÃüÁîµÄÄ©¶ËʹÓ÷ֺš£ÔÚÎÒÃǵĽ̳ÌÖв»Ê¹Ó÷ֺš£
SQL Êý¾Ý²Ù×÷ÓïÑÔ (DML)
SQL (½á¹¹»¯²éѯÓïÑÔ)ÊÇÓÃÓÚÖ´ÐвéѯµÄÓï·¨¡£µ«ÊÇ SQL ÓïÑÔÒ²°üº¬ÓÃÓÚ¸üС¢²åÈëºÍɾ³ý¼Ç¼µÄÓï·¨¡£
ÕâЩ²éѯºÍ¸üÐÂÓï¾ä¶¼À´×Ô SQL µÄ DML ²¿·Ö£º
SELECT - ´ÓÊý¾Ý¿â±íÖлñÈ¡Êý¾Ý
UPDATE - ¸üÐÂÊý¾Ý¿â±íÖеÄÊý¾Ý
DELETE - ´ÓÊý¾Ý¿â±íÖÐɾ³ýÊý¾Ý
INSERT INTO - ÏòÊý¾Ý¿â±íÖвåÈëÊý¾Ý
SQL Êý¾Ý¶¨Ò
Ïà¹ØÎĵµ£º
µ±×°ÉÏÁËMSSQL2005ºó£¬ÄÚ´æµÄÕ¼Óûá±äµÃºÜ´ó¡£ËùÒÔÈç¹ûÓÃÒ»¸öÅúÁ¿´¦ÀíÀ´¿ªÆô»ò¹Ø±ÕMSSQL2005ËùÓеķþÎñ£¬Äǽ«»áÈÃÎÒÃǵĵçÄÔ¸üºÃʹÓ᣸ù¾Ý×Ô¼ºµÄ¾Ñ飬×ö³öÁËÏÂÃæÁ½¸öÅú´¦Àí£º
1¡¢¿ªÆô·þÎñ£º£¨¸´ÖƺáÏßµÄÄÚÈÝ£¬×¢Ò⣬·þÎñÆ÷ÕæÕýµÄÃû³ÆÄã¿ÉÒÔͨ¹ý“¿ªÊ¼--¡·¿Ø¼þÃæ°æ--¡·¹ÜÀí¹¤¾ ......
ÓÃEXPLAIN PLAN ·ÖÎöSQLÓï¾ä
http://blog.csdn.net/kj021320/archive/2006/08/19/1096021.aspx
ÈçºÎÉú³Éexplain plan?
¡¡¡¡½â´ð:ÔËÐÐutlxplan.sql. ½¨Á¢plan ±í
¡¡¡¡Õë¶ÔÌض¨SQLÓï¾ä£¬Ê¹Óà explain plan set statement_id = 'tst1' into plan_table
¡¡¡¡ÔËÐÐutlxplp.sql »ò utlxpls.sql²ì¿´explain plan
EXPLAIN PLA ......
--
¾ÛºÏº¯Êý
use
pubs
go
select
avg
(
distinct
price)
--
Ëãƽ¾ùÊý
from
titles
where
type
=
'
business
'
go
use
pubs
go
select
max
(ytd_sales)
--
×î´óÊý
from
titles
go
use
pubs
go
select
min
(ytd_sales)
--
×îСÊý
fr ......
Ç°Ìá,MS SQLµÄͨÅä·ûº¬Ò壺
ÐòºÅ
ͨÅä·û
º¬Òå
ʾÀý
1
%
°üº¬Áã¸ö»ò¶à¸ö×Ö·ûµÄÈÎÒâ×Ö·û´®¡£
WHERE title LIKE '%computer%' ½«²éÕÒÔÚÊéÃûÖÐÈÎÒâλÖðüº¬µ¥´Ê"computer" µÄËùÓÐÊéÃû¡£
2
_
Èκε¥¸ö×Ö·û¡£
WHERE au_fname LIKE '_ean' ½«²éÕÒÒÔ ean ½áβµÄËùÓÐ 4 ¸ö×ÖĸµÄÃû×Ö£¨Dean¡¢Sean µÈ£©¡£
3
[ ] ......