PostgresSQLITeye - 凯时娱乐

PostgresSQLITeye

2019年03月24日10时13分44秒 | 作者: 宇熙 | 标签: 运用,一个,索引 | 浏览: 1253

PostgreSQL 列一切表结构的查询句子

SELECT  tablename  FROM  pg_tables 
WHERE  tablename  NOT  LIKE  pg% 
AND tablename NOT LIKE sql_%
ORDER  BY  tablename;

查询表t_appointment_apply表的字段信息
SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a
.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname=t_appointment_apply and a.attnum 0 and a.attrelid=c.oid and a.atttypid=t.oid

 

-

1.
PostgresSQL 支撑规范的 SQL 类型 int,smallint, real,double precision, char(N), varchar(N),date, time,timestamp 和 interval
2.
\c [数据库名]   多个数据库之间的切换
\a              在非对齐和对齐的输出形式之间切换
\C [字串]       设置表标题, 假如参数空则撤销标题
\cd [目录名]    改动当时的作业目录
\copy ...       履行 SQL COPY, 数据流指向客户端主机
\copyright      显现 PostgreSQL 用法和发布信息
\d [姓名]       描绘表, 索引, 序列, 或许视图
                 列出表/索引/序列/视图/体系表
\d{t|i|s|v|S} [形式]     (加 "+" 获取更多信息)
                 列出表/索引/序列/视图/体系表
\da [形式]      列出集合函数
\dd [形式]      显现方针的注释
\dD [形式]      列出域
\df [形式]      列出函数 (加 "+" 获取更多的信息)
\do [姓名]      列出操作符
\dl             列出大目标, 和 \lo_list 相同
\dp [形式]      列出表拜访权限
\dT [形式]      列出数据类型 (加 "+" 获取更多的信息)
\du [形式]      列出用户
\e [文件名]     用一个外部修改器修改当时查询缓冲区或许文件
\echo [字串]    向规范输出写出文本
\encoding [哪种编码]       设置客户端编码(EUC_JP,GBK)
\f [字串]       设置域分隔符
\g 文件名       向服务器发送 SQL 指令 (而且把成果写到文件或许 |管道)
\h [姓名]       SQL 指令的语法协助, 用 * 能够看一切指令的协助
\H              在 HTML 输出形式之间切换 (当时是 封闭)
\i 文件名       履行来自文件的指令
\l              列出一切数据库
\lo_export, \lo_import, \lo_list, \lo_unlink
                 大目标操作
\o 文件名       向文件或许 |管道 发送一切查询成果
\p              显现当时查询缓冲区的内容
\pset 姓名 [值]      设置表的输出选项 (NAME := {foramt|border|expaned|
                 (姓名 := {foramt|border|expanded|fieldsep|null|recordsep|
                 tuples_only|title|tableattr|pager})
\q              退出 psql
\r              重置 (整理) 查询缓冲区
\s [文件名]     打印前史或许将其保存到文件
\set [姓名 [值]]      设置内部变量, 假如没有参数就列出一切
\t              只显现行 (当时是 封闭)
\T [字串]       设置 HTML 表 符号特点, 假如没有参数就撤销设置
\timing         查询计时开关切换 (现在是 封闭)
\unset 姓名     撤销(删去)内部变量
\w [文件名]     将当时查询缓冲区写出到文件
\x              在扩展输出之间切换 (现在是 封闭)
\z [形式]       列出表拜访权限 (和 \dp 相同)
\! [指令]       在 shell 里履行指令或许开端一个交互的 shell
\echo [string]  输出字符串
\qecho [字串]     向查询输出流写出文本 (见 \o)
3. 将数据库 导出到 文件标识符
 
指令:
\copy 表名 to 文件名|规范输出 [delimiter  as  ‘分隔符’]  [null  as ‘null表明的字符串’ ] [csv  quote  as ‘引号的类型’]
 
解说:
能够将表中的数据输出到 文件或规范输出。

delimiter as ‘分隔符’   :阐明输出设备的文本中表的每个字段用什么字符切割,默许是tab;

null  as ‘null表明的字符串’:阐明输出设备的文本中表的NULL值的表明办法,默许为“\N”;

csv quote as ‘引号类型’ :阐明导出的csv文件中的引号类型是什么,

关于Postgres7.4曾经的版别,不支撑csv的导入与导出,这时不要运用这个可选项。
比如:

aa=#\copy  testtable to data.csv  delimiter as ‘,’ csv quote as ‘”‘

这条指令将testtable表中的内容作为SQL句子,导出data.csv文件中。

delimiter as ‘,’   :阐明data.txt文本中表的每个字段用“逗号”切割;

csv quote as ‘”‘ :阐明csv中的引号类型是“双引号”。

像前面说的:这个比如不适用在Postgres7.4曾经的版别。
 
留意文件保存途径默许在:
C:\Documents and Settings\zhuyaopeng\目录下  而且只保存数据,不保存表结构
 
4. 从 文件标识符 导入数据到数据库
 
指令:
\copy 表名 form 文件名|规范输入 [delimiter  as  ‘分隔符’]  [null  as ‘null表明的字符串’ ]   [csv  quote  as ‘引号的类型’]
比如:
aa=#\copy  testtable from data.csv  delimiter as ‘,’ csv quote as ‘”‘

这条指令将data.csv文件中的文本,作为sql句子导入到testtable表,

delimiter as ‘,’   :阐明data.txt文本中表的每个字段用“逗号”切割;

csv quote as ‘”‘ :阐明csv中的引号类型是“双引号”。
 
5.怎么只挑选一个查询成果的头几行?或是随机的一行?
 
假如你仅仅要提取几行数据,而且你在履行查询中知道切当的行数,你能够运用LIMIT功用。 假如有一个索引与 ORDER BY中的条件匹配,PostgreSQL 或许就只处理要求的头几条记载, (不然将对整个查询进行处理直到生成需求的行)。假如在履行查询功用时不知道切当的记载数, 可运用游标(cursor)和FETCH功用。
可运用以下办法提取一行随机记载的:
  SELECT  cols
  FROM tab
  ORDER BY random()
  LIMIT 1 ;
 
 6.怎么检查表、索引、数据库以及用户的界说?怎么检查psql里用到的查询指令并显现它们?
 
在psql中运用 \dt 指令来显现数据表的界说,要了解psql中的完好指令列表可运用\? ,别的,你也能够阅览 psql 的源代码 文件pgsql/src/bin/psql/describe.c,它包括为生成psql反斜杠指令的输出的一切 SQL 指令。你还能够带 -E 选项发动 psql, 这样它将打印出履行你在psql中所给出的指令的内部实践运用的SQL查询。PostgreSQL也供给了一个兼容SQL的INFORMATION SCHEMA接口, 你能够从这儿获取关于数据库的信息。
在体系中有一些以pg_ 打头的体系表也描绘了表的界说。
运用 psql -l 指令能够列出一切的数据库。
也能够阅读一下 pgsql/src/tutorial/syscat.source文件,它列举了许多可从数据库体系表中获取信息的SELECT语法。
 
7.怎么更改一个字段的数据类型?
 
在8.0版别里更改一个字段的数据类型很简单,可运用 ALTER TABLE ALTER COLUMN TYPE 。
在曾经的版别中,能够这样做:
 BEGIN;
    ALTER TABLE tab ADD COLUMN new_col new_data_type;
    UPDATE tab SET new_col = CAST(old_col AS new_data_type);
    ALTER TABLE tab DROP COLUMN old_col;
    COMMIT;
你然后能够运用VACUUM FULL tab 指令来使体系回收无效数据所占用的空间。
 
8.一行记载,一个表,一个库的最大尺度是多少?
 
下面是一些约束:
一个数据库最大尺度? 无约束(已存在有 32TB 的数据库)
一个表的最大尺度? 32 TB
一行记载的最大尺度? 1.6 TB
一个字段的最大尺度? 1 GB
一个表里最大行数? 无约束
一个表里最大列数? 250-1600 (与列类型有关)
一个表里的最大索引数量? 无约束
当然,实践上没有真实的无约束,仍是要受可用磁盘空间、可用内存/交流区的约束。 事实上,当这些数值变得反常地大时,体系功用也会受很大影响。
表的最大尺度 32 TB 不需求操作体系对大文件的支撑。大表用多个 1 GB 的文件存储,因而文件体系尺度的约束是不重要的。
假如缺省的块巨细增长到 32K ,最大的表尺度和最大列数还能够增加到四倍。
9.存储一个典型的文本文件里的数据需求多少磁盘空间?
 
一个 Postgres 数据库(存储一个文本文件)所占用的空间最多或许需求相当于这个文本文件本身巨细5倍的磁盘空间。
例如,假定有一个 100,000 行的文件,每行有一个整数和一个文本描绘。 假定文本串的均匀长度为20字节。文本文件占用 2.8 MB。寄存这些数据的 PostgreSQL 数据库文件大约是 6.4 MB:
     32 字节: 每行的头(估计值)
     24 字节: 一个整数型字段和一个文本型字段
   +  4 字节: 页面内指向元组的指针
  
     60 字节每行
   PostgreSQL 数据页的巨细是 8192 字节 (8 KB),则:
   8192 字节每页
   -   =  136 行/数据页(向下取整)
     60 字节每行
   100000 数据行
     =  735 数据页(向上取整)
      128 行每页
   735 数据页 * 8192 字节/页  =  6,021,120 字节(6 MB)
索引不需求这么多的额定耗费,但也的确包括被索引的数据,因而它们也或许很大。
空值NULL寄存在位图中,因而占用很少的空间。
10.为什么我的查询很慢?为什么这些查询没有运用索引?
 
并非每个查询都会主动运用索引。只要在表的巨细超越一个最小值,而且查询只会选中表中较小份额的记载时才会选用索引。 这是由于索引扫描引起的随即磁盘存取或许比直接地读取表(次序扫描)更慢。
为了判别是否运用索引,PostgreSQL有必要取得有关表的计算值。这些计算值能够运用 VACUUM ANALYZE,或 ANALYZE 取得。 运用计算值,优化器知道表中有多少行,就能够更好地判别是否运用索引。 计算值对确认优化的衔接次序和衔接办法也很有用。在表的内容发作变化时,应定时进行计算值的更新搜集。
索引一般不用于 ORDER BY 或履行衔接。对一个大表的一次次序扫描,再做一个显式的排序一般比索引扫描要快。
可是,在 LIMIT 和 ORDER BY 结合运用时经常会运用索引,由于这只会回来表的一小部分。 实践上,尽管 MAX() 和 MIN() 并不运用索引,经过对 ORDER BY 和 LLIMIT 运用索引取得最大值和最小值也是能够的:
 SELECT col
 FROM tab
 ORDER BY col [ DESC ]
 LIMIT 1;
假如你坚信PostgreSQL的优化器运用次序扫描是不正确的,你能够运用SET enable_seqscan TO off指令, 然后再次运转查询,你就能够看出运用一个索引扫描是否的确要快一些。
当运用通配符操作,例如 LIKE 或 ~ 时,索引只能在特定的状况下运用:
字符串的开端部分有必要是一般字符串,也就是说:
LIKE 形式不能以 % 打头。
~ (正则表达式)形式有必要以 ^ 打头。
字符串不能以匹配多个字符的形式类打头,例如 [a-e]。
巨细写无关的查找,如 ILIKE 和 ~* 等不运用索引,但能够用 4.8 节描绘的函数索引。
在做 initdb 时有必要选用缺省的本地设置 C locale,由于体系不或许知道在非C locale状况时下一个最大字符是什么。 在这种状况下,你能够创立一个特别的text_pattern_ops索引来用于LIKE的索引。
在8.0之前的版别中,除非要查询的数据类型和索引的数据类型相匹配,不然索引经常是未被用到,特别是对int2,int8和数值型的索引。
11.我怎样做正则表达式查找和巨细写无关的正则表达式查找?怎样运用索引进行巨细写无关查找?
 
操作符 ~ 处理正则表达式匹配,而 ~* 处理巨细写无关的正则表达式匹配。大写些无关的 LIKE 变种成为 ILIKE。
巨细写无关的等式比较一般写做:
    SELECT *
    FROM tab
    WHERE lower(col) = abc;
这样将不会运用规范的索引。可是能够创立一个可被运用的函数索引:
    CREATE INDEX tabindex ON tab (lower(col));
12.在一个查询里,我怎样检测一个字段是否为 NULL ?我怎么才干精确排序而不管某字段是否含 NULL 值?
 
用 IS NULL 和 IS NOT NULL 测验这个字段,具体办法如下:
   SELECT *
   FROM tab
   WHERE col IS NULL;
为了能对含 NULL字段排序,可在 ORDER BY 条件中运用 IS NULL和 IS NOT NULL 修饰符,条件为真 true 将比条件为假false 排在前面,下面的比如就会将含 NULL 的记载排在成果的上面部分:
   SELECT *
   FROM tab
   ORDER BY (col IS NOT NULL)
 
13.各种字符类型之间有什么不同?
 
类型 内部称号 阐明
VARCHAR(n) varchar 指定了最大长度,变长字符串,缺乏界说长度的部分不补齐
CHAR(n) bpchar 定长字符串,实践数据缺乏界说长度时,以空格补齐
TEXT text 没有特别的上限约束(仅受行的最大长度约束)
BYTEA bytea 变长字节序列(运用NULL也是答应的)
"char" char 一个字符
在体系表和在一些错误信息里你将看到内部称号。
上面所列的前四种类型是"varlena"(变长)类型(也就是说,最初的四个字节是长度,后边才是数据)。 所以实践占用的空间比声明的巨细要多一些。 可是这些类型都能够被紧缩存储,也能够用 TOAST 脱机存储,因而磁盘空间也或许比料想的要少。
VARCHAR(n) 在存储约束了最大长度的变长字符串是最好的。 TEXT 适用于存储最大可达 1G左右但未界说约束长度的字符串。
CHAR(n) 最适合于存储长度相同的字符串。 CHAR(n)会依据所给定的字段长度以空格补足(缺乏的字段内容), 而 VARCHAR(n) 只存储所给定的数据内容。 BYTEA 用于存储二进制数据,尤其是包括 NULL 字节的值。这些类型具有相似的功用特性。
 
14.我怎么取得一个刺进的序列号的值?
 
一种办法是在刺进之前先用函数 nextval() 从序列目标里检索出下一个 SERIAL 值,然后再显式刺进。运用 4.11.1 里的例表,可用伪码这样描绘:
 new_id = execute("SELECT nextval(person_id_seq)");
 execute("INSERT INTO person (id, name) VALUES (new_id, Blaise Pascal)");
这样还能在其他查询中运用寄存在 new_id 里的新值(例如,作为 person 表的外键)。 留意主动创立的 SEQUENCE 目标的称号将会是 table _ serialcolumn _seq, 这儿 table 和 serialcolumn 分别是你的表的称号和你的 SERIAL 字段的称号。
相似的,在 SERIAL 目标缺省刺进后你能够用函数 currval() 检索刚赋值的 SERIAL 值,例如:
 execute("INSERT INTO person (name) VALUES (Blaise Pascal)");
 new_id = execute("SELECT currval(person_id_seq)");
 
15.运用 currval() 会导致和其他用户的抵触状况(race condition)吗?
 
不会。currval() 回来的是你本次会话进程所赋的值而不是一切用户的当时值。
 
16.为什么不在业务反常连续后重用序列号呢?为什么在序列号字段的取值中存在连续呢?
 
为了进步并发性,序列号在需求的时分赋予正在运转的业务,而且在业务完毕之前不进行确定, 这就会导致反常连续的业务后,序列号会呈现距离。
 
17.什么是 OID ?什么是 CTID ?
 
PostgreSQL 里创立的每一行记载都会取得一个仅有的OID,除非在创立表时运用WITHOUT OIDS选项。 OID创立时会主动生成一个4字节的整数,一切 OID 在整个 PostgreSQL 中均是仅有的。 可是,它在超越40亿时将溢出, OID尔后会呈现重复。PostgreSQL 在它的内部体系表里运用 OID 在表之间树立联络。
在用户的数据表中,最好是运用SERIAl来替代OID 由于SERIAL仅仅确保在单个表中数据是仅有的,这样它溢出的或许性就十分小了, SERIAL8可用来保存8字节的序列号字段。
CTID 用于标识带着数据块(地址)和(块内)偏移的特定的物理行。 CTID 在记载被更改或重载后发作改动。索引进口运用它们指向物理行。
 
18.为什么我收到错误信息“ERROR: Memory exhausted in AllocSetAlloc()”?
 
这很或许是体系的虚拟内存用光了,或许内核对某些资源有较低的约束值。在发动 postmaster 之前试试下面的指令:
 ulimit -d 262144
 limit datasize 256m
取决于你用的 shell,上面指令只要一条能成功,可是它将把你的进程数据段约束设得比较高, 因而或许能让查询完结。这条指令应用于当时进程,以及一切在这条指令运转后创立的子进程。 假如你是在运转SQL客户端时由于后台回来了太多的数据而呈现问题,请在运转客户端之前履行上述指令。
 
19.怎样进行 outer join (外衔接)?
 
PostgreSQL 选用规范的 SQL 语法支撑外衔接。这儿是两个比如:
 SELECT *
 FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
或是
 SELECT *
 FROM t1 LEFT OUTER JOIN t2 USING (col);
这两个等价的查询在 t1.col 和 t2.col 上做衔接,而且回来 t1 中一切未衔接的行(那些在 t2 中没有匹配的行)。 右[外]衔接(RIGHT OUTER JOIN)将回来 t2 中未衔接的行。 彻底外衔接(FULL OUTER JOIN)将回来 t1 和 t2 中未衔接的行。 关键字 OUTER 在左[外]衔接、右[外]衔接和彻底[外]衔接中是可选的,一般衔接被称为内衔接(INNER JOIN)。
20.怎么运用触及多个数据库的查询?
 
没有办法查询当时数据库之外的数据库。 由于 PostgreSQL 要加载与数据库相关的体系目录(体系表),因而跨数据库的查询怎么履行是不定的。
附加增值模块contrib/dblink答应选用函数调用完成跨库查询。当然用户也能够一起衔接到不同的数据库履行查询然后在客户端兼并成果。
 
21.怎么让函数回来多行或多列?
 
在函数中回来数据记载集的功用是很简单运用的,概况拜见: http://techdocs.postgresql.org/guides/SetReturningFunctions
 
22.为什么我在运用PL/PgSQL函数存取暂时表时会收到错误信息“relation with OID # does not exist”?
 
PL/PgSQL会缓存函数的内容,由此带来的一个欠好的副作用是若一个 PL/PgSQL 函数拜访了一个暂时表,然后该表被删去偏重建了,则再次调用该函数将失利, 由于缓存的函数内容依然指向旧的暂时表。处理的办法是在 PL/PgSQL 顶用EXECUTE 对暂时表进行拜访。这样会确保查询在履行前总会被从头解析。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表凯时娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1
  • 2

    PostgresSQLITeye

    运用,一个,索引
  • 3
  • 4

    Oracle 数据库用户办理ITeye

    用户,权限,数据库
  • 5
  • 6

    mysql的字符串函数ITeye

    字符串,函数,字符
  • 7
  • 8

    pro*c 动态sql二ITeye

    数据,过错,成功
  • 9
  • 10