MySQL 第三章3.1 数据库查询(select)

MySQL 第三章3.1 数据库查询(select)

    select语句可以从一个或多个表中选取符合某种条件的特定行和列，结果通常是生产一个临时表，下面介绍select语句，select语句是SQL的核心

select
[all | distinct | distinct row]   在多表连接时去掉重复排列
列名
from 表名
where 条件
group by {列名 | 表达式 | position}
having 条件     # 作用同where 只是条件是聚类时使用      
order by  {列名 | 表达式 | position}   

3.1.1 选择输出列

select语句中需要指定查询的列

(1) 选择指定列

select 姓名,专业名,总学分
from xs;

(2) 选择指定列并定义列别名

select 学号 as number,姓名 as name,专业名,总学分 as mark
from xs;

注意：

• 自定义的列别名中有空格时，必须用引号将列别名括号起来

select 学号 as 'student number',姓名 as' student name',专业名,总学分 as mark
from xs;

• 不允许在where子句中使用列别名，因为这个select未完成还没定义出列别名构成的表，where语句只能检测from子句中出现的表的信息。

select 学号 as number,姓名 as name,专业名,总学分 as mark
from xs;
where number = 0 ;   #出现错误

(3) 替换查询结果中的数据
利用case替换表达式，可以使select得到的临时表中的行内容与from原表不同（修改列名称用列别名）

case
when 条件1 then 表达式1
when 条件2 then 表达式2
...
else 表达式n
end as 列别名  #这里的列别名是相当于将from表中包含条件1,,,的列重新定义名称

例如查询xs表中计算机专业各个同学的学号、姓名和总学分，并对总学分按照如下规则进行替换：
若总学分为空值，则替换为‘尚未选课’
若总学分小于50，则替换为‘不及格’
若总学分在50~52之间，则替换为‘合格’
若总学分大于52，则替换为‘优秀’
若总学分列的标题更改为‘等级’。

select 学号,姓名,
case
when 总学分 is null then '尚未选课'
when 总学分 < 50 then  '不及格'
when 总学分 >= 50 and 总学分 <= 52 then  '合格'
else  '优秀'
end as 等级
from xs
where 专业名 = '计算机';

注意：

• case语句要接在select之后 from 之前 且select最后要跟一个逗号(,)
• end as 列别名 和end 的区别 如果不as列别名会显示出所有case 的条件来如下：

1）end as 列别名结尾的情况

 +--------+-----------+-----------+
| 学号   | 姓名      | 等级      |
+--------+-----------+-----------+
| 081101 | 王林      | 合格      |
| 081102 | 程明      | 合格      |
+--------+-----------+-----------+

1）只用end 结尾的情况

+--------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 学号   | 姓名      | case
when 总学分 is null then '尚未选课'
when 总学分 < 50 then  '不及格'
when 总学分 >= 50 and 总学分 <= 52 then  '合格'
else  '优秀'
end                        |
+--------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 081101 | 王林      | 合格                                                                                                                                                             |
| 081102 | 程明      | 合格                                                                                                                                                             
+--------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

(4) 计算列的值
select 表达式
例： 按照120 分重新计算成绩显示xs_kc表中学号为081101的学生成绩信息

select 学号,课程号,成绩 *1.2 as 成绩120
from xs_kc
where 学号 = '081101';

(5) 消除结果集中的重复行 distinct
对表中内容只选择某些列时，输出结果可能会重复比如：

姓名     课程名        成绩
王林     计算机基础     80
王慧     计算机基础     80   

当select 课程名,成绩时这两个表的内容其实是重复的。

select distinct 专业名,总学分
from xs；

(6) 聚合函数

    select 的输出列可以包括聚合函数，聚合函数常常是对一组值进行计算，然后返回单个值。

    常用的聚合函数

函数名           说明
count            求组中项目个数，返回int类型整数
max              求最大值
min              求最小值
sum              返回表达式中所有值的和
avg              求组中值的平均值
std或stddev      返回给定表达式中所有值的标准差
variance         返回给定表达式中所有值的方差
group_concat     返回属于一组的列值连接组合而成的结果
BIT_AND          逻辑或
BIT_OR           逻辑与
BIT_XOR          逻辑异或

1）count() 函数

    count()函数用于统计组中满足条件的行数或总行数，返回select语句检索到的行中非null值的数目，若找不到匹配的行，则返回0。

例如：统计xs表中备注不为空的学生数目。

select count(备注) as '备注不为空的学生数目'
from xs;

注意：这里如果没有as 列别名，则该列名称为count(备注)

例如：统计xs表中总学分在50分以上学生数目。

select count(总学分) as '总学分在50分以上学生数目'
from xs
where 总学分 > 50;

注意：这里count 内填什么信息都可以，因为where已经把不满足的记录进行了删选，count只是对形成的临时表的行数进行一个计算。所以采用临时表中的任何一个特征都可。

2）max()、min() 函数

    max()、min() 函数分别用于求表达式中所有值项的最大值和最小值，格式为

max/min([all | distinct]  表达式)

例如：求选修101课程的学生的最高分和最低分

select max(成绩) as 最高分,min(成绩) as 最低分
from xs_kc
where 课程号 = '101'；

3）sum()、avg() 函数

    sum()、avg() 函数分别用于求表达式中所有值项的总和和平均值，语法表达如下：

sum/avg([all | distinct]  表达式)

例如：求学号081101学生的总成绩

select sum(成绩) as 总成绩
from xs_kc
where 学号 = '081101';

例如：求选修101课程的学生的平均成绩

select avg(成绩)
from xs_kc
where 课程号 = ‘101’;

4）variance()、stddev() 函数

    variance()、stddev() 函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式如下：

variance/stddev([all | distinct]  表达式)

例如：求选修101课程的成绩的方差

select variance(成绩)
from xs_kc
where 课程号 = '101';

例如：求选修101课程的成绩的标准差

select stddev(成绩)
from xs_kc
where 课程号 = '101'

注意： stddev 可简写成std

5）group_concat()函数

    group_concat()函数返回来自一个组制定列的所有非null值，这些值一个接一个放置，中间用逗号隔开，并表示为一个长长的字符串。这个字符串的长度是有限制的，标准值是1024。语法格式为

group_concat({[all | distinct]  表达式}| *)

例如：求选修了206课程的学生的学号

select group_concat(学号)
from xs_kc
where 课程号 = '206';

3.1.4 分组：group by 子句

    将group by子句放到3.1.1select后面将主要是group by 子句通常在聚类函数后使用，聚类函数可以统计不同分组后的数目

    group by 子句的语法格式如下
group by { 列名 | 表达式 | 列顺序 } [ asc | desc],…[with rollup]

注意：

• 该子句可以根据一个或者多个列进行分组，也可以根据表达式进行分组，经常和聚合函数一起使用。group by 子句可以在列的后面指定ASC（升序）或desc（降序）。
• 如果选择‘顺序号’，则分组的列名为顺序号，相同的顺序号归为同一行记录。
• rollup 指定在结果集中不仅包括分开的行，也包括汇总的行，例如选择‘专业名’进行分组，则第一行为‘计算机专业’，第二行为‘通信工程’专业，第三行为‘null’即汇总两种专业都包括那种专业都算。

例如：查询xs表中各专业及其学生数目

 select 专业名,count(*) as 学生数目
 from xs
 group by 专业名;

例如：求被选修的各门课程的平均成绩和选修该课程的人数

select 课程号,avg(成绩) as 平均成绩,count(*) as 选修人数
from xs_kc
group 课程号;

例如:查询每个专业的男生人数，女生人数，总人数，及学生总人数

select 专业名,性别,count(*) as 人数
from xs
group by 专业名,性别
with rollup

注意：with rollup统计总人数

例如：在xscj数据库上产生一个结果集，包括每门课程各专业的平均成绩，每门课的总平均成绩和所有课程的总平均成绩

select 课程名,专业名,avg(成绩) as 平均成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号=xs_kc.课程号
group by 课程名,专业名
with rollup;

3.1.5 分组条件：HAVING 子句

    使用having子句的目的和where子句类似，不同的是where子句是用来在from子句之后选择行，having是用来在group by之后选择行。

having 条件

    其中条件的定义和where子句中的条件类似，不过having子句中的条件可以包含聚合函数，而where子句中却不可以，sql标准要求having子句必须引用group by子句中的列或用于聚合函数中的列。

例如：查找平均成绩在85分以上的学生的学号和平均成绩

select 学号,avg(成绩) as 平均成绩
from xs_kc
group by 学号
having avg(成绩) >= 85;

例如：查找选修课超过2门且成绩都在80分以上的学生的学号

select 学号
from xs_kc
where 成绩 >= 80
group by 学号
having count(*) >= 2;  

例如：查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩

select 学号,avg(成绩) as 平均成绩
from xs_kc
where 学号 in
（select 学号 
from xs
where 专业名 = '通信工程'）
group by 学号
having avg(成绩) > 85;