SQL & ORM notes

mwish

2020-07-17

ORM 和 SQL 备忘

今天被上头派去写 SQL, 发现自己把 SQL 忘光了，把以前做的笔记翻出来看了一遍。

这篇文章写的很丢人，纯当作备忘了…

MySQL & SQLAlchemy: 查询1

概念

基础查询

查询可以从mysql查询出一个或多个列

可以多列，甚至用 * 指定多列查询

用SELECT DISTINCT col FROM table 指定不同的行

用limit指定搜索的结果, 可以LIMIT BEGIN, END也可以 LIMIT NUMS。注意从0行开始。

SQL 形式类似于LIMIT <COUNT> offset

SELECT tablename.columnSELECT后可以跟着表名

排序查询

直接查询返回的是没有排序的结果，建议order_by
(排序被称为是子句 clause)

order_by 可以指定多个列，按顺序排序(like 字符串)。

如 ORDER BY c1 DESC, c2

指定DESC可以反序查询，完成美妙的操作

过滤查询

MYSQL中可以对查询指定条件

具体可以看看这里

WHERE子句
SELECT ... FROM ... WHERE x = a
做相等性测试，这里支持的操作很多
ORDER_BY 在where之后
BETWEEN a AND b可以做范围检查。必须指定低值到高端值。
AND OR 支持多个条件的逻辑运算，可以括号联系起来，在where后指定 NOT否定。关于计算持续可以用括号做自己的一些制定
Like clause Like 可以指定WILECARD(通配符)。%表示任意匹配(任意长度，任意词)，jet%表示jet开头所有，大小写敏感_匹配任意单个字符。
SQL 定义了 substring, upper, concat 之类的算子，可以帮用户完成操作。不过在各种 sql 实现里可能不一样/
REGEX搜索 WHERE COLUMN REGEXP ‘a|b’
IS NULL做空值查询。
IN 操作是做条件匹配，IN (v1, v2, ...) 相当于在里面的都是valid的操作。

实操

1 2	>>> for instance in session.query(User).order_by(User.id): ... print(instance.name, instance.fullname)

query()会创建对象，帮助做查询，用order_by表顺序维持，返回一个tuple

也可以对专门的字段进行查询

1 2	>>> for row in session.query(User, User.name).all(): ... print(row.User, row.name)

结果是显然的

query对象通常返回一个新的query对象，可以用.filter().filter()… 等实现and操作的逻辑

filter的过滤操作可见此中的常用过滤操作(有equal unequal like ilike in isnull 等)

用text对象包装SQL

>>> stmt = text("SELECT name, id, fullname, password "
...             "FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id, User.fullname, User.password)
SQL>>> session.query(User).from_statement(stmt).params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]

text可写入原生的SQL语句查询

返回哪些

有.first() .all()两个常用, 有的 ORM 也定义了 single

.one()返回一个，没有会抛出异常，.one_or_none()没有时会返回无

SQLAlchemy&MySQL备忘：基本操作(CUD)与完整性约束

创建(Create)、更新(Update)与删除(Delete)

Create, read, update and delete 简称CRUD，表示我们常做的操作。

下面来讲插入，这里就不介绍目的了…大家都能理解的，对吧…

经常要做的插入操作

插入完整的行
插入行的一部分(???)
插入多行
插入查询的结果

操作

1	INSERT INTO tables VALUES(...)

INSERT 语句一般不会产生输出，对每个列提供一个值。

INSERT INTO table(c1, c2, ..., cn) VALUES(c1r, c2r, ..., cnr) 更加的安全

给出列名的可改变文本中列的顺序

1	INTO table(...) VALURS (), ()

VALUES 后可跟多个tuple

插入查找的结果

INSERT INTO table ... SELECT cols FROM ...

可以让你插入查找的结果。

更新

结构

表名
更新操作
过滤条件

操作

1
2
3

UPDATE table 
SET c1 = , c2 = ,...
WHERE conds

注意更新如果失败，前面所有操作CANCEL, 若希望继续执行，则用UPDATE IGNORE

删除

操作

1	DELETE table WHERE conds

想删除所有数据用TRUNCATE TABLE

创建表

结构

注意狗屎编码问题，UTF-8天下第一

1
2
3

CREATE TABLE tablename (
    (colname type constraint,)*
)

约束

约束可能要满足参照完整性(referential integrity) 或者单个关系的完整性约束。

约束的类型

注意：约束可以是联合的，在 SQLAlchemy 中设置联合可以参照这里: 比如设置多个primary_key = True即可。

`NOT NULL`

需要指定

`AUTOINCREMENT`

自增

`DEFAULT`

制定默认值

`PRIMARY KEY`

主键

有可能出现联合主键这样的情况,格式如下：

CREATE TABLE student(       
          name VARCHAR(20),
          class VARCHAR(20),
         CONSTRAINT PK_STUD_ID PRIMARY KEY(name,class)       
          )

`UNIQUE`

UNIQUE KEY

`CHECK`

检查

MySQL没有这玩意。应用于关系声明时check(P)指定谓词P，每个被插入的数据都要满足P

CREATE TABLE table (
	attr1 type ...,
	...,
	check (attr1 in ('老司机', '马老爷', '付垃圾', '天阳哥'))
)

check允许对子集加以限制。

`FOREIGN KEY` 外键约束

我们在从表中可以设置对父表的外键

1	CONSTRAINT fk_class_id FOREIGN KEY(classe_id) REFERENCES classes(id)

没有References，默认是PRIMARY KEY

CASCADE(级联)

1
2
3

FOREIGN KEY(student) REFERENCE dorm403
	on delete cascade
	on update cascade,

也可以选择set null, set default。cascade 在从表中设置，表示当你更新或删除主键表时，那么外键表也会跟随一起更新或删除。student对应属性的删除、更新会传播到整个链中。

更新表

希望在表的层面增加操作(针对列)

ALTER TABLE table OPERATIONS是需要用到的语法

一般使用ADD, DROP操作并写上列。

DROP TABLE删除整个表，不仅仅是删除其内容。

表约束的修改

//删除主键约束  
ALTER TABLE 表名 DROP PRIMARY KEY;    
//添加主键  
ALTER TABLE 表名 ADD PRIMARY KEY(列名);    
//修改列为主键
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

删除

https://www.quora.com/What-is-the-main-difference-between-Truncate-Delete-and-Drop-in-a-database

http://blog.csdn.net/hanxuemin12345/article/details/7818662

DELETE TRUNCATE DROP等操作

SQLAlchemy-MySQL-备忘-计算字段和分组查询

MYSQL和理论部分

计算字段

(这里根据我的理解更像对搜索出的数据函数调用)
不同于直接存储的数据

计算字段搜索的结果是按你在计算字段编写的逻辑显示的，比如说你编写的指定是MAN(money), 显示出来就是FXW(-100).

Concat可以在形式上合并多个字段，进行操作
SELECT Concat(col1, '(', cow2, ')') FROM table ORDER BY ...
输出的形式是col1(col2)，同时也可以对选定的算术运算。

实际上说是函数，但是 + - * 都是可以直接写的，函数更多指的是文本处理等方面。

可以使用文本处理函数，比如DATE等(实际上需要日期使用Date，Time指的实际上比Date更细一层)

也有time对象，但是推荐使用日期是必定用Date

汇总数据与aggregate function

有的时候不需要完整数据只需要单行单列的信息，这个时候可以用汇总数据获得

聚集函数

运行在行组上，返回单个值，我们希望能够

获得表中的行数
获得表中行组(按你定义的规则分组)
获得表中一列的统计量

有AVG，SUM，COUNT等函数。对一列操作，也可以对多列的计算字段操作。

注意这些东西一般都会忽略值为null的行

需注意COUNT：

COUNT() 返回行数
`SELECT COUNT() AS cow1 FROM table`
输出table表行数
上面改成COUNT(cow)改成一列的和，忽略null

DISTINCT指定：上述操作是对ALL的指定，也就是对所有非null字段计算

在aggregete中指定DISTINCT对不等值汇总，相当于一个数据子走一趟=>SELECT COUNT(DISTINCT id) from ...这个distinct可能不同。

SUM 可以对多个值计算，指定适当的指定的结果。

1	SELECT COUNT(*) AS all, MIN(prod) as min FROM ...

允许对多个行列进行操作。

分组

希望把大量的，所有的数据分成几个逻辑组，对每个组计算。

分组操作

计算字段很有用，更多在于和分组结合以后。对每个小组进行计算

多个字段的存储很多有相同的vent_id, 统计每个vent_id的数目

1	SELECT vent_id, COUNT(*) from table GROUP BY vent_id

这个操作针对 vent_id 来分组，得到vent_id对应的数目。

关于GROUP BY的坑

可以包含任意数目的 Row，能让分组更加的细致
所有列一起计算。
使用的都是列或者有效的表达式，在SELECT选定表达式(c1 * c2) 在 GROUP BY一样使用同样的expr.
NULL 值被当成一个单独的分组返回
必须在where , order by 之间

过滤

对于这样的分组，我们有的时候需要过滤，此时用HAVING 子句，它的用法同WHERE。

但是WHERE指定的是行，只有HAVING子句相对的指定的是分组，能够让我们对分组进行一定程度的过滤。可以把where当成分组前过滤，事实上where中排除的值不再会出现在分组中。

(Having对分组过滤，例如上一个例子加上HAVING COUNT(*) >= 2)

GROUP BY 只负责分组不负责排序，所以尽量使用之后sort把

HAVING子句经常和SUM等聚集函数结合，又如

1	SELECT val1, SUM(val2val3) AS val23 FROM table ORDER BY val23 HAVING SUM(val2val3) >= 50

过滤相关理论(相当重要！！！)

先用from计算关系
出现where子句，filter 1的结果
group by 进行分组，分出新的组们
having 进行过滤
SELECT 追踪查询

实操

.group_by实现分组查询

from sqlalchemy import func在func中有很多有用的包。比如func.count(), 相当于COUNT()。同理又恨多别的这样的库函数。以下参考SQLAlchemy的文档

Using the Query, we build a statement like this from the inside out. The statement accessor returns a SQL expression representing the statement generated by a particular Query - this is an instance of a select() construct, which are described in SQL Expression Language Tutorial:

>>> from sqlalchemy.sql import func
>>> stmt = session.query(Address.user_id, func.count('*').\
...         label('address_count')).\
...         group_by(Address.user_id).subquery()

SQLAlchemy & MySQL 备忘: 组合查询

组合(Union)查询

目的

执行多个查询，并当成单个查询返回

实际上任何拥有多个WHERE条件的查询都可以当成组合查询

基础

组合查询可以用关键字UNION，来组合多个查询

1
2
3

SELECT c1, c2, c3 FROM table WHERE cond1
UNION
SELECT c1, c2, c3 FROM table WHERE cond2

相当于WHERE cond1 OR cond2

UNION 在每两个相邻的 SELECT 中
每个 SELECT 查询的数据相同
列的数据类型必须是兼容的

重复的行

两个查询的条件都满足却 UNION ，重复的行会被 UNIQUE，但是如果使用UNION ALL, 则不取消重复的行。

排序

再最后一个SELECT后，链式用 ORDER BY排序

组合不同表

实际上我们可以看到，UNION是把搜索的结果做一个交集，同样我们也可使用不同的表，但是需要同样的 schema，例如：

1
2
3

Select Col1, Col2, Col3, Col4, Col5 from Table1
Union
Select Col1, Col2, Col3, Null as Col4, Null as Col5 from Table2

Python:

from sqlalchemy.orm import aliased

class Part(Base):
    __tablename__ = 'part'
    part = Column(String, primary_key=True)
    sub_part = Column(String, primary_key=True)
    quantity = Column(Integer)

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)

incl_alias = aliased(included_parts, name="pr")
parts_alias = aliased(Part, name="p")
included_parts = included_parts.union_all(
    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\
            filter(parts_alias.part==incl_alias.c.sub_part)
    )

q = session.query(
        included_parts.c.sub_part,
        func.sum(included_parts.c.quantity).
            label('total_quantity')
    ).\
    group_by(included_parts.c.sub_part)

其中：

included_parts = session.query(
                Part.sub_part,
                Part.part,
                Part.quantity).\
                    filter(Part.part=="our part").\
                    cte(name="included_parts", recursive=True)

和

included_parts = included_parts.union_all(
    session.query(
        parts_alias.sub_part,
        parts_alias.part,
        parts_alias.quantity).\
            filter(parts_alias.part==incl_alias.c.sub_part)
    )

使用了UNION ALL

当然，这个我觉得很大程度上和使用WHERE + AND/OR 等是等价的。

SQLAlchemy & MySQL 备忘: 关系(relationship)与连接(join)

设计良好，能够不犯太多事的情况下好好添加的数据库称之为可伸缩性好(scale well).

重点

子表类用foreign key引用父表类, 外键定义了两张表的关系。
students = db.relationship('Student', backref='_class', lazy="select") STUDENTS是子对象的列表。

概念

目标：我们希望储存在多个表，相关联的数据能被一个查询找出来。

显然我们已经知道每个表有唯一标识，被称为主键。

外键是一个表中的一列，包含了另一个表单的主键，定义了数据库的表间的关系

联结是一种机制，在一条select语句中处理关联表。它并不在数据库中实际存在，而是一种操作。

等值联结(equijoin)

这里只用到了SELECT FROM WHERE，不过有两张表，并且在where中详细标注了(完全限定列名)。

1	select col from tables where table1 x table2 = ... order by ...

可以成功在两张表中搜索

没有where的话你会很兴奋的看到笛卡尔积。作为保证我们希望所有子句都含有where.

内部联结(Inner Join)

关键字是 INNER JOIN 和 ON。

1	SELECT cols FROM table1 INNER JOIN table2 ON cond

事实上首选是 Inner Join

多表联结

选择的表数目没有限制，但是表的数量越多，数据库查找速度下降越快。

也可以选择select from多个表，然后where处指定条件

在A字段定义B的relationship完成了A有多个B的关系

表的别名

FROM table1 as t1, table2 as t2 允许你在查询中使用别名并不反馈到列中。

自联结

一个表里有所有科目的所有成绩(不要问我为什么这么设计)，我们想找到高等数学挂科的人，看看他的概率统计是不是也挂了.

可以利用表别名, 给同一个表多个别名，完成查询操作。

1	SELECT p1.mathgrade, p2.xxx FROM students as p1, students AS p2 WHERE p1.(cond) AND p2.(cond)

自然联结

目的：至少有一个列(字段)出现在不止一个表中，自然联结让每个列返回一次。事实上我们做的都是自然联结。

R S中，如果R中的一行r和S中的一行s在$R\cap S$ 中的值相等，则将其连接

外部联结

联结把有外键关联的行联结。但是有的时候我们希望统计没关联的行。

有的时候也希望关联没有关联的行，这里口述不是很能说清，大概相当于左／右联结的时候，如果另一侧没有联结的ON ...数据，则为本侧 | null 的形式。

可以瞅一眼这里的DEMO

使用 LEFT OUTER JOIN RIGHT OUTER JOIN 表示这种外部联结。

带聚集函数的联结

目的：希望对联结后得到的那张表进行分组

1	SELECT ..., COUNT(,,,) AS ... FROM table1 INNER JOIN table2 GROUP BY ...;

WORKFLOW：
内联结 – > 分组 – > 筛选出来

SQLAlchemy

relationship第一个字段基于类名，可以与backref等合起来构成反向索引

>>> from sqlalchemy import ForeignKey
>>> from sqlalchemy.orm import relationship

>>> class Address(Base):
...     __tablename__ = 'addresses'
...     id = Column(Integer, primary_key=True)
...     email_address = Column(String, nullable=False)
...     user_id = Column(Integer, ForeignKey('users.id'))
...
...     user = relationship("User", back_populates="addresses")
...
...     def __repr__(self):
...         return "<Address(email_address='%s')>" % self.email_address

>>> User.addresses = relationship(
...     "Address", order_by=Address.id, back_populates="user")

ForeignKey约束指col被约束为其他地方应当存在的值。foreignkey参数指定的事table name

Address.user是多对一关系。以上市制定了back_populates的情况，并且互相标清出了字段的名称，backref只能在子表定义foreign_ky

在一对多关系中，子表ForeignKey应用父表参考字段。

一对一关系

child = relationship("Child", uselist=False, back_populates="parent")
uselist = false，设置一对一关系

也可以
child = relationship("Child", backref=backref("parent", uselist=False))

注意在应用关系的过程中，ORM模型可以通过Father.child表示对应的对象，但是这个对象是靠foreigning key存储在referencing table中的。

因为以上的愿意，所以有lazy = “xxx” 的属性，来表示这个属性是否是

多对多关系

比如人-关注-人
采用一张中间表

SQLAlchemy : 关系操作

join操作作用在query对象上
对关系操作，返回是对象

es

一对多的多，调用字段(对象的字段/属性)查询会返回列表。

join操作

指定了
join指定条件，两表连接查询。

对应了SQL中的联结的关系

以下隐式的指定id为相同 key 来进行查询

>>> session.query(User).join(Address).\
...         filter(Address.email_address=='[email protected]').\
...         all()
[<User(name='jack', fullname='Jack Bean', password='gjffdd')>]

Query.join() knows how to join between User and Address because there’s only one foreign key between them. If there were no foreign keys, or several, Query.join() works better when one of the following forms are used:

query.join(User.addresses)                       
# specify relationship from left to right
query.join(Address, User.addresses)              # same, with explicit target
query.join('addresses')                          # same, using a string

join操作指的是对于两张或者多张表，给定共同的条件并且对于共同的条件来进行查询

因为查询的是join, 所以查询的顺序是(A对象, B对象)

在这里通过对query对象操作来产生新的 query对象，可以指定

明确的条件

1 2	query.join(Address, User.id==Address.user_id) # explicit condition

一个键的关联

1 2	query.join(User.addresses) # pecify relationship from left to right

被关联的字段-关联字段外键
1
query.join(Address, User.addresses)

字段字符串名

1 2	query.join('addresses') # same, using a string

outerjoinquery.outerjoin(User.addresses)

多个entity的返回值确定

query = session.query(User, Address).select_from(Address).join(User)
select_from指定了查询返回值的类型，否则join最左侧的项

SQLAlchemy-MySQL备忘：子查询

#SQLAlchemy-MySQL备忘：子查询和集合

概念

任何SQL语句都是查询，虽然一般查询指的是select

SQL允许创建子查询(subquery)

子查询指的是相当于查询套查询。一般我们是希望处理多个表中的数据（比方说某g开头的老司机想黑我的号，就调用豆瓣 github 的数据库所有邮箱为 [email protected]的号。）

编写时建议向下方这样分成多行。

1
2
3

SELECT col FROM table1 WHERE col2 IN (
    SELECT ...
)

使用注意

1	WHERE v1, v2 IN (SELECT r1, r2 FROM TABLE)

最常见的子查询是在 WHERE 和 IN 中，在限定的tuple（行）中做查询。

SELECT返回的是选出行数据的tuple，需要拥有相同的列

子查询效果不一定比的上联结

子查询需要完全比较列名，因为可能有多义性，比方说一定要执行a.x = b.x

需要对所有成员计算(比方说对条件中的成员用COUNT(*)计算总数目)，可以采用子查询。

集合的比较: some, any, all…

SELECT name 
FROM table
WHERE table.attr1 > SOME (
    SELECT attr2...
    FROM ..
)

从table中找出name，然后使其大于之查询的某一个(只要找到一个即可).

同时，<some, >=some 等比较操作在这里也是受支持的。

有all等操作，表示每一个

空关系查找与判断： exists

首先我们得明确一点，之查询的外界的值可以代入子查询内。使用了外层查询相关的叫相关子查询

找出符合两个条件的

SELECT value1
FROM table as T
WHERE value1 = "" and value2=""
and EXISTS (
    SELECT * FROM table as S
    where T.name = S.name
)

以上找到v1 = v2, 而且满足内部条件的行

再给出一个范例：对于学生选修的课程，看看哪些学生选修的课程包含生物系的课程。

SELECT S.ID, S.name
FROM students AS S  // 学生的表
WHERE NOT EXISTS (
    (SELECT ... )
    EXCEPT
    (SELECT ... )
)

NOT EXISTS (B EXCEPT A) –> 关系A包含关系B，即B - A = 空集, 实际上表示B包含于A。

这个可以用于一定的复杂的逻辑，以上是包含生物系课程，以下的逻辑表示：此人选课包括了生物系的所有课程

SELECT S.ID, S.name
FROM students AS S  // 学生的表
WHERE NOT EXISTS (
    (
        SELECT course_id
        FROM classes JOIN take USING(class_id)
        WHERE 
    )
    EXCEPT
    (SELECT ... )
)

重复行的存在性测试

对“是否存在”这个特征进行判断。
例子：2009年至少开设两次的课程

SELECT course_name, course_id 
FROM courses
WHERE not unique (
    SELECT C1.course_id
    FROM courses as C1,
        courses as C2
    WHERE C1.course_id = C2.course_id
    AND C1.year = 2009
)

from 子句中的子查询

把from的内容当成一个子查询返回的表。

例子：选出工资大于42000的系的成员的平均工资

–> 总感觉跟分组查询没什么很大的区别…

with 子句的使用

功能：定义临时关系，只对这句话有效。和from select子查询是等价的，但是相当于语法糖，能够表现的更加清晰。

（总觉得有点像视图，视图过会儿讲）

SQLAlchemy&MySQL备忘：事务和视图的使用

视图

概念目的

视图相当于把一个SQL的查找等过程存储为一张伪表，它并不实际上创建一张表、不存储数据，而是作为“虚关系”。同时，底层数据发生变化的时候，视图的结果仍然是符合要求的。

希望能够重用SQL语句并且一定程度上能够保护数据。

许多时候用于检索而非更新。

定义与使用

CREATE VIEW DROP VIEW完成视图的创建删除。

SHOW CREATE VIEW查看：

1
2
3

CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor	;

AS查询语句，得到这里的view。

查询到的是视图，可以类似table使用

1 2	SELECT ID FROM faculty WHERE cond

视图这里相当于子查询。

注意，视图不允许使用recursive.

materialized view

物化视图

特定的数据库允许存储视图关系，并且定义的关系改变，这个视图也跟着改变，这样的视图被称为：materialized view。

同时，一般对视图只能进行查询，但是有限的情况下，允许对视图关系进行修改，不同数据库对这个提供了不同的支持。

对视图更新一般是不允许的…但是…有的数据库系统制定了允许更新的关旭，一般来说：

from 自由一个数据库关系
select 只含关系，没有表达式、聚集、DISTINCT等
没有出现在select的属性可以空
无having或group by

SQLAlchemy use

out of the box: 开箱即用的，即已经封装好的，缩写为OOTB

关于read-only un-materialized view, 似乎没有直接的支持，可以看看这里

对于materialized views, 可以参考这里，和这个用PostgreSQL的例子似乎要用到比较多的高级属性？

数据库事务(transaction)

可以设置commit, rollback. 可以设置多个点便于提交/回滚。

事务有着ACID的特性，请看老哥博客

这里暂时简单提一下transaction，以后会详细介绍。