2022.09.11学习笔记

SQL

• full join 需要连接条件，需要使用on，cross join 没有连接条件，可以全部显示出来（一个表3条数据，一个表4条数据，cross join出来12条数据）

• 如果存在某行中的某列数据为null，此时直接使用count(*)统计的是行数，而不能统计出有多少该列不为null的行，需要具体的count(某列)

  select stu.student_id,student_name,sbj.subject_name,count(e.subject_name) attended_exams
  from Students stu cross join Subjects sbj
  left join Examinations e on stu.student_id = e.student_id and sbj.subject_name = e.subject_name
  group by student_id,sbj.subject_name
  order by student_id,sbj.subject_name
  1
  2
  3
  4
  5

• with a as (select * from k)

  相当于是根据括号里面的select条件构造出一个新的表a

• union会去重 union all 不会去重

  with a as (
      select caller_id caller,duration from Calls
      union all 
      select callee_id caller,duration from Calls
  )
  select c.name country
  from a 
  left join Person p on a.caller = p.id
  left join Country c on left(p.phone_number,3) = c.country_code
  group by c.name
  having avg(a.duration) > (select avg(duration) from a)
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• in字段可以是两个字段同时使用 比如

  WHERE (Employee.DepartmentId , Salary) IN 
  (SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId)
  1
  2

• COALESCE() 用来返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null

  SELECT
      s1.id, COALESCE(s2.student, s1.student) AS student
  FROM
      seat s1
          LEFT JOIN
      seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
  ORDER BY s1.id;
  1
  2
  3
  4
  5
  6
  7

• 在MySQL中，CROSS JOIN语法上等价于INNER JOIN (它们可以彼此代替。在标准SQL中，它们不等价。INNER JOIN结合ON子句使用；CROSS JOIN 用于其它地方)。内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。

  总而言之，如果inner join 没有使用on 那么和cross join 是等价的

• 注意！！！left join后如果是没有的，即使该域为int类型的，它也是null，而且判断是否为null不能用= null，而要用is null

  函数判断取值ifnull(a,b)如果a是null，则赋值为b

  select k.product_id,ifnull(new_price,10) price
  from
  (
      select distinct product_id 
      from Products 
  ) as k
  left join
  (
      select product_id,new_price,
      rank() over (partition by product_id order by change_date desc) ranking
      from Products p 
      where change_date <= '2019-08-16'
  ) as t
  on k.product_id = t.product_id
  where new_price is null or t.ranking = 1
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• CEILING函数用于把数值字段向上取整；FLOOR 函数用于把数值字段向下取整数；ROUND 函数用于把数值字段舍入为指定的小数位数

  ROUND(k,0)是向上取整