1. Introduction to the logical structure of the database
2. Basic database operations
2.1 Create database
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ]
Parameter description is as follows.
·OWNER [=] user_name: used to specify which user the newly created database belongs to. If not specified, the newly created database belongs to the user currently executing the command.
·TEMPLATE [=] template: Template name (from which template to create a new database), if not specified, the default template database (template1) will be used.
·[ENCODING [=] encoding]: The character encoding used to create a new database.
·TABLESPACE [=] tablespace: used to specify the name of the table space associated with the new database.
·CONNECTION LIMIT [=] connlimit]: used to specify how many concurrent connections the database can accept. The default value is “-1”, which means no limit.
postgres=# postgres=# CREATE DATABASE osdbadb; CREATE DATABASE postgres=# postgres=# CREATE DATABASE testdb01 ENCODING 'UTF-8' TEMPLATE template0; CREATE DATABASE postgres=#
2.2 Modify database
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
The "option" here can have the following syntax structures:
·CONNECTION LIMIT connlimit.
·ALTER DATABASE name RENAME TO new_name.
·ALTER DATABASE name OWNER TO new_owner.
·ALTER DATABASE name SET TABLESPACE new_tablespace.
·ALTER DATABASE name SET configuration_parameter {TO |=}
{value|DEFAULT}.
·ALTER DATABASE name SET configuration_parameter FROM
CURRENT.
·ALTER DATABASE name RESET configuration_parameter.
·ALTER DATABASE name RESET ALL.
Example 1, modify the maximum number of connections of the database “testdb01” to “10”, the command is as follows:
Example 2, change the name of database “testdb01” to “mydb01”, the command is as follows:
Example 3, change the configuration parameters of the database “testdb01” so that once the user connects to this user, a certain configuration parameter is set to the specified value. For example, to turn off the default index scan on the database “testdb01”, the command is as follows:
postgres=# postgres=# postgres=# alter database testdb01 CONNECTION LIMIT 10; ALTER DATABASE postgres=# postgres=# postgres=# postgres=# postgres=# alter database testdb01 rename to mydb01; ALTER DATABASE postgres=# postgres=# postgres=# postgres=# postgres=# postgres=# ALTER DATABASE mydb01 SET enable_indexscan TO off; ALTER DATABASE postgres=#
2.3 Delete database
postgres=# postgres=# drop database testdb01; ERROR: database "testdb01" does not exist postgres=# postgres=# drop database if exists mydb01; DROP DATABASE postgres=#
Note that if there are still users connected to this database, the database cannot be deleted.
2.4 FAQ
3. Mode
Schema is a basic concept in the database field. Some databases combine schema and users into one, while PostgreSQL has a clear schema definition.
3.1 What is a pattern
Schema is a concept in the database. It can be understood as a namespace or directory. Different schemas can have tables, functions and other objects with the same name without conflict. The concept of mode is proposed to facilitate management. As long as there is permission, the objects of each mode can call each other.
·Allows multiple users to use the same database without interfering with each other.
·Organize database objects into logical groups in different modes to make database objects easier to manage.
·Third-party applications can be placed in different schemas so that they do not conflict with other object names.
Usage of 3.2 patterns
postgres=# create schema maxdba; CREATE SCHEMA postgres=# \dn List of schemas Name|Owner -------- + ---------- maxdba | postgres public|postgres (2 rows) postgres=# drop schema maxdba; DROP SCHEMA postgres=# postgres=# create schema authorization postgres; CREATE SCHEMA postgres=# postgres=# \dn List of schemas Name|Owner ---------- + ---------- postgres | postgres public|postgres (2 rows) postgres=#
The name and owner can be modified in the mode.
The syntax format is as follows:
ALTER SCHEMA name RENAME TO newname
ALTER SCHEMA name OWNER TO newowner
postgres=#
postgres=# create schema postgres
postgres-# CREATE TABLE t1 (id int, title text)
postgres-# CREATE TABLE t2 (id int, content text)
postgres-# CREATE VIEW v1 AS SELECT a.id,a.title, b.content FROM t1 a,t2 b where a.id=b.id;
CREATE SCHEMA
postgres=# \d
List of relations
Schema | Name | Type | Owner
---------- + ------------- + ------- + ----------
postgres | t1 | table | postgres
postgres | t2 | table | postgres
postgres | v1 | view | postgres
public | class | table | postgres
public | ipdb1 | table | postgres
public | ipdb2 | table | postgres
public | jtest01 | table | postgres
public | jtest02 | table | postgres
public | jtest03 | table | postgres
public | score | table | postgres
public | student | table | postgres
public | student_bak | table | postgres
public | t | table | postgres
public | test02 | table | postgres
public | test1 | table | postgres
public | testtab05 | table | postgres
public | testtab06 | table | postgres
public | testtab07 | table | postgres
public | testtab08 | table | postgres
public | testtab09 | table | postgres
(20 rows)
postgres=# alter schema postgres rename to postgresold;
ALTER SCHEMA
postgres=# \dn
List of schemas
Name|Owner
------------- + ----------
maxdba | postgres
osdba | postgres
postgresold | postgres
public|postgres
(4 rows)
postgres=#
The first pattern in the search path is called the current pattern. In addition to being the first schema searched, it is also the schema to which the newly created table belongs when CREATE TABLE does not declare a schema name. To display the current search path, use the following command:
postgres=# postgres=# show search_path; search_path ------------------ "$user", public (1 row) postgres=#
Search path for 3.3 pattern
postgres=# postgres=# postgres=# show search_path; search_path ------------------ "$user", public (1 row) postgres=#
4.Table
4.1 Create table
postgres=# create table test01(id int primary key, note
postgres(#varchar(20));
CREATE TABLE
postgres=# create table test02(id1 int, id2 int, note
postgres(# varchar(20), CONSTRAINT pk_test02 primary key(id1,id2));
ERROR: relation "test02" already exists
postgres=# drop table test02;
DROP TABLE
postgres=# create table test02(id1 int, id2 int, note
postgres(# varchar(20), CONSTRAINT pk_test02 primary key(id1,id2));
CREATE TABLE
postgres=# drop test03;
ERROR: syntax error at or near "test03"
LINE 1: drop test03;
^
postgres=# drop table test03;
ERROR: table "test03" does not exist
postgres=# create table test03(id1 int, id2 int, id3 int,
postgres(# note varchar(20), CONSTRAINT pk_test03 primary
postgres(# key(id1,id2), CONSTRAINT uk_test03_id3 UNIQUE(id3));
CREATE TABLE
postgres=#
postgres=# CREATE TABLE child(name varchar(20), age int,
postgres(# note text, CONSTRAINT ck_child_age CHECK(age <18));
CREATE TABLE
postgres=# CREATE TABLE baby (LIKE child);
CREATE TABLE
postgres=# \d child;
Table "public.child"
Column | Type | Collation | Nullable | Default
-------- + ----------------------- + ---------- + ----- ----- + ---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
Check constraints:
"ck_child_age" CHECK (age < 18)
postgres=# \d baby
Table "public.baby"
Column | Type | Collation | Nullable | Default
-------- + ----------------------- + ---------- + ----- ----- + ---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
postgres=# CREATE TABLE baby2 (LIKE child INCLUDING
postgres(# ALL);
CREATE TABLE
postgres=# \d baby2
Table "public.baby2"
Column | Type | Collation | Nullable | Default
-------- + ----------------------- + ---------- + ----- ----- + ---------
name | character varying(20) | | |
age | integer | | |
note | text | | |
Check constraints:
"ck_child_age" CHECK (age < 18)
postgres=# CREATE TABLE baby2 AS SELECT * FROM child WITH
postgres-# NO DATA;
ERROR: relation "baby2" already exists
postgres=# CREATE TABLE baby3 AS SELECT * FROM child WITH
NO DATA;
CREATE TABLE AS
postgres=#
4.2 Constraints
· Check constraints.
postgres=# CREATE TABLE persons ( postgres(# name varchar(40), postgres(# age int CONSTRAINT check_age CHECK (age >= 0 and age postgres(# <=150), postgres(#sex boolean postgres(# ); CREATE TABLE postgres=# CREATE TABLE books ( postgres(# book_no integer, postgres(# name text, postgres(# price numeric CHECK (price > 0), postgres(# discounted_price numeric CHECK (discounted_price > 0), postgres(# CHECK (price > discounted_price) postgres(# ); CREATE TABLE postgres=#
·Non-empty constraints.
A non-null constraint simply states that a field must not be NULL.
postgres=# postgres=# CREATE TABLE books1 ( postgres(# book_no integer not null, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# CREATE TABLE books2 ( postgres(# book_no integer NOT NULL, postgres(# name text, postgres(# price numeric NOT NULL CHECK (price >0) postgres(# ); CREATE TABLE postgres=#
·Unique constraint.
A unique constraint ensures that data in a field or a group of fields is unique compared to data in other rows in the table.
postgres=# postgres=# CREATE TABLE books3 ( postgres(# book_no integer UNIQUE, postgres(# name text, postgres(# price numeric postgres(# ); CREATE TABLE postgres=# postgres=# postgres=# postgres=# postgres=# postgres=# CREATE TABLE books4 ( postgres(# book_no integer, postgres(# name text, postgres(# price numeric, postgres(#UNIQUE(book_no) postgres(# ); CREATE TABLE postgres=#
· Primary key.
The difference between a primary key and a unique constraint is that the primary key cannot be empty. Usually we specify the primary key when creating the table:
postgres=# postgres=# CREATE TABLE books5 ( postgres(# book_no integer primary key, postgres(# name text, postgres(# price numeric, postgres(#UNIQUE(book_no) postgres(# ); CREATE TABLE postgres=# ALTER TABLE books add constraint pk_books_book_no primary postgres-# key (book_no); ALTER TABLE postgres=#
·Foreign key constraints.
A foreign key constraint is a constraint on the relationship between tables. It is used to constrain that the value of one or more fields in this table must appear in one or more fields of another table. This constraint may also be called a referential integrity constraint between two related tables.
postgres=# postgres=# CREATE TABLE class( postgres(# class_no int primary key, postgres(# class_name varchar(40) postgres(# ); CREATE TABLE postgres=# CREATE TABLE student( postgres(# student_no int primary key, postgres(# student_name varchar(40), postgres(# age int, postgres(# class_no int REFERENCES class(class_no) postgres(# ); CREATE TABLE postgres=# select * from class; class_no | class_name ---------- + ---------- (0 rows) postgres=# insert into student values(1,'Zhang San',13,10); ERROR: insert or update on table "student" violates foreign key constraint "student_class_no_fkey" DETAIL: Key (class_no)=(10) is not present in table "class". postgres=#
4.3 Modify table
·Add fields.
·Delete fields.
·Add constraints.
·Delete constraints.
·Modify the default value.
·Delete default value.
·Modify field data type.
·Rename fields.
·Rename the table.