使用 PHP 連接 Microsoft SQL Server 資料庫

依不同的 PHP 版本而定...

PHP 5.x

上古時代想連接 SQL Server 則要透過 FreeTDS 開源套件，說明如下：

FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases.

; 安裝所需套件
# apt-get install freetds-common freetds-bin unixodbc php5-sybase

其中 php5-sybase 就包括 mssql.so 函式庫，所以基本上一行指令可以搞定所有事；之後更改 FreeTDS 相關參數(TDS連線協定版本)與連線編碼(UTF8支援中文字)：

設定 /etc/freetds/freetds.conf：

[global]
# TDS protocol version
tds version = 8.0
client charset = UTF-8

連線測試可用下列指令：

tsql -S DBserver -p 1433 -U dbadmin -P dbpass
1> SELECT  @@servername
2> GO
3> SELECT @@servicename
4> GO

在 PHP 程式中使用下列指令進行連線/查詢 (PHP7中捨棄)：

• mssql_connect()
• mssql_query()
• mssql_fetch_array()

 PHP 7.x

微軟針對這個PHP版本所開發延伸套件：Microsoft Drivers for PHP for SQL Server(專案網址)

; 在 /etc/apt/sources.list 加入 APT 套件庫
deb https://packages.microsoft.com/debian/8/prod jessie main

; 安裝套件
# apt-get install php-pear msodbcsql mssql-tools unixodbc-dev
# pecl install sqlsrv
# pecl install pdo_sqlsrv

; 掛載函式庫
extension=sqlsrv.so
extension=pdo_sqlsrv.so

在 PHP 程式中使用下列指令進行連線/查詢：

• sqlsrv_connect()
• sqlsrv_query()
• sqlsrv_fetch_array()

MySQL 設定參數

在 Debian 9 封裝的 mysql-server 認證模式預設採用 auth_socket(或unix_socket)，傳統的認證為 mysql_native_password 方式，可以透過下列方式更改，說明詳見此頁討論：

# mysql -u root -p

> USE mysql;
> SELECT User, Host, plugin FROM mysql.user;
> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
> UPDATE user SET password =PASSWORD('foobar') WHERE user = 'root'; // MariaDB 10
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'foobar'; // MariaDB 10.4
> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('foobar');
> FLUSH PRIVILEGES;

# /etc/init.d/mysql restart

[ 匯出資料或架構Schema ]

mysqldump -u root -p databaseX > dbX_dump.sql
mysqldump -u root -p databaseX --no-data > dbX_dump.sql

[ 帳戶權限操作 ]
權限設成 localhost 僅限從 mysql server 本機登入，反之權限設定為 % 代表可從任何遠端主機連入；在 mysql 系統上兩個帳號視為不同且可並存。

select user,host from mysql.user;
create user 'jack'@'localhost' identified by 'NEW_PASSWORD';
create user 'jack'@'%' identified by 'NEW_PASSWORD';

設定遠端連入：/etc/mysql/mariadb.conf.d/50-server.cnf

bind-address  = 0.0.0.0

將 MysQL 中所有資料列塞進 Memory 內加快查詢

有點暴力的作法，基本上就是將資料存一份在磁碟中，於資料庫啟動後把所有資料全倒進記憶體內，加快查詢速度。

(1) 調整 heap table 的大小，因為 MySQL 的 Memory Engine 使用這個資料結構。

# my.ini
max_heap_table_size = 512MB

(2) 將資料倒入

# 建立表格 mem_parkTable 其 Schema 延用 parkTable
mysql> CREATE TABLE mem_parkTable LIKE parkTable;

# 設定儲存引擎為記憶體
mysql> ALTER TABLE mem_parkTable ENGINE = MEMORY;

# 開始倒資料進來
mysql> INSERT INTO mem_parkTable SELECT * FROM parkTable;

(3) 基本上到這邊就完成了，注意一下如果 MySQL 重新啟動後，在記憶體內的資料會完全不見(廢話)，需要重新匯入。

匯入「100萬筆」記錄至 MySQL 資料庫

手上的樣本檔有150萬筆資料量，檔案(csv格式)大小約100MB，用 phpMyAdmin 匯入當然是失敗！硬著頭皮看有什麼指令可用...

(1) 因為安全考量 MySQL 預設關閉從檔案匯入資料，所以先調整 my.ini 設定檔：

[mysql]
local-infile=1
[mysqld]
local-infile=1

(2) 建立資料庫(parkDB)後，再建立表格(parkTable)存放記錄：

'第一欄位序號(SN)作為主鍵並具遞增性質
CREATE TABLE parkTable
( SN integer AUTO_INCREMENT PRIMARY KEY ,
  PARK_DATE varchar(10), PARK_TIME varchar(10),
  CAR_NO varchar(10), BRAND varchar(20),
  COLOR varchar(20) );

(3) 從檔案匯入資料庫

# mysql -u root -p
mysql> use parkDB
mysql> LOAD DATA LOCAL INFILE 'SOURCE.csv' INTO TABLE parkTable
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
    -> (PARK_DATE, PARK_TIME, CAR_NO, BRAND, COLOR);

參數相關說明：

• 從檔案 SOURCE.csv 匯入資料到表格(parkTable)
• 欄位區分是以逗號(,)來作識別。例如資料 A,B,C 看作三欄。
• 每欄資料是用雙引號(")圍起來。例如資料 "XYZ-123"。
• 換行符號是 "\n"
• 匯入資料對應欄位為 (PARK_DATE, PARK_TIME ...)

(4) 資料量很大時，為加快查詢請記得建立索引。

'索引欄位 CAR_NO
CREATE INDEX idx_CAR_NO ON parkTable (CAR_NO);

SQL UPSERT 語法

在 SQL 中的 UPSERT 語法指的是：若資料不存在則新增一筆，否則更新已存在資料。原文見維基百科 Merge (SQL)條目

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches.

在 MySQL 實作語法為 INSERT ... ON DUPLICATE KEY UPDATE
實際用法：

INSERT INTO table (IDX, IPAddr, Msg, Status)
VALUES ('1', '127.0.0.1', 'foorbar', 'UNSENT')
ON DUPLICATE KEY UPDATE Status = 'SENT'

說明：

• 若記錄不存在，則新增一筆 (1, 127.0.0.1, 'foorbar', 'UNSENT') 
• 如果原記錄存在，修正更新為 (1, 127.0.0.1, 'foorbar', 'SENT')

針對資料列存在與否，不須仰賴其他程式進行判斷，直接透過 SQL 處理。若資料列不存在，則進行插入(INSERT)動作，否則進行更新(UPDATE)動作。

實例更新(Jan. 22, 2018)：

資料表 daily_transaction 結構 ( idx, date, qty, amount )

• idx 為主鍵自動遞增(AUTO INCREMENT)
• date 為唯一鍵(UNIQUE key)
• qty 代表件數、amount 代表總金額

在此例中只在乎日期(date)是否存在、不管 idx 值；存在則更新資料，否則插入資料：

INSERT INTO daily_transaction (date, qty, amount)  //date 是唯一鍵
VALUES('1070122', '168', '368100')
ON DUPLICATE KEY UPDATE qty='168', amount='368100';

[MySQL] Can't create database 'performance_schema'

在執行完 MySQL upgrade 後就炸掉了...

/etc/mysql/debian-start[4802]: ERROR 1007 (HY000) at line 160: Can't create database 'performance_schema'; database exists

修正方式 ：

# rm -rf /var/lib/mysql/performance_schema
# mysql_upgrade -u root -p

MySQL 效能分析調教工具

MySQL 效能調教工具：
- MySQLTuner (使用 perl 寫的分析工具，2011)
- MySQL Performance Tuning Primer Script (2011)

大型範例資料庫 (供練習 SQL 語法使用)

http://dev.mysql.com/doc/index-other.html

由 MySQL 提供的大型範例資料庫(Sample Database)，可拿來練習 SQL 語法。

以 "Employees database" 這個資料庫為例

# mysql -u root -p
mysql> source employees.sql

挑戰 PHP5&MySQL 程式設計樂活學

《挑戰 PHP 5 MySQL 程式設計樂活學, 2/e》



ISBN: 9789862763384
書中範例 (載點1 , 載點2)

PHP & MySQL 案例開發實戰手冊

《PHP & MySQL 案例開發實戰手冊》

出版日期: 2012-05-24
ISBN: 9789862765166
頁數: 640

書中範例 載點 1、載點 2

SQL - NOT EXIST usage

網上有一些關於EXISTS 說明的例子,但都說的不是很詳細.比如對於著名的供貨商數據庫,查詢:找出供應所有零件的供應商的供應商名,對於這個查詢,網上一些關於EXISTS的說明文章都不能講清楚.

我先解釋本文所用的數據庫例子,'供貨商' 數據庫,共3個表. 供貨商表 S(S#,SNAME), 貨物表 P(P#,PNAME), 供貨商-貨物表 SP(S#,P#). 字段S#,P#分別代表供貨商和貨物的ID.

在C.J.Date的數據庫系統導論第八版中文版第147頁給出了, EXISTS的比較正規的解釋, "EXISTS( SELECT ... FROM ...)取真值,當且僅當 SELECT ... FROM ... 取非空值.在作為相關子查詢的例子中,SQL涉及子查詢,因此它包含了一範圍變量的引用,即隱式範圍變量S, 它在外查詢中定義."

我個人認為,此處所指的外查詢定義的隱式範圍變量S, 可以用另外一種方法來解釋: 將外查詢表的每一行,代入內查詢作為檢驗, 如果內查詢返回的結果取非空值,則EXISTS子句返回TRUE, 這一行行可作為外查詢的結果行, 否則不能作為結果.

至此可以明確,EXISTS(包括 NOT EXISTS )子句的返回值是一個BOOL值. EXISTS內部有一個子查詢語句(SELECT ... FROM...), 我將其稱為EXIST的內查詢語句.其內查詢語句返回一個結果集. EXISTS子句根據其內查詢語句的結果集空或者非空,返回一個布爾值.

舉一例子說明: 找出供應所有零件的供應商的供應商名

SELECT DISTINCT S.SNAME
FROM S
WHERE NOT EXISTS
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE SP.S#=S.S#
AND SP.P#=P.P#) );

假設數據如下:

S

S#	SNAME
1	S1
2	S2

P

P#	PNAME
1	P1
2	P2

SP

S#	P#
1	1
1	2
2	1

這個查詢過程如下:

STEP1: 將S表第一行(1,S1) 作為隱式變量V1, 代入第一個NOT EXISTS子句. 由於這個子句嵌套一個NOT EXISTS子句, 再將 P表第一行(1,P1) 作為隱式變量V2, 和V1一起代入第二個NOT EXISTS子句中, 這時第二個NOT EXISTS的內查詢子句變成

SELECT *
FROM SP
WHERE SP.S#=1
AND SP.P#=1

其返回結果集為

S#	P#
1	1

這個返回結果集非空,注意NOT EXISTS子句返回的是EXISTS子句的非,因此 第二個NOT EXISTS 子句返回FALSE. 因此V2不能加入第一個NOT EXISTS子句的內查詢子句返回結果.

同理,將P表第二行(2,P2)作為隱式變量V3, 與V1一起代入第二個NOT EXISTS子句中,內查詢返回結果集非空(返回 行(1,2) ), 因此V3也不能加入第一個NOT EXISTS子句的內查詢返回結果集.

至此, 對於隱式變量V1(也就是S的第一行), P表的每一行都已代入第二個NOT EXISTS子句中進行檢驗,返回結果是一個空集, 因此對於第一個NOT EXISTS子句,其內查詢子句返回結果為空.因此,第一個NOT EXISTS子句返回TRUE.因此, V1(1,S1)加入外查詢的結果集.

STEP 2: 將S表的第二行(2,S2)作為隱式變量 V4, 代入第一個 NOT EXISTS 子句. 將 V4,V2, 一起代入第二個NOT EXISTS子句. 第二個NOT EXISTS子句內查詢結果集返回非空(2,1),第二個NOT EXISTS子句返回FALSE.V2 不能加入第一個NOT EXISTS子句的內查詢結果集.

將V4,V3 一起代入第二個NOT EXISTS子句, 這時第二個NOT EXISTS子句的內查詢子句變成:

SELECT *
FROM SP
WHERE SP.S#=2

AND SP.P#=2

在SP表中,並沒有S#=2 AND P#=2 的一行,因此,第二個NOT EXISTS子句的內查詢子句返回空集,第二個NOT EXISTS子句返回 TRUE. 因此V3, 可以插入第一個NOT EXISTS子查詢結果集.

至此, 對於隱式變量V4(也就是S的第2行), P表的每一行都已代入第二個NOT EXISTS子句中進行檢驗.第一個NOT EXISTS子查詢語句返回結果集為:

P#	PNAME
2	P2

非空,因此第一個NOT EXISTS子句返回false,V4(2,S2) 不能加入外查詢的結果集.

至此S表的每一行都代入第一個NOT EXISTS子句中進行檢驗, 外查詢的返回結果是

SNAME

S1

查詢結束.

從上述查詢過程來可以得知, 第二個NOT EXISTS子句的內查詢語句返回的結果集的含義是, 一個供貨商能否供應某種貨物. 第一個NOT EXISTS的內查詢語句返回的結果集的含義是, 某一個供貨商不能供應的貨物. 而連起來使用,就是用排除法得到"沒有不能供應的貨物的供貨商", 也就是能供應所有貨物的供貨商.

SQL 快攻手冊

# (DDL) 針對 Table 架構運作的指令 - CREATE / DROP / ALTER
# (DML) 針對 值組 運作的指令 - INSERT / DELETE / UPDATE

# 建立一張表內含兩個欄位
create table myTab
(sn char(10),
name char(10)
)

# 在 myTab 加入一筆資料
INSERT INTO myTab
VALUES ('1', 'jack');

# 在 myTab 中異動加入一個新的欄位 tel
alter table myTab
add tel char(20);

關聯式計算 (Relational Calculus)

[關聯式計算] 分為二類：值組導向與定義域導向

員工(編號, 姓名, 電話, 薪水, 年紀)

ex: 找出年紀大於 30 的員工列出編號名字
ans: { e.編號 , e.名字 | 員工(e) and e.age>30 }

中譯: 在員工表中的每個 值組(tuple) 命名為 變數e 來操作

要找年紀大於30者，就直接給條件 e.age>30 就可以了

ER diagram to Relation

ASP.net environment setup

目前測試成功的安裝組合：

[ 第一組 ]
(1) Visual Studio Express 2008 with SP1
(3) Windows Installer 4.5 (下載)
(4) PowerShell 1.0 (下載)
(4) SQL Server 2008 Express with Tools

[ 第二組 ]
(1) Visual Studio Team System 2008
(2) Windows Installer 4.5
(3) PowerShell 1.0
(4) Visual Studio 2008 Service Pack Preparation Tool (下載)
(5) Visual Studio SP1
(6) SQL Server 2008 Express with Tools

我的 Visual Studio 2008 環境設定檔：下載