dos命令行连接PostgreSQL:
1. 接入PostgreSQL数据库: psql -h IP地址 -p 端口 -U 用户名 -d 数据库名2. 输入数据库密码
C:\Users\admin\Desktopλ psql -U postgres -d yiibai_db用户 postgres 的口令：psql (11.1)输入 "help" 来获取帮助信息.yiibai_db=# \dt 关联列表 架构模式 | 名称 | 类型 | 拥有者----------+----------+--------+---------- public | student | 数据表 | postgres public | student2 | 数据表 | postgres(2 行记录)
查看数据库:
1. SELECT datname FROM pg_database;或者2. psql命令行中执行:\l (反斜杠l) 例如:
yiibai_db=# SElECT datname FROM pg_database; datname----------- postgres template1 template0 yiibai_db(4 行记录)yiibai_db=# \l 数据库列表 名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限-----------+----------+----------+-----------------------------------------------------+-----------------------------------------------------+----------------------- postgres | postgres | UTF8 | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | template0 | postgres | UTF8 | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | =c/postgres + | | | | | postgres=CTc/postgres yiibai_db | postgres | UTF8 | Chinese (Simplified)_People's Republic of China.936 | Chinese (Simplified)_People's Republic of China.936 | =Tc/postgres + | | | | | postgres=CTc/postgres(4 行记录)yiibai_db=#
切换数据库:
切换数据库：\c databasename
yiibai_db=# SElECT datname FROM pg_database; datname----------- postgres template1 template0 yiibai_db(4 行记录)yiibai_db=# \c postgres;您现在已经连接到数据库 "postgres",用户 "postgres".postgres=#
查看当前数据库中的表:
1. SELECT tablename FROM pg_tables WHERE schemaname='public'; # 列出数据库中用户自定义的表名或者2. psql>\dt
yiibai_db=# \dt 关联列表 架构模式 | 名称 | 类型 | 拥有者----------+----------+--------+---------- public | student | 数据表 | postgres public | student2 | 数据表 | postgres(2 行记录)yiibai_db=# SELECT tablename FROM yiibai_db;ERROR: relation "yiibai_db" does not exist第1行SELECT tablename FROM yiibai_db; ^yiibai_db=# SELECT tablename FROM pg_tables WHERE schemaname='public'; tablename----------- student student2(2 行记录)
查看库中某个表的结构:
使用SQL去查询:SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS lengthvar, a.attnotnull AS notnull, b.description AS comment FROM pg_class c, pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, pg_type t WHERE c.relname = 'student' and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum
使用命令查询psql>\d tablename
yiibai_db-# \d student 数据表 "public.student" 栏位 | 类型 | Collation | Nullable | Default----------+----------------+-----------+----------+--------- name | character(100) | | | id | integer | | | subjects | character(1) | | |
显示数据的字符：\encoding
yiibai_db-# \encodingGBKyiibai_db-#
修改数据库的表名/表的字段名/表的字段属性:
1.更改表名 alter table 表名 rename to 新表名2.更改字段名alter table 表名 rename 字段名 to 新字段名3,更改字段类型如：ID 字段 原类型为 character varying(50) 新类型为integer其中，ID中原有数据为1，2，3等数字用如下语句更改 alter table dbo.titemtype alter column id type integer using to_number(id,'9');
1、增加一列ALTER TABLE table_name ADD column_name datatype;2、删除一列ALTER TABLE table_name DROP column_name;3、更改列的数据类型ALTER TABLE table_name ALTER column_name TYPE datatype;4、表的重命名ALTER TABLE table_name RENAME TO new_name;5、更改列的名字ALTER TABLE table_name RENAME column_name to new_column_name;6、字段的not null设置ALTER TABLE table_name ALTER column_name {SET|DROP} NOT NULL;7、给列添加defaultALTER TABLE table_name ALTER column_name SET DEFAULT expression;