python学习笔记 day44 表与表之间的关系

create table person(
  id varchar(10) not null PRIMARY key,  # 人员信息表的主键 其实是跟car表关联的字段
  name varchar(20) not null,
  age int not null,
  sex int not null)

alter table person modify sex char(2) not null;

insert into person values("P0001","西西",22,"女");
insert into person values("P0002","东东",23,"男");
insert into person values("P0003","楠楠",24,"女");

select * from person;

create table car(
  cid varchar(10) not null primary key, # car表中的主键
  name varchar(20) not null default "大众" ,
  p_id varchar(10) not null,  # car表中跟person表中关联的字段，需要设置为外键约束
  constraint fk_pid foreign key(p_id) references person(id))    # 设置为外键约束（关联另一张表person的主键id）

insert into car values("c0001","大众","P0001"); # 这两个车都是属于同一个人P0001
insert into car values("c0002","奔驰","P0001"); # 这两个车都是属于同一个人P0001
insert into car values("c0003","奥迪","P0002"); # c0002车属于P0002这个人
insert into car values("c0004","奥迪","P0003");
select * from car;

create table class(
  id varchar(10) not null primary key,  # 班级id设置为主键，其实也是两张表的关联字段
  c_name varchar(30) not null)

insert into class values("C0001","python课程班级");
insert into class values("C0002","java课程班级");
select * from class;

create table student(
  id varchar(10) not null primary key,
  name varchar(30) not null,
  age int not null default 22,
  sex char(2) not null default "女",
  c_id varchar(20) not null,   # student表中的外键，关联的是class表中的班级id（主键）所以多student表中添加外键约束即可；
  constraint fk_student_cid foreign key(c_id) references class(id)  # 对student表中的c_id设置外键约束，关联的是class表的班级id
                                             # 需要注意 student表的外键c-id 必须跟class表的主键id数据类型一样~~
)
insert into student values("S0001","西西",22,"女","C0001");  # S0001 S0002属于同一个班级，student表的c-id字段可以重复（也就是多）
insert into student values("S0002","静静",23,"女","C0001");
insert into student values("S0003","东东",24,"男","C0002");
insert into student values("S0004","二狗子",25,"男","C0002");

select * from student;

create table card(
  id varchar(10) not null primary key,  # card 表的主键，person表c_id的关联字段（主键和外键数据类型必须一样，其实就是一个东西，只是在两张表中都出现）
  code varchar(20) not null unique    # 需要为普通字段添加唯一约束，因为一对一指的就是card的code普通字段 还有person表的c-id 外键+唯一约束
)

insert into card values("C0001","129304903940");
insert into card values("C0002","232392039094");
insert into card values("C0003","403903403283");  # 三条不同的身份证信息
select * from card;

create table person_info(
  id varchar(10) not null primary key,  # person表的主键
  name varchar(20) not null,
  age int not null default 22,
  sex char(2) not null default "女",
  c_id varchar(10) not null unique, # 跟card表的主键id关联的字段,需要添加外键和唯一约束的
  constraint fk_person_cid foreign key(c_id) references card(id))

insert into person_info values("P0001","西西",22,"女","C0003");   # P0001 对应的身份证信息是C0003那条数据；
insert into person_info values("P0002","楠楠",24,"男","C0001");  
insert into person_info values("P0003","宝宝",23,"女","C0002");  
select * from person_info;

create table user(
  id varchar(10) not null primary key,
  name varchar(20) not null)

insert into user values("U0001","西西");
insert into user values("U0002","东东");
select * from USER;

create table blog(
  id varchar(10) not null primary key,
  name varchar(20) not null, 
  user_id varchar(10) not null unique,  -- 两张表的关联字段，设置为外键（关联的是user表的主键id，通过外键user-id就可以唯一找到这个人
  constraint blog_userid foreign key(user_id) references user(id))  -- 需要为外键添加唯一约束（这样两张表就建立一对一的关系）
insert into blog values("B0001","璇璇是小仙女呀","U0002");  # 这个博客是U0002用户的
insert into blog values("B0002","小仙女哈哈哈哈","U0001");

select * from blog;

create table student_info(
  id varchar(10) not null primary key,
  name varchar(20) not null,
  age int not null,
  sex char(2) not null
)

insert into student_info values("S0001","璇璇",22,"女");
insert into student_info values("S0002","西西",23,"女");
insert into student_info values("S0003","东东",24,"男");
insert into student_info values("S0004","楠楠",25,"男");


select * from student_info;

create table course(
  id varchar(10) not null primary key,
  name varchar(10) not null)

insert into course values("C0001","python课程");
insert into course values("C0002","java课程");
insert into course values("C0003","c++课程");

select * from course;

create table relation_info(
  s_id varchar(10) not null,
  c_id varchar(10) not null,
  primary key(s_id,c_id),   # 将s_id 与 c_id设置为联合主键 分别指的是student_info表的学生id 以及course表的课程id
  constraint fk_relation_info_sid foreign key(s_id) references student_info(id),  # 为关系表中的两个字段（其实就是另外两张表的主键，添加外键约束）
  constraint fk_relation_info_cid foreign key(c_id) references course(id))

insert into relation_info values("S0001","C0002");  # s00001选了C0002这门课
insert into relation_info values("S0001","C0003");  
insert into relation_info values("S0001","C0001");  
insert into relation_info values("S0003","C0001");
insert into relation_info values("S0003","C0002");
insert into relation_info values("S0003","C0003");
insert into relation_info values("S0002","C0002");
insert into relation_info values("S0002","C0003");

select * from relation_info;