数据库--SQL语言-1

练习网站：自学SQL网

Select 查询语法复习

SELECT column, another_column, …FROM mytableWHERE condition AND/OR another_condition AND/OR …;

操作符号：

如果属性是字符串, 我们会用到字符串相关的一些操作符号，其中 LIKE（模糊查询） 和 %（通配符） 需要重点学习。 

注意通配符%、-的使用，前面不适用=，而要使用like

col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符

例如表格：

1. 【复杂条件】找到所有Toy Story系列电影 

   SELECT * FROM movies
   where Title like"Toy Story%";

   这里要使用like而不能使用 =

2. 【复杂条件】找到所有John Lasseter导演的电影
3. 【复杂条件】找到所有不是John Lasseter导演的电影
4. 【复杂条件】找到所有电影名为 "WALL-" 开头的电影
5. 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来

2.
SELECT * FROM movies
where Director="John Lasseter";3.
SELECT * FROM movies
where Director!="John Lasseter";
4.
SELECT * FROM movies
where Title like "WALL-%";
5.
SELECT * FROM movies
where year=1998;

 去重：DISTINCT

 DISTINCT 关键字来指定某个或某些属性列唯一返回，原理：DISTINCT 语法会直接删除重复的行

SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);

结果排序：ORDER BY col_name 

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

其中ASC是升序 ，DESC 降序

选取部分结果：Limit

LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用，当我们对整个结果集排序之后，我们可以 LIMIT来指定只返回多少行结果 ,用 OFFSET来指定从哪一行开始返回。

你可以想象一下从一条长绳子剪下一小段的过程，我们通过 OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度。

SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;

task：

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

1.
SELECT distinct Director FROM movies 
order by Director ASC;
2.
SELECT * FROM movies 
order by Year DESC limit 4;
3.
SELECT * FROM movies 
order by  Title ASC limit 5;
4.
SELECT * FROM movies 
order by  Title ASC  limit 5 offset 5;
5.
SELECT Title FROM movies 
where Director="John Lasseter"
order by  Length_minutes DESC 
limit 1 offset 2;

注意：offset 其实是从零开始排，如第五题出第三个，但需要offset2；

SELECT Title FROM movies 
where Director="John Lasseter"
order by  Length_minutes DESC ;

但若加上最后一句，显示的是 Cars 2

SELECT Title FROM movies 
where Director="John Lasseter"
order by  Length_minutes DESC 
limit 1 offset 0;

查询综合练习：

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

需要注意的地方都#啦

1.
SELECT * FROM north_american_cities
where Country="Canada";
#注意这里Canada是字符串要“ ”2.
SELECT * FROM north_american_cities
where Longitude<(SELECT Longitude  FROM north_american_citieswhere City="Chicago")
order by Longitude ASC
;#嵌套需要括号3.
SELECT * FROM north_american_cities
where Country="Mexico"
order by population DESC
limit 2
;
4.
SELECT * FROM north_american_cities
where Country="United States"
order by population DESC
limit 2 offset 2
;#offset 2

 用JOINs进行多表联合查询：

连接INNER JOIN.：

主键(primary key)： 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复（想象你的身份证号码)。

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同 主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。

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;

INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。

其实就是数据库里面常说的连接啦。

INNER JOIN 可以简写做 JOIN. 两者是相同的意思

例题：

Table: Movies (Read-Only)

Table: Boxoffice (Read-Only)

1. 【联表】找到所有电影的国内Domestic_sales和国际销售额
2. 【联表】找到所有国际销售额比国内销售大的电影
3. 【联表】找出所有电影按市场占有率rating倒序排列

【联表】每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少

1.
SELECT  Domestic_sales,International_sales 
FROM movies
join Boxoffice on movies.id=Boxoffice.Movie_id
;#这个如果过不了得话，把select后面换为*
2.
SELECT  *
FROM movies
join Boxoffice on movies.id=Boxoffice.Movie_id
where Domestic_sales<International_sales 
;
3.
SELECT  *
FROM movies
join Boxoffice on movies.id=Boxoffice.Movie_id
order by Rating desc
;
4.
SELECT Director,International_sales
FROM movies
join Boxoffice on movies.id=Boxoffice.Movie_id
order by International_sales desc
limit 1
;#排名最靠前

外连接（OUTER JOINs）

INNER JOIN 只会保留两个表都存在的数据，意味着一些数据的丢失，在某些场景下会有问题.

于是就有了：左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN. 

#用LEFT/RIGHT/FULL JOINs 做多表查询
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_tableON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

ps：这些Join也可以写作 LEFT OUTER JOIN, RIGHT OUTER JOIN, 或 FULL OUTER JOIN, 和 LEFT JOIN, RIGHT JOIN, and FULL JOIN 等价.

左外链接就是保留左边，右外链接就是保留右边，全链接就是都要 

例题：

Table: Employees (Read-Only)

Table: Buildings (Read-Only)

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

1.
SELECT  distinct Building
FROM   employees
where Years_employed>0
;#千万注意去重！！
2.
SELECT distinct Building_name, Role
FROM   Buildings 
left join employees on Building=Building_name
;#这个着重讲一下
3.
SELECT distinct Building_name, Capacity
FROM  employees 
left join Buildings on Building=Building_name
where Years_employed>0

ps：因为这个练习DB的限制,只可以用 LEFT JOIN来解决问题.

读第二题：找到所有办公室里的所有角色，不难看出我们是需要输出所有办公室的（输出结果应该如下图），所以Buildings (Read-Only)需要全部保存。即Buildings left join employees。

另外唯一输出(DISTINCT) ：如果有【A,B】和【A,C】这两个算是不同的，都需要输出，

即distinct Building_name, Role （类似【 Building_name, Role】）

关于特殊关键字 NULLs：

 在数据库中，NULL表达的是 "无"的概念，或者说没有东西。而某个属性列是 NULL的情况， 这种特殊性会造成编写SQL的复杂性，所以没有必要的情况下，我们应该尽量减少 NULL的使用，让数据中尽可能少出现 NULL的情况。

如果某个字段你没有填写到数据库，很可能就会出现NULL 。所有一个常见的方式就是为字段设置默认值,比如 数字的默认值设置为0，字符串设置为 ""字符串. 但是在一些NULL 表示它本来含义的场景，需要注意是否设置默认值还是保持NULL。 (比如, 当你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算).还有一些情况很难避免 NULL 的出现, 比如之前说的 outer-joining 多表连接，A和B有数据差异时，必须用 NULL 来填充。

NULL的查询：可以用IS NULL和 IS NOT NULL 来选在某个字段是否等于 NULL.

WHERE column IS/IS NOT NULL

例题：（前面的图）

1. 【难题】找到还没有雇员的办公室 ✓

SELECT Building_name
FROM Buildings
left join employees on Building=Building_name
where Name is NULL 
;