First, the basic operation of PostgreSQL

-------------------------------------------------------------------------------------------------------Directory:--1. Database----1.1 Creating a database----1.2 Deleting a database--2. Architecture----2.1 Creating schemas----2.2 Deleting schemas--3. Table----3.1 Creating a table------3.1. Union uniqueness of more than 1 fields------3.1.2 primary and foreign keys----3.2 Deleting a table----3.3 Modifying a table-------------------------------------------------------------------------------------------------------1. Database----1.1 Creating a databaseCreate Databasetest;----1.2 Deleting a databaseDrop Databasetest;----1.3 Modifying a databaseAlter DatabaseTest Rename toTestDB;--2. Architecture----2.1 Creating schemasCreate SchemaTestschema;----2.1 Deleting schemasDrop SchemaTestschema;--3. Table----3.1 Creating a tableCreate Tabletest_table1 (SID serial,--serial The field is self-incrementStu_numberinteger  not NULL,--NOT NULL represents a non-null constraintNametext Unique,----unique represents a Uniqueness constraint, the specified field cannot be inserted into a duplicate valueMath_score Numericdefault 59.99,----defalut indicates that the default value for this field is setEnglish_score NumericCheck(English_score> 0),----check indicates that the value of the field must conform to its expressionDescriptiontext,    UNIQUE(description)--uniqueness constraints can also be written like this);------3.1. Union uniqueness of more than 1 fieldsCreate TableExample3 (Ainteger, binteger, Cinteger,    UNIQUE(A,c));Insert  intoExample3Values(1,1,1);Insert  intoExample3Values(1,1,2);--pasedInsert  intoExample3Values(1,2,1);--failed:duplicate key value violates UNIQUE constraint "Example3_a_c_key"------3.1.2 primary and foreign keysCreate TableExample4 (Ainteger, binteger, Cinteger,    Primary Key(B,C)--primary keys can act on multiple fields simultaneously, forming a federated primary key);Create TableExample5 (Ainteger Primary Key, binteger, Cinteger,    Foreign Key(B,C)ReferencesExample4 (B,C)--the number of fields in the foreign key and the number of primary keys in the referenced table must be consistent);----description------(1) When there are primary foreign key referential constraints between multiple tables, if you want to delete a row of data from the primary key, the value of the primary key field for the row's record may be associated with a record in its reference table, which will cause the delete operation to failInsert  intoExample4Values(1,1,1);Insert  intoExample4Values(1,2,2);Insert  intoExample5Values(1,3,3);--failed:insert or update on table "Example5" violates FOREIGN KEY constraint "Example5_b_fkey". Key (b, c) = (3, 3) is not present in table "Example4".Insert  intoExample5Values(2,1,1);Insert  intoExample5Values(3,2,2);Select *  fromexample4;Select *  fromexample5;Delete  fromExample4whereA= 1;--failed:update or delete on table "Example4" violates FOREIGN KEY constraint "example5_b_fkey" on table "Example5". Key (b, c) = (1, 1) is still referenced from table "Example5".--(2) psql provides limitations and cascading deletions to resolve issues in (1)Create TableA (qinteger Primary Key, Winteger, Einteger, Tinteger);Insert  intoAValues(1,1,1,1);Insert  intoAValues(2,1,1,1);Create TableB (Ainteger ReferencesA (q) on Delete Cascade,--Cascade Delete a referenced row in the primary table, all rows referencing it are also automatically deletedBinteger);Insert  intoBValues(1,1);Insert  intoBValues(1,2);Insert  intoBValues(2,1);Delete  fromAwhereQ= 1;Select *  fromb;Create TableC (Ainteger ReferencesA (q) on Delete Restrict,--restrict prohibit deletion of referenced rows    --a integer references a (q) on UPDATE cascadeBinteger);Insert  intoCValues(2,1);Delete  fromAwhereQ= 2;--error:update or delete on table ' a ' violates foreign key constraint ' c_a_fkey ' on table ' C ' detail:key (q) = (2) is Still referenced from table "C".-----------------------3.2 Deleting a tableDrop TableTableName;----3.3 Modifying a tableAlter TableTest_table1Add columnAdd_columntext  not NULL;--Add a fieldAlter TableTest_table1Drop columnAdd_column;--If the table is the primary table and the field is a referenced field, the operation will fail--If you want to delete all associated data for a reference field when you delete it, you can use the following methodAlter TableADrop columnQCascade

First, the basic operation of PostgreSQL