目的:
1 unique唯一的特性。
2 unique是可以为空的。
为部门创建一张员工信息表
创建表:
表company.department1
示例1:
CREATE TABLE company.department1 (
dept_id INT,
dept_name VARCHAR(30) UNIQUE,
comment VARCHAR(50)
);
示例2:
表company.department2
CREATE TABLE company.department2 (
dept_id INT,
dept_name VARCHAR(30),
comment VARCHAR(50),
UNIQUE(dept_name)
);
(CONSTRAINT uk_name UNIQUE(dept_name)
可以再设置约束时,为列起一个名称。)
查看表结构:
观察UNIQUE的显示位置:
MariaDB [(none)]> desc company.department1;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_id | int(11) | YES | | NULL | |
| dept_name | varchar(30) | YES | UNI | NULL | |
| comment | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
插入数据
1、插入合法数据
insert into department1 values (1,'zhangsan','yyy');
2、插入空值数据
MariaDB [company]> insert into department1 values (1,NULL,'yyy');
Query OK, 1 row affected (0.00 sec)
3、插入空值数据(空值是允许重复的)
MariaDB [company]> insert into department1 values (1,NULL,'yyy');
Query OK, 1 row affected (0.01 sec)
4、插入非法数据
insert into department1 values (1,'zhangsan','yyy');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'dept_name'
网友评论