mysql常见约束条件

not null+default

create table t4(id int,name char,sex enum(‘male’,‘female’) not null default ‘male’);
mysql> insert into t4(id,name) values(1,‘san’);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
±-----±-----±-----+
| id | name | sex |
±-----±-----±-----+
| 1 | s | male |
±-----±-----±-----+
1 row in set (0.00 sec)

unique key

unique 有单行唯一和联合唯一
create table phone(
id int unique,
name char(10),
number int,
unique(name,number));
insert into phone values(1,”iphone”,12),(2,”iphone”,11),(3,”iphone”,10);

mysql> create table phone(
-> id int unique, 这是单列唯一
-> name char(10),
-> number int,
-> unique(name,number)); 这是联合唯一
Query OK, 0 rows affected (0.03 sec)

mysql> desc phone;
±-------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±---------±-----±----±--------±------+
| id | int(11) | YES | UNI | NULL | |
| name | char(10) | YES | MUL | NULL | |
| number | int(11) | YES | | NULL | |
±-------±---------±-----±----±--------±------+
3 rows in set (0.01 sec)

mysql> insert into phone values(1,“iphone”,12),(2,“iphone”,11),(3,“iphone”,10);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from phone;
±-----±-------±-------+
| id | name | number |
±-----±-------±-------+
| 1 | iphone | 12 |
| 2 | iphone | 11 |
| 3 | iphone | 10 |
±-----±-------±-------+
3 rows in set (0.00 sec)

primary key （主键）

约束：not null unique
对于innodb来说一张表内必须要有一个主键
create table t5(
id int primary key, 单列主键
name char(5)
);
mysql> desc t5;
±------±--------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±--------±-----±----±--------±------+
| id | int(11) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
±------±--------±-----±----±--------±------+
2 rows in set (0.01 sec)
mysql> insert into t5(name) values(“**”); 这边我们不指定id，mysql会默认帮我们去添加一个id主键
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t5;
±—±-----+
| id | name |
±—±-----+
| 0 | ** |
±—±-----+
1 row in set (0.00 sec)
mysql> insert into t5(name) values(“**”); 当我们再次去添加重复的内容时会报错！！！
ERROR 1062 (23000): Duplicate entry ‘0’ for key ‘PRIMARY’
这边也是存在复合主键的概念
create table t6(
ip char(15),
port int,
primary key(ip,port)
);
mysql> desc t6;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| ip | char(15) | NO | PRI | | |
| port | int(11) | NO | PRI | 0 | |
±------±---------±-----±----±--------±------+
2 rows in set (0.01 sec)
mysql> insert into t6 values(“1.1.1.1”,80),(“1.1.1.1”,80); 可以看到重复插入会报错
ERROR 1062 (23000): Duplicate entry ‘1.1.1.1-80’ for key ‘PRIMARY’
mysql> insert into t6 values(“1.1.1.1”,80),(“1.1.1.1”,81);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t6;
±--------±-----+
| ip | port |
±--------±-----+
| 1.1.1.1 | 80 |
| 1.1.1.1 | 81 |
±--------±-----+
2 rows in set (0.00 sec)

auto_increment（自增长）

create table t7(
id int primary key auto_increment,
name char(5)
);
mysql> show create table t7;
±------±-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-----------------------------------------------------------------------------------------------------------------------------------------------------+
| t7 | CREATE TABLE t7 (
id int(11) NOT NULL AUTO_INCREMENT,
name char(5) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±------±-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t7(name) values(“***”);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t7;
±—±-----+
| id | name |
±—±-----+
| 1 | *** |
±—±-----+
1 row in set (0.00 sec)

mysql> insert into t7(name) values(“***”);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t7;
±—±-----+
| id | name |
±—±-----+
| 1 | *** |
| 2 | *** |
±—±-----+
2 rows in set (0.00 sec)

mysql> show create table t7;
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t7 | CREATE TABLE t7 (
id int(11) NOT NULL AUTO_INCREMENT,
name char(5) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | 可以看到自增长的数字在不断的变化
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

foreign key（外键）

先建立被关联表，在建立关联表
create table subject(
id int primary key,
name char(10)
);

create table teacher(
id int primary key,
name char(7),
subject_id int,
foreign key(subject_id) references subject(id) on delete cascade on update cascade
);
mysql> show create table teacher;
±--------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE teacher (
id int(11) NOT NULL,
name char(7) DEFAULT NULL,
subject_id int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY subject_id (subject_id),
CONSTRAINT teacher_ibfk_1 FOREIGN KEY (subject_id) REFERENCES subject (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
插入数据也是一样，需要先插入被关联的表再插关联的表
insert into subject values(1,”语文”),(2,”数学”),(3,”英语”),(4,”政治”);
insert into teacher values(1,”张三”,1),(2,”李四”,2),(3,”王五”,3),(4,”赵六”,4),(5,”***”,1);
mysql> select * from teacher;
±—±-------±-----------+
| id | name | subject_id |
±—±-------±-----------+
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
| 5 | *** | 1 |
±—±-------±-----------+
5 rows in set (0.00 sec)

mysql> select * from subject;
±—±-------+
| id | name |
±—±-------+
| 1 | 语文 |
| 2 | 数学 |
| 3 | 英语 |
| 4 | 政治 |
±—±-------+
4 rows in set (0.00 sec)
我们可以直接去修改关联的表
delete from subject where id=1;
mysql> select * from subject;
±—±-------+
| id | name |
±—±-------+
| 2 | 数学 |
| 3 | 英语 |
| 4 | 政治 |
±—±-------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
±—±-------±-----------+
| id | name | subject_id |
±—±-------±-----------+
| 2 | 李四 | 2 |
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
±—±-------±-----------+
3 rows in set (0.00 sec)
update subject set id = 2022 where id = 2;
mysql> update subject set id = 2022 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from teacher;
±—±-------±-----------+
| id | name | subject_id |
±—±-------±-----------+
| 2 | 李四 | 2022 |
| 3 | 王五 | 3 |
| 4 | 赵六 | 4 |
±—±-------±-----------+
3 rows in set (0.00 sec)
但是还是要说一句要尽量少用外键