SQL学习笔记

1. AS和INNER JOIN

数据库pet_records上面两张表Pets和Owners，合成Pets_And_Owners,注意AS和INNER JOIN的用法：

SELECT p.Name AS Pet_Name, o.Name as Owner_Name
FROM `pet_records.pets` as p
INNER JOIN `pet_records.owners` as o ON p.ID = o.Pet_ID

2. GROUPBY和ORDERBY

从数据库nhtsa_traffic_fatalities里交通事故表accident_2015中统计每周哪几天交通事故最频繁：

SELECT COUNT(consecutive_number), EXTRACT(DAYOFWEEK FROM timestamp_of_crash) as dayofweek
FROM `nhtsa_traffic_fatalities.accident_2015`
GROUP BY dayofweek
ORDER BY COUNT(consecutive_number) DESC

3. Having

选出出现次数大于1的动物：

SELECT Animal, COUNT(ID)
FROM `pet_records.pets`
GROUP BY Animal
HAVING COUNT(ID) > 1