澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

MySQL基础知识09触发器,UNPIVOT行列转变

2019-08-10 作者:数据库网络   |   浏览(200)

 

深信不疑大家都精晓怎么使用数据操作语言(DML)对SQL Server表的数码开展扦插、删除和更新等拍卖。有时候,大家供给用INSERT语句进行插队的多少实际上是多得令人头痛,有多数思想但麻烦的法门可以用 来插入大批量数目,然而SQL Server 2010提供了一种能够简化数据插入过程的新章程。本文将为我们简介那么些用来插入数据的艺术之间的出入,在那之中囊括SQL Server 二零零六提供的新情势——行值构造器(Row Value Constructor)。

本文首要内容如下:

SQL Server2007引进了广大投其所好开垦者口味的新特点,即使更动相当的小,却大大了削减了开辟者的职业量,这种替用户着想的开支思路,值得赞扬。

在SQL Server3000中,要兑现行反革命列转变,需求综合应用聚合函数和动态SQL,完毕起来要求自然的技能,所以在CSDN的SQL探讨区里能够看出大量打探行列转变怎么着完成的难点。到了二零零六中,使用新引入的根本字PIVOT/UNPIVOT,能够轻易达成行列转变的须求。

类似Oracle11g也策画引进PIVOT/UNPIVOT个性,对于Oracle开拓以来,It's a good news。

正文通过几个简单的事例体现PIVOT/UNPIVOT的用法。详细的语法请参照他事他说加以考察联机协助。

PIVOT

创制测量检验表,插入测量试验数据

create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)

select * from test
id name quarter profile


1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500

(8 row(s) affected)

采用PIVOT将个季度的收益转成横向彰显:

select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt

id name 一季度 二季度 三季度 四季度



1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

UNPIVOT

树立测量试验表,插入测量检验数据

drop table test

create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)

insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)

select * from test

id name Q1 Q2 Q3 Q4



1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500

(2 row(s) affected)

应用UNPIVOT,将同一行中八个季度的列数据调换到四行数据:

select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt

id name quarter profile


1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500

(8 row(s) affected)

数据库通过插入、更新和删除等方法来该表表中的记录,当中

OUTPUT 子句

大家向表插入数据的历史观办法有七个,介绍如下:

  1. 触发器的语法结构

  2. 触发器的精晓

  3. 触发器的事例

insert语句实现插入数据

可以在多少开始展览增加和删除改的时候,能够回到受影响的行。先策画一张表

方法一

3.1. 触发器的选择的事例

update语句达成立异数据

create table #t
(
    id int identity primary key 
    ,name varchar(100)
)
go

借使大家有几个名字为MyTestDB的数据库,个中有一个名叫MyTest1的表,数据库和表的创设进度如下:

3.2. 触发器和作业的事例

delete语句实现删除数据


USE [master] 
GO 
IF EXISTS (SELECT name FROM sys.databases 
WHERE name = N'MyTestDB') 
DROP DATABASE [MyTestDB] 
GO 
Create database MyTestDB 
Go 
Use [MyTestDB] 
Go 
IF EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[MyTest1]') 
AND type in (N'U')) 
DROP TABLE [dbo].[MyTest1] 
GO 
USE [MyTestDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[MyTest1]( 
[Id] [int] NULL, 
[Fname] [varchar](100) NULL, 
[Lname] [varchar](100) NULL, 
[salary] [money] NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 

3.3. 在触发器中期维修改所在行的数额

参考表

 

今后大家用守旧的ANSI插入语句向上表加多5行数据,这里要求用到带VALUE从句的INSERT SQL语句来插入数据,脚本如下:

  1. 触发器注意事项

图片 1

1、insert ,影响行在inserted表里

insert into MyTest1 (id ,fname ,lname , salary) values (1 , 'John' , 'Smith' , 150000.00)

4.1. 触发器不能够调用重回结果集的存放进度

插入数据不点名字段名插入

insert into #t(name)
output inserted.*
values('a')

insert into MyTest1 (id ,fname ,lname , salary) values (2 , 'Hillary' , 'Swank' , 250000.00)

4.2. 触发器中不能够导致循环触发的口舌

不点名字段名插入

归来结果:

insert into MyTest1 (id ,fname ,lname , salary) values (3 , 'Elisa' , 'Smith' , 120000.00)

4.3. 触发器的频率难题

复制代码 代码如下:

id name


1 a

批量安顿:

insert into #t
output inserted.*
select 'b'

insert into MyTest1 (id ,fname ,lname , salary) values (4 , 'Liz' , 'Carleno' , 151000.00)

 

mysql> insert into person values(1,'张三','男',1988);
Query OK, 1 row affected, 1 warning (0.03 sec)

id name


2 b

 


 

2、delete ,影响行在deleted表里

delete from #t
output deleted.id
where id = 1

重返结果:

insert into MyTest1 (id ,fname ,lname , salary) values (5 , 'Tony' , 'Mcnamara' , 150300.00)

1. 触发器的语法结构

触发器的语法如下:

 

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

 

其中,trigger_time的值为BEFORE或许AFTE福睿斯,表示触发器的话语在激活该触发器的话语从前或之后实行。

trigger_event钦定激活该触发器的言辞的品类,富含以下二种:

INSERT:将新行插入到多少表中,包含INSERT、LOAD DATA 和REPLACE语句。

UPDATE:修改现成行的数量,包涵UPDATE语句。

DELETE:删除现有数量,包含DELETE和REPLACE语句。

 

 

nsert into前面是表名,values前边是急需插入的数据

id

1

 


 

3、update,会将新数据放在inserted表里,老多少放在deleted表里

update #t
set name='new value'
OUTPUT deleted.id,deleted.name,inserted.id,inserted.name  
where id=2

实践结果如下:

2. 触发器的知道

 

对此同一个数据表的同一个轩然大波和同一个时辰,不可能有多个触发器。举例,对于数据表table1,不得以有八个BEFORE INSERT触发器,也不能够有两各AFTEGL450 INSERT触发器。可是允许有贰个BEOFRE INSERT触发器和一个AFTEPAJERO INSERT触发器同期设有。

 

在触发器中,使用外号OLD和NEW分别援引动作爆发时的原始记录数据和新记录数据。

事件

OLD(只读)

NEW(可使用SET修改)

BEFORE INSERT

无效

将要插入的数据

AFTER INSERT

无效

已经插入的数据

BEFORE UPDATE

将要被修改的原始数据

将要代替原始数据的新数据

AFTER UPDATE

已经被修改的原始数据

已经代替了原始数据的新数据

BEFORE DELETE

将要被删除的原始数据

无效

AFTER DELETE

已经被删除的原始数据

无效

 

要是在触发器中采用了没用的OLD或NEW,则在成立触发器时会报错。

 

(1)在INSERT触发器中分裂意选拔OLD。

mysql> delimiter $$

mysql> create trigger tr_p0 before insert

    -> on test1 for each row

    -> begin

    ->  insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

    ->  insert into t1 values ( concat(OLD.value, NEW.value) );

    -> end;

    -> $$

ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

mysql> delimiter ;

 

(2)在DELETE触发器中区别意行使NEW。

mysql> delimiter $$

mysql> create trigger tr_p2  before delete

    -> on test1 for each row

    -> begin

    ->  insert into history (old_value,new_value,time) values (OLD.value, NEW.value, current_timestamp());

    -> end;

    -> $$

ERROR 1363 (HY000): There is no NEW row in on DELETE trigger

mysql> delimiter ;

 

 

创办触发器时,ON内定的多少表tbl_name必须已经存在;不容许创制针对空中楼阁的数据表的触发器。

 

TRUNCATE TABLE语句并不会触发基于DELETE事件的触发器。

 

values中的数据必须与字段名相相配,如首先字段为空值则输入null,尾巴部分可不输入

id name id name




2 d 2 new value

(1 row(s) affected)

 


 

4、OUTPUT INTO 帮衬将数据 插入到表里

DECLARE @outputTable TABLE(name1 varchar(100),name2 varchar(100))

update #t
set name='new value 3'
OUTPUT deleted.name,inserted.name into @outputTable
where id=2

SELECT * FROM @outputTable

(1 row(s) affected)

(1 row(s) affected)

3. 触发器的例子

 

急需细心的是,字符串数据必须用引号包裹

name1 name2


new value new value 3

(1 row(s) affected)

 

 

)

 

(1 row(s) affected)

3.1. 触发器的施用的事例

 

 

若是存在以下数据表:

mysql>  create table history (id integer auto_increment primary key, old_value varchar(100), new_value varchar(100), time timestamp);

Query OK, 0 rows affected (0.02 sec)

 

mysql> create table test1 ( value varchar(100) primary key);

Query OK, 0 rows affected (0.03 sec)

 

需求:

使用history表来记录test1表的记录的翻新历史场馆。

 

消除办法:

创办八个触发器:

(1)触发器1:在test1表新添记录时,向history数据表插入一条记下,记录起头值。

(2)触发器2:在test1表修改记录时,向history数据表插入一条记下,记录旧值和新值。

(3)触发器3:在test1表删除记录时,向hisotry数据表插入一条记下,记录最后的值。

 

 

触发器1:

drop trigger if exists tr_p0;

delimiter $$

create trigger tr_p0 before insert

on test1 for each row

begin

 insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

end;

$$

delimiter ;

 

 

触发器2:

drop trigger if exists tr_p1;

delimiter $$

create trigger tr_p1 before update

on test1 for each row

begin

 insert into history (old_value,new_value,time) values (OLD.value, NEW.value, current_timestamp());

end;

$$

delimiter ;

 

 

触发器3:

drop trigger if exists tr_p2;

delimiter $$

create trigger tr_p2  before delete

on test1 for each row

begin

 insert into history (old_value,new_value,time) values (OLD.value, NULL, current_timestamp());

end;

$$

delimiter ;

 

 

测量检验结果:

mysql> insert into test1 values ('001');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from test1;

-------

| value |

MySQL基础知识09触发器,UNPIVOT行列转变。 -------

| 001   |

-------

1 row in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

---- ----------- ----------- ---------------------

1 row in set (0.00 sec)

 

mysql> insert into test1 values ('002');

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

---- ----------- ----------- ---------------------

2 rows in set (0.00 sec)

 

mysql> update test1 set value='A001' where value='001';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

| 17 | 001       | A001      | 2017-08-29 11:50:27 |

---- ----------- ----------- ---------------------

3 rows in set (0.00 sec)

 

mysql> select * from test1;

-------

| value |

-------

| 002   |

| A001  |

-------

2 rows in set (0.00 sec)

 

mysql> update test1 set value='A001B' where value='A001';

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

mysql> select * from test1;

-------

| value |

-------

| 002   |

| A001B |

-------

2 rows in set (0.00 sec)

 

MySQL基础知识09触发器,UNPIVOT行列转变。mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

| 17 | 001       | A001      | 2017-08-29 11:50:27 |

| 24 | A001      | A001B     | 2017-08-29 11:50:58 |

---- ----------- ----------- ---------------------

4 rows in set (0.00 sec)

 

mysql> delete from test1 where value='A001B';

Query OK, 1 row affected (0.02 sec)

 

mysql> select * from test1;

-------

| value |

-------

| 002   |

-------

1 row in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

| 17 | 001       | A001      | 2017-08-29 11:50:27 |

| 24 | A001      | A001B     | 2017-08-29 11:50:58 |

| 31 | A001B     | NULL      | 2017-08-29 11:51:20 |

---- ----------- ----------- ---------------------

5 rows in set (0.00 sec)

 

能够见见,在行使触发器之后,history表中曾经成功的笔录了test1表的笔录的更换历史图景。

 

 

钦命字段名插入

(1 row(s) affected)

3.2. 触发器和作业的例证

 

当导致触发器动作的讲话存在于多个专门的职业中时,触发器中的语句也实行在该业务中。当原始语句的事体回滚时,触发器中的语句的功用也将被回滚;当原始语句的政工提交时,触发器中的语句的职能也将被交付。

 

 

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into test1 values ('003');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test1;

-------

| value |

-------

| 002   |

| 003   |

-------

2 rows in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

| 17 | 001       | A001      | 2017-08-29 11:50:27 |

| 24 | A001      | A001B     | 2017-08-29 11:50:58 |

| 31 | A001B     | NULL      | 2017-08-29 11:51:20 |

| 38 | NULL      | 003       | 2017-08-29 11:53:06 |

---- ----------- ----------- ---------------------

6 rows in set (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from test1;

-------

| value |

-------

| 002   |

-------

1 row in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ----------- ---------------------

| id | old_value | new_value | time                |

---- ----------- ----------- ---------------------

|  3 | NULL      | 001       | 2017-08-29 11:49:34 |

| 10 | NULL      | 002       | 2017-08-29 11:50:02 |

| 17 | 001       | A001      | 2017-08-29 11:50:27 |

| 24 | A001      | A001B     | 2017-08-29 11:50:58 |

| 31 | A001B     | NULL      | 2017-08-29 11:51:20 |

---- ----------- ----------- ---------------------

5 rows in set (0.00 sec)

 

 

 

复制代码 代码如下:

(1 row(s) affected)

3.3. 在触发器中期维修改所在行的数据

在触发器中能够行使SET语句修改NEW.XXX的值,从而使得数据表中的新扩张行的数额造成修改后的值。

 

drop trigger if exists tr_p0;

delimiter $$

create trigger tr_p0 before insert

on test1 for each row

begin

 set NEW.value = concat( 'coe2coe:', NEW.value);

 insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

end;

$$

delimiter ;

 

 

施行结果如下:

mysql> truncate table test1;

Query OK, 0 rows affected (0.03 sec)

 

mysql> truncate table history;

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into test1 values ('001');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test1;

-------------

| value       |

-------------

| coe2coe:001 |

-------------

1 row in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ------------- ---------------------

| id | old_value | new_value   | time                |

---- ----------- ------------- ---------------------

|  3 | NULL      | coe2coe:001 | 2017-08-29 13:07:56 |

---- ----------- ------------- ---------------------

1 row in set (0.00 sec)

 

能够见到,数据表test1中新扩充的行的value字段的值由INSERT语句中的001化为了coe2coe_001,那便是触发器中的SET语句的效劳。

 

mysql> insert into person(id,name,sex,birth) values(6,'王芳','女',1992);
Query OK, 1 row affected, 1 warning (0.05 sec)

(1 row(s) affected)

4. 触发器注意事项

 

insert into 后边接表名和字段,此处的字段可调动岗位

方法二

4.1. 触发器无法调用重回结果集的蕴藏进度

触发器中得以调用不回来结果集的储存进度,不过不能调用重回结果集的囤积进程。

 

以下存储进度不回来任何结果集,因而得以健康调用。

drop procedure if exists sp_p5;

delimiter $$

 

create procedure sp_p5()

begin

set @x='coe2coe@qq.com';

end;

$$

delimiter ;

 

触发器:

drop trigger if exists tr_p0;

delimiter $$

create trigger tr_p0 before insert

on test1 for each row

begin

 insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

 call sp_p5();

end;

$$

delimiter ;

 

试行结果:

mysql> insert into test1 values ('coe2coe@qq.com');

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from test1;

----------------

| value          |

----------------

| 002            |

| 003            |

| coe2coe@qq.com |

----------------

3 rows in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ---------------- ---------------------

| id | old_value | new_value      | time                |

---- ----------- ---------------- ---------------------

|  3 | NULL      | 001            | 2017-08-29 11:49:34 |

| 10 | NULL      | 002            | 2017-08-29 11:50:02 |

| 17 | 001       | A001           | 2017-08-29 11:50:27 |

| 24 | A001      | A001B          | 2017-08-29 11:50:58 |

| 31 | A001B     | NULL           | 2017-08-29 11:51:20 |

| 52 | NULL      | 003            | 2017-08-29 12:01:54 |

| 59 | NULL      | coe2coe@qq.com | 2017-08-29 12:03:32 |

---- ----------- ---------------- ---------------------

7 rows in set (0.00 sec)

 

比如将上述存款和储蓄进度修改为回到结果集的蕴藏进度,则不能够在触发器中调用,否则在触及该触发器时将生出错误。此种境况下,在概念触发时并不会唤醒任何错误。

 

mysql> drop procedure if exists sp_p5;

Query OK, 0 rows affected (0.01 sec)

 

mysql> delimiter $$

mysql>

mysql> create procedure sp_p5()

    -> begin

    -> select 'coe2coe@qq.com';

    -> end;

    -> $$

Query OK, 0 rows affected (0.01 sec)

 

mysql> delimiter ;

mysql>

mysql> drop trigger if exists tr_p0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delimiter $$

mysql> create trigger tr_p0 before insert

    -> on test1 for each row

    -> begin

    ->  insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

    ->  call sp_p5();

    -> end;

    -> $$

Query OK, 0 rows affected (0.02 sec)

 

mysql> delimiter ;

 

 

选拔以下语句触发该触发器,则提示错误。

mysql> select * from test1;

----------------

| value          |

----------------

| 002            |

| 003            |

| coe2coe@qq.com |

----------------

3 rows in set (0.00 sec)

 

mysql> insert into test1 values ('coe2coe');

ERROR 1415 (0A000): Not allowed to return a result set from a trigger

mysql> select * from test1;

----------------

| value          |

----------------

| 002            |

| 003            |

| coe2coe@qq.com |

----------------

3 rows in set (0.00 sec)

 

mysql> select * from history;

---- ----------- ---------------- ---------------------

| id | old_value | new_value      | time                |

---- ----------- ---------------- ---------------------

|  3 | NULL      | 001            | 2017-08-29 11:49:34 |

| 10 | NULL      | 002            | 2017-08-29 11:50:02 |

| 17 | 001       | A001           | 2017-08-29 11:50:27 |

| 24 | A001      | A001B          | 2017-08-29 11:50:58 |

| 31 | A001B     | NULL           | 2017-08-29 11:51:20 |

| 52 | NULL      | 003            | 2017-08-29 12:01:54 |

| 59 | NULL      | coe2coe@qq.com | 2017-08-29 12:03:32 |

---- ----------- ---------------- ---------------------

7 rows in set (0.00 sec)

 

 

从上述结果能够看出,在触发触发器时若是发生了错误,则导致触发器触发的原始INSERT语句也倒闭了,在test1表中并不会看出该INSERT新添的记录,history表也一直不有关记录。

 

 

但贰个要求条件是末端的values值必须与其字段对应

若果我们在上述的MyTestDB数据库中有表MyTest2,如下:

4.2. 触发器中不可能导致循环触发的言辞

 

在三个触发器中,不可能包括导致触发器的大循环触发的言语。下边包车型地铁触发器由test1表的BEFORE INSERT事件触发,而在该触发器中施行了一条test1表上的insert语句,那将导致触发器的循环触发,产生三个死循环。这种场地在概念触发器时即使并不报错,不过在向test1表插入新数据时将报告二个不当。

 

mysql> delimiter $$

mysql> create trigger tr_p0 before insert

    -> on test1 for each row

    -> begin

    ->  insert into history (old_value,new_value,time) values (NULL, NEW.value, current_timestamp());

    ->  insert into test1 values ( concat('001_', NEW.value) );

    -> end;

    -> $$

Query OK, 0 rows affected (0.01 sec)

 

mysql> delimiter ;

mysql>

mysql> select * from test1;

Empty set (0.00 sec)

 

mysql> select * from history;

Empty set (0.00 sec)

 

mysql> insert into test1 values ('coe2coe@qq.com');

ERROR 1442 (HY000): Can't update table 'test1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

 

 

 

同期插入多条数据

USE [MyTestDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest2]')AND type in (N'U')) 
DROP TABLE [dbo].[MyTest2] 
GO 
CREATE TABLE [dbo].[MyTest2]( 
[Id] [int] NULL, 
[Fname] [varchar](100) NULL, 
[Lname] [varchar](100) NULL, 
[salary] [money] NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 

4.3. 触发器的频率难题

 

 

触发器的触及是依附数据表的行记录触发的,即每一行数据都会招致触发器的一回接触。当一条SQL语句导致大气行数据变动时,将会变成大气的触发器动作。那或许会导致相比较印证的品质难点。

不对路的应用触发器,还恐怕变成有的锁方面包车型大巴难点。因而,除非极度须要,尽量在应用程序中成就有关工作的多寡操作,实际不是行使触发器。

 

复制代码 代码如下:

上边大家再用另外一种古板的插入方法同样充裕5行数据,也正是接纳带SELECT从句的INSERT SQL语句,脚本如下:

mysql> insert into person(id,name) values(8,'钱名'),(9,'章硕');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into MyTest2 select 1 , 'John' , 'Smith' , 150000.00

values后边用四个括号插入数据,逗号隔开即可

insert into MyTest2 select 2 , 'Hillary' , 'Swank' , 250000.00

关于插入的字段只需结合方面讲的三个例证使用

insert into MyTest2 select 3 , 'Elisa' , 'Smith' , 120000.00

将查询结果插入到表中

insert into MyTest2 select 4 , 'Liz' , 'Carleno' , 151000.00

复制代码 代码如下:

insert into MyTest2 select 5 , 'Tony' , 'Mcnamara' , 150300.00

mysql> insert into person2(id,name,sex,birth) select * from person;
Query OK, 9 rows affected, 6 warnings (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 6

奉行结果如下:

此处要注意,插入的字段和表中的字段个数和数据类型必须一致,不然就能够报错

(1 row(s) affected)

复制一张表

(1 row(s) affected)

复制代码 代码如下:

(1 row(s) affected)

mysql> CREATE TABLE per AS SELECT * FROM person;
Query OK, 1 row affected (0.16 sec)
Records: 1  Duplicates: 0  Warnings: 0

(1 row(s) affected)

update 表名代表要革新的表,set前边设置须要更新的内容

(1 row(s) affected)

where用作限制创新标准,前边接表明式,只要表达式为真便知足条件

方法三

Tips:where 1也能代表真,即一切满意

一律的,大家再若是上述的MyTestDB数据库中有表MyTest3,如下:

多字段更新

USE [MyTestDB] 
GO 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest3]')AND type in (N'U')) 
DROP TABLE [dbo].[MyTest3] 
GO 
USE [MyTestDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[MyTest3]( 
[Id] [int] NULL, 
[Fname] [varchar](100) NULL, 
[Lname] [varchar](100) NULL, 
[salary] [money] NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 

复制代码 代码如下:

上面大家用第三种守旧的插入方法一样充足5行数据,这里运用的是带SELECT从句和UNION从句的INSERT SQL语句,脚本如下:

mysql> update person set name='小红',sex='女' where id=3;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

insert into MyTest3

多字段更新只要求在set前边增添多少个要修改的字段和数目就可以,用逗号隔断

select 1 , 'John' , 'Smith' , 150000.00

即使想翻新具备记录则没有需求加where

union select 2 , 'Hillary' , 'Swank' , 250000.00

tips:使用update要特地小心,因为有极大概率多条记下满意where条件

union select 3 , 'Elisa' , 'Smith' , 120000.00

     最佳是先查看一边表,鲜明要更新的笔录

union select 4 , 'Liz' , 'Carleno' , 151000.00

去除字段
删去钦点记录

union select 5 , 'Tony' , 'Mcnamara' , 150300.00

复制代码 代码如下:

实行理并了结果如下:

mysql> delete from person where id=9;
Query OK, 1 row affected (0.02 sec)

(5 row(s) affected)

删除记录也亟需跟上where限定

方法四

tips:除非您不行鲜明where子句只会删除你想要删除的行

最终一种办法,供给插入数据的目的是MyTestDB数据库中的表MyTest4,如下:

   不然都应当用select来认可意况

USE [MyTestDB] 
GO 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTest4]')AND type in (N'U')) 
DROP TABLE [dbo].[MyTest4] 
GO 
USE [MyTestDB] 
GO 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
SET ANSI_PADDING ON 
GO 
CREATE TABLE [dbo].[MyTest4]( 
[Id] [int] NULL, 
[Fname] [varchar](100) NULL, 
[Lname] [varchar](100) NULL, 
[salary] [money] NULL 
) ON [PRIMARY] 
GO 
SET ANSI_PADDING OFF 
GO 

去除全体记录

方今大家要用到SQL Server 二零一零中提供的新点子——行值构造器的插入SQL语句为上述表插入5行数据,这种艺术能够在二个INSERT语句中贰次性插入多行数据,脚本如下:

复制代码 代码如下:

insert into MyTest4 (id ,fname ,lname , salary) values

mysql> delete from person;
Query OK, 8 rows affected (0.03 sec)

(1 , 'John' , 'Smith' , 150000.00),

在不跟where限定标准的景观下就可以逐个删除全体记录

(2 , 'Hillary' , 'Swank' , 250000.00),

另外还应该有truncate table语句,它会删除原本的表,再另行确立,成效越来越高

(3 , 'Elisa' , 'Smith' , 120000.00),

tips:那边删除不会要任何提醒,说删就删了,快的很

(4 , 'Liz' , 'Carleno' , 151000.00),

     所以使用的时候要那三个小心,最佳先把数据备份

(5 , 'Tony' , 'Mcnamara' , 150300.00)

insert 语句完成插入数据 update 语句完结立异数据 delete 语句达成删除数据 参考...

实行结果如下:

(5 row(s) affected)

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:MySQL基础知识09触发器,UNPIVOT行列转变

关键词: