专业的linux技术站点
Controlled by you.

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=InnoDBAUTO_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>

更新视图

通常,视图是可更新的(通过INSERTUPDATEDELETE)。

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

  • 分组(使用GROUP BYHAVING)
  • 联结;
  • 子查询;
  • 并;
  • 聚集函数(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,如果存储过程接受参数,它们将在()中列举出来。
BEGINEND语句用来限定存储过程体,过程本身仅是一个简单的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表示对存储过程传入和传出。
存储过程的代码位于BEGINEND语句内,它们是一系列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>

下面使用INOUT参数,使得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,用于返回输出。selectwhere语句使用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语句(或位于BEGINEND之间的一组语句):

  • DELETE
  • INSERT
  • UPDATE

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

一、创建触发器(CREATE TRIGGER)

在创建触发器时,需要给出4条信息:

1)唯一的触发器名;

2)触发器关联的表;

3)触发器应该相应的活动(DELETEINSERTUPDATE

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

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

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

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

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

创建有多个执行语句的触发器,一般有满足条件的删除操作,就会执行BEGINEND语句:

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返回事务开始之前的状态。
注意:事务处理只能用来管理INSERTUPDATEDELETE语句

二、使用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;

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

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

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

set autocommit=0;

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

打赏作者

您的支持将鼓励我们继续创作!

[微信] 扫描二维码打赏

[支付宝] 扫描二维码打赏

正在跳转到PayPal...
转载请注明出处:北慕城南's Blog » MySQL高级用法总结-

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址