【SQL学习+练习篇】基础训练，适用于初学者（一）

最新推荐文章于 2024-05-26 11:09:35 发布

小慌慌

最新推荐文章于 2024-05-26 11:09:35 发布

阅读量3.4w

点赞数 12

分类专栏：数据库/mysql/oracle 文章标签： sql p2p linq

本文为博主「小慌慌」原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。--by 留白

本文链接：https://blog.csdn.net/whowhowhoisimportant/article/details/121842198

版权

数据库/mysql/oracle 专栏收录该内容

32 篇文章 12 订阅

订阅专栏

SQL简介

SQL, 全称为Structured Query Language（结构化查询语言)。要讲SQL就绕不开database(数据库), 平时所说的数据库，一般就是指的 Relational database(关系型数据库).

大家知道数据库是用来存储大量数据的一种软件，那么SQL呢是用来操作数据里的数据，具体来说SQL可以做数据查询，数据更新，写入数据等等。

初识

这里的电影表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

学习0：简单查询

SELECT 语句, 通常又称为查询（queries), 正如其名, SELECT 可以用来从数据库中取出数据. 一条 SELECT 语句或者叫一个查询, 可以描述我们要从什么表取数据，要取哪些数据，在返回之前怎么对结果做一些转化计算等等. 我们接下来会说明 SELECT 的语法，看 SELECT 是怎么来实现上述的取数据任务的。

你可以把一个表(Table)想象成一个类别的事物，比如狗 (Dogs), 表里的每一行就是一条狗，每一列代表了狗的一种属性，比如：颜色，长度等等）

现在有了这么一张表，最常见的一种查询就是取出表中的一个或某几个属性列（注意：是所有数据的某几个属性列）

Select 查询某些属性列（specific columns）的语法

SELECT column（列名）, another_column, … FROM mytable（表名）;

查询的结果是一个二维的表格，由行（rows）和列（columns）组成, 看起来像是复制了一遍原有的表（Table）,只不过列是我们选定的，而不是所有的列.

如果我们想取出所有列的数据, 当然可以把所有列名写上，不过更简单的方式用星号 (*) 来代表所有列.如下：

Select 查询所有列

SELECT * FROM mytable（表名）;

SELECT * FROM table. 这条语句经常用来在不清楚table(表）中有什么数据时，能取出所有的数据瞜一眼。

练习0：简单查询

1.【简单查询】找到所有电影的名称title

SELECT title FROM movies;

2.【简单查询】找到所有电影的导演

SELECT director FROM movies;

3.【简单查询】找到所有电影的名称和导演

SELECT Title,Director FROM movies;

4.【简单查询】找到所有电影的名称和上映年份

SELECT Title,year FROM movies;

5.【简单查询】找到所有电影的所有信息

SELECT * FROM movies;

6.【简单查询】找到所有电影的名称,Id和播放时长

SELECT Title,id,Length_minutes FROM movies;

学习1：条件查询

我们已经学会了从数据表里取出某几个列的SQL, 思考一下：如果数据表里有100万行数据？真实世界中确实存在，世界上肯定有100万条狗那么多. 如果数据量这么大之前的SQL语句很可能运行时会崩溃（想象一下你一次性下载1T的电影）

真实情况下，我们很少直接查所有行，即使查询出来也看不完。为了更精确的查询出特定数据，我们需要学习一个新的SQL语法：SELECT查询的 WHERE 子句. 一个查询的 WHERE子句用来描述哪些行应该进入结果，具体就是通过 condition条件限定这些行的属性满足某些具体条件。比如：WHERE 体重大于 10KG的狗。你可以把 WHERE想象成一个筛子，每一个特定的筛子都可以筛下某些豆子。

条件查询语法

SELECT column, another_column, … FROM mytable WHERE condition AND/OR another_condition AND/OR …;
注：这里的 condition 都是描述属性列的，具体会在下面的表格体现。

可以用 AND or OR 这两个关键字来组装多个条件（表示并且，或者） (ie. num_wheels >= 4 AND doors <= 2 这个组合表示 num_wheels属性大于等于 4 并且 doors 属性小于等于 2). 下面的具体语法规则，可以用来筛选数字属性列（包括整数，浮点数） :

Operator（关键字） Condition（意思） SQL Example(例子）
=, !=, < <=, >, >= Standard numerical operators 基础的大于，等于等比较 col_name != 4
BETWEEN … AND … Number is within range of two values (inclusive) 在两个数之间 col_name BETWEEN 1.5 AND 10.5
NOT BETWEEN … AND … Number is not within range of two values (inclusive) 不在两个数之间 col_name NOT BETWEEN 1 AND 10
IN (…) Number exists in a list 在一个列表 col_name IN (2, 4, 6)
NOT IN (…) Number does not exist in a list 不在一个列表 col_name NOT IN (1, 3, 5)

越是精确的条件筛选，会让结果更容易理解，同时因为条件在返回之前筛掉不必要的结果，SQL的运行速度也会快很多（想象一下你只是想看下最近有哪些大片上映，你没必要下载整个电影看一遍）.

小贴士?

虽然之前我们的SQL 关键之如 SELECT, WHERE, AND,OR 都是大写的，但SQL其实是兼容写成 select,where小写的. 大写这些关键字有助于我们把关键字和你的表名，列名区分开，让 SQL更容易理解。

练习1：条件查询

1.找到id为6的电影

SELECT *
FROM movies
WHERE id = 6

2.找到在2000-2010年间year上映的电影

SELECT *
FROM movies
WHERE YEAR between 2000 and 2010

3.找到不是在2000-2010年间year上映的电影

SELECT *
FROM movies
where year NOT between 2000 and 2010

4.找到头5部电影

SELECT *
FROM movies
where ID between 1 and 5

5.找到2010（含）年之后的电影里片长小于两个小时的片子

SELECT *
FROM movies
where YEAR >=2010
AND Length_minutes < 120

6. 找到99年和09年的电影,只要列出年份和片长

SELECT YEAR,Length_minutes
FROM movies
where YEAR = 2009 OR YEAR= 1999
          或者
SELECT Length_minutes,YEAR
FROM movies
where YEAR IN (2009,1999)

学习2：操作符

我们已经学会了WHERE 语句来筛选数字类型的属性，如果属性是字符串, 我们会用到字符串相关的一些操作符号，其中 LIKE（模糊查询）和 %（通配符）是新增的两个. 下面这个表格对字符串操作符有详细的描述:

Operator（操作符） Condition（解释） Example（例子）
= Case sensitive exact string comparison (notice the single equals)完全等于 col_name = "abc"
!= or <> Case sensitive exact string inequality comparison 不等于 col_name != "abcd"
LIKE Case insensitive exact string comparison 没有用通配符等价于 = col_name LIKE "ABC"
NOT LIKE Case insensitive exact string inequality comparison 没有用通配符等价于 != col_name NOT LIKE "ABCD"
% Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符 col_name LIKE "%AT%"
(matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符
_ Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符 col_name LIKE "AN_"
(matches "AND", but not "AN")
IN (…) String exists in a list 在列表 col_name IN ("A", "B", "C")
NOT IN (…) String does not exist in a list 不在列表 col_name NOT IN ("D", "E", "F")

小贴士?

在字符串表达式中的字符串需要用引号 " 包含，如果不用引号，SQL会认为是一个属性列的名字，如：col_name = color 表示 col_name和color两个属性一样的行 col_name = "color" 表示 col_name 属性为字符串 "color"的行.

练习2：操作符

字符串操作符

找到所有Toy Story系列电影

SELECT * 
FROM movies
WHERE title LIKE "Toy Story%"

找到所有John Lasseter导演的电影

SELECT * 
FROM movies
WHERE director LIKE "John Lasseter"
--或者
--WHERE director = "John Lasseter"

找到所有不是John Lasseter导演的电影

SELECT * 
FROM movies
WHERE director <> "John Lasseter"
--或者
--WHERE director NOT LIKE  "John Lasseter"

找到所有电影名为 "WALL_" 开头的电影

SELECT * 
FROM movies
WHERE TITLE LIKE "WALL_%"

找到98年电影中文名《虫虫危机》

SELECT *
FROM movies
WHERE YEAR IN (1998)

找出所有Pete导演的电影,只要列出电影名,导演名和年份就可以

SELECT TITLE,DIRECTOR,YEAR
FROM movies
WHERE DIRECTOR LIKE "Pete%"

John Lasseter导演了两个系列，一个Car系列一个Toy Story系列,请帮我列出这John Lasseter导演两个系列千禧年之后(含千禧年)的电影

SELECT * 
FROM movies
WHERE DIRECTOR = "John Lasseter"
AND YEAR > "2000"
AND TITLE LIKE "Toy Story%"
OR  TITLE LIKE "Car%"

学习3：查询结果Filtering过滤和 sorting排序

DISTINCT 语法介绍，我们拿之前的 Movies表来说，可能很多电影都是同一年Year发布的，如果你想要按年份排重，一年只能出现一部电影到结果中，你可以用 DISTINCT 关键字来指定某个或某些属性列唯一返回。写作：DISTINCT Year

选取出唯一的结果的语法

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

因为 DISTINCT 语法会直接删除重复的行, 我们还会学习 GROUP BY 语句， GROUP BY 也会返回唯一的行，不过可以对具有相同的属性值的行做一些统计计算，比如：求和.

结果排序（Ordering results）

在实际的数据表中，数据添加的时候不是完全顺序的，比如我们实际的Dog表，不会是按狗的身高从小到大去添加数据，当数据量成千上万之后，如果结果不做任何排序，结果会看起来很错乱.

为了解决结果排序问题, 我们可以用 ORDER BY col_name 排序的语法来让结果按一个或多个属性列做排序.

结果排序（ordered results）

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

ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序或 DESC 降序，对数字来说就是升序 1，2，3，... 或降序 ... 3,2,1 . 对于文本列，升序和降序指的是按文本的字母序。

通过Limit选取部分结果

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;
你可以想象一下一个新闻网站的新闻条目数据，他们在页面上是按热度和时间排序的，每一个页面只显示10条数据，在所有这些属性都是不断变化的情况下。我们可以想见通过SQL的ORDER LIMIT OFFSET 句法，我们可以根据要求从数据库筛选出需要的新闻条目.

小贴士?

如果你对 LIMIT 和 OFFSET 在SQL中何时执行有疑问，可以说LIMIT和OFFSET一般在SQL的其他部分都执行完之后，再执行。

练习3：查询结果Filtering过滤和 sorting排序

按导演名排重列出所有电影(只显示导演)，并按导演名正序排列

SELECT distinct director
FROM movies
ORDER BY director ASC

列出按上映年份最新上线的4部电影

SELECT distinct *
FROM movies
ORDER BY year DESC
LIMIT 4

按电影名字母序升序排列，列出前5部电影

SELECT distinct *
FROM movies
ORDER BY title asc
LIMIT 5

按电影名字母序升序排列，列出上一题之后的5部电影

SELECT distinct *
FROM movies
ORDER BY title asc
LIMIT 5 offset 5

如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可

SELECT title
FROM movies
where director = "John Lasseter"
ORDER BY Length_minutes DESC
LIMIT 1 OFFSET 2

按导演名字母升序,如果导演名相同按年份降序,取前10部电影

SELECT *
FROM movies
ORDER BY director ASC,year DESC
limit 10

学习4：复习 SELECT 查询

到目前为止，你已经学习基本的SQL查询语法了，是时候综合这些知识，来尝试下能否解决一些实际问题了.

服务SELECT查询语法
SELECT column, another_column, … 
FROM mytable WHERE condition(s) 
ORDER BY column ASC/DESC 
LIMIT num_limit OFFSET num_offset;
练习

正如实际工作中，最大的变化不是SQL语法，而是实际数据的表结构和数据。在本节练习中，我们会放出一个新的表，简单介绍一下这个表，这个表存储了北美一些城市的人口信息和经纬度地理位置信息，信息.

小贴士?

在这个数据表中，你需要熟悉一下latitudes（纬度）和 longitudes（经度）的概念， latitudes在赤道以北是正数，以南是负数；longitudes在子午线东部是正数，以西是负数，在查询中需要注意经纬度和东西南北方向的对应关系。

练习4：复习 SELECT 查询

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信息(包括所有字段)

SELECT *
FROM north_american_cities
where country="Canada"

2.列出所有美国United States的城市按纬度从北到南排序(包括所有字段)

SELECT * 
FROM north_american_cities
WHERE COUNTRY="United States"
ORDER BY Latitude DESC

3.列出所有在Chicago西部的城市，从西到东排序(包括所有字段)

SELECT * 
FROM north_american_cities
WHERE Longitude<-87.629798
ORDER BY Longitude DESC

4.用人口数population排序,列出墨西哥Mexico最大的2个城市(包括所有字段)

SELECT * 
FROM north_american_cities
WHERE COUNTRY="Mexico"
ORDER BY population DESC
LIMIT 2

5.列出美国United States人口3-4位的两个城市和他们的人口(包括所有字段)

SELECT *
FROM north_american_cities
WHERE COUNTRY="United States"
ORDER BY Population
LIMIT 2 OFFSET 2

6.北美所有城市,请按国家名字母序从A-Z再按人口从多到少排列看下前10位的城市(包括所有字段)

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.

用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 想成两个集合的交集。

小贴士?

INNER JOIN 可以简写做 JOIN. 两者是相同的意思，但我们还是会继续写作 INNER JOIN 以便和后面的 LEFT JOIN， RIGHT JOIN等相比较.

练习5：用JOIN进行多表联合查询

Table: Boxoffice

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
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

1.找到所有电影的国内Domestic_sales和国际销售额

SELECT *
FROM movies
join boxoffice
on movies.id=boxoffice.Movie_id
--或者
--where movies.id=boxoffice.Movie_id

2.找到所有国际销售额比国内销售大的电影

SELECT * FROM Movies
JOIN Boxoffice
WHERE Id=Movie_id 
AND Domestic_sales<International_sales

3.找出所有电影按市场占有率rating倒序排列

SELECT * FROM Movies
JOIN Boxoffice
WHERE Id=Movie_id
order by rating desc

4.每部电影按国际销售额比较,排名最靠前的导演是谁,线上销量多少

SELECT director,International_sales FROM Movies
INNER JOIN Boxoffice
WHERE Id=Movie_id
order by International_sales desc
limit 1

学习6：外连接（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
和INNER JOIN 语法几乎是一样的. 我们看看这三个连接方法的工作原理：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

我们还是可以用集合的图示来描述：
LEFT JOIN

RIGHT JOIN 反过来

FULL JOIN

将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。所有在用这三个JOIN时，你需要单独处理 NULL. 关于 NULL 下一节会做更详细的说明

小贴士?

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

练习6：外连接（OUTER JOINs）

Table: Employees

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
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

Table: Buildings

1.找到所有有雇员的办公室(buildings)名字

SELECT distinct Building FROM employees
join Buildings
on Building_name=Building
order by Building;

或者

SELECT distinct building 
FROM employees
where building is not null

3.找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

select distinct building_name,role 
from Buildings a 
left join employees b 
on a.building_name =b.building

4.找到所有有雇员的办公室(buildings)和对应的容量

select distinct building_name,Capacity 
from employees a 
left join buildings b 
on a.building =b.Building_name
where a.building is not null

学习练习网站参考：

自学SQL网(教程视频练习全套)

2021-12-09学习回顾篇，随手记录！

小慌慌

关注

12
点赞
踩
69

收藏

觉得还不错? 一键收藏
打赏
2
评论
【SQL学习+练习篇】基础训练，适用于初学者（一）

SQL简介SQL, 全称为Structured Query Language（结构化查询语言)。要讲SQL就绕不开database(数据库), 平时所说的数据库，一般就是指的Relational database(关系型数据库).大家知道数据库是用来存储大量数据的一种软件，那么SQL呢是用来操作数据里的数据，具体来说SQL可以做数据查询，数据更新，写入数据等等。初识这里的电影表Movies后续会一直用到--电影表Id Title Director ...
复制链接

扫一扫

专栏目录