mysql查询多个部门的子部门,数据库（多表查询，子查询）

1、笛卡尔集

1、什么是笛卡尔集

假设集合A={a,b},集合B={0,1,2},

则两个集合的笛卡尔集为　{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

可以拓展到多个集合的情况

2、同时查询两个表(使用一个SQL语句),出现的就是笛卡尔集结果

SELECT *

FROM students,score;

3、查询时给表起别名

SELECT * FROM stu st,score sc;

4、(去除笛卡尔集)在查询时要把主键和外键保持一致

SELECT *

FROM students,score

WHERE students.id = score.sid;

5、连接方式分类

image.png

2、内连接划分

1、99写法

SELECT * FROM

students st,score sc

WHERE st.id = sc.sid;

2、内连接写法

SELECT * FROM students st INNER JOIN score sc ON st.id = sc.sid

3、方法:

2.多表联查约束主外键一样,只是写法改变了

3. ON后面只写主外键

4. (对于结果)如果还有条件直接在后面写WHERE5. 多表联查后还有条件就直接写AND

image.png

3、左外连

1、定义

就是左边表数据全部查询出来,右边表只查询满足条件的

2、例子

SELECT *

FROM students st

LEFT OUTER JOIN score sc

ON st.id = sc.sid;

you

4、右外联

定义

就是右边表数据全部查询出来,左边表只查询满足条件的

例子

SELECT * FROM students st RIGHT OUTER JOIN score sc ON st.id = sc.sid;

image.png

5、多表连接

1、建立学生,分数,科目表(多对多)

多个学生可以去考同一个学科,同一个学生可以考多个科目

2、使用99连接法

SELECT st.name,sc.score,co.name

FROM students st,score sc,course co

WHERE st.id = sc.sid AND sc.cid = co.id

3、使用内联查询

SELECT *

FROM students st

JOIN score sc

ON st.id = sc.sid

JOIN course co

ON sc.cid = co.id;

6、非等值连接

1、非等值查询实现

查询所有员工的姓名,工资,所在部门的名称以及工资的等级.

SELECT e.ename,e.salary,d.dname

FROM emp e,dept d,salgrade g

WHERE e.deptno = d.deptno

AND e.salary >= g.lowSalary

AND e.salary <= g.highSalary;

SELECT e.ename,e.salary,d.dname

FROM emp e,dept d,salgrade g

WHERE e.deptno = d.deptno

BETWEEN g.lowSalary AND g.highSalary;

SELECT * FROM emp e

JOIN dept d ON e.deptno = d.deptno

JOIN salgrade g ON e.salary BETWEEN g.lowSalary AND g.highSalary;

7、自连接

注意

自然连接 还是笛卡尔集现象:两张连接的表中列名称和类型完全一致的列作为条件

1、两张连接的表中列名称和类型完全一致的列作为条件

2、 会去除相同的列

CREATE TABLE students( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20)

);CREATE TABLE score( sid INT PRIMARY KEY AUTO_INCREMENT,

score INT

);

随意添加数据后

99查询(隐式内连接)

SELECT * FROM students,score;

SELECT * FROM students,score WHERE students.id = score.sid;

内连接(显示内连接)

SELECT * FROM students JOIN score ON students.id = score.sid;

自然连接 还是笛卡尔集现象:两张连接的表中列名称和类型完全一致的列作为条件

SELECT * FROM students NATURAL JOIN score;

8、子查询

1、什么是子查询

一个select语句中包含另外一个完整的select语句

或者说两个以上select,那么就是子查询语句了

2、子查询出现的位置

where后,把select查询出的结果当做另外一个select的条件值

from后,把查询出的结果当作一个新表

3、例子：

1、查询与项羽同一个部门人员工

(1)先查出项羽所在的部门编号

SELECT deptno FROM emp WHERE ename = '项羽';

(2)再根据编号查询同一部门的员工

SELECT ename FROM emp WHERE deptno = 20;

(3)把第１条查出来的结果当第2条语句的条件

SELECT ename,deptno FROM empWHERE deptno = (SELECT deptno FROM emp WHERE ename = '项羽')

2、查询30号以内大于2000的薪水的人(放在from,是把这个语句当成一个表)

SELECT ename,salary,deptno FROM empWHERE deptno = 30;

SELECT ename FROM (SELECT ename,salary,deptno FROM emp WHERE deptno=30) AS s WHERE s.salary > 2000;

;

3、查询工资高于程咬金的员工

SELECT salary FROM emp WHERE ename = '程咬金';

SELECT ename,salary FROM emp WHERE salary > (SELECT salary FROM emp WHERE ename = '程咬金')

4、工资高于30号部门所有人的员工信息

(1)先查出30号部门工资最高的那个人

SELECT MAX(salary) FROM emp WHERE deptno=30;

(2)再到整个表中查询大于30号部门工资最高的那个人

SELECT ename,salary FROM emp WHERE salary > (SELECT MAX(salary) FROM emp WHERE deptno=30);

5、查询工作和工资与妲己已完全相同的员工信息

SELECT job,salary FROM emp WHERE ename='妲己';

SELECT * FROM emp WHERE (job,salary) in (SELECT job,salary FROM emp WHERE ename='妲己');

SELECT * FROM emp e,(SELECT job,salary FROM emp WHERE ename='妲己') AS rWHERE e.job = r.job AND e.salary = r.salary;

6、有2个以上直接下属的员工信息

SELECT mgr,GROUP_CONCAT(mgr),COUNT(mgr) FROM empGROUP BY mgr HAVING COUNT(mgr) >= 2;

SELECT * FROM emp WHERE

empno in (SELECT mgr,GROUP_CONCAT(mgr),COUNT(mgr) FROM empGROUP BY mgr HAVING COUNT(mgr) >= 2);

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

SELECT e.ename,e.salary,d.dname,d.local FROM emp e,dept dWHERE e.deptno = d.deptno AND e.empno = 7788;

自连接

例: 求7369员工编号、姓名、经理编号和经理姓名

SELECT mgr FROM emp WHERE empno = 7369;

SELECT * FROM emp WHERE empno = (SELECT mgr FROM emp WHERE empno = 7369)

所以要用到自连接

SELECT * FROM emp e1,emp e2WHERE e1.empno = e2.empno;

SELECT e1.empno,e1.ename,e2.ename FROM emp e1, emp e2WHERE e1.mgr = e2.empno;

AND e1.empno = 7369;