MySql--复杂查询

Myth丶恋晨 2022-06-18 07:37 274阅读 0赞

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)

**/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/**