linq to sql Óï·¨ ѧϰ±Ê¼Ç ´æÒ»ÏÂ
Join²Ù×÷·û
ÊÊÓó¡¾°£ºÔÚÎÒÃÇ±í¹ØÏµÖÐÓÐÒ»¶ÔÒ»¹ØÏµ£¬Ò»¶Ô¶à¹ØÏµ£¬¶à¶Ô¶à¹ØÏµµÈ¡£¶Ô¸÷¸ö±íÖ®¼äµÄ¹ØÏµ£¬¾ÍÓÃÕâЩʵÏÖ¶Ô¶à¸ö±íµÄ²Ù×÷¡£
˵Ã÷£ºÔÚJoin²Ù×÷ÖУ¬·Ö±ðΪJoin(Join²éѯ), SelectMany(SelectÒ»¶Ô¶àÑ¡Ôñ)ºÍGroupJoin(·Ö×éJoin²éѯ)¡£
¸ÃÀ©Õ¹·½·¨¶ÔÁ½¸öÐòÁÐÖмüÆ¥ÅäµÄÔªËØ½øÐÐinner join²Ù×÷
SelectMany
˵Ã÷£ºÎÒÃÇÔÚд²éѯÓï¾äʱ£¬Èç¹û±»·Òë³ÉSelectManyÐèÒªÂú×ã2¸öÌõ¼þ¡£1£º²éѯÓï¾äÖÐûÓÐjoinºÍinto£¬2£º±ØÐë³öÏÖEntitySet¡£ÔÚÎÒÃÇ±í¹ØÏµÖÐÓÐÒ»¶ÔÒ»¹ØÏµ£¬Ò»¶Ô¶à¹ØÏµ£¬¶à¶Ô¶à¹ØÏµµÈ£¬ÏÂÃæ·Ö±ð½éÉÜһϡ£
1.Ò»¶Ô¶à¹ØÏµ(1 to Many)£º
var q =
from c in db.Customers
from o in c.Orders
where c.City == "London"
select o;Óï¾äÃèÊö£ºCustomersÓëOrdersÊÇÒ»¶Ô¶à¹ØÏµ¡£¼´OrdersÔÚCustomersÀàÖÐÒÔEntitySetÐÎʽ³öÏÖ¡£ËùÒÔµÚ¶þ¸öfromÊÇ´Óc.Orders¶ø²»ÊÇdb.OrdersÀï½øÐÐɸѡ¡£Õâ¸öÀý×ÓÔÚfrom×Ó¾äÖÐʹÓÃÍâ¼üµ¼º½Ñ¡ÔñÂ׶ؿͻ§µÄËùÓж©µ¥¡£
var q =
from p in db.Products
where p.Supplier.Country == "USA" && p.UnitsInStock == 0
select p;Óï¾äÃèÊö£ºÕâÒ»¾äʹÓÃÁËp.Supplier.CountryÌõ¼þ£¬¼ä½Ó¹ØÁªÁËSupplier±í¡£Õâ¸öÀý×ÓÔÚWhere×Ó¾äÖÐʹÓÃÍâ¼üµ¼º½É¸Ñ¡Æä¹©Ó¦ÉÌÔÚÃÀ¹úÇÒȱ»õµÄ²úÆ·¡£Éú³ÉSQLÓï¾äΪ£º
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID],
[t0].[CategoryID],[t0].[QuantityPerUnit],[t0].[UnitPrice],
[t0].[UnitsInStock], [t0].[UnitsOnOrder],[t0].[ReorderLevel],
[t0].[Discontinued] from [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Suppliers] AS [t1] ON
[t1].[SupplierID] = [t0].[SupplierID]
WHERE ([t1].[Country] = @p0) AND ([t0].[UnitsInStock] = @p1)
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]2.¶à¶Ô¶à¹ØÏµ(Many to Many)£º
var q =
from e in db.Employees
from et in e.EmployeeTerritories
where e.City == "Seattle"
select new
{
e.FirstName,
Ïà¹ØÎĵµ£º
¡¡¡¡±¾ÎÄÌÖÂÛSQL Server 2005µÄй¦ÄÜ£¬ËüÔÊÐíÄ㽫XMLÊý¾Ý·Ö½âµ½¹ØÏµ¸ñʽÖУ¬¶ø²»±ØºÄÓÃÌ«¶àÄÚ´æ¡£
¡¡¡¡±¾ÎÄÌÖÂÛSQL Server 2005µÄй¦ÄÜ£¬ËüÔÊÐíÄ㽫XMLÊý¾Ý·Ö½âµ½¹ØÏµ¸ñʽÖУ¬¶ø²»±ØºÄÓÃÌ«¶àÄÚ´æ¡£
¡¡¡¡ÔÚ¾ÙÀý˵Ã÷ÈçºÎ·Ö½âÉÏһƪÎÄÕÂÖеÄÊý¾Ýʱ£¬ÎÒÃÇÊ×ÏÈÁ˽âÒ»ÏÂXQueryºÍËüÔÚSQL Server 2005ÖÐΪ¿ª·¢ÕßÌṩµÄ¹¦ÄÜ¡£
¡ ......
SQL ServerÖÐδ¹«²¼µÄÀ©Õ¹´æ´¢¹ý³Ì
×÷Õß: echsys, ¡¡³ö´¦:blog,¡¡ÔðÈαà¼: ÀîÊéÇÙ,¡¡
2008-09-09 07:00
¡¡¡¡ÔÚSQL ServerÖаüº¬Á˼¸¸ö¿ÉÒÔ·ÃÎÊϵͳע²á±íµÄÀ©Õ¹´æ´¢¹ý³Ì¡£ËüÃÇÌṩÁËÔÚµ±Ç°µÄSQL Server°æ±¾ÖзÃÎÊϵͳע²á±íµÄÄÜÁ¦£¬¶øÇҺܶàÈËÀûÓÃSQL ServerÀ´½øÐй¥»÷ϵͳ¡£±¾ÎĽéÉÜÁËÈçºÎÔÚSQL ServerÖнûÓÃËûÃÇ¡£
......
ÏÂÎçÏòÊý¾Ý¿âSQL SERVERÖÐÓöµ½Ìí¼ÓͼƬÀàÐÍÊý¾ÝµÄÎÊÌ⣬±¾ÒÔΪÔÚ½çÃæ¹ÜÀíÆ÷ÖпÉÒÔÖ±½Ó£¨Í¨¹ýÖ¸¶¨Â·¾¶°ÑͼƬµÄÊý¾Ý±£´æÆðÀ´£©Ìí¼ÓͼƬȴ²»È»¡£ÍøÉÏËÑÁËÏÂÕÒµ½ÁËÏà¹ØµÄ½ÏרҵµÄ×ÊÁÏ£¬ÕûºÏ½â¶Áһϣº Á½ÖÖ·½·¨£º µÚÒ»¾ÍÊÇÔÚÊý¾Ý¿âÖд洢ͼƬµÄ·¾¶£¬È»ºóÔÚ³ÌÐòÖиù¾Ý¶ÁÈ¡µÄ·¾¶¶ÁȡͼƬ£»ÕâÖ ......
--ÈÕÆÚת»»²ÎÊý,ÖµµÃÊÕ²Ø
select CONVERT(varchar, getdate(), 120)
2004-09-12 11:06:08
select convert(varchar(10),getdate() ,120)
----------
2009-04-09
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , get ......
SQL´æ´¢¹ý³ÌÈëÃÅ
Ò»¡¢SQL´æ´¢¹ý³ÌµÄ¸ÅÄÓŵ㼰Óï·¨
¡¡¡¡ÕûÀíÔÚѧϰ³ÌÐò¹ý³Ì֮ǰ£¬ÏÈÁ˽âÏÂʲôÊÇ´æ´¢¹ý³Ì?ΪʲôҪÓô洢¹ý³Ì£¬ËûÓÐÄÇЩÓŵã
¡¡¡¡¶¨Ò壺½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute ......