数据库安全性定义与检查

实验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只允许插入’男’或者’女’。