select查询语句(基础)

(1)Select查询语句

1.1 常用的聚合(多行)函数
所谓单行函数主要指 单条数据传入给该函数后处理的结果还是单条数据；
所谓聚合(多行)函数主要指 多条数据传入给函数后处理的结果是单条数据；

常用的聚合函数有：
sum() - 主要用于实现多个数据累加和的计算，只能计算数值类型的数据；
avg() - 主要用于实现多个数据平均值的计算，只能计算数值类型的数据；
max() - 主要用于实现多个数据最大值的计算，可以计算任意类型的数据；
min() - 主要用于实现多个数据最小值的计算，可以计算任意类型的数据；
count() - 主要用于实现多个数据的计数，可以计算任意类型数据，如：count(*)

1.2 group by子句
select 字段名1 [as 别名1], 字段名2 [as 别名2], … from 表名
[where 条件表达式]
[group by 字段名1, 字段名2]
[order by 字段名/别名/位置编号];

如：
– 查询学生表中每个年级的总人数，显示年级编号和总人数
select id, count() from student
group by id
order by count();

注意：
select后面允许出现的字段只能是group by后面跟的字段以及聚合函数。

1.3 having子句
select 字段名1 [as 别名1], 字段名2 [as 别名2], … from 表名
[where 条件表达式]
[group by 字段名1, 字段名2]
[having 条件表达式]
[order by 字段名/别名/位置编号];

如：
– 查询学生表中每个年级的总人数，并将总人数超过18人的年级编号显示出来
select id, count() from student
where count() > 18
group by id; – error: where子句中不能使用聚合函数

select id, count() from student
group by id
having count() > 18 – having子句可以对分组的结果再次筛选
order by count(*);

Select查询语句的执行流程：
from 表名 => where子句 => group by分组 => having子句 => select取出数据
=> order by子句

1.4 子查询(重点、难点)
所谓子查询主要指 让一次查询的结果作为条件再次进行查询的过程

练习：
– 使用子查询在员工表中查询与’埃琳娜’在同一个部门的员工信息
– 首先查询员工表中’埃琳娜’所在的部门编号
select dept_id from s_emp
where first_name = ‘埃琳娜’; – 查询结果：41

– 其次查询员工表中与’埃琳娜’所在部门编号相等的员工信息
select * from s_emp
where dept_id = 41;

– 合并为子查询语句
select * from s_emp
where dept_id = (select dept_id from s_emp
where first_name = ‘埃琳娜’);

案例讲解：
– 查询’JavaSE’课程并且考了100分的学生信息
select * from student
where studentno = ‘S1101019’;

-- 从课程表中查询'JavaSE'课程对应的课程编号
select subjectid from subject
where subjectname = 'JavaSE';                 -- 查询结果： 1

-- 从成绩表中查询'JavaSE'课程并且考了100分的学生编号
select studentno from result 
where subjectid = 1 and studentresult = 100;  -- 查询结果：S1101019

-- 合并上述代码
select * from student
where studentno = (select studentno from result 
	where subjectid = (select subjectid from subject
		where subjectname = 'JavaSE' ) and studentresult = 100 );

练习：
– 查询“青铜”阶段开设的课程
– 该需求涉及到 grade阶段表 和 subject课程表，表之间通过 gradeid 字段关联
– 先由阶段名称可以查询到阶段编号，再由阶段的编号得到课程名称
select subjectname from subject
where gradeid = (select gradeid from grade
where gradename = ‘青铜’);

-- 查询参加最近一次“HTML和CSS网页技术”考试成绩的最高分和最低分
-- 该需求涉及到 subject课程表 和 result成绩表，表之间通过subjectid字段关联
-- 先由课程名称可以查询到课程编号，再由课程编号可以查询到考试成绩和
-- 最近一次考试的时间，再根据考试成绩得到最高分和最低分
select max(studentresult) 最高分, min(studentresult) from result
	where subjectid = ( select subjectid from subject
		where subjectname = 'HTML和CSS网页技术')
	and examdate = ( select max(examdate) from result
		where subjectid = ( select subjectid from subject
			where subjectname = 'HTML和CSS网页技术')); 

案例讲解：
– 查询’JavaSE’课程考试成绩不及格的学生名称
– 该需求涉及到 subject课程表 和 result成绩表 以及 student学生表
– 其中subject表和result表之间采用 subjectid 字段关联
– 其中result表和student表之间采用 studentno 字段关联
– 先由课程名称可以查询到课程编号，再由课程编号以及考试成绩可以查询到学号
– 再根据学生的学号可以得到学生的名字
select studentname from student
where studentno = ( select studentno from result
where studentresult < 60 and subjectid =(select subjectid from subject
where subjectname = ‘JavaSE’ )); – error: 有多个人不及格

-- 当子查询的结果有多个数据内容时，则将 = 换成in即可
select studentname from student 
where studentno in ( select studentno from result
	where studentresult < 60 and subjectid =(select subjectid from subject 
		where subjectname  = 'JavaSE' ));

-- 当子查询的结果有多个数据内容时，则在 = 的后面加上any关键字即可
select studentname from student 
where studentno = any ( select studentno from result
	where studentresult < 60 and subjectid =(select subjectid from subject 
		where subjectname  = 'JavaSE' )); 

练习：
– 查询参加“JavaSE”课程最近一次考试的在读学生名单
– 该需求涉及到 subject课程表 和 result成绩表 以及 student学生表
– 其中subject表和result表之间采用 subjectid 字段关联
– 其中result表 和 student表之间采用 studentno 字段关联
– 先由课程名称得到课程编号，再由课程编号和最近一次考试时间得到学生编号
– 最后由学生编号得到学生的名字

   select studentname from student
   where studentno = ( select studentno from result
   	where examdate = ( select max(examdate) from result 
   		where subjectid = ( select subjectid from subject 
   			where subjectname = 'JavaSE'))
   	and subjectid = ( select subjectid from subject 
   		where subjectname = 'JavaSE'));

作业：
– 查询和Ben一个部门的员工的id，first_name,dept_id,salary信息

   select id,first_name,dept_id,salary from s_emp
   where dept_id = (select dept_id from s_emp
   where first_name ='Ben');

– 显示所有工资超过Ben的员工的信息，包括：id,first_name,salary

select id,first_name,salary from s_emp
where salary >(select salary from s_emp
where first_name = 'Ben');

– 显示工资高于全公司平均工资的员工信息，包括id,first_name,salary

select id, first_name,salary from s_emp
where salary >(select avg(salary)from s_emp);

– 显示工资高于部门id=43的平均工资 的员工信息，包括id,first_name,salary

select id,first_name,salary from s_emp
where salary >(select avg(salary) from s_emp
where dept_id =43 );