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

下面我们在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() -gt; 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() -gt; Convert to Upper Case ,LCASE() -gt; Convert to Lower Case,
-- SUBSTRING() -gt;Display selected char from column -gt;SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay)
--,LEN() -gt; lenth of column date,
-- ROUND() -gt; 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() -gt; to Display the Current Date and Time
-- Format() -gt; 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 –gt;nbsp; 该函数可以获取年、月、日的信息。

DateADD –gt;nbsp; 该函数可以对当前的日期进行加减。

DateDiffnbsp; –gt;nbsp; 该函数可以比较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() -gt; 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
--gt; First Display top 2 Records
Select TOP 2 Item_Code
 ,Item_name as Item
 ,Price
 ,Description
 ,In_DATE
FROM ItemMasters
--gt; to Display the Last to Records we need to use the Order By Clause
-- order By to display Records in assending or desending order by the columns
Select TOP 2 Item_Code
 ,Item_name as Item
 ,Price
 ,Description
 ,In_DATE
FROM ItemMasters
ORDER BY Item_Code DESC

（编辑：武汉站长网）

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