【PostgreSql】基础知识点总结

最新推荐文章于 2024-05-24 17:23:44 发布

Yirschen

最新推荐文章于 2024-05-24 17:23:44 发布

阅读量725

点赞数

分类专栏： PostgreSql 文章标签： sql 数据库 postgresql

本文链接：https://blog.csdn.net/Yirschen/article/details/105401701

版权

PostgreSql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

操作数据库对象

1.创建数据库对象：create database 数据库名;
create database db_jikexueyuan	#数据库名
    with ENCODING='utf-8'		#编码为utf-8
         OWNER=postgres			#所有者
         CONNECTION LIMIT=10;	#限制连接数为10

2.修改数据库对象（其中的一些参数）：alter database ...
(1)alter database db_jikexueyuan	#修改数据库名称
        rename to db_jikexueyuan1;
(2)alter database db_jikexueyuan1	#修改数据库连接数限制
        with CONNECTION LIMIT = 100;
或 alter database db_jikexueyuan1 CONNECTION LIMIT 100;

3.删除数据库对象：drop database 数据库名;

操作数据表对象

1.创建数据表对象：create table 表名 ...
create table student (
	id int,
	name varchar(30),
	birthday data,
	score numeric(5,2)
);

2.修改数据表对象：alter table 表名 ...
alter table student rename to student1;	#修改表名。修改表名student为student1
alter table student1 rename id to bh;	#修改字段名。修改表student1的字段id为bh
alter table student1 alter column name type varchar(40);	#修改字段数据类型。修改name字段的数据类型为VARCHAR(40)
alter table student1 drop column birthday;			#删除字段。删除字段birthday，同时会删除对应的信息
alter table student1 add column address varchar(200);	#添加字段。添加address字段，类型为VARCHAR(200)				 

3.删除数据表对象：drop table 表名 ...
drop table student1;	#删除数据表
drop table if exists student1;	#只有数据表存在时才执行删除操作

数据类型

整数类型：
smallint		//小范围整数，取值范围：-32768~32767
int(integer)	//普通大小整数-2147483648~2147483647

任意精度浮点数类型：
real		//6位十进制数字精度
numeric(m,n)	//任意精度类型，m为数值最大长度，n为保留的小数，小数点不占位数

日期与时间类型：
time		//只用于一日内时间，如	10:05:05
date		//只用于日期，如		1987-04-04
timestamp	//日期和时间，如		1987-04-04 10:05:05

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

运算符

算数运算符：
+，-，*，/，%	//与c语言相同

比较运算符：
=、<>(!=)、<=、>=、>、<		//返回t或f。字符串会转化为数值比较，如'2'=2为t
least			//在有两个或者多个参数时，返回最小值
greatest		//返回最大值
between and	//判断一个值是否落在两个值之间。select 2 between 1 and 3, 2 between 3 and 5,3 between 3 and 6；返回t,f,t
in		//判断一个值是否是in列表中的任意一个值。select 2 in (2,3,4), 2 in (3,4,5), 2 not in (3,4,5);返回t,f,t
like		//通配符匹配，'%'可匹配一或多个字符，'_'只能匹配一个字符。select 'abc' like 'a%', 'abc' like '_b_', 'abc' not like '%d';返回t,t,t

逻辑运算符：
not（逻辑非）	//select not '1', not 'y', not '0', not 'n';返回f,f,t,t
and（逻辑与）	//select '1' and 'y', '1' and '0', '0' and 'n';返回t,f,f
or（逻辑或）		//select '1' or 'y', '1' or '0', '0' or 'n';返回t,t,f

简单数据插入

create table student(
	id int,
	name varchar(30),
	birthday date,
	score numeric(5,2)
);

向单表中插入数据：insert into 表名 values （值1,值2,...）	//值的顺序必须严格根据表的字段顺序
insert into student values (1, '张三', '1990-01-01', 3.85);	//如果某个值不知道的话，一定要用null来填

向数据表中指定字段插入数据：insert into 表名 (字段1, 字段2, ...) values (值1, 值2, ...)	//字段与值一一对应
insert into student (id, name, birthday) values (2, '李四', '1990-01-02');		//没插入的部分为null
insert into student (id,birthday , name) values (2, '1990-01-02', '李四' );		//字段和值的顺序随意，不过要一一对应

批量插入数据

create table student(
	id int,
	name varchar(30),
	birthday date,
	score numeric(5,2)
);
使用insert语句批量向数据表中插入数据：insert into 表名 （属性名称） values （取值列表1）,	(取值列表2），...（取值列表n）;
insert into student (id, name, birthday) values
(3, '张三1', '1990-02-01'),
(4, '张三2', '1990-02-02'),
(5, '张三3', '1990-02-03');

create table student_new(
	id int,
	name varchar(30),
	birthday date,
	score numeric(5,2)
);
使用select查询批量向数据表中插入数据：insert into 表名1 （属性名称） select （属性值） from 表名2 where （查询条件）;
insert into student_new select * from student;	//将student表中所有字段的内容插入到student_new中
delete from student_new;			//删除表student_new中的内容
insert into student_new (id, name) select id, name from student;		//将表student中的id，name的内容插入到表student_new中

数据更新

create table student(
	id int,
	name varchar(30),
	birthday date,
	score numeric(5,2)
);
指定条件更新数据：update 表名 set 要更新的字段名 = 要更新的字段内容 where 要更新的哪一条数据;
update student set name = '李四1' where id = 2;		//更新id=2的学生姓名为“李四1”

批量更新数据
update student set score = 0;			//将student表中的学生学分全更新为0

将指定结果更新到对应字段
update student set score = 1.1+2.3 where id = 2;		//更新的内容为指定的结果3.4

数据删除

create table student(
	id int,
	name varchar(30),
	birthday date,
	score numeric(5,2)
);
数据删除操作
delete from student where id = 4;		//删除id=4的记录
delete from student where birthday between '1990-01-01' and '1990-02-01'；		//删除生日在1990-01-01到1990-02-01的记录
delete from student;		//清空表student

使用truncate清空数据表
truncate table student;		//清空表student

delete与truncate的区别
        		    		 | delete  | truncate
执行速度    					 | 慢  	   | 快
可指定条件    				 | 可以	   | 不可以
语句分类   					 |  DML    | DDL
可以回滚事务（将删除事务找回）	 | 可以 	   | 不可以
删除操作记录信息（用于数据恢复）	 | 记录	   |  不记录

数据表主键、外键介绍

如何定义主键约束
（1）列级约束
create table emp(
	id int primary key,		//因为id号唯一，所以选id号为主键。主键是数据表中唯一标识一条记录的主要方式
	name varchar(30),
	salary numeric(9,2)
);
（2）表级约束
create table emp1(
	id int,
	name varchar(30),
	salary numeric(9,2),
	constraint pk_emp1 primary key(id)	//constraint 主键名 primary key(字段名)
);

如何定义外键约束
create table dept(
	id int primary key,
	name varchar(40)
);
insert into dept values (1, '开发部');
insert into dept values (2, '测试部');
create table emp3(
	id int primary key,
	name varchar(30),
	salary numeric(9,2),
	deptId int,	//标识所在部门
	constraint fk_emp3_dept foreign key(deptId) references dept(id)
	//constraint 外键名 foreign key(创建外键的字段) references 外键参考的表(外键参考的表的字段)	//外键参考的表的字段必定是外键参考的表的主键
);

主键约束和外键约束的作用
主键约束作用：
1、唯一标识一条记录
2、提高数据的检索效率（根据主键索引）
外键约束作用：
1、保证数据的完整性
2、提高数据的检索效率

非空约束、唯一约束、默认值约束

非空约束：
create table emp4(
	id int primary key,
	name varchar(30) not null,	//非空
	salary numeric(9,2),
);

唯一约束：
create table emp4(
	id int primary key,
	name varchar(30) not null,
	phone varchar(30) unique,	//唯一
	salary numeric(9,2),
);

默认值约束：
create table emp4(
	id int primary key,
	name varchar(30) not null,
	salary numeric(9,2) default 0.0,	//默认为0.0
);

11.简单数据查询

为数据表employee指定别名为e:
select e.e_no, e.e_name, e.e_hireDate from employee as e;
as可以省略：
select e.e_no, e.e_name, e.e_hireDate from employee e;

为字段取别名：
select e.e_no as a, e.e_name as b, e.e_hireDate as c from employee e;	//显示的字段名称会变成别名
as可以省略：
select e.e_no a, e.e_name b, e.e_hireDate c from employee e;

12.单表指定条件复杂查询

查询空值内容
select e_no, e_name, e_salary from employee where e_salary is null;	//查询e_salary为空的内容，不能写=null或=' '
select e_no, e_name, e_salary from employee where e_salary is not null;	//查询e_salary为非空的内容

and、or多条件查询
select e_no, e_name, e_gender, dept_no from employee where e_gender='f' and dept_no in (10,20);
select e_no, e_name, dept_no from employee where dept_no = 10 or dept_no = 20;

查询结果集排序
select e_no, e_name, e_salary from employee order by e_salary asc;	//按e_salary升序排序，空值显示在最后面
select e_no, e_name, e_salary from employee order by e_salary desc;	//按e_salary降序排序
select e_no, e_name, e_salary, e_hireDate from employee order by e_salary asc, e_hireDate desc;//按e_salary升序排序，如果两个记录的e_salary相等，则按照e_hireDate降序排序
select e_no, e_name, e_salary from employee order by e_salary asc nulls first;	//按e_salary升序排序，空值显示在最前面
select e_no, e_name, e_salary from employee order by e_salary asc nulls last;	//按e_salary升序排序，空值显示在最后面

limit关键字查询 
select * from employee limit 5;	//只显示前5条
select * from employee limit 5 offset 6;	//忽略前面6条，从第7条开始，显示5条数据

多表连接查询

内连接查询操作：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee, dept where dept_no = d_no;	//隐式内连接查询操作
与上行语句相等的语句：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee inner join dept on dept_no = d_no;	//显示内连接查询操作

左连接查询操作：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee left join dept on dept_no = d_no;
与上行语句相等的语句：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee left outer join dept on dept_no = d_no;

右连接查询操作：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee right join dept on dept_no = d_no;
与上行语句相等的语句：
select e_no, e_name, dept_no, d_no, d_name, d_location from employee right outer join dept on dept_no = d_no;

子查询操作

子查询：一个语句嵌套在另一个语句中的查询，即在一个select语句中会嵌套另一个select语句，把内部的select语句叫做子查询，把外部的select语句叫做主查询。
数据库会先计算子查询的数据的内容，然后将子查询计算返回的结果作为主查询的过滤条件来使用。

exists关键字子查询操作
select * from employee where exists （select d_no from dept where d_name='开发部'）;	//开发部的编号d_no比如10，会作为外部主查询的查询条件。如
果子查询查询到记录，那么子查询返回的就是真值，那么就会返回外面主查询所查询的所有内容
从雇员表中查询雇员所在的部门名称是开发部的所有雇员数据：
select * from employee where exists （select d_no from dept where d_name='开发部' and d_no=dept_no）; 

in关键字子查询操作
查询在雇员表中雇员所在的部门名称是开发部的雇员数据：
select * from employee where dept_no in (select d_no from dept where d_name = '开发部');

标量子查询操作
select e_no, e_name, (select d_name || ' ' || d_location from dept where dept_no=d_no) as address from employee;	//||表示字符串连接

查询结果集合并操作

使用union all对查询结果进行合并
select e_no, e_name, dept_no, e_salary from employee where dept_no in (10,20)
union all
select e_no, e_name, dept_no, e_salary from employee where e_salary>5000;	//只是简单合并（行相加），两个select的字段必须相同，若字段不同，可用null填充。不会去重

使用union对查询结果进行合并
select e_no, e_name, dept_no, e_salary from employee where dept_no in (10,20)
union
select e_no, e_name, dept_no, e_salary from employee where e_salary>5000;	//会去重，其他与union all相同

常用函数介绍

常用的数值函数
函数名称		函数作用
avg()		返回某列的平均值
count()		返回某列的行数
max()		返回某列的最大值		select max(e_salary) from employee;//查询雇员工资最大值
min()		返回某列的最小值		select min(e_salary) from employee;//查询雇员工资最小值
sum()		返回某列的值之和

常用的字符串函数
函数名称						函数作用
length(s)					计算字符串长度		select e_name,length(e_name) from employee;//查询雇员名称及其名称长度
concat(s1,s2,...)			字符串合并函数		select e_no, e_name, e_hireDate, concat(c_no, e_name, e_hireDate) from employee;
ltrim(s)/rtrim(s)/trim(s)	删除字符串空格函数
replace(s,s1,s2)			字符串替换函数
substring(s,n,len)			获取子串函数

常用的日期和时间函数
函数名称					函数作用
extract(type from d)	获取日期指定值函数		select e_no, e_name, e_hireDate, extract(year from e_hireDate), extract(month from e_hireDate), extract(day from e_hireDate) from employee;//将雇员的年，月，日进行拆分
current_date			获取当前日期函数		select current_date, current_time, now();
current_time			获取当前时间函数
now()					获取当前日期时间函数

自定义函数

创建函数的语法
CREATE FUNCTION			//声明创建函数
	add(integer,integer)		//定义函数名称，参数类型
RETURN integer			//定义函数返回值
	AS 'select $1 + $2;'		//定义函数体
LANGUAGE SQL			//通过SQL语言来创建函数
RETURNS NULL ON NULL INPUT;	//定义参数为NULL时处理情况


函数的创建

create function add(integer, integer) returns integer
	as ' select $1 + $2; '		//$1代表第一个传入的参数，$2代表第二个传入的参数
language sql
returns null on null input;
调用函数：select add(1,2);//返回3

自定义字符串连接函数：
create or replace function concat_test(integer, varchar, date) returns varchar//or replace的意思是如果当前数据库中存在concat_test函数，就将其覆盖，若不存在就直接创建。
	as 'SELECT $1||$2||$3;'
	language sql
returns null on null input;
函数调用：select e_no, e_name, e_hireDate, concat(e_no, e_name, e_hireDate), concat_test(e_no, e_name, e_hireDate) from employee;


函数的删除：drop function 函数名(参数内容);
drop function concat_test(integer, varchar, date);

数据库索引

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

索引的创建和删除：create index 索引名称 on 表名(字段名);(默认为B-tree索引)	drop index 索引名;
create index emp_name_index on employee(e_name);	//在雇员的名称这个字段上去创建索引	

drop index emp_name_index;

数据库视图

视图的创建：create view 视图名 as select语句;
create view v_emp_dev as select e_no, e_name, e_salary, e_hireDate from employee where dept_no = 10 order by e_salary desc;
使用视图与使用表类似：
select * from v_emp_dev;

视图的删除：drop view 视图名;
drop view v_emp_dev;

视图的作用：简单化、安全性、逻辑数据独立性

Yirschen

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
【PostgreSql】基础知识点总结

操作数据库对象1.创建数据库对象：create database 数据库名;create database db_jikexueyuan #数据库名 with ENCODING='utf-8' #编码为utf-8 OWNER=postgres #所有者 CONNECTION LIMIT=10; #限制连接数为102.修改数据库对象（其中...
复制链接

扫一扫

专栏目录