PostgreSQL is a relational database management system (RDBMS), which means that it is a system for managing data stored in relationships.
1, Prepare a test database
1. docker installation pg
docker run -d --name postgres --restart always \ -e POSTGRES_USER=pg \ -e POSTGRES_PASSWORD=5740## \ -e ALLOW_IP_RANGE=0.0.0.0/0 \ -v /home/postgres/data:/var/lib/postgresql -p 5432:5432 postgres:10
-e ALLOW_IP_RANGE=0.0.0.0/0, which means that all ip access is allowed. If it is not added, non local ip access is not allowed
-e POSTGRES_USER= xx user name
-e POSTGRES_PASS=xx - specify password
2. Start a pgadmin
docker run --name pgadmin -p 5080:80 \ -e 'PGADMIN_DEFAULT_EMAIL=zjz@qq.com' \ -e 'PGADMIN_DEFAULT_PASSWORD=123456' \ -e 'PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION=True' \ -e 'PGADMIN_CONFIG_LOGIN_BANNER="Authorised users only!"' \ -e 'PGADMIN_CONFIG_CONSOLE_LOG_LEVEL=10' \ -d dpage/pgadmin4:4.17
2, Advanced query operations
1. Average and AS
How does the AS clause rename the output column
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
2. Where clause: a query can be "decorated" with a where clause, which specifies which rows are required.
The WHERE clause contains a Boolean (truth value) expression, AND only those rows that make the Boolean expression true will be returned. Common Boolean operators (AND, OR, AND NOT) can be used in conditions.
select * from weather where temp_lo = 46 AND prcp > 0.4;
3,ORDER BY
The ORDER BY statement sorts the result set by the specified column.
The ORDER BY statement sorts records in ascending ORDER BY default.
If you want to sort records in descending order, you can use the DESC keyword.
SELECT * FROM weather ORDER BY prcp; SELECT * FROM weather ORDER BY temp_lo, prcp;
4. De duplicated rows DISTINCT in the results of the query
SELECT DISTINCT temp_lo FROM weather;
5. Multi table joint query
SELECT * FROM weather, cities WHERE city = name;
When the city field and name field of the weather and cities table are the same, the query result will be displayed.
5. Remove the name field to make the display more beautiful
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
6. Columns have different names, so the planner automatically finds out which table they belong to.
If there are columns with duplicate names in two tables, you need to limit the column name to indicate which one you want, such as:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
7, INNER JOIN
SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);
8. LEFT OUTER JOIN
What you want the query to do is scan the weather table and find the matching cities table row for each row. If we don't find a matching row, we need some "null values" to replace the columns of the cities table. This type of query is called external connection (the connections we saw before are internal connections).
SELECT *
FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
The query is a left outer join, because the row in the table on the left of the join operator must appear in the output at least once, while the row in the table on the right is output only when the matching left table row can be found. If the row of the output left table does not correspond to the row of the matching right table, the column of the row of the right table will be filled with null values
9. Right outer connection
10. Total external connection
11. Self join: re label the weather table as ， W1 ， and ， W2 ， to distinguish the left and right parts of the join·
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
12. Use aliases to save keystrokes in other queries, such as:
SELECT *
FROM weather w, cities c
WHERE w.city = c.name;
13. Aggregate function
PostgreSQL supports aggregate functions. An aggregate function calculates a result from multiple input lines. For example, we have functions that calculate count, sum, avg, max, and min on a row set
We can use the following statement to find the highest of the lowest temperatures in all records:
SELECT max(temp_lo) FROM weather;
14. Aggregate max cannot be used in the WHERE clause (this limitation exists because the WHERE clause determines which rows can be included in the aggregate calculation; therefore, obviously, it must be calculated before the aggregate function).
Subquery can be used: because subquery is an independent calculation, it calculates its own aggregation independent of the outer query.
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
15. HAVING} filtering and LIKE operators for pattern matching
Only those temps are given_ Cities with lo values lower than 40. Finally, if we only care about cities whose names begin with "S", we can use:
SELECT city, max(temp_lo)
FROM weather
WHERE city LIKE 'S%' -- (1)
GROUP BY city
HAVING max(temp_lo) < 40;
16. update, set
UPDATE weather
SET temp_hi = temp_hi - 4, temp_lo = temp_lo - 4
WHERE date = '1995-11-27';
17. Delete delete
DELETE FROM weather WHERE city = 'Hayward'; DELETE FROM tablename;
If there is no limit, DELETE will DELETE all rows from the specified table and empty it. The system will not ask you to confirm before doing this!
18. View (shortcut keys)
Suppose the combined list of weather records and cities is useful for our application, but we don't want to type in the whole query every time we need to use it. You can create a view on the query, which will give the query a name. We can use it like an ordinary table:
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
19. Foreign keys (primary key s, references)
You want to ensure that no one can insert rows into the weather table until there are corresponding entries in the cities table. This is called maintaining referential integrity of data.
In an overly simplified database system, you can first check whether a matching record exists in the cities table, and then decide whether to accept or reject the row to be inserted into the weather table. This method has some problems and is inconvenient, so PostgreSQL can solve them for us:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
An attempt was made to insert an illegal value
INSERT INTO weather1 VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR: insert or update on table "weather1" violates foreign key constraint "weather1_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities1".
The behavior of foreign keys can be well adjusted according to the application.
20. Business
Transaction is the basic concept of all database systems. The most important thing about a transaction is that it bundles multiple steps into a single operation that is either complete or incomplete. The intermediate state between steps is invisible to other concurrent transactions, and if some errors cause the transaction to fail to complete, any of these steps will not affect the database.
For example, consider a bank database that holds multiple customer account balances and total deposits in branches. Suppose we want to record a transfer of $100.00 from Alice's account to Bob's account. After being simplified to the greatest extent, the SQL commands involved are:
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
In PostgreSQL, to start a transaction, you need to surround the SQL command with BEGIN and COMMIT commands.
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
If we don't want to COMMIT during transaction execution (perhaps we notice that Alice's balance is insufficient), we can issue the ROLLBACK command instead of the COMMIT command, so that all current updates will be cancelled.
PostgreSQL actually executes every SQL statement as a transaction. If we do not issue the BEGIN command, each independent statement will be surrounded by an implicit BEGIN and (if successful) COMMIT. A set of statements surrounded by BEGIN and COMMIT is also called a transaction block
21. Window function
A window function performs a calculation on a series of table rows that have some association with the current row. This is comparable to the calculation completed by an aggregation function. However, window functions do not aggregate multiple rows into a single output row, which is different from the usual non window aggregation functions. Instead, rows retain their separate identities. Behind these phenomena, window functions can access more than the current row of query results.
The following is an example to show how to compare the salary of each employee with the average salary of his / her department:
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
The first three output columns come directly from the table empty, and each row in the table has an output row. The fourth column indicates that the average value is obtained for all table rows with the same depname value as the current row (this is actually the same function as the non window avg aggregation function, but the OVER clause makes it treated as a window function and calculated on an appropriate window frame)
A window function call always contains an OVER clause directly following the window function name and its parameters. This makes it syntactically distinct from an ordinary function or non window function. The OVER clause determines which rows in the query are separated and processed by the window function. The PARTITION BY clause in the OVER clause specifies that rows with the same PARTITION BY expression value are grouped or partitioned. For each row, the window function will calculate on the row of the same partition of the current row.
You can control the order in which the window function processes rows through ORDER BY on OVER (the ORDER BY of the window does not necessarily conform to the order of row output).
22. Succession
Inheritance is a concept in object-oriented database. It shows new possibilities for database design.
CREATE TABLE cities ( name text, population real, altitude int -- (in ft) ); CREATE TABLE capitals ( state char(2) ) INHERITS (cities);
In this case, the row of a capital inherits all columns (name, population and altitude) from its parent cities. The type of column name is text, a local PostgreSQL type for variable length strings. State capitals have an additional column state to display their states. In PostgreSQL, a table can inherit from 0 or more tables
PostgreSQL = > recursive query - funnyZpC - blog Garden (cnblogs.com)
PostgreSQL: recursive query application scenario | PostgreSQL Chinese website
PostgreSQL: how to connect rows of Group By result set| PostgreSQL Chinese website
PostgreSQL: basic knowledge about Left Join | PostgreSQL Chinese website
PostgreSQL: how to query the field information of a table| PostgreSQL Chinese website
PostgreSQL: related query of partition table | PostgreSQL Chinese network
PostgreSQL: how to query all tables containing a field in the library| PostgreSQL Chinese website
Advanced SQL features that PostgreSQL users should master | PostgreSQL Chinese network
PostgreSQL technology house: PostgreSQL novice guide (pgsql.tech)
SQL ORDER BY Clause (w3school.com.cn) Must see
PostgreSQL technology house: PostgreSQL novice guide (pgsql.tech) Must see
Two pg bosses: