MariaDB相关的约束

1. MariaDB数据库操作
2. MariaDB表操作
3. MariaDB表结构修改
4. MariaDB增删改查操作
5. MariaDB子句
6. MariaDB聚合函数
7. MariaDB连接
8. MariaDB操作符
9. MariaDB正则与条件
10. MariaDB约束
11. MariaDB导出与导入
12. MariaDB权限与权限管理

NOT NULL

非空约束，是否允许该列的值为NULL，这里有一点很重要，很多字段（除了时间？）默认值如果不指定的话都是NULL，所以除了NULL=NULL，其他值并不等于NULL，比如“”、0等。

修改一个字段为NOT NULL：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)
 
MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)   
Records: 5  Duplicates: 0  Warnings: 5
 
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | NO   |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

这里还有一个问题，对于默认值为NULL但是又没有指定插入这个字段：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
//看username这个字段，默认值为NULL，不允许NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
//这里看到我们插入成功了。
MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 |          | test7    | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL  |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

可以看到username这一列的值为空字符，而它的默认值为NULL啊，
而logip默认值为NULL，但是允许插入NULL值，所以这里显示了NULL值。

查了一下~因为NULL为默认值，但是又不允许NULL值，所以，也就是说现在username这个字段没有值，因为SQL_MODE的原因，只会警告一下并不会直接报错，当我们指定SQL_MODE为’STRICT_ALL_TABLES’时，此时插入就会报如下错误：

MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

UNIQUE

unique代表唯一约束：唯一约束是指定table的列或列组合不能重复，保证数据的唯一性，虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同，MySQL会给唯一约束的列上默认创建一个唯一索引。

添加唯一约束：

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username为约束名称，UNIQUE(可多个字段）
 
//当插入用户名相同的数据事则会直接报错
 
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'
 
//删除此约束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;
 
//添加两个字段的约束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);
 
//测试添加数据
MariaDB [mydb]> SELECT * FROM user;                                                     
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
|  7 | test2    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  8 | test3    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  9 | test4    | test5    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)
 
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)
 
//仅当两个字段的数据都相同时才违反唯一约束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

PRIMARY KEY

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。如果是多列组合的主键约束， 那么这些列都不允许为空值，并且组合的值不允许重复。每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建，MySQL的主键名总是PRIMARY， 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作如下：

//因为现在的表中已经有主键了，先把主键删掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
 
//告诉我们一张表里只允许有一个字段为自动增长，且这个字段必须是主键，所以，我们要先取消它的自动增长。
 
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)               
Records: 4  Duplicates: 0  Warnings: 0
 
MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field    | Type        | Null | Key | Default             | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id       | int(11)     | NO   | PRI | NULL                |       |
 
 
//再次删除主键
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)               
Records: 4  Duplicates: 0  Warnings: 0
 
//好了，再让我们把主键加上吧~~~   以下两种方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;

FOREIGN KEY

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 也就是说从表的外键值必须在主表中能找到或者为空，当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据，还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，那么从表引用的数据就不确定记录的位置，同一个表可以有多个外键约束。

现在，我们创建一个GROUP表吧，用于记录用户的分组信息，

 CREATE TABLE `usergroup` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `comment` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |

然后呢~为user表添加一个记录，记录用户属于那个组

MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

//添加一个外键

ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);

//验证外键约束

MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))

//可以为空，但是不可以为参照表中没有的值

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)

外键定义：

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

其中一下级联操作需要注意：
ON DELETE CASCADE：当删除父（参照）表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除，不推荐使用。
ON DELETE SET NULL：当删除父（参照）表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为NULL