表的增删改查（三）（MySQL）

查询

1、聚合查询

（1）聚合函数

a.常见的聚合函数：

b.示例
先创建一张老师表，并插入数据

-- 创建一张teacher表
create table teacher(
    id int,
    name varchar(20),
    role varchar(20),
    salary decimal(9,2)
);
-- 插入数据
insert into teacher values(1,'马睿','讲师',2994.7);
insert into teacher values(2,'刘明','教授',8948.8);
insert into teacher values(3,'王玥','副教授',3694.5);
insert into teacher values(4,'赵辉','教授',9394.4);
insert into teacher values(5,'孙露','讲师',3894.8);
insert into teacher values(6,'周良','讲师',4894.6);
insert into teacher values(7,'吴牧','教授',8894.6);
insert into teacher values(8,'钱落','副教授',6794.2);
insert into teacher values(9,'李平','副教授',7764.7);

select 聚合函数() from 表名 where 条件；

查询示例：

-- 查询所有老师姓名的数量
select count(name) from teacher;
-- 查询所有职位为教授的工资总和
select sum(salary) from teacher where role = '教授';
-- 查询所有职位为副教授的工资的平均值
select avg(salary) from teacher where role = '副教授';
-- 查询所有老师的最低工资
select min(salary) from teacher;
-- 查询所有老师的最高工资
select max(salary) from teacher;

（2）group by子句

select 列名 from 表名 group by 列名；

select 中使用group by子句可以对指定列进行分组查询。需要满足：
使用group by进行分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在select 中则必须包含在聚合函数中

-- 根据职位，查询职位，及相应的最低工资，最高工资，和平均工资
select role,min(salary),max(salary),avg(salary) from teacher group by role;

（3）having

select 列名 from 表名 group by 列名 having 条件；

group by子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要用having

-- 使用group by子句进行分组以后，筛选条件时要用having
select role,min(salary),max(salary),avg(salary) from teacher 
group by role having avg(salary) >6000;

2、联合查询

多表查询是对多张表的内容取笛卡尔积，即

注意：

关联查询可以对关联表使用别名

创建几个表插入数据以供查询

-- 创建一张班级表，其中包括班级id和班级名
create table classes (
    id int primary key auto_increment,
    name varchar(20)
    );
-- 创建一张学生表，其中包括学生id，学号，姓名，班级id
create table student (
    id int primary key auto_increment,
    sn varchar(20),  
    name varchar(20),
    classes_id int
    );
-- 创建一张课程表，其中包括课程id和课程名
create table course(
    id int primary key auto_increment,
    name varchar(20)
    );
-- 创建一张分数表，其中包括分数，学生id，课程id
create table score(
    score decimal(3, 1), 
    student_id int, 
    course_id int
    );
-- 插入班级数据
insert into classes(name) values 
('一班'),('二班'),('三班');
-- 插入学生信息数据
insert into student(sn, name,classes_id) values
('00011','秦诺',1),
('53944','吴婼',3),
('00192','赵世杰',1),
('00154','马波',2),
('00021','冯琛',1),
('00009','陆廷',2),
('00029','游玖',2),
('0046','郑雪',3);
-- 插入课程数据
insert into course(name) values
('Java'),('数学'),('化学'),('语文'),('英语'),('物理');
-- 插入分数数据
insert into score(score, student_id, course_id) values
-- 秦诺
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 吴婼
(60, 2, 1),(59.5, 2, 5),
-- 赵世杰
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 马波
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 冯琛
(81, 5, 1),(37, 5, 5),
-- 陆廷
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 游玖
(80, 7, 2),(92, 7, 6),
--郑雪
(99,8,1),(80,8,4),(90,8,6);

（1）内连接

语法：

select 列名 from 表1 别名，表2 别名 where 条件；
select 列名 from 表1 别名 jion 表2 别名 on 条件；

示例：

-- 查询秦诺和吴婼的成绩
select score.course_id,score.score
from student,score
where student.name = '秦诺' and student.id = score.student_id;
select score.course_id,score.score
from student join score
on student.name = '秦诺'and student.id = score.student_id;

（2）外连接

外连接分为左外连接和右外连接。
如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

（3）自连接

指在同一张表上连接自身进行查询

-- 查询所有java成绩比英语成绩高的信息
select s1.* from score s1,score s2
where s1.student_id = s2.student_id
and s1.score > s2.score
and s1.course_id = 1
and s2.course_id = 5;


select s1.* from score s1 join score s2
on s1.student_id = s2.student_id
and s1.score > s2.score
and s1.course_id = 1
and s2.course_id = 5;

（4）子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
a.单行子查询：返回一行记录的子查询
b.多行子查询：返回多行记录的子查询
<1>关键字 [not]in ,

-- 查询语文和物理的成绩
-- 使用in
select * from score where course_id in (
    select id from course where name='语文' or name='物理'
);
-- 使用 not  in
select * from score where course_id not in (
    select id from course where name!='语文' and name!='物理'
);

<2>关键字[not]exist;

-- 查询语文和java的成绩
-- 使用exist
select * from score sco where exists (
    select sco.course_id from course cou where (name='语文' or name='java') 
    and cou.id = sco.course_id
);
-- 使用 not exist
select * from score sco where not exists (
    select sco.course_id from course cou where (name!='语文' and name!='java')
    and cou.id = sco.course_id
);

c.在from子句中使用子查询：子查询语句出现在from子句中，把一个子查询当做一个临时表使用

-- 查询比英语平均分数高的成绩
select * from score sco,(
    select avg(score) scor from score s,course 
    where course.name = '英语' and course.id = s.course_id
)temp
where sco.score>temp.scor;

（5）合并查询

为了合并多个select的执行结果，可以使用集合操作符 union，union all。
使用union和union all时，前后查询的结果集中，字段需要一致

union (该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行 )
union all(该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行)

示例：

-- 查询id>3 和名称为物理的课程
-- 使用union会去除重复数据
select * from course where id>3
union 
select * from course where name ='英语';
-- 使用or有相同的效果
select * from course where id>3 or name ='英语';
-- 使用union all不会去除重复数据
select * from course where id>3
union all
select * from course where name ='英语';