15个数据库基础SQL查询语句
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
15个数据库基础SQL查询语句
1、创建表和数据插入SQL
我们在开始创建数据表和向表中插入演示数据之前，我想给大家解释一下实时数据表的设计理念，这样也许能帮助大家能更好的理解SQL查询。
在数据库设计中，有一条非常重要的规则就是要正确建立主键和外键的关系。
现在我们来创建几个餐厅订单管理的数据表，一共用到3张数据表，Item Master表、Order Master表和Order Detail表。
创建表：
创建Item Master表：
CREATE TABLE [dbo].[ItemMasters](
[Item_Code] [varchar](20) NOT NULL,
[Item_Name] [varchar](100) NOT NULL,
[Price] Int NOT NULL,
[TAX1] Int NOT NULL,
[Discount] Int NOT NULL,
[Description] [varchar](200) NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] [varchar](20) NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] [varchar](20) 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表插入数据：
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')
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](20) NOT NULL,
[Table_ID] [varchar](20) NOT NULL,
[Description] [varchar](200) NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] [varchar](20) NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] [varchar](20) 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](20) NOT NULL,
[Order_No] [varchar](20) CONSTRAINT fk_OrderMasters
FOREIGN KEY REFERENCES OrderMasters(Order_No),
[Item_Code] [varchar](20) CONSTRAINT fk_ItemMasters FOREIGN KEY REFERENCES ItemMasters(Item_Code),
[Notes] [varchar](200) NOT NULL,
[QTY] INT NOT NULL,
[IN_DATE] [datetime] NOT NULL,
[IN_USR_ID] [varchar](20) NOT NULL,
[UP_DATE] [datetime] NOT NULL,
[UP_USR_ID] [varchar](20) NOT NULL,