mysql 小林

系统环境

Ubuntu14.04 64位版本

约束

作用

给表添加约束值，从而约束用户操作数据库的行为

常见约束

1. 默认值约束 (default)
2. 非空约束 (not null)
3. 唯一值约束 (unique)
4. 主键约束 (primary key)
5. 自增长约束 (auto_increment)
6. 外键约束 (foreign key)

准备

启动 MySQL 服务

# 启动服务
pinsily@zhu ~$ sudo service mysql start

# 以 root 用户登录，并输入密码
pinsily@zhu ~$ mysql -u root -p
Enter password:

说明

下面的实例以数据库 pinsily_user 中的表 student 进行示范，且每次示范完都把 student 表删掉之后重新创建

• 连接数据库

mysql> USE pinsily_user;

• 删除表

mysql> DROP TABLE student;

默认值约束 (default)

当没有插入值的时候默认值生效

# 创建 student 表
mysql> CREATE TABLE student(id int,name varchar(20) default 'pinsily');

# 插入值,不插入姓名
mysql> INSERT INTO student(id) VALUES(1);

# 查看表内容,没插入的姓名为默认值
mysql> SELECT * FROM student;
+------+---------+
| id   | name    |
+------+---------+
|    1 | pinsily |
+------+---------+
1 row in set (0.00 sec)

撤销 DEFAULT 约束

ALTER TABLE student
ALTER name DROP DEFAULT

非空约束(not null)

当某个字段必须要值(不能不插入数值，也不能插入null),就给这个字段添加一个非空约束

# 创建表，name 字段非空
mysql> CREATE TABLE student(id int,name varchar(25) not null);
Query OK, 0 rows affected (0.20 sec)

# 没插入值会报错
mysql> INSERT INTO student(id) VALUES(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

# 插入 null 值也会报错
mysql> INSERT INTO student(id,name) VALUES(1,null);
ERROR 1048 (23000): Column 'name' cannot be null

唯一值约束(unique)

如 id 的值不能出现重复值。这时就要给 id 添加一个唯一约束

# 创建表，id 为唯一值
mysql> CREATE TABLE student(id int unique,name varchar(20));
Query OK, 0 rows affected (0.16 sec)

# 插入值
mysql> INSERT INTO student(id,name) VALUES(1,'pinsily');
Query OK, 1 row affected (0.04 sec)

# 若插入相同的 id 会报错
mysql> INSERT INTO student(id,name) VALUES(1,'peng');
ERROR 1062 (23000): Duplicate entry '1' for key 1

主键约束 (primary key)

通常情况下，我们会给每张表都会设置一个主键字段，用来标记记录的唯一性,一般每张表都会设置 id 字段成为主键
简单来讲，主键拥有 唯一性和非空性

# 新建表，将 id 字段设置为主键
mysql> CREATE TABLE student(id int primary key,name varchar(25));
Query OK, 0 rows affected (0.17 sec)

# 插入新值
mysql> INSERT INTO student(id,name) VALUES(1,'peng');
Query OK, 1 row affected (0.07 sec)

# 若插入相同 id 会报错，唯一性
mysql> INSERT INTO student(id,name) VALUES(1,'pinsily');
ERROR 1062 (23000): Duplicate entry '1' for key 1

# 若插入空值会报错，非空性
mysql> INSERT INTO student(name) VALUES('pinsily');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

自增长约束 (auto_increment)

一个表中的id值通常都是一次+1的，这样的需求其实不需要我们每次手动去插入数值，数据库会自动帮我们插入数据,需要配合主键使用

# 新建表，id 自动增加
mysql> CREATE TABLE student(id int primary key auto_increment,name varchar(25));
Query OK, 0 rows affected (0.18 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('pinsily');
Query OK, 1 row affected (0.07 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('peng');
Query OK, 1 row affected (0.10 sec)

# 插入
mysql> INSERT INTO student(name) VALUES('liang');
Query OK, 1 row affected (0.07 sec)

# 查看表，可以看到 id 自动增加了
mysql> SELECT * FROM student;
+----+---------+
| id | name    |
+----+---------+
|  1 | pinsily |
|  2 | peng    |
|  3 | liang   |
+----+---------+
3 rows in set (0.00 sec)

外键约束 (foreign key)

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY

FOREIGN KEY 约束用于预防破坏表之间连接的动作

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

假设 student 表如下：

s_id	name	c_id
1	A	computer science 1
2	B	computer science 1
3	C	computer science 1
4	D	computer science 2
5	E	computer science 2
6	F	computer science 2

但是觉得 class 重复太多，浪费了，这时候可以使用分成两个表了
建立 class 表

c_id	class
1	computer science 1
2	computer science 2

建立 student 表

s_id	name	c_id
1	A	1
2	B	1
3	C	1
4	D	2
5	E	2
6	F	2

这时 student 表中的外键 c_id 关联了 class 表中的主键 c_id

# 创建 class 表，即外键指向的表
mysql> CREATE TABLE class(c_id int primary key,class varchar(55));
Query OK, 0 rows affected (0.16 sec)

# 插入数据
mysql> INSERT INTO class(c_id,class) VALUES(1,'computer science 1');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO class(c_id,class) VALUES(2,'computer science 2');
Query OK, 1 row affected (0.07 sec)

mysql> select * from class;
+------+--------------------+
| c_id | class              |
+------+--------------------+
|    1 | computer science 1 |
|    2 | computer science 2 |
+------+--------------------+
2 rows in set (0.00 sec)

# 创建 student 表，外键 c_id 指向 class 中的主键 c_id 
mysql> CREATE TABLE student(
    -> s_id int primary key auto_increment,
    -> name varchar(20),
    -> c_id int,
    -> FOREIGN KEY (c_id) REFERENCES class(c_id));
Query OK, 0 rows affected (0.14 sec)

# 插入数据并查询
mysql> INSERT INTO student(name,c_id) VALUES('A',1) ;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO student(name,c_id) VALUES('B',1) ;
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO student(name,c_id) VALUES('C',1) ;
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO student(name,c_id) VALUES('D',2) ;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO student(name,c_id) VALUES('E',2) ;
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO student(name,c_id) VALUES('F',2) ;
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM student;
+------+------+------+
| s_id | name | c_id |
+------+------+------+
|    1 | A    |    1 |
|    2 | B    |    1 |
|    3 | C    |    1 |
|    4 | D    |    2 |
|    5 | E    |    2 |
|    6 | F    |    2 |
+------+------+------+
6 rows in set (0.00 sec)

# 若插入外键表中不存在的数据，会报错
mysql> INSERT INTO student(name,c_id) VALUES('F',3) ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pinsily_user/student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`c_id`))