SQL SERVER 2005 »ù±¾²éѯ£¨Á¬½Ó²éѯ£©
use AdventureWorks
GO
SELECT c.LastName from Person.Contact c;
SELECT * from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID; n
SELECT ProductID,Name,ProductNumber,ReorderPoint
from Production.Product
where ProductID in( select ProductID from Production.Product where ProductID in (1,2,3) )
if EXISTS( select ProductID from Production.Product where ProductID in (1,2,3) )
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
SELECT e.EmployeeID,ce.FirstName,ce.LastName
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
INNER JOIN Person.Contact ce
ON e.ContactID = ce.ContactID
INNER JOIN Person.Contact cm
ON m.ContactID = cm.ContactID
WHERE cm.FirstName = 'Jo' and cm.LastName = 'Brown';
Select SalesOrderID,SUM(OrderQty) as sum
from Sales.SalesOrderDetail
where SalesOrderID between 43684 and 43686
group by SalesOrderID
having SUM(OrderQty) > 5
create table UserInfor
(
UserID int not null,
FirstName nvarchar(50),
LastName nvarchar(50)
)
GO
ALTER TABLE UserInfor
add primary key (UserID)
INSERT INTO UserInfor(UserID,FirstName,LastName)
SELECT e.EmployeeID,ce.FirstName,ce.LastName
from HumanResources.Employee e
INNER JOIN HumanResources.Employee m
ON e.ManagerID = m.EmployeeID
INNER JOIN Person.Contact ce
ON e.ContactID = ce.ContactID
INNER JOIN Person.Contact cm
ON m.ContactID = cm.ContactID
WHERE cm.FirstName = 'Jo' and cm.LastName = 'Brown';
declare @MyTable table
(
SalesOrderID int,
CustomerID int
)
INSERT INTO @MyTable(SalesOrderID,CustomerID)
select SalesOrderID,CustomerID
from AdventureWorks.Sales.SalesOrderHeader
where SalesOrderId between 50222 and 50225
select * from @MyTable
create table Shippers
(
ShipperID int identity not null primary key,
ShipperName varchar(30) not null,
Address varchar(20) not null,
Ïà¹ØÎĵµ£º
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
ÔÚѧϰSQLʱ¿´µ½µÄһƬºÜºÃµÄÎÄÕ£¬ÌØÌù³öÀ´ºÍ´ó¼ÒÒ»Æð·ÖÏí£¡
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQLÓï¾ä¡£
£¨1£©Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
OracleµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦ ......
1. SELECT
ʵÀý105
SELECT ID "±àºÅ",Name ÐÕÃû,
Math_Score 'Êýѧ³É¼¨', //ÔõôÓеÄÓÐAS,ÓеÄûÓÐ
Music_Score AS ÒôÀֳɼ¨,
English_Score AS Ó¢Îijɼ¨
f ......
CURSOR
==================================
l SQL ÓαêCURSORµÄʹÓÃ
ʹÓÃÆðÀ´ºÜ¼òµ¥£¬Ïȶ¨Ò壬Ȼºó¸³¸öÖµ£¬´ò¿ª£¬Í¨¹ýWhile Loop Ò»¸öÒ»¸ö¶ÁÏÂÈ¥£¬×îºó¹Ø±Õ£¬ÊÍ·ÅÄÚ´æ¡£»ù±¾Ì×·ÈçÏ£º
DECLARE MyCursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪµ¥´¿ÏòǰµÄÓαꡣÈç¹ûÏëҪǰºóÌøÀ´ÌøÈ¥µÄ£¬Ð´³ÉScroll Cursor¼ ......
l ÔÚ³ÌÐòÖÐÆ´½ÓSQLÃüÁî
¿ÉÊÇÈçºÎÔÚC#³ÌÐòÖÐÔËÐÐÄØ£¿ÎÒÃǼǵÃÔÚÆÕͨµÄSQL²éѯÖУ¬Ò»°ãÐèÒª°Ñ²éѯÓï¾ä¸³Öµ¸øSalCommand.CommandTextÊôÐÔ£¬ÕâÀïÒ²¾ÍÏñÆÕͨµÄSQL²éѯÓï¾äÒ»Ñù£¬½«ÕâЩÓï¾ä¸³¸øSqlCommand.CommandTextÊôÐÔ¼´¿É¡£Òª×¢ÒâµÄÒ»µãÊÇ£¬ÆäÖеÄ"GO"Óï¾ä±êÖ¾×ÅSQLÅú´¦ÀíµÄ½áÊø£¬±àдSQL½Å±¾ÊÇÐèÒªµÄ£¬µ«ÊÇÔÚÕâÀïÊDz ......