mysql的约束和表的关系

最新推荐文章于 2024-11-01 23:37:32 发布

湫風如兮

最新推荐文章于 2024-11-01 23:37:32 发布

阅读量334

点赞数 5

文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/qq_39218045/article/details/141438770

版权

打开MySQL进入表格

加入数据，创建表格。
create table t1(id int default 110, name varchar(10));
insert into t1(name) value("thenew"),("ameng");
insert into t1 value(1,'tongyao'),(55,'talent');

+------+---------+ 
| id   | name    |
+------+---------+
|  110 | thenew  |
|  110 | ameng   |
|    1 | tongyao |
|   55 | talent  |
+------+---------+

not null 非空约束

create table t2(id int not null, name varchar(20));

insert into t2(name)  values("haha");
# Field 'id' doesn't have a default value

unique key 唯一约束

create table t3(id int unique key, name varchar(20) not null);

insert into t3 value(1,'xiaoming');
insert into t3 value(1,'xiaoming'); # 报错

primary key 主键约束

# 非空且唯一 就是主键约束
create table t4(id int primary key,name varchar(20) not null unique key);

insert into t4 values(1,'yy');

auto_increment 自增长约束

create table t5(id int primary key auto_increment, name varchar(10));
insert into t5(name)  values('xiaowang'),('laowang');

create table tb5(id int primary key auto_increment, name varchar(10))auto_increment=100;

foreign key 外键约束

create table a(a_id int primary key auto_increment,
               a_name varchar(20) not null
              );
              
insert into a values(1,'a1'),(2,'a2');  

select * from a;
+------+--------+
| a_id | a_name |
+------+--------+
|    1 | a1     |
|    2 | a2     |
+------+--------+


学生表 （id  name  number）  10 个学员

学生详情表 （age  sex  h  t   爱好  。。。。。 ）    

create table b(b_id int primary key,
               age int not null,
               sex varchar(20),
               hei int,
               fy_id int not null,
               foreign key(fy_id) references a(a_id)
              );
              
insert into b values(1,19,'nan',178,1),(2,20,'nv',172,2);


mysql> select * from b;
+------+---------+
| a_id | a_name  |
+------+---------+
|    1 | tongyao |
|    2 | tangsan |
+------+---------+

+------+-----+------+------+-------+
| b_id | age | sex  | hei  | fy_id |
+------+-----+------+------+-------+
|    1 |  19 | nan  |  178 |     1 |
|    2 |  20 | nv   |  172 |     2 |
+------+-----+------+------+-------+