MySQL（四）—— 列属性

目录

列属性

空属性

列描述

默认值

字段属性

主键约束

更新主键 & 删除主键

主键分类

自动增长

新增自增长

自增长使用

修改自增长

删除自增长

唯一键

增加唯一键

唯一键约束

更新唯一键 & 删除唯一键

索引

---

列属性

列属性: 真正约束字段的是数据类型, 但是数据类型的约束很单一. 需要有一些额外的约束, 来更加保证数据的合法性.

列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment

空属性

两个值: NULL(默认的)和NOT NULL(不为空)

虽然默认的, 数据库基本都是字段为空, 但是实际上在真实开发的时候, 尽可能的要保证所有的数据都不应该为空: 空数据没有意义; 空数据没有办法参与运算.

select NULL;
select 1+NULL;

创建一个实际案例表: 班级表(名字,教室)

-- 创建班级表
create table my_class(
name varchar(20) not null,
room varchar(20) null -- 代表允许为空: 不写默认就是允许为空
)charset utf8;
-- 查看表结构
desc my_class;

列描述

列描述: comment, 描述, 没有实际含义: 是专门用来描述字段,会根据表创建语句保存: 用来给程序猿(数据库管理员)来进行了解的.

-- 创建表
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;

默认值

默认值: 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好: 在需要真实数据的时候,用户可以选择性的使用默认值.

默认值关键字: default

-- 默认值
create table my_default(
name varchar(20) not null,
age tinyint unsigned default 0,
gender enum('男','女','保密') default '男'
)charset utf8;
-- 查看表结构
desc my_default;

默认值的生效: 使用, 在数据进行插入的时候,不给改字段赋值

想要使用默认值,可以不一定去指定列表,故意不使用字段列表: 可以使用default关键字代替值

-- 插入数据
insert into my_default (name) values('高强');
insert into my_default values('范立峰',18,default);

-- 查看数据
select * from my_default;

字段属性

主键, 唯一键和自增长.

主键

主键: primary key,主要的键. 一张表只能有一个字段可以使用对应的键, 用来唯一的约束该字段里面的数据, 不能重复: 这种称之为主键.

一张表只能有最多一个主键.

增加主键

SQL操作中有多种方式可以给表增加主键: 大体分为三种.

方案1: 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)

优点: 非常直接; 缺点: 只能使用一个字段作为主键

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

-- 查看表结构
desc my_pri1;

方案2: 在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键)

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

-- 查看表结构
desc my_pri2;

方案3: 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.

Alter table 表名  add primary key(字段列表);

前提: 表中字段对应的数据本身是独立的(不重复)

-- 追加主键
create table my_pri3(
course char(10) not null comment '课程编号: 3901 + 0000',
name varchar(10) not null comment '课程名字'
);

-- 查看表结构
desc my_pri3;

-- 追加主键
alter table my_pri3 add primary key(course);

-- 再次查看表结构
desc my_pri3;

主键约束

主键对应的字段中的数据不允许重复: 一旦重复,数据操作失败(增和改)

-- 向pri1表插入数据
insert into my_pri1 values('古学星','itcast0001'),('蔡仁湾','itcast0002');
insert into my_pri2 values('itcast0001','39010001',90),('itcast0001','39010002',85),('itcast0002','39010001',92);

-- 主键冲突(重复) 报错
insert into my_pri1 values('刘辉','itcast0002'); -- 不可以: 'itcast0002'主键冲突
insert into my_pri2 values('itcast0001','39010001',100); -- 不可以:'itcast0001','39010001'冲突

更新主键 & 删除主键

没有办法更新主键: 主键必须先删除,才能增加.

Alter table 表名 drop primary key;

-- 查看表结构
desc my_pri3;

-- 删除主键
alter table my_pri3 drop primary key;

-- 再次查看表结构
desc my_pri3;

主键分类

在实际创建表的过程中, 很少使用真实业务数据作为主键字段(业务主键,如学号,课程号); 大部分的时候是使用逻辑性的字段(字段没有业务含义,值是什么都没有关系), 将这种字段主键称之为逻辑主键.

Create table my_student(

Id int primary key auto_increment comment ‘逻辑主键: 自增长’, -- 逻辑主键

Number char(10) not null  comment ‘学号’,

Name varchar(10) not null

)

自动增长

自增长: 当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.

自增长通常是跟主键搭配.

新增自增长

自增长特点: auto_increment

1. 任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)
2. 自增长字段必须是数字(整型)
3. 一张表最多只能有一个自增长

-- 自增长
create table my_auto(
id int primary key auto_increment comment '自动增长',
name varchar(10) not null
)charset utf8;

自增长使用

当自增长被给定的值为NULL或者默认值的时候会触发自动增长.

-- 触发自增长
insert into my_auto(name) values('邓立军');
insert into my_auto values(null,'龚森');
insert into my_auto values(default,'张滔');

-- 查看数据
select * from my_auto;

自增长如果对应的字段输入了值,那么自增长失效: 但是下一次还是能够正确的自增长(从最大值+1)

-- 指定数据
insert into my_auto values(6,'何思华');
insert into my_auto values(null,'陈少炼');

-- 查看数据
select * from my_auto;

如何确定下一次是什么自增长呢? 可以通过查看表创建语句看到.

show create table my_auto;

修改自增长

自增长如果是涉及到字段改变: 必须先删除自增长,后增加(一张表只能有一个自增长)

修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效)

Alter table 表名 auto_increment  = 值;

-- 修改表选项的值
alter table my_auto auto_increment = 4; -- 向下修改(小) 不可以
-- 查看表结构创建语句
show create table my_auto;

alter table my_auto auto_increment = 10; -- 向上修改 可以
-- 查看表结构创建语句
show create table my_auto;

思考: 为什么自增长是从1开始?为什么每次都是自增1呢?

所有系统的变现(如字符集,校对集)都是由系统内部的变量进行控制的.

查看自增长对应的变量: show variables like ‘auto_increment%’;

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

 

可以修改变量实现不同的效果: 修改是对整个数据修改,而不是单张表: (修改是会话级)

Set auto_increment_increment = 5; -- 一次自增5

-- 修改自增长步长
set auto_increment_increment = 5;

删除自增长

自增长是字段的一个属性: 可以通过modify来进行修改(保证字段没有auto_increment即可)

Alter table 表名 modify 字段 类型;

-- 删除自增长
alter table my_auto modify id int primary key; -- 错误: 主键理论是单独存在
alter table my_auto modify id int; -- 有主键的时候,千万不要再加主键

唯一键

一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个主键: 唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题.

唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)

增加唯一键

基本与主键差不多: 三种方案

方案1: 在创建表的时候,字段之后直接跟unique/ unique key

-- 唯一键
create table my_unique1(
number char(10) unique comment '学号: 唯一,允许为空',
name varchar(20) 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)
)charset utf8;

-- 查看表结构
desc my_unique1;

 方案3: 在创建表之后增加唯一键

create table my_unique3(
id int primary key auto_increment,
number char(10) not null,
name varchar(20) not null
)charset utf8;

-- 追加唯一键
alter table my_unique3 add unique key(number);

-- 查看表结构
desc my_unique1;

唯一键约束

唯一键与主键本质相同: 唯一的区别就是唯一键默认允许为空,而且是多个为空.

如果唯一键也不允许为空: 与主键的约束作用是一致的.

-- 插入数据
insert into my_unique1 values(null,'曾光'),('itcast0001','晁松'),(null,'李帅');
-- 报错 唯一键itcast0001冲突
insert into my_unique1 values('itcast0001','周江');

更新唯一键 & 删除唯一键

更新唯一键: 先删除后新增(唯一键可以有多个: 可以不删除).

删除唯一键

Alter table 表名 drop unique key; -- 错误: 唯一键有多个

Alter table 表名 drop index 索引名字; -- 唯一键默认的使用字段名作为索引名字

-- 查看表结构
desc my_unique3;

-- 删除唯一键
alter table my_unique3 drop index number;

-- 再次查看表结构
desc my_unique3;

索引

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

索引: 系统根据某种算法, 将已有的数据(未来可能新增的数据),单独建立一个文件: 文件能够实现快速的匹配数据, 并且能够快速的找到对应表中的记录.

索引的意义

1. 提升查询数据的效率
2. 约束数据的有效性(唯一性等)

增加索引的前提条件: 索引本身会产生索引文件(有时候有可能比数据文件还大) ,会非常耗费磁盘空间.

如果某个字段需要作为查询的条件经常使用, 那么可以使用索引(一定会想办法增加);

如果某个字段需要进行数据的有效性约束, 也可能使用索引(主键,唯一键)

Mysql中提供了多种索引

1. 主键索引: primary key
2. 唯一索引: unique key
3. 全文索引: fulltext index
4. 普通索引: index

全文索引: 针对文章内部的关键字进行索引

全文索引最大的问题: 在于如何确定关键字

英文很容易: 英文单词与单词之间有空格

中文很难: 没有空格, 而且中文可以各种随意组合(分词: sphinx)