【MySQL】多表操作

USE db1;

CREATE TABLE department(
did INT(4) NOT NULL PRIMARY KEY,
dname VARCHAR(36)
);

CREATE TABLE employee(
id INT(4) NOT NULL PRIMARY KEY,
NAME VARCHAR(36),
age INT(2),
did INT(4) NOT NULL
);

INSERT INTO department(did,dname) VALUES(1,'网络部');
INSERT INTO department(did,dname) VALUES(2,'媒体部');
INSERT INTO department(did,dname) VALUES(3,'研发部');
INSERT INTO department(did,dname) VALUES(5,'人事部');

INSERT INTO employee(id,NAME,age,did)VALUES(1,'王红',20,1);
INSERT INTO employee(id,NAME,age,did)VALUES(2,'李强',22,1);
INSERT INTO employee(id,NAME,age,did)VALUES(3,'赵四',20,2);
INSERT INTO employee(id,NAME,age,did)VALUES(4,'郝娟',20,4);


/*交叉连接
所有的排列组合*/
SELECT * FROM department CROSS JOIN employee;/*共查到4*4=16 条数据*/

/*内连接
内连接使用比较运算符对两个表中的数据进行比较，列出匹配的行组合成新纪录*/

SELECT * FROM department INNER JOIN employee ON department.did=employee.did/*匹配条件*/;

/*外连接 
LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中符合连接条件的记录。
RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中符合连接条件的记录。
*/
SELECT * FROM department LEFT JOIN employee ON department.did=employee.did/*匹配条件*/;/*查询到的数量不小于左表的记录总数*/

SELECT * FROM department RIGHT JOIN employee ON department.did=employee.did/*匹配条件*/;/*查询到的数量不小于右表的记录总数*/

/*复合条件连接查询
复合条件连接查询就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加精确。
*/

SELECT * FROM department INNER JOIN employee ON department.did=employee.did ORDER BY age ASC/*ASC 升序 DESC 降序*/;/*将查询到的信息按照年龄升序排列*/

/*带IN关键字的子查询*/
SELECT * FROM department WHERE did IN (SELECT did FROM employee WHERE age=20);/*查询存在年龄为20岁的员工的部门*/
SELECT * FROM department WHERE did NOT IN (SELECT did FROM employee WHERE age=20);/*查询不存在年龄为20岁的员工的部门*/


/*带EXISTS关键字的子查询
EXISTS关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回TRUE或FALSE，当返回值为TRUE时，外层查询才会执行。
*/

SELECT * FROM department WHERE EXISTS(SELECT did FROM employee WHERE age>21);/*查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录*/


/*带ALL关键字的子查询*/
SELECT * FROM department WHERE did>ALL(SELECT did FROM employee);/*查询大于所有员工部门编号的部门*/


/*带比较运算符的子查询
子查询中可以使用比较运算符，如“>”、“<”、“>=”、“=”、“!=”等。
*/
SELECT * FROM department WHERE did=(SELECT did FROM employee WHERE NAME='赵四' LIMIT 1/*保留一条记录*/);/*查询'赵四'是哪个部门的员工*/