MySQL 多表查询_mysql多表查询语句

创建表语句脚本放在最后

一、合并结果集

        前提是两张表的列数相同

        1.union 合并后去重复

SELECT * FROM student01
UNION
SELECT * FROM student02;

        2.union all 合并后不去重复

SELECT * FROM student04
UNION ALL
SELECT * FROM student06;

二、连接查询

        1.笛卡尔积  假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)} 其中肯定有我们不要的数据，去除不重要的数据，就需要使用下一条方法    

select * from emp,dept;

        2.使用主外键关系做为条件来去除无用信息

                将多张表连接进行查询

                2.1 内连接

                        2.1.1 MySQL方言                        

select * from emp e,dept d where e.deptno = d.deptno;
# emp e ，e是别名

                        2.1.2 标准内连接     inner join ...on   

select * from emp e inner join dept d on e.deptno = d.deptno;

                2.2 外连接        left outer join...on

                        2.2.1 左外连接  先将emp表查询出来，再查询dept表，dept不满足的用null显示

select * from emp e left outer join dept d on e.detpno =  d.deptno;

 emp和dept表通过deptno列连接，左外连接即先将emp表查询出来，如图中1这部分；再查询dept表，即2这部分；由于emp中的保洁员没有deptno，所以dept表中查无此人，此时dept表中将不满足的用null显示。

                        2.2.2 右外连接        right outer join ... on

select * from emp e right outer join dept d on e.deptno = d.deptno;

  emp和dept表通过deptno列连接，右外连接即先将dept表查询出来，如图中1这部分；再查询emp表，即2这部分；由于dept表中deptno为40，而emp表中没有这个部门的员工，此时emp表中将不满足的用null显示。

三、自然连接

        大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：前提是要连接的表有相同的字段名和列类型。

        1.自然内连接 natural join                 

select * from emp natural join dept;

        2.自然外连接   分析过程同上

                2.1 自然左外连接 natural left join

select * from emp natural left join dept;

                2.2 自然右外连接 natural right join

select * from emp natural right join dept;

四、子查询

        子查询就是嵌套查询，即SELECT中包含SELECT。

        几个案例如下：

        1.工资高于JONES的员工。

-- 工资高于JONES的员工。
select * from emp where sal >
(select sal from emp where ename = 'jones');

        2.查询与SCOTT同一个部门的员工。

-- 查询与SCOTT同一个部门的员工。
select * from emp where deptno = 
(select deptno from emp where ename = 'scott');

 

         3.工资高于30号部门所有人的员工信息

-- 工资高于30号部门所有人的员工信息
select * from emp where sal > 
(select max(sal) from emp where deptno = 30);

 

        4.查询工作和工资与MARTIN（马丁）完全相同的员工信息  

-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息
select * from emp where (job,sal) in 
(select job,sal from emp where ename = 'martin')
AND ename != 'martin';

        5.有2个以上直接下属的员工信息

-- 有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN 
(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>2);

         6.-- 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

-- 查询员工编号为7788的员工名称、员工工资、部门名称、部门地址
SELECT ename,sal,dname,loc FROM emp,dept 
WHERE emp.`deptno` = dept.`deptno` AND empno=7788;

        7.-- 求7369员工编号、姓名、经理编号和经理姓名
 

-- 求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno,e1.ename,e2.mgr,e2.ename FROM emp e1, emp e2 
WHERE e1.`deptno`=e2.`deptno` AND e1.empno=7369; 

 

五、创表脚本语句

CREATE DATABASE java232002;
CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);
 
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);
 
CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		DECIMAL(7,2),
	deptno		INT
) ;
 
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
 
CREATE TABLE dept(
	deptno		INT,
	dname		VARCHAR(14),
	loc		VARCHAR(13)
);
 
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');