MySQL知识大总结（进阶）

一，数据库的约束

1，约束类型

 1，not null 

试着使用not null 来创建数据表 

create table if not exists student(
    -> id bigint not null,
    -> name varchar(20) not null
    -> );

 desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

我们看到在null的列，两个字段都不允许为空，我们添加两行不为空和一行为空的数据来试一下。

insert into student values (1,'张三'),(2,'李四');

select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+

成功添加

insert into student values (null,null);
ERROR 1048 (23000): Column 'id' cannot be null

在新增数据发生报错，它不允许字段id为空。

2，unique

在试着用unique来创建一个表

create table if not exists student(
    -> id bigint unique,
    -> name varchar(20) unique
    -> );

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

key 变成了唯一类型

我们添加三个数据，两个相同的，一个不同的

insert into student values (1,'张三'),(2,'李四');
Query OK, 2 rows affected (0.00 sec)

成功添加了，

insert into student values (1,'王五');
ERROR 1062 (23000): Duplicate entry '1' for key 'student.id'

 insert into student values (3,'张三');
ERROR 1062 (23000): Duplicate entry '张三' for key 'student.name'

两个字段都设置为唯一类型的，所以无论哪一个字段重复了都不可以。

3，default

 create table if not exists student(
    -> id bigint default 0,
    -> name varchar(20) default '无名氏' unique
    -> );

这些数据库约束是可以一起使用的，

desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| id    | bigint      | YES  |     | 0         |       |
| name  | varchar(20) | YES  | UNI | 无名氏    |       |
+-------+-------------+------+-----+-----------+-------+

我们来添加两个啥都没有的数据

insert into student (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student (name) values ('张三');
Query OK, 1 row affected (0.00 sec)

select * from student;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 无名氏    |
|    0 | 张三      |
+------+-----------+

空缺的元素都由默认值填补了。

4，primary key 

可以使用auto_increment来在主键后面，这样就代表这个字段是自增主键，不用对其进行赋值，在其他列的插入时，就会自行生成对应的id，但是如果我们这行sql语句写错了的话id是不会接着上一行数据的id继续的，

 create table if not exists student(
    -> id bigint primary key auto_increment,
    -> name varchar(20)
    -> );

复合主键

create table if not exists student(
    -> id bigint,
    -> name varchar(20),
    -> primary key(id,name)
    -> );

desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | bigint      | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+

5，foreign key

create table if not exists student(
    -> id bigint primary key auto_increment,
    -> name varchar(20),
    -> class_id bigint,
    -> foreign key (class_id) references class(id)
    -> );

desc student;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | bigint      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| class_id | bigint      | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

有了外键到约束我们就不能随便插入数据了

select * from class;
+----+---------+
| id | name    |
+----+---------+
|  1 | java113 |
|  2 | java112 |
|  3 | java111 |
+----+---------+