SQL多表查询

SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

SELECT *  
FROM movies as a 
inner join boxoffice as b 
on a.id = b.movie_id 
where domestic_sales < international_sales

SELECT director,international_sales 
FROM movies as a 
inner join boxoffice as b 
on a.id = b.movie_id 
order by international_sales desc
limit 1

SELECT building_name FROM buildings
left join employees on buildings.building_name = employees.building
where name is  null

SELECT id,title,(international_sales+domestic_sales)/ 1000000 as total 
FROM movies
left join boxoffice on movies.id=boxoffice.movie_id

取余 %

SELECT id,title,year  
FROM movies
where year % 2 = 0

SELECT title,(domestic_sales+international_sales)/length_minutes as value 
FROM movies as a
left join boxoffice as b on a.id=b.movie_id
where director='John Lasseter'
order by value desc
limit 3

group by语句——分组统计

SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column

select role,avg(years_employed) as a
from employees
group by role

Having语句

对分组之后的数据再做SELECT筛选

SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias, …
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition;

SELECT count(name) as num 
FROM employees
group by role 
having role = 'Artist'