数据库，DQL03

DQL

1、子查询：嵌套在查询中间的查询，叫做子查询

子查询可以作用的地方：select 语句，from语句，where语句，group by语句，order by语句，having语句之上

作用在select语句：

根据两张表，完成关联数据的查询：
   SELECT
	(
		SELECT
			stu_name
		FROM
			t_student
		WHERE
			t_student.id = t_score.fk_student_id
	) AS stu_name,
	(
		SELECT
			class_name
		FROM
			t_student
		WHERE
			t_student.id = t_score.fk_student_id
	) AS class_name,
	english,
	math,
	chinese
FROM
	t_score;

特点：放置在select语句中的子查询，只能返回单行以及单列数据

作用在where语句：

根据两张表，筛选获得数据：
   SELECT
	(
		SELECT
			stu_name
		FROM
			t_student
		WHERE
			t_student.id = t_score.fk_student_id
	) AS stu_name,
	(
		SELECT
			class_name
		FROM
			t_student
		WHERE
			t_student.id = t_score.fk_student_id
	) AS class_name,
	english,
	math,
	chinese
FROM
	t_score
WHERE
	fk_student_id IN (
		SELECT
			id
		FROM
			t_student
		WHERE
			class_name = 'T40'
	);

//查询英语成绩 > 平均成绩的学生信息
SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND score.english >= (
	SELECT
		SUM(english) / COUNT(*)
	FROM
		t_score
);


特点：放置在where语句之后，主要用于筛选数据

作用在from语句：

例子1：

只查询T40班的所有学生的考试成绩：
SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T40'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id;

例子2：

Oracle数据中分页查询10条数据：

  SELECT
	*
FROM
	(
		SELECT
			ROW_.*, ROWNUM ROWNUM_
		FROM
			(
				SELECT
					*
				FROM
					TABLE1
				WHERE
					TABLE1_ID = XX
				ORDER BY
					GMT_CREATE DESC
			) ROW_
		WHERE
			ROWNUM <= 20
	)
WHERE
	ROWNUM_ >= 10;


SELECT * FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC    //根据条件查询，满足条件的数据

SELECT ROW_.*, ROWNUM ROWNUM_ FROM （SELECT * FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC） ROW_ WHERE ROWNUM <= 20;   //从上述数据中，获得数据库投影表中编号在20以下的数据


//从上述的20条数据中，取出后十条数据
SELECT * FROM （SELECT ROW_.*, ROWNUM ROWNUM_ FROM （SELECT * FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC） ROW_ WHERE ROWNUM <= 20）WHERE ROWNUM_ >= 10;


特点：放置在from语句中的子查询，大家可以把它想象成一张表的数据，可以多行多列

ALL关键字

用于：将一个表的数据和另外一个满足条件的所有的数据进行比较

和子查询的结果逐一比较，必须全部满足时表达式的值才为真。
SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T41'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND score.english >= ALL (
	SELECT
		IFNULL(english, 0)
	FROM
		t_score
	WHERE
		fk_student_id IN (
			SELECT
				id
			FROM
				t_student
			WHERE
				class_name = 'T40'
		)
);


//显示T41班上英语成绩，大于所有T40班英语成绩的学生信息


IFNULL(english,0)    NULL参与任何比较运算，结果都是false,所以如果NULL有空参与的地方，可以通过该函数设置一个默认值

ANY关键字

用于：将一个表的数据和另外一个满足条件的所有的数据进行比较

和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。
SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T41'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND score.english >= ANY (
	SELECT
		IFNULL(english, 0)
	FROM
		t_score
	WHERE
		fk_student_id IN (
			SELECT
				id
			FROM
				t_student
			WHERE
				class_name = 'T40'
		)
);

//显示T41班上英语成绩，大于任意一个T40班英语成绩的学生信息

EXISTS（存不存在）

查询T41班有没有和T40班上，英语成绩一样的学生
   SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T41'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND score.english IN (
	SELECT
		english
	FROM
		t_score
	WHERE
		fk_student_id IN (
			SELECT
				id
			FROM
				t_student
			WHERE
				class_name = 'T40'
		)
)
推荐大家：用EXISTS 去替代in        用NOT EXISTS 去替代not in    
原因是：EXISTS 关注的数据存不存在，而不是依次去比对，性能更高
SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T41'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND EXISTS (
	SELECT
		english
	FROM
		t_score
	WHERE
		score.english = english
	AND fk_student_id IN (
		SELECT
			id
		FROM
			t_student
		WHERE
			class_name = 'T40'
	)
)    

NOT EXISTS

SELECT
	stu.id,
	stu.stu_name,
	stu.class_name,
	score.english,
	score.chinese,
	score.math
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T41'
	) AS stu,
	t_score AS score
WHERE
	score.fk_student_id = stu.id
AND NOT EXISTS (
	SELECT
		english
	FROM
		t_score
	WHERE
		score.english = english
	AND fk_student_id IN (
		SELECT
			id
		FROM
			t_student
		WHERE
			class_name = 'T40'
	)
)

子查询分为：相关子查询，非相关子查询

相关子查询：子查询在查找时，需要使用外部查询的数据

非相关子查询：子查询在查找时，不需要使用外部查询的数据

相关子查询，外部查询查询一次，子查询就需要执行一次
而非相关子查询，子查询总共执行一次，执行完毕后后将值传递给外部查询


优化策略：在做子查询时，能不用in就不要用in，尽量改用exists来做查询


子查询的示例：
	查询学生考试数据，在平均分以上的这种

UNION（将多个表的数据进行连接起来）

例如：将成都市人口表中的数据  和  绵阳市人口表中的数据  都查询出来    UNION
SELECT
	*
FROM
	(
		SELECT
			id,
			stu_name,
			class_name
		FROM
			t_student
		WHERE
			class_name = 'T40'
		UNION
			SELECT
				id,
				stu_name,
				class_name
			FROM
				t_student
			WHERE
				class_name = 'T41'
	) AS stu
ORDER BY
	id ASC;    	



select id,english from t_score union select id,stu_name from t_student;

2.联接

表联接：将多张表联接到一起，来完成数据的查询 相比于Union的纵向联接，它就是一个横向的联接
表联接分为：内连接，外联接，自联接

内连接：查询的是多张表中有相互关系的数据，语法结构： 表1 inner join 表2 on 表1.外键 = 表2.主键
例如：

SELECT
	s.id,
	s.stu_name,
	s.class_name,
	sc.english,
	sc.math,
	sc.chinese
FROM
	t_score AS sc
INNER JOIN t_student AS s ON sc.fk_student_id = s.id;

inner可以省略
例如：

SELECT
	s.id,
	s.stu_name,
	s.class_name,
	sc.english,
	sc.math,
	sc.chinese
FROM
	t_score AS sc
JOIN t_student AS s ON sc.fk_student_id = s.id;

内连接另外一种用法： 表1,表2 where 表1.外键 = 表2.主键
例如：

SELECT
	*
FROM
	t_score,
	t_student
WHERE
	t_score.fk_student_id = t_student.id;

外联接

left outer join
right outer join              关键字 outer 可以省略


左外联：以左表为基础(左表满足条件的数据，都会被查询出来)，查询相关数据，右表有数据就显示数据，没有数据就显示为NULL
SELECT
	s.id,
	s.stu_name,
	s.class_name,
	sc.english,
	sc.math,
	sc.chinese
FROM
	t_score AS sc
LEFT JOIN t_student AS s ON sc.fk_student_id = s.id;

右外联：以右表为基础(右表满足条件的数据，都会被查询出来)，查询相关数据，左表有数据就显示数据，没有数据就显示为NULL
SELECT
	s.id,
	s.stu_name,
	s.class_name,
	sc.english,
	sc.math,
	sc.chinese
FROM
	t_score AS sc
RIGHT JOIN t_student AS s ON sc.fk_student_id = s.id;

自关联：在本表中创建一个属于自己的外键，用来描述本表中数据与数据之间的关系。例如：部门，权限，地区……