小编今天给大家带来了新手福利:select查询基础教程,希望各位刚毕业的小伙伴可以愉快上路,当然也希望能给老司机们提供一点参考,闲话少说,我们开始吧!
Select查询语句是SQL中最基本也是最重要的DML语句之一。那么什么是DML?DML全称Data Manipulation Language(数据操纵语言命令),它可以使用户能够查询数据库以及操作已有数据库中的数据。
select 要查的字段列表 from 数据来源表 where 条件 group by 分组字段 order by 排序字段 having 带分组函数的条件; 这是基本的语句结构。
-- With Column Name using 'AS'
-- With more then the one Column
在数据表中使用select查询:
-- To Display all the columns from the table we use * operator in select Statement(当我们需要查询一个表中的所有字段的时候我们可以用*来表示)
-- 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代码来解释这些函数的用法:
-- 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
Top —— 结合select语句,Top函数可以查询头几条和末几条的数据记录。
Order By —— 结合select语句,Order By可以让查询结果按某个字段正序和逆序输出数据记录。
--Top to Select Top first and last records using Select Statement.
--> 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关键字可以过滤重复的数据记录。
--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子句在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'
-- If we want display all the records Iten_Name which Starts with 'C' then we use Like in where clause.
--> 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 -> 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
◆ ◆ ◆ ◆ ◆
联系客服