¹ØÓÚSql´æ´¢¹ý³Ì
SQL ÖеĴ洢¹ý³Ì£º
1.ÔÚ½¨Á¢´æ´¢¹ý³Ì֮ǰ¼ì²éËùÃüÃûµÄ´æ´¢¹ý³ÌÊÇ·ñÓ¦¾´æÔÚ¡££¨ÒòΪÈç¹ûͬÃû´æ´¢¹ý³ÌÒѾ´æÔÚ£¬ÐµĴ洢¹ý³Ì½«²»±»½¨Á¢£©
if exists(select * from sysobject where name='proc name' and type='p')
drop proc proc name
go
2.¶¨Òå´æ´¢¹ý³Ì
create proc test
@gradel int, --¶¨Òå±äÁ¿
@gradeh int output --¶¨ÒåÊä³ö±äÁ¿
as
...
go
3.Ö´Ðд洢¹ý³Ì
declare @l int,@h int
exec proc test 34,@h output
print @h
----------------------------------------------
ÏÂÃæÒÔÒ»¸öÀý×Ó˵Ã÷£º
ÊäÈëÁ½¸ö·ÖÊý£¬ÒªÇóдÁ½¸ö´æ´¢¹ý³Ì£¬Ò»¸ö¶ÔÊäÈë·ÖÊýÅÅÐò£¬ÁíÒ»¸ö²éѯÁ½·ÖÊý¶ÎÖ®¼äµÄ³É¼¨£º
Ò»¹²ÓÐÈý¸ö±í£º
s±í£º£¨s#£ºÑ§ÉúºÅ£¬sname:ѧÉúÐÕÃû£¬age:ÄêÁ䣬sex:ÐÔ±ð£©
c±í£º£¨c#£º¿Î³ÌºÅ£¬cname:¿Î³ÌÃû£¬teacher£ºÀÏʦ£©
sc±í£º£¨s#,c#,grade)
if exists(select * from sysobjects where name='sort'
and type='p')
drop proc sort
go --¶¨ÒåÒ»¸ö´æ´¢¹ý³ÌÓÃÓÚÅÅÐò
create proc sort
@high int,
@low int,--¶¨ÒåÁ½¸öÊäÈë²ÎÊý
@hi int output,
@lo int output--¶¨ÒåÁ½¸öÊä³ö²ÎÊý
as
if @high<@low
begin
set @high=@high+@low
set @low=@high-@low
set @high=@high-@low
set @hi=@high
set @lo=@low
end
go --Èç¹ûδ°´Ë³ÐòÊäÈëÔòÅÅÐò
if exists(select * from sysobjects where name='search' and type='p')
drop proc search
go --¶¨ÒåÒ»¸ö´æ´¢¹ý³ÌÓÃÓÚ²éÕÒÏàÓ¦·¶Î§µÄ¼Ç¼
create proc search
@gradeh int,
@gradel int
as
select * from sc where grade between @gradel and @gradeh
if @@rowcount=0
print '²éѯʧ°Ü'
go
declare @h int,@l int
exec testpro 70,90,@h output,@l output
exec search @h,@l
²Î¿¼£ºhttp://hi.baidu.com/rosalind1717/blog/item/bcb26ceea5a418212cf534ce.html
http://hi.baidu.com/isbx/blog/item/3e06ae514c35ac878d543094.html
Ïà¹ØÎĵµ£º
ÔÚ½éÉÜGROUP BY ºÍ HAVING ×Ó¾äÇ°£¬ÎÒÃDZØÐèÏȽ²½²sqlÓïÑÔÖÐÒ»ÖÖÌØÊâµÄº¯Êý£º¾ÛºÏº¯Êý£¬ÀýÈçSUM, COUNT, MAX, AVGµÈ¡£ÕâЩº¯ÊýºÍÆäËüº¯ÊýµÄ¸ù±¾Çø±ð¾ÍÊÇËüÃÇÒ»°ã×÷ÓÃÔÚ¶àÌõ¼Ç¼ÉÏ¡£
SELECT SUM(population) from bbc
¡¡¡¡ÕâÀïµÄSUM×÷ÓÃÔÚËùÓзµ»Ø¼Ç¼µÄpopulation×Ö¶ÎÉÏ£¬½á¹û¾ÍÊǸòéѯֻ·µ»ØÒ»¸ö½á¹û£¬¼´ËùÓйú¼ÒµÄ× ......
--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 ......
¡¡SQL·ÖÀࣺ
¡¡¡¡DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
¡¡¡¡DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
¡¡¡¡DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
¡¡¡¡Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
¡¡¡¡1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
¡¡¡¡2¡¢ËµÃ÷£ºÉ¾³ýÊý¾Ý ......
distinctÕâ¸ö¹Ø¼ü×ÖÓÃÀ´¹ýÂ˵ô¶àÓàµÄÖظ´¼Ç¼ֻ±£ÁôÒ»Ìõ£¬µ«ÍùÍùÖ»ÓÃËüÀ´·µ»Ø²»Öظ´¼Ç¼µÄÌõÊý£¬¶ø²»ÊÇÓÃËüÀ´·µ»Ø²»ÖؼǼµÄËùÓÐÖµ¡£ÆäÔÒòÊÇdistinctÖ»ÓÐÓöþÖØÑ»·²éѯÀ´½â¾ö£¬¶øÕâÑù¶ÔÓÚÒ»¸öÊý¾ÝÁ¿·Ç³£´óµÄÕ¾À´Ëµ£¬ÎÞÒÉÊÇ»áÖ±½ÓÓ°Ï쵽ЧÂʵġ£
ÏÂÃæÏÈÀ´¿´¿´Àý×Ó£º
table±í
×Ö¶Î1 ×Ö¶ ......
ÎÊÌâÃèÊö£º
±¾ÌâÓõ½ÏÂÃæÈý¸ö¹Øϵ±í£º
CARD ½èÊ鿨¡£ CNO ¿¨ºÅ£¬NAME ÐÕÃû£¬CLASS °à¼¶
BOOKS ͼÊé¡£ BNO ÊéºÅ£¬BNAME ÊéÃû,AUTHOR ×÷Õߣ¬PRICE µ¥¼Û£¬QUANTITY ¿â´æ²áÊý
BORROW ½èÊé¼Ç¼¡£ CNO ½èÊ鿨ºÅ£¬BNO ÊéºÅ ......