postgreSQL学习笔记（一）

这学期有一门课程是数据库，外教老师实在一言难尽，只好自学，看了菜鸟教程云里雾里的，然后又不喜欢看视频学习，就让gemini来教我，效果还不错

基础环境

  • 创建数据路
    CREATE DATABASE datebase_name;
  • 切换数据库
    \c database_name;
  • 查看表结构
    \d table_name;
  • 创建表
    CREATE TABLE table_name;

数据操作

  • 插入数据：INSERT INTO;
  • 查询数据：SELECT column1,column2 FROM table_name;
  • 修改数据：UPDATE table_name SET column1 = new_value1 WHERE condition;
  • 删除数据：必须使用where指定删除哪一行，否则会删除整个表
  • DELETE FROM table_name WHERE condition;

数据查询与表的结构修改

  • 条件查询
    SEWLECT * FROM table_name WHERE condition1 AND/OR condition2;

  • 结果排序
    SELECT * FROM table_name ORDER BY column_name ASC/DESC
    ASC(默认):升序

  • 修改结构
    ALTER TABLE table_name ADD COLUMN column_name data_type

  • 主键

    作用: 唯一标识表中的每一行，其值必须唯一且不能为空 (NOT NULL)。
    添加方式: ALTER TABLE table_name ADD PRIMARY KEY (column_name);

  • 自动递增主键 (SERIAL):

    作用: PostgreSQL特有的类型，用于创建一个自动递增的整数主键。插入新数据时无需手动指定ID。

    id SERIAL PRIMARY KEY

    示例:
    CREATE TABLE students ( id SERIAL PRIMARY KEY, name VARCHAR(100) ); -- 插入时无需提供id INSERT INTO students (name) VALUES ('小明');

多表联查

  • 外键

    -作用: 用于将一张表中的列与另一张表的主键关联起来，保证数据的引用完整性。
    CREATE TABLE books ( id SERIAL PRIMARY KEY, title VARCHAR(255), author_id INT, FOREIGN KEY (author_id) REFERENCES authors(id) );

  • JOIN

    • 内连接 INNER JOIN
      返回两张表中能通过关联条件匹配上的数据。
    • 左连接 LEFT JOIN
      作用：返回左面表的所有记录，以及右边表中能匹配上的记录。如果右边表没有匹配项，则用 NULL 填充。

  • 子查询

    • 返回单个值的子查询：
      • 返回一个值可以使用’='等比较运算符
      • ... WHERE column = (SELECT single_value_column FROM ...);
    • 返回列表的子查询：
      • 必须用 IN 或 NOT IN
      • ... WHERE column IN (SELECT list_column FROM ...);

  • 通用表表达式（CTE）

    • 使用WITH关键字定义一个临时的查询结果集，将复杂的查询拆分
    • WITH cte_name AS ( SELECT ... -- 这里是子查询的逻辑 ) SELECT ... FROM cte_name ...; -- 在主查询中使用这个临时表