mysql系列教程(十一)—–mysql约束和分页

MySQL

本类目为大家讲解mysql相关知识,本节讲解MySQL约束和分页

约束

什么是约束?

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

约束的分类

MySQL不支持check约束,但可以使用check约束,而没有任何效果

  • NOT NULL 非空约束,规定某个字段不能为空
  • UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
  • PRIMARY KEY 主键该字段在表中非空且唯一
  • FOREIGN KEY 外键
  • CHECk 检查约束
  • DEFAULT 默认值

约束的划分

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

NOT NULL 约束

  • 非空约束用于确保当前列的值不为空值,非空约束只能出现在表对象的列上。
  • Null类型特征:
    • 所有的类型值都可以是null,包括int、float等数据类型
    • 空字符串“”不等于null,0也不等于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;
#取消not null约束,增加默认值

UNIQUE约束

  • 唯一约束,允许出现多个空值:NULL
  • 同一个表可以有多个唯一约束,多个列组合的约束。
  • 在创建唯一约束的时候,如果不给唯一约束名称,就默认和列名相同。
  • MySQL会给唯一约束的列上默认创建一个唯一索引
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 USER 
DROP INDEX uk_name_pwd;
#删除约束

PRIMARY KEY约束

  • 主键约束相当于唯一约束+非空约束的组合,主键约束不允许重复,也不允许出现空值
  • 如果是多列组合的主键约束,name这些列都不允许为空值,并且组合的值不允许重复。
  • 每个表最多只允许一个主键,建立主键约束可以在列级别创建,也可以在表级别上创建。
  • 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)
);
#组合主键
ALTER TABLE emp5
DROP PRIMARY KEY;
#删除主键
ALTER TABLE emp5
ADD PRIMARY KEY(NAME,pwd);
#添加主键
ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;
#修改主键

FOREIGN KEY约束

  • 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
  • 从表的外键值必须在主表中能找到或者为空。空主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据
  • 还可以级联删除子表数据
  • 注意:外键约束的参照列,在主表中引用的只能是主键或唯一键约束的列
  • 同一个表可以有多个外键约束
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 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(级联置空):子表中相应的列置空

CHECK约束

  • MySQL可以使用check约束,但check约束对数据验证没有任何作用,添加数据时,没有任何错误或警告
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

分页

MySQL中使用limit实现分页

(当前页数-1)*每页条数,每页条数
SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

注意:limit子句必须放在整个查询语句的最后!

本节介绍到这里,欢迎小伙伴指正补充!

发表评论