快速上手MySql && MySql GUI工具 SQLyog Community （5）

(本节内容主要是多表连接与子查询)

/*多表连接查询

1）使用单个SELECT语句从多个表中取出相关的数据，通过多表之间关系，构建相关数据的查询。
2）多表连接通常是建立在相互关系的父子表上的。
3）SQL1999标准中多表连接的语法。
SELECT...FROM join_table
JOIN_TYPE join_table
ON join_condition
WHERE where_condition


#join_table:参与连接的表
#JOIN_TYPE：连接类型，内连接、外连接、交叉连接、自连接
#join_condition：连接条件
#WHERE where_condition：过滤条件


#交叉连接/笛卡尔连接

*/


#交叉连接/笛卡尔连接:返回两张表的连接
SELECT COUNT(*) FROM tb_emp;#1
SELECT COUNT(*) FROM tb_dept;#5


SELECT * FROM tb_emp,tb_dept;#5=1*5
SELECT COUNT(*) FROM tb_emp,tb_dept;


SELECT * FROM tb_emp CROSS JOIN tb_dept;


#内连接 (连接条件就是主外键关联)
#只列出这些连接表中与连接条件相匹配的数据行，分为：
SELECT * FROM tb_emp e,tb_dept d WHERE e.deptno=d.deptno;


SELECT * FROM tb_emp d,tb_dept e WHERE d.deptno=e.deptno;


SELECT * FROM tb_dept INNER JOIN tb_emb
ON tb_demp.deptno = tb_emp.deptno;




#外连接：不仅列出与连接条件相匹配的行，还列出左表、右表或两个表中所有符合WHERE过滤条件的数据行。


#左外连接,某些不满足条件的列也会列出来，只限制一个表的行，不限制另外一个表的行
#tb_emp做主表，左表作为主表，左表记录全部显示，如果没有找到记录则补NULL


SELECT * FROM tb_dept LEFT JOIN tb_emp
ON tb_dept.deptno=tb_emp.deptno;


#oracle语法，左连接,加号在右边
SELECT *FROM tb_emp e,tb_dept d WHERE e.deptno=d.deptno(+);


#右连接
SELECT * FROM tb_dept RIGHT JOIN tb_emp
ON tb_dept.deptno=tb_emp.deptno;


#自连接：参与连接的表都是同一张表（通过给表取别名虚拟出）
SELECT c.name '类别名',c.name '父类别名'
FROM tb_course c LEFT JOIN tb_course c2
ON c.pid = c2.id




SELECT c.name '类别名',c.name '父类别名'
FROM tb_course c,tb_course c2
WHERE c.pid = c2.id


/*
子查询
1）某些情况下，进行查询的时候，需要的条件是另外一个select语句的结果，这个时候就要用到子查询。
2）为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询。
3）用于子查询的关键字包括in、not in、=、<>等。
4）MySql从4.1开始之初SQL的子查询。
5）一般子查询的效率低于连接查询、表连接都可以用子查询替换，但反过来却不一定。


*/


#子查询
SELECT * FROM tb_emp


#查询构造比ALLEN高的员工
SELECT sal FROM tb_emp WHERE ename = '艾伦'
SELECT *FROM tb_emp WHERE sal > 1600


SELECT * FROM tb_emp
WHERE sal > (
SELECT sal FROM tb_emp WHERE ename = '艾伦'
)


#查询与ALLEN同一个部门的员工
SELECT * FROM tb_emp
WHERE deptno = (
SELECT deptno FROM tb_emp WHERE ename ='艾伦'
)


#查询月薪最高的员工名字
SELECT Ename,sal FROM tb_emp
WHERE sal = (
SELECT MAX(sal) FROM tb_emp
)


#单行子查询返回多个结果：错误
SELECT * FROM tb_emp 
WHERE sal = (
SELECT MIN(sal) FROM tb_emp
GROUP BY deptno
)


#子查询没有返回结果


SELECT * FROM tb_emp
WHERE job =(
SELECT job FROM tb_emp WHERE NAME ='xb'
)


#in与列表中任一成员相等（等于子查询的任意一个结果）
SELECT * FROM tb_emp
WHERE sal IN(
SELECT sal FROM tb_emp WHERE job ='市场销售'
)


#any与子查询返回的每一个值比较（小于子查询的任意一个结果）
SELECT * FROM tb_emp
WHERE sal < ANY (
SELECT sal FROM tb_emp WHERE job ='市场销售'
)


#查询出平均月薪最高的部门编号与名称
SELECT deptno,AVG(sal) 
FROM tb_emp
GROUP BY deptno
ORDER BY AVG(sal) DESC
LIMIT 0,1