07-MySQL的列属性--列属性概述+空属性null+列描述comment+默认值default+主键primary key+自动增长auto_increment+唯一键unique key+索引

一、列属性概述

1、真正约束字段的是数据类型，但数据类型的约束很单一，需要一些额外的约束，来更加保证数据的合法性

2、属性是描述表中字段的，一开始就定义好了，不是使用过程中才有的

3、列属性又称之为字段属性。在MySQL中，一共有6个列属性：null/not null、comment、default、primary key、auto_increment、unique key

二、空属性

1、空属性有两个值：null（默认）、not null（不为空）

2、数据库的字段基本都默认为空。但在实际开发中，要尽可能保证所有的数据都不为空。因为空数据没有意义，且空数据无法参与运算

注：任何数据碰到null的结果都是null，null没有意义

    -- 创建表
    create table my_class(
        name varchar(20) not null,
        room varchar(20) null    -- null代表允许为空。不写默认就是允许为空
    )charset utf8;

    -- 查看表结构
    desc my_class;

三、列描述

1、列描述：comment。没有实际意义，是专门用来描述字段的，是帮助开发人员进行维护的注释说明

2、列描述会跟随表创建语句保存，通过查看表创建语句查看列描述

注：和注释的不同之处在于：注释不会写入表中

    -- 创建表
    create table my_teacher(
        name varchar(20) not null comment '姓名',
        money decimal(10, 2) not null comment '工资'
    )charset utf8;

    -- 查看表结构
    desc my_teacher;

    -- 查看表创建语句，查看列描述
    show create table my_teacher;

3、列描述comment存在的价值就是给别人也给自己提供方便，让以后再查看表的人可以很清楚的看到当前字段设计者的用意是什么

四、默认值

1、默认值：default。某一种数据会经常性的出现某个具体的值，可以在一开始就指定好。在需要真实数据的时候，用户可以选择性的使用默认值。或者是 字段被设计时，如果允许默认条件下用户不进行数据的插入，那么就可以使用默认值进行填充，通常填充的是null

    -- 创建表
    create table my_default(
        name varchar(20) not null,
        age tinyint unsigned default 0,    -- default是默认值
        gender enum('男', '女', '保密') default '男'
    )charset utf8;

    -- 查看表结构
    desc my_default;

2、使用默认值

（1）在进行数据插入的时候，不给该字段赋值，就会使用默认值（省略带有默认值的字段）

    -- 插入数据，使用默认值（省略带有默认值的字段）
    insert into my_default (name) values ('小强');

（2）使用default关键字代替值（使用带有默认值的字段，但其值用default代替），显式告知字段使用默认值

注：如果字段很多，为了一个字段不要（默认值）而写了很多字段，得不偿失。但又想让这个字段使用默认值，此时就可以使用default关键字来触发默认值

    -- 插入数据，使用默认值（使用带有默认值的字段，但其值用default代替）
    insert into my_default values ('张三', 18, default);

五、主键

1、主键：primary key。一张表只能有一个字段可以使用主键，用来唯一的约束该字段里面的数据不能重复。即 一张表最多只能有一个主键（非空、唯一）

2、增加主键

      SQL操作中有三种方式可以给表增加主键

（1）方式一：在创建表的时候，直接在字段之后跟"primary key"关键字（主键本身不能为空）

    -- 增加主键
    create table my_pri1(
        name varchar(20) not null comment '姓名',
        number char(10) primary key comment '学号：itcast + xxxx，不能重复'
    )charset utf8;

    -- 查看表结构
    desc my_pri1;

注：此方式的优缺点：

a). 优点：非常直接

b). 缺点：只能使用一个字段作为主键

说明：被系统优化后存储的表创建语句，主键有了额外的一行在其中存储。即 在所有字段之后，加 primary key(主键字段列表)

（2）方式二：在创建表的时候，在所有字段之后，使用"primary key(主键字段列表)"来创建主键。如果有多个字段作为主键，可以是复合主键（复合主键用的不多）

    -- 复合主键
    create table my_pri2(
        number char(10) comment '学号：itcast + xxxx',
        course char(10) comment '课程代码：xxxx + xxxx',
        score tinyint unsigned default 60 comment '成绩',
        -- 增加主键限制：学号和课程代码应该是对应的，具有唯一性
        primary key(number, course)
    )charset utf8;

    -- 查看表结构
    desc my_pri2;

注：

a). 主键的作用：限制数据

b). 一个表中最多只能有一个主键。如果表中出现两个PRI，一定是复合主键

（3）方式三：当表已经创建好之后，额外追加主键。可以通过修改表字段属性，也可以直接追加

    -- 追加主键：通过修改表字段属性
    alter table 表名 modify 字段名 列类型 primary key;

    -- 追加主键：直接追加
    alter table 表名 add primary key(字段列表);

    -- 追加主键
    create table my_pri3(
        course char(10) not null comment '课程编号：xxxx + xxxx',
        name varchar(10) not null comment '课程名称'
    )charset utf8;

    -- 查看表结构
    desc my_pri3;
    
    -- 追加主键
    -- 方式一：通过修改表字段属性
    alter table my_pri3 modify course char(10) primary key comment '课程编号：xxxx + xxxx';
    -- 方式二：直接追加
    alter table my_pri3 add primary key(course);

    -- 查看表结构
    desc my_pri3;

注：此方式有前提：表中字段对应的数据本身是独立的（不重复）。如果数据有重复值，主键插不进去

3、主键约束

（1）主键对应的字段中的数据不能为空

（2）主键对应的字段中的数据不允许重复。一旦重复，数据操作失败（增和改）-- 保证数据的唯一性

4、更新主键&删除主键

（1）没有办法更新主键。主键必须先删除，才能增加

（2）删除主键

    -- 删除主键
    alter table 表名 drop primary key;    -- 可以这样是因为：一个表只有一个主键

注：

（1）一个字段本来是可以为空的。将其设为主键后，该字段变成了非空、唯一。删除主键，只把唯一去掉了，该字段还是非空的，没有恢复成最开始的可以为空

（2）主键一般搭配自增长。如果主键是自增长的，则删除主键时，要先删除自增长（自增长不能脱离索引存在）

5、主键分类

（1）主键分为两类：业务主键和逻辑主键（逻辑主键较常用）

（2）在实际创建表的过程中，很少使用真实的业务数据作为主键字段（业务主键，eg：学号、课程号等）。大部分时候，使用逻辑性的字段作为主键，这种主键称为逻辑主键

（3）逻辑主键没有业务意义，值是什么都没有关系。但它有唯一性，能够区分当前记录。基本上会为每一张表增加一个逻辑主键来保证数据的唯一性

注：主键的作用是为了保证数据的唯一性，不冲突即可

    -- 创建表，使用逻辑主键
    create table my_student(
        id int primary key auto_increment comment '逻辑主键：自增长',    -- 逻辑主键
        number char(10) not null comment '学号',
        name varchar(10) not null
    );

六、自动增长

1、自增长：auto_increment。当自增长的字段 不给值 | 给null值 | 给默认值 的时候，自增长会自动的被系统触发。系统会将当前字段已有的最大值进行+1操作，得到一个新的不同的值

注：自增长通常和逻辑主键搭配使用

2、新增自增长

（1）自增长的特点

a). 任何一个字段要做自增长，前提是该字段本身是一个索引（key一栏有值）

注：索引不一定是主键，但主键一定是索引，叫主键索引。key一栏代表索引，任何出现在这一栏的都是索引

b). 自增长字段必须是数字（整型）

c). 一张表最多只能有一个自增长

d). 绝大多数情况下，自增长搭配的是逻辑主键

（2）方式一：在创建表的时候，在字段属性之后跟"auto_increment"关键字

    -- 自增长
    create table my_auto(
        -- 自增长：(1)是索引；(2)是整型；(3)一张表只有一个；(4)通常搭配主键使用
        id int primary key auto_increment comment '自动增长',
        name varchar(10) not null
    )charset utf8;

    -- 查看表结构
    desc my_auto;

（3）方式二：当表已经创建好之后，给已有字段追加自增长

    -- 给已有字段追加自增长
    alter table 表名 modify 字段名 列类型 auto_increment;

3、自增长的使用

（1）当自增长没有被给值，或被给定的值为 null或默认值 的时候（不给值 | 给null值 | 给默认值），会触发自动增长

    -- 触发自增长：不给值
    insert into my_auto (name) values ('张三');

    -- 触发自增长：给null值
    insert into my_auto values (null, '李四');

    -- 触发自增长：给默认值
    insert into my_auto values (default, '王五');

    -- 查看表数据
    select * from my_auto;

注：自增长的第一个元素默认为1，且每次都是自增1

（2）自增长对应的字段如果输入了值，那么自增长失效。但下一次还是能够正确的自增长，从最大值+1开始自增

注：修改自增长字段的数据 < 自增长的值，是可以的。但下次自增长的值（表选项中的auto_increment的值）不会被改变

    -- 指定数据
    insert into my_auto values (6, '赵六');

    insert into my_auto values (null, '赵四方');

    -- 查看表数据
    select * from my_auto;

（3）如何确定下一次自增长的值？可以通过查看表创建语句得到

4、修改自增长

（1）自增长如果涉及到字段改变（把自增长改到另外一个字段上），必须先删除自增长，后新增（一张表只能有一个自增长）

注：自增长一旦触发使用之后，会自动的在表选项中增加一个选项。如果有多个自增长字段，表选项中就会有多个值，修改自增长时就无法区分。所以，自增长字段只能有一个

（2）修改当前自增长已经存在的值，修改后的值必须比当前已有的自增长的最大值大，不能小（小不报错，但修改无效）

注：修改后的值为下一个自增长的值，可以通过表创建语句查看

    -- 修改当前自增长已经存在的值（修改表选项）
    alter table 表名 auto_increment = 值;

    -- 修改自增长的值
    alter table my_auto auto_increment = 10;

    -- 查看表创建语句
    show create table my_auto;

（3）为什么自增长是从1开始的？为什么每次都是自增1？

         系统所有的表现（eg：字符集、校对集等）都是由系统内部的变量进行控制的，可以修改变量实现不同的效果（修改是对整个数据库修改，而不是单张表。修改是会话级别的，当前客户端、当次连接有效，关闭失效）

    -- 查看自增长对应的变量
    show variables like 'auto_increment%';

    -- 修改自增长步长（会话级别）
    set auto_increment_increment = 5;

    -- 修改自增长起始值（会话级别）
    set auto_increment_offset = 2;

5、删除自增长

（1）自增长是字段的一个属性，可以用modify进行修改。删除自增长，就是在字段属性之后不再保留auto_increment。当用户修改自增长所在字段时，如果没有看到auto_increment属性，系统会自动清除该自增长

    -- 删除自增长
    alter table 表名 modify 字段名 列类型;

    alter table my_auto modify id int primary key;    -- 错误
    alter table my_auto modify id int;    -- 有主键的时候，千万不要再增加主键

注：被系统优化后存储的表创建语句，主键有了额外的一行在其中存储。在所有字段之后，加 primary key(主键字段列表)。modify时，如果再加上 primary key，相当于又增加了一个主键。而系统规定一张表只能有一个主键，所以会报错

（2）如果主键是自增长的，则删除主键时，要先删除自增长（自增长不能脱离索引存在）

6、细节问题

（1）一张表中只能有一个自增长，自增长会上升到表选项中

（2）创建表之后，如果表中还没有插入数据，自增长就还没有被触发，那么自增长不会表现（表选项中看不到auto_increment）

（3）插入数据时给定了自增长的值A，如果 A<下一次自增长的值，则下一次自增长的值不变；如果 A>下一次自增长的值，则下一次自增长的值变成 A+1

七、唯一键

1、唯一键：unique key。用来保证对应的字段中的数据唯一。主键也可以用来保证字段数据唯一，但一张表中只能有一个主键

（1）唯一键在一张表中可以有多个

（2）唯一键允许字段数据为null，且可以有多个为null。null不参与唯一性比较（因为null与任何字段运算，结果都为null）

注：一张表中只能有一个主键（非空、唯一），但是可以有多个唯一键（可以为空、唯一）

2、增加唯一键

      和增加主键类似，增加唯一键也有三种方式

（1）方式一：在创建表的时候，直接在字段之后跟"unique [key]"关键字

    -- 唯一键
    create table my_unique1(
        number char(10) unique comment '学号：唯一，允许为空',
        name varchar(10) not null
    )charset utf8;

    -- 查看表结构
    desc my_unique1;

（2）方式二：在创建表的时候，在所有字段之后，增加"unique key(字段列表) "来创建唯一键（可以是复合唯一键）

    -- 唯一键
    create table my_unique2(
        number char(10) not null comment '学号',
        name varchar(20) not null,
        -- 增加唯一键
        unique key(number, name)
    )charset utf8;

    -- 查看表结构
    desc my_unique2;

    -- 查看表创建语句
    show create table my_unique2;

注：如果一张表没有主键，且有一个或多个不为空的唯一键，则把第一个不为空的唯一键当作该表的主键（查看表创建语句即可看到真相）

（3）方式三：当表已经创建好之后，额外追加唯一键。可以通过修改表字段属性，也可以直接追加

    -- 追加唯一键：通过修改表字段属性
    alter table 表名 modify 字段名 列类型 unique key;

    -- 追加唯一键：直接追加
    alter table 表名 add unique key(字段列表);

    -- 追加唯一键
    create table my_unique3(
        id int primary key auto_increment,
        number char(10) not null,
        name varchar(20) not null
    )charset utf8;

    -- 查看表结构
    desc my_unique3;

    -- 追加唯一键
    -- 方式一：通过修改表字段属性
    alter table my_unique3 modify number char(10) unique key not null;
    -- 方式二：直接追加
    alter table my_unique3 add unique key(number);

    -- 查看表结构
    desc my_unique3;

3、查看唯一键

（1）方式一：唯一键是属性，可以通过查看表结构来查看唯一键（UNI）

（2）方式二：通过查看表创建语句来查看唯一键

注：在查看表创建语句的时候，会看到唯一键与主键不同的一点，会多出一个xxx（unique key 'xxx' (字段名xxx) ）。系统会为唯一键自动创建一个索引名，默认和字段名相同

4、唯一键约束

（1）唯一键与主键本质相同，唯一的区别就是唯一键默认允许字段为空，而且可以多个为空

注：如果唯一键也不允许为空，那么唯一键与主键的约束作用就是一致的

（2）唯一键在不为空的情况下，不允许重复（非空不允许重复）。为空null的数据可以放心插入，插多少个都没问题（null无法计算）

    -- 创建表
    create table my_unique4(
        number char(10) unique comment '学号：唯一，允许为空',
        name varchar(10) not null
    )charset utf8;

    -- 查看表结构
    desc my_unique4;

    -- 插入数据。唯一键可以有多个为空
    insert into my_unique4 values (null, 'zhangsan'), ('itcast0001', 'lisi'), (null, 'wangwu');

    -- 查看数据
    select * from my_unique4;

    -- 插入数据
    insert into my_unique4 values ('itcast0001', 'zhaoliu');    -- 报错，number需唯一

5、更新唯一键&删除唯一键

（1）更新唯一键：先删除，后新增（唯一键可以有多个，可以不删除）

（2）删除唯一键

    -- 删除唯一键
    -- 唯一键是索引的一种，提升查询效率
    alter table 表名 drop unique key;    -- 错误：唯一键有多个
    alter table 表名 drop index 索引名字;    -- 唯一键默认使用字段名作为索引名字

    -- 查看表结构
    desc my_unique3;

    alter table my_unique3 drop index number;

    -- 查看表结构
    desc my_unique3;

八、索引

1、几乎所有的索引都是建立在字段之上

2、索引：系统根据某种算法，将已有的数据（未来可能新增的数据）单独建立一个文件。该文件能够实现快速的匹配数据，并且能够快速的找到对应表中的记录

3、索引的意义

（1）提升查询数据的效率

（2）约束数据的有效性（唯一性等）

4、索引本身会产生索引文件（有时候可能比数据文件还大），会非常耗费磁盘空间

5、如果某个字段需要作为查询的条件经常使用，那么可以使用索引（一定要想办法增加）。如果某个字段需要进行数据的有效性约束，也可以使用索引（主键、唯一键）

注：

（1）普通查询是全表查询，匹配成功了也会把后面的数据查完

（2）使用索引查询是去索引文件中找该条记录在磁盘上的哪一行，直接去那一行找该条数据即可，其他数据不会被查询

6、MySQL中提供了多种索引

（1）主键索引：primary key

（2）唯一索引：unique key

（3）全文索引：fulltext index

（4）普通索引：index

7、全文索引：针对文章内部的关键字进行索引。全文索引最大的问题在于如何确定关键字

注：英文很容易，因为英文单词与单词之间有空格。中文很难，因为没有空格，而且中文可以各种随意组合（分词：sphinx）