SQL语句 相关学习

SQL 相关学习

1. SQL 线上练习网站
   • 1-12节参考答案
2. SQL 线上练习相关文章
3. SQL 线上语法

SQL 相关知识

1. sql 语句对大小写不敏感 （下面两句 sql 语句最终的执行结果是一样的）
   • SELECT * FROM movies
   • select * from movies

查询语句

1. 查询指定数据表的所有数据(所有属性)

例子: 查询 movies 表中的所有数据, sql 语句如下.

select * from movies

例子: 查询 movies 中所有 title,content 属性的语句

select title,content from movies

2. 查询指定数据表中的指定属性(带条件查询)

例子: 查询 movies 中 year = 1995

select * from movies where year = 1995
select year from movies where year = 1995

3. 查询数据表中总条数(数据总数)

select count(*) from movies

4. select 计算

select 语句后面跟上 数学表达式,可直接进行计算

sql 1+1

简单条件练习

• 【简单条件】找到 id 为 6 的电影 ✓
• 【简单条件】找到在 2000-2010 年间 year 上映的电影
• 【简单条件】找到不是在 2000-2010 年间 year 上映的电影
• 【简单条件】找到头 5 部电影
• 【简单条件】找到 2010（含）年之后的电影里片长小于两个小时的片子

select * from movies where id = 6

select * from movies where year between 2000 and 2010

select * from movies where year not between 2000 and 2010

select * from movies where id <6

select * from movies where year >= 2010 and length_minutes < 120

复杂条件练习

• 【复杂条件】找到所有 Toy Story 系列电影
• 【复杂条件】找到所有 John Lasseter 导演的电影
• 【复杂条件】找到所有不是 John Lasseter 导演的电影
• 【复杂条件】找到所有电影名为 “WALL-” 开头的电影
• 【复杂条件】有一部 98 年电影中文名《虫虫危机》请给我找出来

select * from movies where title like "Toy Story%"

select * from movies where director = "John Lasseter"

select * from movies where director != "John Lasseter"

select * from movies  where title like "WALL-_"

排序

• 【结果排序】按导演名排重列出所有电影(只显示导演)，并按导演名正序排列
• 【结果排序】列出按上映年份最新上线的 4 部电影
• 【结果排序】按电影名字母序升序排列，列出前 5 部电影
• 【结果排序】按电影名字母序升序排列，列出上一题之后的 5 部电影 (这里的上一题指的是,第三题)
• 【结果排序】如果按片长排列，John Lasseter 导演导过片长第 3 长的电影是哪部，列出名字即可

select distinct director from movies

select * from movies
order by year desc
limit 4 offset 1

select * from movies
order by title asc
limit 5 offset 0

select * from movies
order by title asc
limit 5 offset 5

select title from movies
where director ="John Lasseter"
order by length_minutes desc
limit 1 offset 2

综合练习

• 【复习】列出所有加拿大人的 Canadian 信息(包括所有字段) ✓
• 【复习】列出所有在 Chicago 西部的城市，从西到东排序(包括所有字段) ✓
• 【复习】用人口数 population 排序,列出墨西哥 Mexico 最大的 2 个城市(包括所有字段)
• 【复习】列出美国 United States 人口 3-4 位的两个城市和他们的人口(包括所有字段)

select * from north_american_cities
where country = "Canada"

select * from north_american_cities
where longitude < -87.629798
order by longitude asc

select * from north_american_cities
where country = "Mexico"
order by population desc
limit 2 offset 0

select * from north_american_cities
where country = "United States"
order by population desc
limit 2 offset 2

联表查询

1. 注意: 联表查询会把两张表中的所有属性对应的所有数据给返回

联表查询练习

表一: movies

表二: boxoffice

关联字段:

• 主表 - id 附表 - movie_id

• 【联表】找到所有电影的国内 Domestic_sales 和国际销售额 ✓
• 【联表】找到所有国际销售额比国内销售大的电影
• 【联表】找出所有电影按市场占有率 rating 倒序排列
• 【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

select * from movies
inner join boxoffice on id = movie_id

select * from movies
inner join boxoffice on id = movie_id
where International_sales > Domestic_sales

select * from movies
inner join boxoffice on id = movie_id
order by rating

select director,international_sales from movies
inner join boxoffice on id = movie_id
order by international_sales desc
limit 1 offset 0

外连接

inner join 只会保留两个表都存在的数据，在某些场景下会有问题（会丢失部分数据）

左连接:left join ; 右连接: right join; 全连接: full join;

• left join: 保留 a(左表)的所有行
• right join: 保留 b(右表)的所有行
• full join: 同时保留 a b 两张表的所有行

练习

• 【复习】找到所有有雇员的办公室(buildings)名字 ✓
• 【复习】找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)
• 【难题】找到所有有雇员的办公室(buildings)和对应的容量

select distinct building from buildings
inner join employees on building_name = building

select distinct building_name, role from buildings
left join employees on building_name = building

select distinct building, capacity from buildings
inner join employees on building_name = building

特殊关键字 NULLs

A 和 B 有数据差异时，必须用 NULL 来填充。这种情况，可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL.

• 【复习】找到雇员里还没有分配办公室的(列出名字和角色就可以) ✓
• 【难题】找到还没有雇员的办公室

select name,role from employees
left join buildings on building = building_name
where building_name is null

select distinct building_name from  buildings
left join employees on building = building_name
where role is null

查询中使用表达式

在 sql 的查询语句中,可以对查询的属性进行表达式运算
也可以对表达式运算后的属性,使用 as 重新命名.

表达式练习

• 【计算】列出所有的电影 ID,名字和销售总额(以百万美元为单位计算) ✓
• 【计算】列出所有的电影 ID,名字和市场指数(Rating 的 10 倍为市场指数)
• 【计算】列出所有偶数年份的电影，需要电影 ID,名字和年份
• 【难题】John Lasseter 导演的每部电影每分钟值多少钱,告诉我最高的 3 个电影名和价值就可以

select id,title,( Domestic_sales + international_sales)/1000000  from movies
inner join boxoffice on id = movie_id

/* 别名 (扩展题)*/
select id,title,( Domestic_sales + international_sales)/1000000 as total_sales from movies
inner join boxoffice on id = movie_id

select id,title,Rating*10  from movies
inner join boxoffice on id = movie_id

select id,title,year  from movies
inner join boxoffice on id = movie_id
where year%2 =0

/* 最后一题，一定要注意  order by 指令的顺序 （where 指令后） */
select title, (Domestic_sales + International_sales) / length_minutes as sale_value from movies
inner join boxoffice on id = movie_id
where Director ="John Lasseter"
order by sale_value desc
limit 3 offset 0

查询中进行统计 （统计，分组 练习）

注意：一般 group by 指令会配合 sql 函数使用.

• 【统计】找出就职年份最高的雇员(列出雇员名字+年份） ✓
• 【分组】按角色(Role)统计一下每个角色的平均就职年份
• 【分组】按办公室名字总计一下就职年份总和
• 【难题】每栋办公室按人数排名,不要统计无办公室的雇员

select name,years_employed from employees
order by years_employed desc
limit 1 offset 0

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

select  building,sum(years_employed) from employees
group by building

select  building,count(building) from employees
where building is not null
group by Building

查询中进行统计 Ⅱ （统计，分组 练习）

• 【统计】统计一下 Artist 角色的雇员数量 ✓
• 【分组】按角色统计一下每个角色的雇员数量
• 【分组】算出 Engineer 角色的就职年份总计
• 【难题】按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色，数量，有无办公室,注意一个角色如果部分有办公室，部分没有需分开统计）

select count(role) from employees
where role = "Artist"

select role, count(role) from employees
group by role

select  sum(years_employed) from employees
where role = "Engineer"
group by role


select  role,count(role),building is not null as bn from employees
group by role,bn

SQL 完全体

将上面所有语法总结到一起就是下面的样子

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

SQL 查询执行顺序

1. FROM 和 JOINS

FROM 或 JOIN 会第一个执行，确定一个整体的数据范围. 如果要 JOIN 不同表，可能会生成一个临时 Table 来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)

2. WHERE

我们确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选 col 属性 只能来自 FROM 圈定的表. AS 别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式

3. GROUP BY

如果你用了 GROUP BY 分组，那 GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.

4. HAVING

如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.

5. SELECT

确定结果之后,SELECT 用来对结果 col 简单筛选或计算,决定输出什么数据

6. DISTINCT

如果数据行有重复 DISTINCT 将负责排重

7. ORDER BY

再结果集确定的情况下, ORDER BY 对结果做排序. 因为 SELECT 中的表达式已经执行完了.此时可以用 AS 别用.

8. LIMIT / OFFSET

最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

总结

不是每一个 SQL 语句都要用到所有的句法，但灵活运用以上的句法组合和深刻理解 SQL 执行原理将能在 SQL 层面更好的解决数据问题，而不用把问题 都抛给程序逻辑.

练习

• 【复习】统计出每一个导演的电影数量（列出导演名字和数量）
• 【复习】统计一下每个导演的销售总额(列出导演名字和销售总额)
• 【难题】按导演分组计算销售总额,求出平均销售额冠军（统计结果过滤掉只有单部电影的导 演，列出导演名，总销量，电影数量，平均销量)
• 【变态难】找出每部电影和单部电影销售冠军之间的销售差，列出电影名，销售额差额

select director,count(title) from movies
group by director

select director,sum(Domestic_sales) + sum(International_sales) as total_sales  from movies
inner join boxoffice on id =movie_id
group by director

select director ,
sum(Domestic_sales +International_sales) as total_sales,
count(title)as total_count ,
sum(Domestic_sales +International_sales)/ count(title) as avg_sales
from movies
inner join boxoffice on id =movie_id
group by director
having count(title)>1
order by avg_sales desc
limit 1 offset 0

SELECT
    (
        SELECT (Domestic_sales+International_sales) as total_sale FROM movies
        left join boxoffice on movies.id = boxoffice.movie_id
        order by total_sale desc
        limit 1
    )
    - (Domestic_sales+International_sales) as sale_diff,title FROM movies
left join boxoffice on movies.id = boxoffice.movie_id
order by sale_diff desc