Getting started with PostgreSQL _ MySQL

PostgreSQL has become the first choice for open source relational databases since it was acquired by Oracle. This article introduces the installation and basic usage of PostgreSQL for the first time users. The following content is based on the Debian operating system. Other operating systems do not have the energy to take into account, but most of the content should be universally applied. 1. install the PostgreSQL client first. Sudoapt-PostgreSQL

Since MySQL was acquired by Oracle, PostgreSQL has gradually become the first choice for open source relational databases.

This article introduces the installation and basic usage of PostgreSQL for the first time users. The following content is based on the Debian operating system. Other operating systems do not have the energy to take into account, but most of the content should be universally applied.

I. Installation

First, install the PostgreSQL client.

Sudo apt-get install postgresql-client

Then, install the PostgreSQL server.

Sudo apt-get install postgresql

Normally, after the installation is complete, the PostgreSQL server will automatically enable Port 5432 on the local machine.

If you want to install the graphic management interface, run the following command, but this article does not cover this aspect.

Sudo apt-get install pgadmin3

2. add new users and new databases

After the initial installation, a database named S and a database user named postgres are generated by default. Note that a Linux user named S is also generated.

Next, we use postgres users to generate other users and new databases. There are several methods to achieve this purpose. here we will introduce two methods.

The first method is to use the PostgreSQL console.

First, create a new Linux user. you can use the name you want. here it is dbuser.

Sudo adduser dbuser

Then, switch to the postgres user.

Sudo su-postgres

Next, use the psql command to log on to the PostgreSQL console.

Psql

In this case, the system user IPVs logs on to the database as the database user with the same name. No password is required. If everything is normal, the system prompt will change to "S = #", indicating that the database console has been started. The following commands are all completed in the console.

The first thing is to use the \ password command to set a password for the postgres user.

\ Password S

The second thing is to create a database user dbuser (just created a Linux user) and set a password.

Create user dbuser with password 'password ';

The third thing is to create a user database. here is exampledb and the owner is specified as dbuser.

Create database exampledb OWNER dbuser;

The fourth thing is to grant all permissions of the exampledb database to dbuser. otherwise, dbuser can only log on to the console without any database operation permissions.

Grant all privileges on database exampledb to dbuser;

Finally, use the \ q command to exit the console (or press ctrl + D ).

\ Q

The second method is to use shell command line.

Add new users and new databases. in addition to the PostgreSQL console, you can also add new users and new databases using the shell command line. This is because PostgreSQL provides the command line program createuser and createdb. Take dbuser and exampledb as examples.

First, create a database user dbuser and specify it as a Super User.

Sudo-u postgres createuser -- superuser dbuser

Then, log on to the database console, set the password of the dbuser user, and then exit the console.

Sudo-u postgres psql

\ Password dbuser

\ Q

Next, create the database exampledb in the shell command line and specify the owner as dbuser.

Sudo-u postgres createdb-O dbuser exampledb

3. log on to the database

After adding a new user and a new database, you need to log on to the database in the name of the new user. the psql command is used.

Psql-U dbuser-d exampledb-h 127.0.0.1-p 5432

The parameter meanings of the preceding command are as follows:-U specifies the user,-d specifies the database,-h specifies the server, and-p specifies the port.

After entering the preceding command, the system will prompt you to enter the password of dbuser. Enter the correct information to log on to the console.

Psql commands are abbreviated. If you are a Linux user and a PostgreSQL user, you can omit the user name (part of the-U parameter ). For example, if my Linux user name is ruanyf and the PostgreSQL database has a user with the same name, after logging on to the Linux system as ruanyf, you can directly use the following command to log on to the database, password is not required.

Psql exampledb

In this case, if PostgreSQL still has a database with the same name as the current system user, the database name can be omitted. For example, if a database named ruanyf exists, you can directly type psql to log on to the database.

Psql

In addition, to restore external data, use the following command.

Psql exampledb <exampledb. SQL

IV. console commands

In addition to the \ password command (set password) and \ q command (exit), the console also provides a series of other commands.

• \ H: view the explanations of SQL commands, such as \ h select.
• \? : View the psql command list.
• \ L: list all databases.
• \ C [database_name]: connects to other databases.
• \ D: list all tables of the current database.
• \ D [table_name]: list the structure of a table.
• \ Du: list all users.
• \ E: open the text editor.
• \ Conninfo: lists information about the current database and connection.

V. database operations

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

# Creating a new table
Create table userTbl (name VARCHAR (20), signupDate DATE );

# Insert data
Insert into userTbl (name, signupDate) VALUES ('Zhang San', '2017-12-22 ');

# Selecting records
SELECT * FROM user_tbl;

# Update data
UPDATE user_tbl set name = 'Li si' WHERE name = 'Zhang San ';

# Deleting records
Delete from user_tbl WHERE name = 'Li si ';

# Add a column
Alter table user_tbl ADD email VARCHAR (40 );

# Update structure
Alter table userTbl alter column signupDate set not null;

# Rename the column
Alter table userTbl rename column signupDate TO signup;

# Delete a field
Alter table user_tbl drop column email;

# Rename a table
Alter table userTbl rename to backupTbl;

# Deleting a table
Drop table if exists backup_tbl;