搞定linux上MySQL編程(三)：MySQL進階文法，linuxmysql

搞定linux上MySQL編程(三)：MySQL進階文法，linuxmysql
【著作權聲明：尊重原創，轉載請保留出處：blog.csdn.net/shallnet，文章僅供學習交流，請勿用於商業用途】        本節介紹Mysql進階文法，包括試圖、預存程序、索引等，通過使用試圖、觸發器等來最佳化資料庫設計和查詢技術，提高資料處理能力與效率。         視圖操作：視圖是一個虛擬表，內容由查詢定義。視圖並不在資料庫中以儲存的資料值集形式存在，行和列資料來源於定義視圖的查詢所引用的表，且在引用試圖時動態產生。從資料庫系統外部來看，視圖就如同一張表一樣，對錶能夠進行的一般操作的哦可以應用於視圖，例如查詢、插入、修改、刪除等。
視圖是儲存在資料庫中的用於查詢的sql語句，使用視圖可使複雜的查詢易於理解和使用，且可以隱藏一些資料，保證安全。視圖一經定義便儲存在資料庫中，與其相對應的資料並沒有想表那樣又在資料庫中再儲存一份，通過視圖看到的資料只是存放在基本表中的資料。對 視圖所見資料修改或基本表資料發生變化時，都會反映到基本表或視圖中。
使用視圖有如下好處：1.視點集中，視圖集使使用者只關心感興趣的某些特定資料和他們所負責的特定任務。2.簡化操作，每次執行相同操作查詢時，不必重新寫這些複雜的查詢語句，只要一條簡單的查詢檢視語句即可。3. 定製資料，視圖可以讓不同使用者以不同方式看到不同的資料集，這在許多不同層級使用者共用同一資料庫時十分重要。4.合并分割資料。5.安全，通過視圖使用者只能查看和修改他們所能看到的資料，其他資料庫或表既不可見也不可以訪問。
建立視圖文法：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]    VIEW view_name [(column_list)]    AS select_statement    [WITH [CASCADED | LOCAL] CHECK OPTION]select_statement是一種SELECT語句,它給出了視圖的定義。該語句可從基表或其他視圖進行選擇。視圖屬於資料庫。在預設情況下,將在當前資料庫建立新視圖。要想在給定資料庫中明確建立視圖,建立時,應將名稱指定為db_name.view_name 。表和視圖共用資料庫中相同的名稱空間,因此,資料庫不能包含具有相同名稱的表和視圖。
例如建立包含id和最last_login的視圖：

mysql> create view db_users.v_idlogin as select userid, last_login as login_date  from tb_users where birth < '1980-1-1';Query OK, 0 rows affected (0.04 sec)mysql> show tables;+--------------------+| Tables_in_db_users |+--------------------+| tb_users           || v_idlogin          |+--------------------+2 rows in set (0.00 sec)mysql> select * from v_idlogin;+--------+------------+| userid | login_date |+--------+------------+|  10003 | 2014-09-20 ||  10004 | 2015-02-10 |+--------+------------+2 rows in set (0.02 sec)

刪除視圖如下：  

mysql> drop view if exists v_idlogin;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+--------------------+| Tables_in_db_users |+--------------------+| tb_users           |+--------------------+1 row in set (0.00 sec)

預存程序：        一個良好的資料庫都會用到預存程序。預存程序是一組為了完成特定功能的sql語句集，經過編譯後儲存在資料庫中。預存程序的實質就是部署在資料庫端的一組定義代碼以及SQL，將常用的或很複雜的工作預先用sql語句寫好並用一個指定的名稱儲存起來，那麼以後要讓資料庫提供與已定義好的預存程序的功能相同的服務時，只需調用即可自動完成命令。        預存程序增強了sql語言的功能和 靈活性。預存程序可以用流量控制語句編寫，完成複雜的運算，有很強的靈活性。 預存程序保證了資料的安全性和完整性，通過預存程序可以使沒有許可權的使用者在控制之下間接地存取資料庫，從而保證資料的安全。預存程序文法：CREATE PROCEDURE sp_name ([ proc_parameter[,...]])      [ characteristic ...]  routine_body句建立儲存子程式。要在MySQL 5.1 中建立子程式，必須具有CREATE ROUTINE許可權，並且ALTER  ROUTINE和EXECUTE許可權被自動授予它的建立者。如果二進位日誌功能被允許，你也可能需要SUPER 許可權，預設地，子程式與當前資料庫關聯。要明確地把子程式與一個給定資料庫關聯起來，可以在建立子程式的時候指定其名字為db_name.sp_name。
儲存子程式可以使用BEGIN ... END複合陳述式來包含多個語句。[ begin_label :] BEGIN      [ statement_list]END [end_label]statement_list 代表一個或多個語句的列表。statement_list之內每個語句都必須用分號（；）來結尾。下面建立一個簡單的預存程序：

mysql>mysql> delimiter $mysql> create procedure get_one_user(    -> in id int)    -> begin    -> select * from tb_users where userid>id;    -> end;    -> $Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

調用預存程序使用call語句：

mysql> call get_one_user(10005)    -> ;+--------+----------+------------+------------+| userid | username | birth      | last_login |+--------+----------+------------+------------+|  10007 | Hamilton | 1988-07-07 | 2014-11-16 ||  10008 | Johnson  | 1986-06-07 | 2015-01-23 ||  10009 | James    | 1989-08-17 | 2013-12-23 ||  10010 | james    | 1984-12-30 | 2014-12-23 ||  10011 | Gay      | 1987-02-03 | 2014-12-23 ||  10012 | Kaman    | 1981-04-04 | 2010-01-13 ||  10006 | Wade     | 1982-03-04 | 2012-05-19 |+--------+----------+------------+------------+7 rows in set (0.04 sec)Query OK, 0 rows affected (0.04 sec)

索引：        索引是對資料庫表中一列或多列的值進行排序的一種結構，是某個表中一列或多列值得集合和相應的指標向表中物理標識這些值得資料頁的邏輯指標清單。        建立索引可以大大提高系統效能，若果沒有索引，執行查詢是mysql必須從第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄，表裡面記錄越多，這個操作的代價就越高。但如果作為搜尋條件的列上已經建立了索引，mysql無需掃描任何記錄即可迅速地得到目標記錄所在位置。        建立索引文法為：create  [unicode | fulltext | spatial] index index_name[using index_type]on tbl_name (index_col_name, ...)mysql提供以下幾種索引類型選擇：普通索引（create index）、唯一性索引（create unique index）、主鍵（primary key）、全文索引（fulltext）。其中普通索引、唯一性索引和主鍵也可以在建立或修改表時指定。以唯一性索引為例，直接建立唯一索引文法如下：

mysql> select * from tb_users;+--------+----------+------------+------------+| userid | username | birth      | last_login |+--------+----------+------------+------------+|  10000 | Allen    | 1981-01-01 | 2014-02-02 ||  10001 | Ben      | 1982-04-02 | 2014-04-30 ||  10002 | Curry    | 1985-08-12 | 2014-01-17 ||  10003 | Davis    | 1978-07-12 | 2014-09-20 ||  10004 | Ellis    | 1979-09-02 | 2015-02-10 ||  10005 | Faried   | 1984-02-05 | 2014-12-01 ||  10007 | Hamilton | 1988-07-07 | 2014-11-16 ||  10008 | Johnson  | 1986-06-07 | 2015-01-23 ||  10009 | Jackson  | 1989-08-17 | 2013-12-23 ||  10010 | James    | 1984-12-30 | 2014-12-23 ||  10011 | Gay      | 1987-02-03 | 2014-12-23 ||  10012 | Kaman    | 1981-04-04 | 2010-01-13 ||  10006 | Wade     | 1982-03-04 | 2012-05-19 |+--------+----------+------------+------------+13 rows in set (0.00 sec)mysql> create unique index idx_name on tb_users(username(5));Query OK, 13 rows affected (0.13 sec)Records: 13  Duplicates: 0  Warnings: 0mysql> 

主鍵是一種特殊的唯一索引，不允許有空值，一般在建立表時同時建立主鍵索引。一個表只能有一個主鍵。
一般來說應該在這些列上建立索引：經常需要搜尋的列（加快搜尋速度）、經常用於串連的列（加快連線速度）、經常需要根據範圍進行搜尋的列、經常需要排序的列、經常使用在where子句中的列上。