MySQL - 列属性(主键、唯一键和自增等)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/J080624/article/details/71703297

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


查看表数据结构

  • 红色表示数据类型;
  • 蓝色表示列属性;

这里写图片描述

列属性有很多,大致如下:

NOT NULL/NULL,
DEFAULT,
PRIMARY KEY,
UNIKUE KEY,
AUTO_INCREMENT,
COMMENT


【1】空属性

其有两个值:null(默认值) 和 not null (不为空) ;

虽然默认的数据库基本字段为空,但是生产环境中尽可能保证字段都不为空。空数据没有意义,且无法参加运算。

select 1+'hello',null,1+null;

这里写图片描述


【2】列描述

comment:描述,没有实际含义,为列增加文字说明,会随着表创建一起储存起来。


【3】默认值

default:某一种数据会经常性的出现某个具体的值,可以在一开始指定好,用户可以在使用的时候选择性的使用默认值或插入新数据。

create table my_default(
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;

desc my_default;

insert into my_default values('tom',null);--gender为null

insert into my_default values('lily',DEFAULT);-- default,gender取默认值

select * from my_default;

这里写图片描述

  • 当不对gender列进行插入的时候,gender将会使用默认值
insert into my_default (name) VALUE ('lir');--指定列,gender取默认值

select * from my_default;

这里写图片描述

可见,指定列 与 values使用default 两种方式都可以使具有默认值的列,在插入数据的时候使用默认值。


【4】主键

primary key :一张表只能有一个字段可以使用对应的键,用来唯一的约束该字段里面的数据,不能重复。主键本身一定不为空,且不允许重复。

给表增加主键,大体分为三种:

① 创建表的时候在字段后面使用 primary key关键字;

create table my_default(
id int PRIMARY KEY,--使用关键字
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;

② 在创建表的时候,在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键,称之为复合主键)

create table my_pri(
number char(10),
course char(10),
score TINYINT,
PRIMARY KEY(number,course)--复合主键
)charset utf8;

desc my_pri;

这里写图片描述

测试如下:

insert into my_pri VALUES('001','IT','10');--插入成功
insert into my_pri VALUES('001','IT','10');--插入失败

insert into my_pri VALUES('001','IT2','10');--插入成功

这里写图片描述

③ 创建表之后,为表追加主键。有两种方式,第一位修改表字段属性;第二直接追加。

第一种方式示例如下:

alter TABLE my_pri MODIFY number char(11) PRIMARY KEY;

第二种方式示例如下:

alter table table_name add primary key(column) ;

--alter TABLE my_pri add PRIMARY KEY(number,course);--增加复合主键

--alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score)--使用关键字CONSTRAINT 且指定主键名字pk_score 

--alter table my_pri add CONSTRAINT  PRIMARY KEY(score)--使用关键字CONSTRAINT 且使用默认名字 

删除主键:

alter table my_pri drop PRIMARY KEY;
--不能根据主键名字删除,因为只有一个主键

【主键分类】

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


【5】自动增长

| - - |
auto_increment:当对应的字段,不给值 || 给默认值 || 给null 值,会自动的被系统触发。

系统会从当前字段中已有的最大值进行+1操作,得到一个新的不同的字段,作为下一次插入数据是自增长字段的值。若手动修改了auto_increment,且为向上修改,那么下一次值为auto_increment!

自增长通常跟主键搭配。

自增长特定:

① 任何一个字段要做自增长前提必须是一个索引;
② 自增长字段必须是整型数字;

| -
测试如下

create table my_auto(
id int auto_increment ,
name varchar(20)
)charset utf8;
--失败

这里写图片描述

| - 说明自增长字段必须是key(索引)- |

正确如下:

create table my_auto(
id int primary key auto_increment ,
name varchar(20)
)charset utf8;

这里写图片描述

插入数据

insert into my_auto  values(null,'tom');

insert into my_auto  values('','tom2');--插入失败

insert into my_auto  values('null','tom3');--插入失败

insert into my_auto  values(DEFAULT,'tom4');

其中,第二、三将会插入失败–主键不能为空:

这里写图片描述

第一、四插入null 或者默认值,将会自动增长并插入:

这里写图片描述

如果自增长对应的字段输入了值,那么自增长失效。下一次插入数据,仍旧会获取 最大值+1:

insert into my_auto  values(5,'tom5');
insert into my_auto  values(default,'tom6');

select * from my_auto;

这里写图片描述

【修改自增长】

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

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

语法如下:

alter table table_name auto_increment = 值 ;

测试 向下修改:

alter table my_auto auto_increment=3;--现在数据表已存在最大值7

show create table my_auto;

CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

--此时AUTO_INCREMENT=8 ,故向下修改无效

测试 向上修改:

alter table my_auto auto_increment=10;

show create table my_auto;

CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

--可以向上修改

插入数据测试:

insert into my_auto VALUES(null,'Lucy');

select * from my_auto ;

这里写图片描述

此时id为10!!!

演示到这里,有没有一个疑问:为什么自增长是从1开始?

我们知道,所有系统的表现(如字符集,校对集)都是由系统内部的变量进行控制的。

查看自增长对应的变量:show VARIABLES LIKE '%auto_increment%'

auto_increment_increment 1 --步长值
auto_increment_offset    1 --起始值

这里写图片描述

可以修改变量实现不同的效果。但是修改是对整个数据库进行修改而不是单张表,且修改是会话级别,关闭并重新打开链接,变量恢复默认值。

set auto_increment_increment = 2;

show VARIABLES LIKE '%auto_increment%';

这里写图片描述

变量修改后,在第二次插入数据时候生效:

  • 因为修改变量并不会立即更新表选项的auto_increment,当再插入数据后,会更新auto_increment ,此时检测到步长已经改变,故之后插入数据会使用新的步长值!!
insert into my_auto values(null,'Lucy12');
insert into my_auto values(null,'Lucy12');

select * from my_auto;

这里写图片描述


【删除自增长】

自增长不能通过drop消除,只能通过modify column;

需要注意的是,如果自增长对应的字段为主键,那么修改 列属性的时候,不要再加primary key ; 会被系统认为又定义一个主键,将会提示”Multiple primary key defined”

alter table my_auto modify id int primary key;
--错误,会被认为又定义一个主键;

alter table my_auto modify id int ;
--正确,且不会丢失掉 id 的主键

这里写图片描述


【6】唯一键

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

唯一键默认值允许自动为空,而且可以多个不同字段为空—空字段不参与唯一性比较。

【增加唯一键】

分为创建表和创建后两种方式:

① 创建表时(又分两种):

--第一种,字段增加
create table my_unique(
name varchar(4) unique key,
number varchar(20) unique key
)charset utf8;

--第二种,字段末尾增加
create table my_unique(
name varchar(4) ,
number varchar(20) ,
unique key name(name),--指定唯一约束名字
unique key(number)--使用默认名字

)charset utf8;

这里写图片描述

② 创建表后(又分两种):

-- 创建表,不带唯一约束
create table my_unique(
name varchar(4) ,
number varchar(20) 
)charset utf8;
--第一种,modify column
alter table my_unique modify column name varchar(5) UNIQUE KEY;

--第二种 add constraint unique key

alter TABLE my_unique add CONSTRAINT num_uk UNIQUE KEY(number);
--指定唯一约束名字为num_uk

【删除唯一约束】

alter table my_unique drop index num_uk;

--根据指定约束名字删除,若创建的时候未指定名字,默认使用列名作为唯一约束名字。
CREATE TABLE `my_unique` (
  `name` varchar(5) DEFAULT NULL,
  `number` varchar(20) DEFAULT NULL,
  UNIQUE KEY `num_uk` (`number`),--唯一约束名字为num_uk
  UNIQUE KEY `name` (`name`)--唯一约束名字默认为列名
) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面演示一个有趣的现象:

--创建表
create table my_unique(
name varchar(4) not null,--注意这里 not null
number varchar(20) 
)charset utf8;

--增加唯一约束
alter table my_unique modify column name  varchar(5) not NULL UNIQUE KEY;

--查看表结构
desc my_unique;

这里写图片描述

注意key那一列,显示 PRI 不是UNI!!!

解释如下:该表没有主键;刚好是一个不为空的唯一键,性质和主键一样。故MySQL无法判断,显示了PRI。当表中已经存在主键,则会正常显示为UNI


【复合唯一约束】

--第一种创建方式
create table my_unique(
name varchar(4) not null,
number varchar(20) ,
unique KEY na_um_un(name,number)--字段末尾创建,使用指定名
)charset utf8;

desc my_unique;

这里写图片描述

  • 插入数据
insert into my_unique values('tom','1001');

insert into my_unique values('tom','1001');
  • 提示数据重复–约束名字为 na_um_un(创建时候指定的名字)

这里写图片描述

--第二种创建方式

--创建空表,不带约束
create table my_unique(
name varchar(4) not null,
number varchar(20) 
)charset utf8;

--增加复合约束
alter TABLE my_unique add CONSTRAINT num_na_uk UNIQUE KEY(number,name);

--查看表结构
desc my_unique;

这里写图片描述

  • 删除指定名字复合约束
alter table my_unique drop index num_na_uk;
  • 删除默认名字复合约束

    • 若不指定名字,那么复合约束名字为第一个字段名字
alter TABLE my_unique add CONSTRAINT UNIQUE KEY(number,name);

--查看表创建语句
show create TABLE my_unique;

CREATE TABLE `my_unique` (
  `name` varchar(4) NOT NULL,
  `number` varchar(20) DEFAULT NULL,
  UNIQUE KEY `number` (`number`,`name`)--这里,名字为number
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--删除复合约束
alter table my_unique drop index number;

另外,唯一键同样是索引的类型,故唯一键的添加删除可以如下:

ALTER TABLE `weight_num_curr_detail`
DROP INDEX `unque_store_brand` ;
ADD UNIQUE INDEX `unque_store_brand` (`store_code`, `brand_code`, `year_num`) USING BTREE ;
展开阅读全文

没有更多推荐了,返回首页