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 table_name rename to cityName
-- 修改表中字段的名称
alter table cityName rename temp_lo to hh;
-- 修改表中字段的类型
alter table cityName alter column temp_hi type varchar(50);
-- 新增一个字段
alter table cityName add column news varchar(100);
删除数据表对象
-- 删除字段
alter table cityName drop column temp_hi;
-- 删除表：
DROP TABLE cityName;
-- 删除表(在删除之前，会先判断是否存在，存在则删除； 不存在则不执行)
drop table if exists cityname;
PostgreSQL常用数据类型介绍
数值类型
- 整数类型
- smallint // 小范围整数
- int(integer) // 普通大小整数
- 任意精度浮点数类型：
- 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自定义函数
创建函数的语法
- 语法
//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
