MySQL高级用法总结- 包含表操纵、视图、存储过程、触发器、事务处理等

1.创建和操纵表

利用CREATE TABLE创建表，必须给出下列信息：

• 新表的名字，在关键字CREATE TABLE之后给出；

• 表列的名字和定义，用逗号分隔。

示例：

mysql> create table customers
    -> (
    -> cust_id int NOT NULL AUTO_INCREMENT,
    -> cust_name char(50) NOT NULL,
    -> cust_address char(50) NUll,
    -> cust_city char(50) NULL,
    -> PRIMARY KEY(cust_id)
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

mysql>

主键(PRIMARY KEY):
表的主键可以在创建时用PRIMARY KEY关键字指定，这里的主键列是cust_id,如果要指定多个主键，逗号分隔即可，比如PRIMARY KEY(cust_id,cust_name)
（先忽略ENGINE=InnoDB和AUTO_INCREMENT,后面会对它们进行介绍。）
注意：主键中只能使用不允许NULL值的列

如果创建表时，表名存在则会报错，需要手工删除该表，然后重建。如果仅仅想在一个表不存在时创建它，在表名后面给出IF NOT EXISTS即可。

NULL值：

null值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值
每个表列或者是NULL列，或者NOT NULL列，这种状态在创建表时有表的定义规定

示例：

create table orders
(
    order_num    int     NOT NULL AUTO_INCREMENT,
    order_date    datetime    NOT NULL,
    cust_id            int             NOT NULL,
    PRIMARY KEY(order_num)
)    ENGINE=InnoDb

AUTO_INCREMENT：

auto_increment告诉mysql，本列每当增加一行时自动增量。每次执行一个insert操作时，mysql自动对该列增量。每个表只允许一个auto_increment列，而且必须被索引（如，通过使它成为主键）

覆盖auto_increment：当然也可以自己指定值，但是这个值要唯一，该值用来替代auto_increment自动生成的值，后续的增量将开始使用该手工插入的值。

获得auto_increment列最后生成的值，使用last_insert_id()函数获取：

select last_insert_id();

(此语句返回最后一个auto_increment值)

指定默认值：

如果在插入行时(insert操作)没有给出值，使用default关键字指定：

mysql> create table demo
    -> (order_num int NOT NULL,
    -> order_item int NOT NULL,
    -> prod_id   char(10) NOT NULL,
    -> quantity  int    NOT NULL DEFAULT 1,
    -> item_price decimal(8,2) NOT NULL,
    -> PRIMARY KEY(order_num,order_item)
    -> ) ENGINE=InooDB;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> insert  into demo(order_num,order_item,prod_id,item_price) values('10001','0001','apple','25');
Query OK, 1 row affected (0.00 sec)

mysql> select * from demo;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     10001 |          1 | apple   |        1 |      25.00 |
+-----------+------------+---------+----------+------------+
1 row in set (0.00 sec)

mysql>

注意：与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

2.引擎类型

发型多种引擎的原因：因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。
当然也可以完全忽略这些数据库引擎，如果省略ENGINE=语句，则使用默认引擎(很可能是MyISAM)

以下是几个需要知道的引擎：

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

外键不能跨引擎：

混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性，如第1章所述）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

3.更新表(ALTER TABLE)

• 示例，给表添加一个列：

  mysql> alter table vendors add vend_phone char(20);
  Query OK, 6 rows affected (0.06 sec)
  Records: 6  Duplicates: 0  Warnings: 0
  mysql>
  (必须明确数据类型)

• 删除刚刚添加的列：

  mysql> alter table vendors drop column vend_phone;
  Query OK, 6 rows affected (0.03 sec)
  Records: 6  Duplicates: 0  Warnings: 0
  mysql>

定义外键：

alter table orderitems add constraint fk_orderitems_orders foreign key (order_num) references orders (order_num);

4.删除表(DROP TABLE)

示例：

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql>

5.重命名表(RENAME TABLE [TABLE] TO [TABLE2];

• 示例，把demo表重命名为demo2:

  mysql> rename table demo to demo1;
  Query OK, 0 rows affected (0.00 sec)
  mysql>

对多个表重命名用逗号隔开即可：

mysql> rename table backup_customers to customers,backup_vendors to vendors;

6.使用视图(VIEW)

创建视图的规则：

• 视图用CREATE VIEW语句来创建。
• 使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
• 用DROP删除视图，其语法为DROP VIEW viewname;
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

示例，前面提到的联结查询：

mysql> select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;
+----------------+--------------+---------+
| cust_name      | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc.    | Y Lee        | ANV01   |
| Coyote Inc.    | Y Lee        | ANV02   |
| Coyote Inc.    | Y Lee        | TNT2    |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | OL1     |
| Coyote Inc.    | Y Lee        | SLING   |
| Coyote Inc.    | Y Lee        | ANV03   |
| Wascals        | Jim Jones    | JP2000  |
| Yosemite Place | Y Sam        | TNT2    |
| test           | E Fudd       | FC      |
+----------------+--------------+---------+
11 rows in set (0.00 sec)

mysql>

使用视图简化：

mysql> create view productscustomers as select cust_name,cust_contact,prod_id from customers,orders,orderitems where customers.cust_id=orders.cust_id and orderitems.order_num=orders.order_num;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from productscustomers;
+----------------+--------------+---------+
| cust_name      | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc.    | Y Lee        | ANV01   |
| Coyote Inc.    | Y Lee        | ANV02   |
| Coyote Inc.    | Y Lee        | TNT2    |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | OL1     |
| Coyote Inc.    | Y Lee        | SLING   |
| Coyote Inc.    | Y Lee        | ANV03   |
| Wascals        | Jim Jones    | JP2000  |
| Yosemite Place | Y Sam        | TNT2    |
| test           | E Fudd       | FC      |
+----------------+--------------+---------+
11 rows in set (0.00 sec)

mysql> select cust_name,cust_contact from productscustomers where prod_id='tnt2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

mysql>

可以看出，视图极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

用视图重新格式化检索出的数据

如上所述，视图另一常见用途是重新格式化检索出的数据

示例：

mysql> select Concat(rtrim(vend_name),'(',trim(vend_country),')') as vend_title from vendors order by vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

mysql>
现在，假如经常需要这个格式的结果，不必在每次需要的时候执行联结，创建一个视图，每次需要时使用它即可：

mysql> create view vendorlocations as select Concat(rtrim(vend_name),'(',trim(vend_country),')') as vend_title from vendors order by vend_name;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from vendorlocations;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.00 sec)

mysql>

使用视图与计算字段

检索某个特定订单中的物品，计算每种物品的总价格：

mysql> select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where prod_id='tnt2';
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| TNT2    |        5 |      10.00 |          50.00 |
| TNT2    |      100 |      10.00 |        1000.00 |
+---------+----------+------------+----------------+
2 rows in set (0.00 sec)

mysql> create view orderitemsexpanded as select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from orderitemsexpanded;
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANV01   |       10 |       5.99 |          59.90 |
| ANV02   |        3 |       9.99 |          29.97 |
| TNT2    |        5 |      10.00 |          50.00 |
| FB      |        1 |      10.00 |          10.00 |
| JP2000  |        1 |      55.00 |          55.00 |
| TNT2    |      100 |      10.00 |        1000.00 |
| FC      |       50 |       2.50 |         125.00 |
| FB      |        1 |      10.00 |          10.00 |
| OL1     |        1 |       8.99 |           8.99 |
| SLING   |        1 |       4.49 |           4.49 |
| ANV03   |        1 |      14.99 |          14.99 |
+---------+----------+------------+----------------+
11 rows in set (0.00 sec)

mysql> select * from orderitemsexpanded where prod_id='tnt2';
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| TNT2    |        5 |      10.00 |          50.00 |
| TNT2    |      100 |      10.00 |        1000.00 |
+---------+----------+------------+----------------+
2 rows in set (0.00 sec)

mysql>

更新视图

通常，视图是可更新的(通过INSERT、UPDATE、DELETE)。

并非所有视图都是可更新的，如果视图定义中有以下操作，则不能更新：

• 分组(使用GROUP BY和HAVING)
• 联结；
• 子查询；
• 并;
• 聚集函数(Min()、Connt()、Sum()等);
• DISTINCT；
• 导出（计算）列；

一般来说，应该将视图用于检索(SELECT语句)，而不用于更新(INSERT、UPDATE、DELETE）

总结：
视图为虚拟的表，它们包含的不是数据而是根据需要检索数据的查询。删除视图使用DROP VIEW viewname;

7.存储过程(CREATE PROCEDURE)

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

使用存储过程的理由：
①通过把处理封装在容易使用的单元中，简化复杂的操作（正如前面例子所述）。
②由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
③简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化
④提高性能。因为使用存储过程比使用单独的SQL语句要快。
⑤存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码（在下一章的例子中可以看到。）换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。
显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知
道它的一些缺陷。
⑥一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

一、执行存储过程(CALL)

MySQL称存储过程的执行为调用，因此用到CALL语句。CALL接受存储过程的名字以及需要传递给它的任意参数

示例：

call productpricing(@pricelow,@pricehigh,@priceaverage);

解析：执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。
存储过程可显示结果也可以不显示

二、创建存储过程(CREATE PROCEDURE xxx() BEGIN xxx END)

示例：

mysql> delimiter //
mysql> create procedure productpricing() begin select avg(prod_price) as priceavg from products; end//
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> call productpricing();
+-----------+
| priceavg  |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

解析：此存储过程名为productpricing，如果存储过程接受参数，它们将在()中列举出来。
BEGIN和END语句用来限定存储过程体，过程本身仅是一个简单的SELECT语句。
CALL使用(调用)存储过程，因为存储过程实际上可以理解为一种函数，所以存储名后面要接()符号

注意：因为是mysql的命令行模式，默认的mysql语句需要使用分隔符;来结束语句，但因为创建存储过程的时候需要使用; 但它的作用不是分隔符，因此上述使用delimiter //指定分隔为//(除了\符号外，任何字符都可以用作语句分隔符)

如果是使用mysql客户机执行，使用如下语句即可：

create procedure productpricing() begin select avg(prod_price) as priceavg from products; end;

三、删除存储过程(DROP PROCEDURE)

示例，删除我们刚刚创建的productpricing存储过程：

mysql> drop procedure productpricing;
Query OK, 0 rows affected (0.00 sec)

mysql>

注意存储过程名后面没有()符号
当存在时删除，使用IF EXISTS关键词，当存储过程不存在时就不会产生错误，示例：

mysql> drop procedure productpricing;
ERROR 1305 (42000): PROCEDURE course.productpricing does not exist

mysql> drop procedure if exists productpricing;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

四、使用参数
productpricing只是一个简单的存储过程，他显示select语句的结果。一般来说。存储过程并不显示结果，而是把结果返回给你指定的变量。

示例：

mysql> delimiter @@
mysql> create procedure productpricing(out pl decimal(8,2),out ph decimal(8,2),out pa decimal(8,2)) BEGIN select Min(prod_price) into pl from products;select max(prod_price) into ph from products;select avg(prod_price) into pa from products;end@@
Query OK, 0 rows affected (0.00 sec)

mysql>

解析：
此存储过程接受三个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品最低价格。每个参数必须有指定的数据类型，这里使用十进制值。关键字OUT指出该参数用来从存储过程传出一个值（返回给调用者）。
关键词IN表示传递给传递给存储过程，INOUT表示对存储过程传入和传出。
存储过程的代码位于BEGIN和END语句内，它们是一系列SELECT语句，检索值保存到相应的变量(通过INTO关键字)

注意：不能用一个参数返回多个行和列，所以上述语句用了三个参数和三个SELECT语句。

调用此存储过程，必须指定三个变量名：

mysql> delimiter ;
mysql> call productpricing(@pricelow,@pricehigh,@priceaverage);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

变量名：所有MYSQL变量必须以@开始。

在调用时，这条语句把三个变量对应赋值，所以并不显示任何数据，它赋值以后才可以显示变量的值。

执行完上述语句后，显示检索出的产品平均价格：

mysql> select @priceaverage;
+---------------+
| @priceaverage |
+---------------+
|         16.13 |
+---------------+
1 row in set (0.00 sec)

mysql> select @pricelow,@pricehigh,@priceaverage;
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
1 row in set (0.00 sec)

mysql>

下面使用IN和OUT参数，使得ordertotal存储过程接受订单号并返回该订单的合计：

mysql> create procedure ordertotal(in onumber DECIMAL(8,2),OUT ototal DECIMAL(8,2)) begin select sum(item_price*quantity) from orderitems where order_num=onumber INTO ototal;end@@
Query OK, 0 rows affected (0.00 sec)

mysql>

解析：onumber定义为IN，把onumber传入到存储过程。ototal定义为OUT，用于返回输出。select的where语句使用onumber变量过滤用户需要的行，INTO把值传给ototal变量

• 调用这个新存储过程：

mysql> delimiter ;
mysql> call ordertotal(20005,@total);
Query OK, 0 rows affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)

mysql> call ordertotal(20009,@total);
Query OK, 0 rows affected (0.00 sec)

mysql> select @total;
+--------+
| @total |
+--------+
|  38.47 |
+--------+
1 row in set (0.00 sec)

mysql>

五、检查存储过程(show create procedure😉

显示创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句：

show create procedure ordertotal;

显示创建时间、创建人等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS show procedure status列出所有存储过程，可以使用LIKE指定过滤条件：

show procedure status like 'ordertotal%';

8.触发器

想要某条语句(或某些语句)在事件发生时自动执行，需要用到触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）：

• DELETE
• INSERT
• UPDATE

其他MySQL语句不支持触发器。

一、创建触发器(CREATE TRIGGER)

在创建触发器时，需要给出4条信息：

1)唯一的触发器名；

2)触发器关联的表；

3)触发器应该相应的活动（DELETE、INSERT或UPDATE）

4)触发器何时执行（处理之间或之后）

保持每个数据库的触发器名唯一：在MYSQL5中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，所以同一数据库两个表可以具有相同的触发器名，但在其他DBMS中是不允许的，因此，最好是在数据库范围内使用唯一的触发器名。

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联，所以如果需要对INSERT和UPDATE操作执行触发器，应该定义两个触发器。

示例：创建一个名为newtrig的触发器，一旦orders表中有插入动作，就会自动往time表里插入当前时间：

create trigger newtrig after insert on orders for each row insert into time values(now());

创建有多个执行语句的触发器，一般有满足条件的删除操作，就会执行BEGIN和END语句：

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

触发器失败：如果BEFORE触发器失败，则MYSQL将不执行请求操作。此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器。

二、删除触发器(DROP TRIGGER)

示例，删除newproduct触发器：

drop trigger newproduct;

当然也可以使用drop trigger if exists删除，当触发器存在时则删除。
触发器不能更新或覆盖、为了修改一个触发器，必须先删除，然后重建。

三、触发器类别

• INSERT触发器
• DELETE触发器
• UPDATE触发器

查看触发器(mysql版本>=5.6)：

show trigger\G;

将显示所有触发器基本信息；无法查询指定触发器。

注意：mysql触发器不支持call语句，这表示不能从触发器内调用存储过程

9.事务处理(TRANSACTION)

事务处理需要知道的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符，你可以对它发布回退。

控制事务处理

使用下列语句来标识事务的开始：

start transaction;

一、使用ROLLBACK
MySQL的ROLLBACK命令用来回退（撤销）MySQL语句，示例：

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

解析：start transaction标识事务开始的位置，rollback返回事务开始之前的状态。
注意：事务处理只能用来管理INSERT、UPDATE和DELETE语句

二、使用COMMIT

示例：

start transaction;
delete from orderitems where order_num=20010;
delete from orders where order_num=20010;
commit;

解析：从系统中完全删除订单20010，因为涉及更新两个数据表，所以使用事务处理块来保证订单不被部分删除。最后COMMIT语句仅在不出错时写入更改。如果第一条DELETE语句起作用，第二条失败，则所有DELETE不会提交(被自动撤销)。

三、使用保留点
保留点用来回退到某个占位符。

• 创建保留点：

  savepoint delete1;

• 回退到保留点：

  rollback to delete1;

  保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放，也可以使用RELEASE SAVEPONINT明确地释放保留点。

四、更改默认的提交行为(SET autocommit=[0/1])
默认的MySQL行为是自动提交所有更改，即每执行一条MySQL语句，该语句立即生效。

为了指示MySQL不自动提交更改，需要使用以下语句，把autocommit系统变量设置为假：

set autocommit=0;

不管有没有COMMIT语句，设置autocommit为0(假)指示MySQL不自动提交更改，知道autocommit被设置为真为止；