Coursea-用MySQL管理大数据-Week4

1 子查询

• 子查询必须用括号括起来

• 子查询中不能在使用ORDER BY，在包含子查询的外部查询中可以使用ORDER BY

• 子查询若在SELECT或WHERE子句中使用时返回多行，则必须与用于处理多个值的运算符（如IN）结合使用

• 执行顺序：包含子查询的查询始终始终首先运行最里面的子查询，然后依次从最里面的查询到最外面的查询依次运行，最会进行后续查询。

• 必须为在FROM子句内的子查询中创建的任何派生表提供别名。 否则，数据库将无法引用派生表

• 每次我们想要执行基于派生表的函数时，都需要使用此别名

• 子查询中可以引用子查询之外的表。 但是，外部查询不能引用在子查询中的表或者派生表

EXISTS和NOT EXISTS的作用与IN和NOT IN很相似，但是EXISTS和NOT EXISTS只能在子查询中使用！

实际上，EXISTS语句与子查询搭配使用时，子查询中通常在SELECT后使用星号而不是显式列名编写。 因为星号更容易书写，并且由于返回的都只是逻辑上的对/错，因此无论您使用星号还是显式列名都无关紧要。

SELECT DISTINCT u.user_guid AS uUserID
FROM users u
WHERE EXISTS (SELECT d.user_guid
              FROM dogs d 
              WHERE u.user_guid =d.user_guid);

等同于下面

SELECT DISTINCT u.user_guid AS uUserID
FROM users u
WHERE EXISTS (SELECT *
              FROM dogs d 
              WHERE u.user_guid =d.user_guid);

如果要查询users表中没有在dogs表中出现的用户数目，可以用以下实现：但是很疑惑为啥not in 不可以呢？

上图中第二个发现是子查询返回的结果里有NULL，而in或者not in只要跟null沾上，结果必然是空集！

2 逻辑运算

if

语法如下：

IF([your conditions],[value  if conditions are met],[value if conditions are NOT met])

可以利用if赋值1或者0直接集计，也可以进一步通过group by集计

IF可以嵌套使用，在嵌套使用IF表达式时，将每个IF表达式以及整个IF表达式括在括号中非常重要,如：

IF(cleaned_users.country='US','In US', IF(cleaned_users.country='N/A','Not Applicable','Outside US'))

case

如：

SELECT CASE WHEN cleaned_users.country="US" THEN "In US"
            WHEN cleaned_users.country="N/A" THEN "Not Applicable"
            ELSE "Outside US"
            END AS US_user, 
      count(cleaned_users.user_guid)   
FROM (SELECT DISTINCT user_guid, country 
      FROM users
      WHERE country IS NOT NULL) AS cleaned_users
GROUP BY US_user

如果case中的字段不需要进行复杂的操作，也可以使用下面这种比较清爽的写法

SELECT CASE cleaned_users.country
            WHEN "US" THEN "In US"
            WHEN "N/A" THEN "Not Applicable"
            ELSE "Outside US"
            END AS US_user, 
      count(cleaned_users.user_guid)   
FROM (SELECT DISTINCT user_guid, country 
      FROM users
      WHERE country IS NOT NULL) AS cleaned_users
GROUP BY US_user

使用CASE表达式的注意事项！！！

• CASE表达式末尾的END不能省略
• CASE表达式不需要括号
• ELSE是可以省略的。若省略ELSE，则将不满足表达式中明确说明的任何条件的行赋为NULL
• CASE表达式可以在SQL语句中的任何位置使用，包括GROUP BY，HAVING和ORDER BY或SELECT

not and or 执行顺序

在没有括号时，三者的优先级为 not>and>or

CASE WHEN "condition 1" OR "condition 2" AND "condition 3"...

满足条件2和3的同时， 或者吗，满足条件1

CASE WHEN "condition 3" AND "condition 1" OR "condition 2"...

满足条件1和3的同时， 或者满足条件2

CASE WHEN ("condition 1" OR "condition 2") AND "condition 3"...

满足条件1或者2，同时满足条件3