非空(not null)、唯一(unique key)、主键(primary key)、自增长(auto_increment)、默认约束(default)

准备基础环境:

mysql> create database mydb2;             #创建一个名为 mydb2的数据库
Query OK, 1 row affected (0.00 sec)mysql> show databases;                     #查看创建的数据库, mydb2已经创建成功。
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb2              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)mysql> use mydb2;                 #进入新建的数据库。
Database changed
mysql> select database();       #查看当前所在的数据库。
+------------+
| database() |
+------------+
| mydb2      |
+------------+
1 row in set (0.00 sec)mysql>
mysql> show tables;    #查看数据库,里面没有表
Empty set (0.00 sec)

 ################## not null非空约束##################

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_字段

 

mysql> create table tb1(                   #创建表
    -> id int,
    -> name varchar(20) not null            #这里使用not null约束值不能为空。
    -> );
Query OK, 0 rows affected (0.02 sec)mysql> desc tb1;                             #查看表结构,name字段不为空。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_字段_02

#测试插入一条数据,name字段的值为空。结果会报错。
mysql> insert into tb1(id) value(1);                      #如果name字段不使用not null约束的话,这种方式是可以的;但是目前有了约束name字段必须有值。
ERROR 1364 (HY000): Field 'name' doesn't have a default value 
mysql> insert into tb1(name) value('小明');          #单独插入name字段的值是可以的,因为id字段没有约束。
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tb1(name) value('NULL');        #在Mysql里面,NULL不等于空。
Query OK, 1 row affected (0.00 sec) 
mysql> insert into tb1(id,name) value(1,'andy');   #此时插入完整的数据。
Query OK, 1 row affected (0.01 sec) 
mysql> select * from tb1;                                    #查看结果,可发现name字段的值 '小明' 他对应的 id 字段为 null。并且name字段的值为 'NULL' 也添加进去了。
+------+--------+ 
| id   | name   |
+------+--------+
|    1 | andy   |
| NULL | 小明   |
| NULL | NULL   |
+------+--------+
3 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------------------------------------
##使用alter修改表结构
mysql> desc tb1;            #查看当前表结构。当前name字段 Null为 NO

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_字段_03

mysql> alter table tb1 modify name varchar(20);        #使用 alter 修改表结构,相当于重新定义tb1表中的字段name属性。
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0 mysql> desc tb1;            #查看当前表结构。当前name字段 Null为 YES

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_04

mysql> alter table tb1 modify name varchar(20) not null;         #使用alter把name再重新定义为 not null。如果字段里面的值已经是空了,就不能再修改为 not null。
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> select * from tb1;    #查看修改后的表结构。此时name字段 Null为NO

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_05

mysql> select * from tb1;         #查看当前表中的内容。id字段里面的值有NULL,这个NULL不是手动插入的 'NULL'; 是 : insert into tb1(name) value('小明'); 
+------+--------+ 
| id   | name   |
+------+--------+
|    1 | andy   |
| NULL | 小明   |
+------+--------+
2 rows in set (0.00 sec)mysql> alter table tb1 modify id int not null;           #使用alter修改tb1表中的Null为空,结果会报错,因为当前表中id字段中存在NULL。
ERROR 1138 (22004): Invalid use of NULL value
mysql>mysql> delete from tb1 where id is null;            #使用delete结合where,删除id字段里面为空的一条数据。
Query OK, 1 row affected (0.00 sec)mysql> select * from tb1;     #查看删除结果,id 字段为NULL的数据已经被删除
+------+------+
| id   | name |
+------+------+
|    1 | andy |
+------+------+
1 row in set (0.00 sec)mysql>
mysql> desc tb1;           #查看当前表的结构,目前tb1里的Null为YES

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_06

mysql> alter table tb1 modify id int not null;         #使用alter修改表tb1里的Null为not
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tb1;         #查看修改后的结果,目前tb1里的Null为not

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_07


#################  unique key 唯一约束 #################

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_08

 

mysql> create table tb2(            #创建一个表
    -> id int unique key,                   # id 字段使用了 unique key唯一约束,表示这个字段值只能出现一次
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)mysql> desc tb2;        #查看tb2表的结构,多了一个 Key
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql>
mysql> insert into tb2 value(1,'小明');           #插入一条数据
Query OK, 1 row affected (0.01 sec)mysql> insert into tb2 value(1,'andy');          #再插入一个数据,但是id 还是使用1。结果会报错,因为id 使用了 unique key唯一约束
ERROR 1062 (23000): Duplicate entry '1' for key 'id'
mysql> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 mysql> desc tb2;     #查看当前表结构,id字段里面的Key为UNI

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_09

mysql> alter table tb2 drop key id;           #使用alter重写表中字段 id属性
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

 mysql> desc tb2;     #查看当前表结构,id字段里面的Key没有值了。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_10

mysql> alter table tb2 add unique key(id);     #再使用alter重写表中字段 id属性,把unique key添加回去。
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0 mysql> desc tb2;     #查看当前表结构,id字段里面Key的属性为UNI。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_11

 


################# 主键约束  primary key #################

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_12

主键的作用: 可以唯一标识 一条数据,每张表里面只能有一个主键(就像身份证号一样,唯一的标识)。
主键特性: 非空且唯一。当表里没有主键的时,第一个出现的非空且为唯一的列,被当成主键。  
mysql> create table tb3(            #创建一个表
    -> id int primary key,                  # id字段设为 主键约束
    -> name varchar(20) not null     # name字段设为 非空约束
    -> );
Query OK, 0 rows affected (0.01 sec)mysql> desc tb3;                      #查看表结构,发现Key里面多了PRI(primary);并且创建表时 id并没有添加非空,但是添加了主键primary,所以体现出主键非空且为唯一的特性。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_13

mysql> mysql> insert i value(1,'andy');           #插入一条数据,id字段的值为1
Query OK, 1 row affected (0.00 sec)mysql> insert into tb3 value(1,'小米');             #再插入一条数据,id字段还是为1,这时就会报错,因为 id 字段是主键。主键非空且为唯一
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> alter table tb3 drop primary key;            #使用 alter 删除tb3表中的主键。
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> desc tb3;            #查看修改结果,此时Key已经没有PRI。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_字段_14

mysql> alter table tb3 add primary key(id);    #使用alter在tb3表中添加主键
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tb3;           #查看添加的结果,此时Key里面多了PRI

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_15

 


##################  自增长 auto_increment  ##################

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_字段_16

auto_increment :自动编号,一般与主键组合使用(必须是主键)。一个表里面只有一个自增默认情况下,起始值为1,每次的增量为1。
mysql> create table tb5(                      #创建一个表
    -> id int primary key auto_increment,  #id字段设置为 Int类型, 主键( primary key)和 自增长(auto_increment)。
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)mysql> desc tb5;            #查看tb5表结构,里面Extra多了auto_increment。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_17

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql> insert into tb5(name) value('andy龙');       #插入一条数据,这里没有 id 字段。
Query OK, 1 row affected (0.00 sec) mysql> insert into tb5(name) value('andy童');     #再插入一条数据。
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb5;               #查看表tb5里面的内容,发现里面的id也有数值。这个 id是自动按顺序添加的。
+----+---------+
| id | name    |
+----+---------+
|  1 | andy龙  |
|  2 | andy童  |
+----+---------+
2 rows in set (0.00 sec)------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------
mysql> alter table tb5 modify id int;           #使用alter修改表结构,删除自增长。
Query OK, 2 rows affected (0.18 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> desc tb5;              #查看删除修改结果里面Extra没有了auto_increment。此时如果手动插入数据后,
                                        #后续再使用alter把auto_increment加回来,插入数据时会报错,中间插入一条数据,会导致auto_increment的 id号不连续了。
                                        #比如当前有两条数据,自动分配的 id号到2了。现在使用alter删除自增长了,然后再手动车插入一条数据id为3,然后在使用alter添加了自增长auto_increment。
                                        #那时在插入数据它自增长的 id连续号为3,而id又是主键是唯一的。此时插入数据会报错

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_主键_18

mysql> alter table tb5 modify id int auto_increment;        #使用alter修改表结构,添加自增长。
Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> desc tb5;         #查看修改结果,里面Extra增加了auto_increment。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql_19

mysql> insert into tb5(name) value('jack');       #修改完表结构后,再添加一条数据,他会连续后面的id号。并不是每次都从1开始。好像手动插入的id号不会被auto_increment认为是它的序号。
Query OK, 1 row affected (0.00 sec)mysql> select * from tb5;
+----+---------+
| id | name    |
+----+---------+
|  1 | andy龙  |
|  2 | andy童  |
|  3 | lili    |
|  4 | jack    |
+----+---------+
4 rows in set (0.00 sec)##########################auto_incremen 修改编号默认值##########################
mysql> create table tb6(                 #创建一个表。
    -> id int primary key auto_increment,          # id 字段 设为主键(primary key )和自增长(auto_increment)
    -> name varchar(20)
    -> )auto_increment=100;                            #使用auto_increment=100,把自增长的默认序号改为100.
Query OK, 0 rows affected (0.01 sec)mysql> desc tb6;               #查看表结构

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql_20

mysql> insert into tb6(name) values('张单'),('andy'),('jack');            #在新建的表中,插入数据。这个因为id字段是自增长,所以只插入name字段即可。使用 values 而不是 value,同时插入多条数据。
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from tb6;            #查看结果,id号自增长从100开始。
+-----+--------+
| id  | name   |
+-----+--------+
| 100 | 张单   |
| 101 | andy   |
| 102 | jack   |
+-----+--------+
3 rows in set (0.00 sec)mysql>
###################### 默认约束default ######################

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_21

 

mysql> create table tb6(                        #创建一个表
    ->  id int primary key auto_increment,    # id字段设为主键和自增长
    -> name varchar(20) not null,                #name不为空
    -> age int not null default 18                  # age字段设为不为空,同时设为默认约束(设置个默认值为18)
    -> );
Query OK, 0 rows affected (0.01 sec)mysql> desc tb6;   #查看表结构,可发现 age 字段Default 的值为18。

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_22

-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
#插入一条数据,这个 id 字段是自增长,所以可以不要设值,age字段有默认值18;所以只需要设 name的值。
mysql> insert into tb6(name) values('andy'),('小明'),('jack');    #只添加name字段的值,同时使用values添加多个。
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from tb6;            #查看添加结果
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | andy   |  18 |
|  2 | 小明   |  18 |
|  3 | jack   |  18 |
+----+--------+-----+
3 rows in set (0.00 sec)mysql> insert into tb6(id,name,age) value(100,'小强','25');   #再添加一条数据,自增长和默认值都手动设置值。
Query OK, 1 row affected (0.01 sec)mysql> select * from tb6;          #查看结果,新数据的 id值和age 都修改了。(没有使用自增长和默认值)
+-----+--------+-----+
| id  | name   | age |
+-----+--------+-----+
|   1 | andy   |  18 |
|   2 | 小明   |  18 |
|   3 | jack   |  18 |
| 100 | 小强   |  25 |
+-----+--------+-----+
4 rows in set (0.00 sec)mysql>
mysql> insert into tb6(name,age) value('lili',20);       #添加数据,id 的自增长字段不设置值。
Query OK, 1 row affected (0.00 sec)mysql> select * from tb6;          #id字段从101开始了,而是不4。说明自增长只增长不减少。
+-----+--------+-----+
| id  | name   | age |
+-----+--------+-----+
|   1 | andy   |  18 |
|   2 | 小明   |  18 |
|   3 | jack   |  18 |
| 100 | 小强   |  25 |
| 101 | lili   |  20 |
+-----+--------+-----+
5 rows in set (0.00 sec)mysql>
 -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
mysql> alter table tb6 modify age int;                   #使用alter修改表结构,修改删除了 age字段的默认约束
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tb6;     #查看结果,age 字段里面的 Default属性为 NULL

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_23

mysql> alter table tb6 modify age int default 20;          #使用alter修改表结构,给 age字段添加默认约束。
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc tb6;              #查看结果,age 字段里面的 Default属性为 20.

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_24

 #################### 外键约束 foreign key ####################

外键约束 :保持数据一致性,完整性实现一对一或一对多关系。
外键必须关联到键上面去,一般情况是,关联到另一张表的主键
(因为一个表只存一类信息。用外键来做参照,保证数据的一致性,可以减少数据冗余)

 

mysql非主键判断记录存在则更新 mysql的主键非空怎么写_mysql非主键判断记录存在则更新_25