15个初学者必看的基础SQL查询语句

本文将分享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,
 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 letrsquo;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')

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_003','Ord_001','Item003','Very Spicy',4
   ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')

向Order Detail表插入数据：

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_004','Ord_002','Item002','Need very Hot',2
   ,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_005','Ord_002','Item003','very Hot ',2
   ,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_006','Ord_003','Item003','Very Spicy',4
   ,GETDATE(),'SHANU' ,GETDATE(),'SHANU')

2、简单的Select查询语句

Select查询语句是SQL中最基本也是最重要的DML语句之一。那么什么是DML？DML全称Data Manipulation Language（数据操纵语言命令），它可以使用户能够查询数据库以及操作已有数据库中的数据。

（编辑：大同站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!