MySQL表的非外键约束

爱被打了一巴掌 2022-11-17 14:57 225阅读 0赞

# MySQL表的非外键约束 #

/\*  
建立一张用来存储学生信息的表  
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息  
学号是主键 = 不能为空 +? 唯一  
姓名不能为空  
性别默认值是男 ，只能取值男、女  
年龄：18---50  
Email唯一  
\*/

## 1，列级约束 ##

### 创建数据库表 ###

create table student(
        sno int(6) primary key  auto_increment,
        sname varchar(10) not null ,
        sex char(1) default '男'  check( sex ='男' || sex='女'),
        age int(3) check (age>=18 and age <=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique 
    );

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70][]

### 添加数据 ###

insert into student values(1,'张三','男',34,'1999-12-23','Java001','zhs@qq.com');

insert into student values(null,'张三','男',34,'1999-12-23','Java001','zhs2@qq.com');

insert into student values(default,'李四',null,34,'1999-12-23','Java001','lisi@qq.com');

insert into student (sno,sname,enterdate)values(null ,'王五','2001-12-24');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 1][]

### 查询数据 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 2][]

### 删除数据库表 ###

drop stable student;

![20210410203425331.png][]

# 2、表级约束 #

### 创建数据库表 ###

create table student(  
sno int(6) auto\_increment,  
sname varchar(10) not null ,  
sex char(1) default '男',  
age int(3),  
enterdate date,  
classname varchar(10),  
email varchar(15),  
constraint pk\_stu primary key(sno),  
constraint ck\_stu\_sex check(sex ='男' or sex ='女'),  
constraint ck\_stu\_age check(age>=18 and age<=50),  
constraint uk\_stu\_email unique(email)  
);

### 添加数据 ###

insert into student values(1,'zhangsan','男',34,'1999-12-23','Java001','zhs@sxt.cn');

insert into student values(null,'zhangsan','男',34,'1999-12-23','Java001','zhs2@sxt.cn');

### 查询数据 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 3][]

### 查看表的结构 ###

desc student；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70]: /images/20221022/b9c2a89e389b42138e3de81d65c93717.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 1]: /images/20221022/bb268157623440c7a2990238560b5953.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 2]: /images/20221022/0e557373ff57452ab5d5086014b1c74d.png
[20210410203425331.png]: /images/20221022/044edf74c1fb413ebc3025679e58d6b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 3]: /images/20221022/5429e92098b248ffb054e539512732ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ4NzU5NjY0_size_16_color_FFFFFF_t_70 4]: /images/20221022/2a9f54b72b484ecaa1b55d255f1a9478.png