Open JackieMium opened 6 years ago
看文档算是对视图解释很直观了:
Suppose the combined listing of weather records and city location is of particular interest to your application, but you do not want to type the query each time you need it. You can create a view over the query, which gives a name to the query that you can refer to like an ordinary table. Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.
Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.
视图好比我们对需要经常使用,而又不想每次都一次次执行的查询取了个名字,这样以后再需要执行这个查询的时候我们需要做的就跟使用表格一样简单了。
接触 MIMIC 数据库一小阵,勉强一边 Google 一边看
mimic-code
提供的脚本搞定了本地数据库并且把所有提供的 concepts 都建立好了。过程中 R 配合
RPostgreSQL
来连接和操作数据已经相对很容易了,然后还tidyverse
强大的管道 + 数据清洗功能,但是每每涉及到要去看数据mimic-code
没有提供的数据的时候都对数据库操作力不从心。所以说落下的课终究是要补的,天道好轮回,苍天饶过谁。
1. 一些基础概念
postgreSQL
,或者说数据库,有个很重要的概念就是 Schema(模式)和 View(视图)、Materialized View(物化视图)。Schema,模式
Schema 类似与分组,它可以将数据库对象组织到一起形成逻辑组,方便管理。
我们在 postgreSQL 数据库中创建的任何对象(表、索引、视图和物化视图)都会在一个模式下被创建。如果未指定模式,这些对象将会在默认的模式下被创建。这个模式叫做
public
。每一个数据库在创建的时候就会有一个这样的模式。创建一个新的 schema 就是
CREATE SCHEMA my_schema;
,要在这个指定的 schema 里建立表格:删除一个空 schema 是
DROP SCHEMA my_schema;
,如果不是空的就得DROP SCHEMA myschema CASCADE;
了。假如我们进入一个数据库并执行一个命令操作一个叫 my_table 的表格的时候,默认情况下数据库会在
public
这个模式中找,找不到就报错,哪怕这个 my_table 本身在另一个模式(比如 my_schema)里已经存在。这个时候我们就要设置搜索路径了:这样就把
my_schema
放到了搜索路径里public
的前面。这个有点像 Linux 的用户 PATH 这个环境变量。设置了这个之后我们在建立数据库不指定模式的建立对象时默认都会放到my_schema
。但是需要注意,SET search_path
这个设置不是永久的,只在当前会话有效。这有点像 Linux 下终端里 export 一个变量,关掉终端之后就没了。View & Materialized View,视图与物化视图
视图和物化视图就没那么好解释了,我 Google 了一下找到这个博客我觉得比较好理解:It's a view, it's a table... no, it's a materialized view!,节选下重点 :
视图的本质是查询语句,而不是实在的表格。而物化视图的介于两者之间.....好吧,物化视图的解释没有很看懂。 我这个需求大概应该懂就行了吧。实际上在 mimic-code 提供的代码里基本上也都是在导入的原始数据上建立物化视图的。
因为视图和物化视图都是建立在查询上的,所以在创建时也就必须得有查询语句:
删除视图类似删除表
DROP VIEW IF EXISTS view_name;
,非空视图要DROP VIEW IF EXISTS view_name CASCADE;
2. PostgreSQL 中的数据类型
参考这一篇介绍 PostgreSQL 数据类型的博文:PostgreSQL数据类型。
数据类型指定要在表格中每一列存储哪种类型的数据。 创建表格时每列都必须使用数据类型。PotgreSQL中主要有三种数据类型:
数值
常见数值类型包括:
字符串
字符串类型包括
日期/时间
表示日期或时间的数据类型有:
其他
其他数据类型类型还有布尔值 boolean (true 或 false),货币数额 money 和 几何数据等。
3. 入门命令
标准进入数据库的命令是
psql -U USER -d DB -h HOST -p PORT
,这样会要求密码然后进入 DB 数据库。但是我数据库是本地用,而且用户也添加到了数据库超级用户了,所以用户、主机、端口都可以省掉了。最后就是直接psql -d DB
甚至psql DB
就行了。极其常用命令列表一下:
\?
\h cmd
\l
\c db
\d
\d+
\d table
\du
\dn
\e
\r
\i file
基本表格操作
创建一个含 name 和 date 这样两列的表格,而且指定了列的数据类型:
在上述表格中插入一行数据:
改动:
删除行:
添加列:
更改列名:
删除列:
更改表格名:
删除表格: