连表查询

连表查询

表单结构：

部门表单：

员工表单：

学生表单：

查询语句：

-- 查询数据
select * from department;
select * from employee;
select * from student;


-- 通过内连接跨表查询老师的信息
select employee.eid,employee.name,department.name from employee inner join department on employee.department_id = department.did;


-- 左外连接
select employee.eid,employee.name,department.name from employee left join department on employee.department_id = department.did;

select employee.eid,employee.name,department.name from employee right join department on employee.department_id = department.did;

-- 插入一行数据
insert into department(dname) values('教学部');

-- 插入一行数据，不指定列名
insert into department values('防疫指挥部');
insert into employee values('张三丰',1000);
insert into student values('克林',2004,2005),('孙悟空',2004,2005);

-- 插入两行数据
insert into department(dname) select '教质部' union select '后勤部';

-- 插入两行数据
insert into department(dname) values('学习部'),('组织部');


-- 连表查询，查出学生的班主任与教员的名字，并显示出来
select sid,sname as 学生姓名,ep1.name as 班主任,ep2.name as 教员 from student inner join employee as ep1 on student.headmaster_id=ep1.eid inner join employee as ep2 on student.teacher_id = ep2.eid;

部分：