程式開發學習之路

1.replace into

replace into table (id,name) values(1,'MYSQL'),(2,'GG')
此語法的作用是將table中插入兩條記錄。
如果主鍵id為1或2不存在則做新增動作，如果已存在則做替換動作。


2.replace(object,search,replace)

把object中出现search的全部替換為replace

select replace('123QWERTYEEE','E','1')--->123QW1RTY111

例：把表table中的name字段中的123替换为456
update table set name=replace(name,'123','456')

參考網站
http://dev.mysql.com/doc/refman/5.0/en/replace.html

[MySQL]使用正則表達式(REGEXP)的用法


練習

需求說明：
                 有多筆資料，每筆有多種商品代號碼(如：AAA,BBB,AAA_BBB)
                 每種商品用此附號 ( , )分開，
                 請找出商品代號為AAA的資料。
語法如下：

SELECT*
FROM
(#測試資料
      SELECT "AAA,BBB,CCC,DDD,EEE,AAA_BBB" AS number
      UNION ALL
      SELECT "AAA_BBB,CCC_DDD,EEE_AAA_BBB" AS number
      UNION ALL
      SELECT "AAA_BBB_CCC,DDD_EEE,AAA_BBB" AS number
      UNION ALL
      SELECT "BBB,CCC_DDD,EEE_AAA_BBB,AAA" AS number
      UNION ALL
      SELECT "CCC_DDD,EEE_AAA_BBB" AS number
      UNION ALL
      SELECT "CCC_DDD" AS number
      UNION ALL
      SELECT "AAA" AS number
      )AS testData
where testData.number REGEXP '[[:<:]]AAA[[:>:]]' > 0;#判斷語法

UNION ALL 的用法

結果如下：
會將有AAA的商品找出來，而上述的第二筆中的AAA_BBB，是不會背找出來的。

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [[SEPARATOR  str_val])
*SEPARATOR是要用什麼為分割字
參考資料

例：
資料建立一個訂單明細的資料表。
CREATE TABLE products_table (
  order_id bigint(20) NOT NULL default '0' COMMENT '關連訂單id',
  product_name varchar(255) NOT NULL default '' COMMENT '產品名稱',
  price bigint(20) NOT NULL default '0' COMMENT '價格'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO products_table(order_id,product_name,price)
VALUES
        (1,"商品1",10),
        (2,"商品1",10),
        (3,"商品1",10),
        (1,"商品2",20),
        (1,"商品3",30),
        (2,"商品4",40),
        (2,"商品5",50),
        (3,"商品6",60);

查詢語法1：(使用預設的)

SELECT p.order_id,
       GROUP_CONCAT(p.product_name ) as '訂單產品明細'
FROM products_table AS p
GROUP BY p.order_id;
結果：

CONCAT(str1,str2,...)
用法例：
SELECT CONCAT('M','y','S','Q', 'L');
結果：MySQL

用法例：
SELECT CONCAT(user.username,user.password ) FROM user

字串長度測量函式分別是CHARACTER_LENGTH()、CHAR_LENGTH()、LENGTH()。
CHARACTER_LENGTH()、CHAR_LENGTH()是相同的，以UTF8來計算，中英文都可以計算。
LENGTH()也可以計算中英文，但對中文的計算會因編碼不同而計算不同，分別是UTF8一字三位，BIG5一字二位。
字串切割函式分別是SUBSTRING_INDEX(culomnName, exp, number)、SUBSTR(culomnName, startIndex, length)。
SUBSTRING_INDEX(culomnName, exp, number)︰
exp是你想開始切割的辨別字元。
number則是以正負數兩種區分，當輸入正數則顯示辨別字左方所有字元，當輸入負數則顯示右方所有字元，如果輸入0會無法辨別。
SUBSTR(culomnName, startIndex, length)︰
startIndex是欲切割起始的index，開頭第一位是1，如果輸入0會無法辨別。
length是自起始index想要切的長度，一字一位元包含空白字元也要算。
以下將由範例一一示範解說︰
使用SUBSTRING_INDEX(culomnName, exp, number)
SELECT  username, SUBSTRING_INDEX(username, '.', 1)
FROM     user
結果︰辨別字元是'.'，輸入正數所以只出現左邊的所有字元

EXPLAIN 使用的方式：
只要在SELECT語法前面加上 EXPLAIN 指令即可。

以下為執行畫面範例



其中針對EXPLAIN的欄位說明如下：
table：關連到的資料表(Table)會顯示在此。
type：顯示使用了何種類型。從最優至最差的類型為const、eq_reg、ref、range、indexhe、ALL。
         System：system資料表，此為const連接類型的特殊情況。          
         Const：資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行，這個值就是
                      常數，因為MySQL會先讀這個值然後把它當做常數。
         eq_ref：MySQL在連接查詢時，會從最前面的資料表，對每一個記錄的聯合，從資料
                       表中讀取一個記錄，在查詢時會使用索引為主鍵或唯一鍵的全部。
         ref：只有在查詢使用了非唯一鍵或主鍵時才會發生。
         range：使用索引返回一個範圍的結果。例如：使用大於>或小於<查詢時發生。
         index：此為針對索引中的資料進行查詢。
         ALL：針對每一筆記錄進行完全掃描。
possible_keys：顯示可能使用到的索引。此為從WHERE語法中選擇一個適合的欄位名稱。
key：實際使用到的索引。如果為NULL，則是沒有使用索引。
key_len：使用索引的長度。長度越短 準確性越高。
ref：顯示那一列的索引被使用。一般是一個常數(const)。
rows：MySQL用來返回資料的筆數。
Extra：MySQL用來解析額外的查詢訊息。如果此欄位的值為：Using temporary和Using filesort，表示MySQL無法使用索引。
Extra為MySQL用來解析額外的查詢訊息，其中欄位值所代表的意義如下：
         Distinct：當MySQL找到相關連的資料時，就不再搜尋。
         Not exists：MySQL優化 LEFT JOIN，一旦找到符合的LEFT JOIN資料後，就不再搜
                            尋。
         Range checked for each Record(index map:#)：無法找到理想的索引。此為最慢的
                                                                                          使用索引。
         Using filesort：當出現這個值時，表示此SELECT語法需要優化。因為MySQL必須進
                                    行額外的步驟來進行查詢。
         Using index：返回的資料是從索引中資料，而不是從實際的資料中返回，當返回的資
                               料都出現在索引中的資料時就會發生此情況。
         Using temporary：同Using filesort，表示此SELECT語法需要進行優化。此為MySQL
                                       必須建立一個暫時的資料表(Table)來儲存結果，此情況會發生在針
                                       對不同的資料進行ORDER BY，而不是GROUP BY。
         Using where：使用WHERE語法中的欄位來返回結果。

(把字串轉數字排序)欄位varchar型態，用數字型態排序，使用CAST()函式


drop table table_a;
CREATE TABLE table_a (
id bigint(10) NOT NULL AUTO_INCREMENT,
number varchar(255)DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into table_a(number) VALUES(1),(10),(5),(3),(2),(11),(14),(12),(13);


SELECT * FROM table_a AS a ORDER BY CAST(a.number AS UNSIGNED);

SELECT * FROM table_a AS a ORDER BY CAST(a.number AS decimal(10,0))

ELT()從傳入的順位數值取出對應欄位的字串
ELT(N,str1,str2,str3,...)
N為傳入的順位數值
str1,str2,str3,...傳入的字串

若N=1，則回傳值為str1，
若N=2，則回傳值為str2，
以此類推。
若N小於1或大於參數的數目，
則回傳值為NULL。
ELT()是FIELD()的補數。

SELECT ELT(1,'live', 'love', 'like','llee');
'live'

SELECT ELT(4, 'live', 'love', 'like','llee');
'llee'

FIELD() 回傳字串在列表中的順位


FIELD(str,str1,str2,str3,...)
str傳入的字串
str1,str2是指傳入列表的字串

回傳值為字串str在str1, str2, str3,……列表中的順位。
在找不到str的情況下，回傳值為0。
如果所有對於FIELD()的參數均為字串，
則所有參數均按照字串進行比較。
如果所有的參數均為數字，則按照數字進行比較。
否則，參數按照double格式進行比較。
如果str為NULL，則回傳值為0，
原因是NULL不能同任何值進行同等比較。
FIELD()是ELT()的補數。
SELECT FIELD('live', 'live', 'love', 'like','llee');
1

SELECT FIELD('love', 'live', 'love', 'like','llee');
2

tinyint的取值方法

型態byte範圍tinyint
tinyint unsigned1byte-128~127
UNSIGNED： 0~255
tinyint1byte=8bit
1 111 1111 -> -1280 111 1111 ->  127