PostgreSQL - basics

assistantashamedΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

166 εμφανίσεις

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

1
















AIM


To understand the basic commands of PostgreSQL, their mechanics and to develop the skills to
use them efficiently.


MOTIVATION


This is the beginning of your practical work. The primitives you will learn are simple, but
sufficiently p
owerful. If you tackle this session seriously, I suspect that you will have little problems
during the following sessions. Moreover, at the end of this session you will have enough “tools”
with which to create and maintain your own database. I hope that th
e practical work will be fun
and that the issues raised in lectures will be better understood by you through.


MAIN OBJECTIVES


1.

Create a database

2.

Create some tables in your database

3.

“Populate” the tables with some data

4.

Query the database

5.

Modify the databas
e

6.

Destroy a database (CARE!!!)


REMARKS


1.

Read the hints at the end of these lab’s handouts (page 7); they are useful to know before you
start the work.

2.

The information presented here is sufficient for you, in terms of PostgreSQL, to complete the
course
-
wor
k.



PostgreSQL
-

basics

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

2


ISSUES



Creating a database


You can create a database from the Unix promt by typing

$createdb
<database
-
name>

A database
<database
-
name>

was created and you automatically became its administrator. It is
also possible to create a database by issu
ing a PostgreSQL command, but this is going to be
presented later.



Accessing a database


At the Unix prompt type

$psql
<database
-
name>

This command will invoke the
psql

program (i.e. your front end application) which will allow you
to communicate with th
e DBMS (POSTGRES) using the PostgreSQL language.


If your database is called
my_db
, then after you type

$psql my_db

you will see on your screen


Welcome to the POSTGRESQL interactive sql monitor:


Please read the file COPYRIGHT for copyright terms of PO
STGRESQL



type
\
? for help on slash commands


type
\
q to quit


type
\
g or terminate with semicolon to execute query


You are currently connected to the database: my_db


my_db=>


You are now connected to your database (see the PostgreSQL promt, i.e.
=>) through
psql
, and
you can use valid statements in PostgreSQL to work on your database.


Apart from the set of SQL commands, PostgreSQL provides a set of slash commands that help
you in the administration of your database(s). Find about them issuing:

m
y_db=>
\
?

The important ones for you at this stage are


\
?


--

help


\
connect <dbname|
-
> <user>

--

connect to new database (currently
my_db')


\
copy table {from | to} <fname>


\
d [<table>]


--

list tables and indices in database or columns in
<t
able>, * for all


\
dt


--

list only tables in database


\
e [<fname>]


--

edit the current query buffer or <fname>,
\
E
execute too


\
i <fname>


--

read and execute queries from filename

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

3


\
g [<fname>] [|<cmd>]

--

send query to backend [and results

in
<fname> or pipe]


\
o [<fname>] [|<cmd>]

--

send all query results to stdout, <fname>,
or pipe


\
h [<cmd>]


--

help on syntax of sql commands, * for all commands


\
l


--

list all databases


\
q


--

quit


\
r


--

reset(clear
) the query buffer


\
s [<fname>]


--

print history or save it in <fname>


\
! [<cmd>]


--

shell escape or command

After you create a few (one will suffice) tables in your database you can try some of the above
commands. However, leave them aside for the t
ime being, apart from the

my_db=>
\
h <command
-
name>

that you can use to get help on different PostgreSQL commands.



Destroying a database


$destroydb
<database
-
name>

Be very careful with this command because it actually physically removes all the files
a
ssociated with the database and cannot be undone.





After creation, a database is empty. What you have to do afterwards is to define the tables,
populate them with data and then maintain the database (query, update, modify). In the remaining
of this labo
ratory session you will study a minimal set of commands by means of which you can
maintain your first POSTGRES database.




Creating a new table


In order to create a new table you have to specify at least the name of the table, the name of each
field (att
ribute / column) and their associated type (domain). The PostgresSQL command for this
is CREATE TABLE. Its simplified syntax is:

CREATE TABLE <table
-
name> (


<attribute
-
name>

<attribute
-
type>
,


<attribute
-
name>

<attribute
-
type>
,



. . .


. . .




);

For in
stance if you want to store information about your favourite actors, you could use the
following PostgreSQL statement:

CREATE TABLE favourites (


name


varchar(12),


height

real,


weight

real,


ranking

int,


notes


varchar(35)

);

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

4

Check if the table was cr
eated in your database by using a slash command (
\
dt
). Check the
structure of the table, i.e. its columns and their associated type, using a slash command (
\
d

favourites
).


When you create a table you have to specify the type of each attribute. For the ti
me being the
following PostgreSQL types will suffice:


PostgreSQL type

SQL92 or SQL3

Type description

bool

boolean

logical boolean (true/false)

char(n)

character(n)

fixed
-
length character string

varchar(n)

character varying(n)

variable
-
length character
string

date

date

calendar date without time of day

float4

float(p)

floating
-
point number with precision p

float8

real, double precision

double
-
precision floating
-
point number

int2

smallint

signed 2
-
byte integer

int4

int, integer

decimal(p,s)

signed 4
-
byte integer

exact numeric for p <= 9, s = 0

money

decimal(9,2)

US
-
style currency


(Note: in PostgreSQL jargon a ‘table’ is referred to as a ‘class’; actually the notion of a class is
richer but we shall not go into these details at the moment).



Popula
ting a table


By using the command INSERT you can insert a tuple (row) into a table. Its simplified syntax is:

INSERT INTO
<table
-
name>

[(
<attr1>,

...,
<attrN>
)]


VALUES (
<val
-
1>,

...,
<val
-
n>
) ;

For example (insert in your
favourites

table)

INSERT
INTO favourites

VALUES (‘Cristina’,


1.69,


52,


1,


‘she always was, she will always be ...’);

Populate your favourites table with at least five tuples of your choice.


There is also another command that you can use for large amounts of
data, which usually are
stored in ASCII files. For such situations you can use COPY. This command will be described in
another session.



Querying a database


Querying a database is done by means of a SELECT statement. Its general structure is

SELECT

<att
ributes>

FROM

<tables>

WHERE

<conditions on attributes>


(Refer to the lecture notes (slides), especially to the informal introduction to SQL).


Before going into more details, define another table,
films

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

5

CREATE TABLE films (


title


varchar(25),


ratin
g


int,


favourite_actor

varchar(12),


notes



varchar(35)

);

and populate the table with a few tuples. Make sure that you get some of the names you have in
the
favourites

table in the
favourite_actor

field in
films
.


The general format of a relational sel
ection / restriction (note: it is meant: the relational algebra
operator selection) on a table is

SELECT

*

FROM

<table>

WHERE

<conditions on attributes>

;


‘*’ stands for ‘all attributes’.

For instance, the PostgreSQL statement for the query

“which are
my best favourite actors (i.e. ranking = 1)” is

SELECT

*

FROM

favourites

WHERE

ranking = 1;


The general format of a projection is

SELECT

<required attributes>

FROM

<table>

;

For instance, the PostgreSQL statement for the query

“which are the names an
d the notes made about them of my favourite actors” is

SELECT

name, notes

FROM

favourites ;


The general format of a join statement between two tables is

SELECT

<qualified attributes>

FROM

<table1>, <table2>

WHERE

<conditions on attributes>

;

For inst
ance, the PostgreSQL statement for the query

“which are the films in which my favourite actors played; I want all the information about both the
films and the actors” is

SELECT

*

FROM

films, favourites

WHERE

favourite_actor = name ;


A more complex quer
y is implemented (expressed) as a combination of the three basic ones
(note that there can be more than one way of expressing a query). For instance, the PostgreSQL
statement for the query

“which are the name of the films in which my most favourite actors
played; I want the title of the
film, the notes I made about the film, the name of the actor and the notes I made about them” is

SELECT

title, films.notes as film_notes, name,

favourites.notes as actor_notes

FROM

films, favourites

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

6

WHERE

favourite_actor = name and



ranking = 1 ;



Redirecting a SELECT query to a new table (e.g. for intermediate results)


For instan
ce if you wish to store a temporary result you can do this by specifying where the result
should be stored.

SELECT

<attribute
-
list>

INTO
<new
-
table>


FROM

<tables>

WHERE

<conditions
-
on
-
attributes>

For instance, the result of the above query can be store
d into a table
best_films_and_actors

as

SELECT

title, films.notes, name, favourite.notes

INTO

best_films_and_actors

FROM

films, favourites

WHERE

favourite_actor = name and



ranking = 1 ;

This command will create a new table named
best_films_and_actor
s

with the attributes
specified in the query.



Deleting from a table


You can delete a row (tuple) or a set of rows (tuples) from a table by means of a DELETE
statement. Its syntax is

DELETE

FROM


<table>

WHERE


<condition
-
on
-
attributes>

;

For instance,

if you want to delete all the information you have about the favourite actors whose
ranking is bigger than 5 then you issue the following PostgreSQL command

DELETE

FROM


favourites

WHERE


ranking > 5 ;

You can delete all the values stored in a table by
not specifying any condition (i.e. any WHERE
statement).
BE CAREFUL
, this statement will delete all the values in the table and the system will
not ask for any confirmation!!! Use it with care!

DELETE

FROM


<table> ;


You can realise now that by means of

INSERT and DELETE you can keep the information in a
table

updated. However, there is another way of updating a table.



Updating a table


In case you want to modify the values of certain attributes you can use an UPDATE statement.
The syntax of such a sta
tement is

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

7

UPDATE


<table>

SET



<attribute_1>

=
<expression_1>,





...,




<attribute_n>

=
<expression_n>

WHERE


<condition
-
on
-
attributes>

;

For instance, if you decided that all your most favourite actors (i.e. ranking = 1) are not that good
after all
, because you just found a new one who’s much better, you might want to “relegate” them
to a second ranking; then you do

UPDATE


favourites


SET



ranking = 2

WHERE


ranking = 1 ;



CONCLUSION


You have now the “tools” (i.e. the minimum set of commands) b
y means of which you can create
and maintain (at a very simple level) a small database. Do experiment to understand them better.
When you feel you know the mechanics enough, you can start implementing the database for
your course
-
work. Read the REMINDER an
d the HINTS
-

they will give you some advise on how
to be disciplined, therefore efficient, in your work.



REMINDER


Use your logbook! Make notes of all your findings (they might be useful at a later stage). If you
have a question about a certain issue wr
ite it down. After you got the answer, write that down too.
However, don't be over
-
zealous in keeping the logbook. For instance, do not duplicate the lab’s
handouts; don't go into too laborious explanations, etc.


HINTS


1
. Edit your commands in a file (us
ing the
nedit

editor


from program launcher choose Editors;
see last lab handouts).


After the statement was completely edited, save it to a file. Use a suggestive name. To run these
statements, use the slash command:

my_db=>
\
i <file
-
name>

This approach

will make the correction of the commands easier and also you will be able to keep
a log of what you are doing by printing the relevant commands you are working with. Name the
files in which you store these statements using expressive names
-

in Unix you
can have quite
long file names.




2
. You can redirect the result of a query to a file. You can do this by means of a slash command:


my_db=> <SELECT
-
STATEMENT>
\
g <file_name>

For example, the result of the query

SELECT * FROM favourites

can be redirected
to a file, say result1.db, by using the following statement

LABORATORY SESSION #3




Marian Ursu, 1999

version 1.1

8

SELECT * FROM my_favourites
\
g res1.db

The file can then be viewed from nedit:



This approach will help you log your important results.



3
. Copying a table to a file


Another way of logging im
portant data from the database is by copying a table to a file. Thereafter,
the file can be viewed, using
nedit
, printed out, using the
lpr

command (it is a text (ASCII) file),
etc. For instance, if you want to copy your
favourites

table to a file called
m
y_fav_act
, that
you subsequently want to print out you issue the following command:

my_db=>
\
copy favourites to my_fav_act



4
. Either at the Unix promt or at the psql promt you can access the commands history by using the

up arrow. Try and see what happens
. This will help you in quickly editing commands that you have
used before.



5
. Use the tutorial at

http://www.gold.ac.uk/~mas01mu/manuals/tutorial/tutorial.html

in conjunction w
ith this lab handout.