SQL(Structured Query Language)

 

1. SQL 操作

SQL, Structured Query Language, 结构化查询语言，用来查询关系数据库中的数据。

SELECT * FROM celebs;

 

关系型数据库由许多表组成，一张表由行和列组成。

一列通过表示数据的某个属性。

一个单独的行表示一组信息的观察值。

例如有如下表celebs

id	name	age
1	张三	30
2	李四	25
3	王五	32

存储在数据库中的信息是有类型的：

• 整形(INTEGER)
• 文本(TEXT)
• 日期(DATA)， 格式为YYYY-MM-DD
• REAL(实数), 存储小数类型

 

SQL 语句是数据库可以识别的语句，是人与数据数据进行交互的语言，末尾以分号结束。

例如，如下格式

CREATE TABLE table_name (
   column_1 data_type, 
   column_2 data_type, 
   column_3 data_type
);

 

CREATE TABLE 是一个命令，表示执行特定的数据操作，为了便于识别，命令一般大写。

table_name 表示创建的表的名称。

排号中的语句是参数信息，使用逗号分开

 

1.1 CREATE 命令

CREATE 命令可以让数据库创建新的表。例如，如下语句

CREATE TABLE celebs (
   id INTEGER, 
   name TEXT, 
   age INTEGER
);

 

1.2 INSERT 插入

INSERT 命令可以在表中插入一行数据

INSERT INTO celebs (id, name, age) 
VALUES (1, '张三', 22);

 

INSERT INTO 是命令，在表中插入一行值

celebs 是表名

(id, name, age) 是插入表中的属性

VALUES 是插入表中的属性值

 

1.3 SELECT 选择

SELECT 语言是从数据库中选择数据。

以下命令返回celebs表中name列中所有数据

SELECT name FROM celebs;

 

• SELECT 是关键字
• name 表示列，即数据的属性值
• FROM 是关键字
• celebs 是表名

 

也可以使用以下命令查询表中所有数据

SELECT * FROM celebs；

* 表示通配符，即表示表中所有的列。

 

1.4 ALTER 修改表

ALTER TABLE 命令可以增加一列至当前表中。

以下命令增加一列tel在celebs表中

ALTER TABLE celebs 
ADD COLUMN tel TEXT;

如果在插入时，示指定值，则默认是NULL。

 

 

1.5 UPDATE 更新

UPDATE 可以更新表格中的某行数据。

以下语句更新celebs 表中 tel 中的值为 13688888888

UPDATE celebs
SET tel = "13688888888"
WHERE id = 3

 

 

1.6 DELETE 删除

DELETE 可以删除表表格中的一行或多行数据。

DELETE FROM celebs
WHERE tel IS NULL;

 

 

1.7 Constraints 限定

Constraints可以为列限制约束条件，可以优化表格中的数据，使数据看起来整齐。

CREATE TABLE celebs (
   id INTEGER PRIMARY KEY, 
   name TEXT UNIQUE,
   date_of_birth TEXT NOT NULL,
   date_of_death TEXT DEFAULT 'Not Applicable'
);

 

 

PRIMARY KEY (主键) 列表示可以唯一标识一行数据，如果插入相同id数据，则会报错。

UNIQUE（唯一）表示该列数据在数据表中唯一

NOT NULL 表示该列必须有数据

DEFAULT 表示如果没有值，则使用该默认值。

 

 

2. 查询

当一张数据库表很大时，我们可以使用不同的查询命令获得不同的结果。来了解我们的数据。

 

2.1 查询所有数据

SELECT * FROM movies;

 

 

 

2.2 查询单独某列的数据

SELECT column1, column2 
FROM table_name;

 

为了便于阅读，将FROM入到下一行中。

 

 

2.3 AS 关键词

假设有如下SQL语句

SELECT name AS 'Titles'
FROM movies;

 

AS 的作用是将搜索出来的结果集的列名使用Titles替换掉。

 

有两点要说明：

第一，AS后面的名称最好使用单引号引起来，以提示注意

第二，AS只作用于搜索出来的结果，不会对原始表格列名作修改。

 

 

2.4 Distinct 关键词

当我们要检查一个表中的数据时，有时只想知道表格中有什么数据，不想要看重复的数据。

例如如下SQL 查询命令

SELECT age 
FROM user;

 

 

返回结果：

22

25

27

30

27

25

 

如果想要去除重复的数据，可以使用如下命令

SELECT DISTINCT age 
FROM user;

 

 

返回结果

22

25

27

30

 

 

2.5 Where 关键词

假设从用户表中，我只想要知道年龄大于25的人

SELECT *
FROM users
WHERE age > 25;

 

以上的命令搜索结果输出所有年龄大于25的人。

 

在上面的 where 后面是一个表达式语句，WHERE 只返回是TRUE的真。

SQL 中有如下查询条件

• =  等于
• != 不等于
• > 大于
• < 小于
• >= 大于或等于
• <= 小于或等于

 

2.6 LIKE 关键词

LIKE 也是一个操作运算符，当你想要查看相似的结果时，可以使用该关键词

假设用户的姓名是中有王阿美，我想知道表中有多少姓王且以美结尾的用户

SELECT * 
FROM users
WHERE name LIKE '王_美';

 

本例中使用下划线来匹配以王开头且以美结尾的用户名

 

如果我只想知道以姓王的开头，则可以使用 %

SELECT * 
FROM users
WHERE name LIKE '王%';

 

注意

‘王%’ 表示是以王开头的用户名

‘%王’ 表示是以王结尾的用户名

%阿% 表示用户名中只要有阿字的都会被匹配出来

 

% 对大小写不敏感，如果是英文字符，Zhang 与 zhang 都会被匹配住。

 

2.7 IS NULL

在数据表中， 有时，一些值是NULL。

 

我们可以使用如下操作符去判断是否为NULL

IS NULL

IS NOT NULL

 

比如查看用户表中age参数不为空的数据

SELECT name
FROM users 
WHERE age IS NOT NULL;

 

 

2.8 BETWEEN

Between 也是一个操作符，用来获取一段日期、数据或者文本。

 

比如想查询用户表中年龄在20到29之间的人

SELECT *
FROM users
WHERE age BETWEEN 20 AND 29;

 

 

当使用英文查询时，过滤顺序则以字母形式，比如获取A 到 J 中的字母

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

 

 

 

2.9 AND 关键词

有时，查询的语句不止一条，可以使用AND进行连接

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

 

 

第一条 , 是 年份在 1990 到 1999 年之间

第二条， 是genre 表示该值为 romance

AND 表示且的关系。

 

2.10 OR 关键词

与AND使用方法相同，但是OR表示或的关系 。

 

 

2.11 ORDER BY 关键词

ORDER BY 表示对输出的结果进行排列。 有升序 与 逆序的区别，默认是升序，如果使用DESC则表示降序。

SELECT *
FROM users
ORDER BY age;

 

以上是对年龄进行升序排列。

 

降序的写法如下

SELECT *
FROM movies
WHERE imdb_rating > 8
ORDER BY year DESC;

 

 

如果是写成升序的，则ASC

SELECT *
FROM users
ORDER BY age ASC;

 

 

 

2.12 LIMIT 关键词

如果获取列表中的数据太多，则可以使用LIMIT 关键字进行限制。

SELECT *
FROM users
LIMIT 10;

 

以上命令是仅获取10行数据。

 

 

2.13 CASE 关键词

CASE 类似于 switch-case 语句的功能

 

SELECT name,
 CASE
  WHEN age < 14 THEN 'young'
  WHEN age < 40 THEN 'adult'
  ELSE 'old'
 END
FROM users;

 

 

上述语句有几个注意点：

如果 WHEN 后面的语句是TRUE， 则使用THEN

CASE 与 END 是一对

 

 

 

3. 聚合函数

SQL 语句不仅可以用来在数据库查询数据，也可以用来做些计算。

对于表格中不同的行进行计算，叫做聚合(aggregates)。

以下是聚合的操作的关键命令

• COUNT(): 统计计算出来的有多少行
• SUM(): 对列中所有数据进行求和
• MAX()/MIN(): 最大值与最小值
• AVG(): 列中的均值
• ROUND(): 近似值

 

 

3.1 COUNT 函数

如果想要知道结果数据集中有多少行， 则可以使用COUNT()函数

用法如下

SELECT COUNT(*)
FROM table_name;

 

表示统计所有行数。

 

假设要统计价格为零的总数

SELECT COUNT(*)
FROM fake_apps
WHERE price = 0;

 

 

 

3.2 SUM() 函数

想要获取下载次数的总数

SELECT SUM(downloads)
FROM fake_apps;

 

 

 

3.3 MAX()/MIN() 函数

MAX 与 MIN 返回一列中最大值与最小值。

SELECT MAX(downloads)
FROM fake_apps;

 

获取app下载次数最大值。

 

 

3.4 AVERAGE 均值

SQL 使用AVG()函数快速获取一列的均值。

 

SELECT AVG(downloads)
FROM fake_apps;

 

 

 

3.5 ROUND() 函数

假设数据中的数字有小数值，3.45， 2.67 则可以使用ROUND，只取一位小数

SELECT ROUND(price, 0)
FROM fake_apps;

 

 

 

返回值如下

ROUND(price, 0)

0.0

1.0

0.0

2.0

15.0

0.0

3.0

4.0

15.0

 

 

SELECT ROUND(AVG(price), 2)
FROM fake_apps;

 

还可以嵌入式的用。

 

3.6 GROUP BY 关键字

假设我们要知道每年电影评论的均值

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 1999;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2000;

SELECT AVG(imdb_rating)
FROM movies
WHERE year = 2001;

 

 

我们可以通过GROUP BY 有更好的写法

SELECT year,
   AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

 

 

有时，我们想要根据分类信息获取总数数据。

SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY ROUND(imdb_rating)
ORDER BY ROUND(imdb_rating);

 

以上表示获取 1， 2， 3， 4， 5 每个评分分类阶段的总数。

 

还可以使用数字对每列数据进行查询

• 1 表示第一列
• 2 表示第二列
• 3 表示第三列

 

SELECT ROUND(imdb_rating),
   COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;

 

 

3.7 HAVING 关键词

除了使用GROUP BY 对数据进行分类查看，SQL 还可以让包括或者不包括某些信息

 

SELECT year,
   genre,
   COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

 

 

HAVING 与 WHERE 很像，而且可以使用HAVING替代。但一般遵守如下规定：

当我们对表中的数据量进行过滤时，使用WHERE

当我们想要对表中的分类数据进行过滤时，使用HAVING

 

 

 

4. 多表查询

为了更加有效的存储数据，我们经常将数据存储在不同的表中。

例如，我们有一家杂志公司，用户可以订阅不同的杂志。不同的订阅类型拥有不同的属性与权限。

每个用户又拥有多种关联数据。

 

我们可以用一个表创建如下信息

• order_id
• customer_id
• customer_name
• customer_address
• subscription_id
• subscription_description
• subscription_monthly_price
• subscription_length
• purchase_date

但是，上面有许多重复的信息。如果一个用户拥有多个订阅。用户名称与地址将会重复许多遍。

如果相同的订阅类型由许多客户订阅，则订阅价格与订阅描述信息就会重复放多遍。

 

所以可以将数据分别放在如下三个表中

订单表orders，仅包含订单相关信息：

• order_id
• customer_id
• subscription_id
• purchase_date

 

订阅表subscriptions, 可以包含描述每个订阅类的信息

• subscription_id
• description
• price_per_month
• subscription_length

 

用户表 customers， 可以包含订阅的客户信息

• customer_id
• customer_name
• address

 

 

4.1 JOIN 关键词

JOIN 可以联合多个表中的数据，用法如下:

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

 

上述表示在orders 与 customers 表中查找所有客户id相同的列

 

可以与WHERE联用

SELECT * 
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';

 

 

4.1.1 INNER JOIN

默认情况下，JOIN 是内联的即有如下数据

表1

C1	C2
A	B
Q	W
X	Y

表2

C2	C3
B	C
E	R
Y	Z

则使用JOIN联接C2， 则会是以下结果集

C1	C2	C3
A	B	C
X	Y	Z

 

 

4.1.2 LEFT JOIN

如果我们想要在JOIN的时候，保留左侧的数据，则可以使用LEFT JOIN

表1， 表2 数据集还是如上，如果使用LEFT JOIN 则会有如下结果集

SQL 代码如下

SELECT *
FROM table1
LEFT JOIN table2
  ON table1.c2 = table2.c2;

 

 

结果如下

C1	C2	C3
A	B	C
Q	W
X	Y	Z

该结果集会丢弃表2中不相等的数据

 

4.2 主键与外键

主键表示可以唯一标识表中一行数据的值，例如上面三个表中的order_id， subscription_id， customer_id

 

主键有如下要求：

• 所有值必须为非NULL
• 所有值必须是唯一的.
• 一张表中只有一个列是主键

 

外键表示当一张表中的主键列出现在另一张表中的时候，则叫外键。

 

在使用多表进行查询时，经常会使用外键来匹配。

例如某学校有两张表，classes 与 students

• 课程表包含所有课程信息，主键id.
• 学生表包含所有的学生信息，主键是ID. 他还拥有一个class_id，是外键，与课程表中的id对应

使用INNER JOIN 将上面两个表联接起来

SELECT *
FROM classes
JOIN students
  ON classes.id = students.class_id;

 

 

4.3 CROSS JOIN

有时我们想要知道一个表中一列数据与另一个表中所有数据的组合，就可以使用CROSS JOIN

SELECT shirts.shirt_color,
   pants.pants_color
FROM shirts
CROSS JOIN pants;

 

 

假设shirt_color列中有3个数据，pants_color 中有2个数据，则共有 2* 3 个数据。

 

 

4.4 UNION 关键字

假设表1 有如下

pokemon	type
a	1
b	2
c	3

 

表2 有如下数据

pokemon	type
f	5

 

则使用如下命令可以联合

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

 

 

结果为

pokemon	type
a	1
b	2
c	3
f	5

这个使用情况必须列数相同

 

 

4.5 WITH 关键词

假设市场部门想要知道每个用户订购了多少个杂志产品。

我们首先可以使用如下SQL语句

SELECT customer_id,
   COUNT(subscription_id) AS 'subscriptions'
FROM orders
GROUP BY customer_id;

 

 这个查询可以查出所有订阅的用户id, 但是无法给出所有用户名称。

 

我们可以使用如下格式的结果

WITH previous_results AS (
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM previous_results
JOIN customers
  ON _____ = _____;

 

 

使用上面的格式

WITH previous_query AS (
   SELECT customer_id,
      COUNT(subscription_id) AS 'subscriptions'
   FROM orders
   GROUP BY customer_id
)
SELECT customers.customer_name, 
   previous_query.subscriptions
FROM previous_query
JOIN customers
  ON previous_query.customer_id = customers.customer_id;