mysql表操作约束_mysql表结构修改、约束(二)

知识要点：

表结构操作

非空约束

唯一约束

主键约束

自增长

默认约束

表结构操作 ( alter table)

create table tb1(

id int,

name char(4)

);

#修改表名

alter table `tb1` rename `tb2` #将表tb1修改为tb2

#修改列名(字段名)change 改列名和数据类型

alter table `tb1` change `age` `sex` char(4); #将age字段修改为sex;

#修改数据类型 modify 改列的数据类型 (属性)

alter table tb1 modify `age` varchar(20); #修改age数据类型为varchar(20)

#修改已经存在字段的类型

alter table tb1 modify age char(4) #把age字段修改成char类型;

#修改表结构，增加字段 年龄

alter table tb1 add age int first; #增加年级字段到第一列;

alter table tb1 add age int after id;#在id后面增加一个字段age;

#添加多列

alter table `tb1`

add `aa` int,

add `bb` int,

add `cc` int;

#删除数据表中的列

alter table tb1 drop age ; #删除年龄字段;

#删除多列

alter table `tb1`

drop `bb`,

drop `cc`;

#修改字段的相对位置

ALTER TABLE test MODIFY name1 int first|after name2;

约束条件

约束类型：

默认

非空

唯一

自增长

主键

外键

关键字：

default

not null

unique key

auto_increment

primary key

foreign

默认约束 (default)

初始值设置，插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

#例：

mysql> create table tb6(

-> id int primary key auto_increment,

-> name varchar(20) not null,

-> age int not null default 18

-> );

mysql> desc tb6;

mysql> insert into tb6(name) values('张三'),('李四'),('王五');

mysql> select * from tb6;

删除default

mysql> alter table tb6

-> modify age int [not null]; --这种方法会将非空约束也删除了，因为modify是整体修改数据类型，以及其他特性；

mysql> desc tb6;

第二种方法

mysql> alter table tb6

-> alter age drop default; ----指定干掉default,不会删除非空约束；

增加default

mysql> alter table tb6

-> modify age int default 20;

mysql> desc tb6;

#(2)

mysql> alter table tb6

-> alter age set default 21;

非空约束

NULL 字段值可以为空；

NOT NULL 字段值不能为空；

create table tb1(

id int,

name varchar(20) not null #非空约束字段，insert的时候，必须添加字段，不能省略

);

insert into tb1 (id) value(1); #报错，name设置了非空约束，一定要添加值

insert into tb1 (id, name) value(1, ''); #可以插入空字符， 空字符不等于null

注意： 在mysql 里面，空字符'' 不等于null

添加非空约束 (必须这个字段，没有NULL值)

mysql> alter table tb1

-> modify id int not null;--记得类型别丢了，否则报错，modify本身就有修改数据类型之意；

取消非空约束

mysql> alter table tb1

-> modify id int ;

唯一约束 (unique key)

确保字段中的值的唯一unique key

例：

mysql> create table tb2(

-> id int not null unique key,--如果表里面没有定义主键，则第一个出现不为空且唯一性的当做主键；

-> name varchar(20) not null

-> );

mysql> insert into tb2 value(1,'张三');

mysql> insert into tb2 value(1,'张三'); # 报错，违反唯一约束

#添加唯一约束

mysql> ALTER TABLE `tb2`

-> ADD unique key(`name`)

-> ;

#删除唯一约束

mysql> desc tb2;

mysql> alter table tb2

-> drop key name;

#联合唯一

mysql> alter table tb2

-> add aa int,

-> add bb int;

mysql> alter table tb2

-> add unique key (aa,bb);

mysql> insert into tb2 value(4,'佳能',1,2);

mysql> insert into tb2 value(5,'哈哈',1,2);

ERROR 1062 (23000): Duplicate entry '1-2' for key 'aa'--联合唯一，只要其中一个不一样就可以插入

# 删除联合唯一 (show create table tb2;)

mysql> show create table student2; ----首先查看表的详情；

| student2 | CREATE TABLE `student2` (

`id` int(11) NOT NULL,

`name` varchar(20) NOT NULL,

`aa` int(11) DEFAULT NULL,

`bb` int(11) DEFAULT NULL,

UNIQUE KEY `id` (`id`),

UNIQUE KEY `aa` (`aa`,`bb`) --通过查看是前面那个aa，所以只需要把前面那个删除就行了；

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> alter table student2

-> drop key aa;

主键约束 (primary key)

主键作用：可以唯一标识一条数据，每张表里只能有一个主键

主键特性：非空且唯一，表没有主键时，第一个出现的非空且唯一的字段为主键

主键保证记录的唯一性， 唯一标识每一条数据，主键自动为NOT NULL，每张数据表只能存在一个主键，NOT NULL + UNIQUE KEY --唯一且不为空；

一个UNIQUE KEY 又是一个NOT NULL的时候，那么它被当做PRIMARY KEY主键

当一张表里没有一个主键的时候，第一个出现的非空且为唯一的列被视为有主键。

#主键，就是可以数据表中，可以唯一标识，一条数据。就好像身份证一样。

mysql> create table tb3(

-> id int primary key,

-> name varchar(20) not null

-> );

mysql> desc tb3;

mysql> insert into tb3 value(1,'张三');

Query OK, 1 row affected (0.27 sec)

mysql> insert into tb3 value(1,'张三');

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

#删除主键约束

mysql> alter table tb3

-> drop primary key; # 一个表里面，只有一个主键

mysql> desc tb3;

#添加主键约束

mysql> alter table tb3

-> add primary key(id);

#联合主键

mysql> create table tb4(

-> id_a int ,

-> id_b int,

-> content varchar(20),

-> primary key(id_a,id_b)

-> );

mysql> desc tb4;

#删除主键约束

mysql> alter table tb4

-> drop primary key;--不需要指定字段，因为主键唯一性，会自动删除联合的每一个约束；

#添加联合主键

mysql> alter table tb4

-> add primary key(id_a,id_b);

自增长auto_increment

例：

mysql> create table tb5(

-> id int primary key auto_increment,

-> name varchar(20)

-> )auto_increment =100; # 如果不写，默认从1开始

mysql> desc tb5;

mysql> insert into tb5(name) values('张三'),('李四');

mysql> select * from tb5;

#auto_increment值，可以调大

insert into tb5(id,name) values(110,'王五');

mysql> select * from tb5;

#不可以调小

insert into tb5(id,name) values(108,'王八');

insert into tb5(name) values('田七');--是指自增长那个不能调小而已；

mysql> select * from tb5;

#删除自动增长

mysql> alter table tb5

-> modify id int;

#增加自动增长auto_increment

mysql> alter table tb5

-> modify id int auto_increment;

~~~