文章目录
MySQL表的完整性约束
作用:用于保证数据的完整性和一致性
约束条件 | 说明 |
---|---|
PRIMARY KEY (PK) | 标识该字段为该表的主键,可以唯一 的标识记录,不可以为空 UNIQUE + NOT NULL |
FOREIGN KEY (FK) | 标识该字段为该表的外键,实现表与表之间的关联 |
NULL | 标识是否允许为空,默认为NULL |
NOT NULL | 标识该字段不能为空,可以修改 |
UNIQUE KEY (UK) | 标识该字段的值是唯一的,可以为空,一个表中可以有多个UNIQUE KEY |
AUTO_INCREMENT | 标识该字段的值自动增长(整数类型,而且为主键) |
DEFAULT | 为该字段设置默认值 |
UNSIGNED | 无符号,正数 |
1、主键
每张表里只能有一个主键,不能为空,而且唯一,主键保证记录的唯一性,主键自动为NOT NULL。
一个 UNIQUE KEY 又是一个NOTNULL的时候,那么它被当做PRIMARY KEY主键。
定义两种方式:
表存在,添加约束
mysql> alter table t7 add primary key (hostname);
创建表并指定约束
mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
案例:
mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.11');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t9(hostname,ip) values('qfedu.com', '10.10.10.12');
ERROR 1062 (23000): Duplicate entry 'qfedu.com' for key 'PRIMARY'
mysql> insert into t9(hostname,ip) values('qfedu', '10.10.10.11');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t9;
+-----------+-------------+
| hostname | ip |
+-----------+-------------+
| qfedu | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
+-----------+-------------+
2 rows in set (0.00 sec)
mysql> insert into t9(hostname,ip) values('qfjy', '10.10.10.12');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t9;
+-----------+-------------+
| hostname | ip |
+-----------+-------------+
| qfedu | 10.10.10.11 |
| qfedu.com | 10.10.10.11 |
| qfjy | 10.10.10.12 |
+-----------+-------------+
3 rows in set (0.00 sec)
删除主键
mysql> alter table tab_name drop primary key;
主键被删除后,此字段仍然不允许为空值
2、auto_increment
自增(自动编号),且必须与主键组合使用默认情况下,起始值为1,每次的增量为1。
当插入记录时,如果为AUTO_INCREMENT数据列明确指定了一个数值,则会出现两种情况:
- 如果插入的值与已有的编号重复,则会出现出错信息,因为AUTO_INCREMENT数据列的值必须是唯一的;
- 如果插入的值大于已编号的值,则会把该插入到数据列中,并使在下一个编号将从这个新值开始递增。也就是说,可以跳过一些编号。如果自增序列的最大值被删除了,则在插入新记录时,该值被重用。
注:每张表只能有一个字段为自增,成了key才可以自动增长
案例:
mysql> CREATE TABLE department3 (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(30),
comment VARCHAR(50)
);
mysql> select * from department3;
Empty set (0.00 sec)
插入值
mysql> insert into department3(dept_name, comment) values('tom','test'), ('jack', 'test2');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from department3;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
| 1 | tom | test |
| 2 | jack | test2 |
+---------+-----------+---------+
2 rows in set (0.00 sec)
删除自动增长
mysql> ALTER TABLE department3 CHANGE dept_id dept_id INT NOT NULL;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc department3;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int(11) | NO | PRI | NULL | |
| dept_name | varchar(30) | YES | | NULL | |
| comment | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
再次插入数据,报错
mysql> insert into department3(dept_name,comment) values('tom','test1'),('jack','test2');
ERROR 1364 (HY000): Field 'dept_id' doesn't have a default value
3、设置唯一约束 UNIQUE
字段添加唯一约束之后,该字段的值不能重复,也就是说在一列当中不能出现一样的值。
案例:
mysql> create table department2(
-> dept_id int,
-> dept_name varchar(30) unique,
-> comment varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> desc department2;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into department2 values(1,'tom','good');
Query OK, 1 row affected (0.00 sec)
重复使用同一名字,提示错误
mysql> insert into department2 values(2,'tom','good');
ERROR 1062 (23000): Duplicate entry 'tom' for key 'dept_name'
mysql> insert into department2 values(2,'jack','good');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department2(dept_id,comment) values(2,'good');
Query OK, 1 row affected (0.00 sec)
mysql> select * from department2;
+---------+-----------+---------+
| dept_id | dept_name | comment |
+---------+-----------+---------+
| 1 | tom | good |
| 2 | jack | good |
| 2 | NULL | good |
+---------+-----------+---------+
3 rows in set (0.00 sec)
4、null与not null
- 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值
- 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值 sex enum(‘male’,‘female’) not null default ‘male’
#只能选择male和female,不允许为空,默认是male
案例:
mysql> create table t4(id int(5),name varchar(10),sex enum('male','female') not null default 'male');
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t4(id,name) values(1,'tom');
mysql> select * from t4;
+------+------+------+
| id | name | sex |
+------+------+------+
| 1 | tom | male |
+------+------+------+
1 row in set (0.00 sec)
允许为null
mysql> create table t1(id int(5),name varchar(10),age int(5));
Query OK, 0 rows affected (0.00 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(5) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | int(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into t1(id,name) values(1,'tom');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | tom | NULL |
+------+------+------+
1 row in set (0.00 sec)
5、默认约束
添加/删除默认约束
语法:
-
1.设置默认值
- alter table 表名 alter 字段名 set default 默认值; 2.删除默认值
- alter table 表名 alter 字段名 drop default; 3.插入值
- alter table 表名 change 原字段名 现字段名 字段类型 约束;
案例:
1.创建一个表
mysql> create table user(id int not null, name varchar(20), number int, primary key(id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| number | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2、设置默认值
mysql> alter table user alter number set default 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| number | int(11) | YES | | 0 | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3、插入值
mysql> alter table user change id id int not null auto_increment;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into user(name) values('rock');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user(name) values('rock');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+--------+
| id | name | number |
+----+------+--------+
| 1 | rock | 0 |
| 2 | rock | 0 |
+----+------+--------+
2 rows in set (0.00 sec)
删除默认值
mysql> alter table user alter number drop default;
6、扩展:
指定字符集
修改字符集 :在创建表的最后面指定一下: default charset=utf8 (可以指定中文)
* 未指定之前,插入
mysql> insert into t1(id,name) values(1,'石宇飞');
ERROR 1366 (HY000): Incorrect string value: '\xE7\x9F\xB3\xE5\xAE\x87...' for column 'name' at row 1
* 创建表格式指定字符集为utf-8
mysql> create table t6(id int(2),name char(5),age int(4)) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> desc t6;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(2) | YES | | NULL | |
| name | char(5) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t6(id,name) values(1,'石宇飞');
Query OK, 1 row affected (0.00 sec)
change和modify 的区别
1、change可以给字段重命名,modify不可以。
2、当想要更改列的类型而不是名称,change语法仍然要求旧的和新的列名称,即使旧的和新的列名称是一样的。而用modify来改变列的类型,此时不需要重命名。
change
语法:alter table 表名 change 原字段名 现字段名 字段类型 约束 ;
modify
语法:alter table 表名 modify 字段名 字段类型 约束 ;