Sqlite整數型態轉成實數型態之方法

Sqlite的count函數係整數值，如計算2個count次數之比率時，整數相除會得0，要怎麼變成實數，方法如下：

with a as (select count(*) as ct from 輔具評估日誌 where 接案日 = '0.0')
    ,b as (select count(*) as ct from 輔具評估日誌)
select a.ct, b.ct, a.ct*1.0/b.ct from a, b

以一般資料表運算式(Common Table Expression)分解SQL

1.一般資料表運算式可作為暫存表

一個包含許多子查詢的複雜查詢，邏輯上相當難以分析，若先分解成數個邏輯較簡單的暫存資料表再行串接出結果查詢式，則較易分析，但是其缺點是無法以一個查詢表示；而一般資料表運算式(CTE, Common Table Expression)可對查詢結果作命名，等價於暫存資料表之效果，又兼具分解子查詢的效果。

範例1：單一的 CTE 用法

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
)
Select *
From OrdersTable

範例二：2兩個 CTE 作 Join

WITH OrdersTable (訂單ID, 客戶ID, 員工ID) as
(
Select OrderID, CustomerID, EmployeeID
from dbo.Orders
),
OrderDetailsTable as
(
Select OrderID as '訂單ID', ProductID as '產品ID', UnitPrice as '價格'
from dbo.[Order Details]
)

Select *
From OrdersTable A inner join OrderDetailsTable B
on A.訂單ID = B.訂單ID

遞迴表格

範例三：CTE 遞迴 (參考：黑暗大的部落格文章)

資料清單


Code：

--如果CTE的WITH不在第一列, 前方要加上;
;WITH 公司組織結果(部門名稱, 直屬部門, 層級, 排序欄位)
AS
(
--Recursive CTE分為兩個部分, 第一部分為Anchor Member
--指不會被遞迴呼叫到的部分
SELECT 部門名稱, 直屬部門, 0, CONVERT(nvarchar(128), 部門ID)
FROM dbo.公司組織 WHERE 直屬部門=N'ROOT'

UNION ALL

--UNION ALL後方的部分稱為Recursive Member, 會在遞迴過程中反覆執行,
--直到無任何查詢結果為止
SELECT P.部門名稱, P.直屬部門, B.層級+1,
CONVERT(nvarchar(128), B.排序欄位 + '-' + CONVERT(nvarchar(128), P.部門ID))
FROM dbo.公司組織 P, 公司組織結果 B
WHERE P.直屬部門=B.部門名稱
)
SELECT (REPLICATE(' ', 層級) + 部門名稱) as '部門名稱', 層級, 排序欄位
FROM 公司組織結果
ORDER BY 排序欄位




另一個 CTE 遞迴範例 (參考：Journey to SQL Authority with Pinal Dave)

Code：

USE AdventureWorks
GO
WITH Emp_CTE AS (
--找出 ManagerID 為 NULL 的資料當作 Anchor Member
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL

UNION ALL

--之後以 Anchor Member 為依據遞迴查詢
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO

select

從資料庫中檢索資料的過程或命令稱為查詢。 主要是使用 select 命令來定義查詢，select 命令建立一個暫時虛擬表。

定義顯示表頭

程式1：select 語法

select *
from table_expression

最簡單表頭定義的是 *，表頭定義中 * 意指表頭為代表 from 資料表表示式所有欄。表頭定義也可選擇可用欄子集或者在檢索它們之前對欄值進行計算；

程式2：選出員工檔全部欄

select * 
from employees

假設一個資料表稱為employees，這條命令將從 employees 中檢索所有紀錄和欄。

from 定義查詢所要引用並作處理的的資料表，資料表引用可以是基本資料表，連接及子查詢的複雜構造。

from employees 是最簡單的資料表表示式，它只讀取了一個資料表 employees。

欄名引用

程式1：select 語法

select c1, c2, ...
from table_expression

另一種用逗號分隔的欄名陣列來定義表頭，每個欄名為 from 子句定義資料表中實際欄名或別名。。

是本局辦理研習時，要產出所有員工的簽到表可以[emp_namelist.sql]表示，此 sql 表示員工表取出所有員工名稱組成新的表。

表名引用

程式1：select 語法

select t1.c1, t2.c2, ...
from table_expression

若超過一個資料表有相同欄名，那麼還必須指定表名或表別名。

程式2：員工部門名單

select e.name, d.name dep
from employees e join departments d on e.dep_id = d.id

值表示式

值表示式會為結果表每一筆紀錄進行一次計算，計算之前用該欄的數值代換任何值表示式裡引用的欄名。

下式表示各人出生年份。

程式1：各人出生年份

select name, substr(birthday, 0, 4) as year 
from humans

比如若 employees 表頭有 name 和 salary 的欄，分別代表員工名稱及月薪，則下式表每位員工月薪為幾美金：

程式2：每位員工月薪為幾美金

select name, salary / 34 
from employees;

值表示式的並不一定要有引用來自 from 子句中表表示式裡面的表的欄名， 可以有任意常數。

下式產出同員工筆數的體溫正常及不正常標籤，可拿來作門禁用。

程式3：體溫標籤

select 'normal', 'fever'
from employees

可以省略值表示式而用 select 命令當做一個計算器：

程式4：select_no_from.sql

select 3 * 4;

表頭定義裡的表示式也可為函數，比如，可以呼叫函數 random()：

程式5：select_random.sql

select random();

別名

程式1：語法

name [as] alias

其中 name 為欄名，alias 為欄的別名，as 可加可不加。

下式將職稱姓名欄別名為 name_title。

程式2：職稱姓名表

select name || title as name_title
from employees

查詢表頭欄名和在 from 子句裡的欄名是不同。

預設欄名

若表頭未定義欄別名，則系統會賦予預設值。對欄值引用， 預設是欄名。對於函數調用，預設是函數名。對於複雜表示式，系統應生成一個通用名。

對[select_no_from.sql]會產出下表，欄名為表示式文字表示。

表1：select_no_from

3 * 4
12

distinct

若兩筆紀錄裡至少有一欄值不同，則此兩筆紀錄為相異，否則為相同。

若於 select 後加入 distinct 修飾，則合併相同紀錄。

列出本局人員具有的職稱。

程式1：本局人員職稱列表

select distinct title
from employees

相對 distinct 是 all，因它是預設值，所以通常不寫。

兩個空值認為是相同的。

SQL: Insert 插入資料

到目前為止，我們學到了將如何把資料由表格中取出。但是這些資料是如果進入這些表格的呢？ 這就是這一頁 (INSERT INTO) 和下一頁 (UPDATE) 要討論的。

基本上，我們有兩種作法可以將資料輸入表格中內。一種是一次輸入一筆，另一種是一次輸入好幾筆。 我們先來看一次輸入一筆的方式。

依照慣例，我們先介紹語法。一次輸入一筆資料的語法如下：

INSERT INTO "表格名" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)

假設我們有一個架構如下的表格：

Store_Information 表格
Column Name Data Type
store_name char(50)
Sales float
Date datetime
而我們要加以下的這一筆資料進去這個表格：在 January 10, 1999，Los Angeles 店有 $900 的營業額。我們就打入以下的 SQL 語句：

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')

第二種 INSERT INTO 能夠讓我們一次輸入多筆的資料。 跟上面剛的例子不同的是，現在我們要用 SELECT 指令來 指明要輸入表格的資料。如果您想說，這是不是說資料是從另一個表格來的，那您就想對了。 一次輸入多筆的資料的語法是：

INSERT INTO "表格1" ("欄位1", "欄位2", ...)
SELECT "欄位3", "欄位4", ...
FROM "表格2"

以上的語法是最基本的。這整句 SQL 也可以含有 WHERE、 GROUP BY、及 HAVING 等子句，以及 表格連接及別名等等。

舉例來說，若我們想要將 1998 年的營業額資料放入 Store_Information 表格，而我們知道 資料的來源是可以由 Sales_Information 表格取得的話，那我們就可以鍵入以下的 SQL：

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

SQL 資料的異動指令

Truncate 刪除 Table 的全部資料

從資料表中移除所有資料列，且不記錄個別資料列的刪除動作。 TRUNCATE TABLE 類似於不含 WHERE 子句的 DELETE 陳述式；不過，TRUNCATE TABLE 比較快，使用的系統資源和交易記錄資源也比較少。

TRUNCATE TABLE employees;

移除 employees 所有資料列。

日期字串互轉

日期字串互轉對資料操作很重要
INFORMIX
to_date(strdate, strformat)
to_char(date, strformat)

建構 MINUS 等效式

像 Informix 這種不支援 MINUS 運算子的，建構 MINUS 等效式就很重要。
MINUS 運算子第一個運算元的運算式稱為主查詢，
第二個運算元的運算式稱為副查詢。


select first_name, second_name from doctor
MINUS
select first_name, second_name from doctor where dept = "surgery"


上式是找出不是外科部的同名同姓醫生。

如上式，select * from doctor 為主查詢，
select * from doctor where id == 3 為副查詢。

一般有兩種建構法，
第一是將副查詢的 WHERE 條件作反向式：

select * from fred
WHERE id != 3;

第二種將副查詢改成投射 rowid 的查詢，令其為 R
並對主查詢加入 rowid NOT IN 副查詢的條件式：

select * from doctor
WHERE rowid NOT IN (select rowid from fred where id = 3);

組 SQL 字串時，加入 WHERE 1=1 條件，以增加組 SQL 字串彈性

若在一般程式語言存取關聯式資料庫，必須要組 SQL 字串，傳至後端的 DB Server，利用 WHERE 1=1，則可把條件句彈性的組合到 SQL 字串中，如下：

strSQL = "select * ";
strSQL +="from employee ";
strSQL +="where 1=1 ";
if(emp_no.length() > 0) {
strSQL +="and emp_no='"+emp_no+"' ";
}
if(emp_name.length() > 0) {
strSQL +="and emp_name='"+emp_name+"' ";
}

上述程式碼動態要將條件語句加入到原本的 SQL 字串中，而不用考慮原本 SQL 字串的格式。

多個欄位的單一關鍵字查詢，請用 or 運算子

試想，若你希望使用者輸入一個關鍵字來查詢資料， 此關鍵字可以是員工代號，或是員工姓名，那麼也許你會執行兩次 SQL，並判斷兩次 SQL 是否有資料，以達到上述功能：

SQL1:
sqlstr = "select *
from employee
where 1 = 1
and emp_no = '#{keyword}'"

SQL2:
sqlstr = "select *
from employee
where 1 = 1
and emp_name = '#{keyword}'"

上述二個 SQL 可簡單的以 or 運算子合併成下式：

SQL3:

sqlstr = "select *
from employee
where 1 = 1
and emp_no='#{keyword}' or emp_name = '#{keyword}'"

如此一來除了更為簡潔，也讓原本要執行兩次 SQL 查詢，減為一次。

SQL 的 alter 指令

下面有不錯的範例
http://www.techonthenet.com/sql/tables/alter_table.php