Shulou(Shulou.com)05/31 Report--

This article mainly introduces the PostgreSQL database transaction insertion and deletion and update operation of the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, with a certain reference value, I believe that everyone after reading this PostgreSQL database transaction insertion and deletion and update operation method article will have a harvest, let's take a look at it.

INSERT

Use the insert statement to insert data into a table.

Create a table:

CREATE TABLE ProductIns (product_id CHAR (4) NOT NULL, product_name VARCHAR (100) NOT NULL, product_type VARCHAR (32) NOT NULL, sale_price INTEGER DEFAULT 0, purchase_price INTEGER, regist_date DATE, PRIMARY KEY (product_id))

Insert data into the table:

INSERT statement format:

Inside (column 1, column 2, …) It's called a list; (value 1, value 2,...) It's called a value list. The number of lists and values should be the same.

INSERT INTO (column 1, column 2.) VALUES (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0001','T-shirt', 'clothes', 1000, 500, '2222-09-20')

When you INSERT all the columns of a table, you can omit the list. Assign the data in the value list to each column from left to right.

INSERT INTO ProductIns VALUES ('0005', 'iron pot', 'kitchen utensils', 6800, 5000, '2222-01-15')

Insert NULL and write NULL directly in the value list, as long as the column that inserts NULL cannot set NOT NULL constraints.

INSERT INTO ProductIns VALUES ('0006', 'spoon', 'kitchen utensils', 500, NULL, '2222-09-20')

Insert default values:

The ProductIns table was created earlier to set the sale_price default value to 0. When the table is created, the default value is set, and the default value is inserted using the insert statement as follows.

-- explicitly set the default value INSERT INTO ProductIns VALUES ('0007', 'chopsticks', 'kitchen utensils', DEFAULT, 790, '2222-04-28')

The default value is set by the implicit method, and the columns set as default values are omitted from the list and values list.

If you omit a column that is not set as the default, the value of that column is set to NULL. If this column is a NOT NULL constraint, an error will be reported.

Implicit method sets the default value INSERT INTO ProductIns (product_id, product_name, product_type, purchase_price, regist_date) VALUES ('0007', 'chopsticks', 'kitchen utensils', 790, '2222-04-28')

Copy data from other tables:

Create a table with the same structure as Product.

CREATE TABLE ProductCopy (product_id CHAR (4) NOT NULL, product_name VARCHAR (100) NOT NULL, product_type VARCHAR (32) NOT NULL, sale_price INTEGER, purchase_price INTEGER, regist_date DATE, PRIMARY KEY (product_id))

You can insert data from the Product table into the ProductCopy table as below. For SELECT statements in INSERT statements, you can use WHERE clauses, GROUP BY clauses, and so on.

INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date) SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date FROM Product

Insert using a SELECT statement that contains a GROUP BY clause:

Create a table that summarizes by category of goods.

CREATE TABLE ProductType (product_type VARCHAR (32) NOT NULL, sum_sale_price INTEGER, sum_purchase_price INTEGER, PRIMARY KEY (product_type))

Through the following, we get a table that is grouped according to the category of goods, and calculate the sum of the prices of each category.

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price) SELECT product_type, SUM (sale_price), SUM (purchase_price) FROM Product GROUP BY product_type

DELETE

DROP TABLE statement to delete the table.

DELETE statement to delete the data in the table.

The object of the DELETE statement is a row, not a column, and you cannot delete only part of the column's data.

Delete all data rows:

Format DELETE FROM; example DELETE FROM Product

Delete some data rows:

Format DELETE FROM WHERE; example DELETE FROM ProductWHERE sale_price > = 2000 bot update

The UPDATE statement is used to change the data in the table.

Format:

UPDATE SET =

Change all the data in the regist_date column.

UPDATE Product SET regist_date = '2222-02-02'

The effect of the modification is as follows.

Update some data rows:

UPDATE SET = WHERE; UPDATE Product SET sale_price = sale_price * 10 WHERE product_type = 'kitchen utensils'

Update the column to NULL:

Provided that the column does not have NOT NULL and primary key constraints set.

UPDATE Product SET regist_date = NULL WHERE product_id = '0008'

Update multiple columns at the same time:

-- using commas, UPDATE Product SET sale_price = sale_price * 10, purchase_price = purchase_price / 2 WHERE product_type = 'kitchen utensils' can be used in all DBMS;-- list form, UPDATE Product SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2) WHERE product_type = 'kitchen utensils' cannot be used in some DBMS

A transaction transaction, a collection of update processes that need to be performed in the same processing unit.

Sometimes you have to do multiple processes on a table. For example, for something, it is necessary to increase the price of an and reduce the price of b. In this case, multiple processes are performed as the same processing unit. At this time, you can use transactions to deal with.

Format:

Transaction start statement; DML statement 1 position DML statement 2 position. Transaction end statement

Example:

Where the user needs to clearly indicate the end of the transaction. The instructions to end the transaction are COMMIT and ROLLBACK.

COMMIT, the update processing contained in the commit transaction. Once committed, the state before the start of the transaction cannot be restored.

ROLLBACK, canceling the update processing contained in the transaction is equivalent to abandoning the save and restoring the state before the start of the transaction.

The transaction has quietly begun when the database connection is established.

Without using the start statement, autocommit mode is used by default in SQL Server, PostgreSQL and MySQL, and each SQL statement is a transaction.

In Oracle, it counts as a transaction until the user executes COMMIT or ROLLBACK.

-- PostgreSQLBEGIN TRANSACTION; UPDATE Product SET sale_price = sale_price + 1000 WHERE product_name ='T-shirt'; UPDATE Product SET sale_price = sale_price-1000 WHERE product_name = 'pants'; COMMIT

The transaction of DBMS follows the ACID feature.

Atomic Atomicity, where update processing is either COMMIT or ROLLBACK at the end of the transaction.

Consistency Consistency, transaction processing, to meet the constraints set by the database, such as primary key constraints, NOT NULL constraints.

Isolated Isolation, different transactions do not interfere with each other. One transaction adds data to the table, and the newly added data is not seen by other transactions until it is committed.

Persistent Durability, when the transaction ends, the data state at that point in time is saved. If the data is lost due to system failure, it can also be recovered in some ways.

This is the end of the article on "methods for inserting, deleting and updating PostgreSQL database transactions". Thank you for reading! I believe you all have a certain understanding of the knowledge of "PostgreSQL database transaction insertion, deletion and update operation". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.