Postgresql:是一种特性非常齐全的自由软件的对象-关系型数据库管理系统
Postgresql 和 Mysql 都依赖于 SQL(结构化查询语言)
本实验同时适用于CentOS7、8,Rocky8、9
实验环境
- Rocky 9
- 本地yum
首先安装postgresql
yum install postgresql*
设置自启
systemctl enable postgresql
初始化数据库
postgresql-setup --initdb #初始化数据库
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log #初始化完成
初始化完成后启动数据库
systemctl start postgresql
先切换到postgres用户,使用该用户登录数据库,并设置允许root本地和远程登录
su - postgres #切换postgres用户
[postgres@localhost ~]$ psql #登录数据库
psql (13.7)
Type "help" for help.
#登录成功
postgres=# \du #查看数据库用户
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# create role root superuser password '123456' login REPLICATION CREATEDB CREATEROLE;
#create role为创建用户 root为用户名 superuser为超级管理员 login REPLICATION CREATEDB CREATEROLE为权限;
CREATE ROLE #创建成功
postgres=# create database root owner root; #创建名为root的库 owner指定库的所有者root;
CREATE DATABASE #创建成功
postgres=# exit #退出
[postgres@localhost ~]$ su #切换root
Password: #输入密码
vim /var/lib/pgsql/data/postgresql.conf #编辑postgresql主配置文件
60 listen_addresses = '*' #允许监听ip
vim /var/lib/pgsql/data/pg_hba.conf #编辑远程配置
18 host all root 192.168.100.0/24 md5 #添加
# host表示开启远程 all为允许远程登录的数据库 root为允许远程登录的用户 [允许登录的ip网段] md5为免密登录
systemctl restart postgresql #重启postgresql
数据库命令操作
psql #本地登录数据库
psql (13.7)
Type "help" for help.
root=# \l #查看目前所有数据库
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
root | root | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows) #共有4个数据库
root=# create database userdb; #创建名为userdb的数据库
CREATE DATABASE #创建成功
root=# \l #再次查看数据库
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
root | root | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
userdb | root | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
(5 rows) #共有5个数据库
root=# drop database userdb; #删除名为userdb的数据库
DROP DATABASE #删除成功
数据表命令操作
root=# \c userdb; #选择userdb数据库
You are now connected to database "userdb" as user "root". #选择成功
userdb=# create table userinfo( #创建名为userinfo的表(
userdb(# id serial PRIMARY KEY, #id为字段名 serial为数据类型 PRIMARY KEY为主键,
userdb(# name varchar(10),
userdb(# birthday date,
userdb(# password varchar(100)); # );结束
CREATE TABLE #创建成功
userdb=# \d #查看所有表
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+-------
public | userinfo | table | root
public | userinfo_id_seq | sequence | root
(2 rows)
userdb=# \d userinfo; #查看userinfo表信息
Table "public.userinfo"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+--------------------------------------
id | integer | | not null | nextval('userinfo_id_seq'::regclass)
name | character varying(10) | | |
birthday | date | | |
password | character varying(100) | | |
Indexes:
"userinfo_pkey" PRIMARY KEY, btree (id)
userdb=# drop table userinfo; #删除userinfo表
DROP TABLE #删除成功
数据表数据命令操作
userdb=# insert into userinfo values ('1','user1','2005-6-1',md5('user1')); #插入表数据
# userinfo为表名 values()为数据 顺序对应字段 md5为md5加密
INSERT 0 1 #插入成功
userdb=# select * from userinfo; #查询userinfo表数据
id | name | birthday | password
----+-------+------------+----------------------------------
1 | user1 | 2005-06-01 | 24c9e15e52afc47c225b757e7bee1f9d
(1 row) #共1条记录
欢迎交流学习!!!