--sql structured query language
--DML--Data Manipulation Language--Êý¾Ý²Ù×÷ÓïÑÔ
query information (SELECT),
add new rows (INSERT),
modify existing rows (UPDATE),
delete existing rows (DELETE),
perform a conditional update or insert operation (MERGE),
see an execution plan of SQL (EXPLAIN PLAN),
and lock a table to restrict access (LOCK TABLE).
--DDL--Data Definition Language--Êý¾Ý¶¨ÒåÓïÑÔ
create, modify,drop, or rename objects (CREATE,ALTER,DROP,RENAME),
remove all rows from a database object without dropping the structure (TRUNCATE),
manage access privileges (GRANT,REVOKE),
audit database use (AUDIT,NOAUDIT)
and add a description about an object to the dictionary (COMMENT).
--Transaction ControlÊÂÎñ¿ØÖÆÓï¾ä
save the changes(COMMIT)
or discard the changes (ROLLBACK) made by DML statements.
Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback (SAVEPOINT)
and to define th ......
£¨×ªÔØ£©SQL 2K Êý¾ÝÀàÐÍ
(1)char¡¢varchar¡¢textºÍnchar¡¢nvarchar¡¢ntext
charºÍvarcharµÄ³¤¶È¶¼ÔÚ1µ½8000Ö®¼ä£¬ËüÃǵÄÇø±ðÔÚÓÚcharÊǶ¨³¤×Ö·ûÊý¾Ý£¬¶øvarcharÊDZ䳤×Ö·ûÊý¾Ý¡£Ëùν¶¨³¤¾ÍÊdz¤¶È¹Ì¶¨µÄ£¬µ±ÊäÈëµÄÊý¾Ý³¤¶ÈûÓдﵽָ¶¨µÄ³¤¶Èʱ½«×Ô¶¯ÒÔÓ¢ÎÄ¿Õ¸ñÔÚÆäºóÃæÌî³ä£¬Ê¹³¤¶È´ïµ½ÏàÓ¦µÄ³¤¶È£»¶ø±ä³¤×Ö·ûÊý¾ÝÔò²»»áÒÔ¿Õ¸ñÌî³ä¡£text´æ´¢¿É±ä³¤¶ÈµÄ·ÇUnicodeÊý¾Ý£¬×î´ó³¤¶ÈΪ2^31-1(2,147,483,647)¸ö×Ö·û¡£
ºóÃæÈýÖÖÊý¾ÝÀàÐͺÍÇ°ÃæµÄÏà±È£¬´ÓÃû³ÆÉÏ¿´Ö»ÊǶàÁ˸ö×Öĸ"n"£¬Ëü±íʾ´æ´¢µÄÊÇUnicodeÊý¾ÝÀàÐ͵Ä×Ö·û¡£Ð´¹ý³ÌÐòµÄÅóÓѶÔUnicodeÓ¦¸ÃºÜÁ˽⡣×Ö·ûÖУ¬Ó¢ÎÄ×Ö·ûÖ»ÐèÒªÒ»¸ö×Ö½Ú´æ´¢¾Í×ã¹»ÁË£¬µ«ºº×ÖÖڶ࣬ÐèÒªÁ½¸ö×Ö½Ú´æ´¢£¬Ó¢ÎÄÓ뺺×Öͬʱ´æÔÚʱÈÝÒ×Ôì³É»ìÂÒ£¬Unicode×Ö·û¼¯¾ÍÊÇΪÁ˽â¾ö×Ö·û¼¯ÕâÖÖ²»¼æÈݵÄÎÊÌâ¶ø²úÉúµÄ£¬ËüËùÓеÄ×Ö·û¶¼ÓÃÁ½¸ö×Ö½Ú±íʾ£¬¼´Ó¢ÎÄ×Ö·ûÒ²ÊÇÓÃÁ½¸ö×Ö½Ú±íʾ¡£nchar¡¢nvarcharµÄ³¤¶ÈÊÇÔÚ1µ½4000Ö®¼ä¡£ºÍchar¡¢varchar±È½Ï£ºnchar¡¢nvarcharÔò×î¶à´æ´¢4000¸ö×Ö·û£¬²»ÂÛÊÇÓ¢ÎÄ»¹ÊǺº×Ö£»¶øchar¡¢varchar×î¶àÄÜ´æ´¢8000¸öÓ¢ÎÄ£¬4000¸öºº×Ö¡£¿ÉÒÔ¿´³öʹÓÃnchar¡¢nvarcharÊý¾ÝÀàÐÍʱ²»Óõ£ÐÄÊäÈëµÄ×Ö·ûÊÇÓ¢ÎÄ»¹ÊǺº×Ö£¬½ÏΪ·½±ã£¬µ«ÔÚ´æ ......
ÕâÁ½Ì춼ûÓкúÃѧϰ£¬½ñÌìÖÕÓÚËãͶÈëÁËЩ£¬ÓÉÓڿγ̵ĹØϵ£¬ÎÒµÄsqlÒ²ÊÇͬvbÒ»ÆðѧϰµÄ¡£ËäȻ֪µÀÌ°¶à½À²»À㬿ÉÊÇ°´ÕÕʵ¼ÊÇé¿ö£¬ÎÒÍêȫûÓÐÀíÓÉÅ×ÆúsqlµÄ¡£
×î½ü¶¼°Ñʱ¼äͶÈëµ½vbºÍÃæÏò¶ÔÏóÖУ¬sql½ñÌìºÃºÃ¸´Ï°ÁËÏ£¬´Ócreate database ¿ªÊ¼£¬create table£¬alter table £¬ add constraint …… check£¨£©£¬insert into……values£¨£©£¬insert …… select……union£¬½ñÍíÎÒ°ÑÕâЩ¶¼¸´Ï°ÁËÒ»±é£¬»¹ÓÐѧÁËһЩ»ù±¾µÄ²éѯ£¬Ê±¼ä¾ÍÒѾ¹ýÈ¥ÁË¡£¸Ð¾õ»¹Í¦³äʵµÄ£¬ËäÈ»ºÜ¼òµ¥£¬µ«ÊÇÒ²¾ÍÊÇÒòΪÕâÑùÎÒ²ÅÓмÌÐøÏÂÈ¥µÄÓÂÆøºÍÐÅÐÄ°¡£¬ÎÒÏàÐÅ£¬ÔÚÕâÁ½ÄêÀÎÒ»á°Ñ×Ô¼º´ÓÒ»ÎÞËùÖªµÄѧÉú±ä³ÉÒ»¸öºÏ¸ñµÄ³ÌÐòÔ±£¬Ö»ÊǼ°¸ñ£¬²»ÒªÇóÓÅÐ㣬ÒòΪÕâÊÇÎÒ½ñºóÂþ³¤µÄ×ßÑ°µÀ·¡£
½ñÌìÈ¥¶îÍâµÄÌýÁËc++µÄ¿Î£¬ÊÇÒÔÇ°ÀÏʦÉϵģ¬Î»ÖúÜÔ¶£¬Ã»Ìýµ½Ê²Ã´£¬µ«ÊÇËû¸øµÄcppÎļþÎÒ×ÐϸµÄ¿´ÁË¿´¡£ÊǺºÅµÒÁÎÊÌ⣬½²µÄÊǵݹéµÄÎÊÌ⣬ÎÒ¿´Á˺ܾòŰÑÔõôµÝ¹éµ÷Óøø¿´Çå³þ£¬µ«ÊÇÖ±µ½ÏÖÔÚ»¹²»ÄÜ˵ÍêÈ«ÕÆÎÕ¡£µÝ¹éÎÒÊǶ®ÁË£¬µ«ÊÇÄǸöËã·¨ÎÒûŪµÃ³¹µ×Ã÷°×¡£ÒÔÇ°ÌýµÄʱºò¾ÍÒÅÁôÏÂÀ´µÄÎÊÌ⣬ÏÖÔÚÓÖÊǶÔÎҵĿ¼ÑéÁË¡£²»¹ýÓÐÁ˵ã»ù´¡£¬ÎÒ»¹ÊǰѺ¯ÊýµÄµ÷Óà ......
ÔÚPL/SQLÖÐÒ²ÌṩGOTOÓï¾ä,ÆäÓï·¨ÊÇ
GOTO label;
ÕâÀïlabelÊÇÔÚPL/SQL¿éÖж¨ÒåµÄ±êÇ©.±êÇ©ÊÇÓÃË«¼ýÍ·À¨ºÅÀ¨ÆðÀ´µÄ.µ±Ö´ÐÐGOTOÓï¾äµÄʱºò,¿ØÖÆ»áÁ¢¼´×ªµ½ÓɱêÇ©±êʶµÄÓï¾ä.
1.¶ÔÓÚGOTOµÄÏÞÖÆ
a.¶ÔÓÚ¿é,Ñ»·»òÕßIFÓï¾ä¶øÑÔ,ÏëÒª´ÓÍâ²ãÌøµ½ÄÚ²ãÊÇ·Ç·¨µÄ.
b.ʹÓÃGOTO×Ó¾ä´ÓÒ»¸öIF×Ó¾äÌøµ½ÁíÒ»¸ö×Ó¾äÖÐÒ²ÊÇ·Ç·¨µÄ.
begin
if x>3 then
...
goto<<lbl>>
else
<<lbl>>
...
end if;
end;
c.´ÓÒ»¸öÒì³£´¦Àí¿éÄÚÌøתµ½µ±Ç°¿éÊÇ·Ç·¨µÄ¡£
2.Ϊѻ·É趨±êÇ©
Ñ»·±¾ÉíÊÇ¿ÉÒÔ±»É趨±êÇ©µÄ¡£Èç¹û½øÐÐÁËÉ趨£¬ÄÇô¿ÉÒÔÔÚEXITÓï¾äÖÐʹÓøñêÇ©Ö¸Ã÷ÒªÍ˳öÄĸöÑ»·.ÀýÈç:
<<l_outer>>
for v_outer_index in 1..10 loop
...
<<I_inner>>
for v_inner_index in 1..30 loop
...
if v_outer_index > 5 then
EXIT I_outer;
end if;
end loop I_inner;
end loop I_outer;
Èç¹ûÑ»·É趨Á˱êÇ©£¬ÄÇô¿ÉÒÔÔÚEND LOOPÓï¾äµÄºóÃæ°üº¬¸Ã±êÇ©Ãû,ÈçÉÏÃæËùʾ.
3.½÷É÷µÄʹÓÃGOTOÓï¾ä
ÔÚʹÓÃGOTOÓï¾äʱһ¶¨ÒªÐ¡ÐÄ,²»È»¶¼ÊÇЩÌøÀ´ÌøÈ¥µÄ´úÂë,ºÜÄÑÀí½âÒ²ºÜÄÑά»¤.
¼¸ºõËùÓÐʹÓÃGOTOµÄÇé¿ö¶¼¿ÉÒÔʹÓÃÆäËûµÄPL/SQL¿ØÖƽṹ ......
×¢'svw'Ϊ³öÎÊÌâµÄÊý¾Ý¿â,´Ë·½Ê½¶Ôsql7.0ÒÔÉÏ°æ±¾ÓÐЧ,ÆäËüµÍ°æ±¾Îª²âÊÔ
sp_configure 'allow',1
go
reconfigure with override
go
update sysdatabases set status=32768 where name='svw'
go
dbcc rebuild_log('svw','D:\mssql7\data\svw_Log.ldf')
go
update sysdatabases set status=0 where name='svw'
go
sp_configure 'allow',0
go
reconfigure with override
go
dbcc checkdb('svw')
go
DBCC CHECKTABLE (sysobjects)
DBCC CHECKTABLE (sysindexes)
DBCC CHECKTABLE (syscolumns )
DBCC CHECKTABLE (systypes) ......
SqlHelper ÀàʵÏÖÏêϸÐÅÏ¢
SqlHelper ÀàÓÃÓÚͨ¹ýÒ»×龲̬·½·¨À´·â×°Êý¾Ý·ÃÎʹ¦ÄÜ¡£¸ÃÀ಻Äܱ»¼Ì³Ð»òʵÀý»¯£¬Òò´Ë½«ÆäÉùÃ÷Ϊ°üº¬×¨Óù¹Ô캯ÊýµÄ²»¿É¼Ì³ÐÀà¡£
ÔÚ SqlHelper ÀàÖÐʵÏÖµÄÿÖÖ·½·¨¶¼ÌṩÁËÒ»×éÒ»ÖµÄÖØÔØ¡£ÕâÌṩÁËÒ»ÖֺܺõÄʹÓà SqlHelper ÀàÀ´Ö´ÐÐÃüÁîµÄģʽ£¬Í¬Ê±Îª¿ª·¢ÈËԱѡÔñ·ÃÎÊÊý¾ÝµÄ·½Ê½ÌṩÁ˱ØÒªµÄÁé»îÐÔ¡£Ã¿ÖÖ·½·¨µÄÖØÔض¼Ö§³Ö²»Í¬µÄ·½·¨²ÎÊý£¬Òò´Ë¿ª·¢ÈËÔ±¿ÉÒÔÈ·¶¨´«µÝÁ¬½Ó¡¢ÊÂÎñºÍ²ÎÊýÐÅÏ¢µÄ·½Ê½¡£ÔÚ SqlHelper ÀàÖÐʵÏֵķ½·¨°üÀ¨£º
ExecuteNonQuery¡£´Ë·½·¨ÓÃÓÚÖ´Ðв»·µ»ØÈκÎÐлòÖµµÄÃüÁî¡£ÕâЩÃüÁîͨ³£ÓÃÓÚÖ´ÐÐÊý¾Ý¿â¸üУ¬µ«Ò²¿ÉÓÃÓÚ·µ»Ø´æ´¢¹ý³ÌµÄÊä³ö²ÎÊý¡£
ExecuteReader¡£´Ë·½·¨ÓÃÓÚ·µ»Ø SqlDataReader ¶ÔÏ󣬸öÔÏó°üº¬ÓÉijһÃüÁî·µ»ØµÄ½á¹û¼¯¡£
ExecuteDataset¡£´Ë·½·¨·µ»Ø DataSet ¶ÔÏ󣬸öÔÏó°üº¬ÓÉijһÃüÁî·µ»ØµÄ½á¹û¼¯¡£
ExecuteScalar¡£´Ë·½·¨·µ»ØÒ»¸öÖµ¡£¸ÃֵʼÖÕÊǸÃÃüÁî·µ»ØµÄµÚÒ»ÐеĵÚÒ»ÁС£
ExecuteXmlReader¡£´Ë·½·¨·µ»Ø FOR XML ²éѯµÄ XML Ƭ¶Î¡£
³ýÁËÕâЩ¹«¹²·½·¨Í⣬SqlHelper À໹°üº¬Ò»Ð©×¨Óú¯Êý£¬ÓÃÓÚ¹ÜÀí²ÎÊýºÍ×¼±¸ÒªÖ´ÐеÄÃüÁî¡£²»¹Ü¿Í»§¶Ëµ÷ÓÃʲôÑùµÄ·½·¨ÊµÏÖ£¬ËùÓÐÃüÁͨ¹ý SqlCommand ¶ÔÏ ......