MySQL基础之Ⓒ高级查询：临时表、only_full_group_by、条件判断语句 case when...then...end、类似三元运算的运用：if(exp1,exp2,exp3)

1）、临时表

 

select * from (select * from tb where id< 10) as B;
    
    select 
        id,
        name,
        1,
        (select count(1) from tb)
    from tb2

    SELECT 
        student_id,
        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
        (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
    from score as s1;

 

2）、分组时，mysql5.7.5新增sql_mode=only_full_group_by的影响 

　　 A.查看，去掉该模式，根据具体情况选择使用。

 　　查看该模式：
  　　　 会话级变量

 show session variables like 'auto_inc%';

　　　　全局变量

 show global variables like 'auto_inc%';

 

 设置，去掉或者加上变量
 　　# 需要将mysql客户端，查询:

 select @@global.sql_mode;

结果中的ONLY_FULL_GROUP_BY模式去掉

 # 设置命令：

set global  sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

B.在该模式下的对应解决问题的方法：

a、GROUP_CONCAT()的使用

SELECT
    r.student_id as '学生ID',
    GROUP_CONCAT(r.score ORDER BY r.course_id SEPARATOR '    ') as '生物 物理 体育',
    count(1) as '有效课程数',
   avg(r.score) as '有效平均分'
  FROM
   result r
  LEFT JOIN course c ON c.cid = r.course_id
  WHERE
   c.cname = '生物'
  OR c.cname = '物理'
  OR c.cname = '体育'
  group by r.student_id
  order by avg(r.score)

 

 b、any_value()的使用,只显示聚集结果中的第一条。

  select any_value(name), group_id from game group by group_id;

 

3）、条件判断语句 case when...then...end

 select 
  course_id,
  max(num),
  min(num),
  min(num)+1,
  case when min(num) <10 THEN 0 ELSE min(num) END as c  
 from score GROUP BY course_id
 
 select 
  course_id,avg(num),
  sum(case when num <60 THEN 0 ELSE 1 END),
  sum(1),
  sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl 
 from score 
 GROUP BY course_id 
 order by AVG(num) asc,jgl desc;

 

4）、三元运算的运用：if(exp1,exp2,exp3)
 A、运算时的判断是否为null，如果是null就转换成0，方便运算，不报错！
 顺便展示INNER JOIN 不显示null项，这样使用三元运算符显得有点多余！搭配LEFT JOIN 或者RIGHT JOIN就行了！
  

SELECT
   A.student_id
  FROM
   (
    SELECT
     r.student_id,
     c.cname,
     r.score
    FROM
     result r
    LEFT JOIN course c ON c.cid = r.course_id
    WHERE
     c.cname = '生物'
   ) AS A
  LEFT JOIN (
  # INNER JOIN (
   SELECT
    r.student_id,
    c.cname,
    r.score
   FROM
    result r
   LEFT JOIN course c ON c.cid = r.course_id
   WHERE
    c.cname = '物理'
  ) AS B ON A.student_id = B.student_id
  WHERE
   A.score >
  IF (isnull(B.score), 0, B.score); 
  

 

5）、查询字段为其他select语句（临时表，但是唯一值）
 相当于两次循环，外层的select，将变量r.student_id传入到临时表中的select（内层循环），查到唯一结果，显示出来。

 SELECT
     r.student_id as '学生ID',
     count(1) as '有效课程数',
  (select r2.score from result r2 LEFT JOIN course c ON c.cid = r2.course_id where c.cname = '物理' and r2.student_id = r.student_id) as '物理',
  (select r2.score from result r2 LEFT JOIN course c ON c.cid = r2.course_id where c.cname = '生物' and r2.student_id = r.student_id) as '生物',
  (select r2.score from result r2 LEFT JOIN course c ON c.cid = r2.course_id where c.cname = '体育' and r2.student_id = r.student_id) as '体育',
  avg(r.score) as '有效平均分'
 FROM
  result r
 group by r.student_id
 order by avg(r.score)

 

6）、查询语句中的运算符，百分数用到的类型转换
 综合使用条件判断语句，减少运算复杂度，提高速度。

 SELECT
  r.course_id,
  avg(r.score) AS avg_score,
  CAST(
   sum(
    CASE
    WHEN r.score > 60 THEN
     1
    ELSE
     0
    END
   ) / count(1) AS DECIMAL (28, 2)
  ) AS percent
 FROM
  result r
 GROUP BY
  r.course_id
 ORDER BY
  avg_score ASC,
  percent DESC;

 

7）、提高的数据库查询效率的方法
 　　A、表定义阶段
  　　　　a.用char不用varchar，固定长度的字符会加快查询速度；
  　　　　b.如果不得不使用varchar，应该将该字段放到表的尾部；
 　　B、尽量避免使用DISTINCT去重，因为效率不高，可用group by去重；
 　　C、count函数的使用
  　　　　使用count(1),count(主键)，速度快。