数据库安全性定义与检查
实验3:数据库安全性定义与检查
实验3.1实体完整性实验
(1)实验目的
掌握实体完整性的定义和维护方法。
(2)实验内容和要求
定义实体完整性,删除实体完整性。能够写出两种方式定义实体完整性的SQL语句:创建表时定义实体完整性、创建表后定义实体完整性。设计SQL语句验证完整性约束是否起作用。
(3)实验重点和难点
实验重点:创建表时定义实体完整性。
实验难点:有多个候选码时实体完整性的定义。
(3.1.1)单个候选码的完整性定义:
-- ---------------------------- -- Table structure for food -- ---------------------------- DROP TABLE IF EXISTS `food`; CREATE TABLE `food` ( `food_id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '美食ID', `food_name` varchar(20) NOT NULL COMMENT '美食名称', `food_num` int(10) NOT NULL COMMENT '美食被多少篇游记提到', `city_id` int(10) NOT NULL COMMENT '城市ID', -- 单个候选码 定义表时创建。 -- PRIMARY KEY (`food_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
|
(3.1.2)多个候选码的完整性定义:
在表定义完成后创建:
alter table user add constraint p_key primary key(id,name);
-- ---------------------------- -- Table structure for food -- ---------------------------- DROP TABLE IF EXISTS `food`; CREATE TABLE `food` ( `food_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '美食ID', `food_name` varchar(20) NOT NULL COMMENT '美食名称', `food_num` int(10) NOT NULL COMMENT '美食被多少篇游记提到', `city_id` int(10) NOT NULL COMMENT '城市ID', -- 多个候选码 定义表时创建。 PRIMARY KEY (`food_id`,’food_name’) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
|
(3.1.3)在表创建完成后,定义实体完整性
sql语句格式如下:
alter table 表名
add constraint 主键名称 primary key (字段)
在上述例子中:
添加完整性约束:
alter table food add constraint p_key primary key(food_id,food_name);
删除完整性约束:alter table table_name drop primary key;
(3.1.4)对主键定义的检查
user表已有数据
实验3.2 参照完整性实验
(1)实验目的
掌握参照完整性的定义和维护方法。
(2)实验内容和要求
定义参照完整性,定义参照完整性的违约处理,删除参照完整性。写出两种方式定义参照完整性的SQL语句:创建表时定义参照完整性、创建表后定义参照完整性。
(3)实验重点和难点
实验重点:创建表时定义参照完整性。
实验难点:参照完整性的违约处理定义。
(3.2.1)创建一个参照完整性
在创建表单时定义参照完整性:
CONSTRAINT 约束名称 FOREIGN KEY (字段名) REFERENCES 表单名 (字段名)
-- ---------------------------- -- Table structure for food -- ---------------------------- DROP TABLE IF EXISTS `food`; CREATE TABLE `food` ( `food_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '美食ID', `food_name` varchar(20) NOT NULL COMMENT '美食名称', `food_num` int(10) NOT NULL COMMENT '美食被多少篇游记提到', `city_id` int(10) NOT NULL COMMENT '城市ID', PRIMARY KEY (`food_id`,’food_name’), CONSTRAINT con_key FOREIGN KEY (city_id) REFERENCES city (city_id) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
|
(3.2.2)定义一个参照完整性的检查
--检查约束
alter table food check constraint con_key;
(3.2.3)创建表后的定义和删除操作
定义外键:
alter table food add constraint con_key FOREIGN KEY(city_id) REFRENCES city(city_id);
删除外键:
alter table food drop con_key;
实验3.3 用户自定义完整性实验
(1)实验目的
掌握用户自定义完整性的定义和维护方法。
(2)实验内容和要求
针对具体应用语义,选择NULL/NOT NULL、DEFAULT,UNIQUE、CHECK等,定义属性上的约束条件。
(3)实验重点和难点
实验重点:NULL/NOT NULL, DEFAULT。
实验难点:CHECK。
CREATE TABLE `spot` ( `spot_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '景点ID', `spot_name` varchar(20) NOT NULL COMMENT '景点名称', `city_id` int(10) NOT NULL COMMENT '城市ID', `spot_lat` double(10,6) NOT NULL COMMENT '景点经度', `spot_lng` double(10,6) NOT NULL COMMENT '景点纬度', `spot_num` int(10) NOT NULL DEFAULT '0' COMMENT '景点被访问次数', `spot_ticket` varchar(255) DEFAULT NULL COMMENT '景点门票', `spot_timeref` varchar(20) DEFAULT NULL COMMENT '景点用时参考', `spot_opentime` varchar(255) DEFAULT NULL COMMENT '景点开放时间', `spot_conference` varchar(255) DEFAULT NULL COMMENT '关联景点', PRIMARY KEY (`spot_id`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; |
(3.3.1)用户自定义完整性约束示例:
(3.3.2)定义CHECK约束
定义表时的定义的CHECK约束:
constraint ck_age check (id<10);
constraint ck_tel check (LENGTH(tel)=11);
定义表后新增的CHECK约束:
但是在结果检查中发现Mysql中的CHECK语句存在无效化:
上网查找资料后发现Mysql中的CHECK语句是会被忽略的,所以一般用触发器或者数量较少时用枚举来限定数据。
例子:
(1)触发器
DELIMITER $$
CREATE TRIGGER TestField1_BeforeInsert BEFORE INSERT ON user
FOR EACH ROW
BEGIN
IF NEW.id > 0 THEN
SEca = 0;
END IF;
END$$
又因为id是主键,id等于0已有的情况下,语句都会插入不成功。
(2)枚举
create table user (sex enum('男','女'),b int,id int,primary key(id));
这样子表单sex只允许插入’男’或者’女’。