匿名通过本文主要向大家介绍了mysql,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 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')
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（数据操纵语言命令），它可以使用户能够查询数据库以及操作已有数据库中的数据。
下面我们在SQL Server中用select语句来查询我的姓名(Name)：
SELECT 'My Name Is SYED SHANU' -- With Column Name using 'AS' SELECT 'My Name Is SYED SHANU' as 'MY NAME' -- With more then the one Column SELECT 'My Name' as 'Column1', 'Is' as 'Column2', 'SYED SHANU