学习笔记——mysql数据库（二）

约束

约束是表级的强制规定，约束放置在表中删除有关联关系的数据，对数据库中表的字段的值加的一些限制和保护，是对数据保护的最后一道屏障。

约束条件与数据类型的宽度一样，都是可选参数。

作用：用于保证数据的完整性和一致性。

五种约束：

• PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
• FOREIGN KEY (FK) #标识该字段为该表的外键
• NOT NULL #标识该字段不能为空
• UNIQUE KEY (UK) #标识该字段的值是唯一的
• AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）

• DEFAULT #为该字段设置默认值
• UNSIGNED #无符号
• ZEROFILL #使用0填充

非空约束

创建表t1，分别设定id为主键、name不可空、sex默认为‘man’

mysql> create table t1(
    -> id int(11) primary key,
    -> name char(20) not null,
    -> sex varchar(20) default 'man'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| sex   | varchar(20) | YES  |     | man     |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

针对全局设置内容：mysql> insert into t1 values (1,'xiaoxiao','man');

针对局部设置内容：mysql> insert into t1 (id,name) values(2,'zhouma');

mysql> insert into t1 values (1,'xiaoxiao','man');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | xiaoxiao | man  |
+----+----------+------+
1 row in set (0.00 sec)

mysql> insert into t1 (id,name) values(2,'zhouma');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+----------+------+
| id | name     | sex  |
+----+----------+------+
|  1 | xiaoxiao | man  |
|  2 | zhouma   | man  |
+----+----------+------+
2 rows in set (0.00 sec)

创建表t2，分别设定id为主键且自动增长、name不可空、sex默认为‘man’

mysql> create table t2(
    -> id int(11) primary key auto_increment,
    -> name char(20) not null,
    -> sex varchar(20) default 'man' );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   |     | NULL    |                |
| sex   | varchar(20) | YES  |     | man     |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

由于id设置为auto_incremen，故可选择只插入name，id自动增长

mysql> insert into t2 values (1,'honghong','woman');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (name) values('jiage');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+----------+-------+
| id | name     | sex   |
+----+----------+-------+
|  1 | honghong | woman |
|  2 | jiage    | man   |
+----+----------+-------+
2 rows in set (0.00 sec)

而name设置为不可空且没有设置默认值，所以在插入时一定需要包含name内容

mysql> insert into t2 (id) values(33);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

当然了，也可以指定id，那么后面的id则会在最新的id上自动增长

mysql> insert into t2 (name) values('huidong');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (id,name) values(9,'zifan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+----------+-------+
| id | name     | sex   |
+----+----------+-------+
|  1 | honghong | woman |
|  2 | jiage    | man   |
|  3 | huidong  | man   |
|  9 | zifan    | man   |
+----+----------+-------+
4 rows in set (0.00 sec)

主键约束

创建表t3，分别设定id和name

mysql> create table t3(
    -> id int(11),
    -> name char(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

建表后修改表结构增加主键（增加表t3中的id为主键），此时默认为不可空（主键不可空）

alter table 表名 add constraint [主键名称] primary key (主键字段)

mysql> alter table t3 add constraint id primary key(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

外键约束（主从表）

创建表t4，分别设定id为主键、name不可空

mysql> create table t4(
    -> id int(11) primary key,
    -> name varchar(50) not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加外键约束方式

创建时增加：constraint fk_t4_score_sid foreign key(sid) references t4(id)

外面增加：alter table score add constraint fk_sid foreign key(sid) reference t4(sid)

创建表score，并设置sid、score，添加外键约束方式

并且在关联表中加入同步删除（on delete cascade）和同步更新（on update cascade）

mysql> create table score(
    -> sid int(11),
    -> score double,
    -> constraint fk_t4_score_sid foreign key(sid) references t4(id) on update cascade on delete cascade
    -> );
Query OK, 0 rows affected (0.00 sec)

在表t4和表score中插入数据

mysql> insert into t4 values(1,'lanlan');
Query OK, 1 row affected (0.00 sec)

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

mysql> insert into score values(1,86);
Query OK, 1 row affected (0.00 sec)

mysql> select * from score;
+------+-------+
| sid  | score |
+------+-------+
|    1 |    86 |
+------+-------+
1 row in set (0.00 sec)

删除表t4中的id为1的数据，同步删除了表score中id为1的数据

mysql> delete from t4 where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from score;
Empty set (0.00 sec)

mysql> select * from t4;
Empty set (0.00 sec)