xml地图|网站地图|网站标签 [设为首页] [加入收藏]
MySQL中的触发器,表的管理_关于完整性约束的详
分类:数据库

转载自:

SQL Server 表的管理之_关于完整性约束的详解

转载自:

什么是触发器

简单的说,就是一张表发生了某件事(插入、删除、更新操作),然后自动触发了预先编写好的若干条SQL语句的执行;

一、概述:

  ●约束是SQL Server提供的自动保持数据库完整性的一种方法, 它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。

  ●SQL约束用于指定表中数据的规则。

  ●约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。

什么是视图

通俗的讲,视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。

特点及作用

特点:触发事件的操作和触发器里的SQL语句是一个事务操作,具有原子性,要么全部执行,要么都不执行;

作用:保证数据的完整性,起到约束的作用;

1.1SQL创建约束

当使用CREATE TABLE语句创建表时,或者在使用ALTER TABLE语句创建表之后,可以指定约束。

语法

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

SQL CREATE TABLE + CONSTRAINT 语法

CREATE TABLE table_name 
( 
column_name1 data_type(size) constraint_name, 
column_name2 data_type(size) constraint_name, 
column_name3 data_type(size) constraint_name, 
.... 
);

视图的特性

视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变);

可以跟基本表一样,进行增删改查操作(ps:增删改操作有条件限制);

例子:创建触发器,记录表的增、删、改操作记录

接下来将创建user和user_history表,以及三个触发器tri_insert_user、tri_update_user、tri_delete_user,分别对应user表的增、删、改三件事;

  • 创建user表;

    1 DROP TABLE IF EXISTS user; 2 CREATE TABLE user ( 3 id bigint(20) NOT NULL AUTO_INCREMENT, 4 account varchar(255) DEFAULT NULL, 5 name varchar(255) DEFAULT NULL, 6 address varchar(255) DEFAULT NULL, 7 PRIMARY KEY (id) 8 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 创建对user表操作历史表;

    DROP TABLE IF EXISTS user_history; CREATE TABLE user_history ( id bigint(20) NOT NULL AUTO_INCREMENT, user_id bigint(20) NOT NULL, operatetype varchar(200) NOT NULL, operatetime datetime NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  • 创建user表插入事件对应的触发器tri_insert_user;

几点说明:

DELIMITER:改变输入的结束符,默认情况下输入结束符是分号;,这里把它改成了两个分号;;,这样做的目的是把多条含分号的语句做个封装,全部输入完之后一起执行,而不是一遇到默认的分号结束符就自动执行;

new:当触发插入和更新事件时可用,指向的是被操作的记录

old: 当触发删除和更新事件时可用,指向的是被操作的记录

图片 1

DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
end
;;
DELIMITER ;

图片 2

  • 创建user表更新事件对应的触发器tri_update_user;

图片 3

DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;

图片 4

  • 创建user表删除事件对应的触发器tri_delete_user;

图片 5

DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
end
;;
DELIMITER ;

图片 6

  • 至此,全部表及触发器创建完成,开始验证结果,分别做插入、修改、删除事件,执行以下语句,观察user_history是否自动产生操作记录;

图片 7

INSERT INTO user(account, name, address) VALUES ('user1', 'user1', 'user1');
INSERT INTO user(account, name, address) VALUES ('user2', 'user2', 'user2');

UPDATE user SET name = 'user3', account = 'user3', address='user3' where name='user1';

DELETE FROM `user` where name = 'user2';

图片 8

  • 观察结果user表和user_history表的结果,操作记录已产生,说明触发器工作正常;

图片 9

1.2删除约束

任何现有约束都可以通过在 ALTER TABLE 命令中指定 DROP CONSTRAINT 选项的方法删除掉。

例如,要去除 EMPLOYEES 表中的主键约束,可以使用下述命令:

 ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;

一些数据库实现可能提供了删除特定约束的快捷方法。例如,要在 Oracle 中删除一张表的主键约束,可以使用如下命令:

 ALTER TABLE EMPLOYEES DROP PRIMARY KEY;

某些数据库实现允许禁用约束。这样与其从数据库中永久删除约束,你可以只是临时禁用掉它,过一段时间后再重新启用。

视图的作用

方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;

更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别;

弊端

增加程序的复杂度,有些业务逻辑在代码中处理,有些业务逻辑用触发器处理,会使后期维护变得困难;

 

 

 

1.3完整性约束

完整性约束用于保证关系型数据库中数据的精确性和一致性。对于关系型数据库来说,数据完整性由参照完整性(referential integrity,RI)来保证。

有很多种约束可以起到参照完整性的作用,这些约束包括主键约束(Primary Key)、外键约束(Foreign Key)、唯一性约束(Unique Constraint)以及上面提到的其他约束。

约束可分为以下几种:

1、PRIMARY KEY 主键约束

2、FOREIGN KEY 外键约束

3、UNIQUE 约束 唯一约束

4、CHECK 约束 检查约束

5、DEFUALT 定义 默认约束

下面分别详解:

使用场合

权限控制的时候,不希望用户访问表中某些含敏感信息的列,比如salary...

关键信息来源于多个复杂关联表,可以创建视图提取我们需要的信息,简化操作;

二、SQL PRIMARY KEY约束


视图实例1-创建视图及查询数据操作

现有三张表:用户(user)、课程(course)、用户课程中间表(user_course),表结构及数据如下:

表定义:

图片 10图片 11

 1 -- ----------------------------
 2 -- Table structure for `course`
 3 -- ----------------------------
 4 DROP TABLE IF EXISTS `course`;
 5 CREATE TABLE `course` (
 6   `id` bigint(20) NOT NULL AUTO_INCREMENT,
 7   `name` varchar(200) NOT NULL,
 8   `description` varchar(500) NOT NULL,
 9   PRIMARY KEY (`id`)
10 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
11 
12 -- ----------------------------
13 -- Records of course
14 -- ----------------------------
15 INSERT INTO `course` VALUES ('1', 'JAVA', 'JAVA课程');
16 INSERT INTO `course` VALUES ('2', 'C++', 'C++课程');
17 INSERT INTO `course` VALUES ('3', 'C语言', 'C语言课程');
18 
19 -- ----------------------------
20 -- Table structure for `user`
21 -- ----------------------------
22 DROP TABLE IF EXISTS `user`;
23 CREATE TABLE `user` (
24   `id` bigint(20) NOT NULL AUTO_INCREMENT,
25   `account` varchar(255) NOT NULL,
26   `name` varchar(255) NOT NULL,
27   `address` varchar(255) DEFAULT NULL,
28   `others` varchar(200) DEFAULT NULL,
29   `others2` varchar(200) DEFAULT NULL,
30   PRIMARY KEY (`id`)
31 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
32 
33 -- ----------------------------
34 -- Records of user
35 -- ----------------------------
36 INSERT INTO `user` VALUES ('1', 'user1', '小陈', '美国', '1', '1');
37 INSERT INTO `user` VALUES ('2', 'user2', '小张', '日本', '2', '2');
38 INSERT INTO `user` VALUES ('3', 'user3', '小王', '中国', '3', '3');
39 
40 -- ----------------------------
41 -- Table structure for `user_course`
42 -- ----------------------------
43 DROP TABLE IF EXISTS `user_course`;
44 CREATE TABLE `user_course` (
45   `id` bigint(20) NOT NULL AUTO_INCREMENT,
46   `userid` bigint(20) NOT NULL,
47   `courseid` bigint(20) NOT NULL,
48   PRIMARY KEY (`id`)
49 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
50 
51 -- ----------------------------
52 -- Records of user_course
53 -- ----------------------------
54 INSERT INTO `user_course` VALUES ('1', '1', '2');
55 INSERT INTO `user_course` VALUES ('2', '1', '3');
56 INSERT INTO `user_course` VALUES ('3', '2', '1');
57 INSERT INTO `user_course` VALUES ('4', '2', '2');
58 INSERT INTO `user_course` VALUES ('5', '2', '3');
59 INSERT INTO `user_course` VALUES ('6', '3', '2');

View Code

2.1SQL PRIMARY KEY约束

●PRIMARY KEY约束唯一标识数据库表中的每条记录。

●主键必须包含唯一的值。

●主键列不能包含NULL值。

●每个表都应该有一个主键,并且每个表只能有一个主键。


表数据:

图片 12

这时,当我们想要查询小张上的所以课程相关信息的时候,需要这样写一条长长的SQL语句,如下:

图片 13

SELECT
    `uc`.`id` AS `id`,
    `u`.`name` AS `username`,
    `c`.`name` AS `coursename`
FROM
    `user` `u`
LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`))
LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`))
WHERE
    u.`name` = '小张'

图片 14

但是我们可以通过视图简化操作,例如我们创建视图view_user_course如下:

图片 15

-- ----------------------------
-- View structure for `view_user_course`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_course`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_user_course` AS (
    SELECT
        `uc`.`id` AS `id`,
        `u`.`name` AS `username`,
        `c`.`name` AS `coursename`
    FROM
        (
            (
                `user` `u`
                LEFT JOIN `user_course` `uc` ON ((`u`.`id` = `uc`.`userid`))
            )
            LEFT JOIN `course` `c` ON ((`uc`.`courseid` = `c`.`id`))
        )
);

图片 16

几点说明(MySQL中的视图在标准SQL的基础之上做了扩展):

ALGORITHM=UNDEFINED:指定视图的处理算法;

DEFINER=`root`@`localhost`:指定视图创建者;

SQL SECURITY DEFINER:指定视图查询数据时的安全验证方式;

创建好视图之后,我们可以直接用以下SQL语句在视图上查询小张上的所以课程相关信息,同样可以得到所需结果:

图片 17

SELECT
    vuc.username,
    vuc.coursename
FROM
    view_user_course vuc
WHERE
     vuc.username = '小张'

图片 18

2.2CREATE TABLE时的SQL PRIMARY KEY约束

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

视图实例2-增删改数据操作

继续,我们可以尝试在视图view_user_course上做增删改数据操作,如下:

update view_user_course set username='test',coursename='JAVASCRIPT' where id=3

遗憾的是操作失败,提示错误信息如下:

[SQL] update view_user_course set username='test',coursename='JAVASCRIPT' where id=3

[Err] 1393 - Can not modify more than one base table through a join view 'demo.view_user_course'

因为不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作;

那么哪些操作可以在视图上进行呢?

视图与表是一对一关系情况:如果没有其它约束(如视图中没有的字段,在基本表中是必填字段情况),是可以进行增删改数据操作;

如我们创建用户关键信息视图view_user_keyinfo,如下:

图片 19

-- ----------------------------
-- View structure for `view_user_keyinfo`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_keyinfo`;

CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `view_user_keyinfo` AS SELECT
    `u`.`id` AS `id`,
    `u`.`account` AS `account`,
    `u`.`name` AS `username`
FROM
    `user` `u`;

图片 20

进行增删改操作如下,操作成功(注意user表中的其它字段要允许为空,否则操作失败):

INSERT INTO view_user_keyinfo (account, username)
VALUES
    ('test1', 'test1');

DELETE
FROM
    view_user_keyinfo
WHERE
    username = 'test1';

UPDATE view_user_keyinfo
SET username = 'updateuser'
WHERE
    id = 1

视图与表是一对多关系情况:如果只修改一张表的数据,且没有其它约束(如视图中没有的字段,在基本表中是必填字段情况),是可以进行改数据操作,如以下语句,操作成功;

update view_user_course set coursename='JAVA' where id=1;

update view_user_course set username='test2' where id=3;

以下操作失败:

delete from view_user_course where id=3;

insert into view_user_course(username, coursename) VALUES('2','3');

2.3ALTER TABLE时的SQL PRIMARY KEY约束

当表已被创建时,如需在“P_Id”列创建PRIMARY KEY约束,请使用下面的SQL:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

其它

视图中的查询语句性能要调到最优;

修改操作时要小心,不经意间你已经修改了基本表里的多条数据;

其它性能相关方面待实践体会...

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:MySQL中的触发器,表的管理_关于完整性约束的详

上一篇:Server判断某个字段是否包含中文,常用的优化步 下一篇:SQL基础之外键约束,简单快速的处理方法
猜你喜欢
热门排行
精彩图文