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
-
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)
-
in字段可以是两个字段同时使用 比如
WHERE (Employee.DepartmentId , Salary) IN (SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId)
-
COALESCE()
用来返回列表中第一个非null表达式的值。如果所有表达式求值为null,则返回nullSELECT 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;
-
在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,则赋值为bselect 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
-
CEILING函数用于把数值字段向上取整;FLOOR 函数用于把数值字段向下取整数;ROUND 函数用于把数值字段舍入为指定的小数位数
ROUND(k,0)
是向上取整