SQL多表查询

最新推荐文章于 2024-04-12 17:28:59 发布

原创最新推荐文章于 2024-04-12 17:28:59 发布 · 280 阅读

0 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

2 篇文章

订阅专栏

本文深入探讨了SQL查询语句的使用技巧，包括基础的SELECT、JOIN、WHERE、ORDER BY和LIMIT语句，以及进阶的GROUP BY和HAVING子句。通过多个实例，如电影销售数据分析和员工角色平均工作年限统计，展示了如何高效地从数据库中提取所需信息。

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'