关系型数据库,相较于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