Postgresql数据库 常用sql语句

Postgresql数据库 常用sql语句

1. 约束

1.1 主键约束

主键是用于在表中唯一标识行的列或列组。从技术上讲，主键约束是非空约束和UNIQUE约束的组合。

使用列级约束设置主键

• 使用列级约束设置主键, 只能设置一列作为主键，主键默认名称为tablename_pkey

CREATE TABLE IF NOT EXISTS users(
    Id serial PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);

使用表约束设置主键

• 使用表级约束设置主键，可以设置一列或多列作为主键，主键默认名称为tablename_pkey，
• "CONSTRAINT users_pkey" 可省略。

CREATE TABLE IF NOT EXISTS users(
    Id serial,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp,
    CONSTRAINT users_pkey PRIMARY KEY(Id),
    CONSTRAINT users_code_key UNIQUE(Code)
);

通过修改表结构设置主键

• 通过修改表结构设置主键，可以设置一列或多列作为主键，可以指定主键名称。
• 语法：ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(column_1, column_2);

CREATE TABLE IF NOT EXISTS users(
    Id serial,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);

ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (Id);

往已有表添加自增主键

• 通过修改表结构设置主键，可以设置一列或多列作为主键，可以指定主键名称。
• 语法：ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(column_1, column_2);

--1. 创建没有任何主键的表。
CREATE TABLE IF NOT EXISTS users(Name varchar(255));
--2. 往表添加数据
INSERT INTO users (Name) VALUES ('Amy'),('Alen'),('张三');
--3. 查询
select * from users;

数据输出：

name

Amy

Alen

张三

--4. 添加一个名为 id 的自增主键到表中
ALTER TABLE users ADD COLUMN Id SERIAL PRIMARY KEY;
--5. 查询
select * from users;

数据输出：

name	id
Amy	1
Alen	2
张三	3

删除主键约束

• 语法：ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE users DROP CONSTRAINT users_pkey;

1.2 外键约束

外键约束维护子表和父表之间的引用完整性。

使用列级约束设置外键

• 外键默认名称为 tablename_columnname_fkey

CREATE TABLE IF NOT EXISTS skills(
    Id serial PRIMARY KEY,
    Userid integer NOT NULL REFERENCES users(Id),
    Name VARCHAR(10) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);

使用表约束设置外键

• 外键默认名称为 tablename_columnname_fkey
• "CONSTRAINT skills_Userid_fkey" 可省略。

CREATE TABLE IF NOT EXISTS skills(
    Id serial,
    Userid integer NOT NULL,
    Name VARCHAR(10) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp,
    CONSTRAINT skills_pkey PRIMARY KEY(Id),
    CONSTRAINT skills_Userid_fkey FOREIGN KEY(Userid) REFERENCES Users(Id)
);

通过修改表结构设置外键

• 通过修改表结构设置外键，可以指定外键名称。
• 语法：ALTER TABLE table_name_1 ADD [CONSTRAINT constraint_name] FOREIGN KEY(column_1) REFERENCES table_name_2(column_2);

CREATE TABLE IF NOT EXISTS skills(
    Id serial,
    Userid integer NOT NULL,
    Name VARCHAR(10) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);

ALTER TABLE skills ADD CONSTRAINT skills_Userid_fkey FOREIGN KEY(Userid) REFERENCES Users(Id);

删除外键约束

• 同删除其他约束一样，使用同一语法
• 语法：ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE skills DROP CONSTRAINT skills_Userid_fkey;

1.3 唯一约束

确保列或列组中的值在表中是唯一的。

使用列级约束设置唯一约束

• 生成2个列的唯一约束users_Code_key 和 users_Ucode_key
• 唯一约束默认名称为tablename_columnname_pkey

CREATE TABLE IF NOT EXISTS users(
    Id serial PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL UNIQUE,
    Ucode VARCHAR(20) NOT NULL UNIQUE,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);

使用表级约束设置唯一约束

• 注意以下两种方式的区别
• "CONSTRAINT users_Code_key" 可以省略
• 将会生成1个列组的唯一约束users_Code_Ucode_key

CREATE TABLE IF NOT EXISTS users(
    Id serial PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    Ucode VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp,
    CONSTRAINT users_Code_Ucode_key UNIQUE(Code,Ucode)
);

• 将会生成2个列的唯一约束users_Code_key和users_Ucode_key

CREATE TABLE IF NOT EXISTS users(
    Id serial PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    Ucode VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp,
    CONSTRAINT users_Code_key UNIQUE(Code),
    CONSTRAINT users_Ucode_key UNIQUE(Ucode)
);

通过修改表结构设置唯一约束

• 将会生成1个列组的唯一约束users_Code_Ucode_key。如果想要2个列的唯一约束，需写两个alter table。

CREATE TABLE IF NOT EXISTS users(
    Id serial PRIMARY KEY,
    Name VARCHAR(10) NOT NULL,
    Code VARCHAR(20) NOT NULL,
    Ucode VARCHAR(20) NOT NULL,
    CreatedAt timestamptz DEFAULT current_timestamp,
    UpdatedAt timestamptz DEFAULT current_timestamp
);
ALTER TABLE users ADD CONSTRAINT users_Code_Ucode_key UNIQUE (Code, Ucode);

删除唯一约束

• 同删除其他约束一样，使用同一语法
• 语法：ALTER TABLE table_name DROP CONSTRAINT constraint_name;

ALTER TABLE users DROP CONSTRAINT users_Code_Ucode_key;