目录
前言
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。PostgreSQL 开发者把它念作 post-gress-Q-L。PostgreSQL 的 Slogan 是 "世界上最先进的开源关系型数据库"。
一、基本操作
数据库操作
-- 创建数据库 CREATE DATABASE db_lbh; -- 在创建数据库时指定参数信息 WITH OWNER:所有者 ENCODING:编码格式 CREATE DATABASE db_lbh2 WITH OWNER = postgres ENCODING = 'utf-8'; -- 修改数据库名称 ALTER DATABASE db_lbh2 RENAME TO db_lbh3; -- 修改连接限制数 ALTER DATABASE db_lbh CONNECTION LIMIT 20; -- 删除数据库 DROP DATABASE db_lbh3;
创建表
-- 创建表 CREATE TABLE student( id INT, name VARCHAR(30), birthday DATE, score NUMERIC(5,2) );
表操作
-- 修改表名 ALTER TABLE student RENAME TO student1; -- 修改字段名 ALTER TABLE student1 RENAME id TO myid; -- 修改字段类型 ALTER TABLE student1 ALTER COLUMN name TYPE VARCHAR(40); -- 删除字段 ALTER TABLE student1 DROP COLUMN birthday; -- 添加字段 ALTER TABLE student1 ADD COLUMN birthday varchar(20); -- 删除表 DROP TABLE student1; -- 删除前进行判断 DROP TABLE IF EXISTS student1;
往表中插入数据
-- 插入数据 INSERT INTO student VALUES (1,'阿道夫','2022-10-10',NULL); -- 指定字段插入数据 INSERT INTO student (id,name) VALUES('2','王老五'); --SELECT批量插入 -- 将表数据插入到新表中 INSERT INTO student1 SELECT * FROM student; -- 指定字段批量插入 INSERT INTO student1 (id,name) SELECT id, name FROM student;
修改表数据
-- 更新数据，将id等于2的用户名改成'张三' ,如果不指定更新条件，会全局更新 UPDATE student SET name = '张三' WHERE id = 2;
删除表数据
指定条件删除
--删除id等于2的表数据 DELETE FROM student WHERE id = 2; --删除id在1到3的表数据 DELETE FROM student WHERE id BETWEEN 1 and 3;
清空表数据
--清空数据表 DELETE from student; TRUNCATE TABLE student;
表查询
-- 查询所有字段内容 SELECT * FROM student; -- 批量字段查询 SELECT id, name FROM student; -- 多表查询 SELECT student.name, student1.name FROM student; -- 给表取别名后查询，简化代码 SELECT s.id, s.name FROM student s;
单表指定条件查询
-- in关键字查询 查询id为1，3，5的成员 SELECT id, name FROM student WHERE id IN (1,3,5); -- between and 关键字查询，在什么之间 SELECT id, name, birthday FROM student WHERE birthday BETWEEN '2020-10-10' AND '2024-10-10'; --模糊查询,查询所有姓张的用户 SELECT id, name FROM student WHERE name LIKE '张%';
单表指定条件复杂查询
-- 查询所有字段内容 SELECT * FROM student; -- 只显示前3条数据 SELECT * FROM student LIMIT 3; -- 查询三条数据，从第二条开始 SELECT * FROM student LIMIT 3 OFFSET 2; -- 查询姓名非空内容 SELECT id,name FROM student where name is NOT null; -- 1 or 3 ：1与3 ;ORDER BY id ASC 按id升序 ORDER BY id DESC 按id降序 SELECT id,name FROM student where id=1 OR id=3 ORDER BY id ;
多表查询
--创建一个班级表，id为主键 CREATE TABLE class( id int PRIMARY KEY, name VARCHAR(10) ); --插入班级与对应id INSERT INTO class VALUES(1,'一班'),(2,'二班'); --创建学生表，id为主键，classid为外键，关联表class的id CREATE TABLE student( id INT PRIMARY KEY, name VARCHAR(30), birthday DATE, score NUMERIC(5,2), classid int, CONSTRAINT pk_student_id FOREIGN KEY(classid) REFERENCES class(id) ); INSERT INTO student VALUES (1,'阿道夫','2022-10-10',NULL,1), (2,'沙和尚','2022-10-10',NULL,2), (3,'孙悟空','2022-10-10',NULL,2); -- 隐式内连接，关联classid显示student成员所在班级 SELECT student.id, class.id,student.name, class.name FROM student, class WHERE student.classid = class.id; -- 显示内连接,INNER JOIN不返回空值；LEFT JOIN 左连接，RIGHT JOIN 右连接，不能匹配的数据也返回 SELECT student.id, class.id,student.name, class.name FROM student INNER JOIN class ON student.classid = class.id ;
子查询
子查询或称为内部查询、嵌套查询，指的是在 PostgreSQL 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值
-- EXISTS关键字子查询 SELECT * FROM student WHERE EXISTS (SELECT class.id FROM class WHERE class.name = '二班' AND student.classid = class.id); -- IN 关键字子查询 SELECT * FROM student WHERE student.classid IN (SELECT class.id FROM class WHERE class.name = '二班' );
查询结果合并操作
UNION 可去重复 执行速度慢
UNION ALL 不可去重复 执行速度快
二、数据类型
数值类型
--整数类型 SMALLINT //小整数：-32768 ~ 32767 INT(INTEGER) //普通整数：-2147483648 ~ 2147483647 --任意精度浮点数类型 REAL //6位十进制数字精度 NUMERIC(m,n) //任意精度（m总位数，n小数位）
举例
--创建学生表，设置id数值类型 CREATE TABLE student( id1 SMALLINT, id2 INT, id3 REAL, id4 numeric(5,2) );
日期与时间类型
举例：
--创建表 CREATE TABLE student( t TIME, d DATE, tm TIMESTAMP ); --插入数据 INSERT INTO student VALUES('10:10:10','2020-10-10','2020-10-10 10:10:10');
字符串类型
举例：
-- 创建表 CREATE TABLE student( ch CHAR(10), vch VARCHAR(30), t TEXT ); -- 插入数据 INSERT INTO student VALUES ('哈哈哈','哈哈哈','哈哈哈') SELECT concat('(',ch,')'), concat('(',vch,')'), concat('(',t,')') FROM student;
可以看见CHAR类型字符长度不够会空白补充
运算符
算术运算符
比较运算符
-- 判断2是否在1~3之间 T SELECT 2 BETWEEN 1 AND 3; -- 判断2是否在234集合中 T SELECT 2 in (2,3,4); -- like运算符模糊匹配 %代表多个字符 _代表一个字符 SELECT 'abc' LIKE 'a%', --T 'abc' LIKE '_a_', --F 'abc' LIKE '_b_', --T 'abc' NOT LIKE '_b_'; --F SELECT LEAST(1,2,3), --返回最小值：1 GREATEST(1,2,3); --返回最大值：3
逻辑运算符
- NOT （逻辑非）
- AND （逻辑与）
- OR （逻辑或）
函数
数值函数
SELECT 函数（字段名） from 表名；
字符串函数
SELECT 函数（字段名） from 表名；
日期时间函数
EXTRACT使用
自定义函数
基本语法
CREATE FUNCTION //声明创建函数 ADD(INTEGER,INTEGER) //定义函数名称 RETURNS INTEGER //定义函数返回值 AS'SELECT $1 + $2;' //定义函数体 LANGUAGE SQL //用以实现函数的语言名字 RETURNS NULL ON NULL INPUT; //定义参数为NULL时处理情况
举例：
CREATE FUNCTION add2(INTEGER,INTEGER) RETURNS INTEGER AS'SELECT $1 + $2 + $1;' LANGUAGE SQL RETURNS NULL ON NULL INPUT; --使用函数 SELECT add2(1,2); -- 结果： 4
删除函数
DROP FUNCTION concat_student(INT,VARCHAR,DATE);
数据库索引
使用索引的优缺点
优点：
- 通过创建唯一的索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大提高查询速度
- 加速表与表之间的连接
- 减少查询中分组和排序的时间
缺点：
- 创建和维护索引耗费时间，数据量越多耗费的时间越多
- 索引需要占用物理空间
- 对表的数据进行增加、删除、修改的时候，索引也需要动态维护，降低了数据的维护速度。
-- 创建索引 此处默认使用B-tree CREATE INDEX student_name_index ON student(name); -- 删除索引 DROP INDEX student_name_index;
数据库视图
使用视图具有 简单化、安全性、逻辑数据独立性
--创建视图top3，展示id,name,birthday 条件是id<=3 并且按id降序 CREATE VIEW top3 AS SELECT id,name,birthday FROM student where id <= 3 ORDER BY id desc; --查看视图 SELECT * FROM top3; --删除视图 DROP VIEW top3;
主键、外键
主键
比如我们前面创建的学生表student里，id是唯一的，就可以使用PRIMARY KEY语句设置为主键
--方式1，列级约束，使用PRIMARY KEY语句，不能指定主键名称 CREATE TABLE student( id INT PRIMARY KEY, name VARCHAR(30), birthday DATE, score NUMERIC(5,2) ); --方式2，表级约束，可以指定主键名称 CREATE TABLE student( id INT, name VARCHAR(30), birthday DATE, score NUMERIC(5,2), CONSTRAINT pk_student_id PRIMARY KEY(id) );
外键
--创建一个班级表，id为主键 CREATE TABLE class( id int PRIMARY KEY, name VARCHAR(10) ); --插入班级与对应id INSERT INTO class VALUES(1,'一班'),(2,'二班'); --创建学生表，id为主键，classid为外键，关联表class的id CREATE TABLE student( id INT PRIMARY KEY, name VARCHAR(30), birthday DATE, score NUMERIC(5,2), classid int, CONSTRAINT pk_student_id FOREIGN KEY(classid) REFERENCES class(id) );
约束
主键与约束的区别，主键的值是非空加唯一，且一个表只能有一个主键，可以有多个约束
CREATE TABLE student( id INT UNIQUE, // UNIQUE 唯一约束 name VARCHAR(30) NOT NULL, // NOT NULL 非空约束 birthday DATE, score NUMERIC(5,2) DEFAULT 0.0 // DEFAULT 默认值约束 );
