MySQL的约束
1.主键约束
它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段的约束,就可以使得该字段不重复且不为空。
CREATE TABLE user(
id int PRIMARY KEY,
name VARCHAR(20)
);
mysql> create table users(id int PRIMARY KEY, name VARCHAR(20));
Query OK, 0 rows affected (0.08 sec)
写入两条数据:
mysql> insert into users values(1, 'join');
Query OK, 1 row affected (0.01 sec)
mysql> insert into users values(2, 'TimeJoin');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+----+----------+
| id | name |
+----+----------+
| 1 | join |
| 2 | TimeJoin |
+----+----------+
2 rows in set (0.00 sec)
联合主键:多个字段组成的主键就叫做联合主键(只要联合主键值加起来不重复就可以)
如何给多个字段创建约束呢,如下:
CREATE TABLE users2(
id int,
用户 VARCHAR (20),
密码 VARCHAR (20),
PRIMARY KEY (用户,密码)
);
添加数据:
INSERT INTO users2 VALUES (
1,'张三','4894ef484'
);
INSERT INTO users2 VALUES (
1,'FREGcv','ui28mh'
);
INSERT INTO users2 VALUES (
1,'nmty','48dvd84862vdf'
);
2.自增约束(AUTO_INCREMENT)
CREATE TABLE users3(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR (20),
addres VARCHAR (30),
Tel VARCHAR (20)
);
INSERT INTO users3 VALUES (1,'gynu','drtybuinompiuyt ftgyu','15896354692');
mysql> INSERT INTO users3 VALUES (1,'gynu','drtybuinompiuyt ftgyu','15896354692');
Query OK, 1 row affected (0.01 sec)
单独给name字段添加数据,id会自动生成
mysql> INSERT INTO users3(name) VALUES ('drt ybui nom piuyt');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users3;
+----+--------------------+-----------------------+-------------+
| id | name | addres | Tel |
+----+--------------------+-----------------------+-------------+
| 1 | gynu | drtybuinompiuyt ftgyu | 15896354692 |
| 2 | drt ybui nom piuyt | NULL | NULL |
+----+--------------------+-----------------------+-------------+
2 rows in set (0.00 sec)
建表后的添加与删除
如果我们建表后忘记给字段添加主键了,该怎么办呢,如:
mysql> CREATE TABLE sqlDemo(
-> id int ,
-> name VARCHAR (20),
-> addres VARCHAR (30),
-> Tel VARCHAR (20)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
我们可以通过alter来添加:ALTER TABLE sqlDemo ADD PRIMAR KEY (需要添加主键的字段名);
例如:ALTER TABLE sqlDemo ADD PRIMAR KEY(id);
mysql> ALTER TABLE sqlDemo ADD PRIMAR KEY(id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'KEY(id)' at line 1
mysql> alter table sqlDemo add primary key(id);小写却不报错
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
删除(撤销)主键约束 :ALTER TABLE sqlDemo DROP PRIMARY KEY ;
mysql> ALTER TABLE sqlDemo DROP PRIMARY KEY ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改主键约束 :ALTER TABLE sqlDemo MODIFY id int PRIMARY KEY ;通过修改字段的方式来添加主键约束
mysql> ALTER TABLE sqlDemo MODIFY id int PRIMARY KEY ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
3.唯一约束
约束修饰的字段值不可以重复
ALTER TABLE sqlDemo ADD UNIQUE (name) ;
mysql> ALTER TABLE sqlDemo ADD UNIQUE (name) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
INSERT INTO sqlDemo VALUES(1,'张三');
mysql> INSERT INTO sqlDemo VALUES(1,'张三',null,null);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO sqlDemo VALUES(1,'张三',null,null);
ERROR 1062 (23000): Duplicate entry '张三' for key 'sqldemo.name'
所以唯一的原则,所以第二次添加张三信息时会报错
mysql> INSERT INTO sqlDemo VALUES(1,'李四',null,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from sqlDemo;
+----+------+--------+------+
| id | name | addres | Tel |
+----+------+--------+------+
| 1 | 张三 | NULL | NULL |
| 1 | 李四 | NULL | NULL |
+----+------+--------+------+
2 rows in set (0.00 sec)
唯一约束也可以在创建表的时候添加,如:
mysql> CREATE TABLE sqlDemo(
-> id int ,
-> name VARCHAR (20),
-> addres VARCHAR (30),
-> UNIQUE (id, name)
-> );
Query OK, 0 rows affected (0.04 sec)
或者直接在字段后面添加
mysql> CREATE TABLE sqlDemo(
-> id int ,
-> name VARCHAR (20) UNIQUE,
-> addres VARCHAR (30)
-> );
Query OK, 0 rows affected (0.04 sec)
删除唯一约束:
ALTER TABLE sqlDemo DROP INDEX name ;
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> ALTER TABLE sqlDemo DROP INDEX name ;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
通过MODIFY来添加唯一主键:
ALTER TABLE sqlDemo MODIFY name VARCHAR(20) UNIQUE;
mysql> ALTER TABLE sqlDemo MODIFY name VARCHAR(20) UNIQUE;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc sqlDemo;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| addres | varchar(30) | YES | | NULL | |
| Tel | varchar(20) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
剩下的后续更新补上………………