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

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

Select * FROM ItemMasters
--Distinct -gt; 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

6、Where子句

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

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

Select * from ItemMasters
Select * from OrderDetails
--Where -gt; 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%'

--gt; here we display the ItemMasters where the price will be greater then or equal to 40.
--gt; 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 gt;=40
--gt; here we display the OrderDetails where the Qty will be greater 3

Select * FROM OrderDetails WHERE qtygt;3

Where – In 子句

-- In clause -gt; 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

Where – Between子句

-- between -gt; Now if we want to display the data between to date range then we use betweeen keyword
select * FROM ItemMasters

select * FROM ItemMasters
 WHERE
 In_Date BETWEEN '2014-09-22 15:59:02.853' AND '2014-09-22 15:59:02.853'

select * FROM ItemMasters
 WHERE
 ITEM_NAME Like 'C%' 
 AND
 In_Date BETWEEN '2014-09-22 15:59:02.853' AND '2014-09-22 15:59:02.853'

查询某个条件区间的数据，我们常常使用between子句。

7、Group By 子句

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

--Group By -gt; 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 qtygt;=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 qtygt;=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) gt;4

8、子查询

子查询一般出现在where内连接查询和嵌套查询中，select、update和delete语句中均可以使用。

--Sub Query -- Here we used the Sub query in where clause to get all the Item_Code where the pricegt;40 now this sub 
--query reslut we used in our main query to filter all the records which Item_code from Subquery result
SELECT * FROM ItemMasters 
  WHERE Item_Code IN 
 (SELECT Item_Code FROM ItemMasters WHERE price gt; 40)

-- Sub Query with Insert Statement
INSERT INTO ItemMasters      ([Item_Code] ,[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]
      ,[IN_USR_ID],[UP_DATE] ,[UP_USR_ID])
  Select 'Item006'
      ,Item_Name,Price+4,TAX1,Discount,Description
      ,GetDate(),'SHANU',GetDate(),'SHANU'
      from ItemMasters
      where Item_code='Item002'   

--After insert we can see the result as 
    Select * from ItemMasters

9、连接查询

到目前为止我们接触了不少单表的查询语句，现在我们来使用连接查询获取多个表的数据。

简单的join语句：

（编辑：武汉站长网）

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