程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySql數據庫中Select用法小結

MySql數據庫中Select用法小結

編輯:關於MYSQL數據庫

一、條件篩選

1、數字篩選:sql = "Select * from [sheet1$] Where 銷售單價 > 100"

2、字符條件:sql = "Select * from [sheet1$] Where 物品名稱 ='擋泥板'"

3、日期條件:sql = "Select * from [sheet1$] Where 物品名稱 ='擋泥板'"

4、區間條件:sql = "Select * from [sheet1$] Where 出庫日期 between #2005-1-4# and #2005-1-10#"

5、多個條件:用and,OR連接

'sql = "Select * from [sheet1$] Where 出庫日期 between #2005-1-4# and #2005-1-10# and 銷售單價>100"

6、模糊條件:%表示任意多個字符,_(下劃線)表示單個占位符

'sql = "Select * from [sheet1$] Where 物品名稱 like '%扶手%'"

7、插入變量查詢:

'sr = "擋泥板"
'sql = "Select * from [sheet1$] Where 物品名稱 ='" & sr & "'"

8、在字符串組裡:

'sql = "Select * from [sheet1$] Where 物品名稱 in('車衣','扶手箱')"

9、借用函數優化:

'sql = "Select * from [sheet1$] Where left(物品代碼,3)='028'"

二、指定篩選

1、篩選全部字段:*表示全部字段

'sql = "Select * from ChuKu"

2、篩選指定字段:在from前面列出要顯示的所有字段,如果要跳過的用""""

'sql = "Select """",出庫日期,"""",物品代碼,"""",規格,單位 from ChuKu"

3、篩選不重復的:Distinct 字段名 篩選不重復的記錄

'sql = "Select Distinct """",物品代碼 from ChuKu"

4、按百分比篩選:Top N percent 可以顯示前百分之N的記錄

'sql = "Select top 30 Percent * from ChuKu"

'對篩選的字段用函數進行進一步的處理和格式化

'sql = "Select ID,Format(出庫日期,""yyyy-mm-dd"") from ChuKu"

5、排序:Desc降序,Asc升序

'sql = "Select * from ChuKu Order by 出庫日期 asc,銷售單價 desc"
'sql = "Select Top 10 * from ChuKu Order by 出庫日期 asc,銷售單價 desc" '篩選銷售數量前10()

三、分組顯示

Group by 可以配合函數進行分組求和,分組求最大值等。

'sql = "Select """","""",物品代碼,"""","""","""",sum(出庫數量) from ChuKu group by 物品代碼" ' 跳過的用""""

按條件顯示分組記錄

'sql = "Select """","""",物品代碼,"""","""","""",sum(出庫數量) from ChuKu group by 物品代碼 HAVING sum(出庫數量)>=3"

以上所述是小編給大家介紹的MySql數據庫中Select用法小結,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對網站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved