MySQL T6 数据库操作——查询（三）高级查询

复习
算术运算符：+ - * / % 【针对数值型，select子句，where子句】
查询大于18岁的学生，学生表内存的是生日 birthday
where year(sysdate()) - year(birthday) >=18
关系运算符：= > < <= >= <> between ... and
【常用于where子句】
逻辑运算符：and且 or或 not非
where 条件1 and 条件2 or 条件3 or not 条件4
Where子句谓词：
模糊查询：like '%|_' not like
是否为空：is null is not null
包含：in(值1,值2,...,值n) not in
排序：order by 【结果集进行排序】
格式： order by 列名1,...,列名n

select |列的列表 => |列名1,列名2,...,列名n
from 表的列表
[where子句]
[order by 列的列表]

一、SQL的集合函数
SQL提供的统计函数，就叫集合函数（也叫：聚合函数，组合函数）
count(*|列名) 计数，统计记录的行数
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 求平均值

1、数行数 count()（看 结果集 有多少行）

select count(*)
from tb_score;

select count(*)
from tb_score
where sclassname = 'MySQL';

等价于以下步骤=>
1、select *
from tb_score
where sclassname = 'MySQL';
2、count(结果集)
--先查出结果集，再去统计结果，统计函数只有一行结果

二、分组 group by

group by having 分组之后，再刷选显示结果
6、求每个学生的总分和平均分，要求考试不少于2科
（如果一个学生只考一科，总分和平均分没有参考价值）

分组之后，再刷选显示结果
select sno, count(sno) '科目数',
SUM(sscore) '总分', AVG(sscore) '平均分'
from tb_score
group by sno
having count(sno) > 1
order by AVG(sscore);

SQL语句语法规则：
select * | 列的列表
from 表名
[where 子句]
[group by 列的列表
[having 条件] ]
[order by 列的列表]

三、多表查询：连接查询

select tb_student.sname, tb_student.sage,tb_score.sclassname, tb_score.sscore
from tb_student, tb_score
where tb_student.sno = tb_score.sno

=> 精简代码，给表取别名

select a.sname, a.sage,b.sclassname, b.sscore
from tb_student a, tb_score b
where a.sno = b.sno

三表等值连接：
查询学生的成绩表，要求显示学生学号，姓名，性别，课程名和成绩
学号，姓名，性别 来源于 tb_student
课程名 —— tb_class
成绩 ——— tb_score

select a.sno, a.sname,a.ssex,c.cname,b.sscore
from tb_student a, tb_score b, tb_class c
where a.sno = b.sno
and b.cno = c.cno;

四、子查询：
在where子句中，值使用一个查询语句替代

请查询考试成绩大于70分的学生，显示学生编号，姓名和专业
分析：
1、考试成绩大于70分有哪些学生，数据来源于成绩表
可以查出符合条件的学生编号
2、根据第一步的结果，再查询学生表的信息

1、查询成绩大于70分的学号
select distinct sno
from tb_score
where sscore > 70
2、根据学号列表，找姓名和专业
--子查询实现
select sno, sname, sdept
from tb_student
where sno IN(
select distinct sno
from tb_score
where sscore > 70
)

也可以用连接查询实现：
select distinct a.sno, a.sname, a.sdept
from tb_student a, tb_score b
where a.sno = b.sno
and b.sscore > 70;
T5数据库—高级查询.rar

上机作业讲解（第二题）：
/员工信息表1/
create table tb_stafff(
id int not null,
name varchar(10) not null,
deptid int,
salary decimal(8,2),
edlevel tinyint,
hiredate DATE,
primary key(id)
);
select * from tb_stafff
insert into tb_stafff
VALUES(1,'张三',1,2000,3,'2009-10-11'),
(2,'李四',1,2500,3,'2009-10-01'),
(3,'王五',2,2600,5,'2010-10-02'),
(4,'王六',2,2300,4,'2010-10-03'),
(5,'马七',2,2100,4,'2010-10-06'),
(6,'赵八',3,3000,5,'2010-10-05'),
(7,'钱九',3,3100,7,'2010-10-07'),
(8,'孙十',3,3500,7,'2010-10-06');

/*部门信息表2*/

create table tb_dept(
deptid int not null,
name varchar(10) not null,
manager int,
remark VARCHAR(20),
primary key(deptid)
);

select * from tb_dept
insert into tb_dept
values(1,'开发部',1,null),
(2,'设计部',3,'老油条'),
(3,'销售部',8,'最牛');

1、查出所有的员工信息，显示：编号，姓名，部门名称及薪水
select a.id,a.name,b.name,a.salary
from tb_stafff a, tb_dept b
where a.deptid = b.deptid;

2、查出薪水在2500和3000之间的员工，显示姓名，部门名称及职级
select a.name,b.name,a.edlevel
from tb_stafff a, tb_dept b
where a.deptid = b.deptid
and a.salary between 2500 and 3000;

3、请用子查询，查出销售部的，平均工资，最高工资和最低工资
select avg(salary), max(salary), MIN(salary)
from tb_stafff
where deptid in(
select deptid
from tb_dept
where name = '销售部')
;

/ 部门名称来自于 部门表
不能够用 ， 因为员工表中只存 部门id
*/
select deptid
from tb_dept
where name = '销售部'