⛪

SQL

select count(username)等价于select count(*)等价于select count(1)

select count(distinct username)可以去重

聚合函数在配合group之后才能加e.cid


SELECT AVG(s.gpa), e.cid
FROM enrolled AS e JOIN student AS s
ON e.sid = s.sid
GROUP BY e.cid

having是在where和group by的基础上再对聚合结果进行过滤

group by后可跟多个columns

order by后可跟多个columns

order by默认升序

limit + offset分页

where … in …后可跟sub query

select * from student where sid > all(sub query)

select * from student where sid > any(sub query)

select * from student where exists(sub query)

join也可以跟sub query

inner query可以reference outer query

row_number和over

rank函数

with可以定义新的tableT