MySQL高级查询

COUNT

在面试过程中遇到了这样一个SQL问题，学生表S（sid,Sname）、课程表C（cid,Cname）、选课表SC（sid,cid,score），查询出三列：指定课程名 及格人数 不及格人数。SQL如下：

SELECT
    c.cname,
    COUNT( sc.score >= 60 OR NULL ) '及格',
    COUNT( sc.score < 60 OR NULL ) '不及格' 
FROM
    sc,
    c 
WHERE
    sc.cid = c.cid AND sc.cid =1

• 为什么count函数里面要写 or null ?
  如COUNT( sc.score >= 60 OR NULL ) '及格', 这部分 为什么要加上or NULL 直接COUNT( sc.score >= 60) '及格'有什么问题吗？不就是要找分数>=60的数据吗，为什么要计算NULL的数据？

• 答案：
  mysql中count函数在值为NULL时不统计数，即count函数不统计null列，如下添加了两个统计false和null的结果集。

统计null列

因为 当分数不及格(<60)时 ，sc.score >= 60 结果返回 false 而不是 NULL，所以会进行统计。而加上or null就会返回null，不进行统计。

在count函数中，count('任意内容')都会统计出所有记录数，因为count只有在遇见null时不计数，即count(null)==0，因此前者单引号内不管输入什么值都会统计出所有记录数）至于加上or NULL ， 很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式 。当sc.score >= 60这条语句不成立时COUNT( sc.score >= 60 OR NULL ) '及格', 的结果是NULL，Count才不会统计上这条记录数

连接查询

简单理解就是把多张表的数据按照条件进行连接，成为一张信息更加完整的表。保留主表所有数据，把匹配到表的数据连接在对应主表行位置，未匹配到的行全部为空。

• 内连接

格式1(推荐)：select a.*,b.* from a [inner] join b on ab_condition

格式2(隐式)：select a.*,b.* from a ,b where ab_condition

• 外链接

左外连接：select a.*,b.* from a left [outer] join b on ab_condition

右外连接:select a.*,b.* from a right [outer] join b on ab_condition

联合查询

将多次查询(多条select语句)的结果进行拼接，每条查询语句字段总数要求一致，对各个字段类型不做限制。

• UNION(默认) 去除重复行

  
  UNION

• UNION ALL

  
  UNION ALL

Order by

• 在联合查询中: order by不能直接使用,需要对查询语句使用括号才行；
• 另外，要orderby生效: 必须搭配limit: limit使用限定的最大数即可.

正确联合查询Order by

子查询

查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).

子查询分类：

• 按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

1. From子查询: 子查询跟在from之后

2. Where子查询: 子查询出现where条件中

3. Exists子查询: 子查询出现在exists里面

• 按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

以新闻表和分类表分别举例：

新闻表
分类表

1. 标量子查询: 子查询得到的结果是一行一列

   
   标量子查询
   

   需求: 找到分类为科技的所有文章标题

SELECT
    news.title 
FROM
    news 
WHERE
    news.cid = ( SELECT category.id FROM category WHERE category.`name` = '科技' )

2. 列子查询: 子查询得到的结果是一列多行
   我找出语文和数学都及格人的名字

SELECT
    s.name
FROM
    s 
WHERE
    s.sid NOT IN ( SELECT DISTINCT sc.sid FROM sc WHERE sc.score < 60 )

3. 行子查询: 子查询得到的结果是多列一行(多行多列) （1,2,3出现的位置都是在where之后)

4. 表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

Group by

全部新闻
分组统计

GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。

同时，GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。

• GROUP BY关键字与HAVING一起使用
  实例：将employee表的sex字段进行分组查询。然后显示记录数大于等于3的分组。

SELECT
    sex,
    COUNT( sex ) 
FROM
    employee 
GROUP BY
    sex 
HAVING
    COUNT( sex ) >= 3;