PostgreSQL

PostgreSQL

sql语言范畴

• DDL: DROP、CREATE、ALTER等语句。
• DML: INSERT、UPDATE、DELETE语句。
• DQL: SELECT语句o
• DCL: GRANT、REVOKE、COMMIT、ROLLBACK等语句。

DDL 数据库定义语言

• 定义数据库对象
• 定义：数据库表，数据库类型，字段类型
• 删除数据库表，删除字段类型等

DML 数据库操作语言

• 操作数据库中的数据
• 插入数据，更新数据，删除数据

DQL 数据库查询语言

• 查询，检索数据库

DCL 数据库控制语言

• 分配角色，控制事务提交

使用SQL语言操作数据库对象

创建数据库对象

-- 创建数据库
create database postData;

-- 创建数据库，并指定参数内容
	-- owner = postgres 指定所有者为 postgres
	-- encoding 指定编码
create database postgresDemo with owner = postgres encoding = 'utf-8';

修改数据库对象

-- 修改数据库
alter database postgresDemo rename to newPostData;

-- 修改数据库链接数为 20
alter database postgresDemo connection limit 20;

删除数据库对象

-- 删除数据库
drop database newPostData

使用SQL语言操作数据表对象

创建数据表对象

-- 创建表：
CREATE TABLE table_name(
   city     varchar(80),
   temp_lo  int,
   temp_hi  int,
   prcp     real,
   date     date
);

-- 查看表格：
SELECT * FROM cityName

新增数据表对象

添加字段语法:ALTER TABLE <表名> ADD COLUMN <列的定义>

-- 新增一个字段 news
alter table cityName add column news varchar(100);

-- 新增一个字段 textContent
alter table "stuList" add column "textContent" VARCHAR(6);
-- 在stuList 表新增一个字段。textCons 默认为 home, 不能为空
alter table "stuList" add column "textCons" VARCHAR(6)  DEFAULT 'home' not null;

修改数据表对象

-- 修改表名称
alter table table_name rename to cityName

-- 修改表中字段的名称
alter table cityName rename temp_lo to hh; 

-- 修改表中字段的类型
alter table cityName alter column temp_hi type varchar(50);

-- 修改表中字段的值
-- 修改表"orderForm" "courierName"为[极兔快递] 改为 [极兔速递]
update "orderForm" set "courierName"='极兔速递' where "courierName"='极兔快递'

删除数据表对象

删除字段语法:ALTER TABLE <表名> DROP COLUMN <列名>

-- 删除字段
alter table cityName drop column temp_hi;

-- 删除表：
DROP TABLE cityName;

-- 删除表(在删除之前，会先判断是否存在，存在则删除； 不存在则不执行)
drop table if exists cityname;

查询数据表对象

-- 查询所有字段使用*；
SELECT * FROM book

-- 查询系统当前时间
SELECT CURRENT_TIMESTAMP; 

-- 模糊查询，李...
SELECT * FROM "orderForm" WHERE "signName" like '李%'

-- 模糊查询，李* 两个字的
SELECT * FROM "orderForm" WHERE "signName" like '李_'

PostgreSQL常用数据类型介绍

数值类型

1. 整数类型

• smallint // 小范围整数
• int(integer) // 普通大小整数

2. 任意精度浮点数类型：

• real // 6位十进制数字精度
• numeric(m,n) // 任意精度类型 m代表最大长度，n代表精度
  • 小数点，不计算 numeric(5,2) 表示：最长5位，小数点后面两位
  • 注意精度，会四舍五入，比如 numeric(3,2) => 9.888 会变成 9.89

日期与时间类型

类型名称	含义	存储需求	例如
time	只用于一日内时间	8字节	10:05:05
date	只用于日期	4字节	1987-04-04
timestamp	日期和时间	8字节	1987-04-04 10:05:05

字符串类型

类型名称	说明
char(n) / character(n)	固定长度字符串，不足补空白
varchar(n)	变长字符串，有长度限制
text	变长字符串，无长度限制

如何选择正确的数据类型

主要目的：优化存储，提高数据库性能

• 正确使用整数类型和浮点数类型
• 日期与时间类型
• CHAR与VARCHAR之间的特点与选择

PostgreSQL运算符介绍

算术运算符

• 加(+)
• 减(-)
• 乘(*)
• 除(/)
• 求余（模运算，%）

比较运算符

运算符	作用
=	等于
<>(!= )	不等于
<=	小于等于
>=	大于等于
>	大于
<	小于
least	在有两个或者多个参数时，返回最小值
greatest	在有两个或者多个参数时，返回最大值
between and	判断一个值是否落在两个值之间
in	判断一个值是否是 IN 列表中的任意一个值
like	通配符匹配

-- 比较运算符
select 1=2,'2'=2
-- between and
SELECT 5 BETWEEN 6 and 9, 3 BETWEEN 1 and 6

-- IN 与 not in
select 2 in (2,4,3,2), 8 in (2,2)
SELECT 8 in (2,6),2 not in(5,6)

-- like 用于模糊匹配
-- %百分号通配符，表示任意多个字符，也可以是0个
-- _ 下划线通配符， 表示匹配一个字符
SELECT 'asdf' like 'asd','asdf' like 'a%','asd' like '_s_','asd' not like 'a%'

逻辑运算符

• NOT(逻辑非)
• AND(逻辑与)
• OR(逻辑或)

-- 在程序当中
	-- 1 和 y 当真来处理
	-- 0 和 n 当假来处理
-- NOT 的使用
select not '1', not 'y',not '0',not 'n'

-- and 的使用
select '1' and 'y', '1' and '0'

-- or  使用
select '1' or 'y', '1' or'0'

函数，索引，试图

PostgreSQL常用函数介绍

常用的数值函数

函数名称	函数作用
avg()	返回某列的平均值
count()	返回某列的行数
max()	返回某列的最大值
min()	返回某列的最小值
sum()	返回某列的值之和

-- 常用函数
select max(z) from temp;
select min(z) from temp;
select count(z) from temp

常用的字符串函数

函数名称	函数作用
length(s)	计算字符串长度
concat(s1,s2,...)	字符串合并函数
ltrim(s)/rtrim(s)/trim(s)	删除字符串空格函数(两边)
replace(s,S1,s2)	字符串替换函数
substring(s,n,len)	获取子串函数(即:字符串截取)

-- 常用的字符串函数
select * from  stringDemo
select ch, length(ch) from stringDemo
select ch,vch, concat(ch,vch,t) from stringDemo

常用的日期和时间函数

函数名称	函数作用
extract(type from d)	获取日期指定值函数
current_date	获取当前日期函数
current_time	获取当前时间函数
now()	获取当前日期时间函数

-- 常用的日期和时间函数
select CURRENT_DATE,CURRENT_TIME,now();

-- 分开 获取日期中的 年，月，日
select extract(year form times),extract(month form times),extract(day form times) from stringDemo;

PostgreSQL自定义函数

创建函数的语法

1. 语法

//create or replace function   // 声明创建函数  or replace 表示，有这个函数则覆盖，没有直接创建
create function                // 声明创建函数        
  add(integer,integer)        // 定义函数名称，参数类型
returns integer               // 定义函数返回值
  AS 'select $1 + $2;'         // 定义函数体
language sql                   // 用以实现函数的语言的名字
returns null on null input;   // 定义参数为NULL时处理情况

函数的创建

-- 自定义函数
-- 创建
CREATE FUNCTION add(INTEGER,INTEGER) RETURNS INTEGER
	as 'SELECT $1 + $2;'
LANGUAGE SQL
returns null on null input;
-- 使用
select add(5,6)

函数的删除

-- 删除自定义函数
drop FUNCTION add(INTEGER,INTEGER)

PostgreSQL数据库索引介绍

索引的作用

• 提高数据库，检索的效率

索引的分类

索引名称	使用场景
B-tree 索引	适合处理那些能够按顺序存储数据
Hash 索引	只能处理简单的等于比较
GiST 索引	一种索引架构
GIN 索引	反转索引，处理包含多个值的键

索引的创建和删除

• 语法： create index 索引名称 on 表名(字段)

-- 创建索引 B-tree
create index asdf on stringDemo(ch)
-- 删除索引
drop index asdf;

PostgerSQL数据库视图介绍

视图的创建

• 语法： create view 试图名称as ...

-- 创建试图
create view asds as SELECT ch from stringDemo;

-- 使用试图
select * from asds;

视图的删除

-- 删除试图
drop view asdasds;

视图的作用

• 简单化
• 安全性
• 逻辑数据独立性

PostgreSQL数据操作和数据表，约束介绍

1