一、psql介绍
psql是PostgreSQL中的一个命令行交互式客户端工具,类似Oracle中的命令行工具sqlplus:
1.允许你交互地键入SQL或命令,然后把它们发出给PostgreSQL服务器,再显示SQL或命令的结果;
2.输入的内容还可以来自一个文件;
3.还提供了一些元命令和多种类似shell的特性来实现书写脚本,以及对对量任务的自动化工作;
二、psql的简单实用
按照前面的步骤,切换su - postgres用户,实用psql工具连接数据库。
1.查看有哪些数据库
postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (3 rows)
a.安装好后,默认会有一个叫postgres的数据库,还有两个模板数据库template0和template1;
b.用户再建数据库的时候,默认是从模板数据库template1克隆出来;
c.template0是一个最简化的模板库,创建数据库时,如果明确指定从此数据库集成,将创建一个最简化的数据库;
2.创建数据库osdba
postgres=# CREATE DATABASE osdba; CREATE DATABASE postgres-# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- osdba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postgres (4 rows)
3.访问osdba数据库
-bash-4.1$ psql osdba psql (8.4.20) Type "help" for help.
4.在数据库osdba中创建表t
osdba=# create table t(id int primary key,name varchar(40)); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE
5.查看osdb数据库中的表
osdba=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)
6.创建数据库testdb后,并连接到testdb数据库
osdba=# CREATE DATABASE testdb; CREATE DATABASE osdba=# \c testdb psql (8.4.20) You are now connected to database "testdb". testdb=#
三、psql的常用命令
1.\d命令-查看当前数据库中的所有表
osdba-# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres (1 row)
2.\d命令-跟一个表命,查看这个表的结构定义
osdba-# \d t Table "public.t" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(40) | Indexes: "t_pkey" PRIMARY KEY, btree (id)
3.\d命令-可以查看表格t的索引信息
osdba-# \d t_pkey Index "public.t_pkey" Column | Type --------+--------- id | integer primary key, btree, for table "public.t"
4.\d命令-跟通配符如*或?
osdba-# \d t* Table "public.t" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(40) | Indexes: "t_pkey" PRIMARY KEY, btree (id) Index "public.t_pkey" Column | Type --------+--------- id | integer primary key, btree, for table "public.t"
5.\d+命令,显示比\d命令更加详细的信息,显示与表列关联的注释
osdba-# \d+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+---------+------------- public | t | table | postgres | 0 bytes | (1 row)
6.匹配不同对象类型的\d命令,如\dt-只想显示匹配的表;\di-只想显示索引;\ds-只显示序列;\dv-只显示视图;\df-只显示函数等...
osdba-# \dt t* List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t | table | postgres
7.\dn命令-列出所有的schema
osdba-# \dn List of schemas Name | Owner --------------------+---------- information_schema | postgres pg_catalog | postgres pg_toast_temp_1 | postgres public | postgres (5 rows)
8.\db命令-显示所有表空间
osdba-# \db List of tablespaces Name | Owner | Location ------------+----------+---------- pg_default | postgres | pg_global | postgres | (2 rows)
9.\dg-列出数据库中所有角色和用户
osdba-# \dg List of roles Role name | Attributes | Member of -----------+-------------+----------- postgres | Superuser | {} : Create role : Create DB
10.\dp-显示表的权限分配情况
osdba-# \dp Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | t | table | | (1 row)
四、指定字符集编译的命令
1.\encoding gbk-设置客户端的字符编码为gbk
五、\pset命令
1.\pset命令-用于设置输出的格式,\pset border 0/1/2:输出内容无边框、只有内边框、内外都有边框
osdba-# \pset border 0 Border style is 0. osdba-# \dp Access privileges Schema Name Type Access privileges Column access privileges ------ ---- ----- ----------------- ------------------------ public t table (1 row) osdba-# \pset border 1 Border style is 1. osdba-# \dp Access privileges Schema | Name | Type | Access privileges | Column access privileges --------+------+-------+-------------------+-------------------------- public | t | table | | (1 row) osdba-# \pset border 2 Border style is 2. osdba-# \dp Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t | table | | | +--------+------+-------+-------------------+--------------------------+ (1 row)
2.\x命令-可以把表中的每一行的每列数据都拆分为单行展示,如果有一行数据有太多的拆行,显示不下,就可以使用这个命令
osdba-# \x Expanded display is on. osdba-# \dp Access privileges +-[ RECORD 1 ]-------------+--------+ | Schema | public | | Name | t | | Type | table | | Access privileges | | | Column access privileges | | +--------------------------+--------+ osdba-# \x Expanded display is off. osdba-# \dp Access privileges +--------+------+-------+-------------------+--------------------------+ | Schema | Name | Type | Access privileges | Column access privileges | +--------+------+-------+-------------------+--------------------------+ | public | t | table | | | +--------+------+-------+-------------------+--------------------------+ (1 row)
五、psql使用技巧
1.使用上下键把过去使用过的命令或者SQL语句调出来,连续两个tab键表示补全或给出提示输入
osdba-# \d \d \dc \dD \dew \dFd \dg \dn \ds \dT \da \dC \des \df \dFp \di \do \dS \du \db \dd \deu \dF \dFt \dl \dp \dt \dv
2.psql加上-E参数,可以把psql中各种以"\"开头的命令执行的实际SQL打印出来
-bash-4.1$ psql -E postgres psql (8.4.20) Type "help" for help. postgres=# \d ********* QUERY ********** SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN &#39;r&#39; THEN &#39;table&#39; WHEN &#39;v&#39; THEN &#39;view&#39; WHEN &#39;i&#39; THEN &#39;index&#39; WHEN &#39;S&#39; THEN &#39;sequence&#39;WHEN &#39;s&#39; THEN &#39;special&#39; END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (&#39;r&#39;,&#39;v&#39;,&#39;S&#39;,&#39;&#39;) AND n.nspname <> &#39;pg_catalog&#39; AND n.nspname <> &#39;information_schema&#39; AND n.nspname !~ &#39;^pg_toast&#39; AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; ************************** No relations found
如果你在使用之后,想立即关闭
postgres=# \set ECHO_HIDDEN off postgres=# \d No relations found.
以上就是PostgreSQL快速入门:psql工具的使用_MySQL的内容,更多相关内容请关注PHP中文网(www.php1.cn)!