PostgreSql学习
课程详细
1.什么是PostgreSql?
2.初来乍到数据库
3.操作数据表
4.字段类型
5.添加表约束
6.INSERT语句
7.SELECT语句
8.WHERE语句
9.数据抽出选项
10.统计抽出数据
11.方便的函数
12.更新和删除
13.变更表结构
14.操作多个表
15.使用视图
16.使用事务
17.DELETE语句
1.什么是PostgreSql?
PostgreSQL 是一个自由的对象-关系数据库服务器(数据库管理系统),是从伯克利写的 POSTGRES 软件包发展而来的。经过十几年的发展, PostgreSQL 是世界上可以获得的最先进的开放源码的数据库系统, 它提供了多版本并发控制,支持几乎所有SQL语句(包括子查询,事务和用户定义类型和函数),并且可以获得非常广阔范围的(开发)语言绑定 (包括C,C++,Java,perl,python,php,nodejs,ruby)。
面向关系的数据库:
1 Oracle
2 MySql
3 SQLServer
4 PostgreSql
NoSql(非关系型)
1 MongoDB
2 Redis
2 安装 版本
$ sudo apt-get install postgresql
$ psql --version
3 简单操作
$ sudo su postgres
$ psql --version
$ psql -l
$ createdb komablog
$ psql -l
$ psql komablog
help
\h
?
\l
\q
$ psql komablog
select now();
select version();
\q
$ dropdb komablog
$ psql -l
4 操作数据表
create table / drop table / psql使用
$ sudo su postgres
$ createdb komablog
$ psql -l
$ psql komablog
create table posts (title varchar(255), content text);
\dt
\d posts
alter table posts rename to komaposts;
\dt
drop table komaposts;
\dt
\q
$ nano db.sql
create table posts (title varchar(255), content text);
$ psql komablog
\i db.sql
\dt
5 字段类型
PostgreSql的基础数据类型
数值型:
integer(int)
real
serial
文字型:
char
varchar
text
布尔型:
boolean
日期型:
date
time
timestamp
特色类型:
Array
网络地址型(inet)
JSON型
XML型
‘
6 添加表约束
db.sql
create table posts (
id serial primary key,
title varchar(255) not null,
content text check(length(content)) > 8),
is_draft boolean default TRUE,
is_del boolean default FALSE,
created_data timestamp default ‘now‘
);
– 说明/*
约束条件:
not null:不能为空
unique:在所有数据中值必须唯一
check:字段设置条件
default:字段默认值
primary key(not null, unique):主键,不能为空,且不能重复
*/
7 INSERT 语句
$ psql komablog
\dt
\d posts
SQL部分
insert into posts (title, content) values (’’, ‘’);
insert into posts (title, content) values (NULL, ‘’);
insert into posts (title, content) values (‘title1’, ‘content11’);
select * from posts;
insert into posts (title, content) values (‘title2’, ‘content22’);
insert into posts (title, content) values (‘title3’, ‘content33’);
select * from posts;
8 SELECT 语句
init.sql
create table users (
id serial primary key,
player varchar(255) not null,
score real,
team varchar(255)
);
insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');
$ psql komablog
\i init.sql
\dt
\d users
select * from users;
\x
( Expanded display is on / off )
select * from users;
\x
select * from users;
select player, score from users;
9 WHERE 语句
使用where语句来设定select,update,delete语句数据抽出的条件。
select * from users;
select * from users where score > 20;
select * from users where score < 30;
select * from users where score > 20 and score < 30;
select * from users where team = ‘勇士’;
select * from users where team != ‘勇士’;
select * from users where player like ‘阿%’;(后边n个字符)
select * from users where player like ‘阿_’; (后边一个字符)
10 数据抽出选项
select 语句再抽出数据时,可以对语句设置更多的选项,以得到想要的数据。
1 order by (排序)
2 limit (限制数量)
3 offset (前后补偿)
select * from users order by score asc;
select * from users order by score desc;
select * from users order by team;
select * from users order by team, score;
select * from users order by team, score desc;
select * from users order by team desc, score desc;
select * from users order by score desc limit 3;
select * from users order by score desc limit 3 offset 1;
select * from users order by score desc limit 3 offset 2;
select * from users order by score desc limit 3 offset 3;
11 常用函数
1 length (求长度)
2 concat (concat(player, ‘/’, team) : 字符串合并)
3 alias
4 substring (substring(team, 1, 2):取字符串的子字符串,从位置1到位置2)
5 random
select player, length(player) from users;
select player, concat(player, ‘/’, team) from users;
select player, concat(player, ‘/’, team) as “球员信息” from users;
select substring(team, 1, 1) as “球队首文字” from users;
select concat(‘我’, substring(team, 1, 1)) as “球队首文字” from users;
select random();
select * from users order by random();
select * from users order by random() limit 1;
12 更新和删除
1 update [table] set [field=newvalue,…] where …
2 delete from [table] where …
update users set score = 29.1 where player = ‘阿詹’;
update users set score = score + 1 where team = ‘勇士’;
update users set score = score + 100 where team IN (‘勇士’, ‘骑士’);
delete from users where score > 30;
13 变更表结构
1 alter table [tablename] …
2 create index …
3 drop index …
\d users;
alter table users add fullname varchar(255);
\d users;
alter table users drop fullname;
\d users;
alter table users rename player to nba_player;
\d users;
alter table users alter nba_player type varchar(100);
\d users;
create index nba_player_index on users(nba_player);
\d users;
drop index nba_player_index;
\d users;
14 操作多个表
表结合查询的基础知识
renew.sql
create table users (
id serial primary key,
player varchar(255) not null,
score real,
team varchar(255)
);
insert into users (player, score, team) values
('库里', 28.3, '勇士'),
('哈登', 30.2, '火箭'),
('阿杜', 25.6, '勇士'),
('阿詹', 27.8, '骑士'),
('神龟', 31.3, '雷霆'),
('白边', 19.8, '热火');
create table twitters (
id serial primary key,
user_id integer,
content varchar(255) not null
);
insert into twitters (user_id, content) values
(1, '今天又是大胜,克莱打的真好!'),
(2, '今晚我得了60分,哈哈!'),
(3, '获胜咱不怕,缺谁谁尴尬.'),
(4, '明年我也可能转会西部'),
(5, '我都双20+了,怎么球队就是不胜呢?'),
(1, '明年听说有条大鱼要来,谁呀?');
SQL实行
$ dropdb komablog;
$ createdb komablog;
$ psql komablog;
\i renew.sql
select * from users;
select * from twitters;
select users.player, twitters.content from users, twitters where users.id = twitters.user_id;
select u.player, t.content from users as u, twitters as t where u.id = t.user_id;
select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
15 使用视图
视图概念
视图(View)是从一个或多个表导出的对象。视图与表不同,视图是一个虚表,即视图所对应的数据不进行实际存储,数据库中只存储视图的定义,在对视图的数据进行操作时,系统根据视图的定义去操作与视图相关联的基本表。
小马解释
视图就是一个SELECT语句,把业务系统中常用的SELECT语句简化成一个类似于表的对象,便于简单读取和开发。
使用数据库视图(view)
1 create view …
2 drop view …
select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
create view curry_twitters as select u.player, t.content from users as u, twitters as t where u.id = t.user_id and u.id = 1;
\dv
\d curry_twitters
select * from curry_twitters;
drop view curry_twitters;
\dv
16 使用事务
数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务,必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。事务是数据库运行中的逻辑工作单位,由DBMS中的事务管理子系统负责事务的处理。
知识点
PostgreSql数据库事务使用
1 begin
2 commit
3 rollback
select * from users;
begin;
update users set score = 50 where player = ‘库里’;
update users set score = 60 where player = ‘哈登’;
commit;
select * from users;
begin;
update users set score = 0 where player = ‘库里’;
update users set score = 0 where player = ‘哈登’;
rollback;> select * from users;
代码链接: https://gitee.com/komavideo/LearnPostgreSql.
视频链接: https://www.youtube.com/playlist?list=PLliocbKHJNws0zsx5Akn1DVoPznFYYGA9.
17 DELETE语句
delete from users where player = ‘哈登’;
纯学习,我只是个搬运工!