Use of pgadmin, PostgreSQL SQL basic syntax, JDBC connection to PostgreSQL

Brief introduction

We can manage the PostgreSQL database through some graphical interface management programs, such as Pgadmin, to create new objects by visual or command line, delete and edit existing objects (such as directories, schemas, tables, etc.) and access them through JDBC or ODBC.

Interface operation:

To open a connection to a service, select the desired service in the tree, double-click it, or use the Connect on the Tools menu.
In the main window, the structure of the database is displayed. You can create new objects, delete and edit existing objects, and if you are using permissions that are currently connected to a database user, display all servers on the left side of the main window and the tree of objects they contain.
The top right shows the details of the object currently selected in the tree. The bottom right contains a SQL script.

1. File menu
In the File menu, you can change the connection password when you click a node under the server root node.
Click the options (O) button You can adjust some properties, such as: User interface language, preferences, SQL syntax highlighting color and so on. The postgresql.conf, pg_hba.conf, pgpass.conf files can also be edited in the menu. To optimize the performance of PostgreSQL. Add the server you want, and use "Add Server" in the menu. You can select a group when the new server is registered. When the OK button is pressed, a new group appears and the server is included. This folder-like feature can be used to group servers by type or purpose.
2. Edit Menu
Click on the Edit menu to perform the appropriate operations on the database and objects. (Right-click the object that corresponds to all relevant features in the edit)
3. Plugins
Start the Psql console and connect to the database selected in Pgadmin. You can enter the appropriate command.
4. View
Used to adjust the display of the page view. For example: Open and close the SQL window.
5. Tools
In the object tree at the left of the main form, click the highlight of the menu in the different nodes tool to do the corresponding action. (The right-click action for the selected object can be the same effect, but the tool opens the query tool).
6. Help
can view Help documents

Execute SQL:

Open the SQL query from the Tools menu or toolbar, enter the appropriate SQL statement in the SQL editor, click the green arrow to execute the SQL statement, or select partially to execute. (Execute SQL statement shortcut key F5). You can view the corresponding results in the output window below. Click menu Query Interpretation (shortcut key Shift+f7) analysis will appear corresponding to the explanatory diagram similar to graphical query. You can also save the data.
Graphical query constructors allow you to build queries with your mouse: Drag data tables and views from the navigator into the graphics panel, add connections between them, click the check box to add fields, append conditions, and set the order of results.

You can drag a file from the desktop to the query text box and then release it, and the query tool will open the file automatically.

Manipulate various objects:

The creation of the database. Select Database + Right button = new database

The system default mode is public and can be built in other modes. Only the appropriate permissions can see the contents of the mode.

Creation and modification of tables. Select data table and right-click-New database table (especially note that table names and field names are lowercase when you create a new table, because it is case sensitive.) Uppercase or mixed with a double quotation mark) Right-click the table you want to manipulate to select a new object to do a corresponding operation on the table. For example: New fields and so on.

Backup and recovery.
Right-Select the database you want to back up. Pgadmin can be used to back up the postgres into three formats, Compress,tar,plain. Compress the smallest, is compressed, but inside garbled. The file ends in. Backup tar is large, a few lines are garbled, the next part is the data, the format does not look very neat. The file ends with. backup. Plain is also very large, but not garbled, inside the content is very neat, divided into two parts, the upper part is related to the property and data table information. The next section is full of data. Fields are delimited by a tab.
However, only compress and tar files can be recovered when recovering. Which ends with. Backup.

SQL syntax

The basic database operation is to use the general SQL language.

# Create a new tableCREATE TABLEUSERTBL (nameVARCHAR( -), Signupdate DATE); # Insert DataINSERT  intoUSERTBL (name, signupdate)VALUES('Zhang San','2013-12-22'); # Select RecordsSELECT *  fromuser_tbl;# Updating DataUPDATEUser_tblSetName= 'John Doe' WHEREName= 'Zhang San'; # Delete RecordsDELETE  fromUser_tblWHEREName= 'John Doe'; # add FieldALTER TABLEUser_tblADDEmailVARCHAR( +); # Update StructureALTER TABLEUsertblALTER COLUMNSignupdateSET  not NULL; # rename FieldALTER TABLEUsertbl RENAMECOLUMNSignupdate tosignup;# Delete FieldsALTER TABLEUser_tblDROP COLUMNemail;# Form RenamingALTER TABLEUsertbl RENAME tobackuptbl;# Delete a tableDROP TABLE IF EXISTSBACKUP_TBL;

The JDBC of PostgreSQL

The code written in Java is:

Import java.sql.*;
Class.forName ("Org.postgresql.Driver"//String user= "MyUser"; String Password= "MyPassword"; Connection conn= drivermanager.getconnection (Url,user,password);

Or

String sql = "SELECT * FROM Test"; //connection string, format: "JDBC: Database driver name://database Server ip/database name "String url = "Jdbc:postgresql://localhost/soft"; String username= "Scott"; String Password= "Tiger"; Class.forName ("Org.postgresql.Driver"). newinstance ();Connection Conn=drivermanager.getconnection (URL, username, password); Statement stmt=conn.createstatement (resultset.type_scroll_sensitive, Resultset.concur_updatab  LE); ResultSet RS=stmt.executequery (SQL);  Rs.close ();  Stmt.close (); Conn.close (); 

Use of pgadmin, PostgreSQL SQL basic syntax, JDBC connection to PostgreSQL