JAVASCRIP 取消選取效果

取消選取的效果

onselectstart="return false;"
onmousedown="if (typeof event.preventDefault != 'undefined') {
event.preventDefault();}"

javascript windows onload event

var onload_before = window.onload || function () {};
window.onload = function ()
{
  onload_before();
// next..
}

MySQL 字串合併、字串連接 語法 concat substring

篩選或是更新資料的時候，常有需要用到補上字串，或是合併欄位的情況。

這時候使用update table set a=a+b 是不行的，要改用concat ，另外可以用SUBSTRING 來作截字的動作

例如

update table set a=concat(a,b);

update table set a=concat(a,'文字');

SELECT SUBSTRING('Quadratically',5);

 

參考資料如下

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

CONCAT() returns NULL if any argument is NULL.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring lenstr, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. characters long from string

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'        
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'        
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

MySQL 常用維護指令整理

1.更改預設排序方式:
ALTER TABLE `tablename` ORDER BY `id`
也可以是多個排序值

2.修復資料表:
REPAIR TABLE `tablename`

3.重新讀取權限FLUSH PRIVILEGES ;

MySQL 子查詢(sub query) limit 的用法

select * from (select * from table order by a limit 100 ) as newtable order by newtable.b

newtable 是一個別名，取什麼都可以
原因是：
error(#1248 Every derived table must have its own alias)

MySQL 選取特定主鍵跟其下一筆之外的資料

這是來自ptt上的問題，最後將結論摘錄如下：

 SELECT A.id FROM news AS A LEFT JOIN
     (SELECT id FROM news WHERE id >=
        (select IFNULL(max(id),10) from news where id <10 )
      ORDER BY id LIMIT 3) AS B
   ON A.id = B.id WHERE B.id IS NULL

MySQL 更新 KEY 欄位 成流水號的方式

-- 防止序號重複
update `tablename ` set id=id+10000;
SET @ID=0;
UPDATE `tablename ` SET id=(@ID:=@ID+1);
--如果有採用auto_increment 記得去修改回來

ALTER TABLE `tablename ` AUTO_INCREMENT =9999999(自訂)