Библиотека сайта rus-linux.net
|Purchase||Copyright © 2002 Paul Sheer. Click here for copying permissions.||Home|
Next: 39. smbd Up: rute Previous: 37. crond and atd   Contents
- 38.1 Structured Query Language
- 38.4 Installing and Initializing
- 38.5 Database Queries with
- 38.6 Introduction to SQL
- 38.6.1 Creating tables
- 38.6.2 Listing a table
- 38.6.3 Adding a column
- 38.6.4 Deleting (dropping) a column
- 38.6.5 Deleting (dropping) a table
- 38.6.6 Inserting rows, ``object relational''
- 38.6.7 Locating rows
- 38.6.8 Listing selected columns, and the
- 38.6.9 Creating tables from other tables
- 38.6.10 Deleting rows
- 38.6.11 Searches
- 38.6.12 Migrating from another database; dumping and restoring tables as plain text
- 38.6.13 Dumping an entire database
- 38.6.14 More advanced searches
- 38.7 Real Database Projects
This chapter will show you how to set up an SQL server for free.
Structured Query Language (SQL) is a programming language developed specifically to access data arranged in tables of rows and columns--as in a database--as well as do searching, sorting and cross-referencing of that data.
Typically, the database tables will sit in files managed by an SQL server daemon process. The SQL server will listen on a TCP socket for incoming requests from client machines and will service those requests.
SQL has become a de facto industry standard. However, the protocols (over TCP/IP) by which those SQL requests are sent are different from implementation to implementation.
SQL requests can usually be typed in manually from a command-line interface. This is difficult for most users, so a GUI interface will usually hide this process from the user.
SQL servers and SQL support software is major institution. Management of database tables is actually a complicated affair. A good SQL server will properly streamline multiple simultaneous requests that may access and modify rows in the same table. Doing this efficiently, along with the many types of complex searches and cross-referencing, while also ensuring data integrity, is a complex task.
postgres (PostGreSQL) is a free
SQL server written under the BSD
postgres supports an extended
subset of SQL92. [The definitive SQL
standard.] It does a lot of very nifty things
that no other database can (it seems). About the only commercial
equivalent worth buying over
postgres is a certain very expensive industry leader.
postgres runs on every flavor of UNIX and also on Windows NT.
postgres documentation proudly states:
The Object-Relational Database Management System now known as PostgreSQL (and briefly called Postgres95) is derived from the Postgres package written at Berkeley. With over a decade of development behind it, PostgreSQL is the most advanced open-source database available anywhere, offering multi-version concurrency control, supporting almost all SQL constructs (including subselects, transactions, and user-defined types and functions), and having a wide range of language bindings available (including C, C++, Java, Perl, Tcl, and Python).
postgres is also fairly dry. Most people ask why it doesn't
have a graphical frontend. Considering that it runs on so many
different platforms, it makes sense for it to be purely a back-end
engine. A graphical interface is a different kind of software project
that would probably support more than one type of database server
at the back and possibly run under only one kind of graphical
postgres package consists of the files
described in the next two sections:
postgres packages consists of the user programs
and the server programs
Each of these programs has a man page which you should get an inkling of.
Further man pages provide references to actual SQL commands.
man l select (explained further on):
Most important is the enormous amount of HTML documentation
that comes with
postgres. Point your web browser to
then dive into the
Finally, there are the start and stop scripts in
and the directory in which the database tables themselves are stored:
postgres can be gotten prepackaged for your favorite
distribution. Simply install the package using
and then follow the instructions given below.
postgres server if it is running; the
script may be called
commands in parentheses):
init.d script to support TCP requests. There
be a line like the following to which you can add the
Mine looks like:
which also (with the
-o -e option) forces European
formats (28/4/1984 instead of 4/28/1984). Note that hosts
will not be able to connect unless you edit your
/etc/postgresql/pg_hba.conf on Debian) file, and add lines
In either case, you should check this file to ensure that
only trusted hosts can connect to your database, or remove the
option altogether if you are only connecting from the local machine.
To a limited extent, you can also limit what users can connect within
It would be nice if the UNIX domain socket that
postgres listens on (i.e.,
permissions 0770 instead of 0777. That way, you could limit connections
to only those users belonging to the
postgres group. You can add
this feature by searching for the C
chmod command within
src/backend/libpq/pqcomm.c inside the
sources. Later versions may have added a feature to set the permissions
on this socket.
postgres, you need a user of that name.
If you do not already have one then enter
and restart the server with
init.d script initializes a template database
first run, so you may have to start it twice.
Now you can create your own database. The following example
creates a database
finance as well as a
finance. It does these creations while being user
postgres (this is what the
-U option is for). You should
run these commands as user
root or as user
Now that the database exists, you can begin running SQL queries.
The preceeding rows are
tables. Some are actual
tables, and some are views
of tables. [A selective representation of an actual table.]
To get a list of databases, try:
The following are 99% of the commands you are ever going to use. (Note that all SQL commands require a semicolon at the end--you won't be the first person to ask why nothing happens when you press without the semicolon.)
To create a table called
people, with three columns:
The created table will title the columns,
address. Columns are
typed. This means
that only the kind of data that was specified at the time of creation
can go in that column. In the case of
gender, it can only
be true or false for the
boolean type, which
we will associate to the male and female genders. There is probably no
reason to use the boolean value here: using an integer or text field
can often be far more descriptive and flexible. In the case of
address, these can hold anything, since
they are of the
text type, which is the most encompassing type
Note that in the
postgres documentation, a ``column'' is
called an ``attribute'' for historical reasons.
You should try to choose types according to the kind of searches you are going to do and not according to the data it holds. Table 38.1 lists the most of the useful types as well as their SQL92 equivalents. The types in bold are to be used in preference to other similar types for greater range or precision:
SELECT statement is the most widely used statement in SQL. It
returns data from tables and can do searches:
ALTER statement changes something:
You cannot drop columns in
postgres; you must
create a new table from the old table without the column.
How to do this will become obvious further on.
DROP command to delete most things:
Insert a row with (you can continue typing over multiple lines):
The return value is the
oid (Object ID) of the row.
postgres is an
Object Relational database. This term gets thrown
around a lot, but it really means that every table has a hidden column called the
oid column that stores a unique identity number for each row. The
identity number is unique across the entire database. Because it uniquely
identifies rows across all tables, you could call the rows ``objects.''
oid feature is most useful to programmers.
oid of the above row is
20280. To find it:
To list selected columns, try:
It should be obvious what these do.
Here we create a new table and fill two of its columns from columns in our original table:
Delete selected rows, like
About the simplest search you can do with
Or alternatively, case insensitively and across the
% is a wildcard that matches any length of text
Paul, and the final
% matches any text after.
It is the usual way of searching with a field, instead of trying to get an
The possibilities are endless:
dumping and restoring tables as plain text
people table to
as tab delimeter, newline terminated rows.
people table to
as comma-delimited, newline-terminated rows, with
whereever there is supposed to be a zero byte.
Similarly, the command
inserts into the table
people the rows from
It assumes one line per row and the tab character between each
Note that unprintable characters are escaped with a
\ in both output and the interpretation of input.
Hence, it is simple to get data from another database. You just have to work out how to dump it as text.
dumps your entire database as plain
text. If you try this on your database, you will notice that the output contains
straightforward SQL commands. Your database can be reconstructed from scratch
by piping this output through stdin of the
psql command. In other words,
pg_dump merely produces the exact sequence of SQL commands necessary to
reproduce your database.
Sometimes a new version of
postgres will switch to a database file format
that is incompatible with your previous files. In this case it is prudent to do
carefully save the output) before upgrading. The output
pg_dumpall can once again be fed through stdin of the
and contains all the commands necessary to reconstruct all your databases as well
as all the data they contain.
When you have some very complicated set of tables in front of you, you are likely to want to merge, select, search, and cross-reference them in innumerable ways to get the information you want out of them.
Being able to efficiently query the database in this way
is the true power of SQL, but this is about as far as I am going to go
postgres documentation cited above contains
details on everything you can do.
University Computer Science majors learn about subjects like Entity Modeling, Relational Algebra, and Database Normalization. These are formal academic methods according to which good databases are designed. You should not venture into constructing any complex database without these methods.
Most university book shops will have academic books that teach formal database theory.
Next: 39. smbd Up: rute Previous: 37. crond and atd   Contents