sql之查询语句基本操作详解中

### group by 子句

  • group by对指定的列（一个或多个）进行分组，记录相同的分为一组

  • group by 后面跟的列也叫分组特性列，又叫分组字段

  • 使用group by后，select语句能选择的列只能包括分组特性列和聚合函数

  • 例子

    • 按照班号分组，列出学生表中的班号
      image-20191128132857029

    • 按照班号分组，列出学生表中的班号，还要列出学生姓名
      image-20191128133032638
      注：使用group by后select显示的字段只能为分组字段与聚合函数

    • 按照班号分组，列出学生表中的班号，统计每个班的平均身高，平均体重，人数，最高分，不包括未分班的那些同学
      image-20191128133534687

      select cno '班级',avg(height) '平均身高',avg(weight)'平均体 重',count(sno) '班级人数',max(score) '最高分' from stu where cno is not null group by cno;

    • 先按照班号分组，再按照性别分组，列出学生表中的班号和性别，统计出每个班男女生的平均身高，平均体重，人数，最高分，不包括未分班的那些同学，结果先按班号，再按照男女的顺序排序
      image-20191128134002938

      select cno 班级,sex 性别,avg(height) 平均身高,avg(weight) 平均体重,count(*) 人数,max(score) 最高分 from stu where cno is not null group by cno,sex order by cno,sex desc;

    • 按照学生出生年份分组，统计出所有学生每个年份的人数，最高分，最低分，按照年份排序
      image-20191128134315768

      select year(birth) 出生年份,count(*) 人数,max(score) 最高分,min(score) 最低分 from stu group by year(birth) order by year(birth);

having子句

  • having子句用于对group by后的结果进行筛选过滤

  • having子句可以对分组特性列进行过滤，也可以对聚合函数的值进行过滤

  • 例子

    • 按照学生出生年份分组，统计出所有学生每个出生年份的人数，最高分，最低分，按照年份排序，并从结果中找出人数超过2个，并且最高分有超过700分的年份分组
      image-20191128134655431

    • 已分班的学生中，哪些班学生的平均身高超过175，列出其班号和人数
      image-20191128135145844

    • 已分班的学生中，哪些班的学生每个人的体重都超过50公斤，列出其班号和人数
      image-20191128135304795

    • 统计1班的学生人数，列出班号和人数

      image-20191128135546312

      或

      image-20191128135647871

    • 统计人数超过5个的班号和人数，结果按照班号排序，只显示一行记录

      image-20191128140212997

  • where和having的区别(以例5举例说明)

    • 相同点：

      • 都是对表行按照条件进行的筛选

    • 不同点：

      • where对原始的stu表按照条件筛选行

  • having对分组后的新表按照条件筛选行

  • 各个子句执行优先级

    1. from
    2. where
    3. group by
    4. having
    5. select
    6. order by

视图view

  • 属于关系型数据库中的一种常用数据对象，保存一段select语句，可以把视图名称当作普通表来使用

  • 对视图做DML操作

    • 视图仅仅保存SELECT语句，其select语句中的表称为基表（或母表），视图对应的数据是放在基表中的，基表中数据产生变化，视图的结果集也会产生变化
    • 如果视图和其相关基表存在记录一一对应的关系，排除基表上存在约束的条件，通常可以对视图做DML操作，但是不要轻易对视图做DML操作，以防止对基表数据产生意想不到的影响

  • 视图的主要功能

    • 提高数据安全性（隐藏部分行和列）
    • 简化查询

  • 视图常用操作

    • create view view_name as select ... 创建视图

      image-20191128142040241

    • show table status\G 查看定义的视图

      image-20191128142245509

      image-20191128142141556

    • show create table VIEW 查看视图的详情

      image-20191128142359627

多表连接查询

  • 将分散在多个表中的信息（列）横向合并在一起，通常需要指明连接条件，多表连接查询和单表查询相比会耗费更多的系统资源

  • 分类

    • 交叉连接(cross join )

  • 等值连接

    • 内联接(inner join)

    • 左连接 （左外连接 left join）

    • 右连接 （右外连接 right join）

    • 非等值连接

  • 创建测试表

    image-20191128143456676

    image-20191128143522716

  • 交叉连接

    image-20191128143638136

    注：交叉连接的结果为笛卡尔积（直积），

  • 等值连接--内连接

    image-20191128145356267

  • 等值连接--左连接

    image-20191128145504062

  • 等值连接--右连接

    image-20191128145553141

  • 非等值连接

    image-20191128151141335

  • 例子

    • 测试表如下：

      image-20191128160030141

      image-20191128160041559

      image-20191128160054738

    • 显示学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，按照班号和学号排序 (等值连接-内连接)

      image-20191128155932244

      select sno,sname,sex,stu.cno,cname,teacher from stu inner join class on stu.cno=class.cno order by stu.cno,stu.sno;

    • 显示全部学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，无班主任的显示‘暂无’，按照班号和学号排序 (等值连接-左连接)

      image-20191128162732746

    • 显示所有学生的学号，姓名，性别，身高，体重，班号，BMI指数（体重/身高^2）,BMI表中对应的体态，低值，高值和性别 (非等值连接)

      image-20191128163722278

      select sno,sname,stu.sex,height,weight,stu.cno,round(weight/((height/100)*(height/100)),2) BMI,bmi.lval,bmi.hval,bname,bmi.sex from stu join bmi on stu.sex=bmi.sex and round(weighgt/((height/100)*(height/100)),2) between bmi.lval and bmi.hval;

    • 基于上面的查询结果，把肥胖的同学和他们的班主任找出来 (三个表连接)

      image-20191128164722269

      select sno,sname,stu.sex,height,weight,stu.cno,round(weight/((height) BMI,bmi.lval,bmi.hval,bname,bmi.sex,cname,teacher from stu join bmi on stu.sex=bmi.sex and round(weight/((height/100)*(height/100)),2) between bmi.lval and bmi.hval left join class on stu.cno=class.cno where bmi.bname='肥胖';

复合查询

  • 复合查询指用集合运算符对多个查询结果集进行运算，产生新的查询结果集。MySQL常用集合运算符包括以下2种：

    • union，

      • 对两个结果集进行并集操作，重复行只取一次，同时进行默认规则的排序（升序）

    • union all

      • 对两个结果集进行并集操作，包括所有重复行，不进行排序

  • MySQL不支持交集和差集运算，但 ++++可以通过多表连接的方式实现

    image-20191128165006730

  • 创建测试表如下

    image-20191128165329701

  • 求表class和class1的并集，重复记录只显示一次

    image-20191128185547354

  • 求表class和class1的并集，重复记录重复显示

    image-20191128185819869

  • 求表class和class1的交集

    image-20191128190009120

    注：mysql无intersect集合运算符，交集运算可以通过多表连接实现

  • 求表class和class1的差集，即显示class表中在class1表中没有的行记录

    image-20191128190149937

  • 学生表按照班号分组统计各班人数，也要显示出合计总人数

    image-20191128190423696

posted @ 2019-11-28 19:50  MirL  阅读(449)  评论(0)  编辑  收藏  举报