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,
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CR ......
¡¡¡¡¹ØÏµÐÍÊý¾Ýͨ³£ÒԹ淶»¯ÐÎʽ±£´æ£¬¾ÍÊÇ˵ÄãÓ¦¸Ã¾¡¿ÉÄÜÉÙµØÖظ´Êý¾Ý;ͨ³£Çé¿öÏ£¬±íÓë±íÖ®¼ä½öͨ¹ý¸÷ÖÖ¼üֵʵÏÖ¹ØÁª¡£
¡¡¡¡¹ØÏµÐÍÊý¾Ýͨ³£ÒԹ淶»¯ÐÎʽ±£´æ£¬¾ÍÊÇ˵ÄãÓ¦¸Ã¾¡¿ÉÄÜÉÙµØÖظ´Êý¾Ý;ͨ³£Çé¿öÏ£¬±íÓë±íÖ®¼ä½öͨ¹ý¸÷ÖÖ¼üֵʵÏÖ¹ØÁª¡£½øÒ»²½µØ½²£¬¹æ·¶»¯µÄº¬Òå¾ÍÊÇ£ºÄã²»ÄÜÔÚÊý¾Ý¿âÖб£´æ¼ÆËãºóµÄÖµ£¬¶øÄãÖ»ÄÜÔÚ ......
¡¡
¡¡¡¡1. ʹÓÃ%TYPE
¡¡¡¡ÔÚÐí¶àÇé¿öÏ£¬PL/SQL±äÁ¿¿ÉÒÔÓÃÀ´´æ´¢ÔÚÊý¾Ý¿â±íÖеÄÊý¾Ý¡£ÔÚÕâÖÖÇé¿öÏ£¬±äÁ¿Ó¦¸ÃÓµÓÐÓë±íÁÐÏàͬµÄÀàÐÍ¡£ÀýÈ磬students±íµÄfirst_nameÁеÄÀàÐÍΪVARCHAR2(20),ÎÒÃÇ¿ÉÒÔ°´ÕÕÏÂÊö·½Ê½ÉùÃ÷Ò»¸ö±äÁ¿£º
¡¡¡¡DECLARE
¡¡¡¡ v_FirstName VARCHAR2(20);
¡¡
¡¡µ«ÊÇÈç¹ûfirst_nameÁе͍Òå¸Ä±äÁ ......
select * from orders where month(orderdate)= (select month(orderdate) from orders where orderid=10248)
1.Ò»¸öÔµÚÒ»ÌìµÄ
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
2.±¾ÖܵÄÐÇÆÚÒ»
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
3.Ò»ÄêµÄµÚÒ»Ìì
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
......