必威-必威-欢迎您

必威,必威官网企业自成立以来,以策略先行,经营致胜,管理为本的商,业推广理念,一步一个脚印发展成为同类企业中经营范围最广,在行业内颇具影响力的企业。

在SQL2000数据库中可以用下面语句创建数据及表,

2019-09-20 17:23 来源:未知

1:往现有的表中增加一个字段

图片 1图片 2代码

触发器语法格式(实例):

本文将分享15个初学者必看的基础SQL查询语句,都很基础,但是你不一定都会,所以好好看看吧。

SQL语句的转换?
在SQL2000数据库中可以用下面语句创建数据及表,我想在Mysql数据库也用此语句创建,但会出现语法错误。请大家帮一下忙,把改成在MYSQL数据库也能运行的语句,但不能改变其他的。只修改语法错误。。。

IF NOT EXISTS ( SELECT 1

  1 create table Employee
  2 (
  3 Id INT IDENTITY(1,1) PRIMARY KEY, 
  4 [Name] varchar(30) null, 
  5 JobTitle varchar(30) null, 
  6 Manager int null
  7 )
  8 
  9 insert Employee
 10 select 'incf',     'IT Director',null union all
 11 select 'inc3',     'Finance Director',null union all
 12 select 'geovindu',     'assces',1 union all
 13 select 'du',     'assces',1 union all
 14 select 'fa','account',2 union all
 15 select 'd','account',2  
 16 
 17 WITH OrganisationChart (Id, [Name], JobTitle, Manager) AS
 18 (
 19       SELECT
 20             Id, [Name], JobTitle, Manager
 21       FROM dbo.Employee
 22       WHERE
 23             Manager IS NULL
 24       UNION ALL
 25       SELECT emp.Id, emp.[Name], emp.JobTitle, emp.Manager
 26       FROM dbo.Employee emp
 27       INNER JOIN OrganisationChart ON
 28             emp.Manager = OrganisationChart.Id
 29 )
 30 SELECT * FROM OrganisationChart
 31 
 32 
 33 WITH OrganisationChart (Id,  [Name], JobTitle, [Level], Manager) AS
 34 (
 35       SELECT
 36             Id, [Name], JobTitle, 0, Manager
 37       FROM  dbo.Employee
 38       WHERE
 39            Manager IS NULL
 40       UNION ALL
 41       SELECT emp.Id, emp.[Name], emp.JobTitle, [Level] + 1, emp.Manager
 42       FROM dbo.Employee emp
 43       INNER JOIN OrganisationChart ON
 44             emp.Manager = OrganisationChart.Id
 45 )
 46 SELECT * FROM OrganisationChart
 47 ORDER BY [Level]
 48 
 49 
 50 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager, [Root]) AS
 51 (
 52       SELECT  Id, [Name], JobTitle, 0, Manager, Id
 53       FROM  dbo.Employee
 54       WHERE  Manager IS NULL
 55       UNION ALL
 56       SELECT  emp.Id,emp.[Name],emp.JobTitle,[Level] + 1,emp.Manager,[Root]
 57       FROM dbo.Employee emp
 58       INNER JOIN OrganisationChart ON
 59             emp.Manager = OrganisationChart.Id
 60 )
 61 SELECT * FROM OrganisationChart
 62 --WHERE [Name] = 'incf'
 63 WHERE [Root] = 1
 64 
 65  
 66 
 67 --示例数据库
 68 
 69 /*
 70 递归查询对于同一个表父子关系的计算提供了很大的方便,这个示例使用了SQL server 2005中的递归查询,使用的表是CarParts,这个表存储了一辆汽车的所有零件以及结构,part为零件单位,subpart为子零件,Qty为数量。
 71 
 72 具体示例如下:
 73 
 74 */ 
 75 CREATE TABLE CarParts
 76 
 77 (
 78 
 79 CarID INT NOT NULL,
 80 
 81 Part VARCHAR(15),
 82 
 83 SubPart VARCHAR(15),
 84 
 85 Qty INT
 86 
 87 )
 88 
 89 GO
 90 
 91 INSERT CarParts VALUES (1, 'Body', 'Door', 4)
 92 
 93 INSERT CarParts VALUES (1, 'Body', 'Trunk Lid', 1)
 94 
 95 INSERT CarParts VALUES (1, 'Body', 'Car Hood', 1)
 96 
 97 INSERT CarParts VALUES (1, 'Door', 'Handle', 1)
 98 
 99 INSERT CarParts VALUES (1, 'Door', 'Lock', 1)
100 
101 INSERT CarParts VALUES (1, 'Door', 'Window', 1)
102 
103 INSERT CarParts VALUES (1, 'Body', 'Rivets', 1000)
104 
105 INSERT CarParts VALUES (1, 'Door', 'Rivets', 100)
106 
107 INSERT CarParts VALUES (1, 'Door', 'Mirror', 1)
108 
109 INSERT CarParts VALUES (1, 'Mirror', 'small_Mirror', 4)
110 
111 GO
112 
113 SELECT * FROM CarParts
114 
115 GO
116 
117 /*
118 
119 一辆汽车需要各个零件的数目
120 
121 1个Body 需要4个Door
122 
123 1个Door 需要1个Mirror
124 
125 那么
126 
127 1个body需要4个Mirror
128 
129 结构很简单吧
130 
131 */
132 
133 WITH CarPartsCTE(SubPart, Qty)
134 
135 AS
136 
137 (
138 
139 -- 固定成员 (AM):
140 
141 -- SELECT查询无需参考CarPartsCTE
142 
143 -- 递归从此处开始
144 
145 SELECT SubPart, Qty
146 
147 FROM CarParts
148 
149 WHERE Part = 'Body'
150 
151 UNION ALL
152 
153 -- 递归成员 (RM):
154 
155 -- SELECT查询参考CarPartsCTE
156 
157 -- 使用现有数据往下一层展开
158 
159 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
160 
161 FROM CarPartsCTE
162 
163 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
164 
165 WHERE CarParts.CarID = 1
166 
167 )
168 
169 SELECT SubPart,Qty AS TotalNUM
170 
171 FROM CarPartsCTE
172 
173 /*
174 
175 注意看最下层的small_Mirror 位于 表最后的位置,
176 
177 由此可以看出改递归不是开始就进行递归查询而是在1层完全展开后在根据该层展开下一层不是深度优先的递归
178 
179 */
180 
181 drop table CarParts 
182 
183  
184 
185 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
186 
187 (
188 
189 SELECT groupid, member, 0,type AS EmployeeLevel
190 
191 FROM groupinfo
192 
193 WHERE groupid = 'finance_company'
194 
195 UNION ALL
196 
197 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
198 
199 FROM groupinfo e
200 
201 INNER JOIN DirectReports d
202 
203 ON e.groupid = d.member
204 
205 )
206 
207 SELECT b.nickname,groupid, member, EmployeeLevel,type
208 
209 FROM DirectReports,userbasicinfo b
210 
211 where DirectReports.member=b.id
212 
213 and type = 1 
214 
215 
216 USE AdventureWorks;
217 GO
218 WITH DirReps(ManagerID, DirectReports) AS 
219 (
220     SELECT ManagerID, COUNT(*) 
221     FROM HumanResources.Employee AS e
222     WHERE ManagerID IS NOT NULL
223     GROUP BY ManagerID
224 )
225 SELECT ManagerID, DirectReports 
226 FROM DirReps 
227 ORDER BY ManagerID;
228 GO
229 
230 
231 WITH DirReps (Manager, DirectReports) AS 
232 (
233     SELECT ManagerID, COUNT(*) AS DirectReports
234     FROM HumanResources.Employee
235     GROUP BY ManagerID
236 ) 
237 SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
238 FROM DirReps 
239 WHERE DirectReports>= 2 ;
240 GO
241 
242  
243 
244 
245 USE AdventureWorks;
246 GO
247 WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
248 AS
249 (
250     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
251     FROM Sales.SalesOrderHeader
252     GROUP BY SalesPersonID
253 )
254 SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
255     E.ManagerID, OM.NumberOfOrders, OM.MaxDate
256 FROM HumanResources.Employee AS E
257     JOIN Sales_CTE AS OS
258     ON E.EmployeeID = OS.SalesPersonID
259     LEFT OUTER JOIN Sales_CTE AS OM
260     ON E.ManagerID = OM.SalesPersonID
261 ORDER BY E.EmployeeID;
262 GO
263 
264 ---管理員下的下屬
265 USE AdventureWorks;
266 GO
267 WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
268 (
269     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
270     FROM HumanResources.Employee
271     WHERE ManagerID IS NULL
272     UNION ALL
273     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
274     FROM HumanResources.Employee e
275         INNER JOIN DirectReports d
276         ON e.ManagerID = d.EmployeeID 
277 )
278 SELECT ManagerID, EmployeeID, EmployeeLevel 
279 FROM DirectReports 
280 WHERE EmployeeLevel <= 2 ;
281 GO
282 
283 USE AdventureWorks;
284 GO
285 WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
286 AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
287         e.Title,
288         e.EmployeeID,
289         1,
290         CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
291     FROM HumanResources.Employee AS e
292     JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
293     WHERE e.ManagerID IS NULL
294     UNION ALL
295     SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
296         c.FirstName + ' ' + c.LastName),
297         e.Title,
298         e.EmployeeID,
299         EmployeeLevel + 1,
300         CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
301                  LastName)
302     FROM HumanResources.Employee as e
303     JOIN Person.Contact AS c ON e.ContactID = c.ContactID
304     JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
305     )
306 SELECT EmployeeID, Name, Title, EmployeeLevel
307 FROM DirectReports 
308 ORDER BY Sort;
309 GO
310 
311 USE AdventureWorks;
312 GO
313 --Creates an infinite loop
314 WITH cte (EmployeeID, ManagerID, Title) as
315 (
316     SELECT EmployeeID, ManagerID, Title
317     FROM HumanResources.Employee
318     WHERE ManagerID IS NOT NULL
319   UNION ALL
320     SELECT cte.EmployeeID, cte.ManagerID, cte.Title
321     FROM cte 
322     JOIN  HumanResources.Employee AS e 
323         ON cte.ManagerID = e.EmployeeID
324 )
325 --Uses MAXRECURSION to limit the recursive levels to 2
326 SELECT EmployeeID, ManagerID, Title
327 FROM cte
328 OPTION (MAXRECURSION 2);
329 GO
330 
331  
332 
333 USE AdventureWorks;
334 GO
335 WITH cte (EmployeeID, ManagerID, Title)
336 AS
337 (
338     SELECT EmployeeID, ManagerID, Title
339     FROM HumanResources.Employee
340     WHERE ManagerID IS NOT NULL
341   UNION ALL
342     SELECT  e.EmployeeID, e.ManagerID, e.Title
343     FROM HumanResources.Employee AS e
344     JOIN cte ON e.ManagerID = cte.EmployeeID
345 )
346 SELECT EmployeeID, ManagerID, Title
347 FROM cte;
348 GO
349 
350 
351 USE AdventureWorks;
352 GO
353 WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
354 (
355     SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
356         b.EndDate, 0 AS ComponentLevel
357     FROM Production.BillOfMaterials AS b
358     WHERE b.ProductAssemblyID = 800
359           AND b.EndDate IS NULL
360     UNION ALL
361     SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
362         bom.EndDate, ComponentLevel + 1
363     FROM Production.BillOfMaterials AS bom 
364         INNER JOIN Parts AS p
365         ON bom.ProductAssemblyID = p.ComponentID
366         AND bom.EndDate IS NULL
367 )
368 SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
369         ComponentLevel 
370 FROM Parts AS p
371     INNER JOIN Production.Product AS pr
372     ON p.ComponentID = pr.ProductID
373 ORDER BY ComponentLevel, AssemblyID, ComponentID;
374 GO
375 

ALTER TRIGGER tri_edituser
ON dbo.sys_User
FOR INSERT,UPDATE,DELETE
AS
BEGIN
---判断是新增?修改?删除?
DECLARE
@IsInsert BIT,
@IsUpdate BIT,
@IsDelete BIT
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
SET @IsInsert = 1
ELSE
SET @IsInsert = 0
IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsUpdate = 1
ELSE
SET @IsUpdate = 0
IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsDelete = 1
ELSE
SET @IsDelete = 0

1、创建表和数据插入SQL

--1
CREATE DATABASE [FFMS]

*  *FROM sys.sysobjects so WITH
  INNER JOIN sys.syscolumns sc WITH ON so.id = sc.id
  WHERE so.name = 'EgPurchase_OrderPlan'
  AND sc.name = 'IsNewSystem' )
  BEGIN
    ALTER TABLE dbo.EgPurchase_OrderPlan ADD IsNewSystem INT NOT NULL DEFAULT 0
    EXECUTE sp_addextendedproperty N'MS_Description', '是否推送新系统', N'user',
    N'dbo', N'table', N'EgPurchase_OrderPlan', N'column', N'IsNewSystem'
  END
GO

 

---新增
IF(@IsInsert=1)
BEGIN
--获取工号
DECLARE @ID VARCHAR(20)
IF EXISTS(Select * From tab_User Where RecordDate > DATEADD(day,DATEDIFF(day,0,getdate()),0))
BEGIN
SELECT @ID=CAST(MAX(工号) as int) + 1 From tab_User Where RecordDate > DATEADD(day,DATEDIFF(day,0,getdate()),0)
END
ELSE
BEGIN
SELECT @ID=CONVERT(varchar(8),getdate(),112) + '01'
END

我们在开始创建数据表和向表中插入演示数据之前,我想给大家解释一下实时数据表的设计理念,这样也许能帮助大家能更好的理解SQL查询。

--2
USE [FFMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Account]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Account](
[id] [int] IDENTITY NOT NULL,
[userid] [int] NULL,
[accountitemid] [int] NULL,
[amount] [decimal] NULL,
[accountdate] [datetime] NULL,
[remarks] [varchar] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[User]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[User](
[id] [int] IDENTITY NOT NULL,
[loginname] [varchar] NULL,
[password] [varchar] NULL,
[usernumber] [varchar] NULL,
[name] [varchar] NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[AccountItem]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[AccountItem](
[id] [int] IDENTITY NOT NULL,
[pid] [int] NULL,
[accountitemnumber] [varchar] NULL,
[name] [varchar] NULL,
[type] [int] NULL,
CONSTRAINT [PK_AccountItem] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ViewAccount]') AND OBJECTPROPERTY(id, N'IsView') = 1)
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[ViewAccount]
AS
SELECT dbo.Account.id, dbo.Account.amount, dbo.Account.accountdate, dbo.Account.remarks, dbo.Account.userid, dbo.[User].loginname,
dbo.[User].usernumber, dbo.[User].name AS username, dbo.Account.accountitemid, dbo.AccountItem.accountitemnumber,
dbo.AccountItem.name AS accountitemname, dbo.AccountItem.type AS accountitemtype, dbo.AccountItem.pid AS asaccountitempid,
ParentAccountItem.accountitemnumber AS accountitempnumber, ParentAccountItem.name AS accountitempname
FROM dbo.Account INNER JOIN
dbo.[User] ON dbo.Account.userid = dbo.[User].id INNER JOIN
dbo.AccountItem ON dbo.Account.accountitemid = dbo.AccountItem.id INNER JOIN
dbo.AccountItem AS ParentAccountItem ON dbo.AccountItem.pid = ParentAccountItem.id
'
GO

2:更新一个字段

INSERT INTO ERPDB.dbo.tab_user
(
-- ID -- this column value is auto-generated
工号,
用户名,
密码,
角色,
二级权限,
employee_mail,
mobile,
dept,
isVisible,
lookContractRight,
RecordDate
)
SELECT @ID,UserName,'888888','',
'销售查询(个人),费用查询(个人),应收款项(个人),询价查询(个人),进出货查询(个人),利息查询(个人),外借查询(个人),',
Email,NULL,NULL,1,@ID,GETDATE() FROM INSERTED
END
---修改
IF(@IsUpdate = 1)
BEGIN
SELECT @ID = 工号 FROM ERPDB.dbo.tab_user AS tu JOIN INSERTED i ON tu.用户名=i.UserName
IF (@ID IS NOT NULL AND @ID <> '')
BEGIN
UPDATE ERPDB.dbo.tab_user
SET
-- ID = ? -- this column value is auto-generated
用户名 = i.UserName,
employee_mail = i.Email,
isVisible = i.[Status],
RecordDate = GETDATE()
FROM ERPDB.dbo.tab_user tu,INSERTED i,DELETED d
WHERE tu.用户名=i.UserName AND tu.工号=@ID
END
END
IF(@IsDelete = 1)
END

在数据库设计中,有一条非常重要的规则就是要正确建立主键和外键的关系。

INSERT INTO [User] (loginname, password, usernumber, name) VALUES('admin', 'admin', '001', '管理员')

UPDATE dbo.EgSys_ExportSet
SET ColumnNames='订单编号|1,店铺名称|1,仓库名称|1,平台|1,商品|1,商品名称|1,商品编号|1,规格编码|1,颜色名称|1,规格名称|1,商品数量|1,吊牌价|1,单价|1,成本价|1,成交价|1,应付金额|1,实付金额|1,优惠|1,运费|1,重量|1,交易单号|1,实付物流费用|1,快递单号|1,物流公司|1,买家昵称|1,收件人|1,国家|1,省|1,市|1,区|1,地址|1,完整地址|1,邮编|1,买家留言|1,卖家留言|1,买家手机|1,买家电话|1,买家邮箱|1,订单状态|1,订单付款时间|1,订单完结时间|1,发货日期|1,换货状态|1,换货数量|1,付款状态|1,删除状态|1,副单状态|1,退款状态|1,退款金额|1,主订单号|1,城市代码|0,订单备注|0,分销商名|0,业务员|0,国家中文|0,国家简写|0,申报费用|0,商品类别|0,商品类别|0,商品备注|0,客户条形码|1,Sku状态|0,商品条形码|1'
WHERE ModuleMark='AllOrder'

--ALTER TABLE sys_User DISABLE TRIGGER tri_edituser

现在我们来创建几个餐厅订单管理的数据表,一共用到3张数据表,Item Master表、Order Master表和Order Detail表。

3:查询EDL转运单号

创建表:

SELECT d.*
FROM dbo.EgSys_Delivery d WITH
INNER JOIN dbo.Egsys_PlatformTrade t WITH ON t.tid = d.tid
WHERE d.deliveryTime < CONVERT(DATE, GETDATE
AND d.deliveryTime >= CONVERT(DATE, DATEADD(DAY, -70000, GETDATE
AND t.orderState >= 4
AND t.order_deleteState != 1
AND d.logisticsCompany LIKE 'EdlExpress%'
AND d.logisticsNo LIKE 'HMZX%'

创建Item Master表:

4:插入一条记录的严谨写法

CREATE TABLE [dbo].[ItemMasters](
[Item_Code] varchar NOT NULL,
[Item_Name] varchar NOT NULL,
[Price] Int NOT NULL,
[TAX1] Int NOT NULL,
[Discount] Int NOT NULL,
[Description] varchar NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar NOT NULL,
CONSTRAINT [PK_ItemMasters] PRIMARY KEY CLUSTERED
(
[Item_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

/*

向Item Master表插入数据:

*

* Designer:
* Description: nsy_1901161114
* Created: 2019/01/16 11:12:47

INSERT INTO [ItemMasters] ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
,[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Item001','Coke',55,1,0,'Coke which need to be cold',GETDATE(),'SHANU'
,GETDATE(),'SHANU')

*

*/

SELECT [disPlatformID] ,
[disPlatformName] ,
[disPlatformValue] ,
[disPlatformNote] ,
[disPlatformOrder] ,
[DefaultSpaceId] ,
[IsDisable] ,
[NotifyUrl]
INTO #EgSys_DistributionPlatform
FROM [dbo].[EgSys_DistributionPlatform]
-- where 1=0 只读取表结构,节约内存
WHERE 1 = 0
UNION ALL SELECT '42','Oberlo','Oberlo','Oberlo','42','0','0',''
INSERT INTO [dbo].[EgSys_DistributionPlatform]
( [disPlatformID] ,
[disPlatformName] ,
[disPlatformValue] ,
[disPlatformNote] ,
[disPlatformOrder] ,
[DefaultSpaceId] ,
[IsDisable] ,
[NotifyUrl]
)
SELECT d.disPlatformID, -- [int] NOT NULL,
d.disPlatformName , -- varchar NULL
d.disPlatformValue , -- varchar NULL
d.disPlatformNote , -- varchar null
d.disPlatformOrder , -- [int] NULL
d.DefaultSpaceId , -- [int] NULL
d.IsDisable , -- [int] NULL,
d.NotifyUrl -- [varchar] NULL
FROM #EgSys_DistributionPlatform d WITH
LEFT JOIN dbo.EgSys_DistributionPlatform dp WITH ON dp.disPlatformID = d.disPlatformID
WHERE dp.disPlatformID IS NULL
DROP TABLE #EgSys_DistributionPlatform

INSERT INTO [ItemMasters] ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
,[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Item002','Coffee',40,0,2,'Coffe Might be Hot or Cold user choice',GETDATE(),'SHANU'
,GETDATE(),'SHANU')

INSERT INTO [ItemMasters] ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
,[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Item003','Chiken Burger',125,2,5,'Spicy',GETDATE(),'SHANU'
,GETDATE(),'SHANU')

INSERT INTO [ItemMasters] ([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
,[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Item004','Potato Fry',15,0,0,'No Comments',GETDATE(),'SHANU'
,GETDATE(),'SHANU')
创建Order Master表:

CREATE TABLE [dbo].[OrderMasters](
[Order_No] varchar NOT NULL,
[Table_ID] varchar NOT NULL,
[Description] varchar NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar NOT NULL,
CONSTRAINT [PK_OrderMasters] PRIMARY KEY CLUSTERED
(
[Order_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
向Order Master表插入数据:

INSERT INTO [OrderMasters]
([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Ord_001','T1','',GETDATE(),'SHANU' ,GETDATE(),'SHANU')

INSERT INTO [OrderMasters]
([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Ord_002','T2','',GETDATE(),'Mak' ,GETDATE(),'MAK')

INSERT INTO [OrderMasters]
([Order_No],[Table_ID] ,[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('Ord_003','T3','',GETDATE(),'RAJ' ,GETDATE(),'RAJ')
创建Order Detail表:

CREATE TABLE [dbo].[OrderDetails](
[Order_Detail_No] varchar NOT NULL,
[Order_No] varchar CONSTRAINT fk_OrderMasters FOREIGN KEY REFERENCES OrderMasters(Order_No),
[Item_Code] varchar CONSTRAINT fk_ItemMasters FOREIGN KEY REFERENCES ItemMasters(Item_Code),
[Notes] varchar NOT NULL,
[QTY] INT NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] varchar NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] varchar NOT NULL,
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[Order_Detail_No] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--Now let’s insert the 3 items for the above Order No 'Ord_001'.
INSERT INTO [OrderDetails]
([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]
,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('OR_Dt_001','Ord_001','Item001','Need very Cold',3
,GETDATE(),'SHANU' ,GETDATE(),'SHANU')

INSERT INTO [OrderDetails]
([Order_Detail_No],[Order_No],[Item_Code],[Notes],[QTY]
,[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])
VALUES
('OR_Dt_002','Ord_001','Item004','very Hot ',2
,GETDATE(),'SHANU' ,GETDATE(),'SHANU')

TAG标签:
版权声明:本文由必威发布于必威-编程,转载请注明出处:在SQL2000数据库中可以用下面语句创建数据及表,