1. not null 非空約束
// name 不能為空，如果數(shù)據(jù)插入的時候name為空會報錯
create table t_user(
    id int(10),
    name varchar(32) not null,  // 列級約束
    email varchar(128)
    // not null(name) 加到這里是表級約束
);
insert into t_user(id,name,email) values (1,'張三','123@jd.com');
+------+--------+------------+
| id   | name   | email      |
+------+--------+------------+
|    1 | 張三   | 123@jd.com |
+------+--------+------------+
// name設(shè)置不能為空，如果插入的是時候name為空會報這種錯誤
insert into t_user(1,email)values(1,'135@jd.com');
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 '1,email)values(1,'135@jd.com')' at line 1

2. unique 唯一約束
create table t_user(
    id int(10),
    name varchar(32) not null,   // 列級約束
    email varchar(128) unique  // 列級約束
   // unique(email) 表級約束
);
// 給郵箱加上unique 表示在插入數(shù)據(jù)的時候郵箱不能重復(fù)
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(32)  | NO   |     | NULL    |       |
| email | varchar(128) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

3. 主建約束 primary key 簡稱pk
        主建涉及的術(shù)語
                主建約束
                主建字段
                主建值
給字段添加主建約束后,不能為空,并且不能重復(fù)
一張表中應(yīng)該有主建,如果沒有主建表示這張表是無效的
主建值：是當(dāng)前行數(shù)據(jù)的唯一標(biāo)示

/// 添加主建
drop table if exists t_user;
create table t_user(
    id int(10) primary key,
    name varchar(32) ,
    email varchar(128)
);
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
mysql 中提供了一個自增數(shù)字，主建的值是自動增長，不需要用戶維護
auto_increment  ， 自動增長的值只能使用一次

drop table if exists t_user;
create table t_user(
    id int(10) primary key auto_increment,
    name varchar(32) ,
    email varchar(128)
);
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)  | YES  |     | NULL    |                |
| email | varchar(128) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

4. foreign key 外建約束 簡稱fk
      主建涉及的術(shù)語
                外建約束
                外建字段
                外建值
 foreign key 可以把兩張表聯(lián)系起來
兩張表設(shè)置外建
drop table if exists t_student;
drop table if exists t_calss;
create table t_calss(
    cno int(3) primary key,
    cname varchar(120) not null unique
);
create table t_student(
    sno int(3) primary key,
    sname varchar(32) not null,
    calssno int(3),
    foreign key(calssno) references t_calss(cno)
);
insert into t_calss (cno,cname) values(100,'1班');
insert into t_calss (cno,cname) values(200,'2班');
insert into t_calss (cno,cname) values(300,'3班');
insert into t_student(sno,sname,calssno) values(1,'李四',100);
insert into t_student(sno,sname,calssno) values(2,'王五',200);
insert into t_student(sno,sname,calssno) values(3,'趙六',300);
select * from t_student;
+-----+--------+---------+
| sno | sname  | calssno |
+-----+--------+---------+
|   1 | 李四   |     100 |
|   2 | 王五   |     200 |
|   3 | 趙六   |     300 |
+-----+--------+---------+
select * from t_calss;
+-----+-------+
| cno | cname |
+-----+-------+
| 100 | 1班   |
| 200 | 2班   |
| 300 | 3班   |
+-----+-------+
// 下面這行添加失敗t_calss cno 里面 500 不存在
insert into t_student(sno,sname,calssno) values(6,'李四',500);