mysql中的null值和空 mysql nullable

一、数据库

    1、数据库支持5种约束：

1. NOT NULL：非空约束，指定某列不为空
2. UNIQUE：唯一约束，指定某列或者几列组合不能重复
3. PRIMARY KEY：主键，指定该列的值可以唯一的标识该条记录
4. FOREIGN KEY：外键，指定该行记录从属于主表中的一个记录，主要用于保证参照完整性
5. CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式

    2、约束分为两类：

1. 单列约束
2. 多列约束

    3、为数据表指定约束有如下两个时机：

1. 建表的同时为相应的数据列指定约束
2. 建表后约束

二、五种约束

1、NOT NULL 非空约束

    非空约束用于确保指定列不允许为空，SQL中的null具有以下特征：

1. 所有数据类型的值都可以是null，包括int、float、boolean等类型
2. 与Java一样，空字符串不等于null，0也不等于null

    建表语句如下：

create table test
(
    #建立非空约束，这意味着不能为null
    Age int not null,
    #MySQL的非空约束不能指定名字
    Age varchar(45) default 'abc' not null,
    #下面列可以为空，默认就是可以为空
    gender varchar(2) null
);

    除此之外，也可以用alter table修改表时增加或删除非空约束

#增加非空约束
alter table test
modify name char(20) not null;
#取消非空约束
alter table test
modify name char(20) null;
#取消非空约束，并设置默认值
alter table test
modify name char(20) default 'abc' null;

2、UNIQUE唯一约束

    唯一约束用于保证指定列或者指定组合不允许出现重复值，但可以出现多个null值。

create  table UNIQUE_Test
(
    #寄哪里非空约束，这意味着UNIQUE_Test_id不能为null
    UNIQUE_Test_id int not null,
    #unique就是唯一约束，使用列级约束建立唯一约束
    UNIQUE_Test_name varchar(45) unique
);

    如果需要为多列组合建立唯一约束，或者想自行指定约束名，则需要使用表级约束语法。

create  table UNIQUE_Test
(
    #寄哪里非空约束，这意味着UNIQUE_Test_id不能为null
    UNIQUE_Test_id   int not null,
    UNIQUE_Test_name char(45),
    UNIQUE_Test_pass char(45),
    #使用表级约束语法建立唯一约束，
    unique (UNIQUE_Test_name),
    #使用表级约束语法建立唯一约束，而且指定约束名
    constraint UNIQUE_Test unique (UNIQUE_Test_pass)
);

    也可以在修改表结构时使用aa关键字来增加唯一约束

alter table UNIQUE_Test
add unique (UNIQUE_Test_name,UNIQUE_Test_pass);

    还可以再修改表时使用modify关键字，为单列采用列级语法来增加唯一约束

alter tableUNIQUE_Test
modify UNIQUE_Test char(45) unique;

    删除唯一约束

alter table UNIQUE_Test
drop index UNIQUE_Test_name;

3、PRIMARY KEY主键约束

    主键约束相当于非空约束和唯一约束，即主键约束的列不允许出现重复值，也不云允许出现null值；如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些组合不能重复。

    建表创建主键约束，使用列级约束语法

create table primary_test
(
    #建立主键约束
    test_id int primary key,
    test_name varchar(255)
);

    建表创建主键约束，使用表级约束语法

create table primary_test
(
    #建立主键约束
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    #指定主键约束名为test_pk，对大部分数据有效，但对mysql无效
    #mysql数据库中该主键约束名依然是primary
    constraint test_pk primary key (test_id)
);

    如果需要删除指定表的主键约束，则在alter table语句后使用drop primary key字句即可

alter table primary_test
drop primary key;

    如果需要为指定表增加主键约束，则可以通过modify修改定义来增加主键约束，这将采用列级约束语法来增加主键约束；也可通过add来增加逐渐约束，这将采用表级约束语法来增加主键约束

#使用表级约束语法
alter table primary_test
add primary key (test_name,test_pass);

    如果只是为单独的数据列增加主键约束，则可使用modify修改列定义来实现

alter table primary_test
  add primary_test_name varchar(255) primary key;

    4、FOREIGN KEY 外键约束

    外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建与一个表的两个字段或者两个表的两个字段之间的参照关系。

    采用列级约束语法建立外键约束直接使用references关键字，references指定该列参照那个主表，以及参照主表的那一列。

#主表
create table foreign_maintest
(
    #auto_increment：代表数据库的自动编号策略，通常用作数据表的逻辑主键
    id int auto_increment,
    name char(45),
    primary key(id)
);
create table foreign_sontest
(
    #为本表建立主键约束
    id int auto_increment primary key ,
    name char(45),
    #指定java_test参照到foreign_maintest的test_id列
    java_test int references foreign_maintest(id)
);

    虽然mysql支持列级约束语法来建立外键约束，但列级约束语法建立的外键约束不会生效，如果要使mysql中的外键约束生效，则应使用表级约束语法

#主表
create table foreign_maintest
(
    #auto_increment：代表数据库的自动编号策略，通常用作数据表的逻辑主键
    id int auto_increment,
    name char(45),
    primary key(id)
);
create table foreign_sontest
(
    #为本表建立主键约束
    id int auto_increment primary key ,
    name char(45),
    #指定java_test参照到foreign_maintest的test_id列
    java_test int,
    foreign key (java_test) references foreign_maintest(id)
);

    如果需要显示指定外键约束的名字，则可使用constraint来指定名字

#主表
create table foreign_maintest
(
    #auto_increment：代表数据库的自动编号策略，通常用作数据表的逻辑主键
    id int auto_increment,
    name char(45),
    primary key(id)
);
create table foreign_sontest
(
    #为本表建立主键约束
    id int auto_increment primary key ,
    name char(45),
    java_test int,
    #使用表级约束语法建立外键约束，指定外界约束的约束名为foreign_test
    constraint foreign_test foreign key (java_test) references foreign_maintest(id)
);

    如果需要建立多列组合的外键约束，则必须使用表级约束语法

#主表
create table foreign_maintest
(
    name varchar(255),
    pass varchar(255),
    #以两列建立组合主键
    primary key (name,pass)
);
create table foreign_sontest
(
    #为本表建立主键约束
    id int auto_increment primary key ,
    name varchar(255),
    java_test_name varchar(255),
    java_test_pass varchar(255),
    #使用表级约束语法建立外键约束，指定两列的联合外键
    foreign key (java_test_name,java_test_pass) references foreign_maintest(name, pass)
);

    删除外键约束

#删除foreign_sontest中名为foreign_sontest_ibfk_1的外键约束
alter table foreign_sontest
drop foreign key foreign_sontest_ibfk_1;

    增加外键约束

alter table foreign_sontest
add foreign key (java_test_name,java_test_pass)references foreign_maintest(maintest_name, maintest_pass);

    5、CHECK 检查

    CHECK约束不会有任何作用

create table check_test
(
    emp_id int auto_increment,
    emp_name varchar(255),
    salary decimal,
    primary key (name,pass)
    #建立check约束
    check(emp_salary>0)
);

三、更新完整性约束

1、删除约束

    DROP TABLE会删除所有完整性，但是并不经常这样做。

    （1）、删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

    （2）、删除主键约束

ALTER TABLE <表名> DROP PRIMARY KEY;

    （3）、 删除候选键约束

ALTER TABLE <表名> DROP {约束名|候选键字段名};

2、添加约束

    创建表时一般会添加约束，如果没有添加的话，后期添加也是可以的。

    （1）、添加主键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] PRIMARY KEY(主键字段);

    （2）、添加外键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] FOREIGNKEY (外键字段) REFERENCES被参照表(主键字段名);

    （3）、添加候选键约束

ALTER TABLE <表名> ADD [CONSTRAINT<约束名>] UNIQUE KEY(字段名);