postgresql命令操作

最新推荐文章于 2022-09-21 10:13:56 发布

weixin_34336526

最新推荐文章于 2022-09-21 10:13:56 发布

阅读量179

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/haoyonghui/2376807

版权

1.连接数据库
psql -h Server -p Port -U Username DatabaseName

2.创建数据库
postgres=# create database testdb;

3.查看数据库
postgres=# \l

4.删除数据库
postgres=# drop database testdb;

5.进入数据库
postgres=# \c testdb;

6.列出当前库所有表
testdb=# \dt

7.创建表
testdb=# create table account(
testdb(# user_id serial primary key,
testdb(# username varchar(50) unique not null,
testdb(# password varchar(50) not null);

create table products (
testdb(# product_no integer,
testdb(# name varchar(20),
testdb(# price numeric);

create table account (
username varchar(50) unique not null,
count integer);

create table company(
ID int primary key not null,
name TEXT not null,
age int not null,
address char(50),
salary real
);

create table audit(
emp_id int not null,
entry_date text not null
);

8.删除表
testdb=# drop table account;

9.创建模式
testdb=# CREATE SCHEMA myschema;

10.指定模式创建表
create table myschema.mytable(
user_id serial primary key,
username varchar(50) unique not null,
password varchar(50) not null);

11.删除模式里的对象
drop schema myschema CASCADE;

12.删除模式
drop schema myschema

13.插入数据
testdb=# insert into products values (1, 'chiness', 9.99);

14.查询数据
testdb=# select * from products;

15.更新数据
testdb=# update products set name = 'hyh' where product_no = 1;

16.删除表数据
testdb=# delete from products where name = 'hyh'; 字符串必须单引号

17.order by 升序，降序排列
testdb=# select from products order by price asc;
testdb=# select from products order by price desc;

18.order by 多列排序
select * from products order by price,product_no asc;

19.group by分组
testdb=# select name, sum(price) from products group by name;
按名字分组统计每个名字下的价格总额

20.HAVING子句与GROUP BY子句组合使用，用于选择函数结果满足某些条件的特定行
testdb=# select name from products group by name having name != 'hyh';

21.条件查询
AND 条件
testdb=# select * from products where name = 'hyh' and price = 10;

OR 条件
testdb=# select * from products where name = 'hyh' or price = 10;

AND & OR 条件
testdb=# select * from products where name = 'hyh' and price = 10 or product_no = 2;

NOT 条件
testdb=# select from products where name is not null;
testdb=# select from products where name not in ('hyh','chiness');

LIKE 条件
testdb=# select * from products where name like '%ch%';

IN 条件
testdb=# select * from products where price in (13,15);

NOT IN 条件
testdb=# select * from products where name not in ('hyh','chiness');

BETWEEN 条件
testdb=# select * from products where price between 10 and 15;

22.连接
内连接(INNER JOIN)
testdb=# select products.name,account.count from products inner join account on products.name = account.username;

左外连接(LEFT OUTER JOIN)
左外连接返回从“ON”条件中指定的左侧表中的所有行，只返回满足条件的另一个表中的行
testdb=# select products.name,account.username,account.count from products left outer join account on products.price = account.count;

右外连接(RIGHT OUTER JOIN)
右外连接返回从“ON”条件中指定的右侧表中的所有行，只返回满足条件的另一个表中的行
testdb=# select products.name,account.count from products right outer join account on products.name = account.username;

全连接(FULL OUTER JOIN)跨连接(CROSS JOIN)
全外连接从左表和右表中返回所有行。它将NULL置于不满足连接条件的位置
testdb=# select products.name,account.count from products full outer join account on products.name = account.username;

23.创建函数
create or replace function totalRecords()
returns integer as $total$
declare
total integer;
begin
select count(*) into total from products;
return total;
end;
$total$ language plpgsql;

24.调用函数
select totalRecords();

25.触发器

PostgreSQL触发器是一组动作或数据库回调函数，它们在指定的表上执行指定的数据库事件(即，INSERT，UPDATE，DELETE或TRUNCATE语句)时自动运行。触发器用于验证输入数据，执行业务规则，保持审计跟踪等
1.PostgreSQL在以下情况下执行/调用触发器：在尝试操作之前(在检查约束并尝试INSERT， UPDATE或DELETE之前)。或者在操作完成后(在检查约束并且INSERT，UPDATE或DELETE完成后)。或者不是操作(在视图中INSERT，UPDATE或DELETE的情况下)
2.对于操作修改的每一行，都会调用一个标记为FOR EACH ROWS的触发器。另一方面，标记为FOR EACH STATEMENT的触发器只对任何给定的操作执行一次，而不管它修改多少行。
3.您可以为同一事件定义同一类型的多个触发器，但条件是按名称按字母顺序触发。
4.当与它们相关联的表被删除时，触发器被自动删除

例子:
    创建两个表
    create table company(
    ID int primary key not null,
    name TEXT not null,
    age int not null,
    address char(50),
    salary real
   );

create table audit(
    emp_id int not null,
    entry_date text not null
);

    创建函数
    create or replace function auditlogfunc() returns trigger as $example_table$
begin
    insert into audit(emp_id, entry_date) values (new.id,current_timestamp);
    return new;
end;
$example_table$ language plpgsql;

    创建触发器
    create trigger example_trigger after insert on company
for each row execute procedure auditlogfunc();

    insert into company values(1,'小米科技',2,'北京清河',1234);
    #向company插入数据后，自动触发向audit表插入数据

26.索引
索引是用于加速从数据库检索数据的特殊查找表。数据库索引类似于书的索引(目录)。索引为出现在索引列中的每个值创建一个条目
数据库索引的重要特点:
索引使用SELECT查询和WHERE子句加速数据输出，但是会减慢使用INSERT和UPDATE语句输入的数据
您可以在不影响数据的情况下创建或删除索引
可以通过使用CREATE INDEX语句创建索引，指定创建索引的索引名称和表或列名称
还可以创建一个唯一索引，类似于唯一约束，该索引防止列或列的组合上有一个索引重复的索引

索引类型PostgreSQL中有几种索引类型，如B-tree，Hash，GiST，SP-GiST和GIN等。每种索引类型根据不同的查询使用不同的算法。默认情况下，CREATE INDEX命令使用B树索引

在products表name字段创建索引(单列索引)
create index products_index on products (name);

多列索引
create index account_index on account (username,count);

唯一索引，不允许表中出现重复的值
create unique index account_index on account (username);

删除索引
drop index account_index;

以下情况避免使用索引
应该避免在小表上使用索引。
不要为具有频繁，大批量更新或插入操作的表创建索引。
索引不应用于包含大量NULL值的列。
不要在经常操作(修改)的列上创建索引

查询表结构
\d products;

28.union子句
PostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行。
要使用UNION，每个SELECT必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

union all 子句
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

29.alter table
PostgreSQL ALTER TABLE命令用于添加，删除或修改现有表中的列。您还可以使用ALTER TABLE命令在现有表上添加和删除各种约束

增加字段
alter table company add gender char(1);

删除字段
alter table company drop gender;

30.截断表 TRUNCATE TABLE
PostgreSQL TRUNCATE TABLE命令用于从现有表中删除完整的数据。您也可以使用DROP TABLE命令删除完整的表，但会从数据库中删除完整的表结构，如果希望存储某些数据，则需要重新创建此表。
它和在每个表上使用DELETE语句具有相同的效果，但由于实际上并不扫描表，所以它的速度更快。此外，它会立即回收磁盘空间，而不需要后续的VACUUM操作。这在大表上是最有用的

truncate table company;

31.事务
事务具有以下四个标准属性，一般是由首字母缩写词ACID简称
原子性(Atomicity)：确保工作单位内的所有操作成功完成; 否则事务将在故障点中止，以前的操作回滚到其以前的状态
一致性(Consistency)：确保数据库在成功提交的事务时正确更改状态
隔离性(Isolation)：使事务能够独立运作并相互透明
持久性(Durability)：确保在系统发生故障的情况下，提交的事务的结果或效果仍然存在

以下命令用于控制事务：
BEGIN TRANSACTION：开始事务
COMMIT：保存更改，或者您可以使用END TRANSACTION命令
ROLLBACK：回滚更改

回滚事务
testdb=# begin;
BEGIN
testdb=# delete from account where count = 10;
DELETE 1
testdb=# rollback;
ROLLBACK

提交事务
testdb=# begin;
BEGIN
testdb=# delete from account where count = 10;
DELETE 1
testdb=# commit;
COMMIT
testdb=# select * from account;

32.锁
锁或独占锁或写锁阻止用户修改行或整个表。在UPDATE和DELETE修改的行在事务的持续时间内被自动独占锁定。这将阻止其他用户更改行，直到事务被提交或回退。
用户必须等待其他用户当他们都尝试修改同一行时。如果他们修改不同的行，不需要等待。 SELECT查询不必等待。
数据库自动执行锁定。然而，在某些情况下，必须手动控制锁定。手动锁定可以通过使用LOCK命令完成。它允许指定事务的锁类型和范围

testdb=# lock table account in access exclusive mode;

33.子查询
子查询或内部查询或嵌套查询是一个PostgreSQL查询中的查询，它可以嵌入到WHERE子句中。子查询用于返回将在主查询中使用的数据作为进一步限制要检索的数据的条件。子查询可以与SELECT，INSERT，UPDATE和DELETE语句以及运算符(如=，<，>，>=，<=，IN等)一起使用。
子查询必须遵循以下规则：
1.子查询必须括在括号中
2.子查询在SELECT子句中只能有一列，除非主查询中有多个列用于比较其所选列的子查询。ORDER BY不能用于子查询，尽管主查询可以使用ORDER BY。 GROUP BY可用于执行与子查询中的ORDER BY相同的功能。
3.返回多行的子查询只能与多个值运算符一起使用，例如：IN，EXISTS，NOT IN，ANY / SOME，ALL运算符。
4.BETWEEN运算符不能与子查询一起使用; 但是，BETWEEN可以在子查询中使用

select子查询
select * from company where id in (select id from company where salary > 45000);

insert子查询插入数据
insert into company_bkp
select * from company
where id in (select id from company);

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);

34.自增列
PostgreSQL具有数据类型smallserial，serial和bigserial; 这些不是真正的类型，而只是在创建唯一标识符列的标志以方便使用。这些类似于一些其他数据库支持的AUTO_INCREMENT属性。
如果您希望某列具有唯一的约束或是主键，则必须使用其他数据类型进行指定。
类型名称serial用于创建整数列。类型名称bigserial创建一个bigint类型的列。如果您期望在表的使用期限内使用超过2^31个标识符，则应使用bigserial。类型名称smallserial创建一个smallint列

create table company(
id serial primary key, # 自增id name text not null,
age int not null,
address char(50),
salary real);

35.授权
创建用户
create user hyh with password '123456';

grant all on company to hyh; 给用户hyh授予company表的所有权限

取消授权
revoke all on company from hyh;

删除用户
drop user hyh;

36.重启psql
systemctl restart postgresql-9.5.service

转载于:https://blog.51cto.com/haoyonghui/2376807

weixin_34336526

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
postgresql命令操作

1.连接数据库psql -h Server -p Port -U Username DatabaseName2.创建数据库postgres=# create database testdb;3.查看数据库postgres=# \l4.删除数据库postgres=# drop database testdb;5.进入数据库postgres=# \c testdb;6.列出当前库所有表tes...
复制链接

扫一扫