PostgreSQL notes(1)---Basic Knowledge

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. ---------Wikipedia

1.Installation

In this TD we practices PostgreSQL in Linux.

To install PostgreSQL:


sudo apt-get install postgresql postgresql-contrib

To launch:


sudo -i -u postgres

To get a postgres prompt:


psql

Here we go:

PostgreSQL offers an interactive shell that allows you to connect to a PostgreSQL server. You can enter two types of commands :

• Standard SQL commands;

• PostgreSQL-specific commands.

By the way,PostgreSQL handles authentication by associating Linux user accounts with PostgreSQL accounts. This is called "peer" authentication.Upon installation, Postgres creates a Linux user called "postgres" which can be used to access the system. 

To launch this shell, you have to use the following command :

psql -d j4_tutorial -U login_x# ORpsql j4_tutorial

Postgre SQL internal commands:
• \h to see the list of all the SQL commands;
• \h cmd to see the documentation of the command ;
• \i file to execute the commands from the file ;
• \d elt or table_name to display the details about ;
• \dt to list all the tables of your database;
• \q to quit psql 
• \H change the showing mode to aligne or html 
• \du list the current roles and their attributes

2. Language

Creation:

Alter table

If you want to modify something in an existing table (add, delete or update), you can use the ALTER TABLE statement.

And Delete :

DROP TABLE table_name;

DROP TABLE table_name CASCADE;(delete a table and its relationship) 

DELETE FROM table_name WHERE column_name = value;

SELECT,WHERE, ORDER BY[ASC, DESC], LIMIT number OFFSET number(too simple, I remember it)

INSERT INTO table_name(column name) VALUES (values);

UPDATE table_name SET column_name = new_value WHERE column_name = value;

Operators

Like:

SIMILOR TO

INNER JOIN

OUTER JOIN AND LEFT OUTER JOIN：

CROSS JOIN

This type of join is quite special. It corresponds to a Cartesian product. The request will display all the possible combinations of the lines from the table A and B. For a table A having n lines and a table B having m lines, the results will be composed of n * m lines in the end.

NATURAL JOIN

There is a keyword which will allow the DBMS to find how to join two tables by itself. This keyword must be placed before the

previous keywords of joins and doesn’t need a clause ON or USING after that.