6. Postgresql graphical management tool pgAdmin3;
Version: Version 1.4.3
6.1 Installation;
6.1.1 Ubuntu installation;
There are two ways:
1. Search for pgadmin3 in Xinlide package manager and find pgadmin3---app
2. Enter the command in the terminal:
xiaop@xiaop-laptop:~$ sudo apt-get install pgadmin3
6.1.2 Installation of other systems;
You can refer to the installation methods of normal software in other systems. This is similar to the one I will not introduce it here;
6.2 Simple use of pgAdmin3;
The graphical management system is relatively intuitive. You can operate on the command line and then view the effect under pgAdmin3;
6.2.1 Startup of pgAdmin3
You can find the startup item for pgAdmin3 in the application---system tool;
You can also enter it on the command line:
xiaop@xiaop-laptop:~$ /usr/bin/pgadmin3 start
6.2.2 Connect to the created database mydb;
Click Archives------Add a new server, and then enter in the window that pops up:
Address: localhost
Description: Server name (fill in at will)
Maintain database: postgres
Username: Create one by yourself (see Create a user for details)
Password: Correspond to username (create it yourself when creating a user)
After clicking OK, you can view the existing database in postsql;
Note: The database of pgAdmin3 and the database created under the terminal are fully synchronized (you can use refresh to view the effect). pgAdmin3 is a relatively convenient graphical management tool. It can create charts, manage databases, etc. We will discuss the detailed introduction of pgAdmin3 in the future. This article mainly introduces the operations under the command line. The command line that the graphical management tool can do can do. You can create a table under the command line and check whether it is synchronized on pgAdmin3:
7. Create and delete tables;
7.1 Create a new table;
After creating the database, you can create a new table. You can create a table by declaring the name of the table and the names and types of all fields, for example:
mydb#CREATE TABLE weather (
city varchar(80),
temp_lo int, -- minimum temperature
temp_hi int, -- maximum temperature
prcp real, -- precipitation
date date
);
Note: You can type these things in psql with line breaks. psql can recognize that the command does not end until the semicolon, don't forget ";"
You can use whitespace (that is, spaces, tabs, and newlines) in SQL commands. This means you can type commands in a different alignment than above. Two scribings ("--") introduce comments. Anything that follows it until the end of the line is ignored. SQL is a keyword and identifier case-insensitive language, and its case properties can be preserved only when identifiers are surrounded by double quotes.
7.2 Data type;
The varchar(80) in the example above declares a data type that can store any string of up to 80 characters. int is a normal integer type. real is a type used to store single-precision floating point numbers. The date type should be self-interpreting.
PostgresSQL supports standard SQL types int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp and interval, and also supports other common types and rich geometric types. PostgreSQL can be customized to customize any user-defined data type. You can refer to the Chinese document of PostgreSQL to query;
7.3 Delete the table;
If you no longer need a table, or you want to create a different table, you can delete it with the following command:
mydb#DROP TABLE tablename
8. Add rows to the table;
8.1 INSERT;
INSERT is used to add rows to a table, you can enter (operate in the database):
mydb#INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
Note: All data types use a fairly clear input format. Constants that are not simple numeric values must be surrounded by single quotes ('), as in the example.
8.2 point type input;
The point type requires a coordinate pair as input, as follows:
mydb#INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
8.3 COPY;
You can also use COPY to load large amounts of data from text files. This is usually faster because the COPY command is optimized for such applications, but it is less flexible than INSERT. for example:
mydb#COPY weather FROM '/home/user/';
Note: It is a form content document that meets the format standards you wrote in advance;
9. Query a table;
9.1 SELECT;
To retrieve data from a table is to query this table. SQL's SELECT is for this purpose. The statement is divided into a selection list (listing the field parts to be returned), a list of tables (listing the part of the table from which data is retrieved), and optional conditions (declaring any restrictions). For example, to retrieve all rows of the table weather, type:
SELECT * FROM weather;
<code>
Output result:
<code>
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 43 | 57 | 0 | 1994-11-29
Hayward | 37 | 54 | | 1994-11-29
(3 rows)
You can write any expression in the selection list, not just a list of fields. For example, you can:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
This should result:
city | temp_avg | date
---------------+----------+------------
San Francisco | 48 | 1994-11-27
San Francisco | 50 | 1994-11-29
Hayward | 45 | 1994-11-29
(3 rows)
Note how the AS clause here renames the output field. (The AS clause is optional.)
9.2 WHERE;
A query can use the WHERE clause "modify" to declare which lines are needed. The WHERE clause contains a Boolean (true) expression, and only rows of Boolean expressions are returned. Allows you to use commonly used Boolean operators (AND, OR, and NOT) in your condition. For example, the following query retrieves the weather on rainy days in San Francisco:
mydb#SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0;
result:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
(1 row)
9.3 Sort;
You can ask the returned queries to be sorted:
mydb#SELECT * FROM weather
ORDER BY city;
The result is:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
Hayward | 37 | 54 | | 1994-11-29
San Francisco | 43 | 57 | 0 | 1994-11-29
San Francisco | 46 | 50 | 0.25 | 1994-11-27
In this example, the order of sorting is not absolutely clear, so you may see the random sorting of San Francisco rows. But if you use the following statement, you will always get the above result
SELECT * FROM weather
ORDER BY city, temp_lo;
You can ask the query results to be sorted in some order and eliminate duplicate row output:
mydb#SELECT DISTINCT city
FROM weather;
The result is:
city
---------------
Hayward
San Francisco
(2 rows)
Again, the order of the result rows may be random.
10. View;
Suppose your app is particularly interested in a combination list of weather records and city locations, and you don't want to type these queries every time. Then you can create a view on this query, which gives this query a name, and you can reference it like a normal table.
Enter:
mydb#CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
Create a view;
Then select the built view:
SELECT * FROM myview;
The results are as follows:
city | temp_lo | temp_hi | prcp | date | location
---------------+---------+---------+------+------------+-----------
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | (-194,53)
San Francisco | 43 | 57 | 0 | 1994-11-29 | (-194,53)
(2 rows)
11. Update the line;
You can update an existing line with the UPDATE command. Assuming you find that all the temperature counts on November 28 are twice lower, you can update the data in the following ways:
mydb#UPDATE weather
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
WHERE date > '1994-11-28';
Check out the new status of the data:
SELECT * FROM weather;
The result is:
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
San Francisco | 46 | 50 | 0.25 | 1994-11-27
San Francisco | 41 | 55 | 0 | 1994-11-29
Hayward | 35 | 52 | | 1994-11-29
(3 rows)
12. Delete the line;
Data rows can be deleted from the table using the DELETE command. Assuming you are no longer interested in Hayward weather, you can delete those rows from the table using the following method:
mydb#DELETE FROM weather WHERE city = 'Hayward';
We must be careful when using the following statement
DELETE FROM tablename;
If there is no condition, DELETE will delete all rows from the specified table and clear it. The system will not request your confirmation before doing this!
13. About this article
Regarding the backup and recovery of the database, we will introduce it in PostgreSQL backup and recovery. Most of the information in this article is based on Chinese documents. The purpose is to make it easier for brothers to find it. Detailed things are available in Chinese documents. Thank you for your advice:)
Previous page12Read the full text