约束和分页

本篇介绍了MySQL中的各种约束，怎么使用这些约束，如何使用limit进行分页

约束

为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。 约束是表级的强制规定 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过ALTERTABLE 语句 ）

常用约束

①not null (非空), 只能用于列级约束 ②unique(唯一) ③primary key(主键--非空且唯一) 表中只允许一个主键 ④foreign key(外键--引用别的表的数据) ⑤check, 只能用于列级约束 ⑥default 缺省值

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；

约束分类

根据约束数据列的限制： 单列约束：每个约束只约束一列 多列约束：每个约束可约束多列数据

根据约束的作用范围 列级约束只能作用在一个列上，跟在列的定义后面 表级约束可以作用在多个列上，不与列一起，而是单独定义

NOT NULL 约束

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。 Null类型特征：       所有的类型的值都可以是null，包括int、float等数据类型       空字符串""不等于null，0也不等于null

NOT NULL约束使用: ①直接在目标列定义后添加NOT NULL ②ALTER TABLE 表名         MODIFY  列定义 ③使用MODIFY还可以取消列约束

创建NOT NULL 约束

CREATE TABLE emp(

    id INT(10) NOT NULL,

    NAME VARCHAR(20) NOT NULL DEFAULT 'abc',

    sex CHAR NULL

);

增加NOT NULL约束

ALTER TABLE emp

    MODIFY sex VARCHAR(30) NOT NULL;

取消NOT NULL约束

ALTER TABLE emp

    MODIFY sex VARCHAR(30) NULL;

取消NOT NULL约束，并添加默认值

ALTER TABLE emp

    MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

UNIQUE 约束

唯一约束，值不能重复，但是允许出现多个空值：NULL。

同一个表可以有多个唯一约束，多个列组合的约束。 在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引

使用表级约束，设置唯一约束。用户名和密码不能重复

CREATE TABLE USER(

 id INT NOT NULL,

 NAME VARCHAR(25),

 PASSWORD VARCHAR(16),

 #使用表级约束语法

 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)

);

表级约束，在列定义完后，使用关键字进行定义 语法：约束关键字(列名)    CONSTRAINT uk_name_pwd 可以省略

在非定义的时候给某表添加,丢弃表级的唯一约束

添加 alter table 表名     add unique(列名);

添加唯一约束

ALTER TABLE USER

    ADD UNIQUE(NAME,PASSWORD);

-------------------------------

ALTER TABLE USER

    ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

-------------------------------

ALTER TABLE USER

    MODIFY NAME VARCHAR(20) UNIQUE;

删除 alter table 表名      DROP index  ‘列名’

删除唯一约束

ALTER TABLE USER

    DROP INDEX uk_name_pwd;

-----------------------------

ALTER TABLE USER

    DROP INDEX 'NAME,PASSWORD';

PRIMARY KEY 约束

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。 MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

添加主键约束

列级

CREATE TABLE emp4(

    id INT AUTO_INCREMENT PRIMARY KEY,

    NAME VARCHAR(20)

);

表级

CREATE TABLE emp5(

    id INT NOT NULL AUTO_INCREMENT,

    NAME VARCHAR(20),

    pwd VARCHAR(15),

    CONSTRAINT emp5_id_pk PRIMARY KEY(id)

);

组合

CREATE TABLE emp6(

    id INT NOT NULL,

    NAME VARCHAR(20),

    pwd VARCHAR(15),

    CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)

);

定义表级约束的时候 CONSTRAINT emp7_pk可以省略

删除主键约束

ALTER TABLE emp5

    DROP PRIMARY KEY;

添加主键约束(表建立后添加)

ALTER TABLE emp5

      ADD PRIMARY KEY(NAME,pwd);

修改主键约束

ALTER TABLE emp5

      MODIFY id INT PRIMARY KEY;

FOREIGN KEY 约束

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。 还有一种就是级联删除子表数据。 注意： 外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列 同一个表可以有多个外键约束

外键创建方式： [constraint 外键名] foreign key(子表的引用列) references 母表(母表的主键);

创建外键约束

CREATE TABLE dept(

    dept_id INT AUTO_INCREMENT PRIMARY KEY,

    dept_name VARCHAR(20)

);                                             主表

------------------------------------------

CREATE TABLE emp(

    emp_id INT AUTO_INCREMENT PRIMARY KEY,

    last_name VARCHAR(15),

    dept_id INT,

    CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id)

    REFERENCES dept(dept_id)

);                                             从表

创建多列外键组合，必须使用表级约束

CREATE TABLE classes(

    id INT,

    NAME VARCHAR(20),

    number INT,

    PRIMARY KEY(NAME,number)

);                                             主表

------------------------------------------    

CREATE TABLE student(

    id INT AUTO_INCREMENT PRIMARY KEY,

    classes_name VARCHAR(20),

    classes_number INT,

    FOREIGN KEY(classes_name,classes_number)

    REFERENCES classes(NAME,number)

);                                             从表 

当主键约束为两个列时只有两个列连接值一样的时候才会违反约束。

在非定义的时候添加,丢弃外键约束语法  alter table 表名  add [constraint stu_fk] foreign key(classid) references class(id);    alter table 表名  drop foreign key 外键名;

删除外键约束

ALTER TABLE emp

    DROP FOREIGN KEY emp_dept_id_fk;

增加外键约束

ALTER TABLE emp

    ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)

    REFERENCES dept(dept_id);

FOREIGN KEY 约束的关键字

FOREIGN KEY: 在表级指定子表中的列 REFERENCES: 标示在父表中的列 ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除 ON DELETE SET NULL(级联置空): 子表中相应的列置空

设置级联删除 语法： 设置外键，在删除母表记录时，子表记录会被级联，也被删除 alter table 表名    add [constraint 键名] foreign key(子列引用的列) references 母表(母表主键) on delete cascade on update cascade;    设置外键，在删除母表记录时，子表记录中的引用列被置为null alter table 表名     add [constraint 键名] foreign key(子列引用的列) references 母表(母表主键) on delete set null on update set null;

级联删除

alter table classes

    add constraint my_fk foreign key(master) references teachers(id) on delete cascade on update cascade;

级联置null

alter table classes

    add constraint my_fk foreign key(master) references teachers(id) on delete set null on update set null;

级联删除设置

CREATE TABLE student(

    id INT AUTO_INCREMENT PRIMARY?KEY,

    NAME VARCHAR(20),

    classes_name VARCHAR(20),

    classes_number INT,

    /*表级别联合外键*/

    FOREIGN?KEY(classes_name, classes_number)

    REFERENCES classes(NAME, number) ON DELETE CASCADE);

CHECK 约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告

CREATE TABLE temp(

    id INT AUTO_INCREMENT,

    NAME VARCHAR(20),

    age INT CHECK(age > 20),

    PRIMARY KEY(id)

);

 设置主键约束，并测试违反约束情况

create table test(

        id int auto_increment,

        name varchar(50) not null,

        idcard varchar(20),

        address varchar(50),

        unique(idcard),

        primary key(id, name)

);

当违反约束的时候插入不成功，会报错。

---------------------------------

先插入数据：

insert into

     test(id,name,idcard,address)

values (1,'aaa','12213123','beijing'),

       (1,'bbbb','232423','shanghai');

---------------------------------

下面的记录无法插入，违反主键约束

insert into

     test(id,name,idcard,address)

values (1,'aaa','12213123','beijing');

-----------------------------------

无法插入，违反非空约束

insert into

    test (id,name,idcard,address)

values (2,null,'12213123','beijing');

-----------------------------------

违反唯一约束

insert into

    test(id,name,idcard,address)

values (2,'cccc','12213123','beijing');

设置外键约束，并测试违法外键约束

      

教师表，班主任要引用老师表的主键。

create table classes (

    id int auto_increment,

    name varchar(20) unique,

    master int,

    classroom int not null,

    begindate datetime,

    primary key(id),

    foreign key(master) references teachers(id)

);

-----------------------------------------------

插入4值时，需要先到母表中进行检索,若可以找到数据，插入成功

insert into

     classes (name, master, classroom, begindate)

values ('H50826', 4, 232, now());

------------------------------------------------ 

插入400值时, 需要先到母表中进行检索, 如果没有找到数据,则插入失败

insert into

     classes (name, master, classroom, begindate)

values ('JavaEE0826', 400, 333, now());

-------------------------------------------------

插入成功

insert into

     classes (name, master, classroom, begindate)

values ('Android0826', 4, 333, now());

----------------------------------------------------

插入成功

insert into

     classes (name, master, classroom, begindate )

values ('JavaEE1117', 1, 101, '2016-7-17');

-----------------------------------------------------

当母表中的记录被引用时, 不能删除这条记录 先把子表中的相应记录删除,才能再删除这条记录

insert into

    teachers (id, name, gender, age, phone, birthday, home)

values (4, '奈老师', '女', 40, '135234234234', '1976-5-2 10:1:1', '北京京州32号'); 

把子表的外键约束丢弃

alter table classes

    rop foreign key classes_ibfk_1;

分页

MySQL中使用limit实现分页 limit 数值1,数值2 数值1表示略过的记录，数值2表示显示的记录数。 公式：limit (pageNo - 1) * pageSize, pageSize limit子句必须放在整个查询语句的最后！

select     * from     表名 where limit (pageNo - 1) * pageSize, pageSize;