2020-11-16连表查询

连表查询

表单结构

• 部门表单
  

• 员工表单
  

• 学生表单
  

查询语句

-- 查询数据
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('学习部'),('组织部');

部分实例