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 Êý¾Ý¶¨Ò
Ïà¹ØÎĵµ£º
ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£
¡¡¡¡ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£¼ÇµÃÔÚ¸øÒ»¼Ò ......
¾ÛºÏº¯Êý
MAX(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×î´óÖµ
MIN(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×îСֵ
AVG(×Ö¶Î)
Çóij×Ö¶ÎÖеÄƽ¾ùÖµ
SUM(×Ö¶Î)
Çóij×Ö¶ÎÖеÄ×ܺÍ
COUNT(×Ö¶Î)
ͳ¼Æij×ֶηǿռͼÊý
COUNT
......
ÓÅ»¯´æ´¢¹ý³ÌÓкܶàÖÖ·½·¨£¬ÏÂÃæ½éÉÜ×î³£ÓõÄ7ÖÖ¡£
1.ʹÓÃSET NOCOUNT ONÑ¡Ïî
ÎÒÃÇʹÓÃSELECTÓï¾äʱ£¬³ýÁË·µ»Ø¶ÔÓ¦µÄ½á¹û¼¯Í⣬»¹»á·µ»ØÏàÓ¦µÄÓ°ÏìÐÐÊý¡£Ê¹ÓÃSET NOCOUNT ONºó£¬³ýÁËÊý¾Ý¼¯¾Í²»»á·µ»Ø¶îÍâµÄÐÅÏ¢ÁË£¬¼õСÍøÂçÁ÷Á¿¡£
2.ʹÓÃÈ·¶¨µÄSchema
ÔÚʹÓÃ±í£¬´æ´¢¹ý³Ì£¬º¯ÊýµÈµÈʱ£¬×îºÃ¼ÓÉÏÈ·¶¨µÄSchema¡£ÕâÑù¿ÉÒÔÊ ......
(
8)
SELECT
(
9)
DISTINCT
(
11)
<
TOP_specification>
<
select_list>
(
1)
from
<
left_table>
(
3)
<
join_type>
JOIN
<
right_table>
(
2)
ON
<
join_codition>
(
4)
WHERE
<
where_condition>
(
5)
GROUP
BY
& ......
µ¼Èë
Èç¹û±íÒÑ´æÔÚ£¬SQLÓï¾äΪ£º
insert into aa select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\OutData.xls;Extended Properties=Excel 8.0')...[sheet1$]
ÆäÖУ¬aaÊDZíÃû£¬D:\OutData.xlsÊÇexcelµÄȫ·¾¶ sheet1ºó±ØÐë¼ÓÉÏ$
Èç¹û±í²»´æÔÚ£¬SQLÓï¾äΪ£º
SELECT * INTO aa from OPENDAT ......