学习MYSQL笔记分享(五)——数据完整性与表的维护

Valky
阅读15 喜欢0 教程 更新2019-11-26

一、数据完整性约束 

定义了完整性约束,MYSQL服务器会随时检测处于更新状态的数据内容是否符合相关的完整性约束,保证数据的一致性与正确性。 


定义完整约束性 

(1)实体完整性 

实体完整性规则指关系的主属性,即主码的组成不能为空,关系的主属性不能为NULL a.主键约束 每个表只能定义一个主键 键值唯一且不能包含不必要的多余列 一个列名在复合主键的列表中只能出现一次 列的完整性约束:在表中某个列的属性定义后加上PRIMARY KEY

CREATE TABLE ucstomers(
    cust_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cust_name char(50) NOT NULL,
    cust_sex int (1) NOT NULL DEFAULT 0,
    cust_addres chars(50) NULL
)
ENGINE=InnoDB;

表的完整性约束:在表中所有列的属性定义后加PRIMARY KEY(index_col_name,...),主键由表中多列构成只能用这种方法

CREATE TABLE customers(
    cust_id int NOT NULL AUTO_INCREMENT,
    cust_name char(50) NOT NULL,
    cust_sex int (1) NOT NULL DEFAULT 0,
    cust_addres chars(50) NULL,
    PRIMARY KEY (cust_id)
)
ENGINE=InnoDB;

b.候选键的约束 候选键值必须唯一,且不能为NULL 候选键与主键之间的区别: 一个表只能有一个主键 ,可以有若干个候选键 定义主键约束时自动产生PRIMARY KEY 索引,定义候选键约束时自动产生UNIQUE索引 


(2)参照完整性 参照完整性规则是定义外码和主码之间的引用规则,是对关系间引用数据的一种限制,外码要么空,要么参照关系中的主码值 教师(职工号,姓名,性别,系编号) 系(系编号,系名,办公地点) reference_option:参照完整性约束的实现策略如下: - RESTRICT:限制策略,默认使用,违反即拒绝 - CASCADE:级联策略,被参照表中删除或更新记录行时自动删除或更新参照表中的匹配记录行 - SET NULL - NO ACTION

 
CREATE TABLE orders(
    order_id int NOT NULL AUTO_INCREMENT,
    order_product char(50) NOT NULL,
    cust_id int NOT NULL,
    order_amount int NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGH KEY (cust_id) REFERENCES customers(cust_id)
    ON DELETE RESTRICT ON UPDATE RESTRICT
);

注意:被参照表必须存在且有主键 被参照表名指定的列或列的组合必须是按照参照表的主键或侯先健 外键允许出现空值 外键列的数目、数据类型必须与被参照的列的数据、数据类型相同 


(3)用户定义的完整性 用户定义的完整性:非空约束、CHECK约束、触发器 a.非空约束 NOT NULL b.CHECK约束 CHECK(expr)


CREATE TABLE orders(
    order_id int NOT NULL AUTO_INCREMENT,
    order_product char(50) NOT NULL,
    cust_id int NOT NULL,
    CHECK(cust_id IN (SELLECT cust_id FROM customers)),
    order_amount int NOT NULL,
    PRIMARY KEY (order_id)
    );
CREATE TABLE orders(
    order_id int NOT NULL AUTO_INCREMENT,
    order_product char(50) NOT NULL,
    cust_id int NOT NULL,
    order_amount int NOT NULL,
    order_price DOUBLE NOT NULL,
    PRIMARY KEY (order_id),
    CHECK(order_amount<=5 AND order_price>=2000)
    );
命名完整性约束
CONSTRAINT [symbol]
CREATE TABLE orders(
    order_id int NOT NULL AUTO_INCREMENT,
    order_product char(50) NOT NULL,
    cust_id int NOT NULL,
    order_amount int NOT NULL,
    order_price DOUBLE NOT NULL,
    CONSTRAINT PRIMARY_KEY_ORDERS PRIMARY KEY(order_id),
    CONSTRAINT FOREIGN_KEY_ORDERS FOREIGN KEY(order_id),
    REFERENCES customers(cust_id)
   ON DELETE RESTRICT ON UPDATE RESTRICT
    )
    //主键约束命名为PRIMARY_KEY_ORDERS,并将它的外键说明命名为FOREIGN_KEY_ORDERS


更新完整性约束

ALTER TABLE orders
DROP CONSTRAINT FOREIGN_KEY_ORDERS;
//删除表orders的参照完整性约束FOREIGN_KEY_ORDERS

添加约束:ALTER TABLE....AND CONSTRAINT... 二、表维护语句 1. ANALYZE TABLE语句:更新、修复索引散列程度

ANALYZE TABLE mysql_test.customers;
  1. CHECK TABLE语句:检查一个或多个表是否错误
CHECK TABLE mysql_test.customers;
  1. CHECKSUM TABLE语句:保证数据一致
CHECKSUM TABLE mysql_test.customers;
  1. OPTIMIZE TABLE语句:重新利用未使用的空间,整理数据文件的碎片
OPTIMIZE TABLE mysql_test.customers;//优化数据库中的表
5.REPAIRE TABLE语句:修复一个或多个损坏的表
REPAIRE TABLE  mysql_test.customers;