PostgreSql

关系型数据库，相较于mysql慢

安装

安装postgresql

lucas@lucas-virtual-machine:~$ sudo apt-get -y install postgresql

#安装图形化管理工具

lucas@lucas-virtual-machine:~$ sudo snap install postbird

#启动psql，使用 -i 参数时，会启动一个新的 Shell 进程，参数 -i 允许你以目标用户的身份进入一个新的登录 Shell 环境，从而在该环境下执行命令。

sudo -u postgres -i
psql

#创建数据库my_first_db
postgres=# create database my_first_db;    
CREATE DATABASE
#查看当前有哪些数据库，q退出
postgres=# \l    
#选择数据库my_first_db
postgres=# \c my_first_db     
You are now connected to database "my_first_db" as user "postgres".
#创建数据表
my_first_db=# create table films(id integer, name text, release_year integer);    
CREATE TABLE
#查看films表结构
my_first_db=# \d films    
                  Table "public.films"
    Column    |  Type   | Collation | Nullable | Default 
--------------+---------+-----------+----------+---------
 id           | integer |           |          | 
 name         | text    |           |          | 
 release_year | integer |           |          | 

#查看所有表
my_first_db=# \dt        
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | films | table | postgres
(1 row)
#表中插入值
my_first_db=# insert into films(id, name, release_year) values(1, 'The Matrix', 1999);
INSERT 0 1
my_first_db=# insert into films(id, name, release_year) values(2, 'Monster’s Inc.', 2001);
INSERT 0 1
my_first_db=# insert into films(id, name, release_year) values(3, 'Call Me By Your Name', 2017);
#查询
my_first_db=# select * from films;
 id |         name         | release_year 
----+----------------------+--------------
  1 | The Matrix           |         1999
  2 | Monster’s Inc.       |         2001
  3 | Call Me By Your Name |         2017
(3 rows)
#加主键pk限制
my_first_db=# alter table films add constraint pk_id primary key (id);
ALTER TABLE
#修改表，增加一列score，默认null
my_first_db=# alter table films add column score integer unique default null;
ALTER TABLE
#更新score
my_first_db=# update films set score = 9.9 where name = 'The Matrix';
UPDATE 1

case...then用法示例

SELECT name,
       CASE
           WHEN genre = 'romance' THEN 'Chill'
           WHEN genre = 'comedy' THEN 'Chill'
           ELSE 'Intense'
       END AS Mood    #取别名
FROM your_table_name;

#计算score大于200的用户的总分1与所有用户的总分的比值
SELECT (SUM(CASE WHEN score > 200 THEN score ELSE 0 END) * 100.0 / SUM(score)) AS score_percentage
FROM hacker_news;

根据之前筛选的’source‘列，统计url的数量，并添加到新的一列（此处主要考察case...then和用数字引用列group by 2）

select count(*),
  case
    when url like '%github.com%' then 'github'
    when url like '%medium.com%' then 'medium'
    when url like '%nytimes.com%' then 'new york times'
    else 'other' 
  end as 'source'
from hacker_news group by 2;

内联：

 left外联：

 事务：

acid：原子性，一致性，隔离性，持久性

begin;
begin transaction; #两种都是开启事务

commit;
end transaction; #两种都是关闭、提交事务

rollback; #回滚

savepoint 断点名; #设置断点
rollback to 断点名; #回滚到断点 

事务并发问题：

脏读：当一个事务正在访问数据并且对数据进行了修改，但是这个修改还没提交到数据库中，此时另一个事务也访问这个数据。另一个事务读到的就是‘脏数据’

不可重复读：一个事务A多次读取数据，但不修改数据，事务B修改了数据，因为事务A没有修改数据，前后两次读数据的结果应该是一样。一个事务内两次读取的数据不一样，就是不可重复读

幻读：与不可重复读类似，他是事务T1读取了几行数据后，事务T2插入了一些数据，后续T1查询时多了一些原本不存在的记录，就跟中了幻觉一样

幻读与不可重复读的区别是，幻读是插入或删除了数据，而不可重复读是修改数据。解决不可重复读是要锁住整行数据不让其他事务同时处理，而要解决幻读则需锁住整张表

隔离级别	脏读	不可重复读	幻读
read uncommited	×	√	√
read commited	×	√	√
repeatable read	×	×	√
serializable	×	×	×

查看和设置隔离级别：

SHOW transaction_isolation; #查看隔离级别
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; #读取未提交
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; #读取已提交
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ; #可重复读
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; #序列化

分布式事务：两阶段提交：

step1：（增删改后），预提交，数据库可以保存预提交的数据，即使宕机、重启数据也不会丢失 prepare transaction 事务名;

step2：最终提交，commit prepared 事务名;

并发控制：模型有基于锁的并发控制、基于多版本的并发控制

基于锁的并发控制（悲观机制）：共享锁（s锁）、排他锁（x锁），加锁对象大小：锁粒度

基于多版本的并发控制（mvcc）（乐观机制）：锁 是一种预防性机制, 写会阻塞读, 读会阻塞写; MVCC 是一种后验性机制, 等到提交的时候才检查是否有冲突。

视图：本质上就是一个查询语句，是一个虚拟表。可以简化用户操作，保护机密数据

CREATE or replace VIEW myview AS
SELECT empo, ename, job, deptno
from emp
WHERE deptno= 20
with check option; #限制不能往视图中随意添加数据

SELECT * FROM myview; #查询视图
insert into myview (empno, ename, job, deptno) values(999, 'lilili', 'clerk', '20'); 在视图中插入数据，表中也会同步插入

create or replace view myview02
as
select e.empno, e.ename, e.sal, d.deptno, d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000;

 存储：在sql服务器端有个类似小程序的，会把你所有的命令一次执行。不再需要一条命令就访问一次sql服务器。

create procedure mypro01(name varchar(50))
begin
    if name is null or name = "" then
        select * from emp;
    else
        select * from emp where name like concat('%', name, '%');
    end if;
end;

drop procedure mypro01; #删除存储过程

call mypro01(null); #调用存储过程
call mypro01('R');

create procedure mypro02(in name varchar(50), out num int(3)) #in可省略，out表示返回值
begin
    if name is null or name = "" then
        select * from emp;
    else
        select * from emp where name like concat('%', name, '%');
    end if;
    select found_rows() into num; #found_rows()返回查询结果的条数
end;

call mypro01(null, @num); #调用存储过程
select @num;

更新于2023-08-15 21：50