1，多表查询前创建表

1，创建库test
		create database test;
		

2，创建部门表dept(deptno dname loc)
		create table dept(
		deptno int(4) primary key,
		dname varchar(20),
		loc varchar(20)
		);
	
	---给部门表插入数据
	insert into dept values (10, 'Accounting', 'New York'),(20, 'Research', 'Dallas'),(30, 'Sales', 'Chicago'),(40, 'Operations', 'Boston');
	desc dept;
	---查看部门表中数据
	select * from dept;
	

3，创建雇员表emp 
	---创建emp表
	create table emp( 
	empno int(4) primary key, ename varchar(10), 
	job varchar(9), 
	mgr int(4), 
	hiredate date, 
	sal decimal(7,2), 
	comm decimal(7,2), 
	deptno int(2), 
	constraint fk_deptno foreign key(deptno) references dept(deptno) 
	);
	---给emp表插入数据
	insert into emp values (7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20), (7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30), (7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30), (7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20), (7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30), (7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30), (7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10), (7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20), (7839, 'King', 'president', null, '1981-11-17', 5000, null, 10), (7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30), (7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20), (7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30), (7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20), (7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
	---查看emp表
	select * from emp;
	select count(*) from emp;

小扩展：

如果查询信息来自单张表称之为单表查询

如果查询信息来自多张表称之为多表查询

2，交叉连接（关键字cross join）

交叉连接指的是：两张或者多张表进行的笛卡尔积(两张或者多张表中每一行的数据任意组合的结果)
语法格式为：select 查询内容 from  table1 cross join table2

		---将emp表和dept表进行交叉连接
		select * from emp cross join dept;
		select count(*) from emp cross join dept;

特点：
总数据条数是 table1数据条数 * table2数据条数
总列数是 table1列数+table2列数

3，自然连接（关键字natural join）

是指关系表中相同名称的字段进行自动匹配产生的结果，会去掉重复的列
语法格式为：select 查询内容 from table1 natural join table2

		---将emp表和dept表进行自然连接
		select * from emp natural join dept;
		---查询自然连接后总记录条数
		select count(*) from emp natural join dept;

特点：
关联的表中必须有相同名称的字段（字段名称相同，字段数据类型相同）
查询结果中，去掉重复的相同字段

4，内连接

是指：两张或者多张表中按给定的连接条件查询出满足条件的结果

SQL92：语法select 查询内容 from table1 ,table2 where 关联条件
		---查询雇员信息，以及雇员所在部门的信息
		select * from emp,dept where emp.deptno=dept.deptno;
		---使用别名查询雇员信息，以及雇员所在部门的信息
		select * from emp e,dept d where e.deptno=d.deptno;

SQL99：语法select 查询内容 from table1 inner join table2 on 关联条件
		---查询雇员信息，以及雇员所在部门的信息
		select * from emp e inner join dept d on e.deptno=d.deptno;
		---查询部门号为30的雇员信息，及部门名称
		select e.*,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.deptno=30;

注意：关键词inner join 其中inner可以省略

5，自连接

是指：连接两张表是同一张表

SQL92：语法select 查询内容 from table1 ,table2 where 关联条件
		---查询雇员姓名，雇员薪资，雇员领导姓名，雇员领导薪资
		select e.empno,e.ename,e.sal,e.mgr,m.empno,m.ename,m.sal
		from emp e,emp m
		where e.mgr=m.empno;

SQL99：语法select 查询内容 from table1 inner join table2 on 关联条件
		---查询雇员姓名，雇员薪资，雇员领导姓名，雇员领导薪资
		select e.empno,e.ename,e.sal,e.mgr,m.empno,m.ename,m.sal
		from emp e join emp m
		on e.mgr=m.empno;							


---雇员号大于领导号的雇员姓名，雇员号，领导号，领导姓名

SQL92
select e.ename,e.empno,m.ename,m.empno
from emp e,emp m
where e.mgr=m.empno and e.empno>m.empno;

SQL99
select e.ename,e.empno,m.ename,m.empno
from emp e join emp m
on e.mgr=m.empno and e.empno>m.empno;

---另外一种写法是
select e.ename,e.empno,m.ename,m.empno
from emp e join emp m
on e.mgr=m.empno 
where e.empno>m.empno;

6，外连接

是指：不仅要查询两张或者多张满足条件的记录，还要查询其中某张表中不满足连接条件的记录
语法格式为：
		select 查询内容
		from table1 left|right [outer] join table2
		on 连接条件

left join：左外连接 将放到left join 左边表中不满足条件的记录也显示
right join：右外连接 将放到right join 右边表中不满足条件的记录也显示

---查询雇员信息及雇员所在部门信息,没有雇员的部门也要显示出来
select * from emp e,dept d where e.deptno=d.deptno; 此语句没有达到要求
select count(*) from emp e,dept d where e.deptno=d.deptno; 

---查询雇员信息及雇员所在部门信息,没有雇员的部门也要显示出来
------right join右外连接
select *
from emp e right join dept d
on e.deptno=d.deptno; 

------left join左外连接
select *
from dept d left join emp e
on e.deptno=d.deptno; 


---查询雇员信息，以及雇员领导信息，没有领导的雇员也要显示出来
---左连接
select *
from emp e left join emp m
on e.mgr=m.empno;
---右连接
select *
from emp m right join emp e   
on e.mgr=m.empno;

7，子查询

是指：一个查询语句中包含了另一个查询语句

子查询的作用：
		1，作为条件 语法如下
				select 查询内容
				from 表
				where 字段 operator (子查询)
				
			a，标量子查询 返回的是一个单一的值
				---查询雇员薪资高于“Clark”的雇员信息
				select * from emp where sal>(select sal from emp where ename="Clark");，
			b，行子查询 返回的结果是一行N列
				---查询雇员表中部门职位与“Allen”相同的雇员信息
				select * from emp where (deptno,job)=(select deptno,job from emp where ename="Allen");
			c，列子查询 返回的结果是多条数据
			列子查询的操作符是：in any all some exists
			不能使用单行操作符：>   <    >=   <=   !=

				使用in的列子查询
				---查询部门20中同部门10中职位相同的雇员信息
				select * from emp 
				where job in(select job from emp where deptno=10) and deptno=20;
				
				使用any或者some列子查询
				any：返回结果中的任何一个数据 some是any的别名很少使用
				---查询雇员表emp中月薪低于任何一个clerk月薪的雇员信息
				先查询从事职位为clerk的雇员月薪
				select sal from emp where job="clerk";
				select * from emp
				where sal< any(select sal from emp where job="clerk")
				
				使用all的列子查询 返回结果中的所有数据
				---查询雇员表emp中月薪低于所有职位为clerk月薪的雇员信息
				select * from emp
				where sal< all(select sal from emp where job="clerk");
				
				使用exists的列子查询
				子查询语句返回的不是查询记录的结果集而是一个布尔类型的值
				如果子查询有满足条件的记录则返回True,则会执行主查询
				如果子查询无满足条件的记录则返回False,则不会执行主查询
				---查询dept表中有雇员的部门信息
				select * from dept
				where exists(select ename from emp where deptno=dept.deptno);			

		2，作为表
				select 查询内容
				from (子查询)
				where 条件
				
		---查询emp表中，每个部门平均工资最高的平均工资
		---先查询每个部门的平均工资
		select avg(sal) from emp group by deptno; 
		---子查询作为表，查询最高的平均工资
		select max(avgsal)
		from (select avg(sal) avgsal from emp group by deptno) avg_sal;
		
		---查询每个部门的部门人数，部门名称
		---先查询每个部门的部门人数
		select count(*),deptno from emp group by deptno;
		---子查询与部门表连表查询获取部门名称
		select d.dname,dd.cou
		from dept d,(select count(*) cou,deptno from emp group by deptno) dd
		where d.deptno=dd.deptno;
		
		SQL99的实现方式如下：
		select d.dname,dd.cou
		from dept d inner join (select count(*) cou,deptno from emp group by deptno) dd
		on d.deptno=dd.deptno;

多表查询练习如下：

---列出至少有4位员工的所有部门信息
11，先查询每个部门的人数  
select deptno,count(*)from emp group by deptno;
22，至少有4位
select deptno,count(*)from emp group by deptno having count(*)>4;
33，部门信息 将上面的子查询作为表
select d.*,dd.cou
from dept d,(select deptno,count(*) cou from emp group by deptno having count(*)>4) dd where d.deptno=dd.deptno;


---列出薪资比smith多的员工信息
11，先查询Smith的薪资 mysql不区分大小写
select sal from emp where ename="smith";
22，查询比子查询结果高的雇员信息
select * from emp where sal > (select sal from emp where ename="smith");


---列出所有员工的姓名，及其直接上级。没有领导的雇员也要列出。
select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;


---列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
11，查询员工编号和姓名
select e.ename,e.empno
from emp e join emp m on e.mgr=m.empno where e.hiredate<m.hiredate;
22，查询部门名称 连接部门表
select e.ename,e.empno,d.dname
from emp e join emp m on e.mgr=m.empno 
join dept d on e.deptno=d.deptno
where e.hiredate<m.hiredate;


---列出所有clerk办事员的姓名及其部门名称， 部门人数
11，查询所有职位为clerk办事员的姓名
select ename from emp where job="clerk";
22，查询部门人数
select deptno,count(*) from emp group by deptno;
33，查询部门名称，将部门人数子查询作为表
select d.dname,dd.cou,e.ename
from dept d join(select deptno,count(*) cou from emp group by deptno) dd 
on d.deptno=dd.deptno
join emp e on d.deptno=e.deptno
where job="clerk";


---列出最低薪资大于1500的各种工作及从事此工作的全部雇员人数
11，最低薪资大于1500的各种工作
select job from emp group by job having min(sal)>1500
22，每个工作的雇员人数
select job,count(*) from emp 
where job in(select job from emp group by job having min(sal)>1500)
group by job; 



---列出在销售部sales工作的员工姓名，假定不知道销售部的部门编号
11，查询销售部的部门号
select deptno from dept where dname="sales"
22，查询子查询结果的雇员姓名
select ename from emp where deptno=(select deptno from dept where dname="sales");
 

---列出薪资高于公司平均薪资的所有员工，所在部门，上级领导
11，查询高于平均薪资的雇员信息
select e.*
from emp e where sal>(select avg(sal) from emp)
22，连接部门表
select e.*,d.*
from emp e join dept d on e.deptno=d.deptno
where sal>(select avg(sal) from emp)
33，查询上级领导的信息 连接emp表
select e.*,d.*,m.*
from emp e join dept d on e.deptno=d.deptno
join emp m on e.mgr=m.empno
where e.sal>(select avg(sal) from emp)


---列出与“SCOTT”从事相同工作的所有员工及部门名称
select e.*,d.dname
from emp e join dept d on e.deptno=d.deptno
where job=(select job from emp where ename="scott") and e.ename!="scott"

---列出薪资高于在30部门工作的所有员工的姓名，和薪资及部门
11，部门30的薪资
select sal from emp where deptno=30
22，高于在30部门工作的所有员工的薪资
select e.ename,e.sal,
from emp e
where e.sal>all(select sal from emp where deptno=30)
33，查询部门 连接dept表
select e.ename,e.sal,d.dname
from emp e join dept d on e.deptno=d.deptno
where e.sal>all(select sal from emp where deptno=30)


---列出在每个部门工作的员工数量，部门名称，平均工资和平均服务期限
11，查询平均服务期限
select datediff(sysdate(),hiredate)/365 from emp

select count(*),avg(sal),d.dname,avg(datediff(sysdate(),hiredate)/365)
from dept d join emp e on d.deptno=e.deptno
group by d.dname

---找出部门10中所有经理和部门20中的所有办事员的详细资料
select *
from emp 
where (deptno=10 and job="manager") or (deptno=20 and job="clerk");

---找出不收取佣金或收取的佣金低于100的雇员
select * from emp where comm is null or comm < 100
另外一种写法为：
select * from emp where ifnull(comm, 0) < 100

---找出各月最后一天受雇的所有雇员
select * from emp last_day(hiredate) = hiredate

---显示正好为6个字符的雇员姓名
select ename from emp where length(ename)=6;

---显示不带有'R'的雇员姓名
select ename from emp where ename not like "%sR%s";

---显示所有雇员的姓名，用a替换所有'A'
select replace(ename,"A","a") from emp;

---显示所有雇员的姓名的前三个字符
select substring(ename,1,3)from emp;
另一种写法为：
select left(ename,3)from emp;

---显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename,hiredate from emp order by hiredate asc;

---显示所有雇员的姓名、工作和薪金，按工作的升序排序，而工作相同时按薪金降序
select ename,job,sal from emp 
order by job asc,sal desc

---显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的员工排在最前面
select year(hiredate), month(hiredate) from emp

---找出在（任何年份的）2月受聘的所有雇员
select * from emp where month(hiredate)=2;

---对于每个雇员，显示其加入公司的天数
select ename,datediff(curdate(),hiredate) from emp;
另外一种写法如下：
select ename,datediff(now(),hiredate) from emp;

---查询平均工资最高的部门的部门编号、部门名称和该部门的平均工资
11，先查询每个部门的平均工资
select avg(sal) from emp group by deptno

select avg(sal),d.dname,d.deptno
from emp e join dept d on e.deptno=d.deptno
group by d.deptno having avg(sal)>=all(select avg(sal) from emp group by deptno)

---查询所有员工的年薪、所在部门的名称，查询结果按年薪从低往高排序。年薪包含薪金和佣金
select e.ename,(sal+ifnull(comm,0))*12 as total,d.dname
from emp e join dept d on e.deptno=d.deptno
order by total asc

---查询每种工作的最低工资，以及领取该工资的员工姓名，
结果显示工作名称、最低工资、领取该工资的员工姓名
select ename,sal,job from emp where sal in
(select min(sal) from emp group by job)

---查询出管理员工人数最多的人的名字和他管理的人的名字
select m.ename mgr_name, m.empno mgr_no, e.empno, e.ename
from emp m join emp e on m.empno = e.mgr
where m.empno = 
(select mgr from emp group by mgr
having count(*) >= all(select count(*) from emp group by mgr))

---查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年薪降序排列
select e.ename,e.empno,m.empno m_empno,m.ename m_ename,12*(m.sal+ifnull(m.comm,0)) annual_sal
from emp e join emp m on e.mgr=m.empno
order by 12*(m.sal+ifnull(m.comm,0)) desc

---查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工信息
select d.dname dept_name, e.*
from emp e join dept d on e.deptno=d.deptno
where sal<2500 and e.deptno=(select deptno
	from emp where sal<=2500
	group by deptno
	having count(*)>=all(select count(*) from emp where sal<=2500 group by deptno))

---查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e.deptno e_deptno,e.ename,d.dname
from emp e join emp m on e.mgr=m.empno
join dept d on e.deptno=d.deptno
where e.hiredate<m.hiredate

---查询部门名称中带'S'字符的部门的员工的工资总和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数
select d.deptno,d.dname,count(*),sum(sal)
from emp e join dept d on e.deptno=d.deptno
where d.dname like "%S%"
group by e.deptno

---查询在"sales"部门（销售部）工作的员工的姓名
select e.ename
from emp e join dept d on e.deptno=d.deptno
where d.dname="sales"

---显示每个部门中每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资
select t1.deptno, t1.job, t1.avg_sal1, t2.deptno, t2.avg_sal2, t3.job, t3.avg_sal3
from 
	(select deptno, job, avg(sal) avg_sal1 from emp group by deptno, job) t1
	,
	(select deptno, avg(sal) avg_sal2 from emp group by deptno) t2
	,
	(select job, avg(sal) avg_sal3 from emp group by job) t3
where
  t1.deptno = t2.deptno and t1.job = t3.job

---显示与"BLAKE"同部门的所有员工的基本信息，但不显示"BLAKE"的基本信息
select * 
from emp where deptno=(select deptno from emp where ename="blake") and ename != "blake"

---查询出"WARD"所在部门的工作年限最长的员工的姓名
select ename,hiredate from emp where deptno=(select deptno from emp where ename ="ward") having min(hiredate)

---查询出员工姓名以A开头的人数最多的部门的部门名称
select d.dname 
from dept d 
where deptno in(
  select deptno 
  from emp 
  where ename like 'A%' 
  group by deptno 
  having count(*)>=all(
    select count(*)from emp where ename like'A%' group by deptno));

---查询出没有下属的员工的姓名及他的职位
select ename,job from emp where empno not in(select mgr from emp where mgr is not null);

---查询出SMITH所在部门的部门名称、部门工资的平均值（注意平均值保留两位小数）
select d.dname,format(avg(sal),2) avg_sal
from emp e,dept d 
where e.deptno=d.deptno and e.deptno=(select deptno from emp where ename='SMITH')
group by d.dname;

注意注意！！！重要重要！！！

sql执行顺序

(1)，from

(3)， join

(2)， on

(4)， where 后面不能跟聚合函数

(5)，group by(开始使用select中的别名，后面的语句中都可以使用)

(6)，avg,sum,count 聚合函数

(7)，having 语句后面跟聚合函数

(8)，select

(9)， distinct

(10)， order by

1，多表查询前创建表

2，交叉连接（关键字cross join）

3，自然连接（关键字natural join）

4，内连接

5，自连接

6，外连接

7，子查询

多表查询练习如下：

注意注意！！！重要重要！！！

继续阅读

linux 上 MySQL 源代码安装

MySql表链接详解(结合Hadoop中的Hive数据仓库)

mysql进阶：optimize table命令

mysql客户端连接服务器出错

MYSQL练习2

MySql的基本使用MySql的启动与停止MySql的登录与退出常见的命令

数据库学习：高并发数据库设计

学习Shell脚本必须先掌握Shell脚本的基础知识。249个Shell脚本随学随用，包括：1、防范DOS攻击的脚本。2

记一次失败的创建MYSQL表格过程

数据库MySQL-2-数据类型、SQL语言、DDL、DML、DQL、TCL、DCLMySQL数据类型数据库中数据的使用

Mysql入门之简单的DQL查询语句【Mysql数据库基础】

MySql查询语句小汇总一（DQL语句）

DQL查询语句&&约束&&数据库设计&&备份和还原DQL:查询语句

Mysql入门之简单的DML数据操作语句【Mysql数据库基础】

mysql之DQL(Data Query Language)普通查询语句常用字段常用函数分组查询联合查询联合查询(union)子查询

使用SQL管理数据库1、MySQL服务器的SQL模式2、SQL语句中的字母大小写问题3、字符集支持3.4 事务处理