搞定linux上MySQL编程 （三）：MySQL高级语法

【版权声明：尊重原创，转载请保留出处：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: 0

mysql> 

主键是一种特殊的唯一索引，不允许有空值，一般在创建表时同时创建主键索引。一个表只能有一个主键。

一般来说应该在这些列上创建索引：

经常需要搜索的列（加快搜索速度）、经常用于连接的列（加快连接速度）、经常需要根据范围进行搜索的列、经常需要排序的列、经常使用在where子句中的列上。