Mysql多表查询

多表查询

1.内连接查询

1. 隐式内连接： 使用where条件消除无用数据
2. 显式内连接：
   语法： select 字段列表 from 表1 inner join 表2 on 条件

使用内连接查询时关键

1. 应该从哪些表查询
2. 查询条件是什么
3. 查询哪些字段

2.外链接查询

1.左外连接:

• 语法：select 字段列表 from 表1 left join 表2 on 条件；
• 查询的是左表的所有数据以及其交集部分

2.右外链接

• 语法：select 字段列表 from 表1 right join 表2 on 条件；
• 查询的是右表的所有数据以及其交集部分

3.子查询

1. 查询最高工资（查询结果为9000）

    select MAX(salary) from emp;   
   

2. 查询员工信息，并且工资等于最高的（9000）：

    select *from emp where emp.salary=9000;  
   

3. 以上查询一条语句就能搞定：

    select *from emp where emp.salary=(select MAX(salary) from emp);
   

子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断

子查询的结果是多行单列的：

子查询可以作为条件，使用IN来判断

子查询的结果是多行多列的：

子查询可以作为一张虚拟表

多表练习

	create table dept(
	id int PRIMARY KEY,
	dname VARCHAR(50),
	loc VARCHAR(50)
);
insert into dept values
(10,'研发部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

create table job(
	id int PRIMARY KEY,
	jname VARCHAR(20),
	descripition VARCHAR(50)
);

INSERT INTO job VALUES
(1,'董事长','管理整个公司,接单'),
(2,'经理','管理部门员工'),
(3,'销售员','向客人推销产品'),
(4,'文员','使用办公软件');

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,
	FOREIGN KEY(job_id) REFERENCES job(id),
	FOREIGN KEY(dept_id) REFERENCES dept(id)
);

CREATE TABLE salarygrade(
	grade int primary key,
	losalary int,
	hisalary int
);

insert into salarygrade VALUES
(1,7000,12000),
(2,12000,14000),
(3,14000,20010),
(4,20010,30010),
(5,30010,99990);

insert into emp VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(100,'林冲',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);

1. 查询所有员工的信息。查询员工编号，员工姓名，工资，职务名称，职务描述。

    SELECT
    	t1.id,
    	t1.ename,
    	t1.salary,
    	t2.jname,
    	t2.descripition
    FROM 
    	emp t1,
    	job t2
    WHERE 
    	t1.job_id=t2.id;
   

2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

    SELECT
    	t1.id,
    	t1.ename,
    	t1.salary,
    	t2.jname,
    	t2.descripition,
    	t3.loc,
    	t3.dname
    FROM 
    	emp t1,
    	job t2,
    	dept t3
    WHERE
    	t1.dept_id=t3.id
    AND
    	t2.id=t1.job_id;
   

3. 查询员工姓名，工资，工资等级

    SELECT 
    	t1.ename,
    	t1.salary,
    	t2.grade
    FROM
    	emp t1,
    	salarygrade t2
    WHERE
    	t1.salary BETWEEN t2.losalary AND t2.hisalary
    ORDER BY 
    	grade;
   

4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置,工资等级

    SELECT
    	t1.ename,
    	t1.salary,
    	t2.jname,
    	t2.descripition,
    	t3.dname,
    	t3.loc,
    	t4.grade
    FROM
    	emp t1,
    	job t2,
    	dept t3,
    	salarygrade t4
    WHERE
    	t1.dept_id=t3.id
    	AND
    	t2.id=t1.job_id
    	AND
    	t1.salary BETWEEN t4.losalary AND t4.hisalary;
   

5. 查询部门编号，部门名称，部门位置，部门人数

    SELECT
    	t1.id,
    	t1.dname,
    	t1.loc,
    	t2.total
    FROM
    		dept t1,
    		(SELECT
    			dept_id,
    			COUNT(id) total
    		FROM
    			emp
    		GROUP BY dept_id) t2
    WHERE
    	t1.id=t2.dept_id;
   

6. 查询所有员工的姓名及其直接上级的姓名，没有领导的员工也要查询

    SELECT 
    	t1.ename,
    	t1.mgr,
    	t2.ename 上级名字
    FROM
    	emp t1
    	LEFT JOIN
    	emp t2
    ON
    	t1.mgr=t2.id;