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;