JamesPCSchool: SQL Database Management Part 6

1. SQL Syntax

a. select syntax

1. select * from table
2. select 'hello world'
3. select getdate()

4.select @@version;

b. Use the AS to create an alias for the data line. Compare the differences between the two examples

b. 利用AS 建立資料行的別名。比較二個範例的差異

Example 1：select c_name as '中文名' from employee

Example 2： select c_name from employee

c. Distinct: Eliminate duplicate data columns

The information that makes the field repeat appears only once.
讓該欄位重複的資料只出現一次。

d.Import Data

1. Add a new database
2. Import data by access
1.新增資料庫
2.由 access 匯入資料

PS:Key & index --- MS SQL Import & Export Data

To explain why MS SQL's import & export data does not automatically set Key & index, not because of the wizard. It is necessary to understand the difference between Key & index of MS SQL and the difference between data & data Schema.

1. The difference between Key & index

Index: An index, which can be a field or multiple fields. A data table can have multiple indexes. It can also be divided into clusterd-index & non-clustered-index. Clustered indexing and non-clustered indexing (which greatly affects database performance, care must be taken when designing data sheets).

Key: The key value is a kind of index. A data table can only have one key value. He is unique and cannot be repeated.

2. Export / Import: data & data Schema

The so-called data Schema refers to the architecture of the database, data tables, and so on. For example: Key, Index, view, triger, stored procedure, etc.

Please note that the previous picture refers to the import information, not the data structure. Therefore, it will simply import the data. To have data & data Schema, you must first export the data schema and then export the data. Or (above MS SQL Standard Edition) choose to include data Schema when exporting. PS: This function is only available in MS SQL. There is no way to include schema directly if you want to export from MS Access, Excel or other brands. It must be rebuilt afterwards.

PS：Key & index --- MS SQL 的匯入 & 匯出資料
要說明 MS SQL 的匯入 & 匯出資料為何沒有把 Key & index 自動建立起來，不是因為精靈的問題。就要先了解 MS SQL 的 Key & index 的差別，以及 data (資料) & data Schema (資料架構) 的差別。
1. Key & index 的差別
Index：索引，可以是一個欄位或多個欄位所組成的。一個資料表可以有多個索引。又可以區分為 clusterd-index & non-clustered-index。叢集索引與非叢集索引 (會大大地影響到資料庫效能，設計資料表時須注意)。
Key：鍵值，是索引的一種。一個資料表只可以有一個鍵值。他是唯一的，不可重複的。

2. 匯出 / 匯入：data (資料) & data Schema (資料架構)
所謂data Schema (資料架構)就是指資料庫、資料表、等的架構。例如：Key, Index, view, triger, stored procedure 等等。
請注意上一張圖是指匯入資料，並不是資料架構。所以就只會單純的匯入匯出資料 (data)。若要有 data (資料) & data Schema (資料架構) ，必須先匯出匯入data Schema (資料架構)，再執行匯出匯入 data (資料)。或者(在MS SQL 標準版以上)在匯出匯入時選擇包含data Schema (資料架構)即可。 PS：只有 MS SQL 有此功能，若要從 MS Access、excel 或其他廠牌的資料庫匯出就沒有辦法直接包含 schema。必須事後重建。

e. order by

order by 可以有一個以上的欄位，第一個欄位的值相同，會再依第二個欄位排序。

f. select can customize the field to do calculation

f. select 可以自訂欄位可以做計算

PS：
1.select the order of the grammatical content, there are certain rules
1.select 語法內容的順序，有一定規則

SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

2. As an alias, you can't use it in the same select immediately, because the SQL syntax starts after pressing "Execute", and the alias will not appear until after "from".

3.order by Chinese is based on strokes, and can also be set to follow "phonetic" or "romany pinyin" during installation.

4. MS SQL presets are not case sensitive. You can also choose whether to distinguish between English and lowercase when installing.

2. As 別名之後，不可以在同一個 select 中馬上使用，因為 SQL 語法是在按下"執行"之後才開始，而且別名是在 "from" 之後才會開始出現。

3.order by 中文是依照筆畫，也可以在安裝時設定為依照 "注音" 或 "羅馬拼音"。

4. MS SQL 預設是不分英文大小寫。安裝時也可以選擇是否區分英文大小寫。

g. Where clause

g. Where 子句

Example 1：

select * from 書籍訂單 where 客戶名稱 = '十全書店' or 客戶名稱 = '身邊書店' order by 客戶名稱

Example 2：

select * from 書籍訂單 where 客戶名稱 in ('十全書店' , '身邊書店' ) order by 客戶名稱

The syntax of the above two examples is different, but the results are the same

1. The so-called where clause is to compare the data selected in the previous one, and compare the condition of the where clause to a true one. If so, the information is displayed, and the next step is not continued. But SQL will first perform the calculation of the where condition before executing select.

2. Combine with the index
For example:
Select * from book order where number > 50
Therefore, if the quantity field is indexed in advance, only the quantity > 50" will be filtered. If there is no index, it will be a one-by-one comparison. The efficiency of the index is here.

3. How to use an alias to query
select * from
(
select 單價*數量 as 小計 from dbo.書籍訂單
) a
where 小計 > 30000

以上二個範例的語法不同，但結果相同。

1. 所謂 where 子句，就是將前面 select 出來的資料，一筆一筆的比對 where 子句理的條件是否為 true，是的話就顯示該筆資料，不是就繼續下一筆。但是 SQL 會先執行 where 條件的計算，才去執行 select 的。

2. 與索引做結合
例如：
select * from 書籍訂單 where 數量 > 50
因此，若數量欄位有事先做索引，就只會篩選 "數量 > 50" 的資料。若沒有索引，就會一筆一筆的比對。索引的效率就在這裡展現出來了。

3. 如何使用別名來查詢
select * from
(
select 單價*數量 as 小計 from dbo.書籍訂單
) a
where 小計 > 30000

h. is Null & is not Null & isNull

is Null

is not Null

isNull 函數

i. You can add a custom field and enter a fixed value. For example, the "discount" field does not exist originally. It can be temporarily added to the select syntax and specify the content as a fixed value.

i. 可加入自訂欄位並輸入固定值。例如 "折扣" 這個欄位原本並不存在，可臨時在 select 語法中加入，並指定內容為固定值。

2. Operators and functions 運算子與函數

a. Add the fields and then output them. Connect the two fields with "+".a. 欄位相加之後再輸出，用"+"相連二個欄位。

b. Add the number field to the text field. You must first convert the number to text, and then remove the left text from the left text. (because of the length of the field itself)

b. 數字欄位與文字欄位相加，必須先將數字轉成文字，再將轉好的文字去掉左邊的留白。(因為數字本身欄位長度的關係)

The different nature of the fields cannot be added
欄位不同性質無法相加

After converting to a string, add it and it will be OK.
轉成字串之後再相加就OK了

c. Use substring to take the string

c. 使用 substring 取字串

Substring (customer name, 1, 2) as short
Description: substring (field name, taken from the first 1st, take 2 digits) as short

substring(客戶名稱,1,2) as 簡稱
說明：substring(欄位名, 從第 1 開始取, 取2位) as 簡稱

d. The location of the search string is in the first few

d. 搜尋字串的位置在第幾個

CHARINDEX('用',書籍名稱)
說明：CHARINDEX( '要搜尋的字串' , 欄位名稱)

SQL Database Management Part 6