数据查询（下）

一、汇总和分组数据

1.聚合函数

（1）作用：对多条数据做统计功能。（查询语句-->结果集（多条数据）-->聚合函数-->单行记录）

（2）常用的聚合函数

聚合函数	支持的数据类型	描述
sum()	数字	对指定列中的所有非空值求总和
avg()	数字	对指定列中的所有非空值求平均值
min()	数字，字符，日期	返回指定列中的最小数字，最小字符串（字典顺序），最早的日期
max()	数字，字符，日期	返回指定列中的最大数字，最大字符串（字典顺序），最近的日期
count()	任意基于行的数据类型	统计结果集合中全部记录行的数量（统计某一列上是否有数据）

示例1：查询玩家表中一共有多少名玩家信息

    select count(user_id) from users;（这里统计的是这一列中所有非空的值的总和，即这一列中值不为NULL的总和）

    select count(*) from users;（这里统计的是只要这一行对应列的值不为空的总和，即只要这一行有一列的值不为空就统计）

示例2：查询qq号是12301的玩家游戏的总分数

    select sum(score) as '总分数' from scores where user_uid = 1;

示例3：查询QQ是12302的玩家的平均分数

    select avg(score) as '平均分数' from scores where user_uid = 2;

示例4：查询游戏编号是1的游戏的最高分数

    select max(score) as '最高分' from scores where game_uid = 1;

示例5：查询QQ号是12302的玩家的总分、平均分和最高分

    select sum(score) as '总分', avg(score) as '平均分', max(score) as '最高分' from scores where user_uid = 2;

2.在结果集内分组

    查询语句-->结果集（多条数据）-->分组语句（多条单行记录）

示例：查询每个玩家的总分数、平均分和最高分。

    select user_uid, sum(score) as '总分', avg(score) as '平均分', max(score) as '最高分' from scores group by user_uid;

3.筛选分组结果

    在使用group by 子句时，可用having子句为分组统计进一步设置统计条件，having子句与group by 子句的关系相当于where子句与select 子句之间的关系。与where子句的区别是，在having子句中是以聚合函数的统计结果为筛选条件，而where子句是以普通列作为筛选条件。having语句一定要写在GROUP BY 语句之后，不能写在group by语句之前。

示例1：查询平均分数大于85的玩家QQ、总分数、平均分和最高分。

    select user_uid, sum(score) as '总分', avg(score) as '平均分', max(score) as '最高分' from scores group by user_uid having avg(score) > 85;

示例2：查询所有用户的平均分数、总分和最高分，并按平均分倒序排序。

    select user_uid, sum(score) as '总分', avg(score) as '平均分', max(score) as '最高分' from scores group by user_uid order by avg(score) desc;

4.SELECT语句的执行顺序就是select语句的书写顺序：

 ① from子句指定数据源

 ②where子句基于指定的条件对记录进行筛选

 ③group by子句将数据划分为多个分组

 ④使用聚合函数进行计算

 ⑤使用having子句筛选分组

 ⑥使用order by 子句对结果集进行排序

二、连接查询（连接查询分为内连接和外连接两种）

1.FROM子句进行多表查询

（1）多表连接

table1

A	B
a1	b1
a2	b2
a3	b3

table2

X	Y
x1	y1
x2	y2

 

如果执行了select * from table1,table2;会产生这样的结果集：

结果集

A	B	X	Y
a1	b1	x1	y1
a1	b1	x2	y2
a2	b2	x1	y1
a2	b2	x2	y2
a3	b3	x1	y1
a3	b3	x2	y2

示例：查询分数信息，显示玩家昵称、游戏名称和分数；

    select user_name as '玩家昵称', game_name as '游戏名称', score as '分数' from users, games, scores where users.user_id = scores.user_uid and games.game_uid = scores.game_uid;

2.内连接

     内连接的特点：相连接的两张表地位平等，如果一张表中在另一张表中不存在对应的数据，则不做连接，即必须两张表中都有对应的数据才显示。

（1）隐式内连接（FROM子句后面直接出现多个表名，这种连接方式属于内连接，是隐式内连接）

      select user_name as '玩家昵称', score as '分数' from users, scores where users.user_id = scores.user_uid;

（2）显示内连接（这种连接方式比隐式内连接的执行速度更快）

     SELECT COL_LIST FROM Table1 [INNER] JOIN Table2 ON Table1.col = Table2.col;

示例1：查询分数信息，显示玩家昵称，游戏名称和分数

    select user_name as '玩家昵称', game_name as '游戏名称', score as '分数' from users inner join scores on scores.user_uid = users.user_id inner join games on scores.game_uid = games.game_uid;

示例2：查询每个玩家的昵称，总分和平均分

    select user_name as '玩家昵称', sum(score) as '总分', avg(score) as '平均分' from users inner join scores on users.user_id = scores.user_uid group by users.user_id;

   示例3：查询平均分数大于2000的分数信息，显示玩家昵称，总分数、平均分数，并按照平均分数降序排序。

    select user_name as '玩家昵称', sum(score) as '总分', avg(score) as '平均分' from users inner join scores on users.user_id = scores.user_uid group by users.user_id having avg(score) > 2000 order by avg(score) desc;

3.外连接

（1）外连接分为左外连接和右外连接，其特点是：做连接的两个表地位不平等，其中有一张表是基础表。基础表中的每条数据必须出现，即使另一张表中没有数据与之匹配，也要用NULL补齐（也就是说这个数据一定要出现）。左外连接时左表是基础表，右外连接时右表是基础表，语句中先出现的表是“左表”，后出现的表是“右表”。

（2）外连接的格式

     SELECT COL_LIST FROM TABLE1 LEFT|RIGHT [OUTER] JOIN TABLE2 ON TABLE1.COL = TABLE2.COL;

示例：查询所有玩家关于3号游戏的分数信息

    select user_name as '玩家昵称', game_uid as '游戏编号', score as '分数' from users left outer join scores on users.user_id = scores.user_uid and scores.game_uid = 3;

三、子查询

1.使用IN关键字的子查询

    查询游戏类型是“棋牌类”的游戏分数信息

    select * from scores where game_uid in (select game_uid from games where game_type = '棋牌类');

2.使用EXISTS关键字的子查询

    示例：如果存在昵称为“孙大圣”的玩家，则查询分数表中数据

    select * from scores where exists (select * from users where user_name = '孙大圣');// 如果子查询至少能够查询到一条数据的话，那么外围的副查询就会执行，否则外围的副查询就不会执行，结果为空。

四、联合查询

1.使用联合查询

（1）语法格式：SELECT_STATEMENT UNION [ALL] SELECT_STATEMENT UNION [ALL] SELECT_STATEMENT...即用UNION [ALL] 将SELECT语句连接在一起。

（2）作用与特点

    ① 可以把多条查询语句所产生的结果集纵向连接为一体

    ② 有ALL关键字可以显示全部数据（即重复的也显示出来）

    ③ 列的数量与类型都要兼容，如：select user_name, user_sex from users union select game_name from games;

示例1：查询玩家表中所有女性玩家和生日为空的玩家

     select * from users where user_sex = 'FE' union select * from users where user_birthday is null; 还可以这么写

     select * from users where user_sex = 'FE' or user_birthday is null;

示例2：查询QQ号是“12301”的玩家所有分数并计算出总分数和平均分数，并显示到同一结果集中

    select user_uid,game_uid, score from scores where user_uid = '1' union all select '总分', '', sum(score) from scores where user_uid = '1' union all select '平均分', '', avg(score) from scores where user_uid = '1';