一个自学SQL的网站! 在练习中解决错误是最有效的学习手段
电影表
练习1：条件查询
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 between 1 and 5
SELECT *
FROM movies
where YEAR >=2010
AND Length_minutes < 120
-6. 找到99年和09年的电影,只要列出年份和片长
SELECT Length_minutes,YEAR
FROM movies
where YEAR = 2009 OR YEAR= 1999
或者
SELECT Length_minutes,YEAR
FROM movies
where YEAR IN (2009,1999)
练习2：操作符
字符串操作符
在字符串表达式中的字符串需要用引号 " 包含，如果不用引号，SQL会认为是一个属性列的名字，如：col_name = color 表示 col_name和color两个属性一样的行 col_name = "color" 表示 col_name 属性为字符串 "color"的行.
SELECT *
FROM movies
WHERE title LIKE "Toy Story%"
SELECT *
FROM movies
WHERE director LIKE/= "John Lasseter"
SELECT *
FROM movies
WHERE director <> /NOT LIKE "John Lasseter"
SELECT *
FROM movies
WHERE TITLE LIKE "WALL-%"
SELECT *
FROM movies
WHERE YEAR IN (1998)
SELECT TITLE,DIRECTOR,YEAR
FROM movies
WHERE DIRECTOR LIKE "Pete%"
SELECT *
FROM movies
WHERE DIRECTOR = "John Lasseter"
AND YEAR > "2000"
AND TITLE LIKE "Toy Story%"
OR TITLE LIKE "Car%"
练习3：查询结果过滤和排序
就 Movies表来说，可能很多电影都是同一年Year发布的，如果想要按年份排重，一年只能出现一部电影到结果中， 可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回。写作：DISTINCT Year
因为 DISTINCT 语法会直接删除重复的行, 我们还会学习 GROUP BY 语句， GROUP BY 也会返回唯一的行，不过可以对具有相同的 属性值的行做一些统计计算，比如：求和.
为了解决结果排序问题, 可以用 ORDER BY col_name 排序的语法来让结果按一个或多个属性列做排序.ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序，对数字来说就是升序 1，2，3，... 或降序 ... 3,2,1 . 对于文本列，升序和降序指的是按文本的字母序。
LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。你可以想象一下从一条长绳子剪下一小段的过程，我们通过 OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度。
limited查询
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;
SELECT distinct director
FROM movies
ORDER BY director ASC
SELECT distinct *
FROM movies
ORDER BY year DESC
LIMIT 4
SELECT distinct *
FROM movies
ORDER BY title asc
LIMIT 5
SELECT distinct *
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
SELECT *
FROM movies
ORDER BY director ASC,year DESC
limit 10
练习4：复习 SELECT 查询
Table(表）: north_american_cities
SELECT *
FROM north_american_cities
where country="Canada"
结果：
加拿大
SELECT *
FROM north_american_cities
WHERE COUNTRY="United States"
ORDER BY Latitude DESC
United States
SELECT *
FROM north_american_cities
WHERE Longitude<-87.629798
ORDER BY Longitude DESC
Chicago西部的城市
SELECT *
FROM north_american_cities
WHERE COUNTRY="Mexico"
ORDER BY population DESC
LIMIT 2
墨西哥Mexico最大的2个城市
SELECT *
FROM north_american_cities
WHERE COUNTRY="United States"
ORDER BY Population
LIMIT 2 OFFSET 2
美国United States人口3-4位的两个城市
SELECT *
FROM north_american_cities
ORDER BY COUNTRY ,Population DESC
LIMIT 10
北美所有城市排序
练习5：用JOIN进行多表联合查询
主键(primary key), 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复复（例如身份证号码)。一个最常见的主键就是auto-incrementing integer(自增ID，每写入一行数据ID+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.
借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）具体要用到 JOIN 关键字。
(inner join 可以直接写成join）
用INNER JOIN 连接表的语法
SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
ON mytable.id = another_table.id (主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;
通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。此时，可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（和之前的单表操作一样）.
还有一个理解INNER JOIN的方式，就是把 INNER JOIN 想成两个集合的交集。
JOIN
SELECT *
FROM movies
inner join
boxoffice
on movies.id=boxoffice.Movie_id
全部电影销售额
SELECT * FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id AND Domestic_sales国际销售额比国内销售大的电影
SELECT * FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id
order by rating desc
市场占有率rating倒序排列
SELECT director,International_sales FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id
order by International_sales desc
limit 1
排名最靠前的导演
练习6：外连接（OUTER JOINs）
用LEFT/RIGHT/FULL JOINs 做多表查询
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
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行.
将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。所有在用这三个JOIN时，你需要单独处理 NULL. 关于 NULL 下一节会做更详细的说明.
SELECT distinct building
FROM employees
where building is not null
SELECT * FROM Buildings
select distinct building_name,role
from Buildings a
left join
employees b
on a.building_name =b.building
select distinct building_name,Capacity
from employees a
left join
buildings b
on a.building =b.Building_name
where a.building is not null
练习7：关于特殊关键字 NULLs
如果某个字段你没有填写到数据库，很可能就会出现NULL 。所有一个常见的方式就是为字段设置默认值,比如 数字的默认值设置为0，字符串设置为 ""字符串. 但是在一些NULL 表示它本来含义的场景，需要注意是否设置默认值还是保持NULL。 (f比如, 当你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算).
还有一些情况很难避免 NULL 的出现, 比如之前说的 outer-joining 多表连接，A和B有数据差异时，必须用 NULL 来填充。这种情况，可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL.
在查询条件中处理 NULL
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …
SELECT role,name
FROM employees
where building is null
SELECT distinct a.building_name
FROM buildings a
left join employees b
on a.Building_name=b.Building
where b.name is null
练习8：在查询中使用表达式
之前在SQL中的出现col_name（属性名）的 地方，都只是写上col_name自身。其实在SQL中可以用col_name的地方，都可以用表达式 来指定对属性进行一定的计算或处理。举个例子：假设有一个col_name是出生日期，现在要求SQL返回当前的年龄，这就可以用一个时间计算表达式对出生日期做计算得到年龄。表达式可以对 数字运算，对字符串运算，也可以在表达式中只包含常量不包含col_name(如：SELECT 1+1）
包含表达式的例子
SELECT particle_speed / 2.0 AS half_particle_speed (对结果做了一个除2）
FROM physics_data
WHERE ABS(particle_position) * 10.0 >500
（条件要求这个属性绝对值乘以10大于500）
每一种数据库（mysql，sqlserver等）都有自己的一套函数，包含常用的数字，字符串，时间等处理过程.具体需要参看相关文档。
当我们用表达式对col属性计算时，很多事可以在SQL内完成，这让SQL更加灵活，但表达式如果长了则很难一下子读懂。所以SQL提供了AS关键字， 来给表达式取一个别名.
AS使用别名
SELECT col_expression AS expr_description, …
FROM mytable
实际上AS不仅用在表达式别名上，普通的属性列甚至是表（table）都可以取一个别名，这让SQL更容易理解.
属性列和表取别名的例子
SELECT column AS better_column_name, …
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales
ON mywidgets.id = widget_sales.widget_id
SELECT a.id,a.title,(Domestic_sales+International_sales)/1000000 as total
FROM movies a
join boxoffice b
on a.id=b.Movie_id
结果
SELECT a.id,a.title,rating*10 as total
FROM movies a
join boxoffice b
on a.id=b.Movie_id
SELECT id,title,year
FROM movies
where year %2=0
year %2=0表示年份除以2余数为0
SELECT a.title,((b.Domestic_sales+b.International_sales)/a.Length_minutes)as M
FROM movies a
join boxoffice b
on a.id=b.Movie_id
where a.director="John Lasseter"
order by M DESC
limit 3
结果
SELECT a.title,length (title),(b.Domestic_sales+b.International_sales)as total
FROM movies a
join boxoffice b
on a.id=b.Movie_id
order by length (title) desc
limit 3
结果
练习9：在查询中进行统计I (Pt. 1)
SQL默认支持一组统计表达式，他们可以完成数据统计，如：计数，求平均等。 以Movies表数据为例，这些统计表达式可以帮我们回答以下问题："Pixar公司生产了多少电影？", 或 "每一年的票房冠军是谁?".
对全部结果数据做统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
如果不指明如何分组，那统计函数将对查询结果全部数据进行统计，当然每一个统计也可以像之前用AS来取一个别名，以增加可读性.
常见统计函数
GROUP BY 数据分组语法可以按某个col_name对数据进行分组，如：GROUP BY Year指对数据按年份分组， 相同年份的分到一个组里。如果把统计函数和GROUP BY结合，那统计结果就是对分组内的数据统计了.
GROUP BY 分组结果的数据条数，就是分组数量，比如：GROUP BY Year，全部数据里有几年，就返回几条数据， 不管是否应用了统计函数.
用分组的方式统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description, …
FROM mytable
WHERE constraint_expression
GROUP BY column
SELECT name,MAX(Years_employed) FROM employees
SELECT role,avg(Years_employed) FROM employees
GROUP BY role
SELECT building,SUM(Years_employed)
FROM employees
GROUP BY building
SELECT building,count(name)as T
FROM employees
where building is not null
group by building
order by T DESC
select Years_employed,t*100/(select count(1)from employees) as rating
from
(SELECT Years_employed,count(name) as t
FROM employees
where Years_employed in (1,3,5,7)
group by Years_employed)
结果
练习10：在查询中进行统计II (Pt. 2)
在 GROUP BY 分组语法中了解到数据库是先对数据做WHERE，然后对结果做分组，如果对分组完的数据再筛选出几条如何办？ （例如按年份统计电影票房，要筛选出>100万的年份？）
可以用HAVING 语法将用来解决这个问题，对分组之后的数据再做SELECT筛选.
用HAVING进行筛选
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 "数量"
FROM employees
where role="Artist"
SELECT role ,count(name)
FROM employees
group by role
SELECT sum(Years_employed)
FROM employees
where role="Engineer"
SELECT role,count(building),"yes" FROM employees a group by role
union
select role,count(name),"no" from employees b where building is null group by role
SELECT
Role
,case when
Years_employed < 3 then “0-3”
when Years_employed < 6 then “3-6”
else “6-9” end as 就职年份
,count()
FROM employees
where 1 group by Role,就职年份
练习11：查询执行顺序
把之前的所有语法集中到一个句子中.
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
查询执行顺序
FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表)
确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式.
如果用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
如果用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用.
确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据.
如果数据行有重复DISTINCT 将负责排重.
在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.
最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.
练习
SELECT director as "导演", count(id) as "电影数量"
FROM movies
group by director
SELECT a.director as "导演", SUM(b.Domestic_sales+International_sales) as "销售总额"
FROM movies a
join boxoffice b
on a.id=b.movie_id
group by a.director
order by "销售总额" desc
SELECT DIRECTOR,COUNT(ID) AS "电影数量",
SUM(b.Domestic_sales+b.International_sales) as "销售总额",
(SUM(b.Domestic_sales+b.International_sales)/COUNT(ID)) as "平均销量"
FROM movies a
JOIN boxoffice b
ON a.id=b.movie_id
GROUP BY a.director
HAVING count(id)> 1
ORDER BY "平均销量" desc
LIMIT 1
SELECT title as "电影" ,
(SELECT max(international_sales+domestic_sales) FROM boxoffice)
-(international_sales+domestic_sales) as "差额"
FROM movies
LEFT JOIN boxoffice
ON movies.id=boxoffice.movie_id
