#左连接
#查询所有部门信息以及该部门员工信息
SELECT
did,dname,eid,ename
FROM
t_department d
LEFT
JOIN
t_employee e
ON
d.did=e.dept_id;
SELECT
did,dname,eid,ename
FROM
t_employee e
LEFT
JOIN
t_department d
ON
d.did=e.dept_id;
SELECT
did,dname,eid,ename
FROM
t_department d
RIGHT
JOIN
t_employee e
ON
d.did=e.dept_id;
UPDATE
t_employee
SET
dept_id=
NULL
where
eid=5;
SELECT
did,dname,eid,ename
FROM
t_department d
LEFT
JOIN
t_employee e
ON
d.did=e.dept_id;
WHERE
e.dept_id
IS
NULL
;
#查询部门信息,保留没有员工的部门信息
SELECT
did,dname,eid,ename
FROM
t_department d
RIGHT
JOIN
t_employee e
ON
d.did=e.dept_id;
WHERE
e.dept_id
IS
NULL
;
SELECT
did,dname,eid,ename
FROM
t_employee e
LEFT
JOIN
t_department d
ON
d.did=e.dept_id;
WHERE
e.dept_id
IS
NULL
;
#查询所有员工信息,以及员工的部门信息
SELECT
eid,ename,did,dname
FROM
t_employee e
LEFT
JOIN
t_department d
ON
e.dept_id=d.did;
SELECT
eid,ename,did,dname
FROM
t_department d
LEFT
JOIN
t_employee e
ON
e.dept_id=d.did;
#查询员工信息,仅保留没有分配部门的员工
SELECT
eid,ename,did,dname
FROM
t_employee e
LEFT
JOIN
t_department d
ON
e.dept_id=d.did
WHERE
e.dept_id
IS
NULL
;
#查询员工信息,不保留没有分配部门的员工
SELECT
eid,ename,did,dname
FROM
t_employee e
LEFT
JOIN
t_department d
ON
e.dept_id=d.did
WHERE
e.dept_id
IS
NOT
NULL
;
SELECT
id,ename,tid,dname
FROM
student_info s
LEFT
JOIN
student_test t
ON
s.id_id=t.tid
WHERE
t.tid
IS
NOT
NULL
;
SELECT
*
FROM
student;
SELECT
*
FROM
souce;
#分析 左右外连接
#显示所有学生的测试信息
SELECT
st.s_id,
name
,C,Java
FROM
student st
LEFT
JOIN
souce s
ON
st.s_id=s.s_id;
#显示参加考试的学生的测试信息
SELECT
st.s_id,
name
,C,Java
FROM
student st
RIGHT
JOIN
souce s
ON
st.s_id=s.s_id;