概述

数据库

A Database is a collection of data stored in a format that can easily be accessed.

数据库管理系统

为了管理数据库，我们要使用数据库管理系统（Database Management System，简称 DBMS）。

我们通过电脑连接上 DBMS，然后通过指令来查询或修改数据。DBMS 将执行指令并返回结果。

DBMS 被分为两大类，Relational 和 Non-Relational，后者又称为 NoSQL。

关系型数据库管理系统

在 Relational DBMS（简称 RDBMS） 中，数据被存储在通过某种方式互相联系的表格中。

每个表格存储特定类型对象的数据，比如客户、订单、产品。

SQL（Structured Query Language）是我们用来处理关系数据库的语言。

MySQL 是最出名的 RDBMS，此外还有 SQL Server 和 Oracle 等。

不同的 DBMS 有不同的风格，但它们的实现都类似，且都基于标准 SQL 规范。

非关系型数据库管理系统

在非关系型数据库管理系统中，没有表格或关系，与 RDBMS 非常不同，不予讨论。

基本操作

基本界面功能

左侧的 Administration 栏用于做管理工作，例如启动或停止服务器，导入或导出数据等。

左侧的 Schemas 栏显示了当前数据库服务器中的数据库，默认情况下只有一个数据库系统 sys，它是供 MySQL 内部使用的。

中间的主体是查询编辑（Query Editor）窗口，也就是编写代码的地方。

右侧的窗口先忽视，不用在意。

构建数据库

点击左上角第二个图标“Open a SQL script file”，选择对应建库文件（create-databases），然后运行，刷新左侧的 Schemas 栏，就可以得到对应的数据库。

在每一个数据库中都有以下文件夹：

  1. Tables：这里是存储数据的地方
  2. Views：用来存放虚拟的 table，我们可以把多个 table 中的数据组合起来放入 view 中
  3. Stored Procedures
  4. Functions

后两者都是用来存放查询代码的。

在 Tables 文件夹中，把鼠标移到任意一个 table 上，会在右侧看到三个图标，最右侧的图标允许我们查询这个表的所有内容，中间的图标可以检查和更改每一列的属性。

SELECT

SQL 代码简介

查看左侧的导航面板，没有一个数据库是粗体显示的，这说明我们还没有选中一个数据库，所以首先我们要选中一个数据库：

1
USE sql_store

SQL 不区分大小写，但我们最好还是保持关键字大写的习惯。

执行上面代码后，sql_store 数据库就会被粗体显示。

当然也可以通过鼠标双击来选中数据库。

SELECT 关键字后可以跟上我们想要查询的列名，以逗号分隔，或者用星号来选去所有的列。

FROM 后面跟上要查询的表。

如果有多个查询语句，需要用分号来把它们分隔。

还可以用 WHERE 关键字来对结果进行过滤。

利用 ORDER BY 关键字进行排序，DESC 用来表示倒序排列。

1
2
3
4
5
6
7
USE sql_store;

-- 注释
SELECT *
FROM customers
WHERE points > 1000
ORDER BY points DESC

关键词的顺序很重要，不然会报错。

详细讲解 SELECT

改变 SELECT 关键字后的列名的顺序可以改变结果的顺序。

SELECT 关键字后可以加算术表达式，包括加减乘除模运算（+，-，*，/，%）等。

可以利用 AS 关键字给结果取得的列重命名，如果重命名后的列名中包含空格，则应该用引号包起来，单引号双引号都可以。

为了好的习惯，当处理 String 类型的数据时，无论有没有空格，都最好使用单引号包起来。

1
2
3
4
5
6
7
8
USE sql_store;

SELECT 
	firSt_name AS "First name", 
    last_name AS 'Last name',
    points, 
    (points + 100) * 10 AS discount_factor
FROM customers

查看 customers 表格的所有数据，把第一个客户的 state 从 MA 改为 VA，这样就有两个客户来自同一个 state，然后点击右下角的 Apply，保存修改。

利用 DISTINCT 关键词可以删除结果中的重复项。

1
2
3
4
USE sql_store;

SELECT DISTINCT state
FROM customers

WHERE

用 WHERE 关键字可以对结果进行过滤。

SQL 中的比较运算符有大于（>）、大于等于（>=）、小于（<）、小于等于（<=）、等于（=）、不等于（!= 或 <>）。

查询时忽略大小写。

日期类型的数据也要用单引号包起来，标准化的日期格式如下：

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'

AND, OR, NOT

AND，OR 和 NOT 分别对应与或非，用于组合条件。

使用括号比记优先级更好用。

1
2
3
4
5
USE sql_store;

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price * quantity > 30

IN

IN 可以用来把一个属性和多个值进行比较。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE state NOT IN ('VA', 'FL', 'GA')

BETWEEN

用于判断某个值是否在某个区间内。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

LIKE

用于判断某个值是否与某种模式相符。

利用百分号 % 匹配任意个字符。

利用下划线 _ 匹配单个字符。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE address LIKE '%trail%' OR phone LIKE '%9'

REGEXP

正则表达式与 LIKE 类似，但在处理类似 '%trail%' 的情况时可以忽略掉 %。

正则表达式里还有其他符号，比如 ^ 表示 String 的开头，$ 表示 String 的结尾，| 表示关键词的并联。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|^rose'

假设我们想找到名字里面含有字母 e，并且 e 前面有字母 g 或 i 或 m 的用户，可以使用以下方法：

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'

或者查找名字里面含有字母 e，并且 e 前面有字母 a 到 h 中任意字母的用户，可以用以下方法：

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'

IS NULL

用于查找数据为空的项。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
WHERE phone IS NULL

也可以有 IS NOT NULL。

ORDER BY

用于将结果排序。

用 DESC 可以倒序。

可以跟上多个列名，会依次进行排序。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
ORDER BY state DESC, first_name DESC

还可以使用不在 SELECT 中的列来进行排序，比如说我们只想要用户的名字，但希望结果按照生日排序。

1
2
3
4
5
USE sql_store;

SELECT first_name, last_name
FROM customers
ORDER BY birth_date

或者按照 SELECT 中新造的列（算术表达式或重命名得到的新列），或者算术表达式来排序。

1
2
3
4
5
6
USE sql_store;

SELECT *
FROM order_items
WHERE order_id = 2
ORDER BY quantity * unit_price DESC

LIMIT

用于限制返回结果的数量。

可以设置偏移量，例如下面代码偏移量为 6，返回数为 3，则会返回第 7、8、9 项数据。

1
2
3
4
5
USE sql_store;

SELECT *
FROM customers
LIMIT 6, 3

ORDER BY 和 LIMIT 总是最后的两条语句。

INNER JOIN

这个命令中 INNER 是可选的，所以使用时可以只写 JOIN。

利用一下代码，可以把 orders 和 customers 两张表根据 customer_id 连接起来。

1
2
3
4
5
6
USE sql_store;

SELECT *
FROM orders
JOIN customers 
	ON orders.customer_id = customers.customer_id

需要注意的是，上面代码的结果中 customer_id 这一列会出现两次，因此如果在 SELECT 中选择 customer_id 的话，程序就会报错，因为程序不清楚我们指的是哪一列 customer_id，尽管它们内容相同。因此我们需要在 customer_id 这个列名前加一个前缀指出它来自哪一张表，比如将其改成 orders.customer_id。

为了减少重复或作区别，我们可以在命令中给表起别名，示例如下：

1
2
3
4
5
6
USE sql_store;

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c 
	ON o.customer_id = c.customer_id

Joining across database

假设 sql_store 数据库中没有 products 这张表，而 sql_inventory 还有一张 products 表。

现在我们想把 sql_store 数据库中的 order_items 表与 sql_inventory 中的 products 表相连。

1
2
3
4
5
6
USE sql_store;

SELECT *
FROM order_items o
JOIN sql_inventory.products p 
	ON o.product_id = p.product_id

注意，FROM 后的表应来自 USE 后的数据库，或者给 FROM 后的表加前缀。

Self Joins

我们要把 sql_hr 数据库中的 employees 表与自己相连。

1
2
3
4
5
6
USE sql_hr;

SELECT *
FROM employees e
JOIN employees m 
	ON e.reports_to = m.employee_id

Joining Multiple Tables

下面代码把 orders，customers 和 order_statuses 三张表连接起来。

1
2
3
4
5
6
7
8
USE sql_store;

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

Compound Join Conditions 复合联合

当一张表使用的是复合主键时，可以用 AND 把条件关联起来。

1
2
3
4
5
6
7
USE sql_store;

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

Implicit Join Syntax 隐式联合

接下来的两种代码获得的结果相同。

第一种：

1
2
3
4
5
6
USE sql_store;

SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id

第二种：

1
2
3
4
5
USE sql_store;

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

第二种就是隐式联合。

但我们应尽量不这么做，因为如果忘记了使用 WHERE 语句，就会得出错误结果。

OUTER JOIN

执行以下代码：

1
2
3
4
5
6
7
8
9
10
USE sql_store;

SELECT 
	c.customer_id,
  c.first_name,
  o.order_id
FROM customers c
JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

会发现结果中只有有订单的顾客被打印出来了。

如果在这种情况下想显示所有顾客，就要使用 OUTER JOIN。

OUTER JOIN 有两种，LEFT JOIN 和 RIGHT JOIN。

使用 LEFT JOIN 时，左侧表格（FROM 后的表格，本例中为 customers）中的所有数据，无论是否满足 ON 后的条件，都会被返回。

RIGHT JOIN 则会返回右侧表格中的所有数据。

一般都使用 LEFT JOIN，避免使用 RIGHT JOIN。

Outer Joins Between Multiple Tables

与 INNER JOIN 类似，OUTER JOIN 也可以联合多个表。

1
2
3
4
5
6
7
8
9
10
11
12
13
USE sql_store;

SELECT
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

一般都使用 LEFT JOIN。

Self Outer Joins