PostgreSQL的安装配置及使用

一.安装

地址：https://www.postgresql.org/

选择合适的版本下载

二.配置

mac 配置 pg_hba.conf 文件

open .bash_profile
export PATH=$PATH:/Library/PostgreSQL/14/bin

cd /Library/PostgreSQL/14
sudo -u postgres bash
cd data
vim  pg_hba.conf

进入编辑模式：shift + # 然后输入 cc，加入下面这行

host    all             all             0.0.0.0/0            scram-sha-256

点击 ESC 退出编辑模式，并保存退出

：wq

退出 postgres 角色

三.使用

1.创建数据库

create database db_test;//创建数据库
alter database db_test rename to db_test1;//修改数据库名称
alter database db_test1 connection limit 20;//修改数据库配置
drop database db_test1;//删除数据库

错误提示 ERROR: database "db_test" is being accessed by other usersDETAIL: There is 1 other session using the database.SQL 状态: 55006

解决方法：右击 - 断开数据库连接，然后重新执行语句

2.创建表

数据库 - 架构 - public - 表 - 右击创建表

创建表

create table student (
    id int,
    name varchar(30),
    birthday date,
    score numeric(5,2)
);

修改表名

alter table student rename to student1;

修改表中的字段名

alter table student1 rename id to id1;

修改字段类型

alter table student1 alter column name type varchar(40);

删除字段

alter table student1 drop column birthday;

添加字段

alter table student1 add column address varchar(200);

删除数据表

drop table student1;

drop table if exists student1;//删前判断一下

索引

1. 创建索引

create index name_index on student(name);

2. 删除索引

drop index name_index;

插入数据操作

insert into student values(1,'zhangsan','1990-01-01',3.85);

insert into student values(1,'ls','1990-01-01');

insert into student (id,name,birthday) values (2,'wh','1990-02-02');

insert 批量插入

insert into student (id,name,birthday) values 
(3,'wh','1990-02-02'),
(4,'wh2','1990-02-04'),
(5,'wh3','1990-02-05');

select 批量插入

insert into student_new select * from  student;//所有

insert into student_new (id,name) select id,name from student;//指定字段

更新字段

update student set name = 'wh0' where id = 2; 

update student set score = 2; //更新所有

删除数据

delete from student where name = 'ls';

清空表中数据

delete from student

truncate table student;

区别： delete 速度相对较慢，可以回滚，记录删除操作日志

truncate 速度快，不可以回滚，不记录删除操作日志

创建主键

create table emp (
    id int primary key,//列级约束
    name varchar(30),
    salary numeric(5,2)
);


create table emp (
    id int,
    name varchar(30),
    salary numeric(9,2),
    constraint pk_emp primary key(id)//表级约束
);

列级约束在创建主键时不可以指定名称，表级约束可以

创建外键

create table dept (
    id int primary key,
    name varchar(40)
);

insert into dept values (1,'开发部');

insert into dept values (2,'测试部');

create table emp1(
    id int primary key,
    name varchar(30),
    salary numeric(9,2),
    deptId int,
    constraint fk_emp1_dept foreign key(deptId) references dept(id)
)

insert into emp1 values(1,'zhangsan',3000,1);

insert into emp1 values(2,'lisi',3000,2);

非空 ：not null

唯一: unique

默认值： default

select 查询基本语法

select 
**  //*所有或者字段名
from
** //表
where  //查询条件
group by  //数据分组
order by  //排序
limit  //限制数量

建两张表演示一下

create table dept (
    d_no int primary key,   //部门编号
    d_name varchar(30),     //部门名称
    d_location varchar(300) //地理位置
);

insert into dept values
(10,'开发部','北京市海淀区'),
(20,'测试部','北京市东城区'),
(30,'销售部','上海市'),
(40,'财务部','广州市'),
(50,'运维部','武汉市'),
(60,'集成部','南京市');

create table employee (
    e_no int primary key,            //雇员编号
    e_name varchar(30) not null,     //雇员名称
    e_gender char(2) not null,       //性别 
    dept_no int,                     //部门编号
    e_job varchar(50) not null,      //职业
    e_salary numeric(9,2),           //薪资
    e_hireDate date,                 //入职时间
    constraint fk_emp_deptno foreign key (dept_no) references dept(d_no)
);

insert into employee values
(100,'zs','f',10,'开发工程师',5000,'2010-01-01'),
(101,'vs','f',10,'开发工程师',6000,'2012-01-01'),
(102,'xfsw','f',10,'开发经理',8000,'2008-01-01'),
(103,'vd','m',20,'测试工程师',4500,'2013-01-01'),
(104,'zr','f',20,'测试工程师',5000,'2011-01-01'),
(105,'bt','m',20,'测试经理',6000,'2009-01-01'),
(106,'se','f',30,'销售人员',3000,'2014-01-01'),
(107,'zrb','m',30,'销售人员',5000,'2010-01-01'),
(108,'nr','m',30,'销售经理',9000,'2006-01-01'),
(109,'qw','m',30,'销售高级经理',12000,'2003-01-01'),
(110,'ht','m',40,'财务人员',3000,'2011-01-01'),
(111,'yj','f',40,'财务人员',3500,'2010-01-01'),
(112,'zr','m',40,'财务经理',5000,'2008-01-01'),
(113,'ht5','f',10,'实习工程师',null,'2015-01-01'),
(114,'as','f',null,'实习工程师',null,'2015-01-01'),
(115,'bhr','f',null,'实习工程师',null,'2015-01-01')
;

查询部门编号为10和20的雇员

select e_no,e_name,dept_no from employee where dept_no in (20,30);

查询部门编号不为10和20的雇员

select e_no,e_name,dept_no from employee where dept_no not in (20,30);

模糊查询

select e_no,e_name,e_hireDate from employee where e_name like 'z%';//'z_'为两个字的

查询空值

select e_no,e_name,e_salary from employee where e_salary is null;

排序（默认升序 asc，降序desc）

select e_no,e_name,e_salary,e_hireDate from employee order by e_salary asc,e_hireDate desc;

空值排在前面(默认 nulls last)

select e_no,e_name,e_salary from employee order by e_salary asc nulls first

分页查询

select * from employee limit 5 offset ((页码 - 1) * 5);

内连查询（inner join 查询数据 空值不筛选出来）

select e_no,e_name,dept_no,d_no,d_name,d_location from employee inner join dept on dept_no = d_no;

left join 左连接查询，空值也显示

查询部门编号为10的

select e_no,e_name,dept_no,d_no,d_name,d_location from employee left outer join dept on dept_no = d_no where dept_no = 10;

exists 子查询 ，查询开发部的所有人员

select * from employee where exists (select d_no from dept where d_name = '开发部' and d_no = dept_no);

标量子查询（ || 表示字符串的拼接）

select e_no,e_name,(select d_name | | '' || d_location from dept where dept_no = d_no) as address from employee;

合并查询（如果表2中没有的字段需要用 null 占位 ）

select e_no,e_name,dept_no,e_salary from employee where dept_no in (20,30)
union all
select e_no,e_name,dept_no ,e_salary from employee where e_salary > 5000;

合并查询并去掉重复的

select e_no,e_name,dept_no,e_salary from employee where dept_no in (20,30)
union 
select e_no,e_name,dept_no ,e_salary from employee where e_salary > 5000;