Mysql使用中的一些記錄

標籤：

1、Mysql系統時間自動擷取：

     列類型         顯示格式  
TIMESTAMP(14)  YYYYMMDDHHMMSS  
TIMESTAMP(12)  YYMMDDHHMMSS  
TIMESTAMP(10)  YYMMDDHHMM  
TIMESTAMP(8)   YYYYMMDD  
TIMESTAMP(6)   YYMMDD  
TIMESTAMP(4)   YYMM  
TIMESTAMP(2)   YY  


它會自動地用當前的日期和時間標記你的INSERT或UPDATE的操作。如果一張表中有多個TIMESTAMP列，只有第一個自動更新。

在下列情況下MySQL會自動更新第一個TIMESTAMP列為當前系統時間

1、當一個INSERT、UPDATE或LOAD DATA INFILE語句中沒有明確地指定其值時。 

（注意一個UPDATE設定一個列為它已經有的值，這將不引起TIMESTAMP列被更新，因為如果你設定一個列為它當前的值，MySQL為了效率而忽略更改。看附例） 

2、你明確地設定TIMESTAMP列為NULL. 

3、除第一個以外的TIMESTAMP列也可以設定到當前的日期和時間，只要將列設為NULL，或NOW()。

例：

CREATE TABLE test(
  Datetime timestamp(14) NOT NULL ,
)

2、Mysql刪除添加主鍵索引：

  Mysql的兩種主鍵。Primary key 和not null auto_incriment

（1）在建立mysql表時，給一個欄位添加了主鍵primary key沒有添加自增長時
在insert資料時可以不用insert主鍵，mysql會自動添加0，但是在第二次insert時沒有填寫值mysql資料庫還是預設添加0，會導致有重複的主鍵，這是不可以的。所有在定義了primary key時，在insert資料時要給主鍵填寫值。

（2）在建立mysql表時，給一個欄位添加了主鍵not null auto_increment;
這也是一個主鍵。時自增長的以1為開始。這個欄位是可以不用填寫值的，mysql資料庫會自動給填寫值，不會出現primary key的狀況。
Alter table tb add primary key(id);
Alter table tb change id id int(10) not null auto_increment=1;

（3）刪除自增長的主鍵id
先刪除自增長在刪除主鍵
Alter table tb change id id int(10);//刪除自增長
Alter table tb drop primary key;//刪除主建

（4）添加刪除索引

在執行CREATE TABLE語句時可以建立索引，也可以單獨用CREATE INDEX或ALTER TABLE來為表增加索引。

       1）添加索引

                  ALTER TABLE用來建立普通索引、UNIQUE索引或PRIMARY KEY索引

          ALTER TABLE table_name ADD INDEX index_name (column_list)

          ALTER TABLE table_name ADD UNIQUE (column_list)
 
          ALTER TABLE table_name ADD PRIMARY KEY (column_list)

         其中table_name是要增加索引的表名，column_list指出對哪些列進行索引，多列時各列之間用逗號分隔。索引名index_name可選，預設時，MySQL將根據第一個索引列賦一個名稱。另外，ALTER TABLE允許在單個語句中更改多個表，因此可以在同時建立多個索引。

         2）索引類型

               在建立索引時，可以規定索引能否包含重複值。如果不包含，則索引應該建立為PRIMARY KEY或UNIQUE索引。對於單列惟一性索引，這保證單列不包含重複的值。對於多列惟一性索引，保證多個值的組合不重複。

               PRIMARY KEY索引和UNIQUE索引非常類似。事實上，PRIMARY KEY索引僅是一個具有名稱PRIMARY的UNIQUE索引。這表示一個表只能包含一個PRIMARY KEY，因為一個表中不可能具有兩個同名的索引。

               下面的SQL語句對InodbTest表在No上添加PRIMARY KEY索引。

                 ALTER TABLE InodbTest ADD PRIMARY KEY (No)

        3）刪除索引

            可利用ALTER TABLE或DROP INDEX語句來刪除索引。類似於CREATE INDEX語句，DROP INDEX可以在ALTER TABLE內部作為一條語句處理，文法如下。

             DROP INDEX index_name ON talbe_name

             ALTER TABLE table_name DROP INDEX index_name

            ALTER TABLE table_name DROP PRIMARY KEY

            其中，前兩條語句是等價的，刪除掉table_name中的索引index_name。

             第3條語句只在刪除PRIMARY KEY索引時使用，因為一個表只可能有一個PRIMARY KEY索引，因此不需要指定索引名。如果沒有建立PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL將刪除第一個UNIQUE索引。

             如果從表中刪除了某列，則索引會受到影響。對於多列組合的索引，如果刪除其中的某列，則該列也會從索引中刪除。如果刪除群組成索引的所有列，則整個索引將被刪除。

       4）查看索引

        mysql> show index from tblname;

3、Mysql使用預存程序：

（1）       

           查看databases中的預存程序：

                    show procedure status;

           查看預存程序或函數的建立代碼

                  show create procedure proc_name;
                  show create function func_name;

（2）

建立一個預存程序在InodbTest表中插入30個資料：

PS:

DELIMITER  //  是用來改變結束鍵

concat(‘jk‘,ID)    是用來串連jk和數位，轉換為varchar型

DELIMITER //create procedure test_insert997()BEGINdeclare ID int;set ID=1;WHILE(ID<=30) do INSERT INTO InodbTest(name) VALUES(concat('jk',ID));  SET ID=ID+1;end while;END // 

（3）

一.建立預存程序

1.基本文法：create procedure sp_name()
begin
………
end

2.參數傳遞

二.調用預存程序

1.基本文法：call sp_name()
注意：預存程序名稱後面必須加括弧，哪怕該預存程序沒有參數傳遞

三.刪除預存程序

1.基本文法：
drop procedure sp_name//
2.注意事項
(1)不能在一個預存程序中刪除另一個預存程序，只能調用另一個預存程序

四.區塊，條件，迴圈

1.區塊定義，常用
begin
……
end;
也可以給區塊起別名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出區塊，執行區塊以後的代碼
2.條件陳述式if 條件 then
statement
else
statement
end if;

3.迴圈語句
(1).while迴圈

[label:] WHILE expression DO

statements

END WHILE [label] ;
(2).loop迴圈[label:] LOOP

statements

END LOOP [label];(3).repeat until迴圈[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

mysql預存程序中要用到的運算子

算術運算子

+     加   SET var1=2+2;       4
-     減   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3;  3
%     模數 SET var6=10%3 ;     1

比較子

>            大於 1>2 False
<            小於 2<1 False
<=           小於等於 2<=2 True
>=           大於等於 3>=2 True
BETWEEN      在兩值之間 5 BETWEEN 1 AND 10 True
NOT BETWEEN  不在兩值之間 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等於 2=3 False
<>, !=       不等於 2<>3 False
<=>          嚴格比較兩個NULL值是否相等 NULL<=>NULL True
LIKE         簡單模式比對 "Guy Harrison" LIKE "Guy%" True
REGEXP       正則式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      為空白 0 IS NULL False
IS NOT NULL  不為空白 0 IS NOT NULL True

mysq預存程序中常用的函數，字串類型操作，數學類，日期時間類

一.字串類 

CHARSET(str) //返回字串字元集
CONCAT (string2  [,... ]) //串連字串
INSTR (string ,substring ) //返回substring首次在string中出現的位置,不存在返回0
LCASE (string2 ) //轉換成小寫
LEFT (string2 ,length ) //從string2中的左邊起取length個字元
LENGTH (string ) //string長度
LOAD_FILE (file_name ) //從檔案讀取內容
LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定開始位置
LPAD (string2 ,length ,pad ) //重複用pad加在string開頭,直到字串長度為length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重複count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替換search_str
RPAD (string2 ,length ,pad) //在str後用pad補充,直到長度為length
RTRIM (string2 ) //去除後端空格
STRCMP (string1 ,string2 ) //逐字元比較兩字串大小,
SUBSTRING (str , position  [,length ]) //從str的position開始,取length個字元,
註：mysql中處理字串時，預設第一個字元下標為1，即參數position必須大於等於1mysql> select substring(’abcd’,0,2);
+———————–+
| substring(’abcd’,0,2) |
+———————–+
|                       |
+———————–+
1 row in set (0.00 sec)mysql> select substring(’abcd’,1,2);
+———————–+
| substring(’abcd’,1,2) |
+———————–+
| ab                    |
+———————–+
1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字元
UCASE (string2 ) //轉換成大寫
RIGHT(string2,length) //取string2最後length個字元
SPACE(count) //產生count個空格 

二.數學類

ABS (number2 ) //絕對值
BIN (decimal_number ) //十進位轉二進位
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //進位轉換
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小數位元
HEX (DecimalNumber ) //轉十六進位
註：HEX()中可傳入字串，則返回其ASC-11碼，如HEX(’DEF’)返回4142143
也可以傳入十進位整數，返回其十六進位編碼，如HEX(25)返回19
LEAST (number , number2  [,..]) //求最小值
MOD (numerator ,denominator ) //求餘
POWER (number ,power ) //求指數
RAND([seed]) //隨機數
ROUND (number  [,decimals ]) //四捨五入,decimals為小數位元]註：傳回型別並非均為整數，如：
(1)預設變為整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
|           1 |
+————-+
1 row in set (0.00 sec)

mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
|           2 |
+————-+
1 row in set (0.00 sec)

(2)可以設定小數位元，返回浮點型資料
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
|           1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符號,正負或0
SQRT(number2) //開平方

 
三.日期時間類

ADDTIME (date2 ,time_interval ) //將time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //轉換時區
CURRENT_DATE (  ) //當前日期
CURRENT_TIME (  ) //目前時間
CURRENT_TIMESTAMP (  ) //目前時間戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或時間
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式顯示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上減去一個時間
DATEDIFF (date1 ,date2 ) //兩個日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1為星期天
DAYOFYEAR (date ) //一年中的第幾天
EXTRACT (interval_name  FROM date ) //從date中提取日期的指定部分
MAKEDATE (year ,day ) //給出年及年中的第幾天,產生日期串
MAKETIME (hour ,minute ,second ) //產生時間串
MONTHNAME (date ) //英文月份名
NOW (  ) //目前時間
SEC_TO_TIME (seconds ) //秒數轉成時間
STR_TO_DATE (string ,format ) //字串轉成時間,以format格式顯示
TIMEDIFF (datetime1 ,datetime2 ) //兩個時間差
TIME_TO_SEC (time ) //時間轉秒數]
WEEK (date_time [,start_of_week ]) //第幾周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第幾天
HOUR(datetime) //小時
LAST_DAY(date) //date的月的最後日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分附:可用在INTERVAL中的類型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR 

Mysql使用中的一些記錄