PostgreSQL Beginner's Introductory tutorial _postgresql

Since MySQL was acquired by Oracle, PostgreSQL is becoming the first choice for open source relational databases.
This paper introduces the installation and basic usage of PostgreSQL for first-time users. The following are based on the Debian operating system and other operating systems do not have the energy to juggle, but most of the content should be universally applicable.

Installation

1, first, install the PostgreSQL client.

sudo apt-get install postgresql-client

Then, install the PostgreSQL server.

sudo apt-get install PostgreSQL

2, under normal circumstances, after the installation is completed, the PostgreSQL server will automatically open on the 5432 port of the machine.

If you also want to install the graphics management interface, you can run the following command, but this is not covered in this article.

sudo apt-get install pgadmin3

Add new users and new databases

1. After the initial installation, a database named Postgres and a database user named Postgres are generated by default. It should be noted that a Linux system user named Postgres is also generated.
Next, we use the Postgres user to generate other users and new databases. There are several ways to achieve this, and here are two.

2, the first method, the use of PostgreSQL console.

First, create a new Linux user, you can take the name you want, here for Dbuser.
sudo adduser dbuser

Then, switch to the Postgres user.
sudo su-postgres
Next, log on to the PostgreSQL console using the Psql command.

Psql

This is equivalent to the system user Postgres with the same name of the database user, login to the database, which is not required to enter the password. If everything works, the system prompt becomes "postgres=#", indicating that the database console is already in. The following commands are completed in the console.
The first thing is to use the \password command to set a password for the Postgres user.
\password Postgres

The second thing is to create the database user Dbuser (who just created the Linux system user) and set the password.

CREATE USER dbuser with PASSWORD ' PASSWORD ';

The third thing is to create a user database, which is exampledb, and specifies that the owner is dbuser.

CREATE DATABASE exampledb OWNER dbuser;

The fourth thing is to give the Exampledb database all permissions to Dbuser, otherwise dbuser can only log on to the console, without any database operation permissions.

GRANT all privileges on the DATABASE exampledb to Dbuser;

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

3, the second method, using the shell command line.

Adding new users and new databases can also be done under the shell command line in addition to the PostgreSQL console. This is because PostgreSQL provides command line programs CreateUser and Createdb. For example, create a new user Dbuser and a database exampledb.
First, create the database user Dbuser and designate it as Superuser.

Sudo-u postgres CreateUser--superuser dbuser
Then, log on to the database console, set the Dbuser user's password, and exit the console when finished.

Sudo-u Postgres Psql
\password Dbuser
\q

Then, under the shell command line, create the database exampledb and specify the owner as Dbuser.
Sudo-u postgres createdb-o dbuser exampledb

Log on to the database

1, add new users and new database, you will be the name of the new user login database, then use the Psql command.
Psql-u dbuser-d exampledb-h 127.0.0.1-p 5432
The parameters of the above command have the following meanings:-u Specify the user,-D specify the database,-H Specify the server,-p specify the port.
When you enter the above command, you will be prompted to enter the Dbuser user's password. If you enter it correctly, you can log in to the console.
The Psql command exists in shorthand form. If the current Linux system user, and also the PostgreSQL user, you can omit the user name (part of the-u parameter). For example, my Linux system user name is RUANYF, and the PostgreSQL database has a user with the same name, then I log on to the Linux system as RUANYF, I can log on to the database directly using the following command, and do not need a password.

Psql Exampledb
At this point, if a database with the same name as the current system user exists inside the PostgreSQL, the database names can be omitted. For example, assuming there is a database called RUANYF, you can log in to the database directly by typing psql.
Psql
In addition, if you want to recover external data, you can use the following command.
Psql Exampledb < Exampledb.sql

Console command

1. In addition to the previously used \password command (set password) and the \q command (exit), the console also provides a series of other commands.
\h: View the explanation of the SQL command, such as \h Select.
\?: View the list of PSQL commands. \l: Lists all databases.
\c [database_name]: Connecting to other databases.
\d: Lists all tables for the current database.
\d [table_name]: Lists the structure of a single table.
\DU: Lists all users.
\e: Opens a text editor.
\conninfo: Lists the current database and connection information.

Database operations

1 Basic database operations, is the use of the general SQL language.

# Create a new table

usertbl (name VARCHAR, signupdate DATE);
# Insert data into

usertbl (name, signupdate) VALUES (' John ', ' 2013-12-22 ');
# Choose Record Select
* from USER_TBL;
# Updated Data Update
USER_TBL set name = ' Dick ' WHERE name = ' John ';
# Deletes the record delete from
user_tbl WHERE name = ' Dick ';
# add Field
ALTER TABLE user_tbl add email VARCHAR;
# UPDATE structure
alter TABLE USERTBL ALTER COLUMN signupdate SET not NULL;
# rename field
ALTER TABLE usertbl RENAME column signupdate to signup;
# delete field
ALTER TABLE user_tbl DROP column email;
# table renamed 
ALTER table Usertbl RENAME to backuptbl;
# Delete Table
drop table IF EXISTS backup_tbl;

Attention matters

This experience is based on the Debian operating system