龙空技术网

MySQL之多表查询

IT知识一享 254

前言:

此刻你们对“mysql 集合查询”大体比较关心,大家都想要分析一些“mysql 集合查询”的相关知识。那么小编也在网络上收集了一些关于“mysql 集合查询””的相关资讯,希望你们能喜欢,小伙伴们快快来学习一下吧!

基本语法

        Select           列名列表        From          表名列表        Where …

具体实例

创建部门表:

CREATE TABLE dept(  id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(20));INSERT INTO dept(NAME) VALUE('开发部'),('市场部'),('财务部');

创建员工表:

CREATE TABLE tmp(  id INT PRIMARY KEY AUTO_INCREMENT,  NAME VARCHAR(10),  gender CHAR(1),--性别  salary DOUBLE,--工资  join_date DATE,--入职日期  dept_id INT,  FOREIGN KEY(dept_id) REFERENCES dept(id) --外键,关联部门表(部门表的主键) );INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

上面是多表查询的模板

笛卡尔积

有两个集合A,B,取这两个集合的所有组成情况

要完成多表查询,需要消除无用的数据

多表查询的分类

1.内连接查询

(1)隐式内连接:使用where条件来消除无用的数据

i.举例

-- 第一种写法SELECT emp.name,emp.gender,dept.NAMEFROM emp,deptWHERE emp.dept_id=dept.id;
第二种写法SELECT   t1.name,--员工表的名称  t1.gender,--员工表的工资  t2.name,--部门表的名称FROM  emp t1,  dept t2WHERE  t1.deot_id = t2.id;

(2)显式内查询

i.语法

SELECT 字段列表FROM表名(inner) JOIN表名2ON条件

ii.举例

SELECT * FROMempINNER JOINdeptONemp.dept_id = dept.id;

SELECT *FROMempJOINdeptONemp.dept_id = dept.id;

(3)内连接查询总结

1.从哪些表查询数据

2.条件是什么

3.查询哪些字段

2.外连接查询

(1)左外链接

i.语法

SELECT字段列表FROM表1left [outer] join表2ON条件;-- 查询的是左表所有数据以及其交集部分

(2)右外链接

i.语法

SELECT字段列表FROM表1RIGHT [OUTER] JOIN 表2ON 条件;-- 查询的是右表所有数据以及其交集部分

3.子查询

(1)概念

查询中嵌套查询,称嵌套查询为子查询

(2)举例

查询工资最高的员工工资

-- 查询工资最高的员工信息SELECT MAX(salary) FROM emp;-- 查询工资等于9000的员工信息SELECT * FROM emp WHERE emp.salary = 9000;
--一条SQL语句就完成这个条件SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

(3)子查询的不同情况

i.子查询的结果是单行单列的

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

--举例:查询员工工资小于平均工资的人SELECT * FROM empWHERE emp.salary < (SELECT AVG(salary) FROM emp);

ii.子查询的结果是多行单列的

子查询可以作为条件,使用运算符in来判断

-- 举例:查询财务部和市场部所有的员工信息--一般查询SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';SELECT * FROM emp WHERE dept_id = 3 OR dept_id=2;--通过子查询SELECT *FROMempWHEREdept_idIN(SELECT id FROM dept WHERE NAME='财务部' OR NAME='市场部' );

iii.子查询的结果是多行多列的

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

--举例:查询员工入职日期是2011-11-11日之后的员工信息和部门信息--通过子查询SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.join_date > '2011-11-11') t2,WHEREt1.id=t2.dept_id;--通过内链接SELECT *FROMemp t1,dept t2WHEREt1.dept_id = t2.idANDt1.join_date > '2011-11-11'

多表查询练习

--查询员工姓名,工资,工资等级SELECT  t1.ename,  t1.salary,  t2.gradeFROM   emp t1, salarygrade t2 WHERE    t1.salary BETWEEN t2.losalary AND t2.hisalary;

--查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级 SELECT  t1.ename,  t1.salary,  t2.jname,  t2.description,  t3.dname,  t3.loc,  t4.gradeFROM  emp t1,job t2,dept t3,salarygrade t4WHERE  t1.job_id = t2.id  AND  t1.dept_id = t3.id  AND  t1.salary BETWEEN t4.losalary AND t4.hisalary;

--查询出部门编号,部门名称,部门位置,部门人数 /*分析:1.部门编号,部门名称,部门位置---dept表。部门人数---emp表2.使用分组查询。按照emp.dept_id完成分组,查询COUNT(id)3.使用子查询将第2步的查询结果和dept表进行关联查询*/SELECT   t1.id,t1.dname,t1.loc,t2.totalFROM   dept t1,  (SELECT     dept_id,COUNT(id) total   FROM    emp   GROUP BY dept_id) t2   WHERE t1.id = t2.dept_id;

--查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询SELECT   t1.ename,  t1.mgr,  t2.id,  t2.enameFROM  emp t1LEFT JOIN emp t2ON t1.mgr = t2.id;

标签: #mysql 集合查询