JAVAWeb学习笔记--多表查询
多表查询
如:
select * from emp , dept;
但是这样直接取的话会有很多无效数据,需要消除。
消除无效数据:
select * from emp , dept where emp.dep_id = dept.did;
内连接
[inner]意思是inner可以省略不写
-- 隐式内连接:查询emp 和 dept 的数据
select * from emp , dept where emp.dep_id = dept.did;
-- 隐式内连接:查询emp的name,gender,dept表的dname
select emp.name , emp.gender , dept.dname from emp,dept where emp.dep_id = dept.did;
-- 显式内连接:查询emp 和 dept 的数据
SELECT * from emp inner join dept on emp.dep_id=dept.did;
外连接
-- 左外连接
-- 查询emp表所有数据和对应的部门信息
select * from emp left join dept on emp.dep_id=dept.did;
-- 右外连接
-- 查询dept表所有数据和对应的员工信息
select * from emp right join dept on emp.dep_id=dept.did;
左右连接中用的最多的是左连接,因为左右可以互换。
子查询
-- 查询'财务部’和'市场部'所有的员工信息
-- 单行单列:就是一次查did,一次把did传入再查员工信息
-- 多行单列
select * from emp where dep_id in(select did from dept where dname='财务部' or dname='市场部');
-- 查询入职日期是'2011-11-11' 之后的员工信息和部门信息
-- 多行多列
select * from emp where join_date > '2011-11-11' ;
select * from emp,dept where emp.dep_id=dept.did;
select * from (select * from emp where join_date > '2011-11-11') t1,dept where t1.dep_id=dept.did;
-- 也就是将(select * from emp where join_date > '2011-11-11') 作为了一个虚拟表进行了与dept的内连接查询
也就是将(select * from emp where join_date > ‘2011-11-11’) 作为了一个虚拟表进行了与dept的内连接查询。
多表查询案例
DROP TABLE
IF
EXISTS emp;
DROP TABLE
IF
EXISTS dept;
DROP TABLE
IF
EXISTS job;
DROP TABLE
IF
EXISTS salarygrade;-- 部门表
CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR ( 50 ), -- 部门名称
loc VARCHAR ( 50 ) -- 部门所在地
);-- 职务表,职务名称,职务描述
CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR ( 20 ), description VARCHAR ( 50 ) );-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY,-- 员工id
ename VARCHAR ( 50 ),-- 员工姓名
job_id INT,-- 职务id
mgr INT,-- 上级领导
joindate DATE,-- 入职日期
salary DECIMAL ( 7, 2 ),-- 工资
bonus DECIMAL ( 7, 2 ),-- 奖金
dept_id INT,-- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY ( job_id ) REFERENCES job ( id ),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( id )
);-- 工资等级表
CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);-- 添加4个部门
INSERT INTO dept ( id, dname, loc )
VALUES
( 10, '教研部', '北京' ),
( 20, '学工部', '上海' ),
( 30, '销售部', '广州' ),
( 40, '财务部', '深圳' );-- 添加4个职务
INSERT INTO job ( id, jname, description )
VALUES
( 1, '董事长', '管理整个公司,接单' ),
( 2, '经理', '管理部门员工' ),
( 3, '销售员', '向客人推销产品' ),
( 4, '文员', '使用办公软件' );-- 添加员工
INSERT INTO emp ( id, ename, job_id, mgr, joindate, salary, bonus, dept_id )
VALUES
( 1001, '孙悟空', 4, 1004,