六、连接查询和子查询

6.1 链接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中 主要的查询，主要包括内连接、外连接等。通过连接运算符可以实现多个表查询。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在一个表中。当查询数据时，通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询。本节将介绍多表之间的内连接查询、外连接查询以及复合条件连接查询.

1.内连接查询

内连接（INNER JOIN）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

SELECT * FROM dept d INNER JOIN emp e ON d.`deptid`=e.`deptid`;

2.外连接查询

左外连接

SELECT * FROM dept LEFT JOIN emp ON dept.`deptid`=emp.`deptid`

右外连接

 SELECT dept.*,emp.* FROM emp RIGHT JOIN dept ON dept.`deptid`=emp.`deptid`

6.2 子查询

1)子查询

-- 查询员工信息，并展示员工所在的部门名字
SELECT emp.*, (SELECT deptname FROM dept WHERE
dept.`deptid`=emp.`deptid`)deptname
FROM emp WHERE emp.`deptid` IN (SELECT deptid FROM dept)

-- 查询用户锁拥有的角色
SELECT ur.*,
	(SELECT users.`uname` FROM users WHERE users.`uid`=ur.`uid`) uname,
	(SELECT roles.`rname` FROM roles WHERE roles.`rid`=ur.`rid`) rname
	FROM userroles ur

-- 查询成绩最高的学生信息
SELECT * FROM xueshenbiao WHERE xuehao IN
	(
		SELECT xuehao FROM 成绩表 WHERE 成绩
		=(SELECT MAX(chengji) FROM 成绩表)
	);

-- 查询考试没有及格的学生信息
SELECT * FROM xueshengbiao WHERE xuehao IN (
SELECT DISTINCT xuehao FROM 成绩表 WHERE chengji<60
);

-- 查询两门课以上不及格的学生信息
SELECT * FROM xuesb WHERE xuhao IN(SELECT xh FROM chengjibao WHERE cj<60
GROUP BY xh HAVING COUNT(1)>1) ;

-- 查询平均分低于总平均的记录
SELECT studentno,AVG(studentresult)
FROM result GROUP BY studentno HAVING
AVG(studentresult)<
(SELECT AVG(studentresult) FROM result);

-- 查询所有科目中成绩最低的学生信息
SELECT * FROM student WHERE studentno IN
(SELECT studentno FROM result WHERE studentresult =
(SELECT MIN(studentresult) FROM result) );

-- 查询成绩高于所有科目平均分的学生信息
SELECT * FROM student WHERE studentno IN (
SELECT studentno FROM result a,
	(SELECT AVG(studentresult) avgscore ,subjectid FROM result GROUP BY
subjectid)b
	WHERE a.`subjectid`=b.subjectid AND a.`studentresult`>b.avgscore);

-- 查询成绩比张三的语文成绩还低的学生信息
SELECT * FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE studentresult <(
SELECT studentresult FROM result WHERE studentno=
(SELECT studentno FROM student WHERE studentname='张三')
AND subjectid = (SELECT subjectid FROM subjects WHERE subjectname='语文');
)
)

2)any,some

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

CREATE table tbl1 ( num1 INT NOT NULL); CREATE table tbl2 ( num2 INT NOT
NULL);

有两个表tb1与tb2,数据如下

SELECT num1 FROM tb1 WHERE num1> ANY(SELECT num2 FROM tb2);
-- 或
SELECT num1 FROM tb1 WHERE num1> SOME(SELECT num2 FROM tb2);

ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回 TRUE。 将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。

3)All

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。

SELECT num1 FROM tb1 WHERE num1> ALL(SELECT num2 FROM tb2);

-- 找到工资比人事部工资高的信息
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE emp.deptid=
(SELECT deptid FROM dept WHERE deptname='人事部')) ;

4)EXISTS

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返 回的结果是false，此时外层语句将不进行查询。

SELECT * FROM tb1 WHERE EXISTS
(SELECT * FROM tb2 WHERE num2>10) AND num1>10;

如果 SELECT * FROM tb2 WHERE num2>10 有返回结果，则执行外部查询，此时会查询tb1表，并且要过来num1的值大于10的。如果将num2大于100的没有结果，则外部查询不执行。

NOT EXISTS 与 EXISTS相反

5)UNION

创建tb1与tb2，并插入数据

SELECT * FROM tb1
UNION ALL
SELECT * FROM tb2;

SELECT * FROM tb1
UNION
SELECT * FROM tb2;

6)正则查询

正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据 指定的匹配模式匹配文本中符合要求的特殊字符串。例如，从一个文本文件 中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

-- 以字母a开头
SELECT * FROM employees WHERE first_name REGEXP '^a';
-- $结尾
SELECT * FROM employees WHERE first_name REGEXP 'a$';
-- b中间是任意字符c b与c之间得有一个任意字符，
SELECT * FROM employees WHERE first_name REGEXP 'b.c';
-- +b必须得出现到n次
SELECT * FROM employees WHERE first_name REGEXP 'b+c';
-- * 必须得0到n次
SELECT * FROM employees WHERE first_name REGEXP 'b*c';
SELECT * FROM employees WHERE first_name REGEXP 'b{2,}c';
-- {2,5} 2到5次
SELECT * FROM employees WHERE first_name REGEXP 'b{2,4}c';