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,
Ïà¹ØÎĵµ£º
ÔÚѧϰ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.ÈçºÎ´´½¨Êý¾Ý¿â
CREATE DATABASE student
2.ÈçºÎɾ³ýÊý¾Ý¿â
DROP DATABASE student
3.ÈçºÎ±¸·ÝÊý¾Ý¿âµ½´ÅÅÌÎļþ
BACKUP DATABASE student to disk=´c:S4.bak´
4.ÈçºÎ´Ó´ÅÅÌÎļþ»¹ÔÊý¾Ý¿â
RESTORE DATABASE studnet from DISK = ´c:S4.bak´
5.ÔõÑù´´½¨±í£¿
CREATE TABLE Students (
&n ......
--²âÊÔÊý¾Ý
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
......