MySql--复杂查询

MySql拥有着非常丰富的查询方式，除了之前总结的简单查询和子查询外，还提供复杂查询的方式，可以进行分组查询、多表查询和合并查询结果，现将之总结一下。

/*****************************分组查询**************************************/

create table studentInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subject VARCHAR(20), teacher VARCHAR(20));

1、对单列进行分组查询

select subject, COUNT(*) from studentInfo GROUP BY subject;

2、使用HAVING的分组查询

select subject, AVG(score) from studentInfo GROUP BY subject HAVING subject = '数学';

3、对多列进行分组查询

select subject, teacher, AVG(score) from studentInfo GROUP BY subject, teacher;

4、在分组查询中使用ORDER BY

select teacher, SUM(score) from studentInfo GROUP BY teacher ORDER BY sum(score) desc;

/*****************************************************************************/

/*****************************多表查询**************************************/

create table newInfo(id int PRIMARY KEY, name VARCHAR(20), score decimal(4,2), subjectid int, teacherid int);
create table subjectInfo(id int PRIMARY KEY, subjectname VARCHAR(30));
create table teacherInfo(id int PRIMARY KEY, teachername VARCHAR(30));

1、等值连接

mysql> select newInfo.name,subjectInfo.subjectname from newInfo,subjectInfo where newInfo.subjectid=subjectInfo.id;
+--------+-------------+
| name   | subjectname |
+--------+-------------+
| 小明   | 数学        |
| 小红   | 英语        |
| 小李   | 语文        |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername from newInfo,subjectInfo,teacherInfo 
     > where newInfo.subjectid = subjectInfo.id and newInfo.teacherid = teacherInfo.id;
+--------+-------------+-------------+
| name   | subjectname | teachername |
+--------+-------------+-------------+
| 小明   | 数学        | 张老师      |
| 小红   | 英语        | 王老师      |
| 小李   | 语文        | 吴老师      |
+--------+-------------+-------------+
3 rows in set (0.00 sec)

2、笛卡尔积   3*4列和5*7列表不加条件查询，得到8*28列表

select * from newInfo,subjectInfo,teacherInfo;

查询所得结果非常庞大，并且非常臃肿，对实际的应用没有什么太大的意义，在实际应用中要加指定查询条件，避免笛卡尔积的出现

3、外连接

  (1)左外连接

mysql> select newInfo.name,subjectInfo.subjectname from newInfo LEFT OUTER JOIN subjectInfo 
     > ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name   | subjectname |
+--------+-------------+
| 小明   | 数学        |
| 小红   | 英语        |
| 小李   | 语文        |
| 小胡   | NULL        |
+--------+-------------+
4 rows in set (0.00 sec)

  (2)右外连接

mysql> select newInfo.name,subjectInfo.subjectname from newInfo RIGHT OUTER JOIN subjectInfo 
     > ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name   | subjectname |
+--------+-------------+
| 小李   | 语文        |
| 小明   | 数学        |
| 小红   | 英语        |
| NULL   | 计算机      |
+--------+-------------+
4 rows in set (0.00 sec)

4、内连接 相当于等值连接  相比较更加明显是多表查询

mysql> select newInfo.name,subjectInfo.subjectname from newInfo INNER JOIN subjectInfo 
     > ON newInfo.subjectid = subjectInfo.id;
+--------+-------------+
| name   | subjectname |
+--------+-------------+
| 小李   | 语文        |
| 小明   | 数学        |
| 小红   | 英语        |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select newInfo.name,subjectInfo.subjectname,teacherInfo.teachername 
    -> from newInfo INNER JOIN subjectInfo INNER JOIN teacherInfo 
    -> ON newInfo.subjectid=subjectInfo.id and newInfo.teacherid=teacherInfo.id;
	+--------+-------------+-------------+
	| name   | subjectname | teachername |
	+--------+-------------+-------------+
	| 小明   | 数学        | 张老师      |
	| 小红   | 英语        | 王老师      |
	| 小李   | 语文        | 吴老师      |
	+--------+-------------+-------------+
	3 rows in set (0.00 sec)

/*****************************************************************************/

/*****************************合并查询结果**************************************/

1、使用UNION关键字合并查询结果（多表查询的数据表） 两张表的列数相同，数据类型相同

mysql> select * from teacherInfo
		-> UNION
		-> select * from subjectInfo;
	+----+-------------+
	| id | teachername |
	+----+-------------+
	|  1 | 张老师      |
	|  2 | 王老师      |
	|  3 | 吴老师      |
	|  1 | 语文        |
	|  2 | 数学        |
	|  3 | 英语        |
	|  4 | 计算机      |
	+----+-------------+
	7 rows in set (0.00 sec)

2、对合并后的查询结果排序

mysql> select * from teacherInfo
		-> UNION
		-> select * from subjectInfo 
		-> ORDER BY id;
	+----+-------------+
	| id | teachername |
	+----+-------------+
	|  1 | 张老师      |
	|  1 | 语文        |
	|  2 | 数学        |
	|  2 | 王老师      |
	|  3 | 英语        |
	|  3 | 吴老师      |
	|  4 | 计算机      |
	+----+-------------+
	7 rows in set (0.00 sec)

3、限制组合查询结果的行数

mysql> select * from teacherInfo
		-> UNION
		-> select * from subjectInfo 
		-> LIMIT 3;
	+----+-------------+
	| id | teachername |
	+----+-------------+
	|  1 | 张老师      |
	|  2 | 王老师      |
	|  3 | 吴老师      |
	+----+-------------+
	3 rows in set (0.00 sec)

/*****************************************************************************/