数据库的增删改查(三)

1、查询

1.1、聚合查询

1.1.1、聚合函数

数据库提供了很多函数，其中就包括聚合函数，常见的聚合函数如下表

函数	说明
COUNT([IDDINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([IDSTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

示例：

*COUNT

-- 统计班级总共有多少同学
 select count(*) from student01;

 select count(0) from student01;

-- 统计班级收集的qq_mail 有多少个，qq_mail 为null不会计入结果

select count(qq_mail) from student01;

 

 

 

 *SUM

-- 统计数学成绩总分

select SUM(math) from exam_result;

-- 不及格<60 的总分，没有结果，返回null

select SUM(math) from exam_result where math<60;

 

 

 *AVG

-- 全班平均分

select avg(chinese+math+english) as 平均分 from exam_result;

 

 *MAX

-- 返回数学最高分

select max(math) from exam_result;

 

 *MIN

-- 返回英语70分以上的最低分

select min(english) from exam_result where english>70;

 

 1.1.2、GROUP BY子句

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

大致意思就是要对那个字段分组，其次没有分组的字段，以那种方式进行呈现，比如聚合函数中的一些操作，求和，平均

语法：

select column1,sum(column2),... from table group by column1,column3;

示例：

准备测试表及数据：职员表，有id(主键)、name(姓名)、role(角色)、salary(薪水)

 create table emp(

            id int primary key auto_increment,

            name varchar(20) not null,

            role varchar(20) not null,

            salary numeric(11.2)

);

insert into emp(name,role,salary) values

            ('坤坤','rapper',10000.1),

            ('他真的很努力','歌手',5555.22),

            ('咯咯','个人练习生',8888.88),

            ('鸡你太美','dancer',6573.50),

            ('小黑子','职业喷子',1000000),

            ('inkun','真爱粉',500000);

 

*查询每个角色的最高工资，最低工资和平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role; 

 1.1.3、HAVING

GROUP BY子句进行分组以后，需要对分组结果在进行条件过滤，不能使用WHERE，而需要使用HAVING

*显示平均工资低于10000的角色和他的平均工资

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<10000;

 

1.2、联合查询

多表查询往往是对多张表的数据取笛卡尔积

 

*计算多张表的笛卡尔积 

 

 两张表做完笛卡尔积之后就是此纪录（全排列）

 

 *通过连接条件过滤无效数据

首先确定是那几张表与查询

根据几张张表中的主外键关系（关联关系）字段做连接条件的过滤

精简查询字段，得到想要的结果

初始化测试数据  

insert into classes(name, `desc`) values
('计算机系2020级1班', '学习了计算机原原理、Java语言'),
('中文系2020级3班','学习了纺织学'),
('自动化2020级5班','学习了汉语言');
insert into student(sn, name, qq_mail, classes_id) values
('09982','坤坤','kunkun@qq.com',1),
('00835','鸡你太美',null,1),
('00391','哎呦',null,1),
('00031','你干嘛','ganma@qq.com',1),
('00054','rap',null,1),
('51234','篮球','lanqiu@qq.com',2),
('83223','唱跳',null,2),
('09527','练习两年半','session250@qq.com',2);
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),
-- rap
(81, 5, 1),(37, 5, 5),

-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- 练习两年半
(80, 7, 2),(92, 7, 6);

 1.2.1、内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

 示例：

(1)查询“鸡你太美”的成绩

select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='鸡你太美';

或者

select sco.score from student stu ,score sco where stu.id=sco.student_id and stu.name='鸡你太美';

  

(2)查询所有同学的总成绩，及同学的个人成绩

-- 成绩表对学生表是多对一的关系，查询总成绩是根据成绩表的同学id来进行分组的

 SELECT 

        stu.sn,

        stu.name,

        stu.qq_mail,

        sum(sco.score)

 FROM

        student stu
        JOIN score sco ON stu.id = sco.student_id

GROUP BY

       sco.student_id;

 

(3)查询所有同学的成绩，及同学的个人成绩

-- 查询出来的都是有成绩的同学，练习两年半竟然没有出现

select * from student stu join score sco on stu.id=sco.student_id;

-- 学生表、成绩表、课程表三张表关联查询

 SELECT 

         stu.id,

         stu.sn,

         stu.name,

         stu.qq_mail,

         sco.score,

         sco.student_id,

         cou.name

 FROM

         student stu
        JOIN score sco ON stu.id = sco.student_id
        JOIN course cou ON sco.course_id = cou.id

 ORDER BY

         stu.id;

 1.2.2、外连接

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

语法：

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

 示例：查询所有同学的成绩，及同学的个人信息，如果改同学没有成绩也需要显示

-- 左连接

select * from student stu left join score sco on stu.id=sco.student_id;

-- 右连接

select * from student stu left join score sco on stu.id=sco.student_id;

-- 学生表、成绩表、课程表三张表关联查询

 SELECT 

         stu.id,

         stu.sn,

         stu.name,

         stu.qq_mail,

         sco.score,

         sco.student_id,

         cou.name

 FROM

         student stu
         LEFT JOIN score sco ON stu.id = sco.student_id
         LEFT JOIN course cou ON sco.course_id = cou.id

 ORDER BY

         stu.id;

1.2.3、自连接

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

示例：

显示所有“计算机原理”成绩 比“java”高的成绩信息

-- 先查询“计算机原理”和“java”课程的id

select id,name from course where name='java' or name='计算机原理';

-- 再查询成绩表中，“计算机原理”成绩比“java”高的信息

 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=3;

-- 也可以使用join on语句来进行自连接查询

 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 = 3;

 

 

以上查询只显示了成绩信息，并且是分布执行的，要显示学生信息并在一条语句显示 

 SELECT 

           stu.*,

           s1.score Java,

           s2.score 计算机原理

 FROM

           score s1

           JOIN score s2 ON s1.student_id=s2.student_id

           JOIN student stu ON s1.student_id=stu.id

           JOIN course c1 ON s1.course_id=c1.id

           JOIN course c2 ON s2.course_id=c2.id

            AND s1.score<s2.score

            AND c1.name='Java'

            AND c2.name='计算机原理';

        

 

 1.2.4、子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

*单行子查询：返回一行记录的子查询

 select * from student where classes_id=(select classes_id from student where name='练习两年半');

 

*多行子查询：返回多行记录的子查询

示例:查询"语文"或"英语"课程的成绩信息

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] EXISTS关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
 where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
 where (name!='语文' and name!='英文') and cou.id = sco.course_id); 

 *在from子句使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

-- 获取“中文系2020级3班”的平均分，将其看作临时表
 SELECT
 avg( sco.score ) score
 FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
 WHERE
 cls.NAME = '中文系2020级3班';

 

 1.2.5、合并查询

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

*UNION

示例：查询id大于3的，或者课程名为英文的课程

 select * from course where id>3
 union
 select * from course where name='英文';
 -- 或者使用or来实现
 select * from course where id<3 or name='英文';

 

 *UNION ALL

该操作符用于取得两个结果集的并集。当使用该操作符，不会去重掉重复行

 示例：查询id大于3，或者课程名为"Java"的课程

 select * from course where id>3 

 union all

 select * from course where name='Java';