PostgreSQL基础整理(一)
1. 创建数据库:
1)登录bin目录,createdb.exe -U postgres -e mydb;
-U 表示本次操作的登录用户名,如果不写会取windows登录的账户,如Administrator;会提示无创建权限;
2. 登录数据库:
1)用postgre自带的sql shell,登录时选择数据库为创建库
3. CRUD
书写习惯:SQL语句大写,其他小写
3.1)创建表:
CREATE TABLE users( username char(20) PRIMARY KEY, password char(20));
3.2) 插入数据:
INSERT INTO users(username, password) VALUES('fredric','fredric'),('sinny','sinny');
3.4) 查询数据:
SELECT * FROM users;
SELECT * FROM users WHERE username LIKE ’fred%‘;//模糊查找
3.5) 删除数据:
DELETE FROM users WHERE username = "fredric";
3.6) 更新数据:
UPDATE emps SET salary = 8000.0 WHERE userid = 10;
4. JOIN
准备:
CREATE TABLE clubs(clubname char(20) PRIMARY KEY, note text);
CREATE TABLE customers( username char(20) PRIMARY KEY, clubname char(20),FOREIGN KEY(clubname) REFERENCES clubs(clubname));
INSERT INTO clubs(clubname, note) VALUES('myclub','good clubs');
INSERT INTO clubs(clubname, note) VALUES('myclub1','good clubs');
INSERT INTO customers(username, clubname) VALUES('fredric', 'myclub');
4.1) INNER JOIN
SELECT * FROM clubs INNER JOIN customers ON clubs.clubname = customers.clubname;
返回数据 myclub
4.2) OUTTER JOIN(例如 LEFT JOIN)
SELECT * FROM clubs LEFT JOIN customers ON clubs.clubname = customers.clubname;
返回数据 myclub 和 myclub1
5. 索引
5.1) 创建索引
CREATE INDEX clubs_index ON clubs USING hash(clubname);
CREATE INDEX clubs_index ON clubs USING btree(clubname);//B树类型的索引支持多字段
CREATE UNIQUE INDEX clubs_index ON clubs;//唯一索引,postgre默认在主键上生成唯一索引
5.2) 删除索引
DROP INDEX clubs_index;
6. 聚合函数
6.1) COUNT
SELECT COUNT(*) FROM clubs;
6.2) MIN/MAX
SELECT MIN(clubname) FROM clubs; //输出myclub
SELECT MAX(clubname) FROM clubs; //输出myclub1
7. GROUP BY
SELECT * FROM clubs GROUP By(clubname);
SELECT COUNT(*) FROM clubs GROUP By(note);//1,2
SELECT COUNT(*) FROM clubs GROUP By(note) HAVING COUNT(*) > 1;//2
备注:SQL执行顺序 FROM -> WHERE -> 聚合函数-> GROUP BY -> HAVING -> ORDER BY -> SELECT;
8. 事务
BEGIN TRANSACTION;
DELETE FROM customers WHERE clubname = 'myclub';
DELETE FROM clubs WHERE clubname = 'myclub';
COMMIT; //执行删除
ROLLBACK; //回滚操作
SAVEPOINT;为保存点,回滚的位置
例如:
SAVEPOINT mypoint;
ROLLBACK TO mypoint;
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· winform 绘制太阳,地球,月球 运作规律
· AI与.NET技术实操系列(五):向量存储与相似性搜索在 .NET 中的实现
· 超详细:普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码?零基础破解AI编程运行原理
· 上周热点回顾(3.3-3.9)
2013-07-16 Linux 网络编程(IO模型)