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);
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)
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
其他:
Graph: neo4j 图形数据库
neo4j 用节点和边来储存数据
Key-value
Redis 可说是一种小型数据结构瑞士刀,作为搭配用的数据库来使用。我们在百宝箱用 sidekiq 实作异步时看过它。