Mysql常用命令、簡單查詢、遊標、預存程序、觸發器簡單介紹

一、Mysql資料庫基本常用的命令
1、服務啟動與停止
net stop mysql
net start mysql

2、登陸mysql
  mysql -uroot -p斷行符號 輸入密碼
  注意，如果是串連到另外的機器上，則需要加入一個參數-h機器IP110.110.110.110，使用者名稱為root,密碼為abcd123。則鍵入以下命令：
  mysql -h110.110.110.110 -uroot -pabcd123
 

mysql -u root -p/mysql -h localhost -u root -p databaseName;

3、 顯示資料庫列表
  show databases;預設有兩個資料庫：mysql和test。 mysql庫存放著mysql的系統和使用者權限資訊，我們改密碼和新增使用者，實際上就是對這個庫進行操作

4、選擇資料庫和顯示資料表
   use mysql;
   show tables;

5、顯示表結構
   desc tablename

6、建庫、刪庫
    create database dbName；
    drop database   dbName；

7、基本的a建表、b插入資訊、c增加欄位、d修改欄位名、e修改欄位資料類型、f更新資料
   a.create table emp(id int not null auto_increment primary key,name varchar(50));
   b.insert into tableName values ("hyq","M");
   c.alter  table tableName add columnName type;
   d.alter table tableName1 change tableName2 char(10) not null;
   e.alter table tableName modify columnName  type
   f.update tableName set age="25" where id="";
   
8、清空表記錄與刪除表
   delete from  tableName
   drop table tableName;

9、匯出匯入資料
   匯出資料和資料結構：輸入：mysqldump -u [資料庫使用者名稱] -p [要備份的資料庫名稱]>[備份檔案的儲存路徑]
   例子：mysqldump -u root -p test>E:\tt.sql
   匯入：mysql -u root -p<[備份檔案的儲存路徑]
    恢複備份檔案：
    進入MYSQL Command Line Client
    先建立資料庫：create database test 註：test是建立資料庫的名稱
    再切換到當前資料庫：use test
    再輸入：\. d:/test.sql 或 souce E:\tt.sql

10、退出mysql   exit 或快速鍵：Ctrl+C；

二、Mysql查詢

1、幾個常用的函數
   a.count();統計
   b.sum();求和
   c.avg（）求平均
   d.max、min 最大和最小

2、幾個進階查詢運算
   a.UNION 運算子通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重複行而派生出一個結果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重複行。
   b.EXCEPT運算子  通過包括所有在 TABLE1 中但不在 TABLE2 中的行並消除所有重複行而派生出一個結果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重複行
   c.INTERSECT 運算子通過只包括 TABLE1 和 TABLE2 中都有的行並消除所有重複行而派生出一個結果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重複
 （使用以上運算子的幾個查詢結果必須一致）

3、幾個常用的串連查詢
   a.left outer join：左外串連（左串連）：結果集幾包括串連表的匹配行，也包括左串連表的所有行。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c；
   b.右外串連(右串連)：結果集既包括串連表的匹配串連行，也包括右串連表的所有行。
   c. full outer join：全外串連：不僅包括符號串連表的匹配行，還包括兩個串連表中的所有記錄。
   d.inner join：等值串連 只返回兩個表中連接欄位相等的行。

三、Mysql視圖
   
1、概念：是一種虛表--不能在存放資料,只能使用select語句引用已存在表(一個或多個)的資料是一種代理模式的實現；

2、作用：使查詢更清晰，視圖存放我們所需要的資料，簡化操作；讓資料更安全，視圖中的資料，不存在視圖中，還是在基本表裡面，通過視圖這層關係，我們可以有效保護我們的重要資料

3、MySql檢視類型：mysql的視圖有三種類型：MERGE、TEMPTABLE、UNDEFINED。如果沒有ALGORITHM子句，預設演算法是UNDEFINED（未定義的）。演算法會影響MySQL處理視圖的方式。
  a.MERGE，會將引用視圖的語句的文本與視圖定義合并起來，使得視圖定義的某一部分取代語句的對應部分。
  b.TEMPTABLE，視圖的結果將被置於暫存資料表中，然後使用它執行語句。
  c.UNDEFINED，MySQL將選擇所要使用的演算法。如果可能，它傾向於MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了暫存資料表，視圖是不可更新的。

4、基本建立：a.CREATE VIEW db_name.view_name AS SELECT * FROM t;（表和視圖共用資料庫中相同的名稱空間，同一資料庫中view名不能與表明相同。）
             b.CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
              VIEW view_name [(column_list)]
              AS select_statement
             [WITH [CASCADED | LOCAL] CHECK OPTION]

四、Mysql索引
   a.索引是雙刃劍   EXPLAIN SELECT * from tablename   加上explain後用於描述MySQL如何執行查詢操作、以及MySQL成功返回結果集需要執行的行數

五、預存程序（Mysql5之後才支援預存程序）
   1、概念：一組為了完成特殊功能的sql語句集，編譯後放在資料庫中，通過過程名調用執行；
   2、優點：a.增強sql靈活性與功能，能完成複雜的判斷與運算；
            b.執行速度快，能有效節省流量（只傳調用語句）；
            c.資料安全起很大作用.
   3、一個簡單的例子
      delimiter//
      create procedure pro_test(OUT s int)
      begin
      select count(*) total INTO s from user;
      end
      //
      delimiter;
解釋：以上建立了一個預存程序，有一個輸出參數 s，將統計出來的記錄數賦值給s；
      a.delimiter，分割符意思，MySQL預設以";"為分隔字元，如果我們沒有聲明分割符，那麼編譯器會把預存程序當成SQL語句進行處理，
      則預存程序的編譯過程會報錯，所以要事先用DELIMITER關鍵字申明當前段分隔字元。
      b.三種參數類型：IN 輸入參數:表示該參數的值必須在調用預存程序時指定，在預存程序中修改該參數的值不能被返回，為預設值；
                      OUT 輸出參數:該值可在預存程序內部被改變，並可返回；
                      INOUT 輸入輸出參數:調用時指定，並且可被改變和返回；
      c.變數的聲明：declare age int default 18；
      d.變數的賦值：set 變數名=運算式值   
      e.使用者變數： select “hello Kitty” into @X 、 聲明後 select @X   的結果為 “hello Kitty”使用者變數名一般以@開頭濫用使用者變數會導致程式難以理解及管理
  4、Mysql預存程序的調用
     call預存程序名（）；
  5、預存程序的查詢
      select name frommysql.proc where db='dbName';
      show procedure status where db='dbName';
      SHOW CREATE PROCEDURE
 
  6、預存程序的修改、刪除
     alter procedure;
     drop procedure;

六、觸發器
   1、概念：當執行delete、update或insert操作時，可以使用觸發器來觸發某些操作。
   2、建立：CREATE TRIGGER trigger_name trigger_time trigger_event   ON tbl_name FOR EACH ROW trigger_stmt
   3、解釋：其中 trigger_name是觸發器名，
            trigger_time:BEFORE,AFTER
            trigger_event:INSERT、UPDATE、DELETE
            tbl_name:關聯的表名
            注意，INSERT除了插入操作，load data也能啟用該事件。對於同一trigger_event,不能有兩個相同trigger_time的觸發器。
            trigger_stmt：觸發器被啟用時執行的語句，可以使用單條語句，也可以使用BEGIN——END這樣的複合陳述式。
   
  4、舉例：mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
 
      mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
 
      -> FOR EACH ROW SET @sum = @sum + NEW.amount;
 
     mysql> SET @sum = 0;
     mysql> INSERT INTO account values(5,12.5);
      mysql> SELECT @num;
 
在該例子中，關鍵字NEW.col_name在INSERT觸發程式中引用；
另外一個關鍵字OLD.col_name可用於DELETE中
NEW和OLD均可用於UPDATE觸發程式中。
old命令的列為唯讀，new命名的列，如果具有select許可權，可引用它，如果在before出發程式中，具有update許可權，可使用set new.col_name = value的方法，在插入前更改值

七、遊標
  1、定義： DECLARE cursor_name CURSOR FOR SELECT_statement;    
  2、開啟遊標：open cursor_name;
  3、FETCH：擷取遊標當前指標的記錄，並傳給指定變數列表,注意變數數必須與MySQL遊標返回的欄位數一致,要獲得多行資料，使用迴圈語句去執行FETCH
      FETCH cursor_name INTO variable list;
  4、關閉遊標：close cursor_name；MySQL的遊標是向前唯讀,也就是說，你只能順序地從開始往後讀取結果集，不能從後往前，也不能直接跳到中間的記錄.
  5、樣本：
      -- 定義本地變數
    DECLARE o varchar(128);

   -- 定義遊標
   DECLARE ordernumbers CURSOR
   FOR
   SELECT callee_name FROM account_tbl where acct_timeduration=10800;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
   SET no_more_departments=0;
   
   -- 開啟遊標
   OPEN ordernumbers;

   -- 迴圈所有的行
   REPEAT
       -- Get order number
      FETCH ordernumbers INTO o;
      update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@o;
     
     
  -- 迴圈結束
      UNTIL no_more_departments
     END REPEAT;
   -- 關閉遊標
   CLOSE ordernumbers;