数据库

pgsql

基本操作

psql -U sunjun -d postgres # 123456
\l # 列出db
\c # \c db change db
\d # \d table 两个功能
\di # 命令列出数据库中所有索引：
create database runoobdb # DROP;
CREATE TABLE table_name() # DROP TABLE table_name, table_name2;
模式（SCHEMA）
- 模式类似于操作系统层的目录，但是模式不能嵌套。
- 模式（SCHEMA）可以看着是一个表的集合，模式通常用于组织和隔离数据库对象，防止对象名称冲突。
- 一个模式可以包含视图、索引、数据类型、函数和操作符等。
- CREATE SCHEMA myschema () # CREATE TABLE myschema.mytable () 在模式下创建表
- DROP SCHEMA myschema; # 删除一个为空的模式（其中的所有对象已经被删除）：
- DROP SCHEMA myschema CASCADE; # 删除一个模式以及其中包含的所有对象：

CURD操作

INSERT INTO 语句
- INSERT INTO TABLE_NAME (column1,...columnN) VALUES (value1,...valueN); # 插入多行加values(),()就行
- INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN); # 向表中的所有字段插入值，则可以不需要指定字段
WHERE 子句
- and/or/not null/like/in/not in
- BETWEEN # AGE BETWEEN 25 AND 27;
- 子查询，通过 EXISTS 运算符判断它是否返回行
- SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
- SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000); # 查询大于该 AGE(年龄) 字段数据：
update语句
- UPDATE table_name SET column1 = value1, columnN = valueN WHERE [condition];
delete语句
- DELETE FROM table_name WHERE [condition];
LIKE 子句
- 在 LIKE 子句中，通常与通配符结合使用，通配符表示任意字符，主要有以下两种通配符：百分号 %、下划线 _(代表一个字符)
- LIKE 子句是只能用于对字符进行比较，将整型数据类型转化为字符串数据类型AGE::text
- SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';
WITH 子句
- WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。
- WITH 子句的定义，WITH name as (query), 然后使用这个select * from name就行，像是一个查询视图
- 可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句
DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
limit/order by/group by/having 和mysql的用法是一样的

其它操作

约束
- 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
- NOT NULL：指示某列不能存储 NULL 值。
- UNIQUE：确保某列的值都是唯一的。
- PRIMARY Key：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识;
- FOREIGN Key：保证一个表中的数据匹配另一个表中的值的参照完整性。
  - 通常一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)，即维护了两个相关表之间的引用完整性。
  - 用法：EMP_ID INT references COMPANY6(ID)
- CHECK：保证列中的值符合指定的条件。
  - CHECK 约束保证列中的所有值满足某一条件，即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。
  - create table 的时候用 SALARY int CHECK(SALARY > 0)
- EXCLUSION ：排他约束，保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回 false 或空值。
视图
- 是一张假表，是通过相关的名称存储在数据库中的一个 PostgreSQL 语句
- 用 CREATE VIEW 语句创建视图，视图创建可以从一张表，多张表或者其他视图。
- CREATE VIEW COMPANY_VIEW AS SELECT ID, NAME, AGE FROM COMPANY;
- DROP VIEW view_name;
事务
- 事务具有以下四个标准属性，通常根据首字母缩写为 ACID：
  - 原子性（Atomicity）
  - 一致性（Consistency）
  - 隔离性（Isolation）
  - 持久性（Durability）
- BEGIN or BEGIN TRANSACTION;
- ROLLBACK
- COMMIT or END TRANSACTION;
连接(JOIN)，有五种连接类型：
- CROSS JOIN ：交叉连接，笛卡尔积， x*y 行
- INNER JOIN：内连接
  - 是最常见的连接类型，是默认的连接类型
  - 找到所有满足连接谓词的行的匹配对，当满足连接时，A和B行的每个匹配对的列值会合并成一个结果行。
- SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL
  - LEFT OUTER JOIN：左外连接
    - 外部连接是内部连接的扩展，对于左外连接，首先执行一个内连接。
    - 然后，对于表T1中不满足表T2中连接条件的每一行，其中T2的列中有null值也会添加一个连接行。因此连接的表在T1中每一行至少有一行。
  - RIGHT OUTER JOIN：右外连接
    - 与左联接相反，对于T2中的每一行，结果表总是有一行。
  - FULL OUTER JOIN：全外连接
    - 首先，执行内部连接
    - 没看懂，效果和“左外连接”是一样的；
索引
- 索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。
- 使用 CREATE INDEX 语句创建索引，它允许命名索引，指定表及要索引的一列或多列，并指示索引是升序排列还是降序排列。
- 索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。
- CREATE INDEX index_name ON table_name(column1_name, column1_name_n);
- 唯一索引：CREATE UNIQUE INDEX index_name on table_name (column_name);
- DROP INDEX index_name;
- 下面几个情况需要避免使用索引
  - 索引不应该使用在较小的表上。
  - 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
  - 索引不应该使用在含有大量的 NULL 值的列上。
  - 索引不应该使用在频繁操作的列上。
ALTER TABLE 命令
- 1、用于添加，修改，删除一张已经存在表的列 COLUMN：
  - ALTER TABLE table_name ADD column_name datatype;
  - ALTER TABLE table_name DROP COLUMN column_name;
  - ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype; # 修改表中某列的数据类型
- 2、也可以用 ALTER TABLE 命令添加和删除约束 CONSTRAINT：
  - ALTER TABLE table_name ALTER column_name datatype NOT NULL; # 给表中某列添加 NOT NULL 约束
  - ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
  - ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);
  - ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
  - ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint; # 删除约束
AUTO INCREMENT（自动增长）
- 这三个类型是伪类型，有自增的功能，SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：2、4、8个字节
子查询
- 一个 SELECT 语句的查询结果能够作为另一个语句的输入值。并可使用运算符如 =、<、>、>=、<=、IN、BETWEEN等
- 子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用
  - SELECT：SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY WHERE SALARY > 45000) ;
  - INSERT：INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE;
  - UPDATE：UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27);
  - DELETE：DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
- 子查询必须遵循的几个规则：
  - 子查询必须用括号括起来。
  - 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
  - ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
  - 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
  - BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
LOCK（锁）
- 锁主要是为了保持数据库数据的一致性，可以阻止用户修改一行或整个表；
- 有两种基本的锁：排它锁（Exclusive Locks）和共享锁（Share Locks）。
  - 如果数据对象加上排它锁，则其他的事务不能对它读取和修改。
  - 如果加上共享锁，则该数据库对象可以被其他事务读取，但不能修改。
- LOCK [ TABLE ] name IN lock_mode
  - lock_mode：锁定模式指定该锁与哪个锁冲突。如果没有指定锁定模式，则使用限制最大的访问独占模式。可能的值是：
  - ACCESS SHARE
  - ROW SHARE
  - ROW EXCLUSIVE
  - SHARE UPDATE EXCLUSIVE
  - SHARE
  - SHARE ROW EXCLUSIVE
  - EXCLUSIVE，ACCESS EXCLUSIVE
  - 一旦获得了锁，锁将在当前事务的其余时间保持。没有解锁表命令；锁总是在事务结束时释放。
- BEGIN; LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE; LOCK TABLE;
- 上面的消息指示表被锁定，直到事务结束，并且要完成事务;
PRIVILEGES（权限）
- 创建用户
  - CREATE USER runoob WITH PASSWORD 'password';
  - DROP USER runoob;
- 要向用户分配权限，可以使用 GRANT 命令。
- GRANT ALL ON COMPANY TO runoob;
  - GRANT privilege [, ...] ON object [, ...] TO { PUBLIC | GROUP group | username }
  - privilege − 值可以为：SELECT，INSERT，UPDATE，DELETE， RULE，ALL。
  - object − 要授予访问权限的对象名称。可能的对象有： table， view，sequence。
  - username 要授予权限的用户名。
- REVOKE ALL ON COMPANY FROM runoob;
常用函数
- 聚合函数
- 数学函数
- 字符串函数和操作符
- 类型转换相关函数
- 日期/时间函数
数据类型
- 也可以使用 CREATE TYPE 命令在数据库中创建新的数据类；
- 数值类型
  - 数值类型由 2、4、8 字节的整数以及4字节或8字节的浮点数组成
  - smallint、integer、bigint 整数范围
  - decimal、numeric 浮点数
  - real，4 字节，可变精度，不精确。范围是6 位十进制数字精度；
  - smallserial、serial、bigserial 三个自增整数，分别是2、4、8 字节的整数；
- 货币类型
  - money 类型存储带有固定小数精度的货币金额。
  - numeric、int 和 bigint 类型的值可以转换为 money
  - money，8字节的货币金额
- 字符类型
  - character varying(n), varchar(n) 变长，有长度限制
  - character(n), char(n) f定长,不足补空白
  - text 变长，无长度限制
- 日期/时间类型
  - timestamp
  - date
  - time
- 布尔类型
  - boolean 有"true"(真)或"false"(假)两个状态
  - 1 字节
- 枚举类型
  - 枚举类型是一个包含静态和值的有序集合的数据类型。
  - 与其他类型不同的是枚举类型需要使用 CREATE TYPE 命令创建。
  - 一旦创建，枚举类型可以用于表的定义
  - CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
- 几何类型
- 网络地址类型
- 位串类型
- 文本搜索类型
  - 全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。
  - PostgreSQL 提供了两种数据类型用于支持全文检索：tsvector/tsquery
- UUID 类型
- JSON 类型
  - json 数据类型可以用来存储 JSON数据，这样的数据也可以存储为 text，但是json数据类型更有利于检查每个存储的数值是可用的JSON值。
  - 有相关的函数来处理 json 数据：array_to_json/row_to_json
- 数组类型
  - 允许将字段定义成变长的多维数组。数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。
  - 创建表的时候，我们可以声明数组
    - pay_by_quarter integer[], # 为一维整型数组，也可以使用 "ARRAY" 关键字 pay_by_quarter integer ARRAY[4]
    - schedule text[][] # 二维文本类型数组
  - 插入值：
    - 使用花括号 {}，元素在 {} 使用逗号隔开，'{10000, 10000, 10000, 10000}'
    - 也可以使用 ARRAY 构造器语法，ARRAY[25000,25000,27000,27000]
  - 访问数组：
    - SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; # 数组的下标数字是写在方括弧内的。
    - 也可以搜索一个数组中的数值，pay_by_quarter[1] = 10000 OR pay_by_quarter[2] = 10000
    - SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); # 用下面的语句找出数组中所有元素值都等于 10000 的行：
- 复合类型
- 范围类型
  - 范围数据类型代表着某一元素类型在一定范围内的值。
  - timestamp 范围可能被用于代表一间会议室被预定的时间范围；
  - 内置的范围类型有：int4range、numrange、tsrange(timestamp without time zone的范围)、daterange —date的范围
  - CREATE TABLE reservation (room int, during tsrange);
  - INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
- 对象标识符类型
  - 在内部使用对象标识符(OID)作为各种系统表的主键；
  - 系统不会给用户创建的表增加一个 OID 系统字段(除非在建表时声明了WITH OIDS 或者配置参数default_with_oids设置为开启)
- 伪类型(给函数参数和返回值用的)
  - 可以用于声明一个函数的参数或者结果类型。
  - 伪类型在一个函数不只是简单地接受并返回某种SQL 数据类型的情况下很有用。

数据库 ​

pgsql ​

基本操作 ​

CURD操作 ​

其它操作 ​

数据库

pgsql

基本操作

CURD操作

其它操作