数据库--SQL语言-1

练习网站：自学SQL网

Select 查询语法复习

SELECT column, another_column, …

FROM mytable

WHERE condition AND/OR another_condition AND/OR …;

操作符号：

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

=	完全等于 eg.col="abc"
！=or<>	不等于
like	没有用通配符等价于 =
not like	没有用通配符等价于 !=
%	通配符，代表匹配0个以上的字符
-	和% 相似，代表1个字符
in	在列表
not in	不在列表

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

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

例如表格：

Table(表）: movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

 

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；

按片长排列，John Lasseter导演的电影

Title

Cars 2

Cars

A Bug's Life

Toy Story 2

Toy Story

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;

查询综合练习：

Table(表）: North_american_cities

City	Country	Population	Latitude	Longitude
Guadalajara	Mexico	1500800	20.659699	-103.349609
Toronto	Canada	2795060	43.653226	-79.383184
Houston	United States	2195914	29.760427	-95.369803
New York	United States	8405837	40.712784	-74.005941
Philadelphia	United States	1553165	39.952584	-75.165222
Havana	Cuba	2106146	23.05407	-82.345189
Mexico City	Mexico	8555500	19.432608	-99.133208
Phoenix	United States	1513367	33.448377	-112.074037
Los Angeles	United States	3884307	34.052234	-118.243685
Ecatepec de Morelos	Mexico	1742000	19.601841	-99.050674
Montreal	Canada	1717767	45.501689	-73.567256
Chicago	United States	2718782	41.878114	-87.629798

 

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_cities
    	where 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)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000

 

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_table
    ON 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 等价.

 

LEFT JOIN 

RIGHT JOIN 

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

例题：

Table: Employees (Read-Only)

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4

Table: Buildings (Read-Only)

Building_name	Capacity
1e	24
1w	32
2e	16
2w	20

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 
;