sqlserver ´æ´¢¹ý³ÌÀý×Ó
ʲôÊÇ´æ´¢¹ý³ÌÄØ£¿
¡¡¡¡¶¨Ò壺
¡¡¡¡½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ÉÃüÁî¡£
¡¡¡¡½²µ½ÕâÀï,¿ÉÄÜÓÐÈËÒªÎÊ£ºÕâô˵´æ´¢¹ý³Ì¾ÍÊÇÒ»¶ÑSQLÓï¾ä¶øÒÑ°¡£¿
¡¡¡¡Microsoft¹«Ë¾ÎªÊ²Ã´»¹ÒªÌí¼ÓÕâ¸ö¼¼ÊõÄØ?
¡¡¡¡ÄÇô´æ´¢¹ý³ÌÓëÒ»°ãµÄSQLÓï¾äÓÐʲôÇø±ðÄØ?
¡¡¡¡´æ´¢¹ý³ÌµÄÓŵ㣺
¡¡¡¡1.´æ´¢¹ý³ÌÖ»ÔÚ´´Ôìʱ½øÐбàÒ룬ÒÔºóÿ´ÎÖ´Ðд洢¹ý³Ì¶¼²»ÐèÔÙÖØбàÒ룬¶øÒ»°ãSQLÓï¾äÿִÐÐÒ»´Î¾Í±àÒëÒ»´Î,ËùÒÔʹÓô洢¹ý³Ì¿ÉÌá¸ßÊý¾Ý¿âÖ´ÐÐËٶȡ£
¡¡¡¡2.µ±¶ÔÊý¾Ý¿â½øÐи´ÔÓ²Ù×÷ʱ(Èç¶Ô¶à¸ö±í½øÐÐUpdate,Insert,Query,Deleteʱ£©£¬¿É½«´Ë¸´ÔÓ²Ù×÷Óô洢¹ý³Ì·â×°ÆðÀ´ÓëÊý¾Ý¿âÌṩµÄÊÂÎñ´¦Àí½áºÏÒ»ÆðʹÓá£
¡¡¡¡3.´æ´¢¹ý³Ì¿ÉÒÔÖظ´Ê¹ÓÃ,¿É¼õÉÙÊý¾Ý¿â¿ª·¢ÈËÔ±µÄ¹¤×÷Á¿
¡¡¡¡4.°²È«ÐÔ¸ß,¿ÉÉ趨ֻÓÐij´ËÓû§²Å¾ßÓжÔÖ¸¶¨´æ´¢¹ý³ÌµÄʹÓÃȨ
¡¡¡¡´æ´¢¹ý³ÌµÄÖÖÀࣺ
¡¡¡¡1.ϵͳ´æ´¢¹ý³Ì£ºÒÔsp_¿ªÍ·,ÓÃÀ´½øÐÐϵͳµÄ¸÷ÏîÉ趨.È¡µÃÐÅÏ¢.Ïà¹Ø¹ÜÀí¹¤×÷,Èç sp_help¾ÍÊÇÈ¡µÃÖ¸¶¨¶ÔÏóµÄÏà¹ØÐÅÏ¢
¡¡¡¡2.À©Õ¹´æ´¢¹ý³Ì ÒÔXP_¿ªÍ·,ÓÃÀ´µ÷ÓòÙ×÷ϵͳÌṩµÄ¹¦ÄÜ
¡¡¡¡exec master..xp_cmdshell 'ping 10.8.16.1'
¡¡¡¡3.Óû§×Ô¶¨ÒåµÄ´æ´¢¹ý³Ì,ÕâÊÇÎÒÃÇËùÖ¸µÄ´æ´¢¹ý³Ì
¡¡¡¡³£Óøñʽ
Create procedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
ql_statement
¡¡¡¡½âÊÍ:
¡¡¡¡output£º±íʾ´Ë²ÎÊýÊÇ¿É´«»ØµÄ
¡¡¡¡with {recompile|encryption}
¡¡¡¡recompile:±íʾÿ´ÎÖ´Ðд˴洢¹ý³Ìʱ¶¼ÖØбàÒëÒ»´Î
¡¡¡¡encryption:Ëù´´½¨µÄ´æ´¢¹ý³ÌµÄÄÚÈݻᱻ¼ÓÃÜ
¡¡¡¡Èç:
¡¡¡¡±íbookµÄÄÚÈÝÈçÏÂ
±àºÅ ÊéÃû ¼Û¸ñ
001 CÓïÑÔÈëÃÅ $30
002 PowerBuilder±¨±í¿ª·¢ $52
¡¡¡¡ÊµÀý1:²éѯ±íBookµÄÄÚÈݵĴ洢¹ý³Ì
create proc query_book
as
select * from book
go
exec query_book
¡¡¡¡ÊµÀý2:¼ÓÈëÒ»±Ê¼Ç¼µ½±íbook,²¢²éѯ´Ë±íÖÐËùÓÐÊé¼®µÄ×ܽð¶î
Create proc insert_book
@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output
with encryption ---------¼ÓÃÜ
as
insert book(±àºÅ,ÊéÃû£¬¼Û¸ñ£© Values(@param1,@param2,@param3)
select @param4=sum(¼Û¸ñ) from book
go
¡¡¡¡Ö´ÐÐÀý×Ó:
declare @total_price money
exec insert_book '003','Delphi ¿Ø¼þ¿ª·¢Ö¸ÄÏ',$100,@total_p
Ïà¹ØÎĵµ£º
²éѯnorthwindÊý¾Ý¿âÖÐordersÖеÄ10µ½20Ìõ¼Ç¼
select top 10 * from orders
where orderid > ( select max(orderid) from (select top 10 orderid from orders order by orderid) as t&nbs ......
BULK INSERT
¡¡¡¡ÔÚSQL ServerÖУ¬BULK INSERTÊÇÓÃÀ´½«ÍⲿÎļþÒÔÒ»ÖÖÌض¨µÄ¸ñʽ¼ÓÔص½Êý¾Ý¿â±íµÄT-SQLÃüÁî¡£¸ÃÃüÁîʹ¿ª·¢ÈËÔ±Äܹ»Ö±½Ó½«Êý¾Ý¼ÓÔص½Êý¾Ý¿â±íÖУ¬¶ø²»ÐèҪʹÓÃÀàËÆÓÚIntegration ServicesÕâÑùµÄÍⲿ³ÌÐò¡£ËäÈ»BULK INSERT²»ÔÊÐí°üº¬Èκθ´ÔÓµÄÂß¼»òת»»£¬µ«Äܹ»ÌṩÓë¸ñʽ»¯Ïà¹Øµ ......
select case when c.colid=1 then object_name(c.id) else '' end as ±íÃû
,c.name as ×Ö¶ÎÃû
,t.name Êý¾ÝÀàÐÍ
,c.prec as ³¤¶È ......
¡¾ÎÊÌâÃèÊö£º¡¿
Ò»¸öÓû§±íÖеÄ×¢²áÈÕÆÚÏÔʾ¸ñʽÊÇ£ºyyyy-mm-dd Thh:mm:ss.mmm ¡£¶øÎÒÏëͳ¼Æ³öÿÌìµÄÓû§×¢²áÊý£¬Ö±½Ógroup×¢²áÈÕÆÚ×Ö¶ÎÏÔÈ»ÊDz»Ðеġ£
¡¾ÎÊÌâ´¦Àí£º¡¿
ÀûÓÃSQLserverÖеÄconvertº¯Êý¶ÔÈÕÆÚ½øÐÐת»»×ª»»¡£groupת»»ºóµÄÈÕÆÚ¡£
¡¾½Å  ......
create database db
use db
go
create table course
(
sno varchar(20),
cno int ,
Gmark int
)
insert into course values('20071513115',1,80)
insert into course values('20071513114',2,80)
insert into course values('20071513113',3,80)
insert into course values('20071513 ......