您现在的位置是：网站首页> 编程资料编程资料
15个初学者必看的基础SQL查询语句_MsSql_
2023-05-26
344人已围观
简介 15个初学者必看的基础SQL查询语句_MsSql_
本文将分享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 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' as 'Column3'
在数据表中使用select查询：
-- To Display all the columns from the table we use * operator in select Statement. Select * from ItemMasters -- If we need to select only few fields from a table we can use the Column Name in Select Statement. Select Item_Code ,Item_name as Item ,Price ,Description ,In_DATE FROM ItemMasters
3、合计和标量函数
合计函数和标量函数都是SQL Server的内置函数，我们可以在select查询语句中使用它们，比如Count(), Max(), Sum(), Upper(), lower(), Round()等等。下面我们用SQL代码来解释这些函数的用法：
select * from ItemMasters -- Aggregate -- COUNT() -> returns the Total no of records from table , AVG() returns the Average Value from Colum,MAX() Returns MaX Value from Column -- ,MIN() returns Min Value from Column,SUM() sum of total from Column Select Count(*) TotalRows,AVG(Price) AVGPrice ,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal FROM ItemMasters -- Scalar -- UCASE() -> Convert to Upper Case ,LCASE() -> Convert to Lower Case, -- SUBSTRING() ->Display selected char from column ->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay) --,LEN() -> lenth of column date, -- ROUND() -> Which will round the value SELECT UPPER(Item_NAME) Uppers,LOWER(Item_NAME) Lowers, SUBSTRING(Item_NAME,2,3) MidValue,LEN(Item_NAME) Lenths ,SUBSTRING(Item_NAME,2,LEN(Item_NAME)) MidValuewithLenFunction, ROUND(Price,0) as Rounded FROM ItemMasters
4、日期函数
在我们的项目数据表中基本都会使用到日期列，因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心，它随时可以给你带来巨大的麻烦。在项目中，我们要选择合适的日期函数和日期格式，下面是一些SQL日期函数的例子：
-- GETDATE() -> to Display the Current Date and Time -- Format() -> used to display our date in our requested format Select GETDATE() CurrentDateTime, FORMAT(GETDATE(),'yyyy-MM-dd') AS DateFormats, FORMAT(GETDATE(),'HH-mm-ss')TimeFormats, CONVERT(VARCHAR(10),GETDATE(),10) Converts1, CONVERT(VARCHAR(24),GETDATE(),113), CONVERT(NVARCHAR, getdate(), 106) Converts2 ,-- here we used Convert Function REPLACE(convert(NVARCHAR, getdate(), 106), ' ', '/') Formats-- Here we used replace and --convert functions. --first we convert the date to nvarchar and then we replace the '' with '/' select * from Itemmasters Select ITEM_NAME,IN_DATE CurrentDateTime, FORMAT(IN_DATE,'yyyy-MM-dd') AS DateFormats, FORMAT(IN_DATE,'HH-mm-ss')TimeFormats, CONVERT(VARCHAR(10),IN_DATE,10) Converts1, CONVERT(VARCHAR(24),IN_DATE,113), convert(NVARCHAR, IN_DATE, 106) Converts2 ,-- here we used Convert Function REPLACE(convert(NVARCHAR,IN_DATE, 106), ' ', '/') Formats FROM Itemmasters
DatePart –> 该函数可以获取年、月、日的信息。
DateADD –> 该函数可以对当前的日期进行加减。
DateDiff –> 该函数可以比较2个日期。
--Datepart DATEPART(dateparttype,yourDate) SELECT DATEPART(yyyy,getdate()) AS YEARs , DATEPART(mm,getdate()) AS MONTHS, DATEPART(dd,getdate()) AS Days, DATEPART(week,getdate()) AS weeks, DATEPART(hour,getdate()) AS hours --Days Add to add or subdtract date from a selected date. SELECT GetDate()CurrentDate,DATEADD(day,12,getdate()) AS AddDays , DATEADD(day,-4,getdate()) AS FourDaysBeforeDate -- DATEDIFF() -> to display the Days between 2 dates select DATEDIFF(year,'2003-08-05',getdate()) yearDifferance , DATEDIFF(day,DATEADD(day,-24,getdate()),getdate()) daysDifferent, DATEDIFF(month,getdate(),DATEADD(Month,6,getdate())) MonthDifferance
5、其他Select函数
Top —— 结合select语句，Top函数可以查询头几条和末几条的数据记录。
Order By —— 结合select语句，Order By可以让查询结果按某个字段正序和逆序输出数据记录。
--Top to Select Top first and last records using Select Statement. Select * FROM ItemMasters --> First Displa
相关内容
- SQL数据库优化大总结之百万级数据库优化方案_MsSql_
- SQL Report Builder 报表里面的常见问题分析_MsSql_
- SqlServer应用之sys.dm_os_waiting_tasks 引发的疑问(下)_MsSql_
- SqlServer应用之sys.dm_os_waiting_tasks 引发的疑问(中)_MsSql_
- SqlServer应用之sys.dm_os_waiting_tasks 引发的疑问(上)_MsSql_
- 图文教程mssqlserver数据库导出到另外一个数据库的方法_MsSql_
- 深入学习SQL Server聚合函数算法优化技巧_MsSql_
- SQLServer中Partition By及row_number 函数使用详解_MsSql_
- SQLServer中merge函数用法详解_MsSql_
- SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知_MsSql_
点击排行
本栏推荐
