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

### group by 子句

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

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

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

• 例子

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

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

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

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

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

    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;
    

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

    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分的年份分组
    

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

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

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

    

    或

    

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

    

• 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 ... 创建视图

    

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

    

    

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

    

多表连接查询

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

• 分类

  • 交叉连接(cross join )

• 等值连接

  • 内联接(inner join)

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

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

  • 非等值连接

• 创建测试表

  

  

• 交叉连接

  

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

• 等值连接--内连接

  

• 等值连接--左连接

  

• 等值连接--右连接

  

• 非等值连接

  

• 例子

  • 测试表如下：

    

    

    

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

    

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

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

    

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

    

    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;
    

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

    

    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不支持交集和差集运算，但 ++++可以通过多表连接的方式实现

  

• 创建测试表如下

  

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

  

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

  

• 求表class和class1的交集

  

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

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

  

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