约束和分页

Love The Way You Lie 2022-07-15 10:43 106阅读 0赞

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

# **约束** #

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。 
    </div> 
    <div>
      约束是表级的强制规定 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过ALTERTABLE 语句 ） 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## **常用约束** ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div> 
     <span style="font-size:16px">①not null (非空), 只能用于列级约束</span> 
    </div> 
    <div> 
     <span style="font-size:16px">②unique(唯一)</span> 
    </div> 
    <div> 
     <span style="font-size:16px">③primary key(主键--非空且唯一) 表中只允许一个主键</span> 
    </div> 
    <div> 
     <span style="font-size:16px">④foreign key(外键--引用别的表的数据)</span> 
    </div> 
    <div> 
     <span style="font-size:16px">⑤check, 只能用于列级约束</span> 
    </div> 
    <div> 
     <span style="font-size:16px">⑥default 缺省值</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

## **约束分类** ##

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      根据约束数据列的限制： 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">单列约束</span>：每个约束只约束一列 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">多列约束</span>：每个约束可约束多列数据 
    </div> </td> 
  </tr> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      根据约束的作用范围 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">列级约束</span>只能作用在一个列上，跟在列的定义后面 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">表级约束</span>可以作用在多个列上，不与列一起，而是单独定义 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

### **NOT NULL 约束** ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。 
    </div> 
    <div>
      Null类型特征： 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp; 所有的类型的值都可以是null，包括int、float等数据类型 
    </div> 
    <div>
      &nbsp; &nbsp; &nbsp; 空字符串""不等于null，0也不等于null 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div> 
     <strong>NOT NULL约束使用:</strong> 
    </div> 
    <div> 
     <strong>①直接在目标列定义后添加NOT NULL</strong> 
    </div> 
    <div> 
     <strong>②ALTER TABLE 表名</strong> 
    </div> 
    <div> 
     <strong>&nbsp; &nbsp; &nbsp; &nbsp; MODIFY &nbsp;列定义</strong> 
    </div> 
    <div> 
     <strong>③使用MODIFY还可以取消列约束</strong> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

创建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 约束** ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      同一个表可以有多个唯一约束，多个列组合的约束。 
    </div> 
    <div>
      在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 
    </div> 
    <div>
      MySQL会给唯一约束的列上默认创建一个唯一索引 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

CREATE TABLE USER(

id INT NOT NULL,

NAME VARCHAR(25),

PASSWORD VARCHAR(16),

\#使用表级约束语法

CONSTRAINT uk\_name\_pwd UNIQUE(NAME,PASSWORD)

);

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div> 
     <span style="font-size:16px">表级约束，在列定义完后，使用关键字进行定义</span> 
    </div> 
    <div> 
     <span style="font-size:16px">语法：约束关键字(列名) &nbsp;&nbsp;</span> 
     <span style="font-family:Monaco,Menlo,Consolas,'Courier New',monospace"><span style="color:rgb(255,0,0)">CONSTRAINT uk_name_pwd 可以省略</span></span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

添加唯一约束

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;

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      删除 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      &nbsp; &nbsp;&nbsp; 
     <span style="color:rgb(255,0,0)">DROP</span>&nbsp;index &nbsp;‘列名’ 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

删除唯一约束

ALTER TABLE USER

DROP INDEX uk\_name\_pwd;

\-----------------------------

ALTER TABLE USER

DROP INDEX 'NAME,PASSWORD';

### **PRIMARY KEY 约束** ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值 
    </div> 
    <div>
      如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。 
    </div> 
    <div>
      每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。 
    </div> 
    <div>
      MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

添加主键约束

列级

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)

);

删除主键约束

ALTER TABLE emp5

DROP PRIMARY KEY;

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

ALTER TABLE emp5

ADD PRIMARY KEY(NAME,pwd);

修改主键约束

ALTER TABLE emp5

MODIFY id INT PRIMARY KEY;

### **FOREIGN KEY 约束** ###

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 
    </div> 
    <div>
      从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。 还有一种就是级联删除子表数据。 
    </div> 
    <div>
      注意： 
     <span style="color:rgb(255,0,0)">外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列</span> 
    </div> 
    <div>
      同一个表可以有多个外键约束 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

创建外键约束

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)

); 从表

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      在非定义的时候添加,丢弃外键约束语法 
     <br> &nbsp;alter table 表名 
     <br> &nbsp;add [constraint stu_fk] foreign key(classid) references class(id); 
     <br> &nbsp; 
     <br> &nbsp;alter table 表名 
    </div> 
    <div>
      &nbsp;drop foreign key 外键名; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

删除外键约束

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 约束的关键字

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      FOREIGN KEY: 在表级指定子表中的列 
    </div> 
    <div>
      REFERENCES: 标示在父表中的列 
    </div> 
    <div>
      ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除 
    </div> 
    <div>
      ON DELETE SET NULL(级联置空): 子表中相应的列置空 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      设置级联删除 
    </div> 
    <div>
      语法： 
    </div> 
    <div>
      设置外键，在删除母表记录时，子表记录会被级联，也被删除 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      &nbsp;&nbsp; add [constraint 键名] foreign key(子列引用的列) references 母表(母表主键) on delete cascade on update cascade; 
    </div> 
    <div>
      &nbsp;&nbsp; 
    </div> 
    <div>
      设置外键，在删除母表记录时，子表记录中的引用列被置为null 
    </div> 
    <div>
      alter table 表名 
    </div> 
    <div>
      &nbsp; &nbsp; add [constraint 键名] foreign key(子列引用的列) references 母表(母表主键) on delete set null on update set null; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

级联删除

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 约束** ###

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;

# **分页** #

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:99.88776655443323%"> 
    <div>
      MySQL中使用limit实现分页 
    </div> 
    <div> 
     <br> 
    </div> 
    <div>
      limit 数值1,数值2 
    </div> 
    <div>
      数值1表示略过的记录，数值2表示显示的记录数。 
    </div> 
    <div> 
     <span style="color:rgb(255,0,0)">公式：limit (pageNo - 1) * pageSize, pageSize</span> 
    </div> 
    <div>
      limit子句必须放在整个查询语句的最后！&nbsp; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

<table style=""> 
 <tbody> 
  <tr> 
   <td style="border-style:solid; border-width:1px; border-color:rgb(211,211,211); padding:10px; margin:0px; width:100%"> 
    <div>
      select 
    </div> 
    <div>
      &nbsp; &nbsp; * 
    </div> 
    <div>
      from 
    </div> 
    <div>
      &nbsp; &nbsp; 表名 
    </div> 
    <div>
      where limit (pageNo - 1) * pageSize, pageSize; 
    </div> </td> 
  </tr> 
 </tbody> 
</table>