如题:2019年10月
答案:P143,相容性。理解就行了。为什么会是相容性?是和关系型数据库中的关系相关。关系就涉及到相处规则问题,数据也是一样。数据间不能相互矛盾。
扩展:mysql中的完整性约束实现,书上讲了三种,实体、参照、用户自定义完整性,其实还有个用户自定的域完整性。
实现方式:
实体完整性
在关系数据库中,一条记录代表一个实体。而实体是可以相互区分、识别的,也即它们应具有某种唯一性标识(该标识不能取相同的值,也不能为空)。其实这里就是候选键的定义。
如何实现实体完整性呢?
A、主键约束:一张表只能有一列设置主键,值必须唯一,不允许为空,innoDB存储引擎,主键就是索引。
B、唯一值约束:一张表可以有多个列添加唯一值约束,一直允许一条记录为空值。(只能有一条记录为空,挺新鲜的,需要记忆)。
主键约束:
主键约束相当于唯一约束与非空约束的组合,主键约束列不允许重复,也不允许出现空值;多列组合的主键约束,列都不允许为空值,并且组合的值不允许重复。每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。
列级创建,在表的某个列属性后,加上primary key,如:
create table product
(
productID int PRIMARY KEY,
pName VARCHAR(10),
price DOUBLE
)ENGINE=MyISAM default CHARSET=utf8;
表级创建,在表所有列的属性后,加上primary key(index_col_name,...),复合主键只适用此方法。如:
create table product
(
productID int,
pName VARCHAR(10),
price DOUBLE,
CONSTRAINT pk_s_productID PRIMARY KEY(productID)
)ENGINE=MyISAM default CHARSET=utf8;
复合主键,如
create table student
(
studentID int,
id INT,
sname VARCHAR(10),
score int,
PRIMARY KEY(studentid,id)
)ENGINE=MyISAM default CHARSET=utf8;
扩展:
增加主键:适用于复合主键
alter table TStudent add primary key(studentid);
删除主键:适用复合主键
alter table TStudent drop primary key;
自增主键:自增AUTO_INCREMENT 关键字
如果不指定主键值,会自动在现有主键值的最大值上自动增加1作为新记录的主键,主键值默认从1开始。可以在数据数据类型整数型的列上添加自增主键。
创建:
create table product
(
productID int PRIMARY KEY AUTO_INCREMENT not NULL,
pName VARCHAR(10),
price DOUBLE
)ENGINE=MyISAM default CHARSET=utf8;
为现有表增加自增列
alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT;
删除表中自增列
alter table TStudent modify column studentID int not NULL;
删除自增列,仍然时主键,但是没有自增长功能
唯一约束:指定某列和几列组合的数据不能重复
创建表时指定唯一性约束
create table score
(sname VARCHAR(10) UNIQUE,
score int not NULL
);
给现有列增加唯一性约束
alter table score add CONSTRAINT uc_sname UNIQUE(sname);
如果表中现有记录有重复值,不允许添加唯一性约束。可以通过聚合函数,查找有重复值的记录,删除,再创建唯一性约束。
扩展:
创建复合唯一约束
create table student
(
studentID int,
id INT,
sname VARCHAR(10),
score int,
CONSTRAINT uc_id UNIQUE(studentID, id)
)ENGINE=MyISAM default CHARSET=utf8;
删除列的唯一性约束
alter table score drop index uc_sname;
参照完整性
在删除和输入记录时,引用完整性保持表之间已定义的关系。引用完整性确保键值在所有表中一致,不能引用不存在的值.
MySQL参照完整性一般是通过MySQL外键(foreign key)或单独列用 reference_definition实现的。
外键(仅innoDB支持)所引用表的列必须是主键。
外键声明包括三个部分:
A、哪个列或列组合是外键
B、指定外键参照的表和列
C、参照动作[cascade(级联操作),restrict(拒绝操作),set null(设为空),no action,set default]。
如果外键约束指定了参照动作,主表记录做修改,删除,从表引用的列会做相应修改,或不修改,拒绝修改或设置为默认值。
引用表的列名必须是主键,且在删除引用表时必须删除引用关系或者删除当前表。
创建表时指定外键:
创建两张表,学生表student和成绩表score,成绩表的sid列的取值参照学生表(学生表student的sid列设置为主键,且表的存储引擎为innodb,成绩表score的存储引擎也必须设置为innodb)。
create table student
(
sid int not null primary key,
sname varchar(20)
) engine=innodb;
create table score
(
sid int not null,
mark INT,
constraint score_fk FOREIGN KEY (sid) #constraint约束,指定外键约束名
references student(sid) on delete cascade on update cascade
) engine=innodb;
在学生表插入一条记录insert into student values (1,'孙悟空')
在成绩表插入一条记录,学号是1,成功。insert into score values (1,98)
在成绩表插入一条记录,学号是2,失败。insert into score values (2,88)
在学生表插入学号是2的一条记录insert into student values (2,'唐僧')
再在成绩表插入一条学号是2的记录,成功,证明外键参照成功。insert into score values (2,88);
语法详解:
CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action
CONSTRAINT
子句允许您为外键约束定义约束名称。如果省略它,MySQL将自动生成一个名称。FOREIGN KEY
子句指定子表中引用父表中主键列的列。您可以在FOREIGN KEY
子句后放置一个外键名称,或者让MySQL为您创建一个名称。 请注意,MySQL会自动创建一个具有foreign_key_name
名称的索引。REFERENCES
子句指定父表及其子表中列的引用。 在FOREIGN KEY
和REFERENCES
中指定的子表和父表中的列数必须相同。ON DELETE
子句允许定义当父表中的记录被删除时,子表的记录怎样执行操作。如果省略ON DELETE
子句并删除父表中的记录,则MySQL将拒绝删除子表中相关联的数据。此外,MySQL还提供了一些操作,以便您可以使用其他选项,例如ON DELETE CASCADE,当删除父表中的记录时,MySQL可以删除子表中引用父表中记录的记录。 如果您不希望删除子表中的相关记录,请改用ON DELETE SET NULL
操作。当父表中的记录被删除时,MySQL会将子表中的外键列值设置为NULL
,条件是子表中的外键列必须接受NULL
值。 请注意,如果使用ON DELETE NO ACTION
或ON DELETE RESTRICT
操作,MySQL将拒绝删除。ON UPDATE
子句允许指定在父表中的行更新时,子表中的行会怎样执行操作。当父表中的行被更新时,可以省略ON UPDATE
子句让MySQL拒绝对子表中的行的任何更新。ON UPDATE CASCADE
操作允许您执行交叉表更新,并且当更新父表中的行时,ON UPDATE SET NULL
操作会将子表中行中的值重置为NULL
值。ON UPDATE NO ACTION
或UPDATE RESTRICT
操作拒绝任何更新。
扩展:
1、删除参照约束
ALTER TABLEscore DROP FOREIGN KEY score_fk;
2、给现有表增加参照约束
ALTER TABLEscore ADD CONSTRAINTscore_fk2FOREIGN KEY (sid) REFERENCESstudent(sid);
3、验证级联动作删除和更新
在score表创建的参照完整性,删除动作和更新动作的参照动作选择了cascade(级联操作),当学生表的sid更新时,分数表score的相应的sid也会更新,当学生被删除,分数表对应的sid的记录也会自动删除。
更新学生表学号是1的学生的学号为10
update student set sid=10 where sid=1
查看成绩表,可以看到以前学号是1的已经变成了10select * from score
删除学生表学号是2的学生delete from student where sid=2
可以看到成绩表,该学生的成绩已经级联删除select * from score
4、验证级联动作No Aaction
级联动作设置为NO ACTION,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作。
Restrict动作同no action,都是立即检查外键约束。
将参照动作设置为no action,如果成绩表score有该学生sid,将不能更改学生表student表的学生sid列,也不能删除该学生。除非你先删除该学生的成绩,再删除该学生。
删除score表的外键约束ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
增加score表的sid列外键约束
ALTER TABLE `score` ADD CONSTRAINT `score_fk`
FOREIGN KEY (`sid`) REFERENCES `student` (`sid`)
ON DELETE NO ACTION ON UPDATE NO ACTION;
更新学号是10的学生的学号,失败update student set sid=11 where sid=10
删除学号是10的学生,失败delete from student where sid=10
需要先删除该学生成绩表中的记录,再删除该学生。
delete from student where sid=10
delete from score where sid=10;
5、验证级联动作Set NULL
在父表上update/delete记录时,将子表上匹配记录的列设为null ,要注意子表的外键列不能为not null。
删除成绩表的外键约束ALTER TABLE
scoreDROP FOREIGN KEY
score_fk;
增加成绩表的sid列外键约束,参照动作为set nullALTER TABLE
scoreADD CONSTRAINT
score_fkFOREIGN KEY (
sid) REFERENCES
student(
sid) ON DELETE SET NULL ON UPDATE SET NULL;
修改成绩表的sid列默认值为NULL
ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER(11) DEFAULT NULL;
insert into student values (1,'孙悟空')
insert into student values (2,'猪八戒')
insert into score values (1,98)
insert into score values (2,88)
删除学生表中学号1的学生delete from student where sid=1
查看成绩表,成绩表中学号为1的列为NULLselect * from score
自定义完整性
用户自己定义的业务规则,比如使用触发器实现自定义业务规则。
域完整性
指给定列的取值范围(即输入的有效性),比如性别取值应为男或女。
强制域有效性的方法有:限制类型(通过数据类型)、格式(通过 CHECK 约束和规则)或可能值的范围(通过 FOREIGN KEY 约束、CHECK 约束、DEFAULT 定义、NOT NULL 定义和规则)。
扩展:
1、默认值
在表中插入一条新的记录时,如果没有为该字段赋值,那么数据库系统会自动为该字段赋一条默认值。
create table st
(
sid INT not null primary key auto_increment,
sname varchar(10),
subject varchar(20) default '软件工程',
entertime TIMESTAMP default now()
);
给表中一列添加默认值约束
alter table st modify column subject VARCHAR(20) default '计算机科学与技术';
删除表中一列的默认值约束:
alter table st modify column subject VARCHAR(20) default NULL;
2、创建非空约束
非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
Null类型特征: 所有的类型的值都可以是null,包括int、float等数据类型 空字符串是不等于NULL,0也不等于NULL。
创建表时给列指定非空约束
create table score
(sname VARCHAR(10) not NULL,
score int not NULL
);
给指定列指定非空约束
alter table score modify column score int not NULL;
删除非空约束
alter table score modify column score int;
3、检查check
check关键字,在插入新行或者更改已有行时才起作用,作用是阻止不满足条件的值进入该列,对null值无效,因为插入null就相当于没有插入。一个列可有多个check。
age int check(age between 10 and 20);
目前MySQL不支持check约束,微软MSSQL支持Check约束,但创建表时可以指定Check约束,但不起作用。
触发器:
触发器是和表关联的特殊的存储过程,可以在插入,删除或修改表中的数据时触发执行,比数据库本身标准的功能有更精细和更复杂的数据控制能力。
创建触发器:
语法:
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
语法详解:
trigger_name:触发器的名称。
tirgger_time:触发时机,为BEFORE或者AFTER。
注:
BEFORE:希望验证新数据是否满足使用的限制。
AFTER:希望在激活触发器的语句执行之后完成几个或更多的改变。
trigger_event:触发事件,为INSERT、DELETE或者UPDATE。
tb_name:表示建立触发器的表名,在哪张表上建立触发器。
trigger_stmt:触发器的程序体,可以是一条SQL语句或者是用BEGIN和END包含的多条语句。
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器。
MySQL除了对INSERT、UPDATE、DELETE基本操作进行定义外,还定义了LOAD DATA和REPLACE语句,这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中,相当与一系列的 INSERT操作。
REPLACE语句一般来说和INSERT语句很像,只是在表中有primary key或 unique索引时,如果插入的数据和原来primary key或unique索引一致时,会先删除原来的数据,然后增加一条新数据。
INSERT型触发器:插入某一行时激活触发器,通过 INSERT、LOAD DATA、REPLACE语句触发;
1)、在INSERT触发器中,可引用NEW的虚拟表,来访问被插入的行。
2)、在BEFORE INSERT触发器中,NEW中的值也可以被更新。
3)、对于AUTO_INCREMENT中NEW在INSERT执行之前包含的是0值,在INSERT执行之后将包含新的自动生成值。
UPDATE型触发器:更改某一行时激活触发器,通过UPDATE语句触发;
1)、在INSERT触发器中,可引用OLD的虚拟表,来访问以前的值。
2)、在BEFORE INSERT触发器中,NEW中的值也可以被更新(只有被UPDATE语句更新的值才能放入NEW表中)。
3)、OLE中的值全部是只读的,不能被更新。
4)、当触发器涉及对触发表自身的更新操作时,只能使用BEFORE INSERT,而AFTER UPDATE触发器不能使用。
DELETE型触发器:删除某一行时激活触发器,通过DELETE、REPLACE语句触发。
1)、在DELETE触发器中,可引用OLD的虚拟表,来访问被删除的行,可引用NEW的虚拟表,来访问新更新的值。
2)、OLE中的值全部是只读的,不能被更新。
删除触发器:
语法:
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
schema_name是数据库的名称,是可选的。如果省略了schema,将从当前数据库中舍弃触发程序。trigger_name是要删除的触发器的名称。
扩展:
MySQL中定义了NEW和OLD,用来表示触发器的所在表中,触发了触发器的那一行数据。
在INSERT型触发器中,NEW用来表示将要(BEFORE)或已经(AFTER)插入的新数据;
在UPDATE型触发器中,OLD用来表示将要或已经被修改的原数据,NEW用来表示将要或已经修改为的新数据;
在DELETE型触发器中,OLD用来表示将要或已经被删除的原数据;
使用方法:NEW.columnName(columnName为相应数据表某一列名)
另外,OLD是只读的,而NEW则可以在触发器中使用SET赋值,不会再次触发触发器,造成循环调用。
1、变量声明:DECLARE var_name[,...] type [DEFAULT value]
对变量赋值采用SET 语句,语法为:SET var_name = expr [,var_name = expr] ...
如书中的例子:
2、触发器信息查看
在MySQL中,所有的触发器的定义都存在于INFORMATION_SCHEMA数据库的triggers表中,可以通过查询命令SELECT来查看,具体语法如下:
SHOW TRIGGERS [FROM schema_name];
触发器的执行顺序
InnoDB数据库,若SQL语句或触发器执行失败,MySQL会回滚事务,有:
A、如果BEFORE触发器执行失败,SQL无法正确执行。
B、SQL执行失败时,AFTER型触发器不会触发。
C、AFTER类型的触发器执行失败,SQL会回滚。
MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果对非事务表进行操作,那么就无法回滚,数据可能会出错。
3、触发器应用
A、实现业务逻辑
客户下订单订购商品,商品表自动减少数量。
在商品表创建删除触发器,删除某商品,自动删除该商品的订单。
创建产品表,有产品编号,产品名称、产品数量和产品价格四列,其中产品编号自增长列,并设置成主键。
create table product
(
pid int PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10),
price DOUBLE,
pnum INT
)ENGINE=innoDB default CHARSET=utf8;
创建订单表,有三列,订单编号、产品编号和数量,其中订单编号自增长列,并设置成主键。
create table orders
(
oid INT PRIMARY KEY AUTO_INCREMENT,
pid INT,
onum INT
)ENGINE=innoDB DEFAULT CHARSET=utf8;
插入三种产品,产品名称和数量以及价格
insert into product(pname, pnum, price)values('桃子', 100, 2);
insert into product(pname, pnum, price)values('苹果', 80, 8);
insert into product(pname, pnum, price)values('香蕉', 50, 5);
在订单表上创建触发器,当有订单,会根据订单的产品编号和数量自动减少产品的数量。触发器中NEW代表一个表,存放插入的订单记录。
create trigger trigger_order
AFTER INSERT ON orders FOR EACH ROW
BEGIN
UPDATE product SET pnum=pnum-NEW.onum where pid = NEW.pid;
END
插入两个订单
INSERT INTO orders(pid, onum)VALUES(1, 10);
INSERT INTO orders(pid, onum)VALUES(2, 5);
查看产品表,可以看到对应的产品数量减少。操作由订单表的Insert触发器完成。
在订单表上创建新的触发器,当订单定的某产品产品数量大于产品库存,禁止下订单,也就是禁止在订单表中插入记录。
一张表中只能有一个INSERT类型的触发器,先删除INSERT触发器。
drop trigger trigger_order;
MySQL不能在触发器中通过回滚事务取消操作,但如果触发器的SQL语句执行过程中出现错误,会自动撤销操作,曲线实现事务回滚。
create trigger trigger_order
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
DECLARE var int;
DECLARE mesg varchar(20);
SELECT pnum INTO var FROM product where pid=NEW.pid;
IF var<NEW.onum
THEN SELECT XXXX INTO mesg;
ELSE
UPDATE product SET pnum=pnum-NEW.onum where pid=NEW.pid;
END IF;
END
插入订单,看看如果库存不够是否还能够插入成功。
INSERT INTO orders(pid, onum)VALUES(1, 110);
B、实现安全
1、限制插入记录的日期
在订单表上创建插入触发器,周六周日不允许下订单。
create trigger trigger_limitDate
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
DECLARE mesg varchar(10);
IF DAYNAME(now())='sunday' or DAYNAME(now())='saturday'
THEN SELECT XXXXX INTO mesg;
ELSE
SET mesg='允许插入订单';
END IF;
END
验证上面创建的触发器是否工作正常,看看当前时间是否是周六周日,向订单表插入记录,检查是否能够成功。
insert into orders(pid,onum) values (3,30);
2、限制数据更改的范围
在产品表上创建更新触发器,限制产品价格一次上调不能超过20%。
触发器设置成before update,在更改前检查价格增长幅度是否超过20%,如果超过就产生错误,取消操作。
更新操作分为两步,第一步是删除原来的记录,第二步是插入新记录。原来的记录在old表中,新记录在new表中。触发器中new.price存放的是新价格,old.price是原来的价格。
create trigger trigger_limitIncreasePrice
BEFORE UPDATE ON product FOR EACH ROW
BEGIN
DECLARE mesg varchar(10);
if (NEW.price-OLD.price)*100/OLD.price > 20
then select XXXX into mesg;
else
set mesg='更改成功';
end if;
END
验证触发器
update product set price=20 where pid=1;
C、实现数据完整性
使用触发器可以限制表插入某列的数值范围。
创建一个学生表,有四列,姓名、性别、手机和邮箱。
create table personinfo
(
sname VARCHAR(5),
sex CHAR(1),
phone VARCHAR(11)
)ENGINE=innoDB default CHARSET=utf8;
1、指定性别列的取值范围
创建触发器,限制性别列,只允许输入“男”和“女”。before insert触发器,不满足条件执行有错误的SQL语句,退出。
create trigger trigger_limitSex
before insert on personinfo for each row
begin
declare mesg varchar(10);
if NEW.sex='男' or NEW.sex='女'
then
set mesg='更改成功';
else
select xxxx into mesg;
end if;
End
验证触发器
insert into personinfo VALUES('孙悟空', '难','18900000000');
insert into personinfo VALUES('唐僧', '男','18900000001');
2、限制手机列的取值类型和长度
创建触发器,只允许phone列输入的手机号只能是11位数字,且第一位数字是1。
create trigger trigger_limitPhone
before insert on personinfo for each row
begin
declare mesg varchar(10);
if NEW.phone regexp '[1][0-9]{10}'
then set mesg='插入成功';
else
select xxxx into mesg;
end if;
End
验证触发器,如果手机列插入的值位数不对或者第一位不是1,插入都将失败。
insert into personinfo VALUES('唐僧', '男','2890000001');
使用触发器实现对personinfo表数据操作的跟踪,将跟踪事件记录到一张审计表中review。
create table review
(
username VARCHAR(20),
action VARCHAR(10),
studentID CHAR(5),
sname CHAR(10),
actionTime TIMESTAMP
);
1、创建触发器记录插入操作
create trigger trigger_insert
before insert on personinfo for each row
begin
insert into review values(user(),'insert',new.sname,now());
End
插入personinfo表一条记录
insert into personinfo values('孙悟空', '男', '13008080808');
查看review表中增加的INSERT记录select * from review
2、创建触发器记录删除操作
create trigger trigger_delete
after DELETE on personinfo for each row
begin
insert into review values(user(),'delete',old.sname,now());
End
从personinfo删除一条记录
delete from personinfo where sname='孙悟空';
查看reivew表中增加的DELETE记录
3、创建触发器记录修改操作
create trigger trigger_update
after UPDATE on personinfo for each row
begin
insert review values(user(),'update',new.sname,now());
End
更新personinfo表中名字为‘孙悟空’的phone。
update personinfo set phone='189080808' where sname='孙悟空';
查看reivew表中增加的UPDATE记录