20分钟教你从0开始学SQL查询

作为新时代软件公司的一员，代码我们可以不会（毕竟大家不全是做开发的），但是数据库不能不懂。如今数据库查询已经可以称为是仅次于office软件操作的必备技能了，毕竟你总是要做数据的。

小编今天给大家带来了新手福利：select查询基础教程，希望各位刚毕业的小伙伴可以愉快上路，当然也希望能给老司机们提供一点参考，闲话少说，我们开始吧！

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

首先让我们来看一下selcet语句的基本结构

select 要查的字段列表 from 数据来源表 where 条件 group by 分组字段 order by 排序字段 having 带分组函数的条件; 这是基本的语句结构。

下面我们在SQL Server中查询我的姓名(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

合计和标量函数

合计函数和标量函数都是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（分别是一些内置函数类型，具体功效可以参考excel表格）

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

其他Select函数

Top —— 结合select语句，Top函数可以查询头几条和末几条的数据记录。

Order By —— 结合select语句，Order By可以让查询结果按某个字段正序和逆序输出数据记录。

--Top to Select Top first and last records using Select Statement.

Select * FROM ItemMasters

--> First Display top 2 Records（查询头2条记录）

Select TOP 2 Item_Code

,Item_name as Item

,Price

,Description

,In_DATE

FROM ItemMasters

--> to Display the Last to Records we need to use the Order By Clause（为了显示后几条数据我们需要使用order by语句）

-- 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

Distinct —— distinct关键字可以过滤重复的数据记录。

Select * FROM ItemMasters

--Distinct -> To avoid the Duplicate records we use the distinct in select statement

-- for example in this table we can see here we have the duplicate record 'Chiken Burger'

-- but with different Item_Code when i use the below select statement see what happen

Select   Item_name as Item

,Price

,Description

,IN_USR_ID

FROM ItemMasters

-- here we can see the Row No 3 and 5 have the duplicate record to avoid this we use the distinct Keyword in select statement.

select Distinct Item_name as Item

,Price

,Description

,IN_USR_ID

FROM ItemMasters

Where子句

Where子句在SQL Select查询语句中非常重要，为什么要使用where子句？什么时候使用where子句？where子句是利用一些条件来过滤数据结果集。

下面我们从10000条数据记录中查询Order_No为某个值或者某个区间的数据记录，另外还有其他的条件。

Select * from ItemMasters

Select * from OrderDetails

--Where -> To display the data with certain conditions

-- Now below example which will display all the records which has Item_Name='Coke'

select * FROM ItemMasters WHERE ITEM_NAME='COKE'

-- If we want display all the records Iten_Name which Starts with 'C' then we use Like in where clause.

SELECT * FROM ItemMasters WHERE ITEM_NAME Like 'C%'

--> here we display the ItemMasters where the price will be greater then or equal to 40.

--> to use more then one condition we can Use And or Or operator.

--If we want to check the data between to date range then we can use Between Operator in Where Clause.

select Item_name as Item

,Price

,Description

,IN_USR_ID

FROM ItemMasters

WHERE

ITEM_NAME Like 'C%' 

AND 

price >=40

--> here we display the OrderDetails where the Qty will be greater 3

Select * FROM OrderDetails WHERE qty>3

Where – In 子句

-- In clause -> used to display the data which is in the condition

select *

FROM ItemMasters

WHERE

Item_name IN ('Coffee','Chiken Burger')

-- In clause with Order By - Here we display the in descending order.

select *

FROM ItemMasters

WHERE

Item_name IN ('Coffee','Chiken Burger')

ORDER BY Item_Code Desc

Group By 子句

Group By子句可以对查询的结果集按指定字段分组：

--Group By -> To display the data with group result.Here we can see we display all the AQggregate result by Item Name

Select ITEM_NAME,Count(*)  TotalRows,AVG(Price) AVGPrice

,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal 

FROM

ItemMasters

GROUP BY ITEM_NAME

-- Here this group by will combine all the same Order_No result and make the total or each order_NO

Select Order_NO,Sum(QTy) as TotalQTY 

FROM OrderDetails

where qty>=2

GROUP BY Order_NO

-- Here the Total will be created by order_No and Item_Code

Select Order_NO,Item_Code,Sum(QTy) as TotalQTY 

FROM OrderDetails

where qty>=2

GROUP BY Order_NO,Item_Code

Order By Order_NO Desc,Item_Code

Group By & Having 子句

--Group By Clause -- here this will display all the Order_no 

Select Order_NO,Sum(QTy) as TotalQTY 

FROM OrderDetails

GROUP BY Order_NO

-- Having Clause-- This will avoid the the sum(qty) less then 4

Select Order_NO,Sum(QTy) as TotalQTY 

FROM OrderDetails

GROUP BY Order_NO

HAVING Sum(QTy) >4

◆  ◆  ◆  ◆  ◆