1112连表查询课堂演练

表结构

部门表

员工表

学生表

查询语句

-- 查询数据
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;