^^数据库基本操作

• SQL语言
• psql交互式终端
• 表空间
• 数据库
• 数据表
• 数据查询

SQL语言

• 功能：数据库查询、定义数据结构、修改数据库中的数据、说明安全性约束条件
• SQL语句类型：DQL、DML（insert\update\delete）、DDL

psql交互式终端

• 启动服务：net start postgresql-x64-14
• 登录：psql -U postgres
• 常用命令
  \d：查看当前数据库所有表、视图、序列
  \d 表名：表的表定义
  \db：表空间的信息
  \dn：模式信息
  \du或\dg：查看数据库所有角色或用户
  \x：设置查询结果的输出模式
  ?：查询PosrgreSQL支持的所有命令
  \timing：显示语句执行时间

表空间

表空间为表等对象指定一个存储的目录，把不同的表放在不同的存储介质或不同的文件系统下

1. 创建表空间
   create tablespace 表空间名称 owner 表空间所属用户名称 location '表空间对应的绝对路径';
   绝对路径避免在c盘，C盘不能随便访问
2. 修改表空间
   更改表空间名称：alter tablespace 旧名称 rename to 新名称;
   更改表空间所属用户：alter tablespace 表空间名称 owner to 所属用户名称;
   设置表空间参数：alter tablespace 表空间名称 reset tablespace_option[,...];
3. 删除表空间 drop tablespace [if exists] 表空间名称;

• 注意：表空间名称不能以pg_开头，这些名称为系统表空间预留

数据库

1. 创建数据库
   create database 或 createdb 数据库名称
   owner 所属用户名称
   tablespace 表空间名字
   encoding 字符集
   conn limit 允许并发连接数据库的个数
   allow_connections 是否可以连接该数据库
   is_template 是否是模板库
2. 修改数据库 alter database 数据库名称
   allow_connections 是否可以连接该数据库
   conn limit 允许并发连接数据库的个数
   is_template 是否是模板库
   alter database 数据库名称 rename to 新名称
   alter database 数据库名称 owner to 新所属用户名称
   alter database 数据库名称 set tablespace 新表空间名称
   alter database 数据库名称 set configuration_parameter参数 to 参数值
   alter database 数据库名称 set configuration_parameter参数 from current
   alter database 数据库名称 reset configuration_parameter参数
   alter database 数据库名称 reset all
3. 删除数据库
   drop database [if exists] 数据库名称;

数据表

1. 创建
   create table [if not exists] 表名称 (列名 数据类型 限制[ ]);
   • not null | null
   • check
     • 简单字段级：create table department(createtime timestamp check(createtime > '1970-01-01 00:00:00'));要求部门的创建时间必须大于1970-01-01 00:00:00
     • 指定特定名字的字段级：create table department(createtime timestamp constraint createtime_check check(createtime > '1970-01-01 00:00:00'));为check指定特别名字，可以清楚识别出错信息，还可以根据需要修改约束条件
     • 表级：create table department(createtime timestamp, parentcreatetime timestamp, check(createtime > parentcreatetime));所有字段约束可以写成表约束的形式，但是有些表约束不能写成字段约束的形式
   • unique
     • 字段约束：create table department(deptid int unique);
     • 表约束：create table department(deptid int not null, unique(deptid)); 也可以是多个字段的组合unique(a,b)要求ab两个字段的组合取值在全表中是唯一的，但允许其中一个字段的值相同。
     • 可以在创建完表后通过create unique index 命令创建唯一约束
   • primary key
     • 主键约束指定一个字段或几个字段的组合是唯一的，与唯一性约束的差别是要求将这些字段值设为not null，primary key=unique not null
     • 一个表最多只能有一个主键，但可以有多个非空的唯一索引
   • default
     • 为字段设置默认值，在插入某些字段时如果没有给某些字段设置默认值，则会使用其定义的默认值： create table department(createtime timestamp default'1970-01-01 00:00:00');
     • 还可以是表达式，在插入语句时计算的，不是创建时计算的：create table department(createtime timestamp default current_timestamp);
   • references
     • 外键约束要求一个表对应字段的值必须和另外一个表对应字段值相同，用来实现参数完整性
       create table department(
       deptid int primary key,
       deptname text not null,
       createtime timestamp
       );
       create table employee(
       empid int not null,
       empname text not null,
       deptid int not null references department(deptid),
       sex int not null,
       birthdate date not null,
       createtime timestamp not null,
       constraint pk_employee primary key(empid)
       );
       department表为主表，employee表为子表，deptid为employe表的外键，也可以将主表的字段名去掉，此时外键中主表字段名默认是主表的主键。
     • 组合外键约束要求主表的主键字段和子表的外键字段个数和类型都一致:
       create table tb_test01(
       a1 int not null,
       b1 text not null,
       c1 timestamp,
       primary key(b1, c1)
       );
       create table tb_test(
       a int not null,
       b text not null,
       c timestamp,
       foreign key(b, c)reherences tb_test01(b1,c1)
       );
     • 多个外键约束：
       create table excellent_employee(
       deptid int not null references department,
       empid int not null references employee,
       empname text not null,
       primary key(deptid, empid)
       );
   • 设置表属性值自动增加
     • 创建一个序列 create sequence seq_test
       start with 1
       increment by 1
       no minvalue
       no maxvalue
       cache 1;
       将表的字段默认值设置为该序列的next值
       create table tb_test(a int not null default nextval('seq_test'));
       如果向表中插入记录时字段a没有被指定，则seq_test表读取序列的下一个值作为a的值
     • 直接使用serial数据类型：
       create table tb_test(a serial not null);
2. 修改
   • 修改表名：alter table 表名 rename to 新表名;
   • 修改字段名：alter table 表名 rename column 字段名 to 新字段名;
   • 添加字段：alter table 表名 add column 字段名 字段类型 限制;
   • 删除字段：alter table 表名 drop column 字段名;
   • 删除约束：alter table 表名 drop constraint 约束名；
3. 删除
   drop table [if exists] 表名称;
   删除被引用的表：drop table [if exists] 表名称 cascade;
   删除主表只会删除子表的外键引用约束，并不会删除子表，也不会删除子表中的记录
4. 数据的插入、修改和删除
   • 插入
     不指定字段插入数据：insert into department values(1, 'IT', '2018-02-02 00:00:00');列表顺序和表中字段顺序必须一致
     指定字段插入数据： insert into department (createtime,deptname,deptid) values('2018-02-02 00:00:00', 'IT', 1);
     从左到右匹配插入数据
     所有字段都取默认值： insert into department default values;
     同时插入多条数据： insert into department (createtime,deptname,deptid) values ('2018-02-02 00:00:00', 'IT', 1), ('2018-02-03 00:00:00', 'HR', 2);
   • 修改
     update命令：待修改数据的表名和字段名，字段修改后的值，哪些行需要修改
     update 表名 set 字段名 = 字段取值 where 字段 = 字段取值
     update department set createtime = '2018-09-01 00:00:00' where deptid = 2;
     set字句可以是与字段相关的表达式如deptid = deptid + 10，where子句可以是其他比较运算符
   • 删除
     必须删除整行数据不允许删除一行中的某些字段
     如果表中有主键，可以通过主键直接定位到某个特定的行
     如果表中没有主键，只能通过特定的条件来匹配表中需要删除的数据
     delete department where deptid < 5;

数据查询

1. 单表查询
   select * from department;
   select deptid,deptname from department;
2. 创建带过滤条件的查询
   select deptid,deptname from department where deptname = 'IT';
   select deptid,deptname from department where deptname = 'IT' and deptid = 1;