SQL 基础学习(2) Joining 和function ， 作业没有做，需要看百宝箱。NOsql的概念

SQL 基础学习(2)

 

Joining 

可以同时关联（joining）多张表进行复杂的查询。

相比于用Rails捞出数据再用Ruby进行过滤组合，使用SQL更加高效，节能。 

以下是 users has_many events的情景，打开DB Browser for SQLite 并新建一个文件demo2.db.

在terminal中执行sqlite3 demo2.db 

 CREATE TABLE events (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT, capacity INTEGER, user_id INTEGER);
CREATE TABLE users (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT);
INSERT INTO users (name) VALUES ('ihower');
INSERT INTO users (name) VALUES ('john');
INSERT INTO users (name) VALUES ('roy');
INSERT INTO events (name, capacity, user_id) VALUES ('rubyconf',100, 1);
INSERT INTO events (name, capacity, user_id) VALUES ('jsconf', 200, 1);
INSERT INTO events (name, capacity, user_id) VALUES ('cssconf', 150, 2);
INSERT INTO events (name, capacity, user_id) VALUES ('htmlconf', 300, NULL);

 跨Tables进行Joining查询，常用的有Inner Joining 和 Left Outer Joining两种：

 

---

1. Inner joining合并两张tables,接不起来就不要：

捞出所有活动和该活动的主办人资料：

Classic写法： 

SELECT * FROM events INNER JOIN users ON events.user_id = users.id;

Old 写法：

SELECT * FROM events, users WHERE events.user_id = users.id;

 对应的Rails语法是 Users.joins(:events); 

"1" "rubyconf" "100" "1" "1" "ihower"

"2" "jsconf" "200" "1" "1" "ihower"

"3" "cssconf" "150" "2" "2" "john" 

---

2. Outer joining合并两张tables，接不起来就填NUll:

捞出所有活动和该活动的主办人资料，包括没有主办人的活动：

SELECT * FROM events LEFT OUTER JOIN users ON events.user_id = users.id;

 left outer join 和 left join 一样。

对应的Rails语法是 Users.left_outer_joins(:events)  //可以查API（看QueryMethods）

"1" "rubyconf" "100" "1" "1" "ihower"

"2" "jsconf" "200" "1" "1" "ihower"

"3" "cssconf" "150" "2" "2" "john"

"4" "htmlconf" "300" NULL  NULL  NULL  

 

---

 

 

AS语法 (alias)

因为在多张tables进行查询时，可能会出现重复的column name,这时在WHERE条件里可能无法判断，而且可以加上别名AS

 

Includes(*args) 

Specify relationships to be included in the result set.

Event.includes(:user) 相当于另一种SQL策略来Outer joining。

---

不同的连接组合：

（维恩图，涉及intersect and except）

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins 

更好的diagram，Join diagrams!

 https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/

 

---

---

---

Functions

数据库提供一些函数可以用在SQL中：

Aggregate: 

 ['æɡrɪɡət; (for v.) æɡrɪˌɡet]

to put different amounts, pieces of information etc together to form a group or a total

 

 计算

---

1.数量

SELECT COUNT(*) AS event_count FROM events;//加上AS别名才比较好识别处理

对应的Rails语法是Event.count 

2.MIN(列), MAX(列)

对应的Rails语法是Event.minimum()和Event.maximum(); 

3. SUM(列) 

对应的Rails语法是Events.sum() 

4.平均有两种 SUM()/COUNT() 或者AVG()

对应的Rails语法是Events.average(列) 

 

 

分类GROUP BY

 

---

 

GROUP BY 分类功能主要是用来搭配上述 aggregating function 来使用的，例如请回答这个问题：计算每个 user 有多少 events? 

SELECT users.name, COUNT(events.id)

  FROM users LEFT JOIN events

  ON users.id = events.user_id

  GROUP BY user_id;

可再加条件和排序: having 和 order by 

SELECT users.name, COUNT(events.id) AS c

  FROM users LEFT JOIN events

  ON users.id = events.user_id

  GROUP BY user_id

  HAVING c > 1

  ORDER BY c DESC;

 

 其中 WHERE 是给 source tables 的条件，HAVING 才是 aggregation 内的条件函数

 

aggregate:  

 ['æɡrɪɡət; (for v.) æɡrɪˌɡet]总计合计

the total after a lot of different figures or points have been added together

---

 

distinct

可以去除重复的数据

 SELECT DISTINCT(user_id) FROM events;

---

 

数据库还有提供其他函数，例如 字串 SQLite - Useful Functions、时间 SQLite - Date And Time Functions等等。

 

wiki百科解释sqlite3:

https://zh.wikipedia.org/wiki/SQLite 

---

 

---

NOsql

非关系型数据库：

---

 

Column-Oriented: 

 关系型数据库的 Transaction 事务的缺点是效能。数据库在做 Transaction 事务时，不可避免地必须锁住一些数据，避免其他人同时修改。因此如果是一个写入流量非常大的网站，就说是一个售票网站好了，非常多人在开票时准时抢票，这时候数据库的效能就会非常差。

 

cap theorem :

它指出对于一个分布式计算系统来说，不可能同时满足以下三点：

• 一致性（Consistence）（等同于所有节点访问同一份最新的数据副本）
• 可用性（Availability [ə,velə'bɪləti])（每次请求都能获取到非错的响应——但是不保证获取的数据为最新数据）
• 分区容错性（Network partitioning）（以实际效果而言，分区相当于对通信的时限要求。系统如果不能在时限内达成数据一致性，就意味着发生了分区的情况，必须就当前操作在C和A之间做出选择。）

 

根据CAP 定理告诉我们: RDBMS 在多服务器(P)架构下为了维持 C 特性，只能牺牲 A、NoSQL 让你有 tradeoff 的空间，牺牲 C 特性以换得 A、但这不是 C 和 A 二元的选择，而是 C 和 delay 延迟时间的取舍，你愿意容忍多少延迟时间才算作不 Available。

 

因此这类型的 NoSQL 不讲 ACID，而是讲 BASE 特性 (Basically Available, Soft state, Eventual consistency最终的一致性)，重点是 Eventual consistency。想像一个场景: Facebook 和 Twitter 贴文，当你贴文成功的时候，并不是当下马上其他人就可以看到你的贴文，这中间其实是有延迟时间的。这个延迟对于关系型数据库来说是不可以接受的，但是对于这种社交应用来说，却没有关系。牺牲 Consistence 一致性，就可以换到更多的写入反应效能，这就是这类型的 NoSQL 的设计目的。

• Apache HBase 分布式、强调超大 Table： billions of rows X millions of columns (PB以上等级) (Google Big Table 的开源版本，由 Yahoo 推出)
• Apache Cassandra 分布式、最终一致性，高写入场景 (Amazon Dynamo 的开源版本，由 Facebook 推出)
• Amazon DynamoDB
• Google Cloud Datastore

    如果你的数据量不到 1PB (=1000TB)，你就不需要考虑这类型的数据库了，用 MySQL 或 PostgreSQL 足矣。

 

---

其他：

Graph: neo4j 图形数据库

neo4j 用节点和边来储存数据

Key-value

Redis 可说是一种小型数据结构瑞士刀，作为搭配用的数据库来使用。我们在百宝箱用 sidekiq 实作异步时看过它。