【筆記】在Ubuntu 14.04中，用python來連線並查詢MySQL中的資料

先到這裡下載mysql-connector-python_2.0.4-1debian7.6_all.deb：

wget http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python_2.0.4-1debian7.6_all.deb

並將其安裝：

sudo dpkg -i mysql-connector-python_2.0.4-1debian7.6_all.deb

安裝完成後，就開始來寫程式吧！這裡將程式存成mysql_query.py：

#!/usr/bin/python
import mysql.connector
cnx = mysql.connector.connect(user='cbuser', password='cbpass',
                              host='127.0.0.1',
                              database='cookbook')

cursor = cnx.cursor();
query = "select a, b from test"
cursor.execute(query)

for(a, b) in cursor:
        print("column a is {}, column b is {}".
                format(a, b))

cursor.close()
cnx.close()

在test這個資料表中的資料如下：

+---+------+
| a | b    |
+---+------+
| 1 | abc  |
| 2 | xyz  |
+---+------+

最後執行程式：

python mysql_query.py 

執行結果為：

column a is 1, column b is abc
column a is 2, column b is xyz

【筆記】MySQL storage engine

MyISAM Engine

1. 為MySQL 5.1版本以前的預設引擎
2. 不支援交易(transaction)或row-level lock
3. 非remotely crash-safe，此乃最大的弱點
4. 在儲存資料表的方式是將其儲存為一個data file(.MYD)和一個index file(.MYI)
5. MyISAM工作時鎖定(lock)整個資料表，而非單一列資料；但仍可以在有select執行時，做寫入的動作
6. 在MyISAM的資料表中，可以對BLOB和TEXT欄位的前500個字元建立index，MyISAM支援full-text indexes

【筆記】使用MySQL指令來匯入本機檔案到資料庫

現在我們有一個純文字檔，裡面有三筆使用者的資料，我們要匯入到USER資料表中，資料如下：

3,Tom,30000
4,Peggy,25000
5,Hurry,35500

在Terminal中，先使用下列指令登入mysql：

$ mysql --host=localhost -u root --local-infile=1 -p jpa

其中要注意的是--local-infile這個參數，在指令說明中描述如下：

--local-infile      Enable/disable LOAD DATA LOCAL INFILE.

將他設為1可以讓我們匯入本機的檔案。

mysql> LOAD DATA LOCAL INFILE '/home/bio/temp/user_import.txt' 
INTO TABLE user FIELDS TERMINATED BY ',';

若匯入成功，會顯示下列訊息：

Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

檢視匯入的資料：

你可以對這篇文章有興趣：【筆記】使用MySQL指令來匯出資料。

【筆記】使用MySQL指令來匯出資料

最近我發現若使用指示來匯出MySQL中的資料非常方便，尤其是在某些情境下，沒有UI工具可以使用時。

在這裡，來看一下，如何在Ubuntu環境中，使用指示來匯出資料，這些參數在Windows環境中一樣適用。

這裡我使用一個名為「USER」的資料表來做說明，其內容為：

開啟Terminal後，輸入下列指令：

$ mysql --host=localhost --port=3306 --user=root -D jpa \
--password -e "select * from user" \
> /home/bio/temp/user_export.txt

你也可以在select子句中加入where條件來篩選要匯出的資料。

指示輸入後，依要求輸入密碼後，即會把結果匯出到/home/bio/temp/user_export.txt中。

在上列的指令中：

• 使用-D來指定要連線的資料庫。 
• 使用-e來指定要執行的命令。 

更多說明可以在Terminal執行

$ mysql --help

或

$ mysql -?

來了解詳細說明。

檢視匯出的檔案，結果如下：

id      name    salary
1       Mary    10000
2       John    50000

你可能對這篇【筆記】使用MySQL指令來匯入本機檔案到資料庫有興趣。

【筆記】Mac中MySQL啟動、停止或重新啟動的指令

在我的Mac中，MySQL是以dmg檔的方式安裝的，所以在啟動、停止或重新啟動時，要使用下列命令：

sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
sudo /Library/StartupItems/MySQLCOM/MySQLCOM restart

或是

sudo /usr/local/mysql/support-files/mysql.server start
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart

以上指令，在我的電腦中是有作用的。

參考來源

• How do you stop MySQL on a Mac OS install?

【筆記】MySQL判斷varchar型態欄位值是否為數字

在網路上搜尋了一下，在MySQL 中好像沒有內建的函式，可以用來判斷varchar型態的欄位中的字串是否含有「不是數字」的字元，後來找到有人使用正規表示式來做判斷，如下：

SELECT * 
FROM  `emp` 
WHERE phone NOT REGEXP ('(^[0-9]+$)')

結果：

從上列的結果可以得知，只要是欄位中的值有不是數字的字元，就會符合條件。

若要查詢只有數字的值的話，就把NOT拿掉。

在MySQL中，REGEXP是用來判斷要比對的字串，是否有符合定義的模式，若有符合會回傳1，若不符合則回傳0，如：

SELECT 'a' REGEXP '^[a-d]';
-- 結果為 1

詳細說明可以參考MySQL的Regular Expressions文件。

參考來源：

• Regular Expressions
• How to Find Out if an Entire String Is Numeric

【筆記】在MySQL中做字串連接

在MySQL(5.5.22)中，做字串連接，是不可以用「+」，如：

select `name` + ' is here' from user

結果會是：0

要做字串連接，要用Concat這個function，如：

select CONCAT(`name`,' is here') from user

結果就會是：bigbear is here

參考文件

• http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_concat

【筆記】MySQL版本不同，subquery order by結果不同

今天遇到一個問題，用下列的語法先做一次sub query，再對sub query去select，在一個DB中，結果會如小弟預期的，以id這欄位做由大至小排序，但在另一個DB中，結果卻以id做由小至大排序。

select * from (
     select * from mytable a order by id desc
) s;

查明之後，才暸解這是因為MySQL版本的問題，在5.2之前的MySQL，結果會如預期地由大至小排序，但在5.3之後的版本，在sub query外再做select，裡面的排序就沒作用。

【筆記】在MySQL取亂數的用法

在MySQL中要取亂數，要使用rand()這個function。

它回傳的值是大於等於0且小於1的浮點數，所以，若你要取1到1000之間的亂數的話，就用：

select FLOOR(1 + rand() * 1000)

因為rand()回傳>=0且<1，所以rand() * 1000會隨機傳回0~999之間的數字，因此在最前面加上1，再以FLOOR取整數值，就可以隨機取得1~1000的數字了。

【筆記】MySQL select interval

在MySQL中，要取得前幾天、前幾個月、或前幾年的日期，有個好用的方法，比如說要取一天前的datetime：

select now() - interval 1 day

結果如下：

+------------------------+
| now() - interval 1 day |
+------------------------+
| 2014-12-01 22:14:24    |
+------------------------+
1 row in set (0.00 sec)

你也可以搭配MySQL的DATE_ADD或DATE_SUB來使用，如：

select date_add(now(), interval 1 month); 

通用寫法如：

date + INTERVAL expr unit
date - INTERVAL expr unit

上列unit如下表示
秒：second
分：minute
天：day
週：week
月：month
年：year

unit也可以用多種單位表示，如使用HOUR_SECOND表示由時到秒，這時上列的表示式就要寫成'1:1:1'，如：

select now(),date_sub(now(), interval '1:1:1' HOUR_SECOND);

上列執行結果如下：

+---------------------+-----------------------------------------------+
| now()               | date_sub(now(), interval '1:1:1' HOUR_SECOND) |
+---------------------+-----------------------------------------------+
| 2014-12-02 22:10:41 | 2014-12-02 21:09:40                           |
+---------------------+-----------------------------------------------+

結果顯示時、分、秒都減去一單位。

更詳盡的用法請參考MySQL 5.5 Reference Manual。

【筆記】MySQL的USING用法

USING是在MySQL中使用join時可以使用，當兩個要join的資料表中，用在on的欄位名稱相同時，就可以用using代替，如：
在A表中有欄位C1，在B表中也有欄位C1，一般在join A和B兩個Table時，會寫成：

select a.XX, b.yy from A a inner join B b on a.C1 = b.C1

但當join用的欄位名稱相同時，如上列的C1，就可以寫成：

select a.XX, b.yy form A a inner join B b using(C1)