数据的完整性

主键约束

CREATE TABLE orders(
oid INT COMMENT '订单号',
total DOUBLE COMMENT '订单金额总计',
name VARCHAR(20) COMMENT '收货人',
phone VARCHAR(20) COMMENT '收货人电话',
addr VARCHAR(50) COMMENT '收货人地址'
);

INSERT INTO orders(
oid,total,name,phone,addr) values(
1,100,'zs',1366,'xxx');

上表没有主键，添加主键

mysql> desc orders;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid   | int(11)     | YES  |     | NULL    |       |
| total | double      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| addr  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

添加主键

修改添加主键(PRIMARY KEY 主键不能为空)

ALTER TABLE orders ADD PRIMARY KEY(oid);

UNIQUE（主键可以为空）

ALTER TABLE orders ADD UNIQUE(phone);

新建主键

CREATE TABLE orders1(
id INT PRIMARY KEY
);

新建多字段主键

CREATE TABLE orders2(
id INT,
phon VARCHAR(20),
PRIMARY KEY(id,phon)
);

mysql> DESC orders;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid   | int(11)     | NO   | PRI | NULL    |       |
| total | double      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| addr  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

自动增长列AUTO_INCREMENT

CREATE TABLE order3(
id INT PRIMARY KEY AUTO_INCREMENT
);

修改已建立的表，改自动增长

ALTER TABLE order MODIFY id INT  PRIMARY KEY AUTO_INCREMENT; 

索引

创建索引

CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
INDEX(name)
)

修改索引

CREATE INDEX orders_name ON orders(name); 

创建唯一索引

CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
UNIQUE INDEX(name)
)

唯一索引

CREATE UNIQUE INDEX orders_id ON orders(id);

mysql> DESC orders;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| oid   | int(11)     | NO   | PRI | NULL    |       |
| total | double      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | MUL | NULL    |       |
| phone | varchar(20) | YES  | UNI | NULL    |       |
| addr  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE orders\G;
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `oid` int(11) NOT NULL COMMENT '订单号',
  `total` double DEFAULT NULL COMMENT '订单金额总计',
  `name` varchar(20) DEFAULT NULL COMMENT '收货人',
  `phone` varchar(20) DEFAULT NULL COMMENT '收货人电话',
  `addr` varchar(50) DEFAULT NULL COMMENT '收货人地址',
  PRIMARY KEY (`oid`),
  UNIQUE KEY `oid` (`oid`),
  UNIQUE KEY `phone` (`phone`),
  KEY `orders_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

ERROR:
No query specified

非空约束

CREATE TABLE orders(
id INT NOT NULL
);

修改非空约束

ALTER TABLE orders MODIFY id INT NOT NULL;

默认值约束DEFAULT

CREATE TABLE orders(
in INT PRIMARY KEY AUTO_INCERMENT NOT NULL COMMENT '序号',
name VARCHAR(50) DEFAULT  'ABC' COMMENT '姓名',
UNIQUE INDEX name,
);

修改默认值

ALTER TABLE orders MODIFY name VARCHAR(50) DEFAULT 'DCE';

外键的概念：
外键是值引用另一个表中的一列或多列，被引用的列应该是具有主键约束（PRIMARY KEY)或者唯一约束(UNIQUE)。外键用于建立和加强两个表数据之间的链接。

CREATE TABLE subject(
sub_id INT PRIMARY KEY,
sub_name VARCHAR (20)
);
CREATE TABLE student(
stu_id INT PRIMARY KEY,
stu_name VARCHAR(20),
sub_id INT NOT NULL
);
INSERT INTO subject(sub_id,sub_name) VALUES(1,'math');

mysql> SELECT * FROM subject;
+--------+----------+
| sub_id | sub_name |
+--------+----------+
|      1 | math     |
+--------+----------+
1 row in set (0.00 sec)
INSERT INTO student(stu_id,stu_name,sub_id) VALUES(1,'ZS',1);
注：sub_id是外键
mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id   | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| sub_id   | int(11)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
添加外键约束FOREIGN(外)KEY（键）  REFERENCES（约束）
ALTER TABLE student
ADD FOREIGN KEY(sub_id) REFERENCES subject(sub_id);
创建外键约束
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
FOREIGN KEY(stu_id) REFERENCES student
);
删除外键drop
ALTER TABLE student DROP FOREIGN KEY sub_id;