Adapted from Chapter 2 on official documentation
1： 基础概念
- 关系型数据库管理系统（Relational Database Management System）RDBMS; Relation 是Table 对应的数学语言
- tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster(集群)
2: 建立新表格
- 在SQL命令中可以自由的使用空白（空格/tab/换行符）
- （--）双划线引入comment
- varchar(80) specify a data type that can store arbitrary character strings up to 80 characters in length
- real type for store single precision floating-point numbers
3： 标准SQL Types
- int
- smallint
- real
- double precision
- char(N)
- varchar(N)
- date
- time
- timestamp
- interval
4: 删除表格
DROP TABLE tablename;
5: 向表格中加入行 INSERT
非数字的值，通常都在‘’ 内。COPY从文本文件中装载大量数据。
COPY weather FROM '/home/user/weather.txt'; (在表和文档之间拷贝数据)
6： 查询一个表 SELECT
AS ---> relabel output column
WHERE ---> specify which rows are wanted
ORDER BY； DISTINCT and etc.
7: 连接两张表
外链接（outer join）: left/right/full 取决于which part of row output you want to keep;
8： 聚集函数 Aggregate Functions (count, sum, avg, max, min)
* aggregate function cannot be used in WHERE clause: solution ---> subquery
GROUP BY: combination
HAVING: 过滤row
9: 更新 Updates; 删除 DELETE
网友评论