多表查询
如:
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, '2000-12-17', '8000.00', NULL, 20 ),
( 1002, '卢俊义', 3, 1006, '2001-02-20', '16000.00', '3000.00', 30 ),
( 1003, '林冲', 3, 1006, '2001-02-22', '12500.00', '5000.00', 30 ),
( 1004, '唐僧', 2, 1009, '2001-04-02', '29750.00', NULL, 20 ),
( 1005, '李逵', 4, 1006, '2001-09-28', '12500.00', '14000.00', 30 ),
( 1006, '宋江', 2, 1009, '2001-05-01', '28500.00', NULL, 30 ),
( 1007, '刘备', 2, 1009, '2001-09-01', '24500.00', NULL, 10 ),
( 1008, '猪八戒', 4, 1004, '2007-04-19', '30000.00', NULL, 20 ),
( 1009, '罗贯中', 1, NULL, '2001-11-17', '50000.00', NULL, 10 ),
( 1010, '吴用', 3, 1006, '2001-09-08', '15000.00', '0.00', 30 ),
( 1011, '沙僧', 4, 1004, '2007-05-23', '11000.00', NULL, 20 ),
( 1012, '李逵', 4, 1006, '2001-12-03', '9500.00', NULL, 30 ),
( 1013, '小白龙', 4, 1004, '2001-12-03', '30000.00', NULL, 20 ),
( 1014, '关羽', 4, 1007, '2002-01-23', '13000.00', NULL, 10 );-- 添加5个工资等级
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES
( 1, 7000, 12000 ),
( 2, 12010, 14000 ),
( 3, 14010, 20000 ),
( 4, 20010, 30000 ),
( 5, 30010, 99990 );
1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
-- 隐式内连接
SELECT
emp.id,
ename,
salary,
jname,
description
FROM
emp,
job
WHERE
emp.job_id = job.id;-- 显式内连接
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp
INNER JOIN job ON emp.job_id = job.id;
2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
-- 三个表的隐式内连接
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc
FROM
emp,
job,
dept
WHERE
emp.job_id = job.id
AND emp.dept_id = dept.id;-- 三个表的显式内连接
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id;
3.查询员工姓名,工资,工资等级
-- 隐式内连接
SELECT
emp.ename,
emp.salary,
salarygrade.grade
FROM
emp,
salarygrade
WHERE
emp.salary > salarygrade.losalary
AND emp.salary < salarygrade.hisalary -- 上面这句怎么理解呢? 应该先理解把两张表和在一块,然后把其中的emp.ename,emp.salary,salarygrade.grade显示出来
-- 显式内连接
SELECT emp.ename, emp.salary, salarygrade.grade FROM emp INNER JOIN salarygrade ON emp.salary > salarygrade.losalary
AND emp.salary < salarygrade.hisalary
4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
-- 四个表的内连接
SELECT
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
salarygrade.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade ON emp.salary > salarygrade.losalary
AND emp.salary < salarygrade.hisalary;
5.查询出部门编号、部门名称、部门位置、部门人数
-- 部门人数:在emp表中按照dept_ id进行分组,然后count(*)统计数量
SELECT
dept_id,
count(*)
FROM
emp
GROUP BY
dept_id;
--分隔
SELECT
*
FROM
dept;
-- 联合上面两个表进行子查询
SELECT
dept.id,
dept.dname,
dept.loc,
t1.renshu
FROM
( SELECT dept_id, count(*) renshu FROM emp GROUP BY dept_id ) t1,
dept
WHERE
t1.dept_id = dept.id;