- 多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。
1. 连接查询
1.1 内连接查询
- 内连接相当于查询A B交集数据
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
select * from emp, dept where emp.dep_id = dept.did;
select emp.name,emp.gender,dept.dname from emp, dept where emp.dep_id = dept.did;
select t1.name,t1.gender,t2.dname from emp t1, dept t2 where t1.dep_id = t2.did;
select * from emp inner join dept on emp.dep_id = dept.did;
select * from emp join dept on emp.dep_id = dept.did;
1.2 外连接查询
- 左外连接:相当于查询A表所有数据和交集部分数据
- 右外连接:相当于查询B表所有数据和交集部分数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
select * from emp left join dept on emp.dep_id = dept.did;
select * from emp right join dept on emp.dep_id = dept.did;
2. 子查询
- 子查询概念:查询中嵌套查询,称嵌套查询为子查询
- 单行单列:子查询语句作为条件值,使用 = != > < 等进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);
- 多行单列:子查询语句作为条件值,使用 in 等关键字进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
SELECT 字段列表 FROM (子查询) WHERE 条件;
案例
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,
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,
ename VARCHAR ( 50 ),
job_id INT,
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
);
INSERT INTO dept ( id, dname, loc )
VALUES
( 10, '教研部', '北京' ),
( 20, '学工部', '上海' ),
( 30, '销售部', '广州' ),
( 40, '财务部', '深圳' );
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 );
INSERT INTO salarygrade ( grade, losalary, hisalary )
VALUES
( 1, 7000, 12000 ),
( 2, 12010, 14000 ),
( 3, 14010, 20000 ),
( 4, 20010, 30000 ),
( 5, 30010, 99990 );
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp,
job
WHERE
emp.job_id = job.id;
SELECT
*
FROM
emp;
SELECT
*
FROM
job;
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description
FROM
emp
INNER JOIN job ON emp.job_id = job.id;
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 dept.id = emp.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
;
SELECT
emp.ename,
emp.salary,
t2.*
FROM
emp,
salarygrade t2
WHERE
emp.salary >= t2.losalary
AND emp.salary <= t2.hisalary;
SELECT
emp.id,
emp.ename,
emp.salary,
job.jname,
job.description,
dept.dname,
dept.loc,
t2.grade
FROM
emp
INNER JOIN job ON emp.job_id = job.id
INNER JOIN dept ON dept.id = emp.dept_id
INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;
select * from dept;
select dept_id,count(*) from emp group by dept_id;
SELECT
dept.id,
dept.dname,
dept.loc,
t1.count
FROM
dept,
( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1
WHERE
dept.id = t1.dept_id;