SQL Select 語法

Select expression_list

From table_name [,....]

Where expression_group

[Into results table]

[Group By column_list]

[Order By column_list]

　

SQL Select一般程序

column語段

• 直接輸入需要顯示之欄位名稱

Select country From World

• 所有的欄為位均選取,則輸入"*"

Select * From World

• 計算衍生欄位 
  1.刪除Select對話框中Select colume中之星號"*" 
  2.輸入欄位運算式,欄位運算式中應含一個或多個欄位名稱,利用算數運算式或函數等,將欄位計算成所需資訊
  3.給予計算之衍生欄位一個新的名稱,以方便識別,直接以雙引號""內置放新欄位名稱即可

4.如果衍生欄位不只一欄,以","分開即可

範例1: 衍生一新欄位名稱,以百萬人口數欄位
Select  country, Population / 1000000 "Millions" from world

範例2: 衍生一新欄位名稱,將First Name及Last Name欄位合成"Full_Name" (注意:欄位名稱不能有空格)
Select  Fname+" "+Lname "Full_Name" from us_custg

範例3: 衍生一新欄位名稱記載區域之面積值
Select Area( Obj, "sq km") "Net_Area"  from world

Obj 參數代表地圖上的物件(geographical object) ,可能是point, polyline or polygon

[back]

Where語段

• 以邏輯運算 (Or And Not ... ),比較運算( > = < .... )及幾何相位關係(參考下表...)來組合查詢條件

       Single Table

Where Income>15000 and Income < 25000

Where Not Object                       '代表資料表中尚未Geocoded的資料

Select * From customers

Where state=Any("NY", "MA","PA")   '相當於state="NY" or state="MA" or sate="PA"

　

Select * from states                              '巢狀式Select , 注意" >"運算式左右資料之合理性，例如本例比較運算">"之右側必須為單一數字

Where population >

(Select Avg(population) from states)

　

　

Multiple Tables

　

[一般關係]

　

 Select expression from A, B where var1 =   (select B.var1 from B where expression)    '注意運算是左右資料之對等

　

[相位關係]

　

Where A.obj contains B.obj        '幾何相位關係

　

Where A.obj intersects (select obj from B where Area(B,"km")>1000)                     '注意 " >" 運算式左右資料之對等

　

Geographical operator 參考表

　

[back]

Group By 語段

Group by column_name

將資料表中按指定之column_name分組,通常以下列六種函數將分組結果輸出

Avg(column)                                         該欄位之分組平均值

Count(*)                                                該欄位之分組筆數

Max(column)                                         該欄位之分組最大值

Min(column)                                          該欄位之分組最小值

Sum(column)                                         該欄位之分組總合

WtAvg(column, weight_column)           該欄位之分組加權平均值(按某欄位數值加權)

範例:

[back]

SQL Select 一般程序

The general procedure for using SQL Select is as follows: 

1. Open the table you wish to query, if you haven't already done so. 

The table that you query is known as the base table. If you use SQL Select to perform a query on the World table, the World table is your base table. 

2. Choose Query > SQL Select. Fill in the portions of the SQL Select dialog that meet your needs. When you click OK, MapInfo performs the query.

MapInfo extracts data from your base table, stores the query results in a special, temporary table, known as the results table. The results table contains only the rows and columns that meet your criteria. The default name of the results table is Selection (although you can specify a different results table name in the Into Table Named field in the SQL Select dialog). 

3. Open a Map window and/or a Browser window if you want to see the query results. By default, MapInfo displays the results table in a Browser window automatically (unless you clear the Browse Results checkbox in the SQL Select dialog). 

If your results table is called Selection (the default name), the Browser window shows a different table name, such as Query1 or Query2. This is because the moment you Browse the Selection table, MapInfo takes a "snapshot" of the table, and names the snapshot Queryn (where n is a number, one or greater). MapInfo takes the snapshot because "Selection" is a special table name; Selection dynamically changes every time you select or de-select rows. 

In the SQL Select dialog, you can enter a different name for your results table (e.g. you can name your results table My_Query). This prevents MapInfo from renaming your results table Queryn. 

4. MapInfo automatically selects all rows in the results table. Thus, after you perform SQL Select, you can perform operations on the entire set of selected rows. For example, you could apply a different fill color to all selected rows (by choosing Options > Region Style), or you could cut or copy all selected rows. 
Usually, any alterations you make to the results table are automatically applied to your original (base) table. For example, if you use SQL Select to select some of the rows from the Orders table, and then you delete some of the rows from your results table, MapInfo deletes the corresponding rows from your base table (Orders). However, if your query produces subtotals, you can alter the results table without affecting the base table. 

5. Choose File > Save As if you want to make a permanent copy of the results table. If you do not perform Save As, the results table will be deleted when you exit MapInfo.

[back]