create table tb_user
(
    id     int auto_increment comment '主鍵id'
        primary key,
    name   varchar(10) null comment '姓名',
    age    int         null comment '年齡',
    gender char        null comment '年齡 1男 2女',
    phone  char(11)    null comment '手機(jī)號(hào)'
)
    comment '用戶基本信息表';
 

create table tb_user_edu
(
    id            int auto_increment comment '主鍵id'
        primary key,
    degree        varchar(20) null comment '學(xué)歷',
    major         varchar(50) null comment '專業(yè)',
    primaryschool varchar(50) null comment '小學(xué)',
    middleschool  varchar(50) null comment '中學(xué)',
    university    varchar(50) null comment '大學(xué)',
    userid        int         null comment '用戶id',
    constraint userid
        unique (userid),
    constraint fk_userid
        foreign key (userid) references tb_user (id)
)
    comment '用戶教育信息表';
 

create table student(id int auto_increment primary key comment'主鍵id',
name varchar(10) comment'姓名',
novar char(10) comment'學(xué)號(hào)') comment'學(xué)生表';
insert into student values(null,'宋江','001'),(null,'魯智深','002'),(null,'李逵','001');

create table course(
id int auto_increment primary key comment '主鍵id',
name varchar(10) comment'課程名稱'
) comment'課程表';
insert into course values(null,'java'),(null,'js'),(null,'MySQL');

create table student_course
(
    id        int auto_increment comment '主鍵'
        primary key,
    studentid int not null comment '學(xué)生id',
    courseid  int not null comment '課程id',
    constraint f_kcourseid
        foreign key (courseid) references course (id),
    constraint f_kstudentid
        foreign key (studentid) references student (id)
)
    comment '學(xué)生課程中間表';
 

create table dept
(
    id   int auto_increment comment 'id'
        primary key,
    name varchar(50) not null comment '部門名稱'
)
    comment '部門表';
 

create table emp
(
    id         int auto_increment comment 'id'
        primary key,
    name       varchar(50) not null comment '姓名',
    age        int         null comment '年齡',
    job        varchar(20) null comment '職位',
    salary     int         null comment '薪水',
    entrydate  date        null comment '入職時(shí)間',
    mangagerid int         null comment '直屬的領(lǐng)導(dǎo)id',
    dept_id    int         null comment '部門id',
    constraint dept_id
        foreign key (dept_id) references dept (id)
)
    comment '員工表';
 

select 字段名 from 表1,表2 where 條件....;

select 字段名 from 表1 [inner] join 表2 no 連接條件;

select emp.name,dept.name from emp,dept where emp.id=dept.id;

select emp.name,dept.name from emp join dept on emp.id=dept.id;

select 字段列表 from 表1 left [outer] join 表2 on 條件...;

select 字段列表 from 表1 right [outer] join 表2 on 條件...;

select e.*,d.name from emp e left outer join dept d on d.id=e.id;

select d.*,e.* from emp e right outer join dept d on d.id=e.id;

select d.*,e.* from dept d left outer join emp e on d.id=e.id;

select 字段列表 from 表A 別名A join 表B 別名B on 條件;

SELECT 字段列表 FROM 表A ....UNION [ ALL]SELECT 字段列表 FROM 表B ....;