postgrepsql的使用

启动数据库并设置开机启动

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10.service

登录postgrepsql

image.png

su - postgres  /  sudo -i -u postgres
psql
远程连接  用户名 指定数据库
psql -h 远程ip  -p 5432 -U postgress testdb

来设置密码：

ALTER USER postgres WITH PASSWORD '密码';

退出：\q

postgresql是不用密码不支持远程登录的。我们需要修改配置文件

vi /var/lib/pgsql/10/data/pg_hba.conf

image.png

vi /var/lib/pgsql/10/data/postgresql.conf

image.png

重启postgresql
systemctl restart postgresql-10

创建数据库
CREATE DATABASE testdb;
创建用户
CREATE USER testuser CREATEDB LOGIN PASSWORD 'testpassword';

将testdb所有权限赋给用户testuser

GRANT ALL ON DATABASE testdb TO testuser;

\l  查看现有数据库及用户
\du  查看现有用户
\c + 数据库名 来进入数据库：
\d tablename 查看表格信息：
删除数据库
drop database testdb;
删除用户
drop role testuser;

创建表格
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);
删除表格
DROP TABLE table_name;

添加数据
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

删除数据
DELETE FROM table_name WHERE [condition];

修改数据
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

查看数据
SELECT * FROM table_name;
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1]
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
SELECT FROM table_name WHERE column LIKE 'XXXX%';
或者
SELECT FROM table_name WHERE column LIKE '%XXXX%';
或者
SELECT FROM table_name WHERE column LIKE 'XXXX_';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX';
或者
SELECT FROM table_name WHERE column LIKE '_XXXX_';

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

https://blog.csdn.net/rudy5348/article/details/79299162