pgSQL 1

概念级

数据库基础概述

数据库表实质上就是一系列二维数组的集合：横向的行(记录)和纵向的列(字段、属性)
DDL语言：数据库定义语言，主要包括DROP,CREATE,ALTER等语句操作
DML语言：数据库操作语言，INSERT,UPDATE,DELETE等语句操作
DQL语言：数据库查询语言，SWLECT语句
DCL语言：数据库控制语言，GRANT,REVOKE,COMMIT,ROLLBACK等语句操作

数据类型

数值类型

整数类型
SMALLINT 范围是-32768到32767
INT(INTEGER) 普通大小的整数，范围是-2147483648到2147483647

任意精度浮点数类型：
REAL 6位十进制数字精度
NUMERIC(m,n) 任意精度类型 m代表一个字段所能存储的数值最大长度，n代表精度

NUMERIC(5,2) 存储5位，其中可以保留两位小数。另外，小数点不占有一位。比如123.88可以，但1234.88不行
但是如果插入部分多的在小数部分，那么会四舍五入而不会报错，比如 123.888->123.89

日期与时间类型
TIME:只用于一日内的时间，8字节，比如10:05:01
DATE:只用于日期，4字节，比如1987-04-04
TIMESTAMP:日期和时间，8字节，1987-04-04 10:05:01

字符串类型
CHAR(n)/CHARACTER(n) 固定长度字符串，不足补空白。另外在检索数据的速度上，它比VARCHAR要高
VARCHAR(n)/CHARACTER VARYING(n) 变长字符串，有长度限制(和CHAR相比就是，它并不会用空白去补齐)
TEXT 变长字符串，无长度限制

SQL

我们可以使用SQL：
1.在数据库中创建和删除表
2.检索和更新表中的数据

这些操作分别被称为数据定义语言 Data Definition Language (DDL) statements
以及数据操作语言 Data Manipulation Language(DML) statements

DDL

DDL允许用户定义新表和相关的元素
大多数商业SQL数据库在其DDL的专有扩展，允许控制数据库系统的非标准功能
对DDL说，有四种最基本的语句：
CREATE:在数据库中创建对象
ALTER:允许用户以各种方式修改现有对象(例如在现有表格中增加一列)
RENAME:顾名思义rename
TRUNCATE:删除一个表中的所有数据(非标准但常见)
DROP:使数据库中的现有对象被删除，通常是无法恢复的

DML

常见操作包括：
SELECT:查询数据库中的数据
INSERT:向表中插入数据
UPDATE:更新表中的数据
DELETE:从表中删除数据
(增删查改)

操作

Create 创建表

我们可以用下面的语句创建表:
CREATE TABLE

([(<size)]<column constraint),
…
-);

:指的是表的名称，它必须是一个字母数字标识符 :是属性(列的名字)，也必须是一个字母数字标识符

:代表了表的类型，有以下几种比较常见的类型：
INTEGER:整数。可以指定最大位数
DECIMAL:还可以都指定最大小数位数，比如DECIMAL(4,3)代表四位整数三位小数
REAL:实数
BIT：bit 字符串
DATE:日期
TIME：24小时时间
TIMESTAMP：date+time，日期加时间
CHAR:固定大小的字符串，必须指定最大大小
VARCHAR:可变尺寸的字符串或文本

用于指定性质的属性(used to specify a property of an attribute)，可能是以下几种之一：
UNIQUE:代表column value 必须是唯一的，和该类在的其它值不同，常用于key
NOT NULL:不能包含NULL值
PRIMARY KEY:代表这个attribute是primary key的一部分
REFERENCES

:此属性是外键forign key的一部分，此时必须给出另外一个表的名称

外键：代表其它表中的主键、

DEFAULT:如果在插入或更新元组期间未指定值，将这个值运用
CHECK:检查在修改或插入此属性值时是否满足条件，例如，我们希望每个恒星的年龄介于0到200之间

这是一种指定键的替代方法
PRIMARY KEY()
FOREIGN KEY () REFERENCES

比如我们想要创建一个代表人的表，包含四列：lastName,FirstNmae,Address,Age
对应的类型是，varcher,varcher,varchar,integer

1 2	CREATE TABLE Person( LastNamevarchar(30), FirstNamevarchar(10), Address varchar, Age integer) CREATE TABLE <表名>( 列名列名数据类型数据类型)

Select 选择

我们可以利用Select，对数据库进行关系限制relational restrict,项目projrct,连接操作join operation

Select语句用于从表中选择数据。表格结果存储在结果表中(称为结果集 result-set)

比如我们想要从叫”Persons”的表里面，得到”FirstName”和”Lastname”这样的列的内容，使用下面这条语句：
SELECT LastName, FirstName FROM Persons;

想要选取所有列，我们可以用星号来代替列名，获得所有列，比如：
SELECT * FROM Persons;

SQL的查询结果，被存储在一个结果集result-set里面

有的时候我们想要限制检索的行，就使用WHERE
COMPARISON:将一个表达式的值和另外一个表达的值进行比较
RANGE:测试表达式的值是否在指定的值范围内
SET MEMBERSHIP:测试表达式的值是否等于一组值
PATTERN MATCH:测试字符串是否匹配指定的模式
NULL:测试列中是否具有NULL值
在SQL中，一样可以使用其它语言那些=，<,>!=等比较符号

SELECT Student_No, Student_NameFROM STUDENTWHERE year = 2;
这样的语句可以为我们从表中检索到 year = 2的数据

Select studentname,studentid, studentyobfrom Student where studentyob< 1990
选择1990年以前出生的所有学生

pgSQL 2

常用函数

AVG():返回某列的平均值
COUNT():返回某列的行数
MAX():返回某列的最大值
MIN():返回某列的最小值
SUM():返回某列的值之和

常见字符串函数

LENGTH(S):计算字符串长度
CONCAT(S1,S2,…):字符串合并函数
LTRIM(S)/RTRIM(S)/TRIM(S):删除字符串空格函数
REPLACE(S,S1,S2):字符串替换
SUBSTRING(S,N LEN):获取子串

常见日期时间函数

EXTRACT(type FROM d):获取日期指定值函数【详见lecture7-8】
CURRENT_DATE:获取当前日期函数
CURRENT_TIME:获取当前时间函数
NOW():获取当前日期时间函数

COUNT ：

Count:计算结果集中的函数

Count(*) 适用于任何表——它用于计算 ResultSet 中的行数
比如我们现在想要计算一次比赛中，足球队伍的总数，我们可以写：
select count(*) from footballteam

ORDER 排序

ORDER BY:排序方式
ORDER BY位于SQL语句的末尾，当我们使用它的时候，会尝试对结果进行一次排序

比如我们现在想要按照团队名称的升序进行排序
```select teamname,stadiumcap from footballteam where (stadiumcap > 40000) ORDER BY teamname asc``

asc表示我们期望的排序是升序
desc表示我们期望的排序是降序

GROUP BY

于ORDER BY类似，它的作用在于进行数据的分组
“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理

字符串搜索(区分大小写的)

比如，我们想知道哪些球队使用Adidas作为球衣
select teamname,shirtmaker from footballteam where (shirtmaker = 'Addiddas') order by teamname desc

模式匹配 PATTERN MATCHING

LIKE

SQL 中的 LIKE 操作符（更具体地说是在 PostgreSQL 中）允许我们进行模式匹配
这就是SQL可以开始执行分析或查询的地方，而我们人类无法很快完成这些分析或查询

比如，我们现在想知道“哪些球队的经理的名字以字母 A 开头”，我们可以写下：
select teamname,managerfrom footballteam where (manager LIKE 'A%')

‘A%’是什么？意思是——返回所有第一个字符为大写字母A的字符串

这里的百分号%表示，字符串剩下的内容(AXXXXX中的XXXXX)

同理，如果我们想知道“哪支球队有以“体育场”结尾的正式体育场名称”

select teamname,stadiumname from footballtea mwhere (stadiumname LIKE '%Stadium')

“哪些球队的名字中出现了”sea”的地方？”
select teamname,stadiumname from footballteam where (teamname LIKE '%sea%')

另外，% 运算符也匹配空格和标点符号

or运算也同样可以被用于这种情况：
select teamname,stadiumname,manager from footballteam where (teamnameLIKE '%sea%') or (manager LIKE '%es')

VARCHAR(N)

通常，当我们设计表时，我们需要考虑我们使用的数据类型的限制和规格
VARCHAR(N)意味着我们可以存储任意字符串，最大长度为N个字符
正如我们所看到的，VARCHAR是一个可变长度的字符串
所以任何可打印的字符都可以存储在这里

这是一种非常灵活的数据类型，并且足以用于我们需要的基于文本的基于文本的数据类型

使用例子:

CREATE TABLE ComplaintsDatabase2
(
    complaintnumber SERIAL NOT NULL,
    complaintdate DATE NOT NULL,
    complainttime TIME NOT NULL,
    complaint VARCHAR(400) NOT NULL,
    CONSTRAINT ComplaintsDatabase2_pkey 
    PRIMARY KEY (complaintnumber)
)

在这里，我们限制了complaint的最多字符数是400

TEXT

PostgreSql提供了一种称为TEXT的替代数据类型
TEXT允许无限长的字符串
TEXT在表上的声明与任何其他数据类型相同

CREATE TABLE TextExampleData
(
    pknumber SERIAL NOT NULL,
    sampletext TEXT NOT NULL,
    CONSTRAINT TextExampleData_pkeyPRIMARY KEY (pknumber)
);

虽然TEXT如此强大，但是————对varchar中的字符数量的限制通常非常有用(毕竟我们总是想要限制插入的字数，比如用户名长度、密码长度)

pgSQL 3

数据创建与插入操作

表创建

CREATE TABLE 语法格式

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
    ...
);

另外，在指定了数据类型之后，还可以指定限制

例子：以下创建了一个表，表名为 COMPANY 表格，主键为 ID，NOT NULL 表示字段不允许包含 NULL 值

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

插入数据

使用INSERT INTO语句来插入数据

正常插入
INSERT INTO <表名> values(对应数据，逗号隔开)

向指定字段插入
INSERT INTO <表名> (字段名，逗号隔开) values(对应数据，逗号隔开)

批量插入数据
当然，我们可以善用ctrl+cv来进行

或者我们利用逗号隔开每一次插入，达到批量的目的

INSERT INTO <表名>  values
(a1,b1,c1),
(a2,b2,c2),
(a3,b3,c3),
...
(an,bn,cn);

利用表内数据来进行批量增加
比如我们有一模一样的数据格式的s1和s2，那么
INSERT INTO s2 SELECT FROM s1 ;
同理，用向指定字段插入数据的方法来进行指定地从s1插入至s2也是可行的

表别名操作和删除更新操作

Table Aliases 表别名

在SQL中，我们可以更改SQL语句中的表名或列名
此更改只是SQL语句的临时更改，但对于我们处理SQL查询非常有用

临时处理以方便后续操作

别名使数据库管理员以及其他数据库用户能够减少查询所需的代码量，并使查询更易于理解

比如我们现在有表：

显然它的可读性并不强，于是我们可以利用别名来得到：

这就是别名的应用，我们想要实现上述转换，可以用下面的代码:

SELECT AppNameas "Application Name",
RRP as "Recommended Price",
Usability as "Ease of Use Index"
from AliasExample
Order by "Ease of Use Index" asc

注意这里使用的是双引号

同理，除了列名，事实上我们对表名也可以进行别名的操作。这个在之后数据库和数据操作的内容中有很大帮助

SELECT
T1.AppName,
T1.RRP,
T1.Usability
from AliasExample as T1
Order by T1.AppName

此时T1就变成了表的别名
而我们利用了别名T1和列的关系，SELECT到了它们

Function

pgSQL提供了不少函数，主要可以分为两类

以Sum()为例子

删除 DELETE

利用DELETE命令来删除数据，如果不加WHERE进行限制，则删除该表所有数据

DELETE FROM <tableName>
DELETE FROM <tableName> WHERE <condition...>

在pgSQL中，你在一次命令中只可以删除一张表中的数据

在有JOINED表的情况下，DELETE确实从多个表中删除…..但它仍然一次执行一张表

Never use DELETE without a WHERE statement…除非你想删库跑路

仅当您打算清除或删除表中的所有行时，才使用不带WHERE语句的DELETE

另外，如果怕DELETE出错的话，我们可以先用SELECT来进行测试
我们可以将 DELETE 语句视为一种非常特殊的 SELECT 语句

删除 TRUNCATE

truncate table <表名>
直接清空这张表

DELETE 和 TRUNCATE的区别

可以简单认为，TRUNCATE是最直接的删除清空所有表，然后给你一张新表。这个删除是不可以恢复的(delete还有希望)
另外，DELETE属于DML语言，而TRUNCATE就是DDL语言了

更新 Update

语法如下：

UPDATE <表名>   
SET
    ...
    更改的数据
    格式是 xxxx = xxx
    不同数据用，分割
    ...

WHERE <条件>

单条修改
UPDATE <表名> set <修改内容，比如name = ‘Anna’> WHERE <搜索条件>

批量修改
不加where就是对所有数据更新

比如我们想更改下图箭头指向的数据

我们写下：

UPDATE GoogleSharesOctober2015
SET 
openvalue = 700
WHERE sharedate = '2015-10-10';

使得 sharedare = ‘2015-10-10’的那行中的，’openvalue’数据变为700

同理也可以同时执行多项更改：

UPDATE GoogleSharesOctober2015
SET
openvalue = openvalue + 20,
highvalue = highvalue + 20,
lowvalue = lowvalue + 20,
closevalue = closevalue + 20
WHERE sharedate = '2015-10-20';

在UPDATE命令中，WHERE是十分重要的————它控制着更新的地方。如果没有 WHERE 子句，则表中的每一行都会更新。这通常不是必需的，甚至可能会导致数据损坏。

NULL数据

对于NULL数据，我们使用IS NULL命令，因为NULL不能“被比较”，因此EQUALS命令无效

DROP TABLE

DROP TABLE命令会直接删除一张表
如下：
DROP TABLE IF EXISTS <表名>代表我们直接删除这张表

‘IF EXISTS’ 表示如果表不存在，PostgreSQL 不会报错

ALTER TABLE

ALTER TABLE 命令可以更改表的定义
1.改表名
ALTER TABLE <TABLENAME> RENAME TO <NEW TABLENAME>
这样就可以把表名<TABLENAME>改为我们所需要的新表名<NEW TABLENAME>

关键字：RENAME TO

2.改列名
与修改表明类似
ALTER TABLE <TABLENAME> RENAME COLUMN <old_column>TO <new_column>

关键字：RENAME A TO B

3.删除一列
ALTER TABLE <TABLENAME> DROP COLUMN <column_name>

关键字: DROP

4.加一列
ALTER TABLE TollPlazaData ADD COLUMN Direction TEXT;

关键字：ADD

5.改变列的类型
ALTER TABLE <TABLENAME> ALTER COLUMN <column_name> TYPE<New Column Type>

另外，也可以把表的VARCHAR修改，比如VARCHAR(N)改为VARCHAR(M)
也可以将VARCHAR修改为TEXT

当更改已经joined的表时，要注意这会破坏数据库的完整性——您的连接jonis和引用references将不起作用

6.分配主键
ALTER TABLE <tableName> PRIMARY KEY <key>;

关键字：PRIMARY KEY xxx