{"action":"created","issue":{"url":"https://api.github.com/repos/JackieMium/Blog/issues/17","repository_url":"https://api.github.com/repos/JackieMium/Blog","labels_url":"https://api.github.com/repos/JackieMium/Blog/issues/17/labels{/name}","comments_url":"https://api.github.com/repos/JackieMium/Blog/issues/17/comments","events_url":"https://api.github.com/repos/JackieMium/Blog/issues/17/events","html_url":"https://github.com/JackieMium/Blog/issues/17","id":339423835,"node_id":"MDU6SXNzdWUzMzk0MjM4MzU=","number":17,"title":"PostgreSQL 入门","user":{"login":"JackieMium","id":12944832,"node_id":"MDQ6VXNlcjEyOTQ0ODMy","avatar_url":"https://avatars3.githubusercontent.com/u/12944832?v=4","gravatar_id":"","url":"https://api.github.com/users/JackieMium","html_url":"https://github.com/JackieMium","followers_url":"https://api.github.com/users/JackieMium/followers","following_url":"https://api.github.com/users/JackieMium/following{/other_user}","gists_url":"https://api.github.com/users/JackieMium/gists{/gist_id}","starred_url":"https://api.github.com/users/JackieMium/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JackieMium/subscriptions","organizations_url":"https://api.github.com/users/JackieMium/orgs","repos_url":"https://api.github.com/users/JackieMium/repos","events_url":"https://api.github.com/users/JackieMium/events{/privacy}","received_events_url":"https://api.github.com/users/JackieMium/received_events","type":"User","site_admin":false},"labels":[{"id":860416973,"node_id":"MDU6TGFiZWw4NjA0MTY5NzM=","url":"https://api.github.com/repos/JackieMium/Blog/labels/Code","name":"Code","color":"e041c3","default":false},{"id":860415132,"node_id":"MDU6TGFiZWw4NjA0MTUxMzI=","url":"https://api.github.com/repos/JackieMium/Blog/labels/%E5%9F%BA%E7%A1%80","name":"基础","color":"000000","default":false},{"id":970799378,"node_id":"MDU6TGFiZWw5NzA3OTkzNzg=","url":"https://api.github.com/repos/JackieMium/Blog/labels/%E6%95%B0%E6%8D%AE%E5%BA%93","name":"数据库","color":"defc85","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-07-09T12:12:49Z","updated_at":"2018-07-10T07:57:34Z","closed_at":null,"author_association":"OWNER","body":"接触 MIMIC 数据库一小阵，勉强一边 Google 一边看 `mimic-code` 提供的脚本搞定了本地数据库并且把所有提供的 concepts 都建立好了。\r\n\r\n过程中 R 配合 `RPostgreSQL`来连接和操作数据已经相对很容易了，然后还 `tidyverse` 强大的管道 + 数据清洗功能，但是每每涉及到要去看数据 `mimic-code` 没有提供的数据的时候都对数据库操作力不从心。\r\n\r\n所以说落下的课终究是要补的，天道好轮回，苍天饶过谁。\r\n\r\n## 1. 一些基础概念\r\n\r\n`postgreSQL` ，或者说数据库，有个很重要的概念就是 Schema（模式）和 View（视图）、Materialized View（物化视图）。\r\n\r\n### Schema，模式\r\n\r\nSchema 类似与分组，它可以将数据库对象组织到一起形成逻辑组，方便管理。\r\n\r\n我们在 postgreSQL 数据库中创建的任何对象(表、索引、视图和物化视图)都会在一个模式下被创建。如果未指定模式，这些对象将会在默认的模式下被创建。这个模式叫做 `public`。每一个数据库在创建的时候就会有一个这样的模式。\r\n\r\n创建一个新的 schema 就是 `CREATE SCHEMA my_schema;`，要在这个指定的 schema 里建立表格：\r\n```sql\r\nCREATE TABLE my_schema.mytable (\r\n...\r\n);\r\n```\r\n删除一个空 schema 是 `DROP SCHEMA my_schema;`，如果不是空的就得 `DROP SCHEMA myschema CASCADE;` 了。\r\n\r\n假如我们进入一个数据库并执行一个命令操作一个叫 my_table 的表格的时候，默认情况下数据库会在 `public` 这个模式中找，找不到就报错，哪怕这个 my_table 本身在另一个模式（比如 my_schema）里已经存在。这个时候我们就要设置搜索路径了：\r\n```sql\r\nSET search_path TO my_schema, public;\r\n```\r\n这样就把 `my_schema` 放到了搜索路径里 `public` 的前面。这个有点像 Linux 的用户 PATH 这个环境变量。设置了这个之后我们在建立数据库不指定模式的建立对象时默认都会放到 `my_schema`。但是需要注意，`SET search_path` 这个设置不是永久的，只在当前会话有效。这有点像 Linux 下终端里 export 一个变量，关掉终端之后就没了。\r\n\r\n\r\n### View & Materialized View，视图与物化视图\r\n\r\n视图和物化视图就没那么好解释了，我 Google 了一下找到这个博客我觉得比较好理解：[It's a view, it's a table... no, it's a materialized view!](https://www.compose.com/articles/its-a-view-its-a-table-no-its-a-materialized-view/)，节选下重点 :\r\n\r\n> Let's start with TABLE – it's basically an organized storage for your data - columns and rows. You can easily query the TABLE using predicates on the columns. To simplify your queries or maybe to apply different security mechanisms on data being accessed you can use VIEWs – named queries – think of them as glasses through which you can look at your data.\r\n>\r\n>**So if TABLE is storage, a VIEW is just a way of looking at it, a projection of the storage you might say. When you query a TABLE, you fetch its data directly. On the other hand, when you query a VIEW, you are basically querying another query that is stored in the VIEW's definition**. But the query planner is aware of that and can (and usually does) apply some \"magic\" to merge the two together.\r\n>\r\n> Between the two there is MATERIALIZED VIEW - it's a VIEW that has a query in its definition and uses this query to fetch the data directly from the storage, but it also has it's own storage that basically acts as a cache in between the underlying TABLE(s) and the queries operating on the MATERIALIZED VIEW. It can be refreshed, just like an invalidated cache - a process that would cause its definition's query to be executed again against the actual data. It can also be truncated, but then it wouldn't behave like a TABLE nor a VIEW. It's worth noting that this dual nature has some interesting consequences; unlike simple \"nominal\" VIEWs their MATERIALIZED cousins are \"real\", meaning you can - for example - create indices on them. On the other hand, you should also take care of removing bloat from them.\r\n\r\n视图的本质是查询语句，而不是实在的表格。而物化视图的介于两者之间.....好吧，物化视图的解释没有很看懂。\r\n我这个需求大概应该懂就行了吧。实际上在 mimic-code 提供的代码里基本上也都是在导入的原始数据上建立物化视图的。\r\n\r\n因为视图和物化视图都是建立在查询上的，所以在创建时也就必须得有查询语句：\r\n```sql\r\nCREATE [MATERIALIZED] VIEW view_name AS\r\n\tSELECT column1, column2..... FROM table_name\r\n\t\tWHERE [condition]; \r\n```\r\n删除视图类似删除表 `DROP VIEW IF EXISTS view_name;`，非空视图要`DROP VIEW IF EXISTS view_name CASCADE;`\r\n\r\n\r\n## 2. PostgreSQL 中的数据类型\r\n\r\n参考这一篇介绍 PostgreSQL 数据类型的博文：[PostgreSQL数据类型](https://www.yiibai.com/postgresql/postgresql-datatypes.html)。\r\n\r\n数据类型指定要在表格中每一列存储哪种类型的数据。\r\n创建表格时每列都必须使用数据类型。PotgreSQL中主要有三种数据类型：\r\n\r\n- 数值数据类型\r\n- 字符串数据类型\r\n- 日期/时间数据类型\r\n\r\n\r\n### 数值\r\n\r\n常见数值类型包括：\r\n\r\n- smallint：小范围整数；\r\n- integer：典型的整数类型；\r\n- bigint：可以存储大范围整数；\r\n- decimal，numeric：指定的精度的数字，精确数字；\r\n- real，double：可变精度数字，前者精度为 6 位，后者 15 位；\r\n\r\n\r\n### 字符串\r\n\r\n字符串类型包括\r\n\r\n- char(size)，character(size)：固定长度字符串，size 规定了需存储的字符数，由右边的空格补齐；\r\n- varchar(size)，character varying(size)：可变长度字符串，size 规定了需存储的字符数；\r\n- text：可变长度字符串。\r\n\r\n### 日期/时间\r\n\r\n表示日期或时间的数据类型有：\r\n\r\n- timestamp：日期和时间，有或无时区；\r\n- date：日期，无时间；\r\n- time：时间，有或无时区；\r\n- interval：时间间隔。\r\n\r\n\r\n### 其他\r\n\r\n其他数据类型类型还有布尔值 boolean （true 或 false），货币数额 money 和 几何数据等。\r\n\r\n\r\n\r\n## 3. 入门命令\r\n\r\n标准进入数据库的命令是 ` psql -U USER -d DB -h HOST -p PORT  ` ，这样会要求密码然后进入 DB 数据库。但是我数据库是本地用，而且用户也添加到了数据库超级用户了，所以用户、主机、端口都可以省掉了。最后就是直接 `psql -d DB` 甚至 `psql DB`就行了。 \r\n\r\n极其常用命令列表一下：\r\n\r\n|命令|功能|命令|功能|\r\n|----|----|-----|----|\r\n|\t\t`\\?`\t\t|\t命令列表|`\\h cmd`|\t获取命令解释\t |\r\n|`\\l`\t|列举所有数据库 |\t`\\c\tdb`\t|\t连接到另一数据库\t|\r\n|`\\d`\t|\t列举当前数据库的所有对象|`\\d+`\t|\t列举当前数据库的所有对象及其额外信息\t|\r\n|\t`\\d table` |\t列出表格的元数据 | `\\du` |\t列出所有用户 |\r\n|\t`\\dn` |\t列出所有 schema | `\\e` |\t编辑器 |\r\n|`\\r`\t|\t重置当前的 query\t|\t`\\i file` |\t执行文件 |\r\n\r\n\r\n### 基本表格操作\r\n\r\n创建一个含 name 和 date 这样两列的表格，而且指定了列的数据类型：\r\n```sql\r\nCREATE TABLE tab (name VARCHAR, date DATE);\r\n```\r\n在上述表格中插入一行数据：\r\n```sql\r\nINSERT INTO tab (name, date) VALUES ('Jackie', '20180708');\r\n```\r\n改动：\r\n```sql\r\nUPDATE tab SET name='Jack' WHERE name='Jackie';\r\n```\r\n删除行：\r\n```sql\r\nDELETE FROM tab WHERE name='Jack';\r\n```\r\n添加列：\r\n```sql\r\nALTER TABLE tab ADD email VARCHAR;\r\n```\r\n更改列名：\r\n```sql\r\nALTER TABLE tab RENAME COLUMN date TO date_add;\r\n```\r\n删除列：\r\n```sql\r\nALTER TABLE tab DROP COLUMN email;\r\n```\r\n更改表格名：\r\n```sql\r\nALTER TABLE tab RENAME TO my_tab;\r\n```\r\n删除表格：\r\n```sql\r\nDROP TABLE IF EXISTS my_tab;\r\n```"},"comment":{"url":"https://api.github.com/repos/JackieMium/Blog/issues/comments/403734729","html_url":"https://github.com/JackieMium/Blog/issues/17#issuecomment-403734729","issue_url":"https://api.github.com/repos/JackieMium/Blog/issues/17","id":403734729,"node_id":"MDEyOklzc3VlQ29tbWVudDQwMzczNDcyOQ==","user":{"login":"JackieMium","id":12944832,"node_id":"MDQ6VXNlcjEyOTQ0ODMy","avatar_url":"https://avatars3.githubusercontent.com/u/12944832?v=4","gravatar_id":"","url":"https://api.github.com/users/JackieMium","html_url":"https://github.com/JackieMium","followers_url":"https://api.github.com/users/JackieMium/followers","following_url":"https://api.github.com/users/JackieMium/following{/other_user}","gists_url":"https://api.github.com/users/JackieMium/gists{/gist_id}","starred_url":"https://api.github.com/users/JackieMium/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JackieMium/subscriptions","organizations_url":"https://api.github.com/users/JackieMium/orgs","repos_url":"https://api.github.com/users/JackieMium/repos","events_url":"https://api.github.com/users/JackieMium/events{/privacy}","received_events_url":"https://api.github.com/users/JackieMium/received_events","type":"User","site_admin":false},"created_at":"2018-07-10T07:57:33Z","updated_at":"2018-07-10T07:57:33Z","author_association":"OWNER","body":"看[文档](https://www.postgresql.org/docs/10/static/tutorial-views.html)算是对视图解释很直观了：\r\n>  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.\r\n>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.\r\n>\r\n>Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.\r\n\r\n视图好比我们对需要经常使用，而又不想每次都一次次执行的查询取了个名字，这样以后再需要执行这个查询的时候我们需要做的就跟使用表格一样简单了。"}}
{"actor":{"display_login":"JackieMium"}}