SQL学习

前言

SQL, 全称为Structured Query Language（结构化查询语言)

数据库，一般就是指的 Relational database(关系型数据库)，是用来存储大量数据的一种软件

SQL是用来操作数据库里的数据，具体来说SQL可以做数据查询，数据更新，写入数据等等。

关系型数据库

数据库由若干张表(Table)组成，这里说的数据Table很像Excel里的表; 正如Excel里的表格，Table也是由 行(rows)和列(columns)组成

一个Table存储一个类别的数据，每一行是一条数据，每一列是这种数据的一个属性； Table就像一个二维的表格，列(columns)是有限固定的，行(rows)是无限不固定的，如：

试水

 

 

 1、movies为表名，选取某列全部内容

SELECT title FROM movies

2、按Id（某列的值选取行）

SELECT title, director FROM movies WHERE Id < 5

3、计算行数

SELECT count(*) FROM movies

 

语法指令

1、查询语句-SELECT

Select 查询所有列,这条语句经常用来在不清楚table(表）中有什么数据时，能取出所有的数据瞜一眼。
SELECT *
FROM mytable（表名）;

Select 查询某些属性列（specific columns）的语法
SELECT column（列名）, another_column, …
FROM mytable（表名）

2、条件查询-SELECT WHERE

2.1 适用于数字类型的属性

为了更精确的查询出特定数据：SELECT查询的 WHERE 子句. 一个查询的 WHERE子句用来描述哪些行应该进入结果，具体就是通过 condition条件 限定这些行的属性满足某些具体条件

条件查询语法
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

注：这里的 condition 都是描述属性列的，具体会在下面的表格体现。

 

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)

练习：

 

2.1 适用于字符串类型的属性

会用到字符串相关的一些操作符号，其中 LIKE（模糊查询） 和 %（通配符） 是新增的两个

注意：在字符串表达式中的字符串需要用引号 " 包含，如果不用引号，SQL会认为是一个属性列的名字

Select 查询语法复习
SELECT column, another_column, …
FROM mytable
WHERE condition
    AND/OR another_condition
    AND/OR …;

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")

 

练习：

#找到所有Toy Story系列电影

SELECT * FROM movies WHERE title LIKE '%John Lasseter%'

#找到所有John Lasseter导演的电影
SELECT * FROM movies WHERE director = "John Lasseter"

#【复杂条件】找到所有电影名为 "WALL-" 开头的电影

 

3、查询结果Filtering过滤 和 sorting排序

3.1 DISTINCT 语法介绍

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

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

选取出唯一的结果的语法
SELECT DISTINCT column, another_column, … FROM mytable WHERE condition(s);

3.2 结果排序 （Ordering results）

ORDER BY col_name 这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序

结果排序（ordered results）
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;

3.3 通过Limit选取部分结果

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

注意：LIMIT和OFFSET一般在SQL的其他部分都执行完之后，再执行。

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

练习：

#【结果排序】列出按上映年份最新上线的4部电影 
SELECT * FROM movies ORDER BY year DESC LIMIT 4

#【复杂】【结果排序】如果按片长排列，John Lasseter导演导过片长第3长的电影是哪部，列出名字即可 

SELECT title FROM movies 
WHERE director = 'John Lasseter' 
ORDER BY length_minutes DESC 
LIMIT 1 OFFSET 2 

　　

　小总结

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

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

SELECT * FROM North_american_cities
WHERE Longitude < (SELECT Longitude FROM North_american_cities WHERE City='Chicago')
ORDER BY Longitude DESC  #降序排列，大到小，从西到东

　　

 4、用JOINs进行多表联合查询

 一般关系数据表中，都会有一个属性列设置为 主键(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中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）.

　　

 练习：联表】找到所有电影的国内Domestic_sales和国际销售额 

SELECT *
FROM Movies INNER JOIN Boxoffice
ON Movie_id = Id

5、外连接

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里的所有行

如果某一行在另一个表不存在，会用 NULL来填充结果数据。所有在用这三个JOIN时，你需要单独处理 NULL. 关于 NULL 下一节会做更详细的说明　　

例如：Employees 和 Buildings 两个表，这一次我们雇佣了一些雇员但还没有分配办公室.

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

 

SELECT DISTINCT Building FROM Employees
INNER JOIN Buildings on Building=Building_name

SELECT DISTINCT Building FROM Employees
LEFT JOIN Buildings on Building=Building_name
WHERE Building is not null

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

重点在于：所有办公室！！这决定了更倾向于办公室列表
SELECT DISTINCT Building_name, Role FROM Buildings
LEFT JOIN Employees ON Building_name=Building

　　

6、关于特殊关键字 NULLs

有一些情况很难避免 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 …;

例如：重点是保留谁的信息！！！找到还没有雇员的办公室，所以left join偏向于所有办公室

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

SELECT Building_name FROM Buildings 
LEFT JOIN Employees ON Building=Building_name
WHERE Name IS NULL