19 SQL——多表查询（多对多，一对一 关系）

"多对多关系"

create  table  student3(
id    int     primary key  auto_increment  comment '主键ID',
name    varchar(10)     comment  '姓名',
nu       varchar(18)     comment '学号'
)comment '学生表';


create  table  course(
id   int     primary key  auto_increment  comment '主键ID',
subject    varchar(10)  comment '课程'
)comment '课程';


create table student3_course
(
    id int auto_increment primary key comment '主键',
    student3_id int   not null          comment '学生外键',
    course_id  int    not null          comment'课程表外键' ,
    constraint WJ_student3_id foreign key (course_id) references course(id),
    constraint WJ_course_id   foreign key (student3_id)references student3(id)
)comment '学生课程中间表';


insert into student3 values(null,'汪汪科','214211'),(null,'时科数','214212'),(null,'时ee','214213'),(null ,'时第科','214214');

insert into course values(null,'java'),(null,'python'),(null,'c'),(null,'c++'),(null,'SQL');

insert into student3_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

"一对一关系"


create table user_xx
(
    id int auto_increment primary key  comment '主键ID',
    name    varchar(10)     comment  '姓名',
    age      int             comment '年龄',
    gender    char(1)      comment '1：男，2：女',
    phone       varchar(19)     comment '手机号'
)comment '用户基本信息表';

create table user_education
(
  id int auto_increment  primary key  comment '主键ID',
  degree varchar(20)                  comment '学历',
  major  varchar(50)                   comment '专业',
  promary_school    varchar(50)        comment '小学',
  middle_school  varchar(50)            comment '中学',
  university  varchar(50)               comment '大学',
  user_id     int   unique                comment '用户ID',

constraint  WJ_user_id foreign key (user_id)references  user_xx(id)

)comment '受教育信息表';

insert into user_xx  (id,name,age,gender,phone)values (null,'黄渤',45,'1',17333097261),
                                                      (null,'冰冰',22,'2',17333097262),
                                                      (null,'马云',26,'1',17333097263),
                                                      (null,'古拉',35,'2',17333097264),
                                                      (null,'黄字段',19,'1',17333097265);
insert into user_education(id, degree, major, promary_school, middle_school, university, user_id) values
                                                                                                         (null,'本科','舞蹈','静安第一小学','静安第一中学','北京舞蹈学院',1),
                                                                                                          (null,'硕士','话剧','杭州第一小学','汉族第一中学','民族大学',2),
                                                                                                         (null,'本科','表演','西安第一小学','西湖第一中学','复旦',3),
                                                                                                         (null,'本科','英语','北京第一小学','北京第一中学','北大',4),
                                                                                                         (null,'本科','相声','安徽第一小学','安徽第一中学','清华',5)

可视化表示