MySQL(08)—约束和分页

约束和分页

1. 描述约束

1.1 什么是约束

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

• 有以下六种约束
  • NOT NULL 非空约束，规定某个字段不能为空
  • UNIQUE 唯一约束，规定某个字符在整个表中是唯一的
  • PRIMARY KEY 主键（非空且唯一）
  • FOREIGN KEY 外键
  • CHECK 检查约束
  • DEFAULT 默认值

MySQL不支持check约束，但可以使用check约束，而没有任何效果；具体细节可以参阅W3Cschool手册

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

2. 创建和维护约束

2.1 NOT NULL约束

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

1. 创建not null约束

   create table emp(
   id int(10) not null,
   name varchar(20) not null default 'abc',
   sex char null
   );

2. 增加not null约束

   alter table emp
   modify sex varchar(30) not null;

3. 取消not null约束

   alter table emp
   modify sex varchar(30) null;

4. 取消not null约束，增加默认值

   alter table emp
   modify name varchar(15) default 'abc' null;

2.2 UNIQUE约束

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

• MySQL回给唯一约束的列默认创建一个唯一的索引

  create table user(
  	id int not null,
      name varchar(25),
      password varchar(16),
      #使用表级约束语法
      constraint uk_name_pwd unique(name,password)
  );

  表示用户名和密码组合不能重复

1. 添加唯一约束

   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;

2. 删除约束

   alter table user
   drop index uk_name_pwd;

2.3 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 emp6_pk primary key(name,pwd)
);

1. 删除主键约束

   alter table emp5
   drop primary key;

2. 添加主键约束

   alter table emp5
   add primary key(name,pwd);

3. 修改主键约束

   alter table emp5
   modify id int primary key;

2.4 FOREIGN KEY约束

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

1. 创建外键约束

   #主表
   create table dept(
   	dept_id int auto_increment primary key,
       dept_name varchar(20)
   );

   #从表
   create table emp(
   	emp_id into auto_increment primary key,
       last_name varchar(15),
       dept_id int,
       constraint emp_dept_id_fk foreign key(dept_id) 
       references dept(dept_id)
   );

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

   #主表
   create table calsses(
   	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)
   );

3. 删除外键约束

   alter table emp
   drop foreign key emp_dept_id_fk;

4. 增加外键约束

   alter table emp
   add [constraint emp_dept_id_fk] foreign key(dept_id)
   references dept(dept_id);

5. FOREIGN KEY约束的关键字

   FOREIGN KEY：在表级指定子表中的列

   REFERENCES：标示在父表中的列

   ON DELETE CASCADE(级联删除)：当父表中的列被删除时，子表中相对应的列也被删除

   ON DELETE 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
   );

2.5 CHECK约束

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

create table temp(
	id int auto_increment,
    name varchar(20),
    age int check(age > 20),
    primary key(id)
);

3. 数据库分页

• 背景
  • 查询返回的记录太多，查看起来不方便
• 分页原理
  • 所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件

3.1 使用limit实现分页

• 怎么分段，当前在第几段（每页有几条，当前在第几页）

  • 前10条记录：SELECT * FROM table LIMIT 0,10;
  • 第11至20条记录：SELECT * FROM table LIMIT 10,10;
  • 第21至30条记录： SELECT * FROM table LIMIT 20,10;

• 公式：（当前页数-1）*每页条数，每页条数

  SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

• 注意：

  • limit子句必须放在整个查询语句的最后