Ò»¸ösql¼òµ¥º¯ÊýʵÏÖÆ´½Ó×Ö·û´®
--²âÊÔÊý¾Ý
create table table1(AID int,NAME nvarchar(20))
create table table2 (BID int,NUMBER nvarchar(20))
insert into table1 select 1,'Tom' union all
select 2,'Jim'
insert into table2 select 1,20 union all
select 1,30
--º¯Êý
create function F_Str(@ID int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select return @S=isnull(@S+',','')+NUMBER from table2 where BID=@ID
return @S
end
--²éѯ
select NAME,NUMBER = dbo.F_Str(AID) from table1
--½á¹û
-------------------
Tom 20,30
Jim NULL
Ïà¹ØÎĵµ£º
Oracle ¶¯Ì¬SQL·µ»Øµ¥Ìõ½á¹ûºÍ½á¹û¼¯
1. DDL ºÍ DML
/**//*** DDL ***/
begin
EXECUTE IMMEDIATE 'drop table temp_1';
EXECUTE IMMEDIATE 'create table temp_1(name varchar2(8))';
end;
/**//*** DML ***/
declare
v_1 varchar2(8);
& ......
ÕýÈ·µÄÐ޸ķ½·¨£ºsqlÅúÁ¿ÐÞ¸Ä×Ö¶ÎÄÚÈݵÄÓï¾ä
1¡¢Ã»ÊÔ¹ý£¬ÍøÉÏËÑË÷µÄ
ÒýÓÃ
update '±íÃû' set ÒªÐÞ¸Ä×Ö¶ÎÃû = replace (ÒªÐÞ¸Ä×Ö¶ÎÃû,'±»Ìæ»»µÄÌØ¶¨×Ö·û','Ìæ»»³ÉµÄ×Ö·û')
2¡¢×Ô¼º°´ÕÕÒ»¸öÒ»¸öÐÞ¸ÄÊÇ¿´Êý¾Ý±í×ܽáµÄ
ÀýÈçżÐÞ¸Ä×Ô¼º²©¿Íboblog_replies±íÖеÄadminrepidºÍadminreplier×Ö¶ÎÖµ£º
UPDATE `xxx`.`boblog_ ......
ÔÚѧϰSQLʱ¿´µ½µÄһƬºÜºÃµÄÎÄÕ£¬ÌØÌù³öÀ´ºÍ´ó¼ÒÒ»Æð·ÖÏí£¡
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQLÓï¾ä¡£
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
OracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦ ......
l INNER JOIN
ÄÚÁ¬½ÓÊÇ×î³£¼ûµÄÒ»ÖÖÁ¬½Ó£¬ËüÒ³±»³ÆÎªÆÕͨÁ¬½Ó£¬¶øE.FCodd×îÔç³ÆÖ®Îª×ÔÈ»Á¬½Ó¡£
ÏÂÃæÊÇANSI SQL£92±ê×¼
select * from t_institution i
inner join t_teller t
on i.inst_no = t.inst_no //˵Á½¸ö±íÖ®¼äµÄ¹ØÏµÓÃON
where i.inst_no = "5801"
ÆäÖÐinner¿ÉÒÔʡ ......
1. ´æ´¢¹ý³Ì(¶¨Òå&±àд)
l ´´½¨´æ´¢¹ý³Ì
CREATE PROCEDURE storedproc1
AS
SELECT *
from tb_project
WHERE Ô¤¼Æ¹¤ÆÚ<= 90
ORDER BY Ô¤¼Æ¹¤ÆÚ DESC
GO
exec storedproc1
GO
l Ð޸Ĵ洢¹ý³Ì
ALTER PROCEDURE storedproc1
AS
SEL ......